Inventory/migrations/Version20260323100000.php

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * Scaffold missing composant slots for existing composants that have
 * a typeComposant with skeleton requirements but no corresponding slots.
 */
final class Version20260323100000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Scaffold missing composant slots from skeleton requirements for existing composants';
    }

    public function up(Schema $schema): void
    {
        // Piece slots
        $this->addSql(<<<'SQL'
                INSERT INTO composant_piece_slots (id, "composantid", "typepieceid", quantity, position, "createdat", "updatedat")
                SELECT
                  'cl' || substr(md5(random()::text || clock_timestamp()::text || spr.id), 1, 24),
                  c.id,
                  spr."typepieceid",
                  1,
                  spr.position,
                  NOW(),
                  NOW()
                FROM composants c
                JOIN skeleton_piece_requirements spr ON spr."modeltypeid" = c."typecomposantid"
                WHERE c."typecomposantid" IS NOT NULL
                  AND NOT EXISTS (
                    SELECT 1 FROM composant_piece_slots cps
                    WHERE cps."composantid" = c.id AND cps."typepieceid" = spr."typepieceid"
                  )
            SQL);

        // Product slots
        $this->addSql(<<<'SQL'
                INSERT INTO composant_product_slots (id, "composantid", "typeproductid", "familycode", position, "createdat", "updatedat")
                SELECT
                  'cl' || substr(md5(random()::text || clock_timestamp()::text || spr.id), 1, 24),
                  c.id,
                  spr."typeproductid",
                  spr."familycode",
                  spr.position,
                  NOW(),
                  NOW()
                FROM composants c
                JOIN skeleton_product_requirements spr ON spr."modeltypeid" = c."typecomposantid"
                WHERE c."typecomposantid" IS NOT NULL
                  AND NOT EXISTS (
                    SELECT 1 FROM composant_product_slots cps
                    WHERE cps."composantid" = c.id AND cps."typeproductid" = spr."typeproductid"
                  )
            SQL);

        // Subcomponent slots
        $this->addSql(<<<'SQL'
                INSERT INTO composant_subcomponent_slots (id, "composantid", alias, "familycode", "typecomposantid", position, "createdat", "updatedat")
                SELECT
                  'cl' || substr(md5(random()::text || clock_timestamp()::text || spr.id), 1, 24),
                  c.id,
                  spr.alias,
                  spr."familycode",
                  spr."typecomposantid",
                  spr.position,
                  NOW(),
                  NOW()
                FROM composants c
                JOIN skeleton_subcomponent_requirements spr ON spr."modeltypeid" = c."typecomposantid"
                WHERE c."typecomposantid" IS NOT NULL
                  AND NOT EXISTS (
                    SELECT 1 FROM composant_subcomponent_slots css
                    WHERE css."composantid" = c.id
                      AND COALESCE(css."typecomposantid", '') = COALESCE(spr."typecomposantid", '')
                      AND COALESCE(css.alias, '') = COALESCE(spr.alias, '')
                  )
            SQL);
    }

    public function down(Schema $schema): void
    {
        // No-op: slots created by this migration are valid data
    }
}