<?php

/*
 * This file is part of the VITICORE2 package.
 *
 * (c) CP Creation <web@cpcreation.fr>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CpCreation\VitiCore\Bridge\Doctrine\Migrations;

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

final class Version20260708100000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Remplace les collections de variants par des collections de produits.';
    }

    public function up(Schema $schema): void
    {
        $this->addSql(<<<'SQL'
CREATE TABLE IF NOT EXISTS product_collection (
    id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    mode VARCHAR(20) DEFAULT 'manual' NOT NULL,
    automatic_category_id UUID DEFAULT NULL,
    automatic_only_cuvee BOOLEAN DEFAULT true NOT NULL,
    automatic_only_new BOOLEAN DEFAULT false NOT NULL,
    automatic_exclude_sold_out BOOLEAN DEFAULT false NOT NULL,
    automatic_limit INT DEFAULT NULL,
    created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    updated_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
    PRIMARY KEY(id)
)
SQL);
        $this->addSql('CREATE UNIQUE INDEX IF NOT EXISTS uniq_product_collection_code ON product_collection (code)');
        $this->addSql('CREATE INDEX IF NOT EXISTS IDX_PRODUCT_COLLECTION_AUTOMATIC_CATEGORY ON product_collection (automatic_category_id)');
        $this->addSql(<<<'SQL'
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'FK_PRODUCT_COLLECTION_AUTOMATIC_CATEGORY'
    ) THEN
        ALTER TABLE product_collection
            ADD CONSTRAINT FK_PRODUCT_COLLECTION_AUTOMATIC_CATEGORY
            FOREIGN KEY (automatic_category_id)
            REFERENCES product_category (id)
            ON DELETE SET NULL
            NOT DEFERRABLE INITIALLY IMMEDIATE;
    END IF;
END $$;
SQL);
        $this->addSql(<<<'SQL'
CREATE TABLE IF NOT EXISTS product_collection_product (
    id UUID NOT NULL,
    collection_id UUID NOT NULL,
    product_id UUID NOT NULL,
    position INT DEFAULT NULL,
    created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY(id)
)
SQL);
        $this->addSql('CREATE INDEX IF NOT EXISTS IDX_PRODUCT_COLLECTION_PRODUCT_COLLECTION ON product_collection_product (collection_id)');
        $this->addSql('CREATE INDEX IF NOT EXISTS IDX_PRODUCT_COLLECTION_PRODUCT_PRODUCT ON product_collection_product (product_id)');
        $this->addSql('CREATE UNIQUE INDEX IF NOT EXISTS uniq_product_collection_product ON product_collection_product (collection_id, product_id)');
        $this->addSql(<<<'SQL'
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'FK_PRODUCT_COLLECTION_PRODUCT_COLLECTION'
    ) THEN
        ALTER TABLE product_collection_product
            ADD CONSTRAINT FK_PRODUCT_COLLECTION_PRODUCT_COLLECTION
            FOREIGN KEY (collection_id)
            REFERENCES product_collection (id)
            ON DELETE CASCADE
            NOT DEFERRABLE INITIALLY IMMEDIATE;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'FK_PRODUCT_COLLECTION_PRODUCT_PRODUCT'
    ) THEN
        ALTER TABLE product_collection_product
            ADD CONSTRAINT FK_PRODUCT_COLLECTION_PRODUCT_PRODUCT
            FOREIGN KEY (product_id)
            REFERENCES product_product (id)
            ON DELETE CASCADE
            NOT DEFERRABLE INITIALLY IMMEDIATE;
    END IF;
END $$;
SQL);
        $this->addSql(<<<'SQL'
DO $$
BEGIN
    IF to_regclass('public.product_variant_collection') IS NOT NULL THEN
        INSERT INTO product_collection (
            id,
            name,
            code,
            description,
            mode,
            automatic_category_id,
            automatic_only_cuvee,
            automatic_only_new,
            automatic_exclude_sold_out,
            automatic_limit,
            created_at,
            updated_at
        )
        SELECT
            id,
            name,
            code,
            description,
            mode,
            automatic_category_id,
            automatic_only_cuvee,
            automatic_only_new,
            automatic_exclude_sold_out,
            automatic_limit,
            created_at,
            updated_at
        FROM product_variant_collection
        ON CONFLICT (id) DO NOTHING;
    END IF;
END $$;
SQL);
        $this->addSql(<<<'SQL'
DO $$
BEGIN
    IF to_regclass('public.product_variant_collection_variant') IS NOT NULL THEN
        INSERT INTO product_collection_product (
            id,
            collection_id,
            product_id,
            position,
            created_at,
            updated_at
        )
        SELECT
            gen_random_uuid(),
            collection_id,
            product_id,
            position,
            created_at,
            updated_at
        FROM (
            SELECT DISTINCT ON (item.collection_id, variant.product_id)
                item.collection_id,
                variant.product_id,
                item.position,
                item.created_at,
                item.updated_at
            FROM product_variant_collection_variant item
            INNER JOIN product_variant variant ON variant.id = item.variant_id
            ORDER BY item.collection_id, variant.product_id, item.position ASC NULLS LAST, item.created_at ASC
        ) converted_items
        ON CONFLICT (collection_id, product_id) DO NOTHING;
    END IF;
END $$;
SQL);
        $this->addSql(<<<'SQL'
CREATE TABLE IF NOT EXISTS product_collection_audit (
    id SERIAL NOT NULL,
    type VARCHAR(10) NOT NULL,
    object_id VARCHAR(255) NOT NULL,
    discriminator VARCHAR(255) DEFAULT NULL,
    transaction_hash VARCHAR(40) DEFAULT NULL,
    diffs JSON DEFAULT NULL,
    blame_id VARCHAR(255) DEFAULT NULL,
    blame_user VARCHAR(255) DEFAULT NULL,
    blame_user_fqdn VARCHAR(255) DEFAULT NULL,
    blame_user_firewall VARCHAR(100) DEFAULT NULL,
    ip VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY(id)
)
SQL);
        $this->addSql('CREATE INDEX IF NOT EXISTS type_'.md5('product_collection_audit').'_idx ON product_collection_audit (type)');
        $this->addSql('CREATE INDEX IF NOT EXISTS object_id_'.md5('product_collection_audit').'_idx ON product_collection_audit (object_id)');
        $this->addSql('CREATE INDEX IF NOT EXISTS discriminator_'.md5('product_collection_audit').'_idx ON product_collection_audit (discriminator)');
        $this->addSql('CREATE INDEX IF NOT EXISTS transaction_hash_'.md5('product_collection_audit').'_idx ON product_collection_audit (transaction_hash)');
        $this->addSql('CREATE INDEX IF NOT EXISTS blame_id_'.md5('product_collection_audit').'_idx ON product_collection_audit (blame_id)');
        $this->addSql('CREATE INDEX IF NOT EXISTS created_at_'.md5('product_collection_audit').'_idx ON product_collection_audit (created_at)');
        $this->addSql("COMMENT ON COLUMN product_collection_audit.created_at IS '(DC2Type:datetime_immutable)'");

        $this->addSql('DROP TABLE IF EXISTS product_variant_collection_variant');
        $this->addSql('DROP TABLE IF EXISTS product_variant_collection_audit');
        $this->addSql('DROP SEQUENCE IF EXISTS product_variant_collection_audit_id_seq CASCADE');
        $this->addSql('DROP TABLE IF EXISTS product_variant_collection');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP TABLE IF EXISTS product_collection_product');
        $this->addSql('DROP TABLE IF EXISTS product_collection_audit');
        $this->addSql('DROP SEQUENCE IF EXISTS product_collection_audit_id_seq CASCADE');
        $this->addSql('DROP TABLE IF EXISTS product_collection');
    }
}
