import { MigrationInterface, QueryRunner } from "typeorm";

/**
 * Replace load-type string matching with a real wagon-type foreign key.
 *
 * Before this migration, train scheduling picked a wagon type by matching
 * strings — a hardcoded cargo-code → wagon-code map for bulk (COFFEE→KW2, …)
 * and a fixed NW5 default for every container. This adds `wagon_type_id` FKs on
 * `cargo_types` and `container_types` so scheduling resolves the wagon type
 * through the relation instead.
 *
 * The columns are NULLABLE: cargo grouping rows and container/legacy cargo that
 * never ship in bulk have no wagon type, and forcing one onto them is
 * meaningless. Scheduling enforces the requirement at run time (it throws when a
 * scheduled bulk cargo type or a container type in the batch has no wagon type).
 *
 * Backfill reproduces the old hardcoded resolution one final time so existing
 * bulk cargo + container rows are not left unset. After this, the runtime map is
 * removed — the FK is the single source of truth.
 */
export class AddWagonTypeFkToCargoAndContainerTypes1940000000000
  implements MigrationInterface
{
  name = "AddWagonTypeFkToCargoAndContainerTypes1940000000000";

  public async up(queryRunner: QueryRunner): Promise<void> {
    // ── Columns + FKs ────────────────────────────────────────────────────────
    await queryRunner.query(`
      ALTER TABLE freight.cargo_types
        ADD COLUMN IF NOT EXISTS wagon_type_id uuid;
    `);
    await queryRunner.query(`
      ALTER TABLE freight.container_types
        ADD COLUMN IF NOT EXISTS wagon_type_id uuid;
    `);

    await queryRunner.query(`
      ALTER TABLE freight.cargo_types
        ADD CONSTRAINT fk_cargo_types_wagon_type
        FOREIGN KEY (wagon_type_id)
        REFERENCES freight.wagon_types(id)
        ON DELETE RESTRICT;
    `);
    await queryRunner.query(`
      ALTER TABLE freight.container_types
        ADD CONSTRAINT fk_container_types_wagon_type
        FOREIGN KEY (wagon_type_id)
        REFERENCES freight.wagon_types(id)
        ON DELETE RESTRICT;
    `);

    await queryRunner.query(`
      CREATE INDEX IF NOT EXISTS idx_cargo_types_wagon_type_id
        ON freight.cargo_types (wagon_type_id);
    `);
    await queryRunner.query(`
      CREATE INDEX IF NOT EXISTS idx_container_types_wagon_type_id
        ON freight.container_types (wagon_type_id);
    `);

    // ── Backfill: old cargo-code → wagon-code map (one last time) ─────────────
    // COFFEE/GRAIN/WHEAT/SORGHUM/CORN → KW2, FERTILIZER/SUGAR → PW2,
    // COAL → KW3, STEEL/ORE → CW3. Unmapped bulk cargo → CW3 (old default).
    const cargoCodeToWagon: Record<string, string> = {
      COFFEE: "KW2",
      GRAIN: "KW2",
      WHEAT: "KW2",
      SORGHUM: "KW2",
      CORN: "KW2",
      FERTILIZER: "PW2",
      SUGAR: "PW2",
      COAL: "KW3",
      STEEL: "CW3",
      ORE: "CW3",
    };

    for (const [cargoCode, wagonCode] of Object.entries(cargoCodeToWagon)) {
      await queryRunner.query(
        `
        UPDATE freight.cargo_types ct
        SET wagon_type_id = wt.id
        FROM freight.wagon_types wt
        WHERE wt.code = $1
          AND UPPER(TRIM(ct.code)) = $2
          AND ct.wagon_type_id IS NULL;
        `,
        [wagonCode, cargoCode],
      );
    }

    // Remaining bulk cargo (PER_TON) without a mapped code → default bulk wagon CW3.
    await queryRunner.query(`
      UPDATE freight.cargo_types ct
      SET wagon_type_id = wt.id
      FROM freight.wagon_types wt
      WHERE wt.code = 'CW3'
        AND ct.wagon_type_id IS NULL
        AND ct.unit_of_measure = 'PER_TON';
    `);

    // All container types → the old container default wagon NW5.
    await queryRunner.query(`
      UPDATE freight.container_types ct
      SET wagon_type_id = wt.id
      FROM freight.wagon_types wt
      WHERE wt.code = 'NW5'
        AND ct.wagon_type_id IS NULL;
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DROP INDEX IF EXISTS freight.idx_container_types_wagon_type_id;
    `);
    await queryRunner.query(`
      DROP INDEX IF EXISTS freight.idx_cargo_types_wagon_type_id;
    `);
    await queryRunner.query(`
      ALTER TABLE freight.container_types
        DROP CONSTRAINT IF EXISTS fk_container_types_wagon_type;
    `);
    await queryRunner.query(`
      ALTER TABLE freight.cargo_types
        DROP CONSTRAINT IF EXISTS fk_cargo_types_wagon_type;
    `);
    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;
    `);
  }
}
