<?php

namespace KupShop\ParameterGroupsBundle\Resources\upgrade;

use Doctrine\DBAL\Exception\ForeignKeyConstraintViolationException;

class ParameterGroupsUpgrade extends \UpgradeNew
{
    public function check_tableExists()
    {
        return $this->checkTableExists('parameter_groups');
    }

    /** Create table 'parameter_groups' */
    public function upgrade_tableExists()
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS parameter_groups (
                id INT UNSIGNED AUTO_INCREMENT NOT NULL, 
                name VARCHAR(191) NOT NULL UNIQUE,
                JSON TEXT NOT NULL ,
                PRIMARY KEY(id)
            ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;
        ');
        sqlQuery('CREATE UNIQUE INDEX ui_parameter_groups_name ON parameter_groups(name);');

        sqlQuery('ALTER TABLE products ADD id_parameter_group INT UNSIGNED DEFAULT NULL;');
        sqlQuery('ALTER TABLE products ADD CONSTRAINT fk_products_parameter_group
            FOREIGN KEY (id_parameter_group) REFERENCES parameter_groups(id) ON UPDATE CASCADE ON DELETE SET NULL;');

        $this->upgradeOK();
    }

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

    public function upgrade_parameterGroupsItems(): void
    {
        sqlQuery('CREATE TABLE IF NOT EXISTS parameter_groups_items (
            id INT UNSIGNED AUTO_INCREMENT NOT NULL,
            id_parameter_group INT UNSIGNED NOT NULL,
            name VARCHAR(191) NOT NULL,
            PRIMARY KEY(id),
            CONSTRAINT fk_parameter_groups_items_parameter_group
                FOREIGN KEY (id_parameter_group) REFERENCES parameter_groups(id) ON UPDATE CASCADE ON DELETE CASCADE
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;');

        sqlQuery('CREATE TABLE IF NOT EXISTS parameter_groups_items_parameters (
            id_parameter_group_item INT UNSIGNED NOT NULL,
            id_parameter INT UNSIGNED NOT NULL,
            PRIMARY KEY(id_parameter, id_parameter_group_item),
            CONSTRAINT fk_parameter_groups_items_parameters_parameter_group_item
                FOREIGN KEY (id_parameter_group_item) REFERENCES parameter_groups_items(id) ON UPDATE CASCADE ON DELETE CASCADE,
            CONSTRAINT fk_parameter_groups_items_parameters_parameter
                FOREIGN KEY (id_parameter) REFERENCES parameters(id) ON UPDATE CASCADE ON DELETE CASCADE
        ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB;');

        $this->migrateParameterGroups();

        sqlQuery('ALTER TABLE parameter_groups DROP COLUMN JSON');

        $this->upgradeOK();
    }

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

    public function upgrade_parameterGroupsItemsPosition(): void
    {
        sqlQuery('ALTER TABLE parameter_groups_items ADD COLUMN position INT DEFAULT NULL;');

        $this->upgradeOK();
    }

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

    public function upgrade_parameterGroupsItemsParametersPosition(): void
    {
        sqlQuery('ALTER TABLE parameter_groups_items_parameters ADD COLUMN position INT DEFAULT NULL;');

        $this->upgradeOK();
    }

    private function migrateParameterGroups(): void
    {
        $qb = sqlQueryBuilder()->select('*')->from('parameter_groups');

        foreach ($qb->execute() as $group) {
            $this->createParameterGroupItems($group);
        }
    }

    private function createParameterGroupItems(array $group): void
    {
        $json = json_decode($group['JSON'], true);
        foreach ($json as $groupItem) {
            $groupItemId = sqlGetConnection()->transactional(function () use ($group, $groupItem) {
                sqlQueryBuilder()
                    ->insert('parameter_groups_items')
                    ->directValues([
                        'id_parameter_group' => $group['id'],
                        'name' => $groupItem['name'],
                    ])->execute();

                return sqlInsertId();
            });
            $this->createParameterGroupItemParameters($groupItem['parameters'], $groupItemId);
        }
    }

    private function createParameterGroupItemParameters(array $parameterIds, int $groupItemId): void
    {
        foreach (array_filter($parameterIds) as $parameter) {
            try {
                sqlQueryBuilder()
                ->insert('parameter_groups_items_parameters')
                ->directValues([
                    'id_parameter_group_item' => $groupItemId,
                    'id_parameter' => $parameter,
                ])->execute();
            } catch (ForeignKeyConstraintViolationException $e) {
                // ignore
            }
        }
    }
}
