import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Phase 1 of the multi-supplier-per-line-item refactor.
 *
 * WHY:
 *   The current schema stores supplier assignments as fixed columns
 *   on the item row — supplier_id + supplier_2_id on transport, a
 *   single supplier_id on activity + special. That model tops out at
 *   two suppliers on transport and one everywhere else, forces every
 *   consumer to write COALESCE(supplier.name, supplier_name_text) to
 *   read a name, and can't express "60% of cost to vendor A, 40% to
 *   vendor B". See the design review in the session notes for the
 *   full breakdown.
 *
 * WHAT:
 *   Three per-type junction tables (real FKs, not polymorphic):
 *     - booking_transport_item_suppliers
 *     - booking_activity_item_suppliers
 *     - booking_special_item_suppliers
 *
 *   Each carries: (tenant_id, item_id, supplier_id | supplier_name_text,
 *   role, sort_order, actual_supplier_cost, standard audit columns).
 *
 * BACKFILL:
 *   Every existing item row with a supplier_id / supplier_name_text
 *   becomes one junction row at sort_order 0, role 'primary'. Every
 *   transport row with a supplier_2_id also produces a second row at
 *   sort_order 1, role 'secondary'. `actual_supplier_cost` on the
 *   item stays put — it copies to the FIRST junction row (only one
 *   supplier was cost-attributed under the old model). Subsequent
 *   phases move consumers off the columns; Phase 5 drops them.
 *
 * COMPAT:
 *   The old supplier_* columns on the item tables are NOT dropped in
 *   this migration — Phase 3 dual-writes to both, Phase 4 cuts the FE
 *   over, Phase 5 drops. Read paths keep working through the whole
 *   sequence.
 */
export class BookingItemSupplierJunction1784000000000 implements MigrationInterface {
  name = 'BookingItemSupplierJunction1784000000000';

  public async up(qr: QueryRunner): Promise<void> {
    // ── Transport junction ────────────────────────────────────────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "booking_transport_item_suppliers" (
        "id"                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        "tenant_id"             uuid NOT NULL,
        "transport_item_id"     uuid NOT NULL,
        "supplier_id"           uuid,
        "supplier_name_text"    varchar(255),
        "supplier_phone_text"   varchar(50),
        "role"                  varchar(30) NOT NULL DEFAULT 'primary',
        "sort_order"            integer NOT NULL DEFAULT 0,
        "actual_supplier_cost"  numeric(12,2),
        "is_deleted"            boolean NOT NULL DEFAULT false,
        "created_by"            uuid,
        "updated_by"            uuid,
        "created_at"            timestamptz NOT NULL DEFAULT now(),
        "updated_at"            timestamptz NOT NULL DEFAULT now(),
        CONSTRAINT "fk_btis_transport_item"
          FOREIGN KEY ("transport_item_id")
          REFERENCES "booking_transport_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_btis_supplier"
          FOREIGN KEY ("supplier_id")
          REFERENCES "suppliers"("id"),
        CONSTRAINT "chk_btis_supplier_present"
          CHECK ("supplier_id" IS NOT NULL OR "supplier_name_text" IS NOT NULL)
      )
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "uq_btis_item_supplier_role"
      ON "booking_transport_item_suppliers" ("transport_item_id", "supplier_id", "role")
      WHERE "is_deleted" = false AND "supplier_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_btis_item"
      ON "booking_transport_item_suppliers" ("tenant_id", "transport_item_id")
      WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_btis_supplier"
      ON "booking_transport_item_suppliers" ("tenant_id", "supplier_id")
      WHERE "is_deleted" = false
    `);

    // ── Activity junction ────────────────────────────────────────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "booking_activity_item_suppliers" (
        "id"                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        "tenant_id"             uuid NOT NULL,
        "activity_item_id"      uuid NOT NULL,
        "supplier_id"           uuid,
        "supplier_name_text"    varchar(255),
        "supplier_phone_text"   varchar(50),
        "role"                  varchar(30) NOT NULL DEFAULT 'primary',
        "sort_order"            integer NOT NULL DEFAULT 0,
        "actual_supplier_cost"  numeric(12,2),
        "is_deleted"            boolean NOT NULL DEFAULT false,
        "created_by"            uuid,
        "updated_by"            uuid,
        "created_at"            timestamptz NOT NULL DEFAULT now(),
        "updated_at"            timestamptz NOT NULL DEFAULT now(),
        CONSTRAINT "fk_bais_activity_item"
          FOREIGN KEY ("activity_item_id")
          REFERENCES "booking_activity_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_bais_supplier"
          FOREIGN KEY ("supplier_id")
          REFERENCES "suppliers"("id"),
        CONSTRAINT "chk_bais_supplier_present"
          CHECK ("supplier_id" IS NOT NULL OR "supplier_name_text" IS NOT NULL)
      )
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "uq_bais_item_supplier_role"
      ON "booking_activity_item_suppliers" ("activity_item_id", "supplier_id", "role")
      WHERE "is_deleted" = false AND "supplier_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bais_item"
      ON "booking_activity_item_suppliers" ("tenant_id", "activity_item_id")
      WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bais_supplier"
      ON "booking_activity_item_suppliers" ("tenant_id", "supplier_id")
      WHERE "is_deleted" = false
    `);

    // ── Special junction ────────────────────────────────────────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "booking_special_item_suppliers" (
        "id"                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        "tenant_id"             uuid NOT NULL,
        "special_item_id"       uuid NOT NULL,
        "supplier_id"           uuid,
        "supplier_name_text"    varchar(255),
        "supplier_phone_text"   varchar(50),
        "role"                  varchar(30) NOT NULL DEFAULT 'primary',
        "sort_order"            integer NOT NULL DEFAULT 0,
        "actual_supplier_cost"  numeric(12,2),
        "is_deleted"            boolean NOT NULL DEFAULT false,
        "created_by"            uuid,
        "updated_by"            uuid,
        "created_at"            timestamptz NOT NULL DEFAULT now(),
        "updated_at"            timestamptz NOT NULL DEFAULT now(),
        CONSTRAINT "fk_bsis_special_item"
          FOREIGN KEY ("special_item_id")
          REFERENCES "booking_special_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_bsis_supplier"
          FOREIGN KEY ("supplier_id")
          REFERENCES "suppliers"("id"),
        CONSTRAINT "chk_bsis_supplier_present"
          CHECK ("supplier_id" IS NOT NULL OR "supplier_name_text" IS NOT NULL)
      )
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "uq_bsis_item_supplier_role"
      ON "booking_special_item_suppliers" ("special_item_id", "supplier_id", "role")
      WHERE "is_deleted" = false AND "supplier_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bsis_item"
      ON "booking_special_item_suppliers" ("tenant_id", "special_item_id")
      WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bsis_supplier"
      ON "booking_special_item_suppliers" ("tenant_id", "supplier_id")
      WHERE "is_deleted" = false
    `);

    // ── Backfill from existing columns ────────────────────────────

    // Transport — primary supplier (sort_order 0).
    // WHERE NOT EXISTS guards re-runs and cases where Phase 3 dual-
    // write ran before the backfill. actual_supplier_cost copies to
    // the primary row only — the secondary row (below) doesn't get a
    // cost until ops explicitly re-attributes it.
    await qr.query(`
      INSERT INTO "booking_transport_item_suppliers"
        (tenant_id, transport_item_id, supplier_id, supplier_name_text,
         role, sort_order, actual_supplier_cost, created_at, updated_at)
      SELECT ti.tenant_id, ti.id, ti.supplier_id, ti.supplier_name_text,
             'primary', 0, ti.actual_supplier_cost, now(), now()
        FROM booking_transport_items ti
       WHERE ti.is_deleted = false
         AND (ti.supplier_id IS NOT NULL OR ti.supplier_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM booking_transport_item_suppliers j
            WHERE j.transport_item_id = ti.id
              AND j.role = 'primary'
              AND j.is_deleted = false
         )
    `);

    // Transport — secondary supplier (sort_order 1) where supplier_2 is set.
    await qr.query(`
      INSERT INTO "booking_transport_item_suppliers"
        (tenant_id, transport_item_id, supplier_id, supplier_name_text,
         role, sort_order, created_at, updated_at)
      SELECT ti.tenant_id, ti.id, ti.supplier_2_id, ti.supplier_2_name_text,
             'secondary', 1, now(), now()
        FROM booking_transport_items ti
       WHERE ti.is_deleted = false
         AND (ti.supplier_2_id IS NOT NULL OR ti.supplier_2_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM booking_transport_item_suppliers j
            WHERE j.transport_item_id = ti.id
              AND j.role = 'secondary'
              AND j.is_deleted = false
         )
    `);

    // Activity — primary supplier + phone text.
    await qr.query(`
      INSERT INTO "booking_activity_item_suppliers"
        (tenant_id, activity_item_id, supplier_id, supplier_name_text,
         supplier_phone_text, role, sort_order, actual_supplier_cost,
         created_at, updated_at)
      SELECT ai.tenant_id, ai.id, ai.supplier_id, ai.supplier_name_text,
             ai.supplier_phone_text, 'primary', 0, ai.actual_supplier_cost,
             now(), now()
        FROM booking_activity_items ai
       WHERE ai.is_deleted = false
         AND (ai.supplier_id IS NOT NULL OR ai.supplier_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM booking_activity_item_suppliers j
            WHERE j.activity_item_id = ai.id
              AND j.role = 'primary'
              AND j.is_deleted = false
         )
    `);

    // Special — primary supplier + phone text.
    await qr.query(`
      INSERT INTO "booking_special_item_suppliers"
        (tenant_id, special_item_id, supplier_id, supplier_name_text,
         supplier_phone_text, role, sort_order, actual_supplier_cost,
         created_at, updated_at)
      SELECT si.tenant_id, si.id, si.supplier_id, si.supplier_name_text,
             si.supplier_phone_text, 'primary', 0, si.actual_supplier_cost,
             now(), now()
        FROM booking_special_items si
       WHERE si.is_deleted = false
         AND (si.supplier_id IS NOT NULL OR si.supplier_name_text IS NOT NULL)
         AND NOT EXISTS (
           SELECT 1 FROM booking_special_item_suppliers j
            WHERE j.special_item_id = si.id
              AND j.role = 'primary'
              AND j.is_deleted = false
         )
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    // Order matters — drop indexes first, then tables. The FKs go
    // when the tables drop. No data restore — the original columns
    // still hold the primary supplier.
    await qr.query(`DROP TABLE IF EXISTS "booking_special_item_suppliers"`);
    await qr.query(`DROP TABLE IF EXISTS "booking_activity_item_suppliers"`);
    await qr.query(`DROP TABLE IF EXISTS "booking_transport_item_suppliers"`);
  }
}