import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Movement Dashboard status model change (client 2026-10-10):
 *   add UNPLANNED (default, no supplier) + HOLD; remove DISPATCHED + DROPPED.
 *   Final set: UNPLANNED, PLANNED, HOLD, PICKED_UP, NO_SHOW, CANCELLED.
 *
 * Data mapping (applied while NO op_status CHECK is in force):
 *   - DISPATCHED    → PLANNED   (the optional hop is gone)
 *   - old PICKED_UP → PLANNED   (was "in transit", not the done state;
 *                                mapping to PLANNED keeps it active and
 *                                preserves downstream cash-collection logic)
 *   - DROPPED       → PICKED_UP (old "done/completed" → new done = PICKED_UP)
 *   - anything else / NULL → UNPLANNED (safety net)
 * Then backfill the base states (PLANNED/UNPLANNED) to match supplier
 * presence: a row with a supplier → PLANNED, without → UNPLANNED.
 *
 * NAME-AGNOSTIC constraint swap: the old CHECK constraint may have been
 * created with an explicit name OR a TypeORM auto-generated CHK_<hash>
 * name — we don't know which on a given database. So instead of
 * `DROP CONSTRAINT IF EXISTS "<assumed name>"` (which silently no-ops and
 * leaves the old rule enforcing the OLD value set — blocking UNPLANNED),
 * we discover and drop EVERY CHECK constraint on the table whose
 * definition references op_status, then add the new one under a stable name.
 */
export class OpStatusUnplannedHold1787400000000 implements MigrationInterface {
  name = 'OpStatusUnplannedHold1787400000000';

  private readonly NEW_SET = "'UNPLANNED','PLANNED','HOLD','PICKED_UP','NO_SHOW','CANCELLED'";
  private readonly OLD_SET = "'PLANNED','DISPATCHED','PICKED_UP','DROPPED','NO_SHOW','CANCELLED'";

  /** Drop every CHECK constraint on `table` that mentions op_status,
   *  whatever its name. `table` is an internal literal — no user input. */
  private async dropOpStatusChecks(qr: QueryRunner, table: string): Promise<void> {
    await qr.query(`
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT con.conname
          FROM pg_constraint con
          WHERE con.conrelid = '"${table}"'::regclass
            AND con.contype = 'c'
            AND pg_get_constraintdef(con.oid) ILIKE '%op_status%'
        LOOP
          EXECUTE format('ALTER TABLE "${table}" DROP CONSTRAINT %I', r.conname);
        END LOOP;
      END $$;
    `);
  }

  private async migrateTable(qr: QueryRunner, table: string): Promise<void> {
    // 1) Remove any op_status CHECK first, so the remap below can't be
    //    blocked by either the old or a half-applied new constraint.
    await this.dropOpStatusChecks(qr, table);

    // 2) Remap old values. Order matters: old PICKED_UP ("in transit")
    //    must become PLANNED BEFORE DROPPED ("done") becomes PICKED_UP.
    await qr.query(
      `UPDATE "${table}" SET "op_status" = 'PLANNED' WHERE "op_status" IN ('DISPATCHED','PICKED_UP')`,
    );
    await qr.query(
      `UPDATE "${table}" SET "op_status" = 'PICKED_UP' WHERE "op_status" = 'DROPPED'`,
    );

    // 3) Safety net — any NULL or unexpected legacy value becomes
    //    UNPLANNED so the new CHECK can never reject an existing row.
    await qr.query(`
      UPDATE "${table}"
         SET "op_status" = 'UNPLANNED'
       WHERE "op_status" IS NULL
          OR "op_status" NOT IN (${this.NEW_SET})
    `);

    // 4) Add the new CHECK (stable name) + new default.
    await qr.query(
      `ALTER TABLE "${table}" ADD CONSTRAINT "chk_${table}_op_status" CHECK ("op_status" IN (${this.NEW_SET}))`,
    );
    await qr.query(`ALTER TABLE "${table}" ALTER COLUMN "op_status" SET DEFAULT 'UNPLANNED'`);

    // 5) Backfill base states from supplier presence. Only touches the
    //    two base states; a manual Hold / Picked Up / No-Show / Cancelled
    //    stays as set.
    await qr.query(`
      UPDATE "${table}"
         SET "op_status" = CASE
           WHEN "supplier_id" IS NOT NULL
             OR NULLIF(BTRIM("supplier_name_text"), '') IS NOT NULL
           THEN 'PLANNED' ELSE 'UNPLANNED' END
       WHERE "op_status" IN ('PLANNED','UNPLANNED')
    `);
  }

  public async up(qr: QueryRunner): Promise<void> {
    await this.migrateTable(qr, 'booking_transport_items');
    await this.migrateTable(qr, 'booking_activity_items');
  }

  public async down(qr: QueryRunner): Promise<void> {
    // Best-effort revert: collapse the new-only states into the old set,
    // then restore the old CHECK (also name-agnostic on the drop).
    for (const table of ['booking_transport_items', 'booking_activity_items'] as const) {
      await qr.query(
        `UPDATE "${table}" SET "op_status" = 'PLANNED' WHERE "op_status" IN ('UNPLANNED','HOLD')`,
      );
      await this.dropOpStatusChecks(qr, table);
      await qr.query(
        `ALTER TABLE "${table}" ADD CONSTRAINT "chk_${table}_op_status" CHECK ("op_status" IN (${this.OLD_SET}))`,
      );
      await qr.query(`ALTER TABLE "${table}" ALTER COLUMN "op_status" SET DEFAULT 'PLANNED'`);
    }
  }
}