<?php
namespace MRBS;

// Assigns a UID to all existing bookings that don't already have one.   They should
// do in theory, but a bug (https://sourceforge.net/p/mrbs/bugs/334/) resulted in some
// not doing so.   The code is based on Upgrade 24.

// This upgrade assigns 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).

global $dbsys, $server;

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

$n_entry = $upgrade_handle->query1("SELECT COUNT(*) FROM " . _tbl('entry'));
$n_repeat = $upgrade_handle->query1("SELECT COUNT(*) FROM " . _tbl('repeat'));
if ($display_timing)
{
 echo "Upgrade 47: $n_entry entry rows and $n_repeat repeat rows";
}

$start_clock = get_microtime();


// In these queries we set timestamp=timestamp to prevent it being automatically set
// to the current time (only applies to MySQL - PostgreSQL timestamps don't update)

// MySQL (mysql and mysqli)
// ------------------------
if ($dbsys != "pgsql")
{
  // 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()),
                 timestamp=timestamp
           WHERE ical_uid=''";
  $upgrade_handle->command($sql);

  // 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,
                 E.timestamp=E.timestamp,
                 R.timestamp=R.timestamp
           WHERE E.ical_uid=''
             AND E.repeat_id=R.id";
  $upgrade_handle->command($sql);

  // 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'),
                 timestamp=timestamp
           WHERE repeat_id!=0
             AND ical_recur_id=''";
  $upgrade_handle->command($sql);

  // Give all the individual entries, that haven't already got one, an ical uid
  // (There shouldn't be any of these as the bug was only affecting repeats)
  $sql = "UPDATE " . _tbl('entry') . "
             SET ical_uid=CONCAT(CAST(id AS char), '-', UUID()),
                 timestamp=timestamp
           WHERE ical_uid='' AND repeat_id IS NULL";
  $upgrade_handle->command($sql);

  // Sequence numbers were being generated correctly under the bug, so we don't need to worry about them
}

// 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 (mb_strpos($server['SERVER_NAME'], 'www.') === 0)
  {
    $domain_name = substr($server['SERVER_NAME'], 4);
  }
  else
  {
    $domain_name = $server['SERVER_NAME'];
  }

  // 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=''";
  $upgrade_handle->command($sql);

  // 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') . " E
             SET ical_uid=R.ical_uid
            FROM " . _tbl('repeat') . " AS R
           WHERE E.ical_uid=''
             AND E.repeat_id=R.id";
  $upgrade_handle->command($sql);

  // 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=''";
  $upgrade_handle->command($sql);

  // Give all the individual entries, that haven't already got one, an ical uid
  // (There shouldn't be any of these as the bug was only affecting repeats)
  $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 IS NULL";
  $upgrade_handle->command($sql);

  // Sequence numbers were being generated correctly under the bug, so we don't need to worry about them
}


$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";
}