<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

final class Version20260312170000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Create skeleton requirement tables (IF NOT EXISTS) and migrate JSON data from ModelType skeleton columns';
    }

    public function up(Schema $schema): void
    {
        // ── Table creation (idempotent) ──────────────────────────────────────

        $this->addSql(<<<'SQL'
                CREATE TABLE IF NOT EXISTS skeleton_piece_requirements (
                    id VARCHAR(36) NOT NULL,
                    "modeltypeid" VARCHAR(36) NOT NULL,
                    "typepieceid" VARCHAR(36) NOT 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);

        $this->addSql(<<<'SQL'
                CREATE TABLE IF NOT EXISTS skeleton_product_requirements (
                    id VARCHAR(36) NOT NULL,
                    "modeltypeid" VARCHAR(36) NOT NULL,
                    "typeproductid" VARCHAR(36) NOT 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);

        $this->addSql(<<<'SQL'
                CREATE TABLE IF NOT EXISTS skeleton_subcomponent_requirements (
                    id VARCHAR(36) NOT NULL,
                    "modeltypeid" VARCHAR(36) NOT NULL,
                    alias VARCHAR(255) NOT NULL,
                    "familycode" VARCHAR(255) NOT NULL,
                    "typecomposantid" VARCHAR(36) 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_skel_piece_req_model ON skeleton_piece_requirements("modeltypeid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_skel_piece_req_type ON skeleton_piece_requirements("typepieceid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_skel_prod_req_model ON skeleton_product_requirements("modeltypeid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_skel_prod_req_type ON skeleton_product_requirements("typeproductid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_skel_sub_req_model ON skeleton_subcomponent_requirements("modeltypeid")');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_skel_sub_req_typecomp ON skeleton_subcomponent_requirements("typecomposantid")');

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

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

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

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

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

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

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

        // ── Data migration: componentSkeleton.pieces → skeleton_piece_requirements ──

        $this->addSql(<<<'SQL'
                INSERT INTO skeleton_piece_requirements (id, "modeltypeid", "typepieceid", position, "createdat", "updatedat")
                SELECT
                    'cl' || encode(gen_random_bytes(12), 'hex'),
                    mt.id,
                    (piece->>'typePieceId'),
                    (ordinality - 1)::int,
                    NOW(), NOW()
                FROM model_types mt,
                LATERAL jsonb_array_elements(mt.componentskeleton::jsonb->'pieces') WITH ORDINALITY AS t(piece, ordinality)
                WHERE mt.category = 'COMPONENT'
                  AND mt.componentskeleton IS NOT NULL
                  AND mt.componentskeleton::jsonb->'pieces' IS NOT NULL
                  AND jsonb_array_length(mt.componentskeleton::jsonb->'pieces') > 0
                  AND NOT EXISTS (SELECT 1 FROM skeleton_piece_requirements spr WHERE spr."modeltypeid" = mt.id)
                  AND EXISTS (SELECT 1 FROM model_types ref WHERE ref.id = (piece->>'typePieceId'))
            SQL);

        // ── Data migration: componentSkeleton.products → skeleton_product_requirements ──

        $this->addSql(<<<'SQL'
                INSERT INTO skeleton_product_requirements (id, "modeltypeid", "typeproductid", "familycode", position, "createdat", "updatedat")
                SELECT
                    'cl' || encode(gen_random_bytes(12), 'hex'),
                    mt.id,
                    (product->>'typeProductId'),
                    (product->>'familyCode'),
                    (ordinality - 1)::int,
                    NOW(), NOW()
                FROM model_types mt,
                LATERAL jsonb_array_elements(mt.componentskeleton::jsonb->'products') WITH ORDINALITY AS t(product, ordinality)
                WHERE mt.category = 'COMPONENT'
                  AND mt.componentskeleton IS NOT NULL
                  AND mt.componentskeleton::jsonb->'products' IS NOT NULL
                  AND jsonb_array_length(mt.componentskeleton::jsonb->'products') > 0
                  AND NOT EXISTS (SELECT 1 FROM skeleton_product_requirements spr WHERE spr."modeltypeid" = mt.id)
                  AND EXISTS (SELECT 1 FROM model_types ref WHERE ref.id = (product->>'typeProductId'))
            SQL);

        // ── Data migration: pieceSkeleton.products → skeleton_product_requirements ──

        $this->addSql(<<<'SQL'
                INSERT INTO skeleton_product_requirements (id, "modeltypeid", "typeproductid", "familycode", position, "createdat", "updatedat")
                SELECT
                    'cl' || encode(gen_random_bytes(12), 'hex'),
                    mt.id,
                    (product->>'typeProductId'),
                    (product->>'familyCode'),
                    (ordinality - 1)::int,
                    NOW(), NOW()
                FROM model_types mt,
                LATERAL jsonb_array_elements(mt.pieceskeleton::jsonb->'products') WITH ORDINALITY AS t(product, ordinality)
                WHERE mt.category = 'PIECE'
                  AND mt.pieceskeleton IS NOT NULL
                  AND mt.pieceskeleton::jsonb->'products' IS NOT NULL
                  AND jsonb_array_length(mt.pieceskeleton::jsonb->'products') > 0
                  AND NOT EXISTS (SELECT 1 FROM skeleton_product_requirements spr WHERE spr."modeltypeid" = mt.id)
                  AND EXISTS (SELECT 1 FROM model_types ref WHERE ref.id = (product->>'typeProductId'))
            SQL);

        // ── Data migration: componentSkeleton.subcomponents → skeleton_subcomponent_requirements ──

        $this->addSql(<<<'SQL'
                INSERT INTO skeleton_subcomponent_requirements (id, "modeltypeid", alias, "familycode", "typecomposantid", position, "createdat", "updatedat")
                SELECT
                    'cl' || encode(gen_random_bytes(12), 'hex'),
                    mt.id,
                    COALESCE(sub->>'alias', ''),
                    COALESCE(sub->>'familyCode', ''),
                    NULLIF(sub->>'typeComposantId', ''),
                    (ordinality - 1)::int,
                    NOW(), NOW()
                FROM model_types mt,
                LATERAL jsonb_array_elements(mt.componentskeleton::jsonb->'subcomponents') WITH ORDINALITY AS t(sub, ordinality)
                WHERE mt.category = 'COMPONENT'
                  AND mt.componentskeleton IS NOT NULL
                  AND mt.componentskeleton::jsonb->'subcomponents' IS NOT NULL
                  AND jsonb_array_length(mt.componentskeleton::jsonb->'subcomponents') > 0
                  AND NOT EXISTS (SELECT 1 FROM skeleton_subcomponent_requirements ssr WHERE ssr."modeltypeid" = mt.id)
                  AND (NULLIF(sub->>'typeComposantId', '') IS NULL OR EXISTS (SELECT 1 FROM model_types ref WHERE ref.id = (sub->>'typeComposantId')))
            SQL);
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP TABLE IF EXISTS skeleton_subcomponent_requirements');
        $this->addSql('DROP TABLE IF EXISTS skeleton_product_requirements');
        $this->addSql('DROP TABLE IF EXISTS skeleton_piece_requirements');
    }
}