#!/usr/bin/php
<?php
//
// FAPWeb Database management utility
// (C) Copyright 2012-2017 Tecnic Software productions (TNSP)
//
if (!file_exists(realpath(dirname(__FILE__))."/mconfig.inc.php"))
{
  die(
    "ERROR: Missing site configuration file. Please copy ".
    "'mconfig.inc.php.example' to 'mconfig.inc.php' and ".
    "edit it as needed.\n");
}

require_once "mconfig.inc.php";
require_once "msite.inc.php";
require_once "dbdefs.inc.php";

stCheckCLIExec();


$currVersion = -1;
$dbMetaData = array(
  "dbVersion" => array(VT_INT, $dbVersion, "Database version"),
);

// Add database meta table into tables to be created
$sqlTables["dbmeta"] = array(
  array("key"          , "VARCHAR(32)", "PRIMARY KEY"),
  array("vtype"        , "INT"),
  array("vstr"         , "VARCHAR(128)"),
  array("vtext"        , "TEXT"),
  array("vint"         , "INT"),
  array("sdesc"        , "VARCHAR(128)"),
);



//
// Helper functions
//
function stCreateTables($dbh, $upgrade)
{
  global $sqlTables;
  echo "Creating tables...\n";
  foreach ($sqlTables as $name => $schemaData)
  {
    echo " - '".$name."'\n";
    stDBExecSQL($dbh, "DROP TABLE IF EXISTS ".$name);
    if (!stDBCreateOneTable($dbh, $name, stDBGetTableSchema($dbh, $schemaData)))
    {
      if (!$upgrade)
        return FALSE;
      else
        echo "\nFailed to create table '".$name."'\n";
    }
  }
  return TRUE;
}


function stUpgradeMap($type, $name)
{
  global $upgradeMappings, $currVersion, $dbVersion;

  $converted = FALSE;
  $tmpName = $name;

  for ($ver = $currVersion; $ver <= $dbVersion; $ver++)
  {
    if (isset($upgradeMappings[$ver]) &&
        isset($upgradeMappings[$ver][$tmpName]) &&
        isset($upgradeMappings[$ver][$tmpName][$type]))
    {
      $tmpName = $upgradeMappings[$ver][$tmpName][$type];
      $converted = TRUE;
    }
  }

  if ($converted)
    return $tmpName;
  else
  switch ($type)
  {
    case "table": return $name;
    case "key": return $name;
    default: die("Invalid upgrade type '".$type."'.\n");
  }
}


function stDoAddSettings($inDB, $outDB, $settings, $table, $upgrade, $group = FALSE)
{
  $status = TRUE;

  foreach ($settings as $key => $data)
  {
    $sql = false;

    // Get setting type
    switch ($data[0])
    {
      case VT_TEXT: $type = "%s"; $var = "vtext"; break;
      case VT_STR:  $type = "%s"; $var = "vstr"; break;
      case VT_BOOL: $type = "%b"; $var = "vint"; break;
      case VT_INT:  $type = "%d"; $var = "vint"; break;
      default:      die("Invalid type in default settings '".$key."', type=".$data[0]."\n");
    }

    $sqlFmt =
      "INSERT INTO ".$table." (key,vtype,".$var.",sdesc".
      ($group !== FALSE ? ",vgroup" : "").") VALUES (%s,%d,".$type.",%s".
      ($group !== FALSE ? ",%d" : "").")";

    // Check if we are upgrading
    if ($upgrade)
    {
      // Map the key, in case the name has changed
      $inKey = stUpgradeMap("key", $key);
      $inTable = stUpgradeMap("table", $table);

      if (($res = stDBFetchSQL($inDB, stDBPrepareSQL($inDB, "SELECT * FROM ".$inTable." WHERE key=%s", $inKey))) !== FALSE)
      {
        // Yup, upgrade the data, if we can
        if ($res["vtype"] != $data[0])
          die("Oops! Data type of '".$key."' does not match in table '".$table.". DB upgrade failed.\n");

        $sql = stDBPrepareSQL($outDB, $sqlFmt,
          $key, $data[0], $res[$var], $data[2], $group);
      }
    }

    if ($sql === false)
    {
      // Normal insertion of default data
      $sql = stDBPrepareSQL($outDB, $sqlFmt,
        $key, $data[0], $data[1], $data[2], $group);
    }

    if (stDBExecSQL($outDB, $sql) === FALSE)
    {
      $status = FALSE;
      break;
    }
  }

  return $status;
} 


function stAddSettingsGroup($inDB, $outDB, $groups, $groupData, $groupTable, $table, $upgrade)
{
  echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table.\n";
  foreach ($groups as $groupID => $settings)
  {
    if (!isset($groupData[$groupID]))
    {
      echo "Group data not set for settings group ID '".$groupID."'.\n";
      return FALSE;
    }
    
    // Add group, if needed
    echo " - Group '".$groupID."' -> ".$groupData[$groupID][1].".";
    $sql = stDBPrepareSQL($outDB,
      "INSERT INTO ".$groupTable." (name,description) VALUES (%s,%s)",
      $groupData[$groupID][0], $groupData[$groupID][1]);

    if (($gid = stDBExecSQLInsert($outDB, $sql)) === false)
    {
      echo "\nFailed to add group '".$groupID."'\n";
      return FALSE;
    }
    
    // Add settings to the group
    stDBBeginTransaction($outDB);
    if (stDoAddSettings($inDB, $outDB, $settings, $table, $upgrade, $gid) === false)
    {
      echo "\nFailed to add settings to group '".$groupID."'\n";
      return FALSE;
    }
    stDBCommitTransaction($outDB);
    echo "\n";
  }

  return TRUE;
}


function stAddSettingsNormal($inDB, $outDB, $settings, $table, $upgrade)
{
  stDBBeginTransaction($outDB);
  echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table.";
  $status = stDoAddSettings($inDB, $outDB, $settings, $table, $upgrade);
  echo "\n";
  if ($status !== FALSE)
    stDBCommitTransaction($outDB);
  return $status;
}


function stAddTestData($outDB)
{
  global $siteTestData;

  echo "Adding test data.\n";

  $status = TRUE;
  stDBBeginTransaction($outDB);

  foreach ($siteTestData as $table => $data)
  {
    echo " - ".$table."...\n";
    if (count($data) >= 3)
    {
      for ($n = 2; $n < count($data); $n++)
      {
        $arr = array_merge(
          array($outDB, "INSERT INTO ".$table." (".$data[0].") VALUES (".$data[1].")"),
          $data[$n]);

        $sql = call_user_func_array('stDBPrepareSQL', $arr);
        if (stDBExecSQL($outDB, $sql) === false)
        {
          $status = false;
          break;
        }
      }
    }
    else
    {
      echo "  Invalid table / data definition.\n";
    }
  }

  stDBCommitTransaction($outDB);
  return $status;
}


function stGetSQLTypeParam($dbh, $def, $value)
{
  switch (substr($def, 0, 3))
  {
    case "INT":
      return intval($value);

    case "VAR":
    case "TEX":
      return $dbh->quote($value);

    default: die("Unknown type ".$col[1].".\n");
  }
}


function stMigrateTables($inDB, $outDB, $upgrade, $excluded)
{
  global $sqlTables;

  echo "Migrating tables...\n";
  $status = TRUE;
  $dbName = $outDB->getAttribute(PDO::ATTR_DRIVER_NAME);
  stDBBeginTransaction($outDB);
  
  foreach ($sqlTables as $table => $schema)
  {
    // Do not migrate excluded tables (or excluded tables with old names)
    $sequences = array();
    $inTable = $upgrade ? stUpgradeMap("table", $table) : $table;
    if ($inTable === FALSE || in_array($inTable, $excluded) || in_array($table, $excluded))
      continue;

    // Process each row of the input table
    echo " - '".$table."' ";
    $query = stDBExecSQL($inDB, "SELECT * FROM ".$inTable);
    if ($query === FALSE)
    {
      echo "SKIPPED!";
    }
    else
    foreach ($query as $row)
    {
      // Convert to new schema, as needed
      $avals = array();
      $acols = array();
      
      foreach ($schema as $col)
      {
        // If input has schema column, add it to output
        if (isset($row[$col[0]]))
        {
          $avals[] = stGetSQLTypeParam($outDB, $col[1], $row[$col[0]]);
          $acols[] = $col[0];
        }

        // If the column should have autoincrement, add it to sequences
        if (in_array("AUTOINCREMENT", $col))
          $sequences[] = $col[0];
      }

      $sql = "INSERT INTO ".$table." (".implode(",", $acols).") VALUES (".implode(",", $avals).")";
      if (stDBExecSQL($outDB, $sql) === false)
      {
        $status = FALSE;
        break;
      }
      echo ".";
    }

    // Perform some specialities here
    switch ($dbName)
    {
      case "pgsql":
        // For PostgreSQL we need to update sequences
        foreach ($sequences as $seq)
        {
          if (($max_id = stDBFetchSQLColumn($outDB, "SELECT MAX(".$seq.") FROM ".$table)) !== false &&
              ($seq_id = stDBFetchSQLColumn($outDB, "SELECT nextval('".$table."_".$seq."_seq')")) !== false &&
              ($seq_id <= $max_id))
          {
            echo "SEQFIX";
            stDBExecSQL($outDB, "SELECT setval('".$table."_".$seq."_seq', ".$max_id.")");
          }
        }
        break;
    }

    echo "\n";
  }

  stDBCommitTransaction($outDB);

  return $status;
}


function stSetDBPermissions($spec)
{
  if (substr($spec, 0, 7) == "sqlite:")
  {
    $filename = substr($spec, 7);
    echo
      "NOTICE! It seems you have SQLite database in use, changing permission ".
      "of the target file '".$filename."' to 0600, for security. You may have to ".
      "loosen up that for the things to actually work, but be careful. Having your ".
      "database world-readable in the web is NOT good.\n";

    if (chmod($filename, 0600) === FALSE)
    {
      echo "ERROR! Could not set permissions of '".$filename."!\n";
    }
  }
}


//
// Main program starts
//
if ($argc < 2)
{
  echo
  "ManageDB - Manage FAPWeb SQL database\n".
  "(C) Copyright 2012-2017 ccr/TNSP\n".
  "\n".
  "Usage: ".$argv[0]." <mode> [args]\n".
  "Where mode is one of following:\n".
  "\n".
  "  new <dbspec>      Create a new database with given PDO spec\n".
  "                    or default to the one in mconfig.inc.php\n".
  "\n".
  "  test <dbspec>     Like new, but add initial test data.\n".
  "\n".
  "  upgrade <input_dbspec> <output_dbspec>\n".
  "                    Upgrade current database, if possible.\n".
  "                    Output to new database (DO NOT USE SAME as current!)\n".
  "\n".
  "  force_upgrade <input_dbspec> <output_dbspec>\n".
  "                    Like 'upgrade' but without version check.\n".
  "                    DO NOT USE unless you know what you are doing.\n".
  "\n".
  "  migrate <input_dbspec> <output_dbspec>\n".
  "                    Like upgrade, but no version check. Creates\n".
  "                    a copy of the database to the output spec.\n".
  "\n";
  exit;
}

// Validate tables data
$errors = FALSE;
foreach ($sqlTables as $table => $data)
{
  if (strtolower($table) != $table)
  {
    echo "Invalid table definition '".$table."', table name must be lower case.\n";
    $errors = TRUE;
  }
  
  foreach ($data as $def)
  if (strtolower($def[0]) != $def[0])
  {
    echo "Invalid table definition '".$table."', column key '".$def[0]."' must be lower case.\n";
    $errors = TRUE;
  }
}

if ($errors)
  die("Errors in table definitions.\n");


// Act according to specified command
$addTestData = FALSE;
$upgrade = FALSE;

switch (stCArgLC(1))
{
  case "test":
    $addTestData = TRUE;

  case "new":
    // Try to connect to database
    if (($inSpec = stCArg(2)) === false)
      die("No PDO database spec specified.\n");

    if (($inDB = stConnectSQLDBSpec($inSpec)) === false)
      die("Could not connect to SQL database '".$inSpec."'.\n");

    echo "Using database spec '".$inSpec."'.\n";

    // Create tables, add defaults
    if (stCreateTables($inDB, FALSE))
    {
      stAddSettingsNormal($inDB, $inDB, $dbMetaData, "dbmeta", FALSE, FALSE);
      stAddSettingsGroup($inDB, $inDB, $siteDefaultSettings, $siteSettingsGroups, "settings_groups", "settings", FALSE);
      stAddSettingsNormal($inDB, $inDB, $siteDisplayVars, "display_vars", FALSE, FALSE);
    }
    
    if ($addTestData)
      stAddTestData($inDB);

    stSetDBPermissions($inSpec);
    break;

  case "upgrade":
  case "force_upgrade":
    $upgrade = TRUE;

  case "migrate":
    //
    // Attempt to upgrade database
    //
    if ($argc < 4)
      die("Usage: ".$argv[0]." upgrade <input_dbspec> <output_dbspec>\n");

    $inSpec = stCArg(2);
    $outSpec = stCArg(3);

    if ($outSpec == $inSpec)
      die("The input and output databases CAN NOT BE SAME.\nBe VERY CAREFUL to not accidentally specify same db!\n");

    echo "Using INPUT database spec '".$inSpec."'.\n";

    if (($inDB = stConnectSQLDBSpec($inSpec)) === false)
      die("Could not connect to SQL database '".$inSpec."'.\n");

    // Check the current version first ...
    if (($currVersion = stGetDBMeta($inDB, "dbVersion")) === FALSE)
      $currVersion = -1;

    if ($currVersion == $dbVersion && stCArgLC(1) == "upgrade")
    {
      echo "Database is already version ".$dbVersion.", no upgrading needed.\n";
    }
    else
    {
      // Okay, we shall create an upgraded version ..
      if (($outDB = stConnectSQLDBSpec($outSpec)) === false)
        die("Could not connect to SQL database '".$outSpec."'.\n");

      echo "Database at version ".$currVersion.", upgrading to ".$dbVersion."\n";
      echo "Using OUTPUT database spec '".$outSpec."'.\n";

      // Possibly bail out incompatible upgrades here
      
      // Create tables
      if (!stCreateTables($outDB, TRUE))
        exit;

      // Migrate data from setting tables ..
      if (!stAddSettingsGroup($inDB, $outDB, $siteDefaultSettings, $siteSettingsGroups, "settings_groups", "settings", TRUE))
        exit;

      if (!stAddSettingsNormal($inDB, $outDB, $siteDisplayVars, "display_vars", TRUE))
        exit;

      stAddSettingsNormal($inDB, $outDB, $dbMetaData, "dbmeta", TRUE);

      // Migrate other tables
      if (!stMigrateTables($inDB, $outDB, $upgrade, array("settings", "display_vars", "dbmeta", "settings_groups")))
        exit;
      
      // Set new database version
      echo "Setting dbVersion.\n";
      stSetDBMeta($outDB, "dbVersion", $dbVersion);
      echo "Upgrade complete.\n";

      stSetDBPermissions($inSpec);
      stSetDBPermissions($outSpec);
    }
    break;

  default:
    echo "ERROR! Invalid operation mode '".stCArg(1)."'.\n";
    break;
}


//
// Clean up permissions
//
foreach (array("managedb.php", "faptool.php") as $filename)
{
  if (@chmod($filename, 0700) === FALSE)
  {
    echo "ERROR! Could not set permissions for '".$filename."'!\n";
  }
}

?>