#!/usr/local/pd-admin2/bin/perl -w

use strict;
use DBI;
use YAML::XS;
use Data::Dumper;

my %disp_table = (
      export_data => \&export_data,
      import_data => \&import_data,
);

use vars qw($dsn $user $password);
do "/opt/pdadmin/etc/pdadmin.conf" or die "Can't do it: $!";

my $user = 'root';
open RPW, '/opt/pdadmin/etc/mysql_rootpw.conf' or die $!;
my $password = <RPW>;
close RPW;
chomp $password;

$dsn = "DBI:mysql:database=mysql;host=localhost;";


my $dbh = DBI->connect($dsn, $user, $password) or die "can't connect!";
$SIG{__DIE__} = sub {  $dbh->disconnect if $dbh;  die @_;};

sub get_row(@) {
  my $query = shift;

  my $sth = $dbh->prepare($query) or die "cannot prepare query '$query'";
  my $rv;
  unless ($rv = $sth->execute(@_)) {
    $dbh = DBI->connect($::dsn, $::user, $::password) or die "can't connect!";
    $sth = $dbh->prepare($query) or die "cannot prepare query '$query'";
    $rv  = $sth->execute(@_) or die "cannot execute query '$query': ". $dbh->errstr;
  }
  my @row = $sth->fetchrow_array;
  $sth->finish;
  return @row;
}

sub update_row {
   my $query = shift;
   print " -- $query --\n";
   my $rueck = '';
   my ($sth, $rv, $rc);
   if ($sth = $dbh->prepare($query)) {
   if ($sth->execute(@_)) {
      $rueck = $sth->{mysql_insertid} || 1;
   }
      $rc = $sth->finish;
   }
   return $rueck;
}

sub passwort_field() {
   my ($mysql_version) = get_row 'SELECT version()';
   my $pw_field = 'Password';
   if ($mysql_version =~ /(5\.7|10\.5|8\.0)/) {
      $pw_field = 'authentication_string'; 
   }
   return $pw_field ;
}

sub export_data() {

   my @user; 
   my $pw_field = passwort_field;

   my $query = "select concat(user, '.' ,host) as unique_key, user, host, $pw_field as password from mysql.user where user not like '%.%'";
   my $sth = $dbh->prepare($query) or die "cannot prepare query '$query'";
   my $rv  = $sth->execute or die "cannot execute query '$query'";
   my $struct =  $sth->fetchall_hashref('unique_key');

   my $yaml = YAML::XS::Dump $struct;

   open UY, '>', '/root/mysql_user.yaml' or die $!;
   print UY $yaml;
   close UY;

   $query = "select COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'db' AND TABLE_SCHEMA = 'mysql'";
   $sth = $dbh->prepare($query) or die "cannot prepare query '$query'";
   $rv  = $sth->execute or die "cannot execute query '$query'";
   my @columns;
   while (my ($column_name) = $sth->fetchrow_array) {
     push @columns, $column_name;
   }
   $sth->finish;
   my $columns = join ', ', @columns;
   $query = "select concat(db, '.', user, '.' ,host) as unique_key, $columns from mysql.db where user not like '%.%'";
   $sth = $dbh->prepare($query) or die "cannot prepare query '$query'";
   $rv  = $sth->execute or die "cannot execute query '$query'";
   $struct =  $sth->fetchall_hashref('unique_key');

   $yaml = YAML::XS::Dump $struct;

   open DBY, '>', '/root/mysql_db.yaml' or die $!;
   print DBY $yaml;
   close DBY;

}

sub read_file($) {
   my $file = shift;
   open F, $file or die $!;
   my $content = join '', <F>;
   close F;
   return $content;
}

sub mysql_create_user($$$) {

    my $user = shift;
    my $host = shift;
    my $password = shift;

    my $pw_field = passwort_field;

    my ($exists) = get_row "select user from mysql.user where user=? and host=?", $user, $host;
    print "$user $host -$exists-\n";

    unless (defined $exists and length $exists) {
        if ($host ne '%') {
           update_row "create user '$user'\@'$host' identified by 'fhajfh493^F*Yhvndkk-3i'";
        } else {
           update_row "create user '$user' identified by 'fhajfh493^F*Yhvndkk-3i'";
        }
    }
    #update_row "set password for $user\@$host = ?", $password;
    update_row "update mysql.user set $pw_field=? where user=? and host=?", $password, $user, $host;

}

sub mysql_create_db($) {

   my $struct = shift;

   my $dbname = $struct->{Db};
   $dbname =~ y/\\//d; 

   print "$dbname\n";

   my ($exists) = get_row "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '$dbname'";
   unless (defined $exists and length $exists) {
       update_row "create database $dbname";
   }

   my @privs = grep /_priv$/, sort keys %{$struct};
   my $priv_values = join(', ', map "$_='$struct->{$_}'", @privs);

   ($exists) = get_row "select user from mysql.db where user=? and host=? and db=?", $struct->{User}, $struct->{Host}, $struct->{Db};

   if (defined $exists and length $exists) {
      update_row "update mysql.db set $priv_values where user=? and host=?", $struct->{User}, $struct->{Host};
   } else {
      update_row "insert into mysql.db set $priv_values, user=?, host=?, db=?", $struct->{User}, $struct->{Host}, $struct->{Db};
   }

}

sub import_data() {
  
   my $yaml = read_file '/root/mysql_user.yaml';
   my $struct = YAML::XS::Load $yaml or die $!;

   for my $key (keys %{$struct}) {
       mysql_create_user $struct->{$key}->{user}, $struct->{$key}->{host}, $struct->{$key}->{password};
   }

   $yaml = read_file '/root/mysql_db.yaml';
   $struct = YAML::XS::Load $yaml or die $!;

   for my $key (keys %{$struct}) {
       mysql_create_db $struct->{$key};
   }

   update_row "flush privileges";

}

my $todo = shift || '';
if (defined $disp_table{$todo}) {
   print $disp_table{$todo}->();
} else {
   print "action undefinied\n";
}