<?php

class StoresUpgrade extends UpgradeNew
{
    public function check_StoresTableExists()
    {
        return $this->checkTableExists('stores');
    }

    /** Create stores table */
    public function upgrade_StoresTableExists()
    {
        sqlQuery('CREATE TABLE stores (
                  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                  name varchar(30) NOT NULL, 
                  id_delivery int(11), 
                  CONSTRAINT stores_delivery_type_delivery_id_fk 
                  FOREIGN KEY (id_delivery) REFERENCES delivery_type_delivery (id) 
                  ON DELETE SET NULL ON UPDATE CASCADE)');

        $this->upgradeOK();
    }

    public function check_StoresItemsTableExists()
    {
        return $this->checkTableExists('stores_items');
    }

    /** Create stores_items table */
    public function upgrade_StoresItemsTableExists()
    {
        sqlQuery('CREATE TABLE stores_items (
                  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                  id_store int(11) NOT NULL, 
                  id_product int(11) NOT NULL, 
                  id_variation int(11), 
                  quantity int(11) NOT NULL, 
                  CONSTRAINT stores_items_stores_id_fk FOREIGN KEY (id_store) REFERENCES stores (id) ON UPDATE CASCADE ON DELETE CASCADE, 
                  CONSTRAINT stores_items_products_id_fk FOREIGN KEY (id_product) REFERENCES products (id) ON UPDATE CASCADE ON DELETE CASCADE, 
                  CONSTRAINT stores_items_products_variations_id_fk FOREIGN KEY (id_variation) REFERENCES products_variations (id) ON UPDATE CASCADE ON DELETE CASCADE)');

        sqlQuery('CREATE UNIQUE INDEX stores_items_id_store_id_product_id_variation_pk 
                  ON stores_items (id_store, id_product, id_variation)');

        $this->upgradeOK();
    }

    public function check_StoresTransfersTableExists()
    {
        return $this->checkTableExists('stores_transfers');
    }

    /** Create stores_transfers table */
    public function upgrade_StoresTransfersTableExists()
    {
        sqlQuery('CREATE TABLE stores_transfers (
                  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                  id_store_from int(11) NOT NULL, 
                  id_store_to int(11) NOT NULL, 
                  date_created datetime NOT NULL, 
                  state enum("O", "S", "R") DEFAULT "O" NOT NULL, 
                  note varchar(50), 
                  CONSTRAINT stores_transfers_stores_id_from_fk FOREIGN KEY (id_store_from) REFERENCES stores (id) ON UPDATE CASCADE, 
                  CONSTRAINT stores_transfers_stores_id_to_fk FOREIGN KEY (id_store_to) REFERENCES stores (id) ON UPDATE CASCADE)');

        $this->upgradeOK();
    }

    public function check_idStoreOnDeleteCascade()
    {
        return $this->checkConstraintRule('stores_transfers', 'stores_transfers_stores_id_from_fk', 'CASCADE')
            || $this->checkConstraintRule('stores_transfers', 'stores_transfers_stores_id_to_fk', 'CASCADE');
    }

    /** Add ON DELETE CASCADE on stores_transfers foreign keys */
    public function upgrade_idStoreOnDeleteCascade()
    {
        try {
            sqlQuery('ALTER TABLE stores_transfers DROP FOREIGN KEY stores_transfers_stores_id_from_fk');
            sqlQuery('ALTER TABLE stores_transfers DROP FOREIGN KEY stores_transfers_stores_id_to_fk');
        } catch (\Throwable $e) {
        }

        sqlQuery('ALTER TABLE stores_transfers ADD CONSTRAINT stores_transfers_stores_id_from_fk FOREIGN KEY (id_store_from) REFERENCES stores (id) ON UPDATE CASCADE ON DELETE CASCADE');
        sqlQuery('ALTER TABLE stores_transfers ADD CONSTRAINT stores_transfers_stores_id_to_fk FOREIGN KEY (id_store_to) REFERENCES stores (id) ON UPDATE CASCADE ON DELETE CASCADE');

        $this->upgradeOK();
    }

    public function check_StoresTransfersItemsTableExists()
    {
        return $this->checkTableExists('stores_transfers_items');
    }

    /** Create stores_transfers_items table */
    public function upgrade_StoresTransfersItemsTableExists()
    {
        sqlQuery('CREATE TABLE stores_transfers_items (
                  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, 
                  id_stores_transfer int(11) NOT NULL, 
                  id_product int(11) NOT NULL, 
                  id_variation int(11) DEFAULT 0 NOT NULL, 
                  quantity int(11) NOT NULL, 
                  CONSTRAINT stores_transfers_items_stores_transfers_id_fk FOREIGN KEY (id_stores_transfer) REFERENCES stores_transfers (id) ON UPDATE CASCADE ON DELETE CASCADE, 
                  CONSTRAINT stores_transfers_items_products_id_fk FOREIGN KEY (id_product) REFERENCES products (id) ON UPDATE CASCADE)');

        sqlQuery('CREATE UNIQUE INDEX stores_transfers_items_id_transfer_id_product_id_variation_pk ON stores_transfers_items (id_stores_transfer, id_product, id_variation)');

        $this->upgradeOK();
    }

    public function check_StoresTransfersItemsCustomData()
    {
        return $this->checkColumnExists('stores_transfers_items', 'custom_data');
    }

    /** Add custom_data field into stores_transfers_items table */
    public function upgrade_StoresTransfersItemsCustomData()
    {
        sqlQuery("alter table stores_transfers_items
            add custom_data text default '' null;
        
        ");

        $this->upgradeOK();
    }

    public function check_idProductOnDeleteCascade()
    {
        return $this->checkConstraintRule('stores_transfers_items', 'stores_transfers_items_products_id_fk', 'CASCADE');
    }

    /** Add ON DELETE CASCADE on stores_transfers_items_products_id_fk */
    public function upgrade_idProductOnDeleteCascade()
    {
        try {
            sqlQuery('ALTER TABLE stores_transfers_items DROP FOREIGN KEY stores_transfers_items_products_id_fk');
        } catch (\Throwable $e) {
        }

        sqlQuery('ALTER TABLE stores_transfers_items ADD CONSTRAINT stores_transfers_items_products_id_fk FOREIGN KEY (id_product) REFERENCES products (id) ON UPDATE CASCADE ON DELETE CASCADE');

        $this->upgradeOK();
    }

    public function check_StoreTransferState()
    {
        return findModule(Modules::WAREHOUSE) && $this->checkColumnExists('stores_transfers', 'checkouts');
    }

    /** Create stores_transfers checkouts */
    public function upgrade_StoreTransferState()
    {
        sqlQuery('
                ALTER TABLE stores_transfers ADD checkouts TEXT DEFAULT NULL;
                ALTER TABLE stores_transfers ADD id_position INT(11) DEFAULT NULL;
                ALTER TABLE stores_transfers ADD CONSTRAINT stores_transfers_position_id_fk FOREIGN KEY (id_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE ON UPDATE CASCADE;
        ');

        $this->upgradeOK();
    }

    public function check_StoreTransferBasePosition()
    {
        return findModule(Modules::WAREHOUSE) && $this->checkColumnExists('stores', 'id_base_position');
    }

    /** Create stores id_base_position */
    public function upgrade_StoreTransferBasePosition()
    {
        sqlQuery('
               ALTER TABLE stores ADD id_base_position  INT(11) DEFAULT NULL;
               ALTER TABLE stores ADD CONSTRAINT stores_transfers_base_position_id_fk FOREIGN KEY (id_base_position) REFERENCES warehouse_positions (id) ON DELETE SET NULL ON UPDATE CASCADE;
        ');

        $this->upgradeOK();
    }

    public function check_StoreReturnPosition()
    {
        return findModule(Modules::WAREHOUSE) && $this->checkColumnExists('stores', 'id_return_table');
    }

    /** Create stores id_base_position */
    public function upgrade_StoreReturnPosition()
    {
        sqlQuery('
               ALTER TABLE stores ADD id_return_table  INT(11) DEFAULT NULL;
               ALTER TABLE stores ADD CONSTRAINT stores_transfers_return_table_id_fk FOREIGN KEY (id_return_table) REFERENCES warehouse_positions (id) ON DELETE SET NULL ON UPDATE CASCADE;
        ');

        $this->upgradeOK();
    }

    public function check_StoresTypeColumn()
    {
        return $this->checkColumnExists('stores', 'type');
    }

    /** Add column `type` to `stores` table */
    public function upgrade_StoresTypeColumn()
    {
        $this->insertSQL('stores', ['id' => 1, 'name' => 'Hlavní sklad']);

        sqlQuery('
                ALTER TABLE `stores` ADD `type` INT DEFAULT 0 NOT NULL;
                UPDATE `stores` SET `type` = 1 WHERE `id` = 1;
        ');

        $this->upgradeOK();
    }

    public function check_min_quantity()
    {
        return $this->checkColumnExists('stores_items', 'min_quantity');
    }

    /** Add min_quantity field into stores_items table */
    public function upgrade_min_quantity()
    {
        sqlQuery('ALTER TABLE stores_items ADD min_quantity  INT(11) DEFAULT NULL;');
    }

    public function check_storesFigure()
    {
        return $this->checkColumnExists('stores', 'figure');
    }

    /** Stores - add figure field to hide stores */
    public function upgrade_storesFigure()
    {
        sqlQuery("ALTER TABLE stores ADD figure ENUM('Y','N') NOT NULL DEFAULT 'Y'");
        sqlQuery('UPDATE stores SET figure="Y"');

        $this->upgradeOK();
    }

    public function check_StoresDataColumn(): bool
    {
        return $this->checkColumnExists('stores', 'data');
    }

    /** stores: add data column */
    public function upgrade_StoresDataColumn(): void
    {
        sqlQuery('ALTER TABLE stores ADD COLUMN data MEDIUMTEXT DEFAULT NULL AFTER figure');

        $this->upgradeOK();
    }

    public function check_storeTransfersIdVariationFix(): bool
    {
        return $this->checkColumnIsNull('stores_transfers_items', 'id_variation', true);
    }

    /** stores transfers items: fix id_variation */
    public function upgrade_storeTransfersIdVariationFix(): void
    {
        sqlQuery('ALTER TABLE stores_transfers_items MODIFY COLUMN id_variation int(11) DEFAULT NULL');
        sqlQuery('UPDATE stores_transfers_items ssi set ssi.id_variation = (select pv.id from products_variations pv where pv.id=ssi.id_variation)');
        sqlQuery('ALTER TABLE stores_transfers_items ADD CONSTRAINT stores_transfers_items_variations_id_fk FOREIGN KEY (id_variation) REFERENCES products_variations (id) ON DELETE SET NULL ON UPDATE CASCADE;');

        $this->upgradeOK();
    }

    public function check_storesItemsVirtualColumn()
    {
        return findModule(Modules::STORES) && $this->checkConstraintExists('stores_items', 'integrity_unique');
    }

    /** stores_items add virtual column (id_store, id_product, id_variation) */
    public function upgrade_storesItemsVirtualColumn()
    {
        try {
            sqlQuery('ALTER TABLE stores_items ADD integrity_unique varchar(50) as (CONCAT_WS("-", `id_store`, `id_product`, `id_variation`)) VIRTUAL');
        } catch (Exception $e) {
            // Ignore exception if column already exists - caused by failing DELETE statement
        }
        sqlQuery('DELETE si FROM stores_items si WHERE si.id NOT IN (SELECT t.* FROM (SELECT MAX(si2.id) FROM stores_items si2 GROUP BY si2.integrity_unique) t)');
        sqlQuery('ALTER TABLE stores_items ADD UNIQUE integrity_unique(integrity_unique)');
        $this->upgradeOK();
    }

    public function check_storesPositionColumn(): bool
    {
        return $this->checkColumnExists('stores', 'position');
    }

    /** Add stores.position column */
    public function upgrade_storesPositionColumn(): void
    {
        sqlQuery('ALTER TABLE stores ADD position INT(11) DEFAULT NULL AFTER id;');
        sqlQuery('UPDATE stores SET position = (id - 1) WHERE position IS NULL;');

        $this->upgradeOK();
    }

    public function check_SellersStoreIdColumn(): bool
    {
        return findModule(\Modules::SELLERS) && $this->checkColumnExists('sellers', 'id_store');
    }

    /** Add sellers.id_store column */
    public function upgrade_SellersStoreIdColumn(): void
    {
        sqlQuery('ALTER TABLE sellers ADD COLUMN id_store INT(11) DEFAULT NULL AFTER id;');
        sqlQuery('ALTER TABLE sellers ADD FOREIGN KEY (id_store)
              REFERENCES stores(id) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }

    public function check_triggerStores(): bool
    {
        return $this->checkIfTriggerExists('trigger_stores');
    }

    /** Add trigger (incrementing position) to stores  */
    public function upgrade_triggerStores(): void
    {
        sqlQuery('CREATE TRIGGER trigger_stores BEFORE INSERT ON stores
                    FOR EACH ROW
                    BEGIN
                    IF NEW.position IS NULL THEN
                        SET NEW.position = (SELECT COALESCE(MAX(stores.position) + 1, 0) FROM stores);
                    END IF;
                END;');

        sqlQuery('SELECT @i := -1; UPDATE stores SET position = (select @i := @i + 1) WHERE 1 ORDER BY position,id');

        $this->upgradeOK();
    }

    public function check_OrderStoresTable(): bool
    {
        return $this->checkTableExists('order_stores');
    }

    public function upgrade_OrderStoresTable(): void
    {
        sqlQuery('CREATE TABLE order_stores(
            id_order INT(11) UNSIGNED NOT NULL PRIMARY KEY,
            id_store INT              NOT NULL,
                CONSTRAINT FK_order_stores_id_store FOREIGN KEY (id_store) REFERENCES stores (id) ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT FK_order_stores_id_order FOREIGN KEY (id_order) REFERENCES orders (id) ON UPDATE CASCADE ON DELETE CASCADE);
        ');

        $this->upgradeOK();
    }
}
