<?php
/*
* Copyright (c) 2009-2010 ATMAIL. All rights reserved
* See http://atmail.com/license.php for license agreement
*/

// NOTE:
// This script will not upgrade you to the full commercial version of
// Atmail, you still need to purchase and download the commercial Atmail
// code. This script simply makes necessary adjustments to the database
// structure after which you can run the commercial Atmail installer.

// Do not allow to run from a http request, must be CLI
if ($_SERVER['GATEWAY_INTERFACE'] || $_SERVER['REQUEST_METHOD']) {
    die("This script can only be run via the command line interface");
}

if (!isset($_SERVER['argv'][1]) || !isset($_SERVER['argv'][2]) || !isset($_SERVER['argv'][3])) {
    die("Usage: php migrate-ao-a5.php [atmail-open-database-name] [user] [password]\n");    
}

$dbName = $_SERVER['argv'][1];
$dbUser = $_SERVER['argv'][2];
$dbPass = $_SERVER['argv'][3];

define('APP_ROOT', dirname(__FILE__) . DIRECTORY_SEPARATOR);
set_include_path(APP_ROOT . "../../library/" . PATH_SEPARATOR . APP_ROOT . "../../library/Atmail" . PATH_SEPARATOR . "library/" . PATH_SEPARATOR . get_include_path());

require_once 'Zend/Loader/Autoloader.php';
$loader = Zend_Loader_Autoloader::getInstance();
//$loader->registerNamespace('App_'); //for model namespacing
$loader->setFallbackAutoloader(true);
$loader->suppressNotFoundWarnings(false);

$pdoParams = array(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
);

$params = array(
    'host'           => 'localhost',
    'username'       => $dbUser,
    'password'       => $dbPass,
    'dbname'         => $dbName,
    'driver_options' => $pdoParams
);

$db = Zend_Db::Factory('Pdo_Mysql', $params, $pdoParams);

try {
    $db->getConnection();
} catch (Exception $e) {
    die("Could not connect to database $dbName\n");    
}

$alpha = range('a', 'z');
$alpha[] = 'other';

fwrite(STDOUT, "Making required database modifications... ");

foreach ($alpha as $a) {
    
    try {
        $db->query("alter table Abook_$a add `UserPhoto` mediumtext");
    } catch(Exception $e) {
        $errors[] = $e;    
    }
    
    try {
        $db->query("alter table Abook_$a add `UserFileAs` varchar(255) default null");
    } catch(Exception $e) {
        $errors[] = $e;    
    }
    
    try {
        $db->query("CREATE TABLE `Calander_$a` (
            `Importance` smallint(1) default NULL,
            `UserTo` varchar(64) default NULL,
            `UserFrom` varchar(64) default NULL,
            `Title` varchar(255) default NULL,
            `DatePost` timestamp NOT NULL,
            `CalMessage` text,
            `Alert` smallint(3) default NULL,
            `DateStart` datetime NOT NULL default '0000-00-00 00:00:00',
            `DateEnd` datetime default NULL,
            `id` mediumint(8) unsigned NOT NULL auto_increment,
            `Type` varchar(16) default NULL,
            `Task` smallint(1) default NULL,
            `Ref` smallint(6) default NULL,
            `Parent` tinyint(1) default NULL,
            `Permission` tinyint(1) default NULL,
            `EntryID` varchar(64) default NULL,
            `DateModified` varchar(12) default NULL,
            `Location` varchar(64) default NULL,
            `AllDayEvent` smallint(1) default NULL,
            `DateAlert` datetime default NULL,
            `IsRecurring` int(11) default NULL,
            `IsException` int(11) default NULL,
            `DateAlertPeriod` mediumint(8) unsigned default '60',
            `SMSalert` int(1) default NULL,
            `Availability` varchar(16) default 'Busy',
            `CalNameID` mediumint(8) unsigned default NULL,
            PRIMARY KEY  (`id`),
            KEY `iUserTo` (`UserTo`)
        )");
    } catch (Exception $e) {
        $errors[] = $e;
    }
    
    try {
        $db->query("CREATE TABLE `EmailDatabase_$a` (
            `EmailSubject` varchar(164) default NULL,
            `EmailFrom` varchar(164) default NULL,
            `EmailTo` varchar(64) default NULL,
            `EmailModified` timestamp NOT NULL,
            `EmailDate` timestamp NOT NULL default '0000-00-00 00:00:00',
            `EmailBox` varchar(64) default NULL,
            `EmailFlag` char(1) default NULL,
            `EmailAttach` char(1) default NULL,
            `id` bigint(12) unsigned NOT NULL auto_increment,
            `Account` varchar(128) NOT NULL default '',
            `EmailUIDL` varchar(70) default NULL,
            `EmailSize` int(10) unsigned default NULL,
            `FlagSeen` tinyint(1) NOT NULL default '0',
            `FlagAnswered` tinyint(1) NOT NULL default '0',
            `FlagDeleted` tinyint(1) NOT NULL default '0',
            `FlagFlagged` tinyint(1) NOT NULL default '0',
            `FlagRecent` tinyint(1) NOT NULL default '0',
            `FlagDraft` tinyint(1) NOT NULL default '0',
            `FlagInferiors` tinyint(1) NOT NULL default '0',
            `FlagSelect` tinyint(1) NOT NULL default '0',
            PRIMARY KEY  (`id`),
            KEY `iAccount` (`Account`),
            KEY `EmailUIDL` (`EmailUIDL`)
        )");
    } catch (Exception $e) {
        $errors[] = $e;
    }
    
    try {
        $db->query("CREATE TABLE `EmailMessage_$a` (
            `EmailMessage` longtext,
            `id` bigint(12) unsigned NOT NULL auto_increment,
            `EmailText` mediumtext,
            PRIMARY KEY  (`id`),
            FULLTEXT KEY `EmailText` (`EmailText`)
        )");
    } catch (Exception $e) {
        $errors[] = $e;
    }
    
    try {
        $db->query("CREATE TABLE `EmailUIDL_$a` (
            `EmailUIDL` varchar(70) NOT NULL default '',
            `EmailType` char(1) default NULL,
            `Account` varchar(128) NOT NULL default '',
            KEY `iEmailUIDL` (`EmailUIDL`)
        )");
    } catch (Exception $e) {
        $errors[] = $e;
    }

    try {
        $db->query("CREATE TABLE `SpamDB_$a` (
            `SpamEmail` varchar(64) NOT NULL default '',
            `Account` varchar(128) default NULL,
            KEY `iSpamEmail` (`SpamEmail`)
        )");
    } catch (Exception $e) {
        $errors[] = $e;
    }

    try {
        $db->query("CREATE TABLE `UserPgp_$a` (
            `Account` varchar(128) NOT NULL default '',
            `pubring` mediumblob,
            `secring` mediumblob,
            `trustdb` mediumblob,
            `public` blob,
            PRIMARY KEY  (`Account`)
        )");
    } catch (Exception $e) {
        $errors[] = $e;
    }
}

try {
    $db->query("CREATE TABLE `AdminGroup` (
        `Username` varchar(64) NOT NULL default '',
        `Ugroup` varchar(32) default NULL,
        `Domain` varchar(64) default NULL,
        `id` mediumint(8) unsigned NOT NULL auto_increment,
        PRIMARY KEY  (`id`),
        KEY `iUsername` (`Username`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `AdminUsers` (
        `Username` varchar(64) NOT NULL default '',
        `Password` varchar(64) NOT NULL default '',
        `UAdd` tinyint(1) default NULL,
        `UDelete` tinyint(1) default NULL,
        `UModify` tinyint(1) default NULL,
        `UPurge` tinyint(1) default NULL,
        `USearch` tinyint(1) default NULL,
        `UList` tinyint(1) default NULL,
        `UMigrate` tinyint(1) default NULL,
        `ULogs` tinyint(1) default NULL,
        `UAlias` tinyint(1) default NULL,
        `id` mediumint(8) unsigned NOT NULL auto_increment,
        `NumUsers` mediumint(6) unsigned default NULL,
        `Company` varchar(64) default NULL,
        `Fullname` varchar(64) default NULL,
        `DateCreate` datetime default NULL,
        `SessionID` varchar(32) default NULL,
        `LastLogin` int(10) unsigned default NULL,
        `NumQuota` mediumint(6) unsigned default NULL,
        `UBrand` tinyint(1) default NULL,
        `BrandDomain` varchar(128) default NULL,
        `UGroupAssign` tinyint(1) default NULL,
        `UAll` tinyint(1) default NULL,
        PRIMARY KEY  (`id`),
        KEY `iUsername` (`Username`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `CalendarNames` (
        `CalName` varchar(64) default NULL,
        `id` mediumint(12) unsigned NOT NULL auto_increment,
        PRIMARY KEY  (`id`),
        KEY `iCalName` (`CalName`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `CalendarPermissions` (
        `CalID` mediumint(8) unsigned default NULL,
        `Account` varchar(128) NOT NULL default '',
        `Permissions` smallint(1) default NULL,
        `Type` varchar(6) default NULL,
        `id` bigint(10) unsigned NOT NULL auto_increment,
        `Domain` varchar(64) default NULL,
        `CalNameID` mediumint(8) unsigned default NULL,
        PRIMARY KEY  (`id`),
        KEY `iAccount` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `ClientBilling` (
        `Account` varchar(128) NOT NULL default '',
        `CardNumber` varchar(64) default NULL,
        `CardName` varchar(64) default NULL,
        `CardExpMonth` varchar(4) default NULL,
        `CardExpYear` varchar(4) default NULL,
        `PaymentStatus` tinyint(4) default NULL,
        `DateSignup` datetime default NULL,
        `DatePaid` datetime default NULL,
        `Amount` mediumint(8) unsigned default NULL,
        `id` mediumint(8) unsigned NOT NULL auto_increment,
        `Service` varchar(32) default NULL,
        PRIMARY KEY  (`id`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `ClientPayment` (
        `Account` varchar(128) NOT NULL default '',
        `Amount` mediumint(8) unsigned default NULL,
        `PaymentStatus` tinyint(4) default NULL,
        `DatePaid` datetime default NULL,
        `id` mediumint(8) unsigned NOT NULL auto_increment,
        PRIMARY KEY  (`id`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `Config` (
      `configId` int(11) NOT NULL auto_increment,
      `section` varchar(255) NOT NULL,
      `key` varchar(255) NOT NULL,
      `value` varchar(255) NOT NULL,
      `type` varchar(255) NOT NULL,
      PRIMARY KEY  (`configId`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `Domains` (
      `Hostname` varchar(255) NOT NULL default '',
      `Enable` char(1) default NULL,
      PRIMARY KEY  (`Hostname`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `Folders` (
        `id` bigint(12) unsigned NOT NULL auto_increment,
        `Account` varchar(128) NOT NULL default '',
        `FolderName` varchar(64) NOT NULL default '',
        `FolderSize` smallint(6) unsigned default NULL,
        `FolderMsgs` smallint(5) unsigned default NULL,
        `FlagSeen` tinyint(1) NOT NULL default '0',
        `FlagAnswered` tinyint(1) NOT NULL default '0',
        `FlagDeleted` tinyint(1) NOT NULL default '0',
        `FlagFlagged` tinyint(1) NOT NULL default '0',
        `FlagRecent` tinyint(1) NOT NULL default '0',
        `FlagDraft` tinyint(1) NOT NULL default '0',
        `FlagInferiors` tinyint(1) NOT NULL default '0',
        `FlagSelect` tinyint(1) NOT NULL default '0',
        `Permission` tinyint(1) default '1',
        `Subscribe` tinyint(1) NOT NULL default '0',
        `ParentID` bigint(12) unsigned default NULL,
        `IMAP` tinyint(1) NOT NULL default '0',
        PRIMARY KEY  (`id`),
        KEY `iAccount` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `Groups` (
        `GroupName` varchar(64) NOT NULL default '',
        `GroupDescription` varchar(64) default NULL,
        `GroupPrice` varchar(5) default NULL,
        `GroupQuota` mediumint(8) unsigned default NULL,
        `MailForwarding` int(1) default NULL,
        `SMSSupport` int(1) default NULL,
        `POP3Support` int(1) default NULL,
        `IMAPSupport` int(1) default NULL,
        `MultiSupport` int(1) default NULL,
        `PersonalSpam` int(1) default NULL,
        `GlobalAbookRead` int(1) default NULL,
        `GlobalAbook` int(1) default NULL,
        `AV` int(1) default NULL,
        `SpamSupport` int(1) default NULL,
        `Sync` int(1) default NULL,
        `PushSupport` int(1) default NULL,
        PRIMARY KEY  (`GroupName`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `MailAliases` (
      `AliasName` varchar(200) default NULL,
      `AliasTo` varchar(200) default NULL,
      `Domain` varchar(128) default NULL,
      `DateCreate` datetime default NULL,
      `id` bigint(20) unsigned NOT NULL auto_increment,
      `AliasMailDir` varchar(200) default NULL,
      PRIMARY KEY  (`id`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try{
    $db->query("CREATE TABLE `MailRelay` (
      `IPaddress` varchar(15) NOT NULL default '',
      `DateAdded` timestamp NOT NULL,
      `Account` varchar(128) default NULL,
      PRIMARY KEY  (`IPaddress`),
      KEY `DateAdded` (`DateAdded`),
      KEY `Account` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {
    $db->query("CREATE TABLE `SMSCredits` (
      `Account` varchar(128) default NULL,
      `Credit` float unsigned default NULL,
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`),
      KEY `Account` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `SMSfilter` (
      `Account` varchar(128) NOT NULL default '',
      `EmailSubject` varchar(128) default NULL,
      `EmailSubjectType` char(2) default NULL,
      `EmailFrom` varchar(128) default NULL,
      `EmailFromType` char(2) default NULL,
      `EmailPriority` char(1) default NULL,
      `EmailAll` char(1) default NULL,
      `MobileNumber` varchar(16) default NULL,
      `Matches` mediumint(8) unsigned default NULL,
      `DateCreated` datetime default NULL,
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`),
      KEY `iAccount` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `SMSqueue` (
      `AlertNumber` varchar(128) default NULL,
      `AlertModified` timestamp NOT NULL,
      `AlertDate` varchar(11) default NULL,
      `AlertMessage` varchar(255) default NULL,
      `AlertStatus` char(1) default NULL,
      `Account` varchar(128) default NULL,
      `id` mediumint(8) unsigned NOT NULL default '0',
      PRIMARY KEY  (`id`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `SMSsent` (
      `Account` varchar(128) NOT NULL default '',
      `SMSFrom` varchar(128) default NULL,
      `SMSTo` varchar(128) default NULL,
      `SMSStatus` varchar(8) default NULL,
      `SMSmessage` varchar(255) default NULL,
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      `SMSid` varchar(64) default NULL,
      `SMSdate` datetime default NULL,
      PRIMARY KEY  (`id`),
      KEY `iAccount` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `SerialConf` (
      `Name` varchar(64) default NULL,
      `Value` mediumtext,
      `Account` varchar(128) default NULL,
      `id` mediumint(8) unsigned NOT NULL auto_increment,
      `DateAdded` datetime default NULL,
      PRIMARY KEY  (`id`),
      KEY `iAccount` (`Account`),
      KEY `iName` (`Name`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `SharedLookup` (
      `Type` varchar(8) default NULL,
      `EntryID` varchar(64) default NULL,
      `LookupID` bigint(10) unsigned NOT NULL default '0',
      `id` bigint(10) unsigned NOT NULL auto_increment,
      `Account` varchar(128) default NULL,
      `DateModified` varchar(12) default NULL,
      PRIMARY KEY  (`id`),
      KEY `LookupID` (`LookupID`),
      KEY `Account` (`Account`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `SpamSettings` (
      `username` varchar(255) default NULL,
      `preference` varchar(30) NOT NULL default '',
      `value` varchar(100) NOT NULL default '',
      `prefid` int(11) NOT NULL auto_increment,
      `domain` varchar(128) default NULL,
      PRIMARY KEY  (`prefid`),
      KEY `username` (`username`),
      KEY `preference` (`preference`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `awl` (
      `username` varchar(100) NOT NULL default '',
      `email` varchar(200) NOT NULL default '',
      `ip` varchar(10) NOT NULL default '',
      `count` int(11) default '0',
      `totscore` float default '0',
      `IndexDate` timestamp NOT NULL,
      PRIMARY KEY  (`username`,`email`,`ip`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

try {    
    $db->query("CREATE TABLE `recurrencePatterns` (
      `id` int(11) NOT NULL auto_increment,
      `account` varchar(128) default NULL,
      `RecurrenceType` int(11) default NULL,
      `PatternStartDate` datetime default NULL,
      `PatternEndDate` datetime default NULL,
      `NoEndDate` int(11) default NULL,
      `Occurences` int(11) default NULL,
      `skipInterval` int(11) default NULL,
      `DayOfWeekMask` int(11) default NULL,
      `DayOfMonth` int(11) default NULL,
      `Instance` int(11) default NULL,
      `Duration` int(11) default NULL,
      `MonthOfYear` int(11) default NULL,
      `linkid` int(11) default NULL,
      `IsSimple` int(11) default NULL,
      PRIMARY KEY  (`id`)
    )");
} catch (Exception $e) {
    $errors[] = $e;
}

fwrite(STDOUT, "Done\n\nYou should now run the A5 > A6 migrate script as such: php migrate-a5-a6.php $dbName [a6-dbname] [mysql-user] [mysql-pass] [mysql-host]\n\n");
