#! /usr/bin/php5
<?php

// This script will update the database file to the current version
$filename = "phonalisa.db";

function getCurrentVersion($filename) {
	$dbh = new PDO('sqlite:'.$filename);
	$arr = $dbh->query("select * from version");
	if (is_object($arr)) {
		foreach ($dbh->query("select * from version") as $row) { 
			return (int)$row['version'];
		}
	}
	return 0;
}

function setCurrentVersion($filename, $version) {

	$dbh = new PDO('sqlite:'.$filename);
	$dbh->exec("DELETE FROM `version`");
	$dbh->exec("INSERT INTO `version` VALUES(".$version.")");
}

function runStatements($filename, $statements) {

	$dbh = new PDO('sqlite:'.$filename);
	$dbh->beginTransaction();
	 foreach ($statements as $statement) {
		//echo "running: ".$statement."\n";
                $dbh->exec($statement);
                if ($dbh->errorCode() != "00000") {
                        $errorinfo = $dbh->errorInfo();
                        echo "Error occured: ".$errorinfo[2]."\n";
                        echo "Failed statement was: ". $statement."\n";
                        echo "Please contact support!\n";
                        die();
                }
        }
        $dbh->commit();
}

$dbversion = getCurrentVersion($filename);
echo "Current Database Version: ".$dbversion."\n";

if ($dbversion == 0) {
	echo "Database does not exists. Creating initial version 1\n";

	// no database exists 
	$statements = array();
	$statements[] = 'CREATE TABLE "user" ("id" INTEGER PRIMARY KEY  NOT NULL ,"password" VARCHAR(128),"name" VARCHAR(128) UNIQUE,"pin" VARCHAR(10), "firstname" VARCHAR(255), "lastname" VARCHAR(255), "email" VARCHAR(255), "extension" VARCHAR(10));';
	$statements[] = 'CREATE TABLE "cdr" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "address" VARCHAR NOT NULL , "billid" VARCHAR NOT NULL , "billtime" VARCHAR NOT NULL , "called" VARCHAR NOT NULL , "caller" VARCHAR NOT NULL , "cdrid" INTEGER NOT NULL , "chan" VARCHAR NOT NULL , "direction" VARCHAR NOT NULL , "duration" VARCHAR NOT NULL , "external" VARCHAR NOT NULL , "reason" VARCHAR NOT NULL , "operation" VARCHAR NOT NULL , "ringtime" VARCHAR NOT NULL , "runid" INTEGER NOT NULL , "status" VARCHAR NOT NULL , "time" VARCHAR NOT NULL );';
	
	 $statements[] = 'CREATE TABLE "sysconfig" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "key" VARCHAR(80), "value" VARCHAR(255) NOT NULL );';
	 $statements[] = 'CREATE TABLE "phones" ("id" INTEGER PRIMARY KEY  NOT NULL ,"type" VARCHAR(30) NOT NULL ,"mac_addr" VARCHAR(14) NOT NULL ,"nobody_index" INTEGER(8) NOT NULL ,"added" INTEGER(10) NOT NULL ,"firmware" VARCHAR(25) NOT NULL ,"fw_manual_update" INTEGER(1) NOT NULL ,"sip_account_id" INTEGER(10) NOT NULL );';
	 $statements[] = 'CREATE TABLE "sip_accounts" ("id" integer PRIMARY KEY NOT NULL ,"authname" varchar(255) NOT NULL ,"subnet" varchar(255),"secret" varchar(40) NOT NULL ,"current_ip" varchar(16),"device_name" varchar(64) ,"location" varchar(255) ,"last_contact" timestamp ,"expires" integer ,"state" varchar(10), "user_id" INTEGER);';
	 $statements[] = 'CREATE TABLE "groups" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "type" VARCHAR(20) NOT NULL , "name" VARCHAR(40) NOT NULL , "title" VARCHAR(40) NOT NULL , "description" VARCHAR(255));';
	 $statements[] = 'CREATE TABLE "calllist" ("id" INTEGER PRIMARY KEY NOT NULL , "calluuid" VARCHAR(100) NOT NULL , "timestamp" VARCHAR NOT NULL DEFAULT CURRENT_TIMESTAMP, "user" VARCHAR(20) NOT NULL , "remotecid" VARCHAR(255) NOT NULL , "duration" INTEGER(10) DEFAULT NULL, "state" INTEGER(3) DEFAULT 0, "type" INTEGER(3) DEFAULT 0, "meta" VARCHAR(255) NOT NULL , "hidden" INTEGER(1) DEFAULT 0, "comment" VARCHAR(255) NOT NULL );';
	 $statements[] = 'CREATE TABLE "gateway" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name" VARCHAR(255) NOT NULL , "host" VARCHAR(255), "authname" VARCHAR(255), "secret" VARCHAR(255), "current_ip" VARCHAR(16), "device_name" VARCHAR(64), "expires" INTEGER, "typ" VARCHAR(10) NOT NULL , "state" VARCHAR(10), "inc_called_field" VARCHAR(20), "inc_called_search" VARCHAR(255), "inc_called_replace" VARCHAR(255));';
	 $statements[] = 'CREATE TABLE "version" ("version" INTEGER NOT NULL);';
	 $statements[] = 'DELETE FROM sqlite_sequence;';
	 $statements[] = 'COMMIT;';
	
	runStatements($filename, $statements);
	setCurrentVersion($filename, 1);
}

// version 1 exists from startup!

if ($dbversion < 2) {
	echo "Upgrading to database version 2\n";
	$statements = array();
	$statements[] = 'CREATE TABLE "testupdate" ("testupdate" INTEGER NOT NULL);';

	runStatements($filename, $statements);
        setCurrentVersion($filename, 2);
}


if ($dbversion < 3) {
        echo "Upgrading to database version 3\n";
        $statements = array();
        $statements[] = 'DROP TABLE "testupdate";';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 3);
}

if ($dbversion < 4) {
        echo "Upgrading to database version 4\n";
}

if ($dbversion < 5) {
        echo "Upgrading to database version 5\n";
        $statements = array();

        $statements[] = '
        CREATE TABLE "resourcesubscription" (
 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
 "subscriber" VARCHAR,
 "notifier_domain" VARCHAR NOT NULL ,
 "notifier" VARCHAR NOT NULL ,
 "event" VARCHAR NOT NULL,
 "notifyto" VARCHAR NOT NULL,
 "data" VARCHAR NOT NULL,
 "inserted" INTEGER NOT NULL,
 "expires" INTEGER NOT NULL);';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 5);
}


if ($dbversion < 6) {
        echo "Upgrading to database version 6\n";
        $statements = array();

        $statements[] = '
        CREATE TABLE "permissions" (
"id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,
"groupId" INTEGER NOT NULL ,
"type" VARCHAR(20) NOT NULL ,
"itemId" INTEGER NOT NULL )';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 6);
}



if ($dbversion < 7) {
        echo "Upgrading to database version 7\n";
        $statements = array();

        $statements[] = '
CREATE TABLE "routingInbound" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
"target" VARCHAR,
"pattern" INTEGER NOT NULL ,
"gw_id" INTEGER NOT NULL ,
"sort" INTEGER NOT NULL ,
"mon" BOOL,
"tue" BOOL,
"wed" BOOL,
"thu" BOOL,
"fri" BOOL,
"sat" BOOL,
"sun" BOOL,
"from_t_h" VARCHAR,
"from_t_min" VARCHAR,
"to_t_h" VARCHAR,
"to_t_min" VARCHAR)
';

	$statements[] = '
CREATE TABLE "routingOutbound" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
"usergroup" VARCHAR,
"pattern" VARCHAR NOT NULL ,
"gw_id" INTEGER NOT NULL ,
"sort" INTEGER NOT NULL ,
"mon" BOOL,
"tue" BOOL,
"wed" BOOL,
"thu" BOOL,
"fri" BOOL,
"sat" BOOL,
"sun" BOOL,
"from_t_h" VARCHAR,
"from_t_min" VARCHAR,
"to_t_h" VARCHAR,
"to_t_min" VARCHAR)
';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 7);
}

if ($dbversion < 8) {
        echo "Upgrading to database version 8\n";
        $statements = array();

$statements[] = 
'CREATE TABLE "fax" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
"user_id" INTEGER,
"filename" VARCHAR,
"date" INTEGER,
"sender" VARCHAR,
"duration" INTEGER,
"size" INTEGER,
"pages" INTEGER,
"resolution" VARCHAR,
 "state" INTEGER NOT NULL,
 "direction" INTEGER NOT NULL,
 "errortext" VARCHAR);';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 8);
}

if ($dbversion < 9) {
        echo "Upgrading to database version 9\n";
        $statements = array();

$statements[] = 
'CREATE  TABLE "usersToGroups" (
"id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,
"userId" INTEGER NOT NULL ,
"groupId" INTEGER NOT NULL );';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 9);
}


if ($dbversion < 10) {
	echo "Upgrading to database version 10\n";
    $statements = array();

$statements[] = 
'CREATE TABLE "phone_key" (
"id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"user_id" INTEGER,
"group_id" INTEGER,
"label" VARCHAR ,
"name" VARCHAR NOT NULL ,
"phone" VARCHAR NOT NULL ,
"key_idx" VARCHAR NOT NULL ,
"shift" BOOL ,
"locked" BOOL ,
"default" VARCHAR,
"function" VARCHAR,
"number_data" VARCHAR
);';

    runStatements($filename, $statements);
    setCurrentVersion($filename, 10);
}



if ($dbversion < 12) {
	echo "Upgrading to database version 12\n";
    $statements = array();

$statements[] = 
'CREATE TABLE "addressbook" (
"id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"title" VARCHAR,
"type" INTEGER NOT NULL );';

    runStatements($filename, $statements);
    setCurrentVersion($filename, 12);
}
/// FAIL VERSION
/*
if ($dbversion < 13) {
        echo "Upgrading to database version 13\n";
    $statements = array();

$statements[] = 

'DROP TABLE IF EXISTS "main"."addresses";

CREATE TABLE "addresses" (
"contact_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"contact_tid" VARCHAR(1) DEFAULT n,
"contact_addrbook_type" VARCHAR(1) DEFAULT p,
"contact_owner" INTEGER(20) NOT NULL ,
"contact_private" INTEGER(4) DEFAULT 0,
"cat_id" VARCHAR(255),
"n_family" VARCHAR(64),
"n_given" VARCHAR(64),
"n_middle" VARCHAR(64),
"n_prefix" VARCHAR(64),
"n_suffix" VARCHAR(64),
"n_fn" VARCHAR(128),
"n_fileas" VARCHAR(255),
"contact_bday" INTEGER,
"org_name" VARCHAR(128),
"org_unit" VARCHAR(64),
"contact_title" VARCHAR(64),
"contact_role" VARCHAR(64),
"contact_assistent" VARCHAR(64),
"contact_room" VARCHAR(64),
"adr_one_street" VARCHAR(64),
"adr_one_street2" VARCHAR(64),
"adr_one_locality" VARCHAR(64),
"adr_one_region" VARCHAR(64),
"adr_one_postalcode" VARCHAR(64),
"adr_one_countryname" VARCHAR(64),
"contact_label" TEXT,
"adr_two_street" VARCHAR(64),
"adr_two_street2" VARCHAR(64),
"adr_two_locality" VARCHAR(64),
"adr_two_region" VARCHAR(64),
"adr_two_postalcode" varchar(64),
"adr_two_countryname" varchar(64),
"tel_work" varchar(40),
"tel_work_ic" varchar(40),
"tel_cell" varchar(40),
"tel_cell_ic" varchar(40),
"tel_fax" varchar(40),
"tel_fax_ic" varchar(40),
"tel_assistent" varchar(40),
"tel_assistent_ic" varchar(40),
"tel_car" varchar(40),
"tel_car_ic" varchar(40),
"tel_pager" varchar(40),
"tel_pager_ic" varchar(40),
"tel_home" varchar(40),
"tel_home_ic" varchar(40),
"tel_fax_home" varchar(40),
"tel_fax_home_ic" varchar(40),
"tel_cell_private" varchar(40),
"tel_cell_private_ic" varchar(40),
"tel_other" varchar(40),
"tel_other_ic" varchar(40),
"tel_prefer" varchar(32),
"tel_prefer_ic" varchar(32),
"contact_email" varchar(128),
"contact_email_home" varchar(128),
"contact_url" varchar(128),
"contact_url_home" varchar(128),
"contact_freebusy_uri" varchar(128),
"contact_calendar_uri" varchar(128),
"contact_note" text,
"contact_tz" varchar(8),
"contact_geo" varchar(32),
"contact_pubkey" text,
"contact_created" int(20),
"contact_creator" int(11),
"contact_modified" int(20),
"contact_modifier" int(11),
"contact_jpegphoto" longblob,
"account_id" int(11),
"contact_etag" int(11) DEFAULT "0",
"contact_uid" varchar(255)
);';

    runStatements($filename, $statements);
    setCurrentVersion($filename, 13);
}
*/
if ($dbversion < 14) {
        echo "Upgrading to database version 14\n";
    $statements = array();

$statements[] = 
'
ALTER TABLE "main"."routingInbound" RENAME TO "oXHFcGcd04oXHFcGcd04_routingInbound";

CREATE TABLE "main"."routingInbound" (
"id" INTEGER PRIMARY KEY  NOT NULL ,
"target" VARCHAR,
"pattern" VARCHAR NOT NULL ,
"gw_id" INTEGER NOT NULL ,
"sort" INTEGER NOT NULL ,
"mon" BOOL,
"tue" BOOL,
"wed" BOOL,
"thu" BOOL,
"fri" BOOL,
"sat" BOOL,
"sun" BOOL,
"from_t_h" VARCHAR,
"from_t_min" VARCHAR,
"to_t_h" VARCHAR,
"to_t_min" VARCHAR
);


INSERT INTO "main"."routingInbound" SELECT
"id",
"target",
"pattern",
"gw_id",
"sort",
"mon",
"tue",
"wed",
"thu",
"fri",
"sat",
"sun",
"from_t_h",
"from_t_min",
"to_t_h",
"to_t_min"
FROM "main"."oXHFcGcd04oXHFcGcd04_routingInbound";

DROP TABLE "main"."oXHFcGcd04oXHFcGcd04_routingInbound";';

    runStatements($filename, $statements);
    setCurrentVersion($filename, 14);
}


if ($dbversion < 15) {
	echo "Upgrading to database version 15\n";
	$statements = array();

	$statements[] =
'CREATE TABLE "emails" (
"id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
"userId" INTEGER NOT NULL ,
"timestamp" INTEGER NOT NULL,
"subject" VARCHAR,
"folder" VARCHAR,
"message_id" VARCHAR,
"to_personal" VARCHAR,
"to_mailbox" VARCHAR,
"to_host" VARCHAR,
"from_personal" VARCHAR,
"from_mailbox" VARCHAR,
"from_host" VARCHAR,
"reply_to_personal" VARCHAR,
"reply_to_mailbox" VARCHAR,
"reply_to_host" VARCHAR,
"recent" VARCHAR,
"unseen" VARCHAR,
"flagged" VARCHAR,
"answered" VARCHAR,
"deleted" VARCHAR,
"draft" VARCHAR,
"msgno" VARCHAR,
"maildate" VARCHAR,
"size" VARCHAR,
"body_raw" TEXT
);';

	runStatements($filename, $statements);
	setCurrentVersion($filename, 15);
}

if ($dbversion < 16) {
        echo "Upgrading to database version 16\n";
        $statements = array();

        $statements[] =
'ALTER TABLE "main"."gateway" ADD COLUMN
 "std_prefix" VARCHAR(10) DEFAULT "0";';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 16);
}
/// FAIL VERSION
/*
if ($dbversion < 17) {
        echo "Upgrading to database version 17\n";
        $statements = array();

        $statements[] =
        'ALTER TABLE "main"."addresses" ADD COLUMN
        "xing_url" VARCHAR;';
        $statements[] =
        'ALTER TABLE "main"."addresses" ADD COLUMN
        "facebook_url" VARCHAR;';
        $statements[] =
        'ALTER TABLE "main"."addresses" ADD COLUMN
        "google+_url" VARCHAR;';
        $statements[] =
        'ALTER TABLE "main"."addresses" ADD COLUMN
        "twitter_url" VARCHAR;';
        
	runStatements($filename, $statements);
        setCurrentVersion($filename, 17);
}

if ($dbversion < 18) {
        echo "Upgrading to database version 18\n";
        $statements = array();

        $statements[] =
        'ALTER TABLE "main"."addresses" ADD COLUMN
        "id" INTEGER NOT NULL  DEFAULT "0";';

        runStatements($filename, $statements);
        setCurrentVersion($filename, 18);

}

if ($dbversion < 19) {
        echo "Upgrading to database version 19\n";
        $statements = array();

	'ALTER TABLE "main"."addresses" ALTER COLUMN contact_tid INTEGER NOT NULL DEFAULT "0";';	

        runStatements($filename, $statements);
        setCurrentVersion($filename, 19);

}
*/
if ($dbversion < 20) {
        echo "Upgrading to database version 20\n";
        $statements = array();

        $statements[] =
        'DROP TABLE IF EXISTS "main"."addresses";

        CREATE TABLE "addresses" (
        "contact_id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
        "contact_tid" INTEGER,
        "contact_addrbook_type" VARCHAR,
        "contact_owner" INTEGER(20) NOT NULL ,
        "contact_private" INTEGER(4) DEFAULT 0,
        "cat_id" VARCHAR(255),
        "n_family" VARCHAR(64),
        "n_given" VARCHAR(64),
        "n_middle" VARCHAR(64),
        "n_prefix" VARCHAR(64),
        "n_suffix" VARCHAR(64),
        "n_fn" VARCHAR(128),
        "n_fileas" VARCHAR(255),
        "contact_bday" INTEGER,
        "org_name" VARCHAR(128),
        "org_unit" VARCHAR(64),
        "contact_title" VARCHAR(64),
        "contact_role" VARCHAR(64),
        "contact_assistent" VARCHAR(64),
        "contact_room" VARCHAR(64),
        "adr_one_street" VARCHAR(64),
        "adr_one_street2" VARCHAR(64),
        "adr_one_locality" VARCHAR(64),
        "adr_one_region" VARCHAR(64),
        "adr_one_postalcode" VARCHAR(64),
        "adr_one_countryname" VARCHAR(64),
        "contact_label" TEXT,
        "adr_two_street" VARCHAR(64),
        "adr_two_street2" VARCHAR(64),
        "adr_two_locality" VARCHAR(64),
        "adr_two_region" VARCHAR(64),
        "adr_two_postalcode" varchar(64),
        "adr_two_countryname" varchar(64),
        "tel_work" varchar(40),
        "tel_work_ic" varchar(40),
        "tel_cell" varchar(40),
        "tel_cell_ic" varchar(40),
        "tel_fax" varchar(40),
        "tel_fax_ic" varchar(40),
        "tel_assistent" varchar(40),
        "tel_assistent_ic" varchar(40),
        "tel_car" varchar(40),
        "tel_car_ic" varchar(40),
        "tel_pager" varchar(40),
        "tel_pager_ic" varchar(40),
        "tel_home" varchar(40),
        "tel_home_ic" varchar(40),
        "tel_fax_home" varchar(40),
        "tel_fax_home_ic" varchar(40),
        "tel_cell_private" varchar(40),
        "tel_cell_private_ic" varchar(40),
        "tel_other" varchar(40),
        "tel_other_ic" varchar(40),
        "tel_prefer" varchar(32),
        "tel_prefer_ic" varchar(32),
        "contact_email" varchar(128),
        "contact_email_home" varchar(128),
        "contact_url" varchar(128),
        "contact_url_home" varchar(128),
        "contact_freebusy_uri" varchar(128),
        "contact_calendar_uri" varchar(128),
        "contact_note" text,
        "contact_tz" varchar(8),
        "contact_geo" varchar(32),
        "contact_pubkey" text,
        "contact_created" int(20),
        "contact_creator" int(11),
        "contact_modified" int(20),
        "contact_modifier" int(11),
        "contact_jpegphoto" longblob,
        "account_id" int(11),
        "contact_etag" int(11) DEFAULT "0",
        "contact_uid" varchar(255),

        "xing_url" VARCHAR,
        "facebook_url" VARCHAR,
        "google+_url" VARCHAR,
        "twitter_url" VARCHAR,
        "id" INTEGER NOT NULL  DEFAULT "0");';
        

        runStatements($filename, $statements);
        setCurrentVersion($filename, 20);

}
//$version = $database->exec('SELECT version from version');

//print_r($version);

//CREATE TABLE "version" ("version" INTEGER NOT NULL); 

echo "database is uptodate\n";

?>

