#!/usr/bin/perl
# ----------------------------------------------------------------------------
# This software is Copyright (C) 2007, All Rights Reserved
# ----------------------------------------------------------------------------
# This program is protected by local and international copyright laws. Any
# use of this program is subject to the the terms of the license agreement
# included as part of this distribution archive. Any other uses are strictly
# prohibited without the written permission of the Vendor and all
# other rights are reserved.
# -----------------------------------------------------------------------------
# Warning : Modifying this program violates your license agreement!
# -----------------------------------------------------------------------------

package IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::MoveData;

use IT::Globals;
use IT::DB::Globals;

use strict;

use vars qw($RECORDS_TO_MOVE_AT_ONCE $NEW_TABLE_SUFFIX);
$RECORDS_TO_MOVE_AT_ONCE  = 50; # max number of records to load in memory when moving data
$NEW_TABLE_SUFFIX         = "_new"; # new tables get this suffix until they're renamed to replace the old tables
                                       # (because we can't have more than one table in the schema at a time with the same name)

# ----------------------------------------------------------------------------
# Function    : moveDataForm
# Description :
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::moveDataForm();
# ----------------------------------------------------------------------------

sub moveDataForm {
  my $errorMessage = shift || $FORM->{'error'};
  if ($errorMessage) { &IT::App::alert($errorMessage); }

  ### load current values
  my @connectionFields  = qw(engine appTablePrefix hostname port database username password);
  my $currentConnection = {};
  foreach my $fieldName (@connectionFields) {
    $currentConnection->{$fieldName} = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, $fieldName);
  }
  # set either fileTablePrefix or mysqlTablePrefix
  $currentConnection->{"$currentConnection->{'engine'}TablePrefix"} = $currentConnection->{'appTablePrefix'};



  ### define page placeholders
  my $pagePlaceholders;
  $pagePlaceholders->{'menuGroup'}  = 'databaseEditor';
  $pagePlaceholders->{'saveAction'} = 'databaseEditor_moveData';
  $pagePlaceholders->{'currentDatabaseEngine'} = $currentConnection->{'engine'};
  $pagePlaceholders->{'appTablePrefix'}        = $currentConnection->{'appTablePrefix'};
  $pagePlaceholders->{'datadir'}               = $DATADIR;
  $pagePlaceholders->{'engineType'}            = &firstDefinedValue( $FORM->{'engineType'},       $currentConnection->{'engine'} );
  $pagePlaceholders->{'fileTablePrefix'}       = &firstDefinedValue( $FORM->{'fileTablePrefix'},  $currentConnection->{'fileTablePrefix'} );
  $pagePlaceholders->{'mysqlTablePrefix'}      = &firstDefinedValue( $FORM->{'mysqlTablePrefix'}, $currentConnection->{'mysqlTablePrefix'} );
  $pagePlaceholders->{'mysqlHostname'}         = &firstDefinedValue( $FORM->{'mysqlHostname'},    $currentConnection->{'hostname'} );
  $pagePlaceholders->{'mysqlPort'}             = &firstDefinedValue( $FORM->{'mysqlPort'},        $currentConnection->{'port'} );
  $pagePlaceholders->{'mysqlDatabase'}         = &firstDefinedValue( $FORM->{'mysqlDatabase'},    $currentConnection->{'database'} );
  $pagePlaceholders->{'mysqlUsername'}         = &firstDefinedValue( $FORM->{'mysqlUsername'},    $currentConnection->{'username'} );
  $pagePlaceholders->{'mysqlPassword'}         = &firstDefinedValue( $FORM->{'mysqlPassword'},    $currentConnection->{'password'} );

  $pagePlaceholders->{'overwriteTables'}       = $FORM->{'overwriteTables'};

  $pagePlaceholders->{'isTargetTablesSetup'}   = 0;

  ### display page
  &IT::App::displayInterface({
    'template'           => "admin/menus/admin/databaseActions.html",
    'templatecell'       => 'moveDataForm',
    'selectedMenuOption' => 'admin',
    'menuGroup'          => 'databaseEditor',
    'placeholders'       => $pagePlaceholders,
  });
}

# ----------------------------------------------------------------------------
# Function    : moveData
# Description :
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::moveData();
# ----------------------------------------------------------------------------

sub moveData {

  &IT::App::demoModeAlertAndAction("Database Actions are disabled in demo mode.", \&IT::App::Menu::Admin::DatabaseEditor::TableEditor::listTables);

  ### Setup target tables
  if (!$FORM->{'isTargetTablesSetup'}) {

    # error checking
    my $errors;
    $errors .= &IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::_getSelectDatabaseFieldErrors();
    unless (exists $FORM->{'overwriteTables'})    { $errors .= "Overwrite tables not specified! (must be blank or checked)\n"; }
    if ($errors) { &moveDataForm($errors); }

    &IT::FileLock::getLock('exclusive');

    # cleanup data from failed moves (if any)
    &_removeOldTargetTables();
    &_removeOldTargetConnection();

    # create new target connection and tables
    &_createNewTargetConnection();
    &_createNewTargetTables();

    &IT::FileLock::releaseLock();
  }

  ### Display Multistep Status Page

  my $pagePlaceholders;
  $pagePlaceholders->{'menuGroup'}             = 'databaseEditor';
  $pagePlaceholders->{'saveAction'}            = 'databaseEditor_moveData';
  $pagePlaceholders->{'engineType'}            = $FORM->{'engineType'};
  $pagePlaceholders->{'fileTablePrefix'}       = $FORM->{'fileTablePrefix'};
  $pagePlaceholders->{'mysqlTablePrefix'}      = $FORM->{'mysqlTablePrefix'};
  $pagePlaceholders->{'mysqlHostname'}         = $FORM->{'mysqlHostname'};
  $pagePlaceholders->{'mysqlPort'}             = $FORM->{'mysqlPort'};
  $pagePlaceholders->{'mysqlDatabase'}         = $FORM->{'mysqlDatabase'};
  $pagePlaceholders->{'mysqlUsername'}         = $FORM->{'mysqlUsername'};
  $pagePlaceholders->{'mysqlPassword'}         = $FORM->{'mysqlPassword'};
  $pagePlaceholders->{'overwriteTables'}       = $FORM->{'overwriteTables'};
  $pagePlaceholders->{'isTargetTablesSetup'}   = 1;

  &IT::App::alert('<font color="red"><b>DATABASE UPDATES ARE IN PROGRESS</b></font>', 'keepHTML');
  &IT::App::alert('Please, do not stop or close your browser!');

  &IT::App::displayInterface({
    'template'           => "admin/menus/admin/databaseActions.html",
    'templatecell'       => 'moveDataInProgress',
    'selectedMenuOption' => 'admin',
    'menuGroup'          => 'databaseEditor',
    'placeholders'       => $pagePlaceholders,
    'doNotExit'          => 1,
  });

  ### Disable Database Writes for everyone but us
  &IT::FileLock::getLock('exclusive');
  &IT::DB::disableDatabaseWrites(120);  # for 120 seconds (2 minutes)
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
  &IT::FileLock::releaseLock();

  ### Allow database writes and disable filelocking for us only
  &IT::DB::enableDatabaseWritesForThisProcess();
  local $IT::FileLock::DISABLE_FILELOCKING = 1; # We're expecting server timeouts here so we're using disabled filelocking to avoid stuck locks being left by a killed process.

  ### Move records
  my $elapsedSeconds;
  my($sourceTable, $targetTable)  = &_getFirstIncompleteTableToMove();
  my $recordCount                 = &IT::DB::count("$sourceTable") if $sourceTable;
  my $recordsMoved                = &IT::DB::count("$targetTable") if $sourceTable;
  my $moveFinished                = 0;

  while ($elapsedSeconds <= $SETTINGS->{'maxSecondsPerProcess'}) {
    $elapsedSeconds = time() - $^T;

    # finish when all tables done
    if (!$sourceTable) {
      $moveFinished = 1;
      last;
    }

    # display status message
    my $moveRangeOffset = $recordsMoved;
    my $moveRangeStart  = $moveRangeOffset + 1;
    my $moveRangeEnd   = &IT::Common::mathMin(($moveRangeOffset+$RECORDS_TO_MOVE_AT_ONCE), $recordCount);
    &_addStatusMessage("Copying records $moveRangeStart - $moveRangeEnd of $recordCount to table $targetTable");

    # move records and update counter
    eval { &_copyRecordsToTargetTable($sourceTable, $targetTable, $moveRangeOffset); };
    if ($@) {
      &_addStatusMessage("Error: $@");
      &IT::DB::enableDatabaseWrites();
      &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
      exit;
    }
    $recordsMoved += $RECORDS_TO_MOVE_AT_ONCE;

    # reload after moving each table
    my $isTableMoved = $recordsMoved >= $recordCount;
    if ($isTableMoved) { last; }
  }

  ### Reload on timeout
  if (!$moveFinished) {
    &_addStatusMessage("reloading browser...");
    &_resubmitFormWithPost();
  }

  ### On all steps finished
  elsif ($moveFinished) {
    &_addStatusMessage("Switching to new database...");
    &_switchToTargetConnection();

    &IT::DB::enableDatabaseWrites();
    &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);

    &_addStatusMessage("Move Complete, reloading browser...");
    &IT::Common::redirectBrowserToURL("$CGIURL?action=databaseEditor");
  }

  exit;
}

# ----------------------------------------------------------------------------
# Function    : moveDataTimeout
# Description :
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::moveDataTimeout();
# ----------------------------------------------------------------------------

sub moveDataTimeout {

  my $error = "";
  $error .= "The server timed out and was unable to complete the move operation.\n";
  $error .= "The last status message was: $FORM->{'lastStatusMessage'}\n";
  $error .= "Set 'Max Seconds Per Step' to a lower value and try again.\n";

  &moveDataForm($error);
}

# ------------------------------------------------------------------------------
# Function    : _getFirstIncompleteTableToMove
# Description : Return the first table from the (sorted) list of tables that
#               hasn't been completely moved yet.  This means that either no
#               records have been copied or some of the records, but not all.
# Usage       : my($currentTable, $targetTable) = &_getFirstIncompleteTableToMove();
# ------------------------------------------------------------------------------

sub _getFirstIncompleteTableToMove {

  my @currentTables      = &IT::DB::Schema::listTablesOnConnection($CURRENT_CONNECTION_NAME);
  my @targetTables       = &IT::DB::Schema::listTablesOnConnection($TARGET_CONNECTION_NAME);
  my $currentTablePrefix = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');
  my $targetTablePrefix  = &IT::DB::Schema::getConnectionAttributes($TARGET_CONNECTION_NAME, 'appTablePrefix');

  # get first table that isn't completely moved
  foreach my $index (0..$#currentTables) {
    my $currentTable            = $currentTables[$index];
    my $targetTable             = $currentTable;
    $targetTable                =~ s/^$currentTablePrefix/$targetTablePrefix/ || die "Couldn't match table prefix of '$currentTablePrefix' on '$targetTablePrefix'!";
    $targetTable                .= $NEW_TABLE_SUFFIX;

    my $currentTableRecordCount = &IT::DB::count($currentTable);
    my $targetTableRecordCount  = &IT::DB::count($targetTable);

    my $isIncomplete = ($currentTableRecordCount > $targetTableRecordCount);

    if ($isIncomplete) {
      return($currentTable, $targetTable);
    };
  }

  return ();
}

# ----------------------------------------------------------------------------
# Function    : _removeOldTargetTables
# Description : remove any target tables left over from a failed move
# Usage       : &_removeOldTargetTables();
# ----------------------------------------------------------------------------

sub _removeOldTargetTables {
  &IT::FileLock::lockRequired('exclusive');

  # return if no target connection
  unless (&IT::DB::Schema::existsConnection($TARGET_CONNECTION_NAME)) { return; }

  # drop old target tables (from schema AND datastore)
  my @targetTables = &IT::DB::Schema::listTablesOnConnection($TARGET_CONNECTION_NAME);
  foreach my $tableName (@targetTables) {
    &IT::DB::dropTable($TARGET_CONNECTION_NAME, $tableName) if &IT::DB::existsTable($TARGET_CONNECTION_NAME, $tableName);
    &IT::DB::Schema::dropTable($tableName);
  }

  # Save Schema
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
}

# ----------------------------------------------------------------------------
# Function    : _removeOldTargetConnection
# Description : remove target connection left over from a failed move
# Usage       : &_removeOldTargetConnection();
# ----------------------------------------------------------------------------

sub _removeOldTargetConnection {
  &IT::FileLock::lockRequired('exclusive');

  if (&IT::DB::Schema::existsConnection($TARGET_CONNECTION_NAME)) {
    &IT::DB::Schema::dropConnection($TARGET_CONNECTION_NAME);
    &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
  }

}

# ----------------------------------------------------------------------------
# Function    : _createNewTargetConnection
# Description : create new target connection for move operation
# Usage       : &_createNewTargetConnection();
# ----------------------------------------------------------------------------

sub _createNewTargetConnection {
  &IT::FileLock::lockRequired('exclusive');

  # create new connection
  &IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::_createNewTargetConnectionFromInput();

  # display connection errors (if any)
  my $errors = &IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::_getConnectionErrors($TARGET_CONNECTION_NAME);
  if ($errors) { &moveDataForm($errors); }

  # display error is current and target databases are the same!
  if (&_isCurrentAndTargetDatabasesTheSame()) {
    my $errors = "The new database and the current database point to the same location.\n";
    $errors   .= "Enter a different database location or click Cancel and select a different database action.\n";
    &moveDataForm($errors);
  }

  # Save Schema
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);

}

# ------------------------------------------------------------------------------
# Function    : _isCurrentAndTargetDatabasesTheSame
# Description : Check to see if tables created on new connection appear on old
#               database as well.
# Usage       : if (&_isCurrentAndTargetDatabasesTheSame()) {
# ------------------------------------------------------------------------------

sub _isCurrentAndTargetDatabasesTheSame {
  &IT::FileLock::lockRequired('exclusive');

  # find unused tablename
  my $tempTableName;
  while (!$tempTableName ||
         &IT::DB::existsTable($TARGET_CONNECTION_NAME, $tempTableName) ||
         &IT::DB::existsTable($CURRENT_CONNECTION_NAME, $tempTableName)) {
    $tempTableName = "temp_table_" . int(rand(999_999)+1);
  }

  # check if created tables exist on both connections
  my $fieldDefs = [{ 'name' => 'num', 'type' => 'int', }];
  &IT::DB::createTable($TARGET_CONNECTION_NAME, $tempTableName, $fieldDefs);
  my $isDatabasesTheSame = &IT::DB::existsTable($CURRENT_CONNECTION_NAME, $tempTableName);
  &IT::DB::dropTable($TARGET_CONNECTION_NAME, $tempTableName, $fieldDefs);

  return $isDatabasesTheSame;
}

# ----------------------------------------------------------------------------
# Function    : _createNewTargetTables
# Description : Create a new empty target table for every current table for
#               moving data into.
# Usage       : &_createNewTargetTables();
# ----------------------------------------------------------------------------

sub _createNewTargetTables {
  &IT::FileLock::lockRequired('exclusive');

  ### error checking

  # check for tables in the way on new connection
  my $newTablePrefix = $FORM->{ "$FORM->{'engineType'}TablePrefix" } || die "can't get table prefix!";
  my @targetTables   = &IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::_getTableListWithDifferentPrefixes($CURRENT_CONNECTION_NAME, $newTablePrefix);
  my @tablesInTheWay;

  foreach my $tableName (@targetTables) {
    if (&IT::DB::existsTable($TARGET_CONNECTION_NAME, $tableName)) {
      push(@tablesInTheWay, $tableName)
    };
    if (&IT::DB::existsTable($TARGET_CONNECTION_NAME, "$tableName$NEW_TABLE_SUFFIX")) {
      push(@tablesInTheWay, "$tableName$NEW_TABLE_SUFFIX")
    };
  }

  # display errors about tables in the way
  if (!$FORM->{'overwriteTables'} && @tablesInTheWay) {
    my $errors = "The new database already has some tables with the same name as the program tables:\n";
    $errors   .= join("\n", @tablesInTheWay);
    &moveDataForm($errors);
  }

  # or, overwrite tables in the way (if user requested)
  elsif ($FORM->{'overwriteTables'} && @tablesInTheWay) {
    foreach my $tableName (@tablesInTheWay) {
      &IT::DB::dropTable($TARGET_CONNECTION_NAME, $tableName);
    }
  }

  ### create new tables

  my @currentTables = &IT::DB::Schema::listTablesOnConnection($CURRENT_CONNECTION_NAME);
  for my $index (0..$#currentTables) {
    my $oldTableName = $currentTables[$index];                    # with old prefix
    my $newTableName = $targetTables[$index]. $NEW_TABLE_SUFFIX;  # with new prefix

    my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($oldTableName, 'fields');
    my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;

    &IT::DB::Schema::createTable($TARGET_CONNECTION_NAME, $newTableName, $schemaFieldDefs);
    &IT::DB::createTable($TARGET_CONNECTION_NAME, $newTableName, \@dbFieldDefs);

    # copy table attribute fields
    foreach my $attributeName (keys %VALID_TABLE_ATTRIBUTES) {
      if ($attributeName eq 'name')           { next; }
      if ($attributeName eq 'connectionName') { next; }
      if ($attributeName eq 'fields')         { next; }

      my $attributeValue = &IT::DB::Schema::getTableAttributes($oldTableName, $attributeName);
      &IT::DB::Schema::setTableAttribute($newTableName, $attributeName, $attributeValue);
    }


  }

  # Save Schema
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);

}

# ------------------------------------------------------------------------------
# Function    : _copyRecordsToTargetTable
# Description :
# Usage       : &_copyRecordsToTargetTable($currentTable, $targetTable, $moveRangeOffset)
# ------------------------------------------------------------------------------

sub _copyRecordsToTargetTable {
  my($currentTable, $targetTable, $moveRangeOffset) = @_;

  # load records from current table
  my $records = &IT::DB::select({
    'tableName'  => $currentTable,
    'sortBy'     => ['num'],
    'limit'      => [$moveRangeOffset, $RECORDS_TO_MOVE_AT_ONCE],
  });

  my $recordCount = @$records;

  # insert them into target table
  &IT::DB::disableAutoSequencing($targetTable);
  &IT::DB::insert($targetTable, $records);
  &IT::DB::enableAutoSequencing($targetTable);

}

# ------------------------------------------------------------------------------
# Function    : _switchToTargetConnection
# Description :
# Usage       :
# ------------------------------------------------------------------------------

sub _switchToTargetConnection {

  my @currentTables = &IT::DB::Schema::listTablesOnConnection($CURRENT_CONNECTION_NAME);
  my @targetTables  = &IT::DB::Schema::listTablesOnConnection($TARGET_CONNECTION_NAME);

  # check that old and new tables have the same record counts
  foreach my $index (0..$#currentTables) {
    my $currentTableName = $currentTables[$index];
    my $targetTableName  = $targetTables[$index];

    my $currentRecordCount = &IT::DB::count( $currentTableName );
    my $targetRecordCount = &IT::DB::count( $targetTableName );

    if ($currentTableName != $targetTableName) {
      my $errors = "There was an error moving records.  The current and new databases have different record counts:\n";
      $errors   .= "$currentTableName (records: $currentRecordCount), $targetTableName (records: $targetRecordCount)\n";
      $errors   .= "The move operation has been aborted and your current database and tables haven't been changed!\n";
      die $errors;
    }
  }

  # remove old tables
  foreach my $tableName (@currentTables) {
    &IT::DB::Schema::dropTable($tableName);
    &IT::DB::dropTable($CURRENT_CONNECTION_NAME, $tableName);
  }

  # remove old connection
  &IT::DB::Schema::dropConnection($CURRENT_CONNECTION_NAME);

  # rename new tables (remove suffix)
  foreach my $tableName (@targetTables) {
    my $tableNameWithoutSuffix = $tableName;
    $tableNameWithoutSuffix =~ s/$NEW_TABLE_SUFFIX$//;  # remove suffix
    &IT::DB::Schema::renameTable($tableName, $tableNameWithoutSuffix);
    &IT::DB::renameTable($TARGET_CONNECTION_NAME, $tableName, $tableNameWithoutSuffix);
  }

  # rename new connection (automatically updates connectionName for all tables on this connection)
  &IT::DB::Schema::renameConnection($TARGET_CONNECTION_NAME, $CURRENT_CONNECTION_NAME);

  # save schema
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);

  # update TABLE_PREFIX
  my $tablePrefix = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');
  &IT::App::Menu::Admin::updateTablePrefix($tablePrefix);

}

# ----------------------------------------------------------------------------
# Function    : _addStatusMessage
# Description :
# Usage       : &_addStatusMessage("message");
# ----------------------------------------------------------------------------

sub _addStatusMessage {
  my $message          = shift;
  my $jsEncodedMessage = &IT::Common::javascriptEncode("'", $message);

  print "\n<script>addStatusMessage('$jsEncodedMessage')</script>\n";
}

# ----------------------------------------------------------------------------
# Function    : _resubmitFormWithPost
# Description :
# Usage       : &_resubmitFormWithPost();
# ----------------------------------------------------------------------------

sub _resubmitFormWithPost {
  print "\n<script>submitForm()</script>\n";
}

# ----------------------------------------------------------------------------
# end of file
1;

