import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Hotel per-night configuration — normalize the "one flat row per
 * stay" model into stay + periods + extras.
 *
 * Prior shape (flat, on booking_hotel_items / query_quote_hotels):
 *   room_type, meal_plan, pax_per_room, num_rooms,
 *   aweb, cweb, cnb, nightly_rates (simple-json blob)
 *
 * New shape:
 *   Parent stay row  → check_in/out, nights, subtotal, notes, tax_*, supplier_*
 *   {table}_periods  → nights[] · room_type · meal_plan · num_rooms · pax_per_room · rate_per_night
 *   {table}_extras   → kind · description · qty · rate · nights[] · scale_by_rooms
 *
 * Nights on children are Postgres `integer[]` arrays of 0-based night
 * indexes (0 .. stay.nights - 1). Overlap between period rows is
 * allowed — mixed room types can share the same nights.
 *
 * Backfill strategy:
 *   1. For every existing hotel_item row, create ONE period covering
 *      night indexes 0..(nights-1) with the old flat values. Rate =
 *      subtotal / nights / num_rooms so the stay total is preserved.
 *   2. Extras rows created only when the legacy `aweb` / `cweb` /
 *      `cnb` count > 0 — one row per non-zero counter, rate 0 (ops
 *      backfills the correct value later). Rates were never stored
 *      per bed on the flat model.
 *   3. Legacy columns dropped from the parent tables.
 *
 * Ships during develop stage — the pre-existing DBs on dev are
 * disposable, so this migration is destructive in the sense that
 * the legacy columns are gone at the end. down() re-adds them empty.
 */
export class HotelPerNightConfig1785000000000 implements MigrationInterface {
  name = 'HotelPerNightConfig1785000000000';

  public async up(qr: QueryRunner): Promise<void> {
    // ── 1. Create child tables ─────────────────────────────────────
    // Same shape on both sides — one migration block per pair.
    for (const parent of ['booking_hotel_items', 'query_quote_hotels']) {
      const prefix = parent === 'booking_hotel_items' ? 'booking' : 'query_quote';
      const periodTable = `${prefix}_hotel_periods`;
      const extraTable = `${prefix}_hotel_extras`;

      // Periods
      await qr.query(`
        CREATE TABLE IF NOT EXISTS "${periodTable}" (
          "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,
          "hotel_item_id"   uuid NOT NULL,
          "sort_order"      integer NOT NULL DEFAULT 0,
          "nights"          integer[] NOT NULL DEFAULT '{}',
          "room_type"       varchar(100) NOT NULL DEFAULT '',
          "meal_plan"       varchar(100) NOT NULL DEFAULT '',
          "num_rooms"       integer NOT NULL DEFAULT 1,
          "pax_per_room"    integer NOT NULL DEFAULT 2,
          "rate_per_night"  numeric(12,2) NOT NULL DEFAULT 0,
          CONSTRAINT "ck_${periodTable}_num_rooms" CHECK ("num_rooms" >= 1),
          CONSTRAINT "ck_${periodTable}_pax_per_room" CHECK ("pax_per_room" >= 1),
          CONSTRAINT "ck_${periodTable}_rate_non_neg" CHECK ("rate_per_night" >= 0),
          CONSTRAINT "fk_${periodTable}_parent"
            FOREIGN KEY ("hotel_item_id") REFERENCES "${parent}"("id") ON DELETE CASCADE
        )
      `);
      await qr.query(`
        CREATE INDEX IF NOT EXISTS "ix_${periodTable}_tenant_item"
        ON "${periodTable}" ("tenant_id", "hotel_item_id")
        WHERE "is_deleted" = false
      `);

      // Extras
      await qr.query(`
        CREATE TABLE IF NOT EXISTS "${extraTable}" (
          "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,
          "hotel_item_id"   uuid NOT NULL,
          "sort_order"      integer NOT NULL DEFAULT 0,
          "kind"            varchar(20) NOT NULL,
          "description"     varchar(200) NULL,
          "quantity"        integer NOT NULL DEFAULT 1,
          "rate"            numeric(12,2) NOT NULL DEFAULT 0,
          "nights"          integer[] NOT NULL DEFAULT '{}',
          "scale_by_rooms"  boolean NOT NULL DEFAULT false,
          CONSTRAINT "ck_${extraTable}_kind"
            CHECK ("kind" IN ('AWEB','CWEB','CNB','MEAL_UPGRADE','ROOM_UPGRADE','CUSTOM')),
          CONSTRAINT "ck_${extraTable}_qty" CHECK ("quantity" >= 0),
          CONSTRAINT "ck_${extraTable}_rate_non_neg" CHECK ("rate" >= 0),
          CONSTRAINT "fk_${extraTable}_parent"
            FOREIGN KEY ("hotel_item_id") REFERENCES "${parent}"("id") ON DELETE CASCADE
        )
      `);
      await qr.query(`
        CREATE INDEX IF NOT EXISTS "ix_${extraTable}_tenant_item"
        ON "${extraTable}" ("tenant_id", "hotel_item_id")
        WHERE "is_deleted" = false
      `);
    }

    // ── 2. Backfill periods from existing flat rows ────────────────
    // Only runs against tables that still have the legacy columns
    // (guarded via information_schema so re-run is safe post-drop).
    for (const parent of ['booking_hotel_items', 'query_quote_hotels']) {
      const prefix = parent === 'booking_hotel_items' ? 'booking' : 'query_quote';
      const periodTable = `${prefix}_hotel_periods`;
      const extraTable = `${prefix}_hotel_extras`;

      const hasLegacy = await qr.query(
        `SELECT 1 FROM information_schema.columns
         WHERE table_name = $1 AND column_name = 'room_type'`,
        [parent],
      );
      if (hasLegacy.length === 0) continue;

      await qr.query(`
        INSERT INTO "${periodTable}" (
          "tenant_id", "hotel_item_id", "sort_order", "nights",
          "room_type", "meal_plan", "num_rooms", "pax_per_room",
          "rate_per_night", "created_by", "updated_by"
        )
        SELECT
          h."tenant_id",
          h."id",
          0,
          -- 0..nights-1 as an integer[] array; empty when nights <= 0
          CASE WHEN h."nights" > 0
               THEN ARRAY(SELECT generate_series(0, h."nights" - 1))
               ELSE '{}'::integer[]
          END,
          COALESCE(h."room_type", ''),
          COALESCE(h."meal_plan", ''),
          GREATEST(COALESCE(h."num_rooms", 1), 1),
          GREATEST(COALESCE(h."pax_per_room", 2), 1),
          CASE
            WHEN COALESCE(h."nights", 0) > 0 AND COALESCE(h."num_rooms", 1) > 0
              THEN ROUND(COALESCE(h."subtotal", 0) / h."nights" / GREATEST(h."num_rooms", 1), 2)
            ELSE 0
          END,
          h."created_by",
          h."updated_by"
        FROM "${parent}" h
        WHERE h."is_deleted" = false
      `);

      // Backfill extras only for legacy non-zero AWEB / CWEB / CNB.
      //
      // sort_order MUST be distinct per (hotel_item_id) — the entity
      // carries a partial unique index on (hotel_item_id, sort_order)
      // WHERE is_deleted = false, so all three legacy counters can't
      // share sort_order = 0. Give each kind a fixed slot; ops can
      // reorder later.
      for (const [column, kind, sortOrder] of [
        ['aweb', 'AWEB', 0],
        ['cweb', 'CWEB', 1],
        ['cnb', 'CNB', 2],
      ] as const) {
        await qr.query(`
          INSERT INTO "${extraTable}" (
            "tenant_id", "hotel_item_id", "sort_order", "kind",
            "quantity", "rate", "nights", "scale_by_rooms",
            "created_by", "updated_by"
          )
          SELECT
            h."tenant_id",
            h."id",
            $2,
            $1,
            h."${column}",
            0,
            CASE WHEN h."nights" > 0
                 THEN ARRAY(SELECT generate_series(0, h."nights" - 1))
                 ELSE '{}'::integer[]
            END,
            false,
            h."created_by",
            h."updated_by"
          FROM "${parent}" h
          WHERE h."is_deleted" = false AND COALESCE(h."${column}", 0) > 0
        `, [kind, sortOrder]);
      }
    }

    // ── 3. Drop legacy columns ─────────────────────────────────────
    for (const parent of ['booking_hotel_items', 'query_quote_hotels']) {
      for (const col of [
        'room_type',
        'meal_plan',
        'pax_per_room',
        'num_rooms',
        'aweb',
        'cweb',
        'cnb',
        'nightly_rates',
      ]) {
        await qr.query(`ALTER TABLE "${parent}" DROP COLUMN IF EXISTS "${col}"`);
      }
    }

    // ── 4. Prod-hardening additions ────────────────────────────────
    // (Mirrors the entity @Index / @Check decorators — kept in sync
    // so `synchronize: true` in dev matches this migration in prod.)
    for (const parent of ['booking_hotel_items', 'query_quote_hotels']) {
      const prefix = parent === 'booking_hotel_items' ? 'booking' : 'query_quote';
      const periodTable = `${prefix}_hotel_periods`;
      const extraTable = `${prefix}_hotel_extras`;

      // Unique (hotel_item_id, sort_order) per stay for live rows only —
      // prevents two periods sharing sort order 0 and flickering render.
      await qr.query(`
        CREATE UNIQUE INDEX IF NOT EXISTS "ux_${periodTable}_item_sort"
        ON "${periodTable}" ("hotel_item_id", "sort_order")
        WHERE "is_deleted" = false
      `);
      await qr.query(`
        CREATE UNIQUE INDEX IF NOT EXISTS "ux_${extraTable}_item_sort"
        ON "${extraTable}" ("hotel_item_id", "sort_order")
        WHERE "is_deleted" = false
      `);

      // Absolute-max night-index bound. App layer enforces the tighter
      // "within parent stay's window" via assertNightsInRange.
      for (const [table, name] of [
        [periodTable, `ck_${periodTable}_nights_range`],
        [extraTable, `ck_${extraTable}_nights_range`],
      ] as const) {
        await qr.query(`
          DO $$ BEGIN
            IF NOT EXISTS (
              SELECT 1 FROM pg_constraint WHERE conname = '${name}'
            ) THEN
              ALTER TABLE "${table}"
                ADD CONSTRAINT "${name}"
                CHECK ("nights" = '{}'::integer[] OR (0 <= ALL("nights") AND 99 >= ALL("nights")));
            END IF;
          END $$;
        `);
      }
    }

    // ── 5. Tenant-consistency trigger ─────────────────────────────
    // Defense in depth against app-layer bugs or raw SQL writes: no
    // child row may point at a parent from a different tenant. Uses
    // one shared plpgsql function parametrised via TG_ARGV[0].
    await qr.query(`
      CREATE OR REPLACE FUNCTION check_hotel_child_tenant() RETURNS TRIGGER AS $$
      DECLARE
        parent_table text := TG_ARGV[0];
        parent_tenant uuid;
      BEGIN
        EXECUTE format('SELECT tenant_id FROM %I WHERE id = $1', parent_table)
          INTO parent_tenant USING NEW.hotel_item_id;
        IF parent_tenant IS NULL THEN
          RAISE EXCEPTION 'hotel child row references missing parent id % on %',
            NEW.hotel_item_id, TG_TABLE_NAME;
        END IF;
        IF NEW.tenant_id IS DISTINCT FROM parent_tenant THEN
          RAISE EXCEPTION 'tenant_id mismatch on %: child %, parent %',
            TG_TABLE_NAME, NEW.tenant_id, parent_tenant;
        END IF;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    for (const [child, parent] of [
      ['booking_hotel_periods', 'booking_hotel_items'],
      ['booking_hotel_extras', 'booking_hotel_items'],
      ['query_quote_hotel_periods', 'query_quote_hotels'],
      ['query_quote_hotel_extras', 'query_quote_hotels'],
    ] as const) {
      const trg = `trg_${child}_tenant_check`;
      await qr.query(`DROP TRIGGER IF EXISTS "${trg}" ON "${child}"`);
      await qr.query(`
        CREATE TRIGGER "${trg}"
        BEFORE INSERT OR UPDATE OF hotel_item_id, tenant_id ON "${child}"
        FOR EACH ROW EXECUTE FUNCTION check_hotel_child_tenant('${parent}')
      `);
    }
  }

  public async down(qr: QueryRunner): Promise<void> {
    // Re-add legacy columns (empty) so the parent tables can be read
    // by pre-migration code paths. Data is NOT reconstructed — this
    // migration is destructive by design and the down() is a schema
    // rollback only. All child data is dropped with the tables.
    for (const parent of ['booking_hotel_items', 'query_quote_hotels']) {
      await qr.query(`
        ALTER TABLE "${parent}"
          ADD COLUMN IF NOT EXISTS "room_type" varchar(100) NULL,
          ADD COLUMN IF NOT EXISTS "meal_plan" varchar(100) NULL,
          ADD COLUMN IF NOT EXISTS "pax_per_room" integer NOT NULL DEFAULT 1,
          ADD COLUMN IF NOT EXISTS "num_rooms" integer NOT NULL DEFAULT 1,
          ADD COLUMN IF NOT EXISTS "aweb" integer NOT NULL DEFAULT 0,
          ADD COLUMN IF NOT EXISTS "cweb" integer NOT NULL DEFAULT 0,
          ADD COLUMN IF NOT EXISTS "cnb" integer NOT NULL DEFAULT 0,
          ADD COLUMN IF NOT EXISTS "nightly_rates" text NULL
      `);
    }

    // Drop triggers + shared function first — child tables held them.
    for (const child of [
      'booking_hotel_periods',
      'booking_hotel_extras',
      'query_quote_hotel_periods',
      'query_quote_hotel_extras',
    ]) {
      await qr.query(`DROP TRIGGER IF EXISTS "trg_${child}_tenant_check" ON "${child}"`);
    }
    await qr.query(`DROP FUNCTION IF EXISTS check_hotel_child_tenant()`);

    for (const prefix of ['booking', 'query_quote']) {
      await qr.query(`DROP TABLE IF EXISTS "${prefix}_hotel_extras"`);
      await qr.query(`DROP TABLE IF EXISTS "${prefix}_hotel_periods"`);
    }
  }
}