<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20220221135903 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Assortment delivery method work-around';
    }

    public function up(Schema $schema): void
    {
        $this->cleanup();

        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('ALTER TABLE assortment ADD delivery_method_id CHAR(36) DEFAULT NULL COMMENT \'(DC2Type:uuid)\'');
        $this->addSql('ALTER TABLE assortment ADD CONSTRAINT FK_2A5E6D8B5DED75F5 FOREIGN KEY (delivery_method_id) REFERENCES store_delivery_method (id) ON DELETE CASCADE');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_2A5E6D8B5DED75F5 ON assortment (delivery_method_id)');
    }

    public function down(Schema $schema): void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('ALTER TABLE assortment DROP FOREIGN KEY FK_2A5E6D8B5DED75F5');
        $this->addSql('DROP INDEX UNIQ_2A5E6D8B5DED75F5 ON assortment');
        $this->addSql('ALTER TABLE assortment DROP delivery_method_id');
    }

    public function isTransactional(): bool
    {
        return false;
    }

    private function cleanup()
    {
        $subQuery = 'SELECT assortment_id FROM store_delivery_method WHERE assortment_id IS NOT NULL';

        $this->addSql("DELETE FROM assortment_extra_set_product WHERE extra_set_id IN (
            SELECT id FROM assortment_extra_set WHERE assortment_id IN (
                SELECT id FROM assortment WHERE id NOT IN ($subQuery)
            )
        )");

        $this->addSql("DELETE FROM assortment_question_set_question_answer WHERE question_id IN (
            SELECT id FROM assortment_question_set_question WHERE question_set_id IN (
                SELECT id FROM assortment_question_set WHERE assortment_id IN (
                    SELECT id FROM assortment WHERE id NOT IN ($subQuery)
                )
            )
        )");

        $this->addSql("DELETE FROM assortment_question_set_question WHERE question_set_id IN (
            SELECT id FROM assortment_question_set WHERE assortment_id IN (
                SELECT id FROM assortment WHERE id NOT IN ($subQuery)
            )
        )");

        $this->addSql("DELETE FROM vat WHERE product_id IN (
            SELECT id FROM assortment_product WHERE assortment_id IN (
                SELECT id FROM assortment WHERE id NOT IN ($subQuery)
            )
        )");

        $this->addSql("DELETE FROM assortment_product_position
            WHERE product_id IN
                (
                    SELECT id FROM assortment_product WHERE assortment_id IN (
                        SELECT id FROM assortment WHERE id NOT IN ($subQuery)
                    )
                )
                OR assortment_id IN
                (
                    SELECT id FROM assortment WHERE id NOT IN ($subQuery)
                )
        ");

        // Fix children group id's.
        $groupIds = $this->connection->fetchFirstColumn("SELECT id FROM assortment_group WHERE assortment_id IN (
                SELECT id FROM assortment WHERE id NOT IN ($subQuery)
            )");

        if ($groupIds) {
            $groupIds = implode(',', array_map([
                $this->connection,
                'quote'
            ], $groupIds));
            $this->addSql("DELETE FROM assortment_group WHERE main_group_id IN ($groupIds)");
        }

        foreach (['assortment_product_suggestion', 'assortment_extra_set', 'assortment_product', 'assortment_group', 'assortment_question_set', 'assortment'] as $table) {
            if ($table === 'assortment') {
                $this->addSql("DELETE FROM $table WHERE id NOT IN ($subQuery)");
            }
            else {
                $this->addSql("DELETE FROM $table WHERE assortment_id IN (
                    SELECT id FROM assortment WHERE id NOT IN ($subQuery)
                )");
            }
        }
    }
}