#!/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;

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

use strict;

# ----------------------------------------------------------------------------
# Function    : _getTablePrefixErrors
# Description : Return table prefix errors, if any.
#               Note: Table prefixes are REQUIRED!
# Usage       : $errors .= &_getTablePrefixErrors($tablePrefix);
# ----------------------------------------------------------------------------

sub _getTablePrefixErrors {
  my $tablePrefix = shift;

  my $errors;
  if (!$tablePrefix)                                                   { return "No Table Prefix specified!\n"; };
  if ($tablePrefix =~ /[^a-z0-9_]/)                                    { $errors .= "Table prefix can only contain the following characters (a-z, 0-9, _)\n"; }
  if (length $tablePrefix > $IT::DB::Globals::MAX_TABLE_PREFIX_LENGTH) { $errors .= "Table prefix cannot exceed $IT::DB::Globals::MAX_TABLE_PREFIX_LENGTH characters\n"; }
  if ($tablePrefix !~ /_$/)                                            { $errors .= "Table prefix must end with _\n"; }

  return $errors;
}

# ------------------------------------------------------------------------------
# Function    : _getSelectDatabaseFieldErrors
# Description :
# Usage       : $errors .= &_getSelectDatabaseFieldErrors();
# ------------------------------------------------------------------------------

sub _getSelectDatabaseFieldErrors {
  my $errors;

  if    (!$FORM->{'engineType'})          { return "No database engine type specified!\n"; }

  if ($FORM->{'engineType'} eq 'file') {
    $errors .= &_getTablePrefixErrors( $FORM->{'fileTablePrefix'} );
  }
  elsif ($FORM->{'engineType'} eq 'mysql') {
    $errors .= &_getTablePrefixErrors( $FORM->{'mysqlTablePrefix'} );
    unless ($FORM->{'mysqlHostname'}) { $errors .= "No mysql hostname specified!\n"; }
    unless ($FORM->{'mysqlDatabase'}) { $errors .= "No mysql database name specified!\n"; }
  }
  else                                { $errors .= "Invalid engine type '$FORM->{'engineType'}'!"; }

  return $errors;

}

# ------------------------------------------------------------------------------
# Function    : _getConnectionErrors
# Description :
# Usage       : $errors .= &_getConnectionErrors($connectionName);
# ------------------------------------------------------------------------------

sub _getConnectionErrors {
  my $connectionName = shift || die "no connectionName specified!";

  # call a DB function that connects to mySQL
  eval { &IT::DB::listTables($connectionName); };
  my $errors = $@;

  return $errors;

}

# ------------------------------------------------------------------------------
# Function    : _createNewTargetConnectionFromInput
# Description : Create a new target database connection from "Select Database" form input.
# Usage       : &_createNewTargetConnectionFromInput();
# ------------------------------------------------------------------------------

sub _createNewTargetConnectionFromInput {
  if (@_) { die "Function doesn't take any arguments!"; }

  my $newConnectionRef = {};
  $newConnectionRef->{'name'}           = $TARGET_CONNECTION_NAME;
  $newConnectionRef->{'engine'}         = $FORM->{'engineType'};

  if    ($FORM->{'engineType'} eq 'file') {
    $newConnectionRef->{'appTablePrefix'} = $FORM->{'fileTablePrefix'};
  }
  elsif ($FORM->{'engineType'} eq 'mysql') {
    $newConnectionRef->{'hostname'}       = $FORM->{'mysqlHostname'};
    $newConnectionRef->{'port'}           = $FORM->{'mysqlPort'};
    $newConnectionRef->{'database'}       = $FORM->{'mysqlDatabase'};
    $newConnectionRef->{'username'}       = $FORM->{'mysqlUsername'};
    $newConnectionRef->{'password'}       = $FORM->{'mysqlPassword'};
    $newConnectionRef->{'appTablePrefix'} = $FORM->{'mysqlTablePrefix'};
  }
  else { die "unknown engine type '$FORM->{'engineType'}'!"; }

  # erase any old target connections that are in the way
  if (&IT::DB::Schema::existsConnection($TARGET_CONNECTION_NAME)) {
    &IT::DB::Schema::dropConnection($TARGET_CONNECTION_NAME);
  }

  &IT::DB::Schema::createConnection($newConnectionRef);

  return $newConnectionRef->{'name'};
}

# ----------------------------------------------------------------------------
# Function    : _getTableListWithDifferentPrefixes
# Description : Return a list of schema tables with a different table prefixes
# Usage       : @newTableNames = &_getTableListWithDifferentPrefixes($connectionName, $newPrefix);
# ----------------------------------------------------------------------------

sub _getTableListWithDifferentPrefixes {
  if (@_ != 2)         { die "Function requires TWO arguments!"; }
  my $connectionName = shift || die "no connection name specified!";
  my $newPrefix      = shift;
  my $oldPrefix      = &IT::DB::Schema::getConnectionAttributes($connectionName, 'appTablePrefix');
  my @oldTableNames  = &IT::DB::Schema::listTablesOnConnection($connectionName);
  my @newTableNames;

  foreach my $oldTableName (@oldTableNames) {
    my $newTableName = $oldTableName;
    $newTableName =~ s/^$oldPrefix// || die "table name '$oldTableName' didn't start with prefix '$oldPrefix'!";
    $newTableName = $newPrefix . $newTableName;
    push @newTableNames, $newTableName;
  }

  return @newTableNames;
}

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

sub noDatabaseActionSelected {
  &IT::App::alert("No database action was selected!");
  &IT::App::Menu::Admin::DatabaseEditor::TableEditor::listTables();
}

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

sub showDatabaseActionForm {
  my $actionName   = shift || die "no database action defined!";
  my $errorMessage = shift;
  if ($errorMessage) { &IT::App::alert($errorMessage); }

  # define vars
  my($templateCell, $formDescription, $saveAction);
  my $template       = "admin/menus/admin/databaseActions.html";
  if    ($actionName eq 'changeTablePrefix') {
    $templateCell    = 'editTablePrefix';
    $formDescription = 'Change table name prefix';
    $saveAction      = 'databaseEditor_saveTablePrefix';
  }
  elsif ($actionName eq 'moveData') {
    die "moveData form is displayed by IT::App::Menu::Admin::DatabaseEditor::moveDataForm();";
  }
  elsif ($actionName eq 'changeDataSource') {
    $templateCell    = 'databaseActionForm';
    $formDescription = "Use data from a different database";
    $saveAction      = 'databaseEditor_saveDataSourceLocation';
  }
  elsif ($actionName eq 'createNewTables') {
    $templateCell    = 'databaseActionForm';
    $formDescription = "Create New Tables (start over)";
    $saveAction      = 'databaseEditor_createNewTables';
  }
  else { die "unknown actionName '$actionName'"; }


  # set global placeholders
  &IT::Template::setGlobalPlaceholder('menuGroup',       'databaseEditor');
  &IT::Template::setGlobalPlaceholder('formDescription', $formDescription);
  &IT::Template::setGlobalPlaceholder('saveAction',      $saveAction);

  ### set placeholders
  my $engineType      = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'engine');
  my $appTablePrefix  = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');
  my $fileTablePrefix = ($engineType eq 'file')   ? $appTablePrefix : '';
  my $mysqlTablePrefix = ($engineType eq 'mysql') ? $appTablePrefix : '';
  my $pagePlaceholders;

  # for changePrefix
  $pagePlaceholders->{'oldTablePrefix'}               = $appTablePrefix;
  $pagePlaceholders->{'appTablePrefix'}               = &firstDefinedValue( $FORM->{'appTablePrefix'}, $appTablePrefix );
  $pagePlaceholders->{'databaseType'}                 = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'engine');

  # for all other forms
  $pagePlaceholders->{'engineType'}                     = &firstDefinedValue( $FORM->{'engineType'}, $engineType);
  $pagePlaceholders->{'datadir'}                        = $DATADIR;
  $pagePlaceholders->{'fileTablePrefix'}                = &firstDefinedValue( $FORM->{'fileTablePrefix'}, $fileTablePrefix );
  my($hostname, $port, $database, $username, $password) = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'hostname', 'port', 'database', 'username', 'password');
  $pagePlaceholders->{'mysqlHostname'}                  = &firstDefinedValue( $FORM->{'mysqlHostname'},    $hostname, 'localhost' );
  $pagePlaceholders->{'mysqlPort'}                      = &firstDefinedValue( $FORM->{'mysqlPort'},        $port );
  $pagePlaceholders->{'mysqlDatabase'}                  = &firstDefinedValue( $FORM->{'mysqlDatabase'},    $database );
  $pagePlaceholders->{'mysqlUsername'}                  = &firstDefinedValue( $FORM->{'mysqlUsername'},    $username );
  $pagePlaceholders->{'mysqlPassword'}                  = &firstDefinedValue( $FORM->{'mysqlPassword'},    $password );
  $pagePlaceholders->{'mysqlTablePrefix'}               = &firstDefinedValue( $FORM->{'mysqlTablePrefix'}, $mysqlTablePrefix );

  ### display page
  &IT::App::displayInterface({
    'template'           => $template,
    'templatecell'       => $templateCell,
    'selectedMenuOption' => 'admin',
    'menuGroup'          => 'databaseEditor',
    'placeholders'       => $pagePlaceholders,
  });
}

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

sub saveTablePrefix {

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

  # get old table prefix
  my $oldTablePrefix = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');

  # don't save if prefix hasn't changed
  if ($FORM->{'appTablePrefix'} eq $oldTablePrefix) {
    &IT::App::Menu::Admin::DatabaseEditor::TableEditor::listTables();
    exit;
  }

  # error checking
  my $errors .= &_getTablePrefixErrors( $FORM->{'appTablePrefix'} );
  if ($errors) { &showDatabaseActionForm('changeTablePrefix', $errors); }

  ### rename tables
  &IT::FileLock::getLock('exclusive');

  # create hash of old names to new names
  my @oldTableNames      = &IT::DB::Schema::listTables(); # get new table list in case it changed before we got an exclusive lock
  my @newTableNames      = &_getTableListWithDifferentPrefixes($CURRENT_CONNECTION_NAME, $FORM->{'appTablePrefix'});
  my %oldNamesToNewNames = map { $oldTableNames[$_] => $newTableNames[$_] } (0..$#oldTableNames);

  # check new table names don't already exist
  my $newTablesThatAlreadyExist;
  foreach my $newTableName (@newTableNames) {
    if (&IT::DB::existsTable($CURRENT_CONNECTION_NAME, $newTableName)) { $newTablesThatAlreadyExist .= "$newTableName\n" }
  }
  if ($newTablesThatAlreadyExist) {
    &showDatabaseActionForm('changeTablePrefix', "Some database tables already exist with that prefix:\n$newTablesThatAlreadyExist");
  }

  # rename tables
  foreach my $oldTableName (keys %oldNamesToNewNames) {
    my $newTableName = $oldNamesToNewNames{$oldTableName};
    &IT::DB::renameTable($CURRENT_CONNECTION_NAME, $oldTableName, $newTableName);
    &IT::DB::Schema::renameTable($oldTableName, $newTableName);
  }

  # update connection in schema
  &IT::DB::Schema::setConnectionAttribute($CURRENT_CONNECTION_NAME, 'appTablePrefix', $FORM->{'appTablePrefix'});

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

  # update TABLE_PREFIX
  my $newTablePrefix = $FORM->{'appTablePrefix'};
  &IT::App::Menu::Admin::updateTablePrefix($newTablePrefix);

  &IT::FileLock::releaseLock();

  &IT::App::Menu::Admin::DatabaseEditor::TableEditor::listTables();
}

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

sub saveDataSourceLocation {
  my $oldConnectionName = $CURRENT_CONNECTION_NAME;

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

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

  # Error checking
  my $inputErrors = &_getSelectDatabaseFieldErrors();
  if ($inputErrors) { &showDatabaseActionForm('changeDataSource', $inputErrors); }

  # create new connection
  &_createNewTargetConnectionFromInput();
  my $connectionErrors  = &_getConnectionErrors($TARGET_CONNECTION_NAME);
  if ($connectionErrors) { &showDatabaseActionForm('changeDataSource', $connectionErrors); }

  # check for missing tables on new database location
  my $formFieldName  = $FORM->{'engineType'} . 'TablePrefix';
  my $newTablePrefix = $FORM->{ $formFieldName };
  my @requiredTables = &_getTableListWithDifferentPrefixes($oldConnectionName, $newTablePrefix);
  my %newlistTables   = map { $_, 1 } &IT::DB::listTables($TARGET_CONNECTION_NAME);
  my @missingTables = grep { not exists $newlistTables{$_} } @requiredTables;
  my $missingTables = join "\n", @missingTables;
  if ($#missingTables == $#requiredTables) { &showDatabaseActionForm('changeDataSource', "None of the required program tables could be found on the new database connection!"); }
  if ($missingTables)                      { &showDatabaseActionForm('changeDataSource', "Some of the required program tables couldn't be found on the new database connection:\n$missingTables"); }

  # update all tables in schema with new tablePrefix
  my @oldTableNames = &IT::DB::Schema::listTables();
  my @newTableNames = @requiredTables;
  for my $index (0..$#oldTableNames) {
    my $oldTableName = $oldTableNames[$index];
    my $newTableName = $newTableNames[$index];

    &IT::DB::Schema::renameTable($oldTableName, $newTableName) unless ($oldTableName eq $newTableName);
  }

  # Replace old connection with new connection
  &IT::DB::Schema::replaceConnection($CURRENT_CONNECTION_NAME, $TARGET_CONNECTION_NAME);

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

  # update TABLE_PREFIX
  &IT::App::Menu::Admin::updateTablePrefix($newTablePrefix);

  &IT::FileLock::releaseLock();

  # return to list page
  &IT::App::Menu::Admin::DatabaseEditor::TableEditor::listTables();

}

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

sub createNewTables {
  my $oldConnectionName = $CURRENT_CONNECTION_NAME;

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

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

  # Error checking
  my $inputErrors = &_getSelectDatabaseFieldErrors();
  if ($inputErrors) { &showDatabaseActionForm('createNewTables', $inputErrors); }

  # create new connection
  &_createNewTargetConnectionFromInput();
  my $connectionErrors = &_getConnectionErrors($TARGET_CONNECTION_NAME);
  if ($connectionErrors) { &showDatabaseActionForm('createNewTables', $connectionErrors); }

  # check for pre-existing tables on new database location
  my $formFieldName  = $FORM->{'engineType'} . 'TablePrefix';
  my $newTablePrefix = $FORM->{ $formFieldName };

  my @targetTables   = &_getTableListWithDifferentPrefixes($oldConnectionName, $newTablePrefix);
  my %existingTables = map { $_, 1 } &IT::DB::listTables($TARGET_CONNECTION_NAME);
  my @tablesInTheWay = grep { exists $existingTables{$_} } @targetTables;

  if (@tablesInTheWay) {
    my $errors = "";
    $errors .= "The new database already has some tables with the same name as the program tables:\n";
    $errors .= join(", ", @tablesInTheWay) . "\n\n";
    $errors .= "Try using a different table prefix.";
    &showDatabaseActionForm('createNewTables', $errors);
  }

  # load current user record
  my $currentUserName = $CURRENT_USER->{'username'} || die "Can't get current user name!";
  my $records = &IT::DB::select({
    'tableName'  => $TABLE_PREFIX . 'accounts',
    'whereQuery' => "username = '$currentUserName'",
    'limit'      => [0,1],
    });
  my $currentUserRecord = $records->[0];

  # update schema and create new tables
  my @currentTables = &IT::DB::Schema::listTables();
  for my $index (0..$#currentTables) {
    my $oldTableName = $currentTables[$index];
    my $newTableName = $targetTables[$index];

    # change table prefix in schema
    &IT::DB::Schema::renameTable($oldTableName, $newTableName) unless ($oldTableName eq $newTableName);

    # create table in database
    my($schemaFieldDefs) = &IT::DB::Schema::getTableAttributes($newTableName, 'fields');
    my @dbFieldDefs      = grep { $_->{'type'} ne 'none' } @$schemaFieldDefs;
    &IT::DB::createTable($TARGET_CONNECTION_NAME, $newTableName, \@dbFieldDefs);
  }

  # Replace old connection with new connection
  &IT::DB::Schema::replaceConnection($CURRENT_CONNECTION_NAME, $TARGET_CONNECTION_NAME);

  # create account for current user in new database
  my $accountsTable = $newTablePrefix."accounts";
  &IT::DB::disableAutoSequencing($accountsTable);
  &IT::DB::insert($accountsTable, [$currentUserRecord]);
  &IT::DB::enableAutoSequencing($accountsTable);

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

  # update TABLE_PREFIX
  &IT::App::Menu::Admin::updateTablePrefix($newTablePrefix);

  &IT::FileLock::releaseLock();

  # return to list page
  &IT::App::alert("Please note: Your user account has been copied to the new account database so you can continue to login and access the program.  <b>No other user accounts were copied.</b>", "keepHTML");
  &IT::App::Menu::Admin::DatabaseEditor::TableEditor::listTables();
}

# ----------------------------------------------------------------------------
# Function    : insertFakeRecords
# Description : insert fake records in tables for testing purposes.  New records
#               are added after any existing records in table
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::DatabaseActions::insertFakeRecords();
# ----------------------------------------------------------------------------

sub insertFakeRecords {

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

  my $recordCount = int($FORM->{'records'}) || 500;
  &IT::FileLock::getLock('exclusive');

  my @tableNames  = &IT::DB::Schema::listTablesOnConnection($CURRENT_CONNECTION_NAME);
  foreach my $tableName (@tableNames) {
    if ($tableName !~ /article/) { next; }
    my $highestRecordNum = &IT::DB::getSequence($tableName);

    # create array of new records
    my @newRecords;
    for (1..$recordCount) {
      $highestRecordNum++;

      my $newRecord = {
        'title'    => "$highestRecordNum in $tableName",
        'summary' => "summary: orem ipsum dolor sit amet consectetur adipisicing elit",
        'content' => "content: sed do eiusmod tempor incididunt ut labore et dolore magna aliqua",
      };

      push @newRecords, $newRecord;
    }

    # insert new records all at once
    &IT::DB::insert($tableName, \@newRecords);
  }

  &IT::FileLock::releaseLock();


  &IT::Common::redirectBrowserToURL("$CGIURL?action=databaseEditor");
}

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

