import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * A container type / cargo type can now be carried by SEVERAL wagon types
 * (e.g. a 20ft container rides NX70 or NW5). Replaces the single
 * `wagon_type_id` FK on both tables with proper link tables; train scheduling
 * resolves the wagon type from the list, picking whichever type the schedule's
 * built train (or the yard) actually has.
 *
 * Backfills one link row from each existing `wagon_type_id`, then drops the
 * old column — the single-FK field is removed from the API and UI entirely.
 *
 * NOTE: the shared dev DB has no applied migration history, so this is also
 * hand-applied there. IF NOT EXISTS keeps that idempotent.
 */
export class MultiWagonTypePerCargoAndContainer2160000000000 implements MigrationInterface {
  name = 'MultiWagonTypePerCargoAndContainer2160000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.container_type_wagon_types (
        container_type_id uuid NOT NULL,
        wagon_type_id uuid NOT NULL,
        CONSTRAINT "PK_container_type_wagon_types" PRIMARY KEY (container_type_id, wagon_type_id),
        CONSTRAINT "FK_ctwt_container_type" FOREIGN KEY (container_type_id)
          REFERENCES freight.container_types (id) ON DELETE CASCADE,
        CONSTRAINT "FK_ctwt_wagon_type" FOREIGN KEY (wagon_type_id)
          REFERENCES freight.wagon_types (id) ON DELETE RESTRICT
      );
    `);
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.cargo_type_wagon_types (
        cargo_type_id uuid NOT NULL,
        wagon_type_id uuid NOT NULL,
        CONSTRAINT "PK_cargo_type_wagon_types" PRIMARY KEY (cargo_type_id, wagon_type_id),
        CONSTRAINT "FK_cgwt_cargo_type" FOREIGN KEY (cargo_type_id)
          REFERENCES freight.cargo_types (id) ON DELETE CASCADE,
        CONSTRAINT "FK_cgwt_wagon_type" FOREIGN KEY (wagon_type_id)
          REFERENCES freight.wagon_types (id) ON DELETE RESTRICT
      );
    `);

    // Backfill from the old single FK (column may already be gone on re-run).
    await queryRunner.query(`
      DO $$
      BEGIN
        IF EXISTS (
          SELECT 1 FROM information_schema.columns
          WHERE table_schema = 'freight' AND table_name = 'container_types'
            AND column_name = 'wagon_type_id'
        ) THEN
          INSERT INTO freight.container_type_wagon_types (container_type_id, wagon_type_id)
          SELECT ct.id, ct.wagon_type_id
            FROM freight.container_types ct
           WHERE ct.wagon_type_id IS NOT NULL
          ON CONFLICT DO NOTHING;
        END IF;
      END $$;
    `);
    await queryRunner.query(`
      DO $$
      BEGIN
        IF EXISTS (
          SELECT 1 FROM information_schema.columns
          WHERE table_schema = 'freight' AND table_name = 'cargo_types'
            AND column_name = 'wagon_type_id'
        ) THEN
          INSERT INTO freight.cargo_type_wagon_types (cargo_type_id, wagon_type_id)
          SELECT cg.id, cg.wagon_type_id
            FROM freight.cargo_types cg
           WHERE cg.wagon_type_id IS NOT NULL
          ON CONFLICT DO NOTHING;
        END IF;
      END $$;
    `);

    // Old single-FK column is fully retired (API + UI now use the lists).
    await queryRunner.query(`
      ALTER TABLE freight.container_types DROP COLUMN IF EXISTS wagon_type_id;
    `);
    await queryRunner.query(`
      ALTER TABLE freight.cargo_types DROP COLUMN IF EXISTS wagon_type_id;
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE freight.container_types ADD COLUMN IF NOT EXISTS wagon_type_id uuid
        REFERENCES freight.wagon_types (id) ON DELETE RESTRICT;
    `);
    await queryRunner.query(`
      ALTER TABLE freight.cargo_types ADD COLUMN IF NOT EXISTS wagon_type_id uuid
        REFERENCES freight.wagon_types (id) ON DELETE RESTRICT;
    `);
    // Restore the first linked wagon type per row, then drop the link tables.
    await queryRunner.query(`
      UPDATE freight.container_types ct
         SET wagon_type_id = link.wagon_type_id
        FROM (
          SELECT DISTINCT ON (container_type_id) container_type_id, wagon_type_id
            FROM freight.container_type_wagon_types
           ORDER BY container_type_id, wagon_type_id
        ) link
       WHERE link.container_type_id = ct.id;
    `);
    await queryRunner.query(`
      UPDATE freight.cargo_types cg
         SET wagon_type_id = link.wagon_type_id
        FROM (
          SELECT DISTINCT ON (cargo_type_id) cargo_type_id, wagon_type_id
            FROM freight.cargo_type_wagon_types
           ORDER BY cargo_type_id, wagon_type_id
        ) link
       WHERE link.cargo_type_id = cg.id;
    `);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.container_type_wagon_types;`);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.cargo_type_wagon_types;`);
  }
}
