#!/usr/bin/php
<?
require_once "mconfig.inc.php";
require_once "msite.inc.php";


// Check if we are running from commandline or not
if (php_sapi_name() != "cli" || !empty($_SERVER["REMOTE_ADDR"]))
{
  header("Status: 404 Not Found");
  die();
}

$dbVersion = 5;

$dbMeta = array(
  "dbVersion" => array(VT_INT, $dbVersion, "Database version"),
);


// The defaults we put in
$siteDefaults = array(
  "maxAttendeesHard" => array(VT_INT, 60, "Maximum attendees (HARD limit, <= 0 means no limit)"),
  "maxAttendeesSoft" => array(VT_INT, 50, "Maximum attendees (soft limit, <= 0 means no limit)"),

  "userTimeout"      => array(VT_INT, 120, "User pages (voting) timeout in minutes"),
  "admTimeout"       => array(VT_INT, 15, "Administration interface timeout in minutes"),

  "showAdmin"        => array(VT_BOOL, false, "Always show administration interface link on the menu"),
  "showAttendees"    => array(VT_BOOL, true, "Show attendees list"),
  "allowRegister"    => array(VT_BOOL, false, "Enable event registration"),
  "allowVoting"      => array(VT_BOOL, false, "Enable voting (individual compos must be enabled as well)"),

  "showResults"      => array(VT_BOOL, false, "Enable results page"),
  "showResultsASCII" => array(VT_BOOL, true,  "Show results as ASCII instead of HTML table"),
  "showResAuthors"   => array(VT_BOOL, true, "Show entry authors on results page"),

  "requireEMail"     => array(VT_BOOL, false, "Require e-mail address in registrations"),

  "showNews"         => array(VT_BOOL, true, "Enable News link on main menu"),
  "showNewsOnAbout"  => array(VT_BOOL, true, "Show latest news item on About page"),

  "registerInfoText" => array(VT_TEXT, "<p>
Only your <b>handle</b> and the answer to the botcheck are strictly required.
If you plan on joining the IRC channel
(<a href=\"irc://#fap2013@ircnet\">#fap2013 @ IRCNet</a>) or staying up to date by other means,
<b>e-mail</b> is not required either.
</p>", "Registration page info text"),

  "registerPostText" => array(VT_TEXT, "
<h1>Registration successful</h1>
<p>Now go make a demo about it!</p>",
  "Successful post-registration note text"),

  "registerLimitExceeded" => array(VT_TEXT, "
<h1>Sorry, registration disabled!</h1>
<p>
Registration to the event is not available at this time due to
number of attendees limit having been reached. <b>:(</b>
</p>
",
  "Registration attendee limit exceeded note text"),

  "registerNotEnabled" => array(VT_TEXT, "
<h1>Sorry, registration disabled!</h1>
<p>
Registration to the event is not enabled at this time.
</p>
",
  "Registration not enabled note text"),

  "registerPostNoEmail" => array(VT_TEXT, "
<h2>By the way ...</h2>
<p>As you did not specify an e-mail contact address, you'll have to get updates
and information about the location (if you don't already know it) by 
some other means (IRC, for example.)</p>
", "No e-mail address registration note"),

  "eventDescription" => array(VT_TEXT, "
<h1>Event program &amp; schedule</h1>
<ul>
 <li><b>Aegis</b> of DSS and FAG will be performing a DJ gig.</li>
 <li>.. and possible additional live acts. More info to come.</li>
</ul>

<h2>Friday 30.11.</h2>
<ul>
 <li><b>18:00</b> - <i>Doors open</i>.</li>
 <li><b>22:00</b> - DJ set by Aegis.</li>
</ul>

<h2>Saturday 1.12.</h2>
<ul>
 <li><b>14:00</b> - Deadline for remote entries.</li>
 <li><b>18:00</b> - Deadline for the entries delivered on location.</li>
 <li><b>20:00</b> - Competitions start.</li>
</ul>

Competition schedule and voting deadline will depend on number of entries.

<h2>Sunday 2.12.</h2>
<ul>
 <li><b>12:00</b> - Party over?</li>
</ul>
", "Event general description / timetables etc."),


  "compoDescription" => array(VT_TEXT, "
<h1>General</h1>
<p class=\"notice\">
YOU <b>MUST</b> HAVE AT LEAST ONE ENTRY TO COMPETITIONS IF YOU COME TO THE PARTY.
</p>

<p class=\"note\">
If there are enough entries, then AGA/OCS/ECS demos will be run in separate compos.
<br />
Remote entries are welcome!
</p>

<p>
The compo machine will be an <b>A1200 with an 060/50 and lots of
RAM</b>. An <b>A500 1.3 512k/512k</b> will also be available if your
prod is not AGA compatible.
</p>

<h1>Compos</h1>
", "Compo general description"),


  "siteInfoText"     => array(VT_TEXT, "
<a href=\"about\">
<img src=\"img/fapsm.png\" alt=\"Finnish Amiga Party 2013\" class=\"logo\" /></a>
<div id=\"date\">
5.-8.12.2013<br />
Helsinki, Finland<br />
@ old location<br />
<span class=\"notice\">Entry 15 EUR + prod</span>
</div>", "Site header text"),


  "newsHeader"  => array(VT_TEXT, "", "News page header text"),


  "aboutDescription"  => array(VT_TEXT, "
<div style=\"text-align: center;\">
<img src=\"img/fap.png\" alt=\"FAP\" />
<p>
Pure Amiga demoscene party, all traditional Amiga compos and purely Amiga-oriented program.
<br />
<span class=\"notice\">
YOU <b>MUST</b> HAVE AT LEAST ONE ENTRY TO COMPETITIONS IF YOU COME TO THE PARTY.
</span>
</p>
</div>",
  "About page text"),

  "voteFinishedText" => array(VT_TEXT, "
<h1>Yay, you have voted!</h1>
<p>Now go FAP some more! And make a demo about it.</p>",
  "Message shown after successful voting."),

  "siteMenuHeader" => array(VT_TEXT, "<div>13.FAP:&gt; <span class=\"mblink\">&#9632;</span></div>", "Site menu header text"),
  "siteMenuFooter" => array(VT_TEXT, "", "Site menu footer text"),
  "siteExtraHTML" => array(VT_TEXT, "<div id=\"sponsors\">Gentle Eye</div>", "Extra global HTML code (f.e. sponsors box)"),
);

$sqlTables = array(
  "dbmeta" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",

  "settings" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",
  "news" => "id INTEGER PRIMARY KEY AUTOINCREMENT, utime INT, title VARCHAR(".SET_LEN_NEWS_TITLE."), text VARCHAR(".SET_LEN_NEWS_TEXT."), author VARCHAR(".SET_LEN_NEWS_AUTHOR."), persist INT DEFAULT 0",
  "compos" => "id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(".SET_LEN_COMPO_NAME."), description VARCHAR(".SET_LEN_COMPO_DESC."), visible INT DEFAULT 0, voting INT DEFAULT 0, showAuthors INT DEFAULT 0",
  "entries" => "id INTEGER PRIMARY KEY AUTOINCREMENT, show_id INT DEFAULT 0, name VARCHAR(".SET_LEN_ENTRY_NAME."), author VARCHAR(".SET_LEN_ENTRY_AUTHOR."), compo_id INT DEFAULT NULL, filename VARCHAR(".SET_LEN_ENTRY_FILENAME.") DEFAULT NULL, info VARCHAR(".SET_LEN_ENTRY_INFO.") DEFAULT NULL",

  "attendees" => "id INTEGER PRIMARY KEY AUTOINCREMENT, regtime INT, name VARCHAR(".SET_LEN_USERNAME."), groups VARCHAR(".SET_LEN_GROUPS."), oneliner VARCHAR(".SET_LEN_ONELINER."), email VARCHAR(".SET_LEN_EMAIL."), key_id INT DEFAULT NULL",

  "votekeys" => "id INTEGER PRIMARY KEY AUTOINCREMENT, key VARCHAR(64), active INT DEFAULT 0",
  "votes" => "id INTEGER PRIMARY KEY AUTOINCREMENT, entry_id INT DEFAULT NULL, voter_id INT DEFAULT NULL, value INT DEFAULT 0",

  "displayVars" => "key VARCHAR(32) PRIMARY KEY, vtype INT, vstr VARCHAR(128), vtext TEXT, vint INT, desc VARCHAR(128)",
  "displaySlides" => "id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(128), text VARCHAR(4096)",
  "displayListData" => "id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(128)",
  "displayListSlides" => "id INTEGER PRIMARY KEY AUTOINCREMENT, list_id INT DEFAULT 0, slide_id INT DEFAULT 0",
);

$siteDisplayVars = array(
  "showMode"          => array(VT_INT, 0, "Currently active display mode"),
  "rotateDuration"    => array(VT_INT, 15, "Slide rotation time per slide (seconds)"),

  "tempDuration"      => array(VT_INT, 120, "Temporary slide display time (seconds)"),
  "tempSlide"         => array(VT_INT, 0, "Temporary slide ID"),

  "compoID"           => array(VT_INT, 0, "Compo ID of current compo"),
  "compoPrevEntry"    => array(VT_INT, 0, "Previously shown compo entry"),
  "compoCurrEntry"    => array(VT_INT, 0, "Current / next compo entry to be shown"),
  
  // Not user-manageable
  "tempSlideSet"      => array(VT_BOOL, false, "Temporary slide set"),

  "activeSlideMode"   => array(VT_INT, 0, "Current active slide display mode"),
  "activeSlide"       => array(VT_INT, 0, "Current active slide"),
  "activeSlideExpire" => array(VT_INT, 0, "Expiration timestamp of current slide"),

  "rotateList"        => array(VT_INT, 0, "Current rotation list ID"),
  "rotateListIndex"   => array(VT_INT, 0, "Current index in rotation list"),

  "lastUpdate"        => array(VT_INT, 0, "Timestamp of last slide update"),
);

$siteTestData = array(
  "news" => array(
    "utime,title,text,author",
    time().",%s,%s,%s",
    array("Today's news", "We. Are. Back.", "orgaz"),
    array("Good news, everybody!", "...", "The Professor"),
  ),

  "votekeys" => array(
    "key",
    "%s",
    array("test1"),
    array("test2"),
    array("7jjnqt5z"),
    array("c7jcfk1G"),
  ),

  "attendees" => array(
    "regtime,name,groups,oneliner,email",
    "%d,%s,%s,%s,%s",
    array(time()-0, "man with no alias", "supergroup", "foo-bar", "c@supergroup.com"),
    array(time()-15, "man with alias", "supergroup", "hi!", "c@supergroup.com"),
    array(time()-30, "alias with a man", "supergroup", "mega super kewl rulets", "x@microsoft.com"),
  ),

  "compos" => array(
    "name,description,visible,voting",
    "%s,%s,1,1",
    array("Graphics", "Anything in standard resolutions."),
    array("Protracker music", "Standard 4-channel Protracker MOD music."),
    array("4k intro", "4k intro competition"),
  ),
  
  "entries" => array(
    "name,author,compo_id,filename,info",
    "%s,%s,%d,%s,%s",
    array("Donkey Dong", "electric/extend", 1, "donkey.lbm", "amigaaaah!"),
    array("Your kondom", "ccr/TNSP", 1, "kondom.lbm", "oh my god, it's full of cocks!"),
    array("Penis song", "reed/flt", 2, "penis.mod", "laulu rakkaudelle"),
    array("jenkka", "aegis", 2, "jenkka.mod", ""),
    array("Fungiform 2", "mfx", 3, "mfx-fungiform2.lzh", "OCS-only"),
  ),
  
  "displaySlides" => array(
    "title,text",
    "%s,%s",
    array("Next Up", "<h1>Next up: 4k intro</h1><h2>4k intro compo is about to begin</h2>... in about 10 minutes."),
    array("Astu to infodesk", "<b>Astu</b> - please come to info desk!"),
    array("Gentle Eye mainos", "<b>Buy Amiga stuff!</b><br />Gentle Eye Oy is selling Amiga-related stuff near organizer desk!"),
  ),
  
  "displayListData" => array(
    "name",
    "%s",
    array("Main rotation"),
    array("Next Up"),
  ),
  
  "displayListSlides" => array(
    "list_id,slide_id",
    "%d,%d",
    array(1,2),
    array(1,3),
    array(2,1),
  ),
);


//
// Helper functions
//
function stCArg($index)
{
  global $argc, $argv;
  if ($index < $argc)
    return $argv[$index];
  else
    return FALSE;
}

function stCArgLC($index)
{
  global $argc, $argv;
  if ($index < $argc)
    return strtolower($argv[$index]);
  else
    return FALSE;
}


function stCSQLError($sql)
{
  global $db;
  die("Error executing SQL query: ".implode("; ", $db->errorInfo())." in statement \"".$sql."\"\n");
  exit;
}


function stConnectDB($dbspec)
{
  global $db;
  try {
    $db = new PDO($dbspec);
  }
  catch (PDOException $e) {
    error_log("Could not connect to SQL database '".$dbspec."': ".$e->getMessage().".");
    return FALSE;
  }
  return TRUE;
}


function stGetDBMeta($name)
{
  global $db;

  if (($item = stFetchSQL("SELECT * FROM dbmeta WHERE key=".$db->quote($name))) === FALSE)
    return FALSE;
  
  return stGetSQLSettingData($item);
}


function stSetDBMeta($name, $value)
{
  global $db;

  if (($item = stFetchSQL("SELECT * FROM dbmeta WHERE key=".$db->quote($name))) === FALSE)
    return FALSE;

  $sql = "UPDATE dbmeta SET ".stGetSettingSQL($item, $value)." WHERE key=".$db->quote($name);
  return stExecSQL($sql);
}


function stCreateOneTable($name, $schema)
{
  return (stExecSQL("CREATE TABLE IF NOT EXISTS ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE;
}


function stCreateTables($upgrade)
{
  global $sqlTables;
  echo "Creating tables...\n";
  foreach ($sqlTables as $name => $schema)
  {
    echo " - '".$name."'\n";
    if (!stCreateOneTable($name, $schema) && !upgrade)
      return FALSE;
  }
  return TRUE;
}


function stAddSettings($settings, $table, $upgrade)
{
  echo ($upgrade ? "Adding settings to" : "Upgrading settings in")." '".$table."' table.";

  foreach ($settings as $key => $data)
  {
    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");
    }

    if ($upgrade && ($res = stFetchSQL(stPrepareSQL("SELECT * FROM ".$table." WHERE key=%s", $key))) !== FALSE)
    {
      echo ".";

      if ($res["vtype"] != $data[0])
      {
        die("Oops! Data type of '".$key."' does not match in table '".$table.". DB upgrade failed.\n");
      }

      $sql = stPrepareSQL(
        "UPDATE ".$table." SET desc=%s WHERE key=%s",
        $data[2], $key);
    }
    else
    {
      echo "+";
      $sql = stPrepareSQL(
        "INSERT INTO ".$table." (key,vtype,".$var.",desc) VALUES (%s,%d,".$type.",%s)",
        $key, $data[0], $data[1], $data[2]);
    }

    if (stExecSQL($sql) === FALSE)
      return FALSE;
  }
  echo "\n";
  return TRUE;
}


function stRenameSettings($mapping, $table)
{
  echo "Upgrading '".$table."' table.\n";

  foreach ($mapping as $from => $to)
  {
    $sql = stPrepareSQL(
      "UPDATE ".$table." SET key=%s WHERE key=%s",
      $to, $from);

    stExecSQL($sql);
  }
}


function stAddTestData()
{
  global $siteTestData;
  echo "Adding test data.\n";

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

        $sql = call_user_func_array('stPrepareSQL', $arr);
        stExecSQL($sql);
      }
    }
    else
    {
      echo "  Invalid table / data definition.\n";
    }
  }
}

//
// Main program starts
//
if ($argc < 2)
{
  echo "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 [dbspec]  Upgrade current database, if possible.\n".
  "\n";
  exit;
}


if (!isset($siteSettings["voteKeyMode"]) || $siteSettings["voteKeyMode"] < 0)
{
  echo
    "FATAL ERROR! VoteKeyMode not set in site settings! This setting ".
    "MUST be defined and should not be changed after database creation.\n";
  exit;
}

// Check if database spec provided, if not use default
if (($spec = stCArg(2)) === FALSE)
  $spec = $siteSettings["sqlDB"];


// Try to connect to database
if (!stConnectDB($spec))
  die("Could not connect to SQL database '".$spec."'.\n");

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


// Act according to specified command
$addData = FALSE;
switch (stCArgLC(1))
{
  case "test":
    $addData = TRUE;

  case "new":
    if (stCreateTables(FALSE))
    {
      stAddSettings($dbMeta, "dbmeta", FALSE);
      stAddSettings($siteDefaults, "settings", FALSE);
      stAddSettings($siteDisplayVars, "displayVars", FALSE);
    }
    
    if ($addData)
      stAddTestData();

    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!\n";
      }
    }
    break;

  case "upgrade":
    if (($currVersion = stGetDBMeta("dbVersion")) === FALSE)
      $currVersion = -1;

    if ($currVersion == $dbVersion)
    {
      echo "Database is already version ".$dbVersion.", no upgrading needed.\n";
    }
    else
    {
      echo "Database at version ".$currVersion.", upgrading to ".$dbVersion."\n";
      // Possibly bail out incompatible upgrades here
      
      // Create tables
      if (!stCreateTables(TRUE))
        exit;

      // Do renames etc. here

      // Do upgrading of setting descs
      if (!stAddSettings($siteDefaults, "settings", TRUE))
        exit;

      if (!stAddSettings($siteDisplayVars, "displayVars", TRUE))
        exit;

      stAddSettings($dbMeta, "dbmeta", TRUE);
      
      stSetDBMeta("dbVersion", $dbVersion);
      echo "Upgrade complete.\n";
    }
    break;

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


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

?>