import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * DB Hardening pass 1 — three concerns bundled because they're all
 * additive-only schema changes with no service-layer coupling:
 *
 *   1. Composite indexes on the booking_*_item hot paths (audit #1)
 *   2. CHECK constraints on enum-as-varchar columns (audit #3)
 *   3. Junction-table supplier_id FKs upgraded to ON DELETE RESTRICT
 *      (audit #7) — a supplier with a live junction row can no longer
 *      be deleted, so we don't get orphan finance data on a
 *      Settings → Suppliers → Delete click.
 *
 * All statements idempotent (IF NOT EXISTS / IF EXISTS + DO block guard
 * on the ADD CONSTRAINT calls) so re-running the migration is safe.
 * Safe to ship together — none of the three depends on the others.
 */
export class DbHardening11784100000000 implements MigrationInterface {
  name = 'DbHardening11784100000000';

  public async up(qr: QueryRunner): Promise<void> {
    // ── 1. Composite indexes on booking_*_item ────────────────────
    // Accounting collectLines + MD getMovementChart both scan by
    // (tenant_id, booking_id) — currently only tenant_id is indexed
    // on BaseEntity, so PG falls back to a full scan per booking.
    // Partial index (WHERE is_deleted = false) keeps the index skinny
    // since 99% of active queries filter on live rows only.
    for (const table of [
      'booking_transport_items',
      'booking_activity_items',
      'booking_hotel_items',
      'booking_special_items',
    ]) {
      await qr.query(`
        CREATE INDEX IF NOT EXISTS "ix_${table}_tenant_booking"
        ON "${table}" ("tenant_id", "booking_id")
        WHERE "is_deleted" = false
      `);
    }

    // Booking status filter — settlements + MD chart + booking list all
    // filter by (tenant_id, status). Existing (tenant_id, booking_number)
    // unique index doesn't help for status queries.
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bookings_tenant_status"
      ON "bookings" ("tenant_id", "status")
      WHERE "is_deleted" = false
    `);

    // Transport op_status — MD Chart filters by op_status for the
    // Done/Pending stats and dispatch views.
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bti_tenant_op_status"
      ON "booking_transport_items" ("tenant_id", "op_status")
      WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_bai_tenant_op_status"
      ON "booking_activity_items" ("tenant_id", "op_status")
      WHERE "is_deleted" = false
    `);

    // ── 2. CHECK constraints on enum-as-varchar ──────────────────
    // Keep the columns as varchar (matches TS enum ↔ Postgres string
    // roundtrip pattern used elsewhere) but reject typos at write time.
    // Guarded with DO blocks so re-runs don't 42710.

    // op_status — transport + activity items share the same enum.
    for (const table of ['booking_transport_items', 'booking_activity_items']) {
      await qr.query(`
        DO $$ BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM pg_constraint
            WHERE conname = 'chk_${table}_op_status'
          ) THEN
            ALTER TABLE "${table}"
              ADD CONSTRAINT "chk_${table}_op_status"
              CHECK ("op_status" IN (
                'PLANNED','DISPATCHED','PICKED_UP','DROPPED','NO_SHOW','CANCELLED'
              ));
          END IF;
        END $$
      `);
    }

    // Booking status.
    await qr.query(`
      DO $$ BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_constraint
          WHERE conname = 'chk_bookings_status'
        ) THEN
          ALTER TABLE "bookings"
            ADD CONSTRAINT "chk_bookings_status"
            CHECK ("status" IN (
              'upcoming','ongoing','completed','cancelled','on_hold'
            ));
        END IF;
      END $$
    `);

    // Booking payment status rollup.
    await qr.query(`
      DO $$ BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_constraint
          WHERE conname = 'chk_bookings_payment_status'
        ) THEN
          -- payment_status is nullable (only populated once a
          -- collection lands) — CHECK allows NULL by default so no
          -- explicit OR IS NULL needed.
          ALTER TABLE "bookings"
            ADD CONSTRAINT "chk_bookings_payment_status"
            CHECK ("payment_status" IN ('UNPAID','PARTIAL','PAID','REFUND_DUE'));
        END IF;
      END $$
    `);

    // ── 3. Junction supplier FK → ON DELETE RESTRICT ─────────────
    // Drop then re-add each FK with RESTRICT. RESTRICT blocks a
    // supplier delete while ANY junction row still references it —
    // preserves finance/audit integrity across historical bookings.
    // Ops workflow: suppliers get is_deleted = true (soft) instead
    // of a physical DELETE, so this constraint only fires on truly
    // stray hard-deletes.
    const junctions: Array<{ table: string; fk: string }> = [
      { table: 'booking_transport_item_suppliers', fk: 'fk_btis_supplier' },
      { table: 'booking_activity_item_suppliers', fk: 'fk_bais_supplier' },
      { table: 'booking_special_item_suppliers', fk: 'fk_bsis_supplier' },
    ];
    for (const { table, fk } of junctions) {
      await qr.query(`ALTER TABLE "${table}" DROP CONSTRAINT IF EXISTS "${fk}"`);
      await qr.query(`
        ALTER TABLE "${table}"
          ADD CONSTRAINT "${fk}"
          FOREIGN KEY ("supplier_id")
          REFERENCES "suppliers"("id")
          ON DELETE RESTRICT
      `);
    }
  }

  public async down(qr: QueryRunner): Promise<void> {
    // Reverse in the opposite order for cleanliness. Nothing here is
    // destructive to data — dropping an index or CHECK only removes
    // guardrails, and the FK swap goes back to the original (no
    // ON DELETE action = default NO ACTION, close enough to RESTRICT).
    for (const { table, fk } of [
      { table: 'booking_transport_item_suppliers', fk: 'fk_btis_supplier' },
      { table: 'booking_activity_item_suppliers', fk: 'fk_bais_supplier' },
      { table: 'booking_special_item_suppliers', fk: 'fk_bsis_supplier' },
    ]) {
      await qr.query(`ALTER TABLE "${table}" DROP CONSTRAINT IF EXISTS "${fk}"`);
      await qr.query(`
        ALTER TABLE "${table}"
          ADD CONSTRAINT "${fk}"
          FOREIGN KEY ("supplier_id")
          REFERENCES "suppliers"("id")
      `);
    }

    await qr.query(`ALTER TABLE "bookings" DROP CONSTRAINT IF EXISTS "chk_bookings_payment_status"`);
    await qr.query(`ALTER TABLE "bookings" DROP CONSTRAINT IF EXISTS "chk_bookings_status"`);
    for (const table of ['booking_transport_items', 'booking_activity_items']) {
      await qr.query(`ALTER TABLE "${table}" DROP CONSTRAINT IF EXISTS "chk_${table}_op_status"`);
    }

    await qr.query(`DROP INDEX IF EXISTS "ix_bai_tenant_op_status"`);
    await qr.query(`DROP INDEX IF EXISTS "ix_bti_tenant_op_status"`);
    await qr.query(`DROP INDEX IF EXISTS "ix_bookings_tenant_status"`);
    for (const table of [
      'booking_transport_items',
      'booking_activity_items',
      'booking_hotel_items',
      'booking_special_items',
    ]) {
      await qr.query(`DROP INDEX IF EXISTS "ix_${table}_tenant_booking"`);
    }
  }
}