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

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

use strict;

# ----------------------------------------------------------------------------
# Function    : listTables
# Description : display a list of tables from the schema
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::listTables();
# ----------------------------------------------------------------------------

sub listTables {
  my $errorMessage = shift;
  if ($errorMessage) { &IT::App::alert($errorMessage); }

  my $template = "admin/menus/admin/databaseTableEditor.html";
  my $pagePlaceholders = {};
  my @listTables = &IT::DB::Schema::listTables();


  # set placeholder globals
  &IT::Template::setGlobalPlaceholder('menuGroup', 'databaseEditor');
  &IT::Template::setGlobalPlaceholder('_menuName', 'databaseEditor');

  ### set connection editor placeholders
  &IT::Template::setGlobalPlaceholder('databaseType', &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'engine'));
  &IT::Template::setGlobalPlaceholder('appTablePrefix', &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix'));

  ### generate table list
  my $rowCount = 0;
  &IT::FileLock::getLock('shared'); # required for IT::DB::count
  foreach my $tableName (@listTables) {
    my $tableConnectionName = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
    if ($tableConnectionName ne $CURRENT_CONNECTION_NAME) { next; }  # skip targetConnection tables, if any

    my $rowPlaceholders = {
      tableName      => $tableName,
      recordCount    => &IT::DB::count($tableName),
      description    => &IT::DB::Schema::getTableAttributes($tableName, 'appTableDescription'),
      _bgColorClass   => (++$rowCount % 2) ? 'listRowOdd' : 'listRowEven',
    };
    $pagePlaceholders->{'tableRows'} .= &templateCell($template, 'tableRow', $rowPlaceholders);
  }
  &IT::FileLock::releaseLock();


  ### display "not found" if no results
  $pagePlaceholders->{'rows'} ||= &templateCell($template, 'noTablesFound', {});


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


# ----------------------------------------------------------------------------
# Function    : addTableForm
# Description : show table details and field list
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::TableEditor::addTableForm();
# ----------------------------------------------------------------------------

sub addTableForm {
  my $errorMessage    = shift;
  if ($errorMessage) { &IT::App::alert($errorMessage); }
  my $template        = "admin/menus/admin/databaseTableEditor.html";
  my $appTablePrefix  = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');

  # set placeholders
  my $pagePlaceholders = {};
  $pagePlaceholders->{'menuGroup'}                 = 'databaseEditor';
  $pagePlaceholders->{'connectionName'}            = $CURRENT_CONNECTION_NAME;
  $pagePlaceholders->{'databaseType'}              = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'engine');
  $pagePlaceholders->{'appTablePrefix'}            = $appTablePrefix;
  $pagePlaceholders->{'tablename'}                 = &firstDefinedValue($FORM->{'tablename'}, $appTablePrefix);
  $pagePlaceholders->{'appTableDescription'}       = &firstDefinedValue($FORM->{'appTableDescription'},       '');


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


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

sub addTableSubmit {

  my $appTablePrefix  = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');

  # error checking
  my $errors;
  if ($FORM->{'tablename'} eq '')                 { $errors .= "You must enter a value for 'Table Name'\n"; }
  if ($FORM->{'appTableDescription'} eq '')       { $errors .= "You must enter a value for 'Description'\n"; }
  if ($FORM->{'tablename'} !~ /^$appTablePrefix/) { $errors .= "Table name must start with table prefix '$appTablePrefix'!\n"; }
  if ($errors) { &addTableForm($errors); }

  # create new table
  my $newTableName    = $FORM->{'tablename'};
  my $schemaFieldDefs = [
    {
      'appDisplayOrder'    => '1',
      'name'               => 'num',
      'type'               => 'int',
      'appFieldLabel'      => 'Record Number',
      'appFieldType'       => 'none',
      'appHiddenField'     => '1',
    },
    {
      'appDisplayOrder' => '2',
      'name'            => 'createdTime',
      'type'            => 'int',
      'appFieldLabel'   => 'Created',
      'appFieldType'    => 'date',
      'appDisplayOnly'  => '1',
      'appFieldOptions' => {
        'useCustomFieldHtml' => '1',
        'customFieldHtml'    => "<tr>\n  <td>Created</td>\n  <td>\$createdTime\$ (by \$createdByUserNum:getFieldValueByNum(accounts, username, Unknown User)\$)</td>\n</tr>\n",
      },
    },
    {
      'appDisplayOrder' => '3',
      'name'            => 'createdByUserNum',
      'type'            => 'int',
      'appFieldLabel'   => 'Created By',
      'appFieldType'    => 'none',
      'appHiddenField'  => '1',
    },
    {
      'appDisplayOrder' => '4',
      'name'            => 'lastUpdatedTime',
      'type'            => 'int',
      'appFieldLabel'   => 'Last Updated',
      'appFieldType'    => 'date',
      'appDisplayOnly'  => '1',
      'appFieldOptions' => {
        'useCustomFieldHtml' => '1',
        'customFieldHtml'    => "<tr>\n  <td>Last Updated</td>\n  <td>\$lastUpdatedTime\$ (by \$lastUpdatedByUserNum:getFieldValueByNum(accounts, username, Unknown User)\$)</td>\n</tr>\n",
      },
    },
    {
      'appDisplayOrder' => '5',
      'name'            => 'lastUpdatedByUserNum',
      'type'            => 'int',
      'appFieldLabel'   => 'Last Updated By',
      'appFieldType'    => 'none',
      'appHiddenField'  => '1',
    },
  ];
  &IT::FileLock::getLock('exclusive');
  eval {
    &IT::DB::Schema::createTable($CURRENT_CONNECTION_NAME, $newTableName, $schemaFieldDefs);
    &IT::DB::Schema::setTableAttribute($newTableName, 'appTableDescription', $FORM->{'appTableDescription'});
    &IT::DB::createTable($CURRENT_CONNECTION_NAME, $newTableName, $schemaFieldDefs);
    &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
  };
  if ($@) {
    my $evalError = $@;
    $evalError =~ s/\n.*$//si;            # remove lines after first (removes stacktrace)
    $evalError =~ s/ at \S+ line.*$//si;  # remove perl "died at /path/.../script.cgi line 123" message
    &addTableForm($evalError);
  };
  &IT::FileLock::releaseLock();

  # list tables
  &listTables();
  exit;

}



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

sub confirmEraseTables_form {
  my $errorMessage = shift;
  if ($errorMessage) { &IT::App::alert($errorMessage); }
  my $template     = "admin/menus/admin/databaseTableEditor.html";

  # get selected tables
  my $selectedTableNames = $FORM_ARRAYS->{'selectedTables'} || [];
  if (@$selectedTableNames == 0) {
    &listTables("No tables were selected!");
  }

  ### set placeholders
  my $pagePlaceholders = {};
  $pagePlaceholders->{'menuGroup'}         = 'databaseEditor';
  $pagePlaceholders->{'_menuName'}         = 'databaseEditor';
  $pagePlaceholders->{'_confirmEraseRows'} = '';
  $pagePlaceholders->{'totalRecordCount'}  = 0;

  # display row for each table to erase
  &IT::FileLock::getLock('shared'); # required for IT::DB::count
  my $rowCount = 0;
  foreach my $tableName (@$selectedTableNames) {
    my $rowPlaceholders = {};
    $rowPlaceholders->{'tablename'}     = $tableName;
    $rowPlaceholders->{'description'}   = &IT::DB::Schema::getTableAttributes($tableName, 'appTableDescription');
    $rowPlaceholders->{'recordCount'}   = &IT::DB::count($tableName);
    $rowPlaceholders->{'_bgColorClass'} = (++$rowCount % 2) ? 'listRowOdd' : 'listRowEven',

    $pagePlaceholders->{'_confirmEraseRows'} .= &templateCell($template, 'confirmEraseRow', $rowPlaceholders);
    $pagePlaceholders->{'totalRecordCount'}  += $rowPlaceholders->{'recordCount'};
  }
  &IT::FileLock::releaseLock();


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


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

sub confirmEraseTables_submit {

  # get selected tables
  my $selectedTableNames = $FORM_ARRAYS->{'selectedTables'} || [];
  if (@$selectedTableNames == 0) {
    &listTables("No tables were selected!");
  }

  # erase tables
  &IT::FileLock::getLock('exclusive');
  foreach my $tableName (@$selectedTableNames) {
    &IT::DB::dropTable($CURRENT_CONNECTION_NAME, $tableName) if &IT::DB::existsTable($CURRENT_CONNECTION_NAME, $tableName);
    &IT::DB::Schema::dropTable($tableName);
  };
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
  &IT::FileLock::releaseLock();

  # list remaining tables
  &listTables();
  exit;
}

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

sub renameTable_form {
  my $errorMessage   = shift;
  if ($errorMessage) { &IT::App::alert($errorMessage); }
  my $template       = "admin/menus/admin/databaseTableEditor.html";
  my $appTablePrefix = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');

  # get selected tables
  my $selectedTableNames = $FORM_ARRAYS->{'selectedTables'} || [];
  if (@$selectedTableNames == 0) { &listTables("No table was selected to rename!");  }
  if (@$selectedTableNames > 1)  { &listTables("You can only rename one table at a time!");  }

  # set placeholders
  my $pagePlaceholders = {};
  $pagePlaceholders->{'menuGroup'}                 = 'databaseEditor';
  $pagePlaceholders->{'connectionName'}            = $CURRENT_CONNECTION_NAME;
  $pagePlaceholders->{'databaseType'}              = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'engine');
  $pagePlaceholders->{'appTablePrefix'}            = $appTablePrefix;
  $pagePlaceholders->{'selectedTables'}            = $FORM->{'selectedTables'};
  $pagePlaceholders->{'newTableName'}              = &firstDefinedValue($FORM->{'newTableName'}, $FORM->{'selectedTables'});
  $pagePlaceholders->{'appTableDescription'}       = &firstDefinedValue($FORM->{'appTableDescription'},       '');

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


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

sub renameTable_submit {

  my $appTablePrefix  = &IT::DB::Schema::getConnectionAttributes($CURRENT_CONNECTION_NAME, 'appTablePrefix');

  # error checking
  my $errors;
  if ($FORM->{'newTableName'} eq '')                 { $errors .= "You must enter a new table name!\n"; }
  if ($FORM->{'newTableName'} !~ /^$appTablePrefix/) { $errors .= "New table name must start with table prefix '$appTablePrefix'!\n"; }
  if ($errors) { &renameTable_form($errors); }

  # rename table
  my $oldTableName = $FORM->{'selectedTables'};
  my $newTableName = $FORM->{'newTableName'};
  &IT::FileLock::getLock('exclusive');
  eval {
    &IT::DB::Schema::renameTable($oldTableName, $newTableName);
    &IT::DB::renameTable($CURRENT_CONNECTION_NAME, $oldTableName, $newTableName);
    &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
  };
  if ($@) {
    my $evalError = $@;
    $evalError =~ s/\n.*$//si;            # remove lines after first (removes stacktrace)
    $evalError =~ s/ at \S+ line.*$//si;  # remove perl "died at /path/.../script.cgi line 123" message
    &renameTable_form($evalError);
  };
  &IT::FileLock::releaseLock();

  # list tables
  &listTables();
  exit;

}


# ----------------------------------------------------------------------------
# Function    : editTable
# Description : show table details and field list
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::editTable();
# ----------------------------------------------------------------------------

sub editTable {
  my $errorMessage = shift;
  if ($errorMessage) { &IT::App::alert($errorMessage); }
  my $tableName        = $FORM->{'tableName'} || die "no tablename specified!";
  my $template         = "admin/menus/admin/databaseTableEditor.html";

  ### error checking
  unless (&IT::DB::Schema::existsTable($tableName)) {
    &IT::App::alert("Can't find table: $tableName");
    &listTables();
  }

  # set placeholder globals
  &IT::Template::setGlobalPlaceholder('menuGroup', 'databaseEditor');
  &IT::Template::setGlobalPlaceholder('tableName', $tableName);

  ### load table data
  my ($connectionName)          = &IT::DB::Schema::getTableAttributes($tableName, 'connectionName');
  my $defaultSort               = &IT::DB::Schema::getTableAttributes($tableName, 'defaultSort') || [];
  my $defaultSortString         = join(', ', @$defaultSort);;
  my $defaultSearchFields       = &IT::DB::Schema::getTableAttributes($tableName, 'appDefaultSearchFields') || [];
  my $defaultSearchFieldsString = join(', ', @$defaultSearchFields);

  my $pagePlaceholders = {};
  $pagePlaceholders->{'connectionName'}            = $connectionName;
  $pagePlaceholders->{'databaseType'}              = &IT::DB::Schema::getConnectionAttributes($connectionName, 'engine');
  $pagePlaceholders->{'appTablePrefix'}            = &IT::DB::Schema::getConnectionAttributes($connectionName, 'appTablePrefix');
  $pagePlaceholders->{'appTableDescription'}       = &firstDefinedValue($FORM->{'appTableDescription'}, &IT::DB::Schema::getTableAttributes($tableName, 'appTableDescription'));
  $pagePlaceholders->{'defaultSortString'}         = &firstDefinedValue($FORM->{'defaultSortString'}, $defaultSortString);
  $pagePlaceholders->{'defaultSearchFieldsString'} = &firstDefinedValue($FORM->{'defaultSearchFieldsString'}, $defaultSearchFieldsString);

  &IT::FileLock::getLock('shared'); # required for IT::DB::count
  $pagePlaceholders->{'recordCount'} = &IT::DB::count($tableName);
  &IT::FileLock::releaseLock();

  ### generate field list html
  &IT::FileLock::getLock('shared'); # required for IT::DB::count
  my $appDisplayOrder  = 0;
  my $rowCount         = 0;
  my @sortedFieldNames = &IT::App::getUserSortedFieldNames($tableName, 'INCLUDE_SCHEMA_ONLY_FIELDS');
  foreach my $fieldName (@sortedFieldNames) {
    my($dbFieldType, $userEditable, $appFieldType, $appDisplayOnly, $appHiddenField, $appFieldLabel, $appFieldSeparator) = &IT::DB::Schema::getFieldAttributes($tableName, $fieldName, 'type','appUserEditable','appFieldType', 'appDisplayOnly', 'appHiddenField', 'appFieldLabel', 'appFieldSeparator');
    $appDisplayOrder += 10;

    my $rowPlaceholders = {
      appDisplayOrder     => $appDisplayOrder,
      fieldName           => $fieldName,
      appFieldLabel       => $appFieldLabel,
      dbFieldType         => $dbFieldType,
      appFieldType        => $appFieldType,
      appDisplayOnly      => $appDisplayOnly,
      appHiddenField      => $appHiddenField,
      isSystemField       => int !($userEditable),
      _bgColorClass       => (++$rowCount % 2) ? 'listRowOdd' : 'listRowEven',
      separatorType       => $appFieldSeparator->{'separatorType'},
      separatorHeaderText => $appFieldSeparator->{'separatorHeaderText'},

    };

    # add row
    my $templateCell = 'fieldRow';
    $pagePlaceholders->{'fieldRows'} .= &templateCell($template, $templateCell, $rowPlaceholders);
  }
  &IT::FileLock::releaseLock();


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

# ----------------------------------------------------------------------------
# Function    : saveTable
# Description : update field details and field order
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::saveTable($doUpdateFieldOrder);
# ----------------------------------------------------------------------------

sub saveTable {
  my $doUpdateFieldOrder = shift;
  my $tableName        = $FORM->{'tableName'} || die "no tablename specified!";
  my $template         = "admin/menus/admin/databaseTableEditor.html";

  my %validFieldNames        = map { $_ => 1 } &IT::DB::Schema::getFieldNames($tableName);
  my @appDefaultSearchFields = split(/,\s*/, $FORM->{'defaultSearchFieldsString'});
  my @defaultSortArray       = split(/,\s*/, $FORM->{'defaultSortString'});
  my $defaultSortRef         = scalar(@defaultSortArray) ? \@defaultSortArray : undef;

  ### Error checking
  my $errors;
  foreach my $fieldName (@appDefaultSearchFields) {
    if (!$validFieldNames{$fieldName}) { $errors .= "Invalid fieldname '$fieldName' in default search fields!\n"; }
  }
  foreach my $fieldNameWithPrefix (@defaultSortArray) {
    my $fieldName = $fieldNameWithPrefix;
    $fieldName =~ s/^-//; # remove reverse sort order prefix
    if (!$validFieldNames{$fieldName}) { $errors .= "Invalid fieldname '$fieldName' in default sort fields!\n"; }
  }
  if ($errors) { &editTable($errors); }


  ### update description, search fields, and sort fields
  &IT::DB::Schema::setTableAttribute($tableName, 'appTableDescription',    $FORM->{'appTableDescription'});
  &IT::DB::Schema::setTableAttribute($tableName, 'appDefaultSearchFields', \@appDefaultSearchFields);
  &IT::DB::Schema::setTableAttribute($tableName, 'defaultSort', $defaultSortRef);

  ### update field display order values
  if ($doUpdateFieldOrder) {

    # get fields sorted by submitted form values
    my @unsortedFieldNames = &IT::DB::Schema::getFieldNames($tableName, 'INCLUDE_SCHEMA_ONLY_FIELDS');
    my @sortedFieldNames   = sort { $FORM->{$a.'_appDisplayOrder'} <=> $FORM->{$b.'_appDisplayOrder'} } @unsortedFieldNames;

    # re-number fields
    my $newAppDisplayOrder = 0;
    foreach my $fieldName (@sortedFieldNames) {
      $newAppDisplayOrder += 10;
      &IT::DB::Schema::setFieldAttribute($tableName, $fieldName, 'appDisplayOrder', $newAppDisplayOrder);
    }

    &IT::App::alert("Field order has been updated and re-numbered.");
  }

  ### save schema
  &IT::FileLock::getLock('exclusive');
  &IT::DB::Schema::saveSchema($SCHEMA_FILEPATH);
  &IT::FileLock::releaseLock();

  ### return to list page
  if ($doUpdateFieldOrder) { &editTable(); }
  else                     { &listTables(); }
}

# ----------------------------------------------------------------------------
# Function    : setSystemFieldEditingTo
# Description : update field details and field order
# Usage       : &IT::App::Menu::Admin::DatabaseEditor::TableEditor::setSystemFieldEditingTo('EnabledOrDisabled');
# ----------------------------------------------------------------------------

sub setSystemFieldEditingTo {
  my $enabledOrDisabled = shift;

  # update setting
  if    ($enabledOrDisabled eq 'enabled')  { $SETTINGS->{'allowSystemFieldEditing'} = 1; }
  elsif ($enabledOrDisabled eq 'disabled') { $SETTINGS->{'allowSystemFieldEditing'} = 0; }
  else {
    die "enabledOrDisabled must be 'enabled' or 'disabled' not '$enabledOrDisabled'!";
  }

  # update settings.* placeholders
  &IT::App::defineGlobalTemplatePlaceholders();

  # save setting
  &IT::App::Menu::Admin::saveAdminSettingsToFile($SETTINGS);


  # display list page
  &IT::App::alert("System field editing has been $enabledOrDisabled.");
  &editTable();
  exit;

}

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

