<?php

declare(strict_types=1);

namespace KupShop\OrderingBundle\Resources\upgrade;

class ProductSetsUpgrade extends \UpgradeNew
{
    protected function isAllowed()
    {
        return findModule(\Modules::PRODUCT_SETS);
    }

    public function check_ProductSetsIdColumn(): bool
    {
        return $this->checkColumnExists('products_sets', 'id');
    }

    /** products_sets: add `id` column */
    public function upgrade_ProductSetsIdColumn(): void
    {
        sqlQuery('ALTER TABLE products_sets DROP FOREIGN KEY products_sets_ibfk_1;');
        sqlQuery('ALTER TABLE products_sets DROP FOREIGN KEY products_sets_ibfk_2;');
        sqlQuery('ALTER TABLE products_sets DROP PRIMARY KEY');

        sqlQuery('ALTER TABLE products_sets ADD COLUMN id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST');

        $this->upgradeOK();
    }

    public function check_ProductsSetsProductForeignKey(): bool
    {
        return $this->checkForeignKeyExists('products_sets', 'id_product');
    }

    /** products_sets: add missing foreign keys */
    public function upgrade_ProductsSetsProductForeignKey(): void
    {
        // delete products_sets with reference to not existing products
        sqlQueryBuilder()
            ->delete('products_sets', 'ps')
            ->leftJoin('ps', 'products', 'p', 'ps.id_product = p.id')
            ->leftJoin('ps', 'products', 'p2', 'ps.id_product_set = p2.id')
            ->where('p.id IS NULL OR p2.id IS NULL')
            ->execute();

        // add foreign keys
        sqlQuery('ALTER TABLE `products_sets`
         ADD CONSTRAINT `products_sets_ibfk_2` FOREIGN KEY (`id_product_set`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
         ADD CONSTRAINT `products_sets_ibfk_1` FOREIGN KEY (`id_product`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE');

        $this->upgradeOK();
    }
}
