Inventory/migrations/Version20260403_CategoryOnlyLinks.php

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

final class Version20260403_CategoryOnlyLinks extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Allow category-only machine links: make entity FKs nullable, add modelType FK to link tables';
    }

    public function up(Schema $schema): void
    {
        // 1. Make entity FK columns nullable
        $this->addSql('ALTER TABLE machine_component_links ALTER COLUMN composantid DROP NOT NULL');
        $this->addSql('ALTER TABLE machine_piece_links ALTER COLUMN pieceid DROP NOT NULL');
        $this->addSql('ALTER TABLE machine_product_links ALTER COLUMN productid DROP NOT NULL');

        // 2. Add modeltypeid column to all 3 tables
        $this->addSql('ALTER TABLE machine_component_links ADD COLUMN IF NOT EXISTS modeltypeid VARCHAR(36) DEFAULT NULL');
        $this->addSql('ALTER TABLE machine_piece_links ADD COLUMN IF NOT EXISTS modeltypeid VARCHAR(36) DEFAULT NULL');
        $this->addSql('ALTER TABLE machine_product_links ADD COLUMN IF NOT EXISTS modeltypeid VARCHAR(36) DEFAULT NULL');

        // 3. Add FK constraints from modeltypeid to model_types(id) ON DELETE SET NULL
        $this->addSql(<<<'SQL'
            DO $$ BEGIN
              IF NOT EXISTS (
                SELECT 1 FROM information_schema.table_constraints
                WHERE constraint_name = 'fk_machine_component_links_modeltype' AND table_name = 'machine_component_links'
              ) THEN
                ALTER TABLE machine_component_links ADD CONSTRAINT fk_machine_component_links_modeltype
                  FOREIGN KEY (modeltypeid) REFERENCES model_types(id) ON DELETE SET NULL;
              END IF;
            END $$;
            SQL);

        $this->addSql(<<<'SQL'
            DO $$ BEGIN
              IF NOT EXISTS (
                SELECT 1 FROM information_schema.table_constraints
                WHERE constraint_name = 'fk_machine_piece_links_modeltype' AND table_name = 'machine_piece_links'
              ) THEN
                ALTER TABLE machine_piece_links ADD CONSTRAINT fk_machine_piece_links_modeltype
                  FOREIGN KEY (modeltypeid) REFERENCES model_types(id) ON DELETE SET NULL;
              END IF;
            END $$;
            SQL);

        $this->addSql(<<<'SQL'
            DO $$ BEGIN
              IF NOT EXISTS (
                SELECT 1 FROM information_schema.table_constraints
                WHERE constraint_name = 'fk_machine_product_links_modeltype' AND table_name = 'machine_product_links'
              ) THEN
                ALTER TABLE machine_product_links ADD CONSTRAINT fk_machine_product_links_modeltype
                  FOREIGN KEY (modeltypeid) REFERENCES model_types(id) ON DELETE SET NULL;
              END IF;
            END $$;
            SQL);

        // 4. Add indexes on modeltypeid
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_machine_component_links_modeltypeid ON machine_component_links (modeltypeid)');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_machine_piece_links_modeltypeid ON machine_piece_links (modeltypeid)');
        $this->addSql('CREATE INDEX IF NOT EXISTS idx_machine_product_links_modeltypeid ON machine_product_links (modeltypeid)');
    }

    public function down(Schema $schema): void
    {
        // Drop indexes
        $this->addSql('DROP INDEX IF EXISTS idx_machine_component_links_modeltypeid');
        $this->addSql('DROP INDEX IF EXISTS idx_machine_piece_links_modeltypeid');
        $this->addSql('DROP INDEX IF EXISTS idx_machine_product_links_modeltypeid');

        // Drop FK constraints
        $this->addSql('ALTER TABLE machine_component_links DROP CONSTRAINT IF EXISTS fk_machine_component_links_modeltype');
        $this->addSql('ALTER TABLE machine_piece_links DROP CONSTRAINT IF EXISTS fk_machine_piece_links_modeltype');
        $this->addSql('ALTER TABLE machine_product_links DROP CONSTRAINT IF EXISTS fk_machine_product_links_modeltype');

        // Drop modeltypeid columns
        $this->addSql('ALTER TABLE machine_component_links DROP COLUMN IF EXISTS modeltypeid');
        $this->addSql('ALTER TABLE machine_piece_links DROP COLUMN IF EXISTS modeltypeid');
        $this->addSql('ALTER TABLE machine_product_links DROP COLUMN IF EXISTS modeltypeid');

        // Restore NOT NULL on entity FK columns
        $this->addSql('ALTER TABLE machine_component_links ALTER COLUMN composantid SET NOT NULL');
        $this->addSql('ALTER TABLE machine_piece_links ALTER COLUMN pieceid SET NOT NULL');
        $this->addSql('ALTER TABLE machine_product_links ALTER COLUMN productid SET NOT NULL');
    }
}