<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * Fallback migration: if the legacy M2M tables (_composantconstructeurs, _piececonstructeurs)
 * no longer exist in production, this migration inserts the links directly from the backup (3) data.
 *
 * Run AFTER Version20260405_MigrateConstructeurLinks (which handles the case where legacy tables exist).
 * This migration only inserts links that don't already exist (ON CONFLICT DO NOTHING).
 *
 * Note: Some entity IDs from the backup may no longer exist (deleted composants/pieces).
 * The migration disables FK checks temporarily to avoid failures, then cleans up orphans.
 */
final class Version20260405_RestoreConstructeurLinksFromBackup extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Restore constructeur links from backup (3) data — fallback if legacy M2M tables are gone';
    }

    public function up(Schema $schema): void
    {
        // Temporarily disable FK checks
        $this->addSql('SET session_replication_role = replica');

        // === COMPOSANT-CONSTRUCTEUR LINKS (5 from backup 3) ===
        $this->addSql("
            INSERT INTO composant_constructeur_links (id, composantid, constructeurid, supplierreference, createdat, updatedat)
            VALUES
                ('clbkp3_cc_001', 'cmgz7fd3l009y47fff1l4g0p0', 'cmgqp5dvp00014705qpkci8qc', NULL, NOW(), NOW()),
                ('clbkp3_cc_002', 'cmh3jvqoa002y47zbctflkydc', 'cmhnaaoam000847s85wfwi2wm', NULL, NOW(), NOW()),
                ('clbkp3_cc_003', 'cmh0d59v5000347s561ahbept', 'cmhnaaoam000847s85wfwi2wm', NULL, NOW(), NOW()),
                ('clbkp3_cc_004', 'cmh0d59v5000347s561ahbept', 'cmg93n9sk000047uuwm6u20mj', NULL, NOW(), NOW()),
                ('clbkp3_cc_005', 'cmkr0nq1a004e1eq6v6ubxlfl', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW())
            ON CONFLICT (id) DO NOTHING
        ");

        // === PIECE-CONSTRUCTEUR LINKS (25 from backup 3) ===
        $this->addSql("
            INSERT INTO piece_constructeur_links (id, pieceid, constructeurid, supplierreference, createdat, updatedat)
            VALUES
                ('clbkp3_pc_001', 'cmizudzfy00021e2w2mtd9zv8', 'cmizu5ugx00011e2wjpr6nb3k', NULL, NOW(), NOW()),
                ('clbkp3_pc_002', 'cmizv8nzu00081e2wen6ur31b', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW()),
                ('clbkp3_pc_003', 'cmjcixqq300141e2wqkvz0cx6', 'cmjcirqnh00101e2w0ht25qic', NULL, NOW(), NOW()),
                ('clbkp3_pc_004', 'cmjcixqq300141e2wqkvz0cx6', 'cmjcismo400111e2whfxnsnd3', NULL, NOW(), NOW()),
                ('clbkp3_pc_005', 'cmjcixqq300141e2wqkvz0cx6', 'cmjciuk3t00121e2wxtz9o5fh', NULL, NOW(), NOW()),
                ('clbkp3_pc_006', 'cmjcixqq300141e2wqkvz0cx6', 'cmjcivgex00131e2wf04n31ql', NULL, NOW(), NOW()),
                ('clbkp3_pc_007', 'cmjcpdwqs00161e2wu4juy4u2', 'cmjcirqnh00101e2w0ht25qic', NULL, NOW(), NOW()),
                ('clbkp3_pc_008', 'cmkr20cpy005a1eq6nn5kmtys', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_009', 'cmkr25xz1005v1eq6i0fib4er', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_010', 'clcff0f15790b2c7084f781df6', 'cl219849fbab8bbaf6163f5700', NULL, NOW(), NOW()),
                ('clbkp3_pc_011', 'cl4807538979ddd27099d77578', 'cl219849fbab8bbaf6163f5700', NULL, NOW(), NOW()),
                ('clbkp3_pc_012', 'cl960c6ffcfabd9eeb2b1452ab', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_013', 'cl5b928245d51ff4f037f6cc6d', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_014', 'cl92edc1a20a7fd0f1355fd476', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_015', 'cl811abd3d9d8ba63585424906', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_016', 'clfd2b5e2570b0be44f7196870', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_017', 'cl0ba5ceffb2e5496624087d85', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_018', 'cl50fe870a07e42759b37b511f', 'cl219849fbab8bbaf6163f5700', NULL, NOW(), NOW()),
                ('clbkp3_pc_019', 'cl9de983224260763d7ea6fe95', 'cmkqpnznr001p1eq6hdh2ept8', NULL, NOW(), NOW()),
                ('clbkp3_pc_020', 'clbd07ebf2568ea14ac792ba49', 'cmhaac3vo003547v7s1wv6jhv', NULL, NOW(), NOW()),
                ('clbkp3_pc_021', 'cl754c25154e5546882a7d6706', 'cmhaac3vo003547v7s1wv6jhv', NULL, NOW(), NOW()),
                ('clbkp3_pc_022', 'clc8fa00057b54d782c06aebd0', 'cmhaac3vo003547v7s1wv6jhv', NULL, NOW(), NOW()),
                ('clbkp3_pc_023', 'cl6480b97f6516fba22ce86434', 'cmg93n9sk000047uuwm6u20mj', NULL, NOW(), NOW()),
                ('clbkp3_pc_024', 'cl9579b05774d92096117841b0', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW()),
                ('clbkp3_pc_025', 'cl5b02c64fcc5ae8a3bfb6e5e6', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW())
            ON CONFLICT (id) DO NOTHING
        ");

        // Note: 6 additional Limatech piece links from backup (3) used old Limatech ID 'cla14aa4a50a799c2e54391be7'.
        // Limatech was recreated with a new ID. These links use the current Limatech ID (cmizv4lm500071e2w6xymi2p6)
        // for the last 2 entries above. The remaining 4 Limatech links:
        $this->addSql("
            INSERT INTO piece_constructeur_links (id, pieceid, constructeurid, supplierreference, createdat, updatedat)
            VALUES
                ('clbkp3_pc_026', 'cle788fea147886d499676b745', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW()),
                ('clbkp3_pc_027', 'clfd3cb41a407ab5a3f9d5baae', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW()),
                ('clbkp3_pc_028', 'cld08dae22796b5855152580d9', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW()),
                ('clbkp3_pc_029', 'clf6012fca41994c1e81ce2dba', 'cmizv4lm500071e2w6xymi2p6', NULL, NOW(), NOW())
            ON CONFLICT (id) DO NOTHING
        ");

        // Re-enable FK checks
        $this->addSql('SET session_replication_role = DEFAULT');

        // Clean up orphaned rows (entities that no longer exist)
        $this->addSql("
            DELETE FROM composant_constructeur_links
            WHERE id LIKE 'clbkp3_%'
            AND composantid NOT IN (SELECT id FROM composants)
        ");
        $this->addSql("
            DELETE FROM piece_constructeur_links
            WHERE id LIKE 'clbkp3_%'
            AND pieceid NOT IN (SELECT id FROM pieces)
        ");
        $this->addSql("
            DELETE FROM composant_constructeur_links
            WHERE id LIKE 'clbkp3_%'
            AND constructeurid NOT IN (SELECT id FROM constructeurs)
        ");
        $this->addSql("
            DELETE FROM piece_constructeur_links
            WHERE id LIKE 'clbkp3_%'
            AND constructeurid NOT IN (SELECT id FROM constructeurs)
        ");
    }

    public function down(Schema $schema): void
    {
        $this->addSql("DELETE FROM composant_constructeur_links WHERE id LIKE 'clbkp3_%'");
        $this->addSql("DELETE FROM piece_constructeur_links WHERE id LIKE 'clbkp3_%'");
    }
}