# DATABASE INITIALIZATION STEP
#

sub step_db_init {
  return 100;
}

sub step_db {
  my ($sqlfile, $sqltext, $oldsql, @sqltables, $dbh, $drh, $rc, $dbok, $dbcreated, $res);
  my @comptables = qw( plrdata plrmaps plrweapons plrroles plrvictims gamemaps gameweapons );
  my $saver = $conf->{savetype};
  my $c = copyhash($conf);			# get a copy of the config
  my $s = $c->{$saver};				# shortcut to the saver sub-hash in the temporary $c config
  my $DONE = 0;

  require DBI;					# Make sure we're 

  DBI->trace(1) if $args->{options} =~ /\bdbtrace\b/;


  if (!defined $args->{dbprofiles}) {		# do not delete any profiles: "-profiles" was not specified
    $args->{dbprofiles} = '';
  } elsif ($args->{dbprofiles} eq '') {		# delete all profiles: "-profiles" was specified
    $args->{dbprofiles} = 'players,clans';
  } else {					# delete the profile(s) specified
    $args->{dbprofiles} = $args->{dbprofiles};
  }
  $args->{dbprofiles} = lc $args->{dbprofiles};
  if ($args->{dbprofiles} eq 'all') {		# alternate method
    $args->{dbprofiles} = 'players,clans';
  }

  $wiz->wraptext($lang->{db_start});
  initdb() or exit(1);

  $dbok = 0; 
  $dbcreated = 0;
#  $s->{password} = ""; 				# leave it blank, since some users do not use a PW on their SQL server  $oldsql = $sqltext;

  $sqlfile = catfile('install', 'sql_' . $c->{savetype} . '.txt');
  $sqltext = join('', slurpfile(catfile($FindBin::Bin, $sqlfile)));
  $sqltext =~ s/^#.*$//mg;				# remove the comments
  $sqltext = trim($sqltext);				# remove leading/trailing whitespace
  $sqltext =~ s/\n{3,}/\n\n/mg;				# remove blank lines (leaving only a single newline between each table)
  $oldsql = $sqltext;
  @sqltables = split(/\n\n/, $sqltext);			# seperate each table
  $wiz->{data}{sqlfile} = $sqlfile;
  $wiz->{data}{totaltables} = scalar @sqltables;

  if ($sqltext) {
    $wiz->wraptext($lang->{db_sqlinitok});
    $drh = DBI->install_driver($c->{savetype});
    $wiz->wraptext($lang->{db_userhelp});
    while (!$DONE) {
      $sqltext 		= $oldsql;							# restore original SQL statements
      $wiz->wraptext($lang->{db_username});
      $s->{username}	= $wiz->promptfor("DB Username [$s->{username}]: ", $s->{username});
      $s->{username} 	= '' if lc $s->{username} eq 'none';
      $wiz->wraptext($lang->{db_password});
      $s->{password}	= $wiz->promptfor("DB Password [$s->{password}]: ", $s->{password});
      $s->{password} 	= '' if lc $s->{password} eq 'none';
      $wiz->wraptext($lang->{db_hostname});
      $s->{host} 	= $wiz->promptfor("DB Hostname [$s->{host}]: ", $s->{host});
      $wiz->wraptext($lang->{db_dbname});
      $s->{dbname} 	= $wiz->promptfor("DB Name [$s->{dbname}]: ", $s->{dbname});
      $wiz->wraptext($lang->{db_prefix});
      $s->{tableprefix} = $wiz->promptfor("DB Table prefix [$s->{tableprefix}]: ", $s->{tableprefix});
      $wiz->wraptext($lang->{db_compression});
      $s->{compression}	= $wiz->promptfor("DB Compression [$s->{compression}]: ", $s->{compression});

      $wiz->{data}{dbuser} 		= $s->{username};
      $wiz->{data}{dbpass} 		= $s->{password};
      $wiz->{data}{dbhost} 		= $s->{host};
      $wiz->{data}{dbname} 		= $s->{dbname};
      $wiz->{data}{dbtblprefix} 	= $s->{tableprefix};
      $wiz->{data}{dbcompression} 	= $s->{dbcompression};

      $wiz->wraptext($lang->{db_searchdb}, {trimtail => 1});

      # try to connect to the DB first (this will hopefully fix the following 'createdb' function from stalling on some servers
      $dbh = DBI->connect("DBI:$c->{savetype}:$s->{dbname};host=$s->{host}", $s->{username}, $s->{password}, { PrintError => 0, RaiseError => 0, AutoCommit => 1 } );

      # this will attempt to create the database, if its already present or we don't have permission it will fail.
      $rc = !$dbh ? $drh->func('createdb', $s->{dbname}, $s->{host}, $s->{username}, $s->{password}, 'admin') : undef;
      if ($rc) {
        $wiz->wraptext($lang->{db_dbcreated});
        $dbok = 1;
        $dbcreated = 1;
      } else {
        # DB could not be created, lets see if its already there (if we haven't connected above already)... 
        $dbh = DBI->connect("DBI:" . $c->{savetype} . ":$s->{dbname};host=$s->{host}", $s->{username}, $s->{password}, { PrintError => 0, RaiseError => 0, AutoCommit => 1 } ) if !$dbh;
        if (!$dbh) {
          $wiz->wraptext($lang->{error});
          $wiz->wraptext($lang->{db_connecterr});
          print hline('-'), "\n", $DBI::errstr, "\n", hline('-'), "\n";
          if ($DBI::errstr =~ /Access denied/i) {
            $wiz->wraptext($lang->{db_usererr});
            next if $wiz->prompt_yesno(1,$lang->{yn_tryagain});
            $wiz->wraptext($lang->{db_fatal});

          } elsif ($DBI::errstr =~ /Unknown.+?Host/i) {
            $wiz->wraptext($lang->{db_hosterr});
            next if $wiz->prompt_yesno(1,$lang->{yn_tryagain});
            $wiz->wraptext($lang->{db_fatal});

          } elsif ($DBI::errstr =~ /Can't connect/i) {
            $wiz->wraptext($lang->{db_srverr});
            next if $wiz->prompt_yesno(1,$lang->{yn_tryagain});
            $wiz->wraptext($lang->{db_fatal});

          } else {
            $wiz->wraptext($lang->{db_unknownerr});
            next if $wiz->prompt_yesno(1,$lang->{yn_tryagain});
            $wiz->wraptext($lang->{db_fatal});
          }
        } else {
          $wiz->wraptext($lang->{db_dbexists});
          $dbok = 1;

#          $wiz->wraptext($lang->{db_deldb});
#          my $dropdb = $wiz->prompt_yesno(0,$lang->{db_yndeldb});

        }
      }
      $DONE = 1;
    } # while !done

    # Now we need to create the table structure for the SQL DB ...
    if ($dbok) {
      if (!$dbh) {
        $dbh = DBI->connect("DBI:" . $c->{savetype} . ":$s->{dbname};host=$s->{host}", $s->{username}, $s->{password}, { PrintError => 0, RaiseError => 0, AutoCommit => 1 } );
        if (!$dbh) {
          $wiz->wraptext($lang->{db_tblfatal});
          $wiz->pause(1);
        }
      }

      # Is the DBH actually valid at this point? .... create the tables if it is!!
      if ($dbh) {
        my ($tbl, $ok, $totalok, $totaltables);
        my $dropfirst = 0;
        $totaltables = scalar @sqltables;
        $totalok = 0;
        $wiz->wraptext($lang->{db_dotables});
        if (!$dbcreated) {
          $wiz->wraptext($lang->{db_deltables});
          $dropfirst = $wiz->prompt_yesno(0,$lang->{db_yndeltables},undef,1);
        }

        # remove the COMPILED tables ...
        if ($dropfirst) {
          $wiz->remove_state_source($conf->{logsource});	# remove the current source(s) from the state file (w/o deleting the entire file)
          $wiz->wraptext($lang->{db_delcompiled}, {trimtail => 1});
          my $c_tblprefix = "c_";		# at some point I need to synchronize this with the PS::Saver::mysql object
          foreach my $tbl (@comptables) {
            $tbl = $s->{tableprefix} . $c_tblprefix . $tbl . '_' . $conf->{gametype};
            $tbl .= '_' . $conf->{modtype} if $conf->{modtype};
            $wiz->{data}{table} = $tbl;
            if (!$dbh->do("DROP TABLE IF EXISTS `$tbl`")) {
              $wiz->{data}{dberror} = $dbh->errstr;
              $wiz->wraptext($lang->{db_delcomperr});
            }
          }
          $wiz->wraptext($lang->{ok});
        }

        foreach my $sql (@sqltables) {
          $sql =~ s/pstats_/$s->{tableprefix}/ if $s->{tableprefix} ne 'pstats_';
          if ($sql =~ /^\s*CREATE TABLE `(.+?)`/) {
            $tbl = $wiz->{data}{table} = $1;

            # If the database was just created then we always want the profile tables to be created
            if (!$dbcreated) {
              $totalok++ && next if ($tbl =~ /plr_profile$/ and $args->{dbprofiles} !~ /player/);	# ignore player profiles if specified
              $totalok++ && next if ($tbl =~ /clans_profile$/ and $args->{dbprofiles} !~ /clan/);	# ignore clan ...
            }

            if ($dropfirst) {
              $wiz->wraptext($lang->{db_deletetbl}, {trimtail => 1});
              if ($dbh->do("DROP TABLE IF EXISTS `$tbl`")) {
                $wiz->wraptext($lang->{ok});
              } else {
                $wiz->{data}{dberror} = $dbh->errstr;
                $wiz->wraptext($lang->{db_delerror});
              }
            }
            $wiz->wraptext($lang->{db_createtbl}, {trimtail => 1});
            $ok = $dbh->do($sql);
            if ($ok) {
              $wiz->wraptext($lang->{ok});
              $totalok++;
            } else {
              $wiz->{data}{dberror} = $dbh->errstr;
              $wiz->wraptext($lang->{db_error});
            }
          } else {
		# NOTHING
          }
        }

        if ($totalok != $totaltables) {
          $wiz->wraptext($lang->{db_missingtbls});
        } else {
          $wiz->wraptext($lang->{db_done});
        }
      }

      if (
	$s->{username} ne $conf->{$saver}{username} or
	$s->{password} ne $conf->{$saver}{password} or
	$s->{host} ne $conf->{$saver}{host} or
	$s->{dbname} ne $conf->{$saver}{dbname} or
	$s->{tableprefix} ne $conf->{$saver}{tableprefix} or
	$s->{compression} ne $conf->{$saver}{compression}
      ) 
      {
        $conf = $c;
        confupdate($conf,0,1);
      }

      # INSERT the PsychoStats VERSION into the 'info' table. We don't care if this query fails.
      $res = $dbh->do("INSERT INTO " . $s->{tableprefix} . "info SET `name`='version', `value`=" . $dbh->quote($PS::VERSION));
#      print "MYSQL ERROR: " . $dbh->errstr . "\n" unless $res;

    } # if $dbok ...
  } else {
    $wiz->wraptext($lang->{db_sqliniterr});
  }

  $DBH = $dbh if defined $dbh;
}

1;