<?php

class Upgrade10 extends UpgradeNew
{
    // overi, jestli je opravnene provest upgrade
    public function checkRightfulness_1()
    {
        $change = true;

        $SQL = sqlQuery("SELECT 1
				FROM information_schema.TABLE_CONSTRAINTS 
				WHERE TABLE_SCHEMA='".$GLOBALS['cfg']['Connection']['database']."' AND CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='".getTableName('products_in_sections', false)."'");
        if (sqlNumRows($SQL) > 0) {
            $change = false;
        }

        return $change;
    }

    // provest samotny upgrade
    public function makeChanges_1()
    {
        // Add constraint to categories
        sqlQuery('ALTER TABLE '.getTableName('sections_relation').' ADD FOREIGN KEY ( `id_section` ) REFERENCES `sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;');

        // Add constraint to photos
        // Ensure validity
        sqlQuery('DELETE ppr.*
		FROM '.getTableName('photos_products_relation').' ppr
		LEFT JOIN '.getTableName('products').' p ON ppr.id_product=p.id
		WHERE p.id IS NULL');

        sqlQuery('ALTER TABLE '.getTableName('photos_products_relation')." CHANGE `id_product` `id_product` INT( 11 ) NOT NULL DEFAULT '0'");
        sqlQuery('ALTER TABLE '.getTableName('photos_products_relation').' ADD FOREIGN KEY ( `id_photo` ) REFERENCES `photos` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;');

        sqlQuery('ALTER TABLE '.getTableName('photos_products_relation').' ADD FOREIGN KEY ( `id_product` ) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;');

        // Products in categories
        // Ensure validity
        sqlQuery('DELETE ppr.*
		FROM '.getTableName('products_in_sections').' ppr
		LEFT JOIN '.getTableName('products').' p ON ppr.id_product=p.id
		WHERE p.id IS NULL');

        sqlQuery('ALTER TABLE '.getTableName('products_in_sections').' ENGINE = InnoDB');

        sqlQuery('ALTER TABLE '.getTableName('products_in_sections')." 
				CHANGE `id_product` `id_product` INT( 11 ) NOT NULL DEFAULT '0',
				CHANGE `id_section` `id_section` INT( 11 ) NOT NULL DEFAULT '0'");

        sqlQuery('ALTER TABLE '.getTableName('products_in_sections').' ADD FOREIGN KEY ( `id_product` ) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;');

        sqlQuery('ALTER TABLE '.getTableName('products_in_sections').' ADD FOREIGN KEY ( `id_section` ) REFERENCES `sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;');

        // Users
        $SQL = sqlQuery('ALTER TABLE '.getTableName('users').'
				DROP INDEX `login` , 
				ADD UNIQUE `login` ( `login` )');

        sqlQuery('ALTER TABLE '.getTableName('sections').' DROP INDEX `id`');

        $this->upgradeOK();
    }

    // overi, jestli je opravnene provest upgrade
    public function checkRightfulness_2()
    {
        $change = true;

        $SQL = sqlQuery('SHOW FIELDS FROM '.getTableName('order_items'));
        while ($row = sqlFetchArray($SQL)) {
            if ($row['Field'] == 'id_variation') {
                $change = false;
                break;
            }
        }

        return $change;
    }

    // provest samotny upgrade
    public function makeChanges_2()
    {
        // Add variation index to item order
        sqlQuery('ALTER TABLE '.getTableName('order_items').' CHANGE id_product id_product INT( 11 ) NULL DEFAULT NULL');

        $SQL = sqlQuery('ALTER TABLE '.getTableName('order_items').' ADD id_variation INT( 11 ) NULL DEFAULT NULL AFTER id_product;');

        sqlQuery('UPDATE '.getTableName('order_items').' oi LEFT JOIN '.getTableName('products').'p ON p.id=oi.id_product SET oi.id_product=NULL WHERE p.id IS NULL;');

        // add constrain
        sqlQuery('ALTER TABLE '.getTableName('order_items').' 
	         ADD FOREIGN KEY ( id_product ) REFERENCES products (id) ON DELETE SET NULL ON UPDATE CASCADE,
	         ADD FOREIGN KEY ( id_variation ) REFERENCES products_variations (id) ON DELETE SET NULL ON UPDATE CASCADE ;');

        $this->upgradeOK();
    }
}