<?php

class WarehouseUpgrade extends UpgradeNew
{
    public function check_WarehouseTables()
    {
        return $this->checkTableExists('warehouse_positions');
    }

    /**  Create warehouse **/
    public function upgrade_WarehouseTables()
    {
        sqlQuery('
         CREATE TABLE warehouse_locations
        (
          id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
          code VARCHAR(20) NOT NULL UNIQUE COLLATE utf8mb4_general_ci
        );
        
        INSERT INTO warehouse_locations (id, code) VALUES (1, \'STUL\'), (2, \'BOX\');
        
        CREATE TABLE warehouse_positions
        (
          id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
          code VARCHAR(20) NOT NULL UNIQUE COLLATE utf8mb4_general_ci,
          id_location INT(11) NOT NULL,
          UNIQUE KEY `warehouse_positions` (code, id_location),
        
          CONSTRAINT FK_wp_id_location FOREIGN KEY (id_location) REFERENCES warehouse_locations (id) ON DELETE CASCADE
        );
        
        CREATE TABLE warehouse_products
        (
          id_product INT(11) NOT NULL,
          id_variation INT(11) NULL,
          id_position INT(11) NOT NULL,
          pieces INT(11) NOT NULL,
          UNIQUE KEY `warehouse_products` (id_product, id_variation, id_position),
        
          CONSTRAINT FK_wp_id_product FOREIGN KEY (id_product) REFERENCES products (id) ON DELETE CASCADE,
          CONSTRAINT FK_wp_id_position FOREIGN KEY (id_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE
        );
        
        CREATE TABLE warehouse_products_history
        (
          id_product INT(11) NOT NULL,
          id_variation INT(11) NULL,
          id_old_position INT(11) NOT NULL,
          id_new_position INT(11) NOT NULL,
          pieces INT(11) NOT NULL,
          admin INT(11) NOT NULL,
          date DATETIME NOT NULL,
        
          CONSTRAINT FK_wp_id_product FOREIGN KEY (id_product) REFERENCES products (id) ON DELETE CASCADE,
          CONSTRAINT FK_wph_id_old_position FOREIGN KEY (id_old_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE,
          CONSTRAINT FK_wph_id_new_position FOREIGN KEY (id_new_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE,
          CONSTRAINT FK_wph_admin FOREIGN KEY (admin) REFERENCES admins (id) ON DELETE CASCADE
        );

      ');
    }

    public function check_MainTable()
    {
        return returnSQLResult('SELECT COUNT(*) FROM warehouse_positions WHERE code="PRIJEM"') == 0;
    }

    /**  Create MainTable position **/
    public function upgrade_MainTable()
    {
        if (returnSQLResult('SELECT COUNT(*) FROM warehouse_positions WHERE code="STUL-1"') != 0) {
            sqlQuery('UPDATE warehouse_positions SET code="PRIJEM" WHERE code="STUL-1"');
        } else {
            sqlQuery('INSERT INTO warehouse_positions (code, id_location) VALUES ("PRIJEM", 1)');
        }

        $this->upgradeOK();
    }

    public function check_Kos()
    {
        return returnSQLResult('SELECT COUNT(*) FROM warehouse_positions WHERE code="STUL-Kos"') == 0;
    }

    /**  Create Kos position **/
    public function upgrade_Kos()
    {
        sqlQuery('INSERT INTO warehouse_positions (code, id_location) VALUES ("STUL-Kos", 1)');
    }

    public function check_Vratky()
    {
        return returnSQLResult('SELECT COUNT(*) FROM warehouse_positions WHERE code="VRATKY"') == 0;
    }

    /**  Create Vratky position **/
    public function upgrade_Vratky()
    {
        if (returnSQLResult('SELECT COUNT(*) FROM warehouse_positions WHERE code="STUL-Vratky"') != 0) {
            sqlQuery('UPDATE warehouse_positions SET code="VRATKY" WHERE code="STUL-Vratky"');
        } else {
            sqlQuery('INSERT INTO warehouse_positions (code, id_location) VALUES ("VRATKY", 1)');
        }

        $this->upgradeOK();
    }

    public function check_Inventura()
    {
        return returnSQLResult('SELECT COUNT(*) FROM warehouse_positions WHERE code="INVENTURA"') == 0;
    }

    /**  Create inventura position **/
    public function upgrade_Inventura()
    {
        sqlQuery('INSERT INTO warehouse_positions (code, id_location) VALUES ("INVENTURA", 1)');
    }

    public function check_Orders()
    {
        return $this->checkTableExists('warehouse_orders');
    }

    /** Add warehouse orders **/
    public function upgrade_Orders()
    {
        sqlQuery('CREATE TABLE warehouse_orders (
          id INT AUTO_INCREMENT PRIMARY KEY,
          id_order INT(11) UNSIGNED,
          id_position INT(11),
          state TEXT,
          CONSTRAINT warehouse_orders_orders_id_fk FOREIGN KEY (id_order) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT warehouse_orders_positions_id_fk FOREIGN KEY (id_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT warehouse_orders_uindex UNIQUE INDEX (id_order, id_position)
        )');
    }

    public function check_OrdersTimestamps()
    {
        return $this->checkColumnExists('warehouse_orders', 'date_start');
    }

    /** Add dates to warehouse orders **/
    public function upgrade_OrdersTimestamps()
    {
        sqlQuery('ALTER TABLE warehouse_orders ADD date_start datetime NOT NULL;
              ALTER TABLE warehouse_orders ADD date_finish datetime NULL;');
    }

    public function check_WarehouseLog()
    {
        return $this->checkTableExists('warehouse_log');
    }

    /** Add warehouse log file **/
    public function upgrade_WarehouseLog()
    {
        sqlQuery('CREATE TABLE warehouse_log (
          id INT AUTO_INCREMENT PRIMARY KEY,
          id_product INT(11) NOT NULL,
          id_variation INT(11) NULL,
          pieces INT(11) NOT NULL,
          old_id_position INT(11) NULL,
          new_id_position INT(11) NULL,
          #id_user INT(11) NULL,
          id_admin INT(11) NOT NULL,
          update_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          id_order INT(11) unsigned DEFAULT NULL,
          note TEXT DEFAULT "",

          CONSTRAINT warehouse_id_product FOREIGN KEY (id_product) REFERENCES products (id) ON DELETE CASCADE,
          CONSTRAINT warehouse_log_old_positions_id_fk FOREIGN KEY (old_id_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT warehouse_log_new_positions_id_fk FOREIGN KEY (new_id_position) REFERENCES warehouse_positions (id) ON DELETE CASCADE ON UPDATE CASCADE,
          #CONSTRAINT warehouse_log_user_id_fk FOREIGN KEY (id_admin) REFERENCES admins (id) ON DELETE CASCADE ON UPDATE CASCADE,
          CONSTRAINT warehouse_log_orders_id_fk FOREIGN KEY (id_order) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE
        )');
    }

    public function check_OrdersCloseDate()
    {
        return $this->checkColumnExists('warehouse_orders', 'date_close');
    }

    /** Add date_close to warehouse orders **/
    public function upgrade_OrdersCloseDate()
    {
        sqlQuery('ALTER TABLE warehouse_orders ADD date_close datetime NULL');
    }

    public function check_IdAdminNullable()
    {
        return $this->checkColumnIsNull('warehouse_log', 'id_admin', true);
    }

    /** Change warehouse_log.id_admin to NULLable **/
    public function upgrade_IdAdminNullable()
    {
        sqlQuery('ALTER TABLE warehouse_log MODIFY id_admin INT(11) NULL');
    }

    public function check_LogPositionsNullable()
    {
        return $this->checkColumnIsNull('warehouse_log', 'old_id_position', true);
    }

    /** Change warehouse_log positions to NULLable **/
    public function upgrade_LogPositionsNullable()
    {
        sqlQuery('
            ALTER TABLE warehouse_log MODIFY old_id_position INT(11) NULL;
            ALTER TABLE warehouse_log MODIFY new_id_position INT(11) NULL;');
    }

    public function check_PositionsPosition()
    {
        return $this->checkColumnExists('warehouse_products', 'over_supply');
    }

    /** Add over_supply to warehouse_positions */
    public function upgrade_PositionsPosition()
    {
        sqlQuery('ALTER TABLE warehouse_products ADD over_supply ENUM("N", "Y") DEFAULT "N"');
    }

    public function check_RenameLocationPosition()
    {
        return !$this->checkColumnExists('warehouse_locations', 'position');
    }

    /** Rename position to sort_index in warehouse_locations */
    public function upgrade_RenameLocationPosition()
    {
        sqlQuery('ALTER TABLE warehouse_locations CHANGE COLUMN position sort_index INT(11) NULL');
    }

    public function check_LocationPosition()
    {
        return $this->checkColumnExists('warehouse_locations', 'sort_index');
    }

    /** Add sort_index to warehouse_locations **/
    public function upgrade_LocationPosition()
    {
        sqlQuery('ALTER TABLE warehouse_locations ADD sort_index INT(11) NULL');
        sqlQuery('SELECT @i := -1; UPDATE warehouse_locations SET sort_index = (select @i := @i + 1) WHERE 1 ORDER BY code');
    }

    public function check_LocationDeleteEmpty()
    {
        return $this->checkColumnExists('warehouse_locations', 'delete_empty');
    }

    /** Add 'delete_empty' on warehouse_location **/
    public function upgrade_LocationDeleteEmpty()
    {
        sqlQuery('ALTER TABLE warehouse_locations ADD delete_empty ENUM("Y", "N") DEFAULT "N"');
        sqlQuery('UPDATE warehouse_locations SET delete_empty="Y" WHERE id IN (1, 2)');
    }

    public function check_WarehouseProductsVariationsForeignKey()
    {
        return $this->checkForeignKeyExists('warehouse_products', 'id_variation');
    }

    /** Add foreign key to warehouse_products.id_variation */
    public function upgrade_WarehouseProductsVariationsForeignKey()
    {
        sqlQuery('ALTER TABLE warehouse_products ADD CONSTRAINT FK_wp_id_variation FOREIGN KEY (id_variation) REFERENCES products_variations(id) ON DELETE CASCADE ON UPDATE CASCADE');
    }

    public function check_WarehouseProductsPiecesType()
    {
        return $this->checkColumnType('warehouse_products', 'pieces', 'int(11) unsigned');
    }

    /** warehouse_products make pieces unsigned */
    public function upgrade_WarehouseProductsPiecesType()
    {
        sqlQuery('ALTER TABLE warehouse_products CHANGE pieces pieces INT(11) UNSIGNED');

        $this->upgradeOK();
    }

    public function check_LocationScoreMultiplier()
    {
        return $this->checkColumnExists('warehouse_locations', 'score_multiplier');
    }

    /** Add column score_multiplier into warehouse_locations table */
    public function upgrade_LocationScoreMultiplier()
    {
        sqlQuery('ALTER TABLE warehouse_locations ADD COLUMN score_multiplier INT(11) DEFAULT 1');

        $this->upgradeOK();
    }

    public function check_trigger_warehouse_locations()
    {
        return $this->checkIfTriggerExists('trigger_warehouse_locations');
    }

    /** Add trigger (incrementing position) to warehouse_locations  */
    public function upgrade_trigger_warehouse_locations()
    {
        sqlQuery('CREATE TRIGGER trigger_warehouse_locations BEFORE INSERT ON warehouse_locations
                    FOR EACH ROW
                BEGIN
                    IF NEW.sort_index IS NULL THEN
                        SET NEW.sort_index = (SELECT COALESCE(MAX(sort_index) + 1, 0) FROM warehouse_locations);
                    END IF;
                END;');
        $this->upgradeOK();
    }

    public function check_lastPositionItemInventory()
    {
        return $this->checkColumnExists('warehouse_products', 'inventory_date');
    }

    /** Add column inventory_date into warehouse_products */
    public function upgrade_lastPositionItemInventory()
    {
        sqlQuery('ALTER TABLE warehouse_products ADD COLUMN inventory_date DATETIME DEFAULT NULL AFTER id_position ');

        $this->upgradeOK();
    }

    public function check_lastPositionInventory()
    {
        return $this->checkColumnExists('warehouse_positions', 'inventory_date');
    }

    /** Add column inventory_date into warehouse_positions */
    public function upgrade_lastPositionInventory()
    {
        sqlQuery('ALTER TABLE warehouse_positions ADD COLUMN inventory_date DATETIME DEFAULT NULL ');

        sqlQuery('UPDATE warehouse_positions as wpos SET 
                   inventory_date = (SELECT MAX(wp.inventory_date) from warehouse_products wp where wp.id_position=wpos.id) WHERE 1=1;');

        $this->upgradeOK();
    }

    public function check_id_packaged_by()
    {
        return $this->checkColumnExists('warehouse_orders', 'id_packaged_by');
    }

    /** Add packaged_by to warehouse orders */
    public function upgrade_id_packaged_by()
    {
        sqlQuery('ALTER TABLE warehouse_orders ADD id_packaged_by INT NULL');

        sqlQuery('ALTER TABLE warehouse_orders
	        ADD CONSTRAINT warehouse_orders_admins_id_fk
		    FOREIGN KEY (id_packaged_by) REFERENCES admins (id)
			ON UPDATE CASCADE ON DELETE SET NULL');

        $this->upgradeOK();
    }

    public function check_WarehouseProductsIdProductOnUpdateCascade(): bool
    {
        return $this->checkConstraintRule('warehouse_products', 'FK_wp_id_product', null, 'CASCADE');
    }

    /** warehouse_products: id_product - add missing on update cascade */
    public function upgrade_WarehouseProductsIdProductOnUpdateCascade(): void
    {
        sqlQuery('alter table warehouse_products drop foreign key FK_wp_id_product;');
        sqlQuery('alter table warehouse_products
            add constraint FK_wp_id_product
                foreign key (id_product) references products (id)
                    on update cascade on delete cascade;');

        $this->upgradeOK();
    }

    public function check_WarehouseLogIdProductOnUpdateCascade(): bool
    {
        return $this->checkConstraintRule('warehouse_log', 'warehouse_id_product', null, 'CASCADE');
    }

    /** warehouse_products: id_product - add missing on update cascade */
    public function upgrade_WarehouseLogIdProductOnUpdateCascade(): void
    {
        sqlQuery('alter table warehouse_log drop foreign key warehouse_id_product;');
        sqlQuery('alter table warehouse_log
            add constraint warehouse_id_product
                foreign key (id_product) references products (id)
                    on update cascade on delete cascade;');

        $this->upgradeOK();
    }

    public function check_WarehouseLogIdTransfer()
    {
        return findModule(Modules::STORES) && $this->checkColumnExists('warehouse_log', 'id_transfer');
    }

    /** Add id_transfer to warehouse orders **/
    public function upgrade_WarehouseLogIdTransfer()
    {
        sqlQuery('ALTER TABLE warehouse_log 
                         ADD COLUMN id_transfer INT DEFAULT NULL;');
        sqlQuery('ALTER TABLE warehouse_log ADD CONSTRAINT warehouse_log_stores_transfers_id_fk
		                 FOREIGN KEY (id_transfer) REFERENCES stores_transfers (id) ON UPDATE CASCADE ON DELETE CASCADE;');
        $this->upgradeOK();
    }

    public function check_ean_unique_constraints()
    {
        return $this->checkConstraintExists('products', 'unique_ean');
    }

    /** Adding unique constraints on EANs */
    public function upgrade_ean_unique_constraints()
    {
        sqlQuery('ALTER TABLE products ADD CONSTRAINT unique_ean UNIQUE (ean);');
        sqlQuery('ALTER TABLE products_variations ADD CONSTRAINT unique_ean UNIQUE (ean);');
    }

    public function check_ean_unique_procedure()
    {
        return $this->checkProcedureExists('check_duplicate_product_ean');
    }

    /**
     * Adding procedures: pseudo-unique constraint across products and products_variations on EAN;.
     */
    public function upgrade_ean_unique_procedure()
    {
        sqlQuery("
        CREATE PROCEDURE IF NOT EXISTS check_duplicate_product_ean (IN new_ean BIGINT, IN in_product_variant TINYINT(1))
            BEGIN
                DECLARE cnt_dup INTEGER;
                DECLARE err_message_text VARCHAR(255);
            
                IF (in_product_variant = 1) THEN
                    SELECT COUNT(p.id)
                        FROM products p
                        WHERE p.ean = new_ean
                        INTO cnt_dup;
                ELSE
                    SELECT COUNT(pv.id)
                        FROM products_variations pv
                        WHERE pv.ean = new_ean
                        INTO cnt_dup;
                END IF;
            
                IF (cnt_dup > 0) THEN
                    SELECT CONCAT('EAN ', CAST(new_ean AS CHAR), ' je již použit.')
                        INTO err_message_text;
                    SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = err_message_text;
                END IF;
            END;"
        );

        $this->upgradeOK();
    }

    public function check_ean_unique_trigger()
    {
        return $this->checkIfTriggerExists('trigger_update_product_ean_in_variants', 'products');
    }

    /**
     * Adding unique constraint on EAN.
     */
    public function upgrade_ean_unique_trigger()
    {
        sqlQuery('CREATE TRIGGER IF NOT EXISTS trigger_update_product_ean_in_variants
            BEFORE UPDATE ON products FOR EACH ROW
            BEGIN CALL check_duplicate_product_ean(NEW.ean, 0); END;'
        );

        sqlQuery('CREATE TRIGGER IF NOT EXISTS trigger_insert_product_ean_in_variants
            BEFORE INSERT ON products FOR EACH ROW
            BEGIN CALL check_duplicate_product_ean(NEW.ean, 0); END;'
        );

        sqlQuery('CREATE TRIGGER IF NOT EXISTS trigger_update_variant_ean_in_products
            BEFORE UPDATE ON products_variations FOR EACH ROW
            BEGIN CALL check_duplicate_product_ean(NEW.ean, 1); END;'
        );

        sqlQuery('CREATE TRIGGER IF NOT EXISTS trigger_insert_variant_ean_in_products
            BEFORE INSERT ON products_variations FOR EACH ROW
            BEGIN CALL check_duplicate_product_ean(NEW.ean, 1); END;'
        );

        $this->upgradeOK();
    }
}
