import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Movement Dashboard cash collection — one live row per itinerary route
 * (transport / activity / special item).
 *
 * Final schema in one CREATE (Own vs Supplier collector, lifecycle
 * amounts/status, collection_date, route FKs). Intermediate unique
 * keys (per booking, then per booking+date) are not created.
 */
export class BookingCashCollection1785800000000 implements MigrationInterface {
  name = 'BookingCashCollection1785800000000';

  public async up(qr: QueryRunner): Promise<void> {
    await qr.query(`
      CREATE TABLE "booking_cash_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,
        "collection_date"     date NOT NULL,
        "transport_item_id"   uuid NULL,
        "activity_item_id"    uuid NULL,
        "special_item_id"     uuid NULL,
        "currency_id"         uuid NOT NULL,
        "collected_by_type"   varchar(20) NOT NULL,
        "collector_name"      varchar(255) NULL,
        "supplier_id"         uuid NULL,
        "source_kind"         varchar(20) NULL,
        "activity_id"         uuid NULL,
        "driver_id"           uuid NULL,
        "expected_amount"     numeric(12,2) NOT NULL DEFAULT 0,
        "notes"               text NULL,
        "status"              varchar(20) NOT NULL DEFAULT 'PENDING',
        "actual_amount"       numeric(12,2) NULL,
        "collected_at"        timestamptz NULL,
        "difference_reason"   varchar(500) NULL,
        "collected_by"        uuid NULL,
        "settlement_payment_id" uuid NULL,
        CONSTRAINT "fk_bcc_booking"
          FOREIGN KEY ("booking_id") REFERENCES "bookings"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_bcc_transport_item"
          FOREIGN KEY ("transport_item_id") REFERENCES "booking_transport_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_bcc_activity_item"
          FOREIGN KEY ("activity_item_id") REFERENCES "booking_activity_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_bcc_special_item"
          FOREIGN KEY ("special_item_id") REFERENCES "booking_special_items"("id") ON DELETE CASCADE,
        CONSTRAINT "fk_bcc_currency"
          FOREIGN KEY ("currency_id") REFERENCES "currencies"("id"),
        CONSTRAINT "fk_bcc_supplier"
          FOREIGN KEY ("supplier_id") REFERENCES "suppliers"("id"),
        CONSTRAINT "fk_bcc_activity"
          FOREIGN KEY ("activity_id") REFERENCES "activities"("id"),
        CONSTRAINT "fk_bcc_driver"
          FOREIGN KEY ("driver_id") REFERENCES "drivers"("id"),
        CONSTRAINT "fk_bcc_collected_by"
          FOREIGN KEY ("collected_by") REFERENCES "users"("id") ON DELETE SET NULL,
        CONSTRAINT "chk_bcc_collected_by_type"
          CHECK ("collected_by_type" IN ('OWN','SUPPLIER')),
        CONSTRAINT "chk_bcc_status"
          CHECK ("status" IN ('PENDING','COLLECTED','CANCELLED')),
        CONSTRAINT "chk_bcc_expected_non_neg"
          CHECK ("expected_amount" >= 0),
        CONSTRAINT "chk_bcc_actual_non_neg"
          CHECK ("actual_amount" IS NULL OR "actual_amount" >= 0),
        CONSTRAINT "chk_bcc_source_kind"
          CHECK ("source_kind" IS NULL OR "source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL')),
        CONSTRAINT "chk_bcc_one_line_item"
          CHECK (
            (CASE WHEN "transport_item_id" IS NULL THEN 0 ELSE 1 END)
            + (CASE WHEN "activity_item_id" IS NULL THEN 0 ELSE 1 END)
            + (CASE WHEN "special_item_id" IS NULL THEN 0 ELSE 1 END)
            <= 1
          ),
        CONSTRAINT "chk_bcc_party_matches_type"
          CHECK (
            (
              "collected_by_type" = 'OWN'
              AND "collector_name" IS NOT NULL
              AND "supplier_id" IS NULL
              AND "driver_id" IS NULL
              AND "activity_id" IS NULL
              AND "source_kind" IS NULL
            )
            OR
            (
              "collected_by_type" = 'SUPPLIER'
              AND "collector_name" IS NULL
              AND "supplier_id" IS NOT NULL
              AND "driver_id" IS NOT NULL
              AND "source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL')
            )
          )
      )
    `);

    await qr.query(`
      CREATE INDEX "ix_bcc_tenant_booking"
      ON "booking_cash_collections" ("tenant_id", "booking_id")
      WHERE "is_deleted" = false
    `);

    await qr.query(`
      CREATE UNIQUE INDEX "ux_bcc_live_transport_item"
      ON "booking_cash_collections" ("tenant_id", "transport_item_id")
      WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "transport_item_id" IS NOT NULL
    `);

    await qr.query(`
      CREATE UNIQUE INDEX "ux_bcc_live_activity_item"
      ON "booking_cash_collections" ("tenant_id", "activity_item_id")
      WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "activity_item_id" IS NOT NULL
    `);

    await qr.query(`
      CREATE UNIQUE INDEX "ux_bcc_live_special_item"
      ON "booking_cash_collections" ("tenant_id", "special_item_id")
      WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "special_item_id" IS NOT NULL
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_special_item"`);
    await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_activity_item"`);
    await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_transport_item"`);
    await qr.query(`DROP INDEX IF EXISTS "ix_bcc_tenant_booking"`);
    await qr.query(`DROP TABLE IF EXISTS "booking_cash_collections"`);
  }
}