<?php

namespace YSquare\Shipcloud\Core;

class Events
{
    /**
     * Execute action on activate event
     */
    public static function onActivate()
    {
        self::addMissingTablesOnUpdate();
        self::addMissingFieldsOnUpdate();
    }

    /**
     * Add missing tables if not already existent
     */
    public static function addMissingTablesOnUpdate()
    {
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();

        $oDbMetaDataHandler = oxNew('oxDbMetaDataHandler');

        if (!$oDbMetaDataHandler->tableExists("ysconfig")) {
            $oDb->execute("CREATE TABLE IF NOT EXISTS `ysconfig` (
              `OXID` char(32) character set latin1 collate latin1_general_ci NOT NULL,
              `OXSHOPID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' 
                  COMMENT 'Shop id (oxshops)',
              `YSMODULE` varchar(32) character set latin1 collate latin1_general_ci NOT NULL 
                  COMMENT 'Name of the module',
              `YSVARNAME` char(32) character set latin1 collate latin1_general_ci NOT NULL 
                  COMMENT 'Variable Name for Module',
              `YSVARVALUE` blob NOT NULL COMMENT 'Variable value',
              `OXTIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY (`OXID`)
            ) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
        }

        if (!$oDbMetaDataHandler->tableExists("ysshipcloud")) {
            $oDb->execute("CREATE TABLE IF NOT EXISTS `ysshipcloud` (
                `OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Article id',
                `OXSHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)',
                `YSCARRIER` varchar(128) NOT NULL DEFAULT '' COMMENT 'Carrier name for shipcloud label',
                `YSCARRIERSERVICE` varchar(128) NOT NULL DEFAULT '' COMMENT 'Carrier service name for shipcloud label',
                `YSPACKAGETEMPLATE` varchar(128) NOT NULL DEFAULT '' 
                    COMMENT 'Package template name for shipcloud label',
                `YSPACKAGETYPE` varchar(128) NOT NULL DEFAULT '' COMMENT 'Package type name for shipcloud label',
                `YSPACKAGEWIDTH` double NOT NULL DEFAULT '0' COMMENT 'Package dimensions: width',
                `YSPACKAGELENGTH` double NOT NULL DEFAULT '0' COMMENT 'Package dimensions: length',
                `YSPACKAGEHEIGHT` double NOT NULL DEFAULT '0' COMMENT 'Package dimensions: height',
                `YSPACKAGECONTENTDESCRIPTION` varchar(255) NOT NULL DEFAULT '' 
                    COMMENT 'Package content description for shipcloud label',
                `YSDELIVERYCONTENTDESCRIPTION` varchar(255) NOT NULL DEFAULT '' 
                    COMMENT 'Package delivery content description for shipcloud label',
                `YSREFERENCENUMBER` varchar(128) NOT NULL DEFAULT '' 
                    COMMENT 'Package reference number for shipcloud label',
                `YSSHIPCLOUDID` varchar(128) NOT NULL DEFAULT '' COMMENT 'Package shipcloud id',
                `YSSHIPCLOUDTRACKINGNUMBER` varchar(128) NOT NULL DEFAULT '' 
                    COMMENT 'Package shipcloud tracking number for carrier',
                `YSSHIPCLOUDDELIVERYCOSTS` varchar(128) NOT NULL DEFAULT '' COMMENT 'Package shipcloud delivery costs',
                `YSSHIPCLOUDLABELURL` varchar(128) NOT NULL DEFAULT '' COMMENT 'Package shipcloud label url',
                PRIMARY KEY (`OXID`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Shipcloud label information';");
        }

        if (!$oDbMetaDataHandler->tableExists("ysshipcloud2orderarticles")) {
            $oDb->execute("CREATE TABLE IF NOT EXISTS `ysshipcloud2orderarticles` (
                `OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Record id',
                `OXSHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)',
                `OXOARTID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' 
                    COMMENT 'Orderarticle id (oxorderarticles)',
                `YSSHCLID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' 
                    COMMENT 'Shipcloud id (oxshipcloud)',
                `OXAMOUNT` double NOT NULL DEFAULT '0' COMMENT 'Amount of oxorderarticles',
                `OXTIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
                    COMMENT 'Timestamp',
                PRIMARY KEY (`OXID`),
                KEY `OXSHOPID` (`OXSHOPID`),
                KEY `OXARTID` (`OXOARTID`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
    COMMENT='Shows one-to-many relationship between shipcloud and orderarticles';");
        }

        if (!$oDbMetaDataHandler->tableExists("ysshipcloudaddress")) {
            $oDb->execute("CREATE TABLE IF NOT EXISTS `ysshipcloudaddress` (
                OXID char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Record id',
               `OXSHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)',
                ADDRESSNAME varchar(255) default '' not null comment 'Address name',                
                OXCOMPANY varchar(255) default '' not null comment 'Company name',
                OXFNAME varchar(255) default '' not null comment 'First name',
                OXLNAME varchar(255) default '' not null comment 'Last name',
                OXSTREET varchar(255) default '' not null comment 'Street',
                OXSTREETNR varchar(16) default '' not null comment 'House number',
                OXADDINFO varchar(255) default '' not null comment 'Additional info',
                OXCITY varchar(255) default '' not null comment 'City',
                OXCOUNTRY varchar(255) default '' not null comment 'Country name',
                OXCOUNTRYID char(32) collate latin1_general_ci default '' not null comment 'Country id (oxcountry)',
                OXSTATEID varchar(32) collate latin1_general_ci default '' not null comment 'State id (oxstate)',
                OXZIP varchar(50) default '' not null comment 'Zip code',
                OXFON varchar(128) default '' not null comment 'Phone number',
                OXFAX varchar(128) default '' not null comment 'Fax number',
                OXSAL varchar(128) default '' not null comment 'User title prefix (Mr/Mrs)',
                OXTIMESTAMP timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP 
                    comment 'Timestamp',
                primary key (OXID)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Shipcloud addresses';");
        }
    }

    /**
     * Add missing field if it activates on old DB
     */
    public static function addMissingFieldsOnUpdate()
    {
        $aTableFields = [
            [
                'table'     => "ysshipcloudaddress",
                'fieldname' => "OXSHOPID",
                'fieldtype' => "int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSSHIPCLOUDSENDMAIL",
                'fieldtype' => "tinyint(1) NOT NULL DEFAULT '0' 
                COMMENT 'Package shipcloud send mail after label bought'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSSHIPCLOUDADVANCENOTICE",
                'fieldtype' => "tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Package shipcloud advance notice'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSSHIPCLOUDSENDSHIPCLOUDMAIL",
                'fieldtype' => "tinyint(1) NOT NULL DEFAULT '0' 
                COMMENT 'Package shipcloud send shipcloud mail after label bought'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSPACKAGEWEIGHT",
                'fieldtype' => "double NOT NULL DEFAULT '0' COMMENT 'Package weight'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSSENDMAILDATE",
                'fieldtype' => "datetime default '0000-00-00 00:00:00' not null comment 'Order send mail date'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSPICKUPTIME",
                'fieldtype' => "char(64) default '' not null comment 'Shipcloud pickup time range'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "OXTIMESTAMP",
                'fieldtype' => "timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'Timestamp'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSTRACKINGURL",
                'fieldtype' => "varchar(128) NOT NULL DEFAULT '' COMMENT 'Package tracking url'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSPACKAGEADDITIONALWEIGHT",
                'fieldtype' => "double NOT NULL DEFAULT '0' COMMENT 'Package additional weight'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSPICKUPADDRESS",
                'fieldtype' => "varchar(1024) default '' not null comment 'Pickup Address html'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YSPICKUPNUMBER",
                'fieldtype' => "varchar(128) default '' not null comment 'Pickup number'",
            ],
            [
                'table'     => "ysshipcloud",
                'fieldname' => "YS_RETURN_ADDRESS",
                'fieldtype' => "char(32) comment 'Return address for label'",
            ],
            [
                'table'     => "ysshipcloudaddress",
                'fieldname' => "YS_PICKUP_ADDRESS",
                'fieldtype' => "tinyint(1) NOT NULL DEFAULT '0'",
            ],
            [
                'table'     => "ysshipcloudaddress",
                'fieldname' => "YS_RETURN_ADDRESS",
                'fieldtype' => "tinyint(1) NOT NULL DEFAULT '0'",
            ],
        ];
        self::executeTableFieldAddition($aTableFields);
    }

    public static function executeTableFieldAddition($aFieldsToAdd)
    {
        $oDbMetaDataHandler = oxNew('oxDbMetaDataHandler');
        $oDb = \OxidEsales\Eshop\Core\DatabaseProvider::getDb();

        if (is_array($aFieldsToAdd) && is_array($aFieldsToAdd[0])) {
            // This case covers an array with several fields
            foreach ($aFieldsToAdd as $aField) {
                if (!$oDbMetaDataHandler->fieldExists($aField['fieldname'], $aField['table'])) {
                    $oDb->execute(
                        "ALTER TABLE `" . $aField['table']
                        . "` ADD `" . $aField['fieldname'] . "` " . $aField['fieldtype'] . ";"
                    );
                }
            }
        } elseif (is_array($aFieldsToAdd) && isset($aFieldsToAdd['table'])) {
            // This case covers one field given alone
            if (!$oDbMetaDataHandler->fieldExists($aFieldsToAdd['fieldname'], $aFieldsToAdd['table'])) {
                $oDb->execute(
                    "ALTER TABLE `" . $aFieldsToAdd['table']
                    . "` ADD `" . $aFieldsToAdd['fieldname'] . "` " . $aFieldsToAdd['fieldtype'] . ";"
                );
            }
        }
    }

    /**
     * Execute action on deactivate event
     */
    public static function onDeactivate()
    {
    }
}
