import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Movement Dashboard — Payment Collection (v2, normalised)
 *
 * Reverses the inline-column design in `1785100000000-MDPaymentCollection.ts`
 * and replaces it with a two-table normalised model.
 *
 * ## Why the redesign
 * The v1 migration added 8 columns to each of `booking_transport_items`,
 * `booking_activity_items`, and `booking_special_items` — 24 columns
 * that would be NULL on ~95% of rows (most services aren't cash-on-
 * delivery). This bloats every wide item table and mixes two lifecycles
 * (the collection requirement vs the confirmation event) in one row.
 *
 * v2 normalises:
 *
 *   service_payment_collections
 *     One row per REQUIREMENT — the plan of "collect X in currency C
 *     from the guest at THIS service". Polymorphic FK to whichever of
 *     the three service item tables the requirement lives on
 *     (exactly one is non-null per row). Denormalised `status`
 *     ('PENDING' | 'COLLECTED' | 'CANCELLED') for fast MD reads.
 *
 *   service_payment_collection_confirmations
 *     Append-only event log — one row per actual money-in event.
 *     Amendments mark the prior row `voided_at` and insert a fresh
 *     one so history is native, not just in audit_log. Links to
 *     settlement_payments via `settlement_payment_id` (customer
 *     receivable auto-reduce on confirm).
 *
 * ## Ops on the migration
 * - Fully reversible. down() drops the two tables and re-runs v1's
 *   up() to restore the inline columns; v1's up() had no data so
 *   nothing is lost.
 * - Idempotent — CREATE / DROP guarded with IF NOT EXISTS / IF EXISTS,
 *   DO $$ block gates on ALTER TABLE ADD CONSTRAINT.
 * - Zero data migration required since v1 was schema-only.
 */
export class MDPaymentCollectionRedesign1785200000000 implements MigrationInterface {
  name = 'MDPaymentCollectionRedesign1785200000000';

  public async up(qr: QueryRunner): Promise<void> {
    // ── 1. Drop v1 inline columns from all three item tables ──────
    const tables = [
      { name: 'booking_transport_items', idxPrefix: 'bti' },
      { name: 'booking_activity_items', idxPrefix: 'bai' },
      { name: 'booking_special_items', idxPrefix: 'bsi' },
    ] as const;

    for (const t of tables) {
      await qr.query(`DROP INDEX IF EXISTS "ix_${t.idxPrefix}_tenant_payment_collection"`);
      await qr.query(
        `ALTER TABLE "${t.name}" DROP CONSTRAINT IF EXISTS "chk_${t.name}_payment_collection_status"`,
      );
      await qr.query(
        `ALTER TABLE "${t.name}" DROP CONSTRAINT IF EXISTS "fk_${t.name}_payment_collection_currency"`,
      );
      await qr.query(
        `ALTER TABLE "${t.name}" DROP CONSTRAINT IF EXISTS "fk_${t.name}_payment_collection_collected_by"`,
      );
      await qr.query(`
        ALTER TABLE "${t.name}"
          DROP COLUMN IF EXISTS "payment_collection_required",
          DROP COLUMN IF EXISTS "payment_collection_expected_amount",
          DROP COLUMN IF EXISTS "payment_collection_currency_id",
          DROP COLUMN IF EXISTS "payment_collection_status",
          DROP COLUMN IF EXISTS "payment_collection_actual_amount",
          DROP COLUMN IF EXISTS "payment_collection_collected_at",
          DROP COLUMN IF EXISTS "payment_collection_collected_by",
          DROP COLUMN IF EXISTS "payment_collection_difference_reason"
      `);
    }

    // ── 2. Create service_payment_collections (requirement) ───────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "service_payment_collections" (
        "id"                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        "tenant_id"          uuid NOT NULL,
        "created_by"         uuid NULL,
        "updated_by"         uuid NULL,
        "created_at"         timestamptz NOT NULL DEFAULT NOW(),
        "updated_at"         timestamptz NOT NULL DEFAULT NOW(),
        "is_deleted"         boolean NOT NULL DEFAULT false,
        "booking_id"         uuid NOT NULL,
        "service_type"       varchar(20) NOT NULL,
        "transport_item_id"  uuid NULL,
        "activity_item_id"   uuid NULL,
        "special_item_id"    uuid NULL,
        "expected_amount"    numeric(12,2) NOT NULL,
        "currency_id"        uuid NOT NULL,
        "status"             varchar(20) NOT NULL DEFAULT 'PENDING',
        "notes"              text NULL,
        CONSTRAINT "chk_spc_service_type"
          CHECK ("service_type" IN ('TRANSPORT','ACTIVITY','SPECIAL')),
        CONSTRAINT "chk_spc_status"
          CHECK ("status" IN ('PENDING','COLLECTED','CANCELLED')),
        CONSTRAINT "chk_spc_expected_non_neg" CHECK ("expected_amount" >= 0),
        CONSTRAINT "chk_spc_item_fk_matches_type" CHECK (
          (
            ("service_type" = 'TRANSPORT'
              AND "transport_item_id" IS NOT NULL
              AND "activity_item_id"  IS NULL
              AND "special_item_id"   IS NULL)
            OR
            ("service_type" = 'ACTIVITY'
              AND "activity_item_id"  IS NOT NULL
              AND "transport_item_id" IS NULL
              AND "special_item_id"   IS NULL)
            OR
            ("service_type" = 'SPECIAL'
              AND "special_item_id"   IS NOT NULL
              AND "transport_item_id" IS NULL
              AND "activity_item_id"  IS NULL)
          )
        ),
        CONSTRAINT "fk_spc_booking"
          FOREIGN KEY ("booking_id") REFERENCES "bookings"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_spc_transport_item"
          FOREIGN KEY ("transport_item_id") REFERENCES "booking_transport_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_spc_activity_item"
          FOREIGN KEY ("activity_item_id") REFERENCES "booking_activity_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_spc_special_item"
          FOREIGN KEY ("special_item_id") REFERENCES "booking_special_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_spc_currency"
          FOREIGN KEY ("currency_id") REFERENCES "currencies"("id")
      )
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_spc_tenant_booking"
      ON "service_payment_collections" ("tenant_id", "booking_id")
      WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_spc_tenant_status"
      ON "service_payment_collections" ("tenant_id", "status")
      WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_transport_item"
      ON "service_payment_collections" ("transport_item_id")
      WHERE "is_deleted" = false AND "transport_item_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_activity_item"
      ON "service_payment_collections" ("activity_item_id")
      WHERE "is_deleted" = false AND "activity_item_id" IS NOT NULL
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "ux_spc_special_item"
      ON "service_payment_collections" ("special_item_id")
      WHERE "is_deleted" = false AND "special_item_id" IS NOT NULL
    `);

    // ── 3. Create service_payment_collection_confirmations (event) ──
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "service_payment_collection_confirmations" (
        "id"                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        "tenant_id"              uuid NOT NULL,
        "created_by"             uuid NULL,
        "updated_by"             uuid NULL,
        "created_at"             timestamptz NOT NULL DEFAULT NOW(),
        "updated_at"             timestamptz NOT NULL DEFAULT NOW(),
        "is_deleted"             boolean NOT NULL DEFAULT false,
        "collection_id"          uuid NOT NULL,
        "actual_amount"          numeric(12,2) NOT NULL,
        "collected_at"           timestamptz NOT NULL,
        "collected_by"           uuid NOT NULL,
        "difference_reason"      varchar(500) NULL,
        "settlement_payment_id"  uuid NULL,
        "voided_at"              timestamptz NULL,
        "voided_by"              uuid NULL,
        "notes"                  text NULL,
        CONSTRAINT "chk_spcc_actual_non_neg" CHECK ("actual_amount" >= 0),
        CONSTRAINT "fk_spcc_collection"
          FOREIGN KEY ("collection_id")
          REFERENCES "service_payment_collections"("id")
          ON DELETE CASCADE,
        CONSTRAINT "fk_spcc_collected_by"
          FOREIGN KEY ("collected_by") REFERENCES "users"("id"),
        CONSTRAINT "fk_spcc_voided_by"
          FOREIGN KEY ("voided_by") REFERENCES "users"("id")
      )
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_spcc_collection"
      ON "service_payment_collection_confirmations" ("collection_id")
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_spcc_collection_live"
      ON "service_payment_collection_confirmations" ("collection_id")
      WHERE "voided_at" IS NULL AND "is_deleted" = false
    `);

    // Optional link: FK on settlement_payment_id if the table exists.
    // Guarded because in unusual bootstrap orders this can run before
    // the settlement_payments table lands; skipping the FK is safe.
    await qr.query(`
      DO $$ BEGIN
        IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'settlement_payments')
           AND NOT EXISTS (
             SELECT 1 FROM pg_constraint
             WHERE conname = 'fk_spcc_settlement_payment'
           )
        THEN
          ALTER TABLE "service_payment_collection_confirmations"
            ADD CONSTRAINT "fk_spcc_settlement_payment"
            FOREIGN KEY ("settlement_payment_id")
            REFERENCES "settlement_payments"("id");
        END IF;
      END $$;
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    // Drop the new tables (children first via CASCADE).
    await qr.query(`DROP TABLE IF EXISTS "service_payment_collection_confirmations"`);
    await qr.query(`DROP TABLE IF EXISTS "service_payment_collections"`);

    // Restore v1 inline columns so a rollback lands on the same shape
    // v1's up() produced.
    const tables = [
      { name: 'booking_transport_items', idxPrefix: 'bti' },
      { name: 'booking_activity_items', idxPrefix: 'bai' },
      { name: 'booking_special_items', idxPrefix: 'bsi' },
    ] as const;

    for (const t of tables) {
      await qr.query(`
        ALTER TABLE "${t.name}"
          ADD COLUMN IF NOT EXISTS "payment_collection_required" boolean NOT NULL DEFAULT false,
          ADD COLUMN IF NOT EXISTS "payment_collection_expected_amount" numeric(12,2) NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_currency_id" uuid NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_status" varchar(20) NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_actual_amount" numeric(12,2) NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_collected_at" timestamptz NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_collected_by" uuid NULL,
          ADD COLUMN IF NOT EXISTS "payment_collection_difference_reason" varchar(500) NULL
      `);
      await qr.query(`
        DO $$ BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM pg_constraint
            WHERE conname = 'chk_${t.name}_payment_collection_status'
          ) THEN
            ALTER TABLE "${t.name}"
              ADD CONSTRAINT "chk_${t.name}_payment_collection_status"
              CHECK ("payment_collection_status" IS NULL OR "payment_collection_status" IN ('PENDING','COLLECTED'));
          END IF;
        END $$;
      `);
      await qr.query(`
        CREATE INDEX IF NOT EXISTS "ix_${t.idxPrefix}_tenant_payment_collection"
        ON "${t.name}" ("tenant_id", "payment_collection_status")
        WHERE "is_deleted" = false AND "payment_collection_required" = true
      `);
    }
  }
}