#!/usr/bin/env php
<?php

$phplib=__DIR__."/../phplib";
require_once "$phplib/netwin_v2/debug.php";
dbg_init("cal.log");
ini_set('max_execution_time', '0');

dbg("Migration: Netwin SabreDav database migration v1 -> v2\n");

$paths = [
    $phplib.'/sabredav_3.2.2/vendor/autoload.php',
];
foreach ($paths as $path) {
    if (file_exists($path)) {
        include $path;
        break;
    }
}


$data_path = __DIR__."/data";
$phplib = __DIR__."/../phplib";

$db_in = $data_path."/caldb.sqlite";
$db_out = $data_path."/caldb_v2.sqlite";

if($_GET["MIGRATION_TYPE"]=='ABK'){
	$db_in = $data_path."/abkdb.sqlite";
	$db_out = $data_path."/abkdb_v2.sqlite";
	dbg("migrating addressbook db");
}else{
	dbg("migrating calendar db");
}

$db_out_schema = $phplib."/netwin_v2/data/caldb_v2.sqlite";
copy($db_out_schema, $db_out);

dbg("Migration: input file: $db_in");

dbg("Migration: Connecting to source database: $db_in");
$pdo = new PDO("sqlite:".$db_in, "", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
if($driver!='sqlite') {
	dbg( "source db init failed");
	echo "source db init failed";
	die(-1);
}

dbg("Migration: Connecting to destination database: $db_out");
$result = $pdo->exec("ATTACH DATABASE '$db_out' AS new_db");

# Copy the users table untouched
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, username, digesta1, created FROM users');
$stmt1 = $pdo->prepare('INSERT INTO new_db.users (id, username, digesta1, created) VALUES (?,?,?,?)');
$count = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
#	echo "TABLE:users ADDING ROW ".$row['username']."\n";
    $stmt1->execute([ $row['id'], $row['username'], $row['digesta1'], $row['created']]);
    $count++;
}
$pdo->commit();
dbg("Migration: transferred users table  $count records");

# Copy the netwinpermissions table untouched
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, cal_id, calendar, shared_with, access FROM netwinpermissions');
$stmt1 = $pdo->prepare('INSERT INTO new_db.netwinpermissions (id, cal_id, calendar, shared_with, access) VALUES (?,?,?,?,?)');
$count = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
#	echo "TABLE:netwinpermissions ADDING ROW ".$row['calendar']."\n";
    $stmt1->execute([ $row['id'], $row['cal_id'], $row['calendar'], $row['shared_with'], $row['access']]);
    $count++;
}
$pdo->commit();
dbg("Migration: transferred netwinpermissions table  $count records");

# Copy the principals table untouched
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, uri, email, displayname, vcardurl FROM principals');
$stmt1 = $pdo->prepare('INSERT INTO new_db.principals (id, uri, email, displayname) VALUES (?,?,?,?)');
$count = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
#	echo "TABLE:principals ADDING ROW ".$row['uri']."\n";
    $stmt1->execute([ $row['id'], $row['uri'], $row['email'], $row['displayname']]);
    $count++;
}
$pdo->commit();
dbg("Migration: transferred principals table  $count records");

# Reformat the calendars table to calendars and calendar instances tables
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, principaluri, displayname, uri, ctag, description, calendarorder, calendarcolor, timezone, components FROM calendars');

$stmt1 = $pdo->prepare('INSERT INTO new_db.calendars (id, synctoken, components) VALUES (?,?,?)');
$stmt2 = $pdo->prepare('INSERT INTO new_db.calendarinstances (id, calendarid, principaluri, access, displayname, uri, description, calendarorder, calendarcolor, timezone) VALUES (?,?,?,?,?, ?,?,?,?,?)');

#$stmt2 = $pdo->prepare('INSERT INTO new_db.calendarinstances (id, calendarid, principaluri, access, displayname, uri, description, calendarorder, calendarcolor, timezone, /*transparent, share_href, share_displayname, share_invitestatus*/) VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)');


$count = 0;
$unique=[];
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
	$uv = $row['principaluri']." | ".$row['uri'];
	if(array_key_exists($uv, $unique)){
		dbg("TABLE:calendars, calendarinstances ADDING ROW ".$row['principaluri']." | ".$row['uri']." | ".$row['displayname']." 	REMOVING DUPLICATE!!");
		continue;
	}
	$unique[$uv] = true;

#	dbg("TABLE:calendars, calendarinstances ADDING ROW ".$row['principaluri']." | ".$row['uri']." | ".$row['displayname']);
    $stmt1->execute([ $row['id'], 1, $row['components']]);
    $stmt2->execute([ $row['id'], $row['id'], $row['principaluri'], 1, $row['displayname'], $row['uri'], $row['description'], $row['calendarorder'], $row['calendarcolor'], $row['timezone'] ]);
    $count++;
}
$pdo->commit();
dbg("Migration: reformatted the calendars table  $count records");

# Reformat the calendarobjects table
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, calendardata, uri, calendarid, lastmodified FROM calendarobjects');
$stmt1 = $pdo->prepare('INSERT INTO new_db.calendarobjects (id, calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence, uid) VALUES (?,?,?,?,? ,?,?,?,?,?, ?)');

#$stmt1 = $pdo->prepare('INSERT INTO new_db.calendarobjects (id, calendardata, uri, calendarid, lastmodified, etag, size, componenttype, firstoccurence, lastoccurence, uid) VALUES (?,?,?,?,? ,?,?,?,?,?, ?)');

$count = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
#	echo "TABLE:calendarobjects ADDING ROW ".$row['uri']."\n";
    $stmt1->execute([ $row['id'], $row['calendardata'], $row['uri'], $row['calendarid'], $row['lastmodified'], md5($row['calendardata']), strlen($row['calendardata']), "VEVENT", 0, PHP_INT_MAX, substr($row['uri'], 0, -4)]);
    $count++;
}
$pdo->commit();
dbg("Migration: reformatted the calendarobjects table  $count records");

# GENERALLY UNUSED Copy the addressbooks table untouched
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, principaluri, displayname, uri, description, ctag FROM addressbooks');
$stmt1 = $pdo->prepare('INSERT INTO new_db.addressbooks (id, principaluri, displayname, uri, description, synctoken) VALUES (?,?,?,?,?, ?)');
$count = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
#	echo "TABLE:addressbooks ADDING ROW ".$row['principaluri']."\n";
    $stmt1->execute([ $row['id'], $row['principaluri'], $row['displayname'], $row['uri'], $row['description'], 1]);
    $count++;
}
$pdo->commit();
dbg("Migration: transferred the addressbooks table  $count records");

# GENERALLY UNUSED Copy the cards table untouched
$pdo->beginTransaction();
$result = $pdo->query('SELECT id, addressbookid, carddata, uri, lastmodified FROM cards');
$stmt1 = $pdo->prepare('INSERT INTO new_db.cards (id, addressbookid, carddata, uri, lastmodified, etag, size ) VALUES (?, ?, ?, ?, ?, ?, ?)');
$count = 0;
while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
#	echo "TABLE:cards ADDING ROW ".$row['principaluri']."\n";
    $stmt1->execute([ $row['id'], $row['addressbookid'], $row['carddata'], $row['uri'], $row['lastmodified'], md5($row['carddata']), strlen($row['carddata'])]);
    $count++;
}
$pdo->commit();
dbg("Migration: transferred the cards table $count records");

dbg("Migration: Completed");
echo "Migration Complete";
