<?php

// $Id: post.inc 2570 2012-12-06 18:34:30Z cimorrison $

// This upgrade assings a UID to all existing bookings and a RECURRENCE-ID to all
// existing members of a series.   However the RECURRENCE-ID is not necessarily going
// to be correct if the entry is a modified entry (ie entry_type=ENTRY_RPT_CHANGED).
// That's because the RECURRENCE_ID is supposed to be the *original* start date and
// time of that entry, and this is information that we no longer have.  (We do know
// the time, as we have the start time in the repeat table, but it is not trivial to
// take advantage of it as we'd have to account for DST changes and we still don't
// have the date.   So I've taken the view here that it's not worth bothering about
// as this upgrade procedure is only used once for existing entries).

function get_microtime()
{
  if (function_exists('microtime'))
  {
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
  }
  else
  {
    return time();
  }
}

function do_sql_command($sql, $step)
{
  if (sql_command($sql) < 0)
  {
    // echo "$sql<br>\n";  // Debug line
    // echo sql_error() . "<br>";  // Debug line
    echo "<span class=\"error\">Step $step failed</span><br>";
    // exit;  // Debug line
  }
}

global $dbsys, $tbl_entry, $tbl_repeat;

// There's the option here to display some timing information as this upgrade
// could take a while
$display_timing = FALSE;

$n_entry = sql_query1("SELECT COUNT(*) FROM $tbl_entry");
$n_repeat = sql_query1("SELECT COUNT(*) FROM $tbl_repeat");
if ($display_timing)
{
 echo "Upgrade 24: $n_entry entry rows and $n_repeat repeat rows";
}

$start_clock = get_microtime();

$sql = "ALTER TABLE $tbl_entry ADD COLUMN saved_ts DATETIME";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to add saved_ts column in entry table.</span>";
}
$sql = "UPDATE $tbl_entry SET saved_ts=timestamp";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to set saved_ts column in entry table.</span>";
}
$sql = "ALTER TABLE $tbl_repeat ADD COLUMN saved_ts DATETIME";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to add saved_ts column in repeat table.</span>";
}
$sql = "UPDATE $tbl_repeat SET saved_ts=timestamp";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to set saved_ts column in repeat table.</span>";
}

// MySQL (mysql and mysqli)
// ------------------------
if ($dbsys != "pgsql")
{
  // Give all the individual entries, that haven't already got one, an ical uid
  $sql = "UPDATE $tbl_entry SET ical_uid=CONCAT(CAST(id AS char), '-', UUID()) WHERE ical_uid='' AND repeat_id=0";
  do_sql_command($sql, 1);

  // Give everything in the repeat table, that doesn't already have one, an ical uid
  $sql = "UPDATE $tbl_repeat SET ical_uid=CONCAT(CAST(id AS char), '-', UUID()) WHERE ical_uid=''";
  do_sql_command($sql, 2);

  // Now go through the entry table and give all entries, that don't have an ical uid
  // and are members of a series, the ical_uid from the corresponding uid from the
  // repeat table
  $sql = "UPDATE $tbl_entry E, $tbl_repeat R
             SET E.ical_uid=R.ical_uid
           WHERE E.ical_uid=''
             AND E.repeat_id=R.id";
  do_sql_command($sql, 3);
  
  // Finally give a recurrence id to any entry in the entry table that hasn't got one
  // and should have one - ie if it is a member of a series                      
  $sql = "UPDATE $tbl_entry
             SET ical_recur_id=DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(start_time), @@session.time_zone, '+0:00'), '%Y%m%dT%H%i%sZ')
           WHERE repeat_id!=0
             AND ical_recur_id=''";
  do_sql_command($sql, 4);
}

// PostgreSQL
// ----------
else         
{
  // PostgreSQL doesn't have a UUID() function as standard, so we have to construct
  // our own UUID.
  //
  // We will generate a uid of the form "MRBS-uniqid-MD5hash@domain_name" 
  // where uniqid is time based and is generated by uniqid() and the
  // MD5hash is the first 8 characters of the MD5 hash of $str concatenated
  // with a random number.  [This is the same process used by the MRBS function
  // generate_global_uid()]
  
  if (empty($_SERVER['SERVER_NAME']))
  {
    $domain_name .= 'MRBS';
  }
  elseif (strpos($_SERVER['SERVER_NAME'], 'www.') === 0)
  {
    $domain_name .= substr($_SERVER['SERVER_NAME'], 4);
  }
  else
  {
    $domain_name .= $_SERVER['SERVER_NAME'];
  }

  // Give all the individual entries, that haven't already got one, an ical uid
  $sql = "UPDATE $tbl_entry 
             SET ical_uid='MRBS-' || CAST(id AS varchar(255)) || '-' || CURRENT_DATE || CURRENT_TIME || '-' || SUBSTRING((MD5(name || CAST(RANDOM() AS varchar(255)))) from 1 for 8) || '@$domain_name'
           WHERE ical_uid=''
             AND repeat_id=0";
  do_sql_command($sql, 1);
             
  // Give everything in the repeat table, that doesn't already have one, an ical uid
  $sql = "UPDATE $tbl_repeat
             SET ical_uid='MRBS-' || CAST(id AS varchar(255)) || '-' || CURRENT_DATE || CURRENT_TIME || '-' || SUBSTRING((MD5(name || CAST(RANDOM() AS varchar(255)))) from 1 for 8) || '@$domain_name'
           WHERE ical_uid=''";
  do_sql_command($sql, 2);

  // Now go through the entry table and give all entries, that don't have an ical uid
  // and are members of a series, the ical_uid from the corresponding uid from the
  // repeat table.   (The SQL is slightly different from the MySQL case)
  $sql = "UPDATE $tbl_entry
             SET ical_uid=R.ical_uid
            FROM $tbl_repeat AS R
           WHERE $tbl_entry.ical_uid=''
             AND $tbl_entry.repeat_id=R.id";
  do_sql_command($sql, 3);
             
  // Finally give a recurrence id to any entry in the entry table that hasn't got one
  // and should have one - ie if it is a member of a series (The SQL is slightly
  // different from the MySQL case)                   
  $sql = "UPDATE $tbl_entry
             SET ical_recur_id=TO_CHAR(TIMESTAMP 'epoch' + start_time * INTERVAL '1 second', 'YYYYMMDD\"T\"HH24MISS\"Z\"')
           WHERE repeat_id!=0
             AND ical_recur_id=''";
  do_sql_command($sql, 4);
}

$sql = "UPDATE $tbl_entry SET timestamp=saved_ts";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to update timestamp column in entry table.</span>";
}
$sql = "ALTER TABLE $tbl_entry DROP COLUMN saved_ts";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to delete saved_ts column in entry table.</span>";
}
$sql = "UPDATE $tbl_repeat SET timestamp=saved_ts";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to update timestamp column in repeat table.</span>";
}
$sql = "ALTER TABLE $tbl_repeat DROP COLUMN saved_ts";
$res = sql_command($sql);
if ($res == -1)
{
  trigger_error(sql_error(), E_USER_WARNING);
  // No need to localise, should never happen
  print "<span class=\"error\">Failed to delete saved_ts column in repeat table.</span>";
}

$stop_clock = get_microtime();
$clock_diff = $stop_clock - $start_clock;
if (is_float($start_clock))
{
  $clock_diff = sprintf('%.3f', $clock_diff);
}
if ($display_timing)
{
 echo " processed in $clock_diff seconds<br>\n";
}

?>