import { type MigrationInterface, type QueryRunner } from 'typeorm';

/**
 * Settlement Master Reshape — one master per booking (Layer 2 in the
 * fact-table → operational-rollup → analytical-views architecture).
 *
 * Replaces the per-party master from migration 1780200000000 with a
 * single rollup row per booking. The per-party split (CUSTOMER /
 * SUPPLIER) becomes columns INSIDE the master rather than separate
 * rows, because finance always wants to see both sides together when
 * looking at a booking (margin = customer total − supplier total).
 *
 * Supplier dimension lives on children (payment_settlements.supplier_id)
 * — supplier-side analytics are GROUP BY queries over that, not a
 * separate master per supplier.
 *
 * New columns on the master:
 *   customer_expected / customer_received / customer_refund_due
 *   customer_status       PENDING / PARTIAL / PAID / REFUND_DUE
 *   supplier_expected / supplier_paid
 *   supplier_status       PENDING / PARTIAL / PAID
 *   planned_margin        customer_expected − supplier_expected
 *   realised_margin       customer_received − supplier_paid
 *   overall_status        PENDING / IN_PROGRESS / COMPLETED / HOLD / REFUND_DUE
 *   hold_*                admin-only HOLD action (mandatory reason)
 *
 * Also adds `sales_agent_id` to payment_settlements — snapshot
 * dimension so commission-base queries can group by agent without
 * joining back to the booking → query.assigned_to (which can change
 * post-settlement). Backfilled here for existing rows.
 *
 * Backfill strategy:
 *   1. Drop old master rows (none in production; safe in dev)
 *   2. Insert one master per distinct booking that has active settlements
 *   3. Re-link `payment_settlements.master_id`
 *   4. Snapshot `sales_agent_id` from `queries.assigned_to`
 *   5. Recompute master rollups via a single aggregate UPDATE
 */
export class SettlementMasterReshape1780500000000 implements MigrationInterface {
  async up(qr: QueryRunner): Promise<void> {
    // ── 1. Clear old master state (table from 1780200000000) ──────────
    // Drop indexes first so the table drop is clean.
    await qr.query(`DROP INDEX IF EXISTS uq_booking_settlement_master_active;`);
    await qr.query(`DROP INDEX IF EXISTS idx_master_tenant_party_status;`);
    await qr.query(`DROP INDEX IF EXISTS idx_master_tenant_booking;`);
    await qr.query(`DROP INDEX IF EXISTS idx_settlement_master;`);
    // Drop the FK + column on payment_settlements first, then the master table.
    // Re-added at the end with the same name so existing entity FK metadata stays valid.
    await qr.query(`ALTER TABLE payment_settlements DROP COLUMN IF EXISTS master_id;`);
    await qr.query(`DROP TABLE IF EXISTS booking_settlement_masters;`);

    // ── 2. New master table ───────────────────────────────────────────
    await qr.query(`
      CREATE TABLE booking_settlement_masters (
        id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        tenant_id             UUID NOT NULL,
        booking_id            UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
        currency_id           UUID NOT NULL REFERENCES currencies(id),

        -- Customer-side rollup (Σ over party=CUSTOMER children)
        customer_expected     NUMERIC(12,2) NOT NULL DEFAULT 0,
        customer_received     NUMERIC(12,2) NOT NULL DEFAULT 0,
        customer_refund_due   NUMERIC(12,2) NOT NULL DEFAULT 0,
        customer_status       VARCHAR(20) NOT NULL DEFAULT 'PENDING',

        -- Supplier-side rollup (Σ over party=SUPPLIER children, across all suppliers)
        supplier_expected     NUMERIC(12,2) NOT NULL DEFAULT 0,
        supplier_paid         NUMERIC(12,2) NOT NULL DEFAULT 0,
        supplier_status       VARCHAR(20) NOT NULL DEFAULT 'PENDING',

        -- Margin
        planned_margin        NUMERIC(12,2) NOT NULL DEFAULT 0,
        realised_margin       NUMERIC(12,2) NOT NULL DEFAULT 0,

        -- Overall (drives the list-page status pill + HOLD)
        overall_status        VARCHAR(20) NOT NULL DEFAULT 'PENDING',
        hold_reason           TEXT,
        hold_set_by           UUID,
        hold_set_at           TIMESTAMPTZ,
        last_activity_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

        -- Audit
        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 chk_master_customer_status
          CHECK (customer_status IN ('PENDING','PARTIAL','PAID','REFUND_DUE')),
        CONSTRAINT chk_master_supplier_status
          CHECK (supplier_status IN ('PENDING','PARTIAL','PAID')),
        CONSTRAINT chk_master_overall_status
          CHECK (overall_status IN ('PENDING','IN_PROGRESS','COMPLETED','HOLD','REFUND_DUE')),
        CONSTRAINT chk_master_hold_reason_when_hold
          CHECK (overall_status <> 'HOLD' OR hold_reason IS NOT NULL)
      );
    `);

    // ── 3. Indexes ────────────────────────────────────────────────────
    // Unique: one master per booking. Partial-unique excludes soft-deleted.
    await qr.query(`
      CREATE UNIQUE INDEX uq_booking_settlement_master_per_booking
        ON booking_settlement_masters (tenant_id, booking_id)
        WHERE is_deleted = false;
    `);
    // List-page hot path: filter by overall_status, sort by recency.
    await qr.query(`
      CREATE INDEX idx_master_tenant_status_activity
        ON booking_settlement_masters (tenant_id, overall_status, last_activity_at DESC)
        WHERE is_deleted = false;
    `);

    // ── 4. Re-add master_id FK on children ────────────────────────────
    await qr.query(`
      ALTER TABLE payment_settlements
        ADD COLUMN master_id UUID REFERENCES booking_settlement_masters(id) ON DELETE CASCADE;
    `);
    await qr.query(`
      CREATE INDEX idx_settlement_master_id
        ON payment_settlements (master_id)
        WHERE is_deleted = false;
    `);

    // ── 5. sales_agent_id snapshot column on settlements ──────────────
    // Lets the by-agent analytical view group by agent without joining
    // back to query.assigned_to (which can change later). Snapshot at
    // create time = stable commission attribution.
    await qr.query(`
      ALTER TABLE payment_settlements ADD COLUMN sales_agent_id UUID;
    `);
    await qr.query(`
      CREATE INDEX idx_settlement_sales_agent
        ON payment_settlements (tenant_id, sales_agent_id)
        WHERE is_deleted = false AND sales_agent_id IS NOT NULL;
    `);

    // ── 6. Backfill ───────────────────────────────────────────────────
    // 6a. Snapshot sales_agent_id on existing settlements from the
    //     booking's query.sales_team[0] (primary agent — the first user
    //     in the JSONB array). Commission attribution always belongs to
    //     a single agent at the settlement level; multi-agent splits
    //     are a future concern handled by a separate commission table.
    await qr.query(`
      UPDATE payment_settlements ps
         SET sales_agent_id = (q.sales_team ->> 0)::uuid
        FROM bookings b
        JOIN queries q ON q.id = b.query_id
       WHERE ps.booking_id = b.id
         AND ps.is_deleted = false
         AND ps.sales_agent_id IS NULL
         AND jsonb_array_length(COALESCE(q.sales_team, '[]'::jsonb)) > 0;
    `);

    // 6b. Create one master per (tenant, booking) that has settlements.
    //     Currency comes from the booking's selling_currency_id; if
    //     somehow missing, falls back to the first child's currency.
    await qr.query(`
      INSERT INTO booking_settlement_masters
        (tenant_id, booking_id, currency_id, last_activity_at, created_by, updated_by)
      SELECT
        s.tenant_id,
        s.booking_id,
        COALESCE(b.selling_currency_id, s.currency_id) AS currency_id,
        NOW(),
        NULL,
        NULL
      FROM (
        SELECT DISTINCT ON (tenant_id, booking_id)
          tenant_id, booking_id, currency_id, created_at
          FROM payment_settlements
         WHERE is_deleted = false
         ORDER BY tenant_id, booking_id, created_at ASC
      ) s
      JOIN bookings b ON b.id = s.booking_id;
    `);

    // 6c. Link each settlement to its master.
    await qr.query(`
      UPDATE payment_settlements ps
         SET master_id = m.id
        FROM booking_settlement_masters m
       WHERE ps.tenant_id = m.tenant_id
         AND ps.booking_id = m.booking_id
         AND ps.is_deleted = false
         AND m.is_deleted = false;
    `);

    // 6d. Recompute master rollups from children in a single statement.
    //     Mirrors the runtime recomputeMaster() logic so the post-
    //     migration state is identical to what fresh saves would produce.
    await qr.query(`
      UPDATE booking_settlement_masters m
         SET customer_expected   = COALESCE(t.cust_exp, 0),
             customer_received   = COALESCE(t.cust_rcv, 0),
             customer_refund_due = COALESCE(t.cust_refund, 0),
             customer_status     = CASE
               WHEN COALESCE(t.cust_refund, 0) > 0.005 THEN 'REFUND_DUE'
               WHEN COALESCE(t.cust_exp, 0) <= 0.005 THEN 'PENDING'
               WHEN COALESCE(t.cust_rcv, 0) + 0.005 >= COALESCE(t.cust_exp, 0) THEN 'PAID'
               WHEN COALESCE(t.cust_rcv, 0) > 0.005 THEN 'PARTIAL'
               ELSE 'PENDING'
             END,
             supplier_expected   = COALESCE(t.sup_exp, 0),
             supplier_paid       = COALESCE(t.sup_paid, 0),
             supplier_status     = CASE
               WHEN COALESCE(t.sup_exp, 0) <= 0.005 THEN 'PENDING'
               WHEN COALESCE(t.sup_paid, 0) + 0.005 >= COALESCE(t.sup_exp, 0) THEN 'PAID'
               WHEN COALESCE(t.sup_paid, 0) > 0.005 THEN 'PARTIAL'
               ELSE 'PENDING'
             END,
             planned_margin      = COALESCE(t.cust_exp, 0) - COALESCE(t.sup_exp, 0),
             realised_margin     = COALESCE(t.cust_rcv, 0) - COALESCE(t.sup_paid, 0),
             overall_status      = CASE
               WHEN COALESCE(t.cust_refund, 0) > 0.005 THEN 'REFUND_DUE'
               WHEN COALESCE(t.cust_rcv, 0) + 0.005 >= COALESCE(t.cust_exp, 0)
                AND COALESCE(t.sup_paid, 0) + 0.005 >= COALESCE(t.sup_exp, 0)
                AND COALESCE(t.cust_exp, 0) > 0.005
                THEN 'COMPLETED'
               WHEN COALESCE(t.cust_rcv, 0) > 0.005
                 OR COALESCE(t.sup_paid, 0) > 0.005
                THEN 'IN_PROGRESS'
               ELSE 'PENDING'
             END,
             last_activity_at    = NOW()
        FROM (
          SELECT
            booking_id,
            SUM(CASE WHEN party_type = 'CUSTOMER' AND type = 'COLLECTION' THEN amount ELSE 0 END)          AS cust_exp,
            SUM(CASE WHEN party_type = 'CUSTOMER' AND type = 'COLLECTION' THEN amount_settled ELSE 0 END)  AS cust_rcv,
            SUM(CASE WHEN party_type = 'CUSTOMER' AND type = 'REFUND'
                     THEN amount - amount_settled ELSE 0 END)                                              AS cust_refund,
            SUM(CASE WHEN party_type = 'SUPPLIER' THEN amount ELSE 0 END)                                  AS sup_exp,
            SUM(CASE WHEN party_type = 'SUPPLIER' THEN amount_settled ELSE 0 END)                          AS sup_paid
            FROM payment_settlements
           WHERE is_deleted = false
           GROUP BY booking_id
        ) t
       WHERE m.booking_id = t.booking_id;
    `);
  }

  async down(qr: QueryRunner): Promise<void> {
    // Reverting drops the new master + sales_agent_id column. The
    // previous shape (1780200000000) is NOT restored — re-run that
    // migration manually if needed.
    await qr.query(`DROP INDEX IF EXISTS idx_settlement_sales_agent;`);
    await qr.query(`ALTER TABLE payment_settlements DROP COLUMN IF EXISTS sales_agent_id;`);
    await qr.query(`DROP INDEX IF EXISTS idx_settlement_master_id;`);
    await qr.query(`ALTER TABLE payment_settlements DROP COLUMN IF EXISTS master_id;`);
    await qr.query(`DROP INDEX IF EXISTS idx_master_tenant_status_activity;`);
    await qr.query(`DROP INDEX IF EXISTS uq_booking_settlement_master_per_booking;`);
    await qr.query(`DROP TABLE IF EXISTS booking_settlement_masters;`);
  }
}