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

/**
 * Payment Settlement (§12 of dmc_scope.md) — Phase A schema.
 *
 *   1. payment_settlements      — one row per money event (initial
 *                                 collection / itinerary delta / refund).
 *   2. settlement_payments      — append-only payment ledger
 *                                 (multiple partials per settlement).
 *   3. bookings.payment_status  — cached rollup pill.
 *   4. booking_transport_items.is_amended           — flags post-booking
 *                                                     additions billed
 *                                                     to the customer.
 *   5. booking_transport_items.actual_supplier_cost — Ops Manager's
 *                                                     per-trip cost
 *                                                     override (internal
 *                                                     margin only).
 *   6. role_permissions          — seed PAYMENT_SETTLEMENT module rows
 *                                  for Admin (full) and Operations
 *                                  Manager (edit). Sales Head reads via
 *                                  BOOKING:view, not a settle perm.
 *                                  Idempotent on re-run.
 */
export class CreatePaymentSettlement1779900000000 implements MigrationInterface {
  async up(qr: QueryRunner): Promise<void> {
    // ── 1. payment_settlements ────────────────────────────────────────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS payment_settlements (
        id                              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        tenant_id                       UUID NOT NULL,
        booking_id                      UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
        type                            VARCHAR(20) NOT NULL,
        amount                          NUMERIC(12,2) NOT NULL,
        amount_settled                  NUMERIC(12,2) NOT NULL DEFAULT 0,
        currency_id                     UUID NOT NULL REFERENCES currencies(id),
        status                          VARCHAR(20) NOT NULL DEFAULT 'PENDING',
        due_date                        DATE NOT NULL,
        settled_date                    DATE,
        notes                           TEXT,
        change_breakdown                JSONB,
        triggered_by_change_request_id  UUID REFERENCES booking_change_requests(id) ON DELETE SET NULL,
        source                          VARCHAR(32) NOT NULL DEFAULT 'INITIAL',
        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_settlement_type   CHECK (type IN ('COLLECTION', 'REFUND')),
        CONSTRAINT chk_settlement_status CHECK (status IN ('PENDING', 'PARTIAL', 'SETTLED')),
        CONSTRAINT chk_settlement_amount CHECK (amount > 0),
        CONSTRAINT chk_settlement_settled_le_amount CHECK (amount_settled <= amount)
      );
    `);

    await qr.query(`
      CREATE INDEX IF NOT EXISTS idx_settlement_tenant_status_due
        ON payment_settlements (tenant_id, status, due_date)
        WHERE is_deleted = false;
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS idx_settlement_tenant_booking
        ON payment_settlements (tenant_id, booking_id)
        WHERE is_deleted = false;
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS idx_settlement_tenant_created
        ON payment_settlements (tenant_id, created_at DESC)
        WHERE is_deleted = false;
    `);

    // ── 2. settlement_payments ────────────────────────────────────────
    await qr.query(`
      CREATE TABLE IF NOT EXISTS settlement_payments (
        id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        tenant_id       UUID NOT NULL,
        settlement_id   UUID NOT NULL REFERENCES payment_settlements(id) ON DELETE CASCADE,
        paid_amount     NUMERIC(12,2) NOT NULL,
        paid_date       DATE NOT NULL,
        mode            VARCHAR(20) NOT NULL,
        ref_no          VARCHAR(100),
        notes           TEXT,
        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_settlement_payment_amount CHECK (paid_amount > 0),
        CONSTRAINT chk_settlement_payment_mode
          CHECK (mode IN ('CASH', 'CARD', 'BANK_TRANSFER', 'UPI', 'WALLET', 'CHEQUE'))
      );
    `);

    await qr.query(`
      CREATE INDEX IF NOT EXISTS idx_settlement_payment_tenant_settlement
        ON settlement_payments (tenant_id, settlement_id)
        WHERE is_deleted = false;
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS idx_settlement_payment_tenant_date
        ON settlement_payments (tenant_id, paid_date DESC)
        WHERE is_deleted = false;
    `);

    // ── 3. bookings.payment_status ────────────────────────────────────
    await qr.query(`
      ALTER TABLE bookings
        ADD COLUMN IF NOT EXISTS payment_status VARCHAR(20) NOT NULL DEFAULT 'UNPAID';
    `);
    await qr.query(`
      ALTER TABLE bookings
        ADD CONSTRAINT chk_booking_payment_status
        CHECK (payment_status IN ('UNPAID', 'PARTIAL', 'PAID', 'REFUND_DUE'));
    `);

    // ── 4. booking_transport_items.is_amended ────────────────────────
    await qr.query(`
      ALTER TABLE booking_transport_items
        ADD COLUMN IF NOT EXISTS is_amended BOOLEAN NOT NULL DEFAULT FALSE;
    `);

    // ── 5. booking_transport_items.actual_supplier_cost ──────────────
    await qr.query(`
      ALTER TABLE booking_transport_items
        ADD COLUMN IF NOT EXISTS actual_supplier_cost NUMERIC(12,2);
    `);

    // ── 6. Seed RBAC for the new module ──────────────────────────────
    // Idempotent: skip rows that already exist (tenant + role + module).
    // EDIT level grants Settle/Record-Payment/Add-Amended access; VIEW
    // grants list+detail. Admin gets DELETE for completeness.
    await qr.query(`
      INSERT INTO role_permissions (id, tenant_id, role_id, module, access_level, is_deleted, created_at, updated_at)
      SELECT gen_random_uuid(), r.tenant_id, r.id, 'payment_settlement', 'delete', false, NOW(), NOW()
        FROM roles r
       WHERE r.is_deleted = false
         AND LOWER(r.name) IN ('admin', 'administrator')
         AND NOT EXISTS (
           SELECT 1 FROM role_permissions rp
            WHERE rp.tenant_id = r.tenant_id
              AND rp.role_id = r.id
              AND rp.module = 'payment_settlement'
              AND rp.is_deleted = false
         );
    `);
    await qr.query(`
      INSERT INTO role_permissions (id, tenant_id, role_id, module, access_level, is_deleted, created_at, updated_at)
      SELECT gen_random_uuid(), r.tenant_id, r.id, 'payment_settlement', 'edit', false, NOW(), NOW()
        FROM roles r
       WHERE r.is_deleted = false
         AND LOWER(r.name) IN ('operations manager', 'ops manager')
         AND NOT EXISTS (
           SELECT 1 FROM role_permissions rp
            WHERE rp.tenant_id = r.tenant_id
              AND rp.role_id = r.id
              AND rp.module = 'payment_settlement'
              AND rp.is_deleted = false
         );
    `);
  }

  async down(qr: QueryRunner): Promise<void> {
    await qr.query(`DELETE FROM role_permissions WHERE module = 'payment_settlement';`);

    await qr.query(
      `ALTER TABLE booking_transport_items DROP COLUMN IF EXISTS actual_supplier_cost;`,
    );
    await qr.query(`ALTER TABLE booking_transport_items DROP COLUMN IF EXISTS is_amended;`);

    await qr.query(`ALTER TABLE bookings DROP CONSTRAINT IF EXISTS chk_booking_payment_status;`);
    await qr.query(`ALTER TABLE bookings DROP COLUMN IF EXISTS payment_status;`);

    await qr.query(`DROP INDEX IF EXISTS idx_settlement_payment_tenant_date;`);
    await qr.query(`DROP INDEX IF EXISTS idx_settlement_payment_tenant_settlement;`);
    await qr.query(`DROP TABLE IF EXISTS settlement_payments;`);

    await qr.query(`DROP INDEX IF EXISTS idx_settlement_tenant_created;`);
    await qr.query(`DROP INDEX IF EXISTS idx_settlement_tenant_booking;`);
    await qr.query(`DROP INDEX IF EXISTS idx_settlement_tenant_status_due;`);
    await qr.query(`DROP TABLE IF EXISTS payment_settlements;`);
  }
}