<?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 Version20260629110000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Ajoute une position aux variants des collections.';
    }

    public function up(Schema $schema): void
    {
        $this->addSql(<<<'SQL'
DO $$
BEGIN
    IF to_regclass('public.product_variant_collection_variant') IS NOT NULL THEN
        ALTER TABLE product_variant_collection_variant DROP CONSTRAINT IF EXISTS product_variant_collection_variant_pkey;
        ALTER TABLE product_variant_collection_variant ADD COLUMN IF NOT EXISTS id UUID;
        ALTER TABLE product_variant_collection_variant ADD COLUMN IF NOT EXISTS position INT DEFAULT NULL;
        ALTER TABLE product_variant_collection_variant ADD COLUMN IF NOT EXISTS created_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL;
        ALTER TABLE product_variant_collection_variant ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL;
        UPDATE product_variant_collection_variant SET id = gen_random_uuid() WHERE id IS NULL;
        ALTER TABLE product_variant_collection_variant ALTER COLUMN id SET NOT NULL;
        ALTER TABLE product_variant_collection_variant ALTER COLUMN created_at DROP DEFAULT;
        ALTER TABLE product_variant_collection_variant ALTER COLUMN updated_at DROP DEFAULT;
        ALTER TABLE product_variant_collection_variant ADD PRIMARY KEY (id);
    END IF;
END $$;
SQL);
        $this->addSql(<<<'SQL'
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY collection_id ORDER BY variant_id) - 1 AS new_position
    FROM product_variant_collection_variant
    WHERE position IS NULL
)
UPDATE product_variant_collection_variant AS item
SET position = ranked.new_position
FROM ranked
WHERE item.id = ranked.id
SQL);
        $this->addSql('CREATE UNIQUE INDEX IF NOT EXISTS uniq_product_variant_collection_variant ON product_variant_collection_variant (collection_id, variant_id)');
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP INDEX IF EXISTS uniq_product_variant_collection_variant');
        $this->addSql('ALTER TABLE product_variant_collection_variant DROP CONSTRAINT IF EXISTS product_variant_collection_variant_pkey');
        $this->addSql('ALTER TABLE product_variant_collection_variant DROP COLUMN IF EXISTS id');
        $this->addSql('ALTER TABLE product_variant_collection_variant DROP COLUMN IF EXISTS position');
        $this->addSql('ALTER TABLE product_variant_collection_variant DROP COLUMN IF EXISTS created_at');
        $this->addSql('ALTER TABLE product_variant_collection_variant DROP COLUMN IF EXISTS updated_at');
        $this->addSql('ALTER TABLE product_variant_collection_variant ADD PRIMARY KEY (collection_id, variant_id)');
    }
}
