<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * Create piece_product_slots table (mirroring composant_product_slots)
 * and add version columns to composants, pieces, products.
 */
final class Version20260313124029 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Create piece_product_slots table, add version columns to composants/pieces/products, migrate piece_products data';
    }

    public function up(Schema $schema): void
    {
        // ── Create piece_product_slots table (idempotent) ─────────────────────

        $this->addSql(<<<'SQL'
                CREATE TABLE IF NOT EXISTS piece_product_slots (
                    id VARCHAR(36) NOT NULL,
                    "pieceid" VARCHAR(36) NOT NULL,
                    "typeproductid" VARCHAR(36) DEFAULT NULL,
                    "selectedproductid" VARCHAR(36) DEFAULT NULL,
                    "familycode" VARCHAR(255) DEFAULT NULL,
                    position INT NOT NULL DEFAULT 0,
                    "createdat" TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
                    "updatedat" TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
                    PRIMARY KEY (id)
                )
            SQL);

        // ── Indexes (idempotent) ──────────────────────────────────────────────

        $this->addSql('CREATE INDEX IF NOT EXISTS idx_piece_prod_slot_piece ON piece_product_slots ("pieceid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_piece_prod_slot_type ON piece_product_slots ("typeproductid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_piece_prod_slot_selected ON piece_product_slots ("selectedproductid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_piece_product_slots_piece_pos ON piece_product_slots ("pieceid", position)');

        // ── Foreign keys (idempotent via DO $$ block) ─────────────────────────

        $this->addSql(<<<'SQL'
                DO $$ BEGIN
                    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_piece_prod_slot_piece') THEN
                        ALTER TABLE piece_product_slots
                            ADD CONSTRAINT fk_piece_prod_slot_piece
                            FOREIGN KEY ("pieceid") REFERENCES pieces (id) ON DELETE CASCADE;
                    END IF;
                END $$
            SQL);

        $this->addSql(<<<'SQL'
                DO $$ BEGIN
                    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_piece_prod_slot_type') THEN
                        ALTER TABLE piece_product_slots
                            ADD CONSTRAINT fk_piece_prod_slot_type
                            FOREIGN KEY ("typeproductid") REFERENCES model_types (id) ON DELETE SET NULL;
                    END IF;
                END $$
            SQL);

        $this->addSql(<<<'SQL'
                DO $$ BEGIN
                    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_piece_prod_slot_selected') THEN
                        ALTER TABLE piece_product_slots
                            ADD CONSTRAINT fk_piece_prod_slot_selected
                            FOREIGN KEY ("selectedproductid") REFERENCES products (id) ON DELETE SET NULL;
                    END IF;
                END $$
            SQL);

        // ── Add version columns (idempotent) ─────────────────────────────────

        $this->addSql('ALTER TABLE composants ADD COLUMN IF NOT EXISTS version INT NOT NULL DEFAULT 1');
        $this->addSql('ALTER TABLE pieces ADD COLUMN IF NOT EXISTS version INT NOT NULL DEFAULT 1');
        $this->addSql('ALTER TABLE products ADD COLUMN IF NOT EXISTS version INT NOT NULL DEFAULT 1');

        // ── Data migration: piece_products → piece_product_slots ─────────────

        $this->addSql(<<<'SQL'
                DO $$
                BEGIN
                    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'piece_products') THEN
                        INSERT INTO piece_product_slots (id, "pieceid", "typeproductid", "selectedproductid", "familycode", position, "createdat", "updatedat")
                        SELECT
                            'cl' || encode(gen_random_bytes(12), 'hex'),
                            pp.piece_id,
                            p.typeproductid,
                            pp.product_id,
                            NULL,
                            ROW_NUMBER() OVER (PARTITION BY pp.piece_id ORDER BY pp.product_id) - 1,
                            NOW(),
                            NOW()
                        FROM piece_products pp
                        JOIN products p ON p.id = pp.product_id
                        WHERE NOT EXISTS (
                            SELECT 1 FROM piece_product_slots pps
                            WHERE pps."pieceid" = pp.piece_id AND pps."selectedproductid" = pp.product_id
                        );
                    END IF;
                END $$
            SQL);
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP TABLE IF EXISTS piece_product_slots');
        $this->addSql('ALTER TABLE composants DROP COLUMN IF EXISTS version');
        $this->addSql('ALTER TABLE pieces DROP COLUMN IF EXISTS version');
        $this->addSql('ALTER TABLE products DROP COLUMN IF EXISTS version');
    }
}