import {
  Injectable,
  BadRequestException,
  NotFoundException,
  ForbiddenException,
} from '@nestjs/common';
import { ClsService } from 'nestjs-cls';
import { PaymentSettlementRepository } from './repositories/payment-settlement.repository';
import { SettlementPaymentRepository } from './repositories/settlement-payment.repository';
import { BookingSettlementMasterRepository } from './repositories/booking-settlement-master.repository';
import { SettlementAnalyticsRepository } from './repositories/settlement-analytics.repository';
import {
  PaymentSettlementEntity,
  SettlementParty,
  SettlementStatus,
  SettlementType,
} from '../../entities/payment-settlement.entity';
import {
  BookingSettlementMasterEntity,
  OverallSettlementStatus,
} from '../../entities/booking-settlement-master.entity';
import { allocateReceiptNumber } from './receipt-sequence.helper';
import { rowsFromUpdateReturning } from './query-update-returning';
import {
  SettlementPaymentEntity,
  SettlementPaymentMode,
} from '../../entities/settlement-payment.entity';
import { SettlementPaymentType } from '../../common/constants/settlement-payment-type';
import { BookingPaymentStatus } from '../../entities/booking.entity';
import { CLS_USER_ID } from '../../common/cls/cls.constants';
import { TaxSlabResolverService } from '../../common/services/tax-slab-resolver.service';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { BookingEntity } from '../../entities/booking.entity';
import { QueryEntity } from '../../entities/query.entity';
import { PaymentCollectionRepository } from './repositories/payment-collection.repository';
import { CustomerCreditLedgerRepository } from './repositories/customer-credit-ledger.repository';
import { SupplierPayoutRepository } from './repositories/supplier-payout.repository';
import {
  PayoutExcessTreatment,
  SupplierPayoutEntity,
} from '../../entities/supplier-payout.entity';
import {
  PaymentCollectionEntity,
  CollectionExcessTreatment,
  CollectionPartyType,
} from '../../entities/payment-collection.entity';
import { CreditLedgerPartyType } from '../../entities/customer-credit-ledger.entity';
import { CreditLedgerDirection } from '../../entities/customer-credit-ledger-movement.entity';
import { TripSourceEntity } from '../../entities/trip-source.entity';
import { BankAccountService } from '../bank-account/bank-account.service';

/**
 * Payload accepted by `recordPayment`. Mirrors the Settle Dialog fields.
 */
export interface RecordPaymentInput {
  paid_amount: number;
  paid_date: string;
  mode: SettlementPaymentMode;
  ref_no?: string | null;
  notes?: string | null;
}

/**
 * Per-line breakdown captured at the time of an itinerary delta so the
 * Settlement Detail page can show "Service Change Breakdown" without a
 * second query against the audit log. Shape kept loose — the renderer
 * just needs `{added, dropped, modified}` arrays.
 */
export interface SettlementBreakdown {
  added?: Array<Record<string, unknown>>;
  dropped?: Array<Record<string, unknown>>;
  modified?: Array<Record<string, unknown>>;
}

interface RecordFromBookingInput {
  booking_id: string;
  amount: number;
  currency_id: string;
  due_date: string;
}

interface RecordDeltaInput {
  booking_id: string;
  delta: number;
  currency_id: string;
  due_date: string;
  breakdown?: SettlementBreakdown | null;
  source?: string;
  triggered_by_change_request_id?: string | null;
}

/**
 * Supplier-side trigger payload. Raised by driver-assignment when ops
 * picks a supplier on a booking_transport_item (or activity item).
 *
 *   amount      — what we owe the supplier (master cost or override)
 *   currency_id — must equal the booking's selling currency; we don't
 *                 invoice suppliers in a different currency than we
 *                 sold the trip in
 *   source_item_type / source_item_id — which booking line spawned it
 *
 * Idempotent — `(tenant_id, source_item_type, source_item_id)` has a
 * partial unique index for SUPPLIER party rows. Reassigning the same
 * supplier on the same line is a no-op; reassigning a DIFFERENT
 * supplier voids the previous row and creates a new one (handled by
 * the driver-assignment hook, not here).
 */
interface RecordSupplierPayableInput {
  booking_id: string;
  supplier_id: string;
  amount: number;
  currency_id: string;
  due_date: string;
  source_item_type: string;
  source_item_id: string;
}

/**
 * One outstanding row on the Collect Payment grid — aggregated per
 * BOOKING (not per service-line settlement). `amount` is the booking's
 * total customer-side COLLECTION invoice, `amount_settled` is what's
 * been received so far, `outstanding` is the difference. `lines` carries
 * the still-open per-line settlements (oldest-first) so a booking-level
 * collection can be spread back across them on submit — the ledger
 * itself stays per-line.
 */
export interface CollectOutstandingBookingRow {
  booking_id: string;
  booking_number: string | null;
  guest_name: string | null;
  guest_mobile: string | null;
  travel_date: string | null; // trip start date
  travel_end_date: string | null; // trip end date
  due_date: string; // earliest open due date across the booking's lines
  amount: number; // total invoice for the booking
  amount_settled: number; // received so far
  outstanding: number;
  currency_id: string;
  currency_code: string;
  destination_id: string | null;
  destination_name: string | null;
  has_pan_card: boolean;
  lines: Array<{ settlement_id: string; outstanding: number; due_date: string }>;
}

/**
 * Booking-anchored outstanding query for the Collect Payment grid.
 *
 * Grain: one row per (booking, currency). `bookings` is the anchor
 * table and the CUSTOMER-side COLLECTION ledger is LEFT-joined so the
 * SUMs cover EVERY service line of the booking — invoice = SUM(amount),
 * received = SUM(amount_settled), outstanding = the difference — even
 * when some lines are already fully settled. `HAVING` then keeps only
 * bookings that still owe. `lines` carries just the still-open lines
 * (oldest-first) as allocation targets; the ledger stays per-line.
 *
 * Param positions are fixed: $1 = tenant_id, $2 = the party value
 * (guest_mobile or source_id), $3 = exclude_collection_id (nil uuid
 * when not excluding). `currencyFilter` is either '' or
 * `AND ps.currency_id = $4` and is injected into the JOIN's ON clause
 * (NOT the WHERE) so it can't collapse the LEFT JOIN to an INNER JOIN.
 *
 * $3 adds this collection's payments back onto outstanding so the
 * collect form can re-edit them without the rows disappearing.
 *
 * @param partyColumn whitelisted column — 'q.guest_mobile' | 'q.source_id'
 */
const NIL_COLLECTION_ID = '00000000-0000-0000-0000-000000000000';

function buildCollectOutstandingByBookingSql(
  partyColumn: 'q.guest_mobile' | 'q.source_id',
  currencyFilter: string,
): string {
  return `SELECT
          b.id               AS booking_id,
          b.booking_number   AS booking_number,
          q.guest_name       AS guest_name,
          q.guest_mobile     AS guest_mobile,
          q.start_date       AS travel_date,
          q.end_date         AS travel_end_date,
          q.destination_id   AS destination_id,
          d.name             AS destination_name,
          ps.currency_id     AS currency_id,
          c.code             AS currency_code,
          MIN(ps.due_date) FILTER (
            WHERE (ps.amount - ps.amount_settled + COALESCE(ex.paid, 0)) > 0.005
          ) AS due_date,
          SUM(ps.amount) AS amount,
          SUM(ps.amount_settled - COALESCE(ex.paid, 0)) AS amount_settled,
          SUM(ps.amount - ps.amount_settled + COALESCE(ex.paid, 0)) AS outstanding,
          COALESCE(
            json_agg(
              json_build_object(
                'settlement_id', ps.id,
                'outstanding',   (ps.amount - ps.amount_settled + COALESCE(ex.paid, 0)),
                'due_date',      ps.due_date
              ) ORDER BY ps.due_date ASC, ps.created_at ASC
            ) FILTER (
              WHERE (ps.amount - ps.amount_settled + COALESCE(ex.paid, 0)) > 0.005
            ),
            '[]'::json
          ) AS lines,
          BOOL_OR(b.pan_card_filename IS NOT NULL) AS has_pan_card
        FROM bookings b
        INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        LEFT JOIN payment_settlements ps
               ON ps.booking_id = b.id
              AND ps.tenant_id = $1
              AND ps.is_deleted = false
              AND ps.party_type = 'CUSTOMER'
              AND ps.type = 'COLLECTION'
              ${currencyFilter}
        LEFT JOIN LATERAL (
          SELECT COALESCE(SUM(sp.paid_amount), 0) AS paid
            FROM settlement_payments sp
           WHERE sp.settlement_id = ps.id
             AND sp.tenant_id = $1
             AND sp.collection_id = $3::uuid
             AND sp.is_void = false
             AND sp.is_deleted = false
        ) ex ON true
        LEFT JOIN currencies c ON c.id = ps.currency_id
        LEFT JOIN destinations d ON d.id = q.destination_id
       WHERE b.tenant_id = $1
         AND b.is_deleted = false
         AND ${partyColumn} = $2
       GROUP BY b.id, b.booking_number, q.guest_name, q.guest_mobile,
                q.start_date, q.end_date, q.destination_id, d.name,
                ps.currency_id, c.code
       HAVING SUM(ps.amount - ps.amount_settled + COALESCE(ex.paid, 0)) > 0.005
       ORDER BY MIN(ps.due_date) FILTER (
         WHERE (ps.amount - ps.amount_settled + COALESCE(ex.paid, 0)) > 0.005
       ) ASC`;
}

/**
 * Coerce a grouped SQL row into a CollectOutstandingBookingRow. pg
 * returns numeric columns as strings and json_agg values as parsed
 * JSON, so we normalise both here.
 */
function mapCollectOutstandingBookingRow(r: Record<string, unknown>): CollectOutstandingBookingRow {
  const rawLines = (r.lines ?? []) as Array<{
    settlement_id: string;
    outstanding: string | number;
    due_date: string;
  }>;
  return {
    booking_id: String(r.booking_id),
    booking_number: (r.booking_number as string) ?? null,
    guest_name: (r.guest_name as string) ?? null,
    guest_mobile: (r.guest_mobile as string) ?? null,
    travel_date: (r.travel_date as string) ?? null,
    travel_end_date: (r.travel_end_date as string) ?? null,
    due_date: String(r.due_date),
    amount: Number(r.amount),
    amount_settled: Number(r.amount_settled),
    outstanding: Number(r.outstanding),
    currency_id: String(r.currency_id),
    currency_code: (r.currency_code as string) ?? '',
    destination_id: (r.destination_id as string) ?? null,
    destination_name: (r.destination_name as string) ?? null,
    has_pan_card: Boolean(r.has_pan_card),
    lines: rawLines.map((l) => ({
      settlement_id: String(l.settlement_id),
      outstanding: Number(l.outstanding),
      due_date: String(l.due_date),
    })),
  };
}

/**
 * Payment Settlement service — Phase B.
 *
 * Three writers + one rollup, all wired through the tenant-aware repos
 * so CLS scoping is automatic:
 *
 *   recordFromBooking      — initial COLLECTION at booking confirm
 *   recordItineraryDelta   — Δ-driven Collection (+) or Refund (−)
 *   recordPayment          — Settle action (handles partials + auto-flip)
 *   recomputeBookingPaymentStatus — updates bookings.payment_status pill
 *
 * Reads (Phase A) stay on `findForBooking` / `findById` / `findPayments`.
 *
 * Callers (BookingService.createFromQuery, BookingItineraryService.update,
 * Settlement controller) MUST be inside an authed request so the CLS
 * context carries tenant_id + user_id — the writers depend on both.
 */
@Injectable()
export class PaymentSettlementService {
  constructor(
    private readonly settlements: PaymentSettlementRepository,
    private readonly payments: SettlementPaymentRepository,
    private readonly masters: BookingSettlementMasterRepository,
    private readonly analytics: SettlementAnalyticsRepository,
    private readonly cls: ClsService,
    // Step 3h — every settlement_payments row captures the customer-
    // side (or supplier-side, for supplier payouts) slab snapshot at
    // the moment money moved. Accountant report (parked) reads from
    // this to prorate VAT collected / Tourism collected / etc. across
    // instalments without re-resolving the slab at report time.
    private readonly taxResolver: TaxSlabResolverService,
    @InjectRepository(BookingEntity)
    private readonly bookingRepo: Repository<BookingEntity>,
    @InjectRepository(QueryEntity)
    private readonly queryRepo: Repository<QueryEntity>,
    private readonly collections: PaymentCollectionRepository,
    private readonly creditLedger: CustomerCreditLedgerRepository,
    private readonly payouts: SupplierPayoutRepository,
    private readonly bankAccounts: BankAccountService,
  ) {}

  /**
   * Resolve the slab snapshot for a settlement at payment-record time.
   * Customer-side instalments use the destination's default slab;
   * supplier-side instalments use the supplier's slab (with the same
   * destination fallback the resolver applies elsewhere).
   */
  private async resolvePaymentSnapshot(
    settlement: PaymentSettlementEntity,
  ): Promise<Record<string, unknown> | null> {
    const booking = await this.bookingRepo.findOne({
      where: { id: settlement.booking_id },
      select: { id: true, query_id: true } as any,
    });
    if (!booking?.query_id) return null;
    const query = await this.queryRepo.findOne({
      where: { id: booking.query_id },
      select: { id: true, destination_id: true } as any,
    });
    if (!query?.destination_id) return null;
    // TaxSlabSnapshotInput is structurally compatible with the JSONB
    // column's `Record<string, unknown>` type, but nominal typing
    // requires the cast. Safe — the snapshot is treated as a frozen
    // blob from here on.
    const snapshot =
      settlement.party_type === SettlementParty.SUPPLIER
        ? await this.taxResolver.resolveForSupplier(
            settlement.supplier_id ?? null,
            query.destination_id,
          )
        : await this.taxResolver.resolveForHotelOrSpecial(query.destination_id);
    return snapshot as unknown as Record<string, unknown> | null;
  }

  // ────────────────────────────────────────────────────────────────────
  // Analytical views — same fact table, sliced differently. Each is a
  // GROUP BY aggregation; no new storage.
  // ────────────────────────────────────────────────────────────────────

  /** By Supplier — accounts-payable view across bookings. */
  async aggregateBySupplier(opts: Parameters<SettlementAnalyticsRepository['bySupplier']>[0]) {
    return this.analytics.bySupplier(opts);
  }

  /** By Sales Agent — commission base view; date-window filtered. */
  async aggregateByAgent(opts: Parameters<SettlementAnalyticsRepository['byAgent']>[0]) {
    return this.analytics.byAgent(opts);
  }

  /** By Trip Source — booking-origin view (TravClan, MakeMyTrip,
   *  walk-in office, …). Powers the All Settlements "By Agent" tab
   *  per §12f. */
  async aggregateBySource(opts: Parameters<SettlementAnalyticsRepository['bySource']>[0]) {
    return this.analytics.bySource(opts);
  }

  /** By Service-line — product P&L view (transport/activity/hotel). */
  async aggregateByService(opts: Parameters<SettlementAnalyticsRepository['byService']>[0]) {
    return this.analytics.byService(opts);
  }

  // ────────────────────────────────────────────────────────────────────
  // Reads (Phase A surface, kept stable)
  // ────────────────────────────────────────────────────────────────────

  async findForBooking(bookingId: string) {
    return this.settlements.findActiveForBooking(bookingId);
  }

  async findById(id: string) {
    return this.settlements.findById(id);
  }

  async findPayments(settlementId: string) {
    return this.payments.findBySettlement(settlementId);
  }

  /** Paginated list + filters for the Settlements admin page. */
  async findPaginated(query: import('./dto/settlement-query.dto').SettlementQueryDto) {
    return this.settlements.findPaginatedList(query);
  }

  /** Settlement detail bundle — row + ledger in one call. Hydrates
   *  the change-request relation so the detail page can render the
   *  "Triggered by Change Request" traceability card. */
  async findDetail(id: string) {
    const settlement = await this.settlements.findByIdWithDetail(id);
    if (!settlement) return null;
    const ledger = await this.payments.findBySettlement(id);
    return { settlement, payments: ledger };
  }

  /** Dashboard stats — pending + settled buckets per type, window-scoped. */
  async stats(from?: string, to?: string, destinationId?: string) {
    return this.settlements.statsForDashboard(from, to, destinationId);
  }

  /**
   * Phase H — Admin edit of settlement.amount post-create. Guards:
   *   - new amount must be > 0 (CHECK constraint at the DB layer too)
   *   - new amount must be >= amount_settled (settled never exceeds amount;
   *     reducing below `amount_settled` would require voiding payments first)
   *   - reason is mandatory (audit trail; passed as `notes` append)
   * Re-flips status to PARTIAL if the new amount is now larger than
   * what's already settled, or to SETTLED if it now matches.
   */
  async editAmount(
    settlementId: string,
    newAmount: number,
    reason: string,
  ): Promise<PaymentSettlementEntity> {
    const settlement = await this.settlements.findById(settlementId);
    if (!settlement) throw new NotFoundException('Settlement not found');
    if (!Number.isFinite(newAmount) || newAmount <= 0) {
      throw new BadRequestException('amount must be greater than 0');
    }
    const settled = Number(settlement.amount_settled);
    if (newAmount < settled - 0.005) {
      throw new BadRequestException(
        `amount ${newAmount.toFixed(2)} cannot be less than amount_settled ${settled.toFixed(2)} — void payments first`,
      );
    }
    if (!reason || !reason.trim()) {
      throw new BadRequestException('reason is required');
    }

    // Status follows the new amount: SETTLED if fully covered, PARTIAL
    // if partly covered, PENDING if nothing landed yet.
    let nextStatus: SettlementStatus;
    if (settled >= newAmount - 0.005) nextStatus = SettlementStatus.SETTLED;
    else if (settled > 0.005) nextStatus = SettlementStatus.PARTIAL;
    else nextStatus = SettlementStatus.PENDING;

    const note = this.buildNoteAppend(
      `Amount edited ${Number(settlement.amount).toFixed(2)} → ${newAmount.toFixed(2)}. Reason: ${reason.trim()}`,
      settlement.notes,
    );

    await this.settlements.update(settlementId, {
      amount: newAmount,
      status: nextStatus,
      // If a previously-SETTLED row reopens, clear settled_date so the
      // dashboard "settled this month" stat doesn't count it twice.
      settled_date: nextStatus === SettlementStatus.SETTLED ? settlement.settled_date : null,
      notes: note,
    });

    if (settlement.party_type !== SettlementParty.SUPPLIER) {
      await this.recomputeBookingPaymentStatus(settlement.booking_id);
    }
    if (settlement.master_id) {
      await this.recomputeMaster(settlement.master_id);
    }

    const refreshed = await this.settlements.findById(settlementId);
    return refreshed!;
  }

  /**
   * Phase H — append a timestamped, user-tagged line to the settlement
   * notes column. Append-only by convention; historical entries stay.
   */
  async appendNote(settlementId: string, text: string): Promise<PaymentSettlementEntity> {
    const settlement = await this.settlements.findById(settlementId);
    if (!settlement) throw new NotFoundException('Settlement not found');
    const cleaned = (text || '').trim();
    if (!cleaned) throw new BadRequestException('note text is required');

    await this.settlements.update(settlementId, {
      notes: this.buildNoteAppend(cleaned, settlement.notes),
    });
    const refreshed = await this.settlements.findById(settlementId);
    return refreshed!;
  }

  private buildNoteAppend(line: string, existing: string | null): string {
    const userId = this.cls.get<string>(CLS_USER_ID) || 'system';
    const stamp = new Date().toISOString().replace('T', ' ').slice(0, 16);
    const entry = `[${stamp} · ${userId}] ${line}`;
    return existing ? `${existing}\n${entry}` : entry;
  }

  /**
   * Phase H — Excel export of the current filtered list. Builds a
   * single-sheet workbook using the same `exceljs` lib transport
   * pricing already uses. Returns a Buffer the controller streams as
   * an attachment.
   *
   * Filters mirror the list endpoint: status, type, party, supplier,
   * destination, date range, free-text search. No pagination — export
   * is always the FULL filtered set (within a sane upper bound).
   */
  async exportToExcel(query: import('./dto/settlement-query.dto').SettlementQueryDto) {
    // Reuse the same list path but with a giant page size to dodge
    // pagination. Excel exports rarely exceed 50k rows in this
    // domain; tenants with more should ask for a chunked job (future).
    const { items } = await this.settlements.findPaginatedList({
      ...query,
      page: 1,
      limit: 10000,
    });

    // Lazy-import exceljs so the cold-start cost is paid only on
    // export calls, not on every module boot.
    const ExcelJS = await import('exceljs');
    const wb = new ExcelJS.Workbook();
    wb.creator = 'DMC Ops';
    wb.created = new Date();
    const ws = wb.addWorksheet('Settlements');
    ws.columns = [
      { header: 'Settlement ID', key: 'id', width: 38 },
      { header: 'Booking #', key: 'booking_number', width: 16 },
      { header: 'Guest', key: 'guest', width: 22 },
      { header: 'Type', key: 'type', width: 12 },
      { header: 'Party', key: 'party_type', width: 12 },
      { header: 'Status', key: 'status', width: 12 },
      { header: 'Currency', key: 'currency', width: 10 },
      { header: 'Amount', key: 'amount', width: 14 },
      { header: 'Settled', key: 'settled', width: 14 },
      { header: 'Outstanding', key: 'outstanding', width: 14 },
      { header: 'Due Date', key: 'due_date', width: 14 },
      { header: 'Settled Date', key: 'settled_date', width: 14 },
      { header: 'Source', key: 'source', width: 18 },
      { header: 'Created', key: 'created_at', width: 22 },
    ];
    ws.getRow(1).font = { bold: true };

    for (const r of items) {
      const amount = Number(r.amount);
      const settled = Number(r.amount_settled);
      ws.addRow({
        id: r.id,
        booking_number: r.booking?.booking_number ?? '',
        guest: r.booking?.query?.guest_name ?? '',
        type: r.type,
        party_type: r.party_type,
        status: r.status,
        currency: r.currency?.code ?? '',
        amount: amount.toFixed(2),
        settled: settled.toFixed(2),
        outstanding: (amount - settled).toFixed(2),
        due_date: r.due_date,
        settled_date: r.settled_date ?? '',
        source: r.source,
        created_at: r.created_at,
      });
    }

    return Buffer.from(await wb.xlsx.writeBuffer());
  }

  // ────────────────────────────────────────────────────────────────────
  // Writers
  // ────────────────────────────────────────────────────────────────────

  /**
   * Create the initial COLLECTION row for a freshly confirmed booking.
   *
   * Idempotent: if a settlement already exists for the booking (any
   * type, any status), this is a no-op. Lets `createFromQuery` call
   * us unconditionally without worrying about double-charging when
   * the booking-create path is retried by a flaky client.
   *
   * Skips when `amount <= 0` — a zero-priced booking has no money to
   * collect and a row with amount = 0 would violate the CHECK constraint.
   */
  async recordFromBooking(input: RecordFromBookingInput): Promise<PaymentSettlementEntity | null> {
    if (!input.amount || input.amount <= 0) return null;

    const existing = await this.settlements.findActiveForBooking(input.booking_id);
    if (existing.length > 0) return null;

    // Booking can't already be HOLD on first create, but the guard is
    // cheap + future-proofs any retry flow.
    await this.assertMasterNotOnHold(input.booking_id);

    // Ensure the per-booking master + snapshot sales agent BEFORE the
    // child save so master_id + sales_agent_id are populated atomically.
    const master = await this.ensureMaster({
      booking_id: input.booking_id,
      currency_id: input.currency_id,
    });
    const sales_agent_id = await this.snapshotSalesAgent(input.booking_id);

    const row = await this.settlements.save({
      booking_id: input.booking_id,
      master_id: master.id,
      sales_agent_id,
      type: SettlementType.COLLECTION,
      amount: input.amount,
      amount_settled: 0,
      currency_id: input.currency_id,
      status: SettlementStatus.PENDING,
      due_date: input.due_date,
      source: 'INITIAL',
      party_type: SettlementParty.CUSTOMER,
    });

    await this.recomputeMaster(master.id);
    await this.recomputeBookingPaymentStatus(input.booking_id);
    return row;
  }

  /**
   * Emit a settlement row for an itinerary edit's price delta.
   *
   * Convention: `delta = new_selling_total - old_selling_total`.
   *   delta > 0  → COLLECTION for `+delta`
   *   delta < 0  → REFUND     for `|delta|`
   *   delta == 0 → no-op
   *
   * `source` defaults to `ITINERARY_EDIT`; the Movement Chart's
   * "+ Add amended transport" pathway passes `MOVEMENT_CHART` to tag
   * the row. `triggered_by_change_request_id` lets us trace which CR
   * approval produced the row (null for direct admin saves).
   *
   * Skips when delta is zero or the booking already has a settled
   * amount that perfectly matches the new total (defensive; should
   * never happen with single-writer flow, but cheap to assert).
   */
  async recordItineraryDelta(input: RecordDeltaInput): Promise<PaymentSettlementEntity | null> {
    const delta = Number(input.delta);
    if (!Number.isFinite(delta) || delta === 0) return null;

    // Refuse to raise auto-deltas against a held booking.
    await this.assertMasterNotOnHold(input.booking_id);

    const type = delta > 0 ? SettlementType.COLLECTION : SettlementType.REFUND;
    const amount = Math.abs(delta);

    const master = await this.ensureMaster({
      booking_id: input.booking_id,
      currency_id: input.currency_id,
    });
    const sales_agent_id = await this.snapshotSalesAgent(input.booking_id);

    const row = await this.settlements.save({
      booking_id: input.booking_id,
      master_id: master.id,
      sales_agent_id,
      type,
      amount,
      amount_settled: 0,
      currency_id: input.currency_id,
      status: SettlementStatus.PENDING,
      due_date: input.due_date,
      change_breakdown: input.breakdown ?? null,
      source: input.source ?? 'ITINERARY_EDIT',
      triggered_by_change_request_id: input.triggered_by_change_request_id ?? null,
      party_type: SettlementParty.CUSTOMER,
    });

    await this.recomputeMaster(master.id);
    await this.recomputeBookingPaymentStatus(input.booking_id);
    return row;
  }

  /**
   * Record a payment against a settlement (collection receipt or refund
   * payout — the row's `type` carries the direction).
   *
   * Behaviour:
   *   - Settlement must be PENDING or PARTIAL. SETTLED → 409-ish guard.
   *   - paid_amount must be > 0 and must NOT push amount_settled past
   *     the settlement's `amount`. Overpayment is rejected; ops splits
   *     into two settlements if needed (or amends via the admin
   *     edit-amount action — gated separately).
   *   - Appends a settlement_payments row, increments parent
   *     amount_settled, flips status to SETTLED + stamps settled_date
   *     when the running total reaches `amount`. Otherwise PARTIAL.
   *   - Recomputes the booking-level payment_status rollup.
   *
   * The CHECK constraint `amount_settled <= amount` on the DB is the
   * belt to this code's braces — a race that gets past the guard will
   * 23514 instead of corrupting the ledger.
   */
  async recordPayment(
    settlementId: string,
    input: RecordPaymentInput,
  ): Promise<{ settlement: PaymentSettlementEntity; payment: SettlementPaymentEntity }> {
    const settlement = await this.settlements.findById(settlementId);
    if (!settlement) throw new NotFoundException('Settlement not found');
    if (settlement.status === SettlementStatus.SETTLED) {
      throw new BadRequestException('Settlement is already settled');
    }
    const paid = Number(input.paid_amount);
    if (!Number.isFinite(paid) || paid <= 0) {
      throw new BadRequestException('paid_amount must be greater than 0');
    }
    const outstanding = Number(settlement.amount) - Number(settlement.amount_settled);
    // 0.005 fudge to absorb decimal rounding when the FE shows 2dp.
    if (paid > outstanding + 0.005) {
      throw new BadRequestException(
        `paid_amount ${paid} exceeds outstanding ${outstanding.toFixed(2)}`,
      );
    }

    // Phase F: receipts. Prefix follows the parent's direction
    // (RCP for collections, RFD for refunds). Year derived from the
    // paid_date so a back-dated payment lands in the correct year
    // bucket. Atomic via the receipt-sequences upsert.
    const year = (() => {
      const m = /^(\d{4})/.exec(input.paid_date);
      return m ? Number(m[1]) : new Date(input.paid_date).getUTCFullYear();
    })();
    const prefix = settlement.type === SettlementType.REFUND ? 'RFD' : 'RCP';
    const { receipt_no, receipt_year } = await allocateReceiptNumber(
      this.settlements.manager,
      this.settlements.currentTenantId(),
      year,
      prefix,
    );

    // Step 3h — freeze the slab snapshot on the payment row. Resolved
    // against the booking's destination + (if supplier-side) the
    // supplier's slab override. Stored as JSONB so a later report can
    // walk all payments and aggregate without re-resolving.
    const tax_snapshot = await this.resolvePaymentSnapshot(settlement);

    const payment = await this.payments.save({
      settlement_id: settlementId,
      paid_amount: paid,
      paid_date: input.paid_date,
      mode: input.mode,
      ref_no: input.ref_no ?? null,
      notes: input.notes ?? null,
      receipt_no,
      receipt_year,
      tax_snapshot,
    });

    // Phase F: decimal cap. Math.min guards against the 1000.02 corner
    // case where 3 × 333.34 would otherwise punch through the
    // amount_settled <= amount CHECK constraint.
    const tentative = Number(settlement.amount_settled) + paid;
    const cappedSettled = Math.min(Number(settlement.amount), tentative);
    const finalSettled = cappedSettled >= Number(settlement.amount) - 0.005;
    await this.settlements.update(settlementId, {
      amount_settled: finalSettled ? Number(settlement.amount) : cappedSettled,
      status: finalSettled ? SettlementStatus.SETTLED : SettlementStatus.PARTIAL,
      settled_date: finalSettled ? input.paid_date : null,
    });

    // Booking-level rollup is CUSTOMER-only. Supplier-side rows never
    // touch bookings.payment_status (they have their own surface).
    if (settlement.party_type !== SettlementParty.SUPPLIER) {
      await this.recomputeBookingPaymentStatus(settlement.booking_id);
    }

    // Phase J — push the rollup up to the parent master. Pre-migration
    // rows surface master_id as null and skip this step.
    if (settlement.master_id) {
      await this.recomputeMaster(settlement.master_id);
    }

    const refreshed = await this.settlements.findById(settlementId);
    return { settlement: refreshed!, payment };
  }

  /**
   * Raise a SUPPLIER-side payable for one booking line. Idempotent —
   * if a row already exists for (tenant, source_item_type, source_item_id),
   * returns the existing row instead of creating a new one. The
   * driver-assignment hook is responsible for voiding the old row on
   * supplier swap.
   *
   * Type is REFUND from the operator's perspective ("money out of us"),
   * matching the convention used on the CUSTOMER side. UI relabels
   * REFUND as "Payout" when party_type = SUPPLIER.
   */
  async recordSupplierPayable(
    input: RecordSupplierPayableInput,
  ): Promise<PaymentSettlementEntity | null> {
    if (!input.amount || input.amount <= 0) return null;

    // Idempotency check against the partial unique index. Avoids
    // catching the 23505 and reasoning about what happened.
    const existing = (await this.settlements.find({
      where: {
        booking_id: input.booking_id,
        source_item_type: input.source_item_type,
        source_item_id: input.source_item_id,
        party_type: SettlementParty.SUPPLIER,
      },
    })) as PaymentSettlementEntity[];
    if (existing.length > 0) return existing[0];

    // HOLD guard — supplier payables on a held booking get blocked
    // until admin releases. Same per-booking master as customer side.
    await this.assertMasterNotOnHold(input.booking_id);

    const master = await this.ensureMaster({
      booking_id: input.booking_id,
      currency_id: input.currency_id,
    });
    const sales_agent_id = await this.snapshotSalesAgent(input.booking_id);

    const row = await this.settlements.save({
      booking_id: input.booking_id,
      master_id: master.id,
      sales_agent_id,
      type: SettlementType.REFUND, // money flowing out to the supplier
      amount: input.amount,
      amount_settled: 0,
      currency_id: input.currency_id,
      status: SettlementStatus.PENDING,
      due_date: input.due_date,
      source: 'SUPPLIER_ASSIGNMENT',
      party_type: SettlementParty.SUPPLIER,
      supplier_id: input.supplier_id,
      source_item_type: input.source_item_type,
      source_item_id: input.source_item_id,
    });
    await this.recomputeMaster(master.id);
    return row;
  }

  /**
   * Adjust a supplier-side payable for a cost change on Movement Chart
   * (`booking_transport_items.actual_supplier_cost` edits).
   *
   *   - No existing SUPPLIER row for this source_item ⇒ no-op (the
   *     initial payable is raised by recordSupplierPayable when a
   *     supplier is first assigned). Caller can wire both paths if
   *     desired.
   *   - Existing row is PENDING (nothing paid yet) ⇒ update its amount
   *     in place so finance sees the corrected number.
   *   - Existing row is PARTIAL/SETTLED ⇒ raise a delta row for the
   *     diff so the original receipt history stays intact:
   *       new_cost > old → SUPPLIER + REFUND   (we owe supplier more)
   *       new_cost < old → SUPPLIER + COLLECTION (we recover from supplier)
   *
   * Idempotent on no-change. Skips when no booking/currency context
   * (defensive — the caller is responsible for happy-path data).
   */
  async adjustSupplierPayable(input: {
    booking_id: string;
    supplier_id: string;
    source_item_type: string;
    source_item_id: string;
    new_amount: number;
    currency_id: string;
    due_date: string;
  }): Promise<PaymentSettlementEntity | null> {
    const target = await this.settlements.findOne({
      where: {
        booking_id: input.booking_id,
        source_item_type: input.source_item_type,
        source_item_id: input.source_item_id,
        party_type: SettlementParty.SUPPLIER,
      },
    });
    if (!target) return null;

    const oldAmount = Number(target.amount);
    const newAmount = Number(input.new_amount);
    if (!Number.isFinite(newAmount) || newAmount < 0) return null;
    if (Math.abs(newAmount - oldAmount) < 0.005) return target; // no real change

    // HOLD guard — supplier cost edits on a held booking are blocked.
    await this.assertMasterNotOnHold(input.booking_id);

    const settled = Number(target.amount_settled);
    // Branch 1 — nothing paid yet: edit the parent row in place.
    // Cleanest possible audit trail; no delta noise.
    if (settled <= 0.005) {
      await this.settlements.update(target.id, { amount: newAmount });
      if (target.master_id) await this.recomputeMaster(target.master_id);
      return target;
    }

    // Branch 2 — payments exist: protect history, raise a delta row.
    // source_item_* deliberately omitted on delta rows so they don't
    // clash with the original payable on the partial unique index.
    // booking_id keeps the delta discoverable from the Booking detail.
    const delta = newAmount - oldAmount;
    const master = await this.ensureMaster({
      booking_id: input.booking_id,
      currency_id: input.currency_id,
    });
    const sales_agent_id = await this.snapshotSalesAgent(input.booking_id);
    const deltaRow = await this.settlements.save({
      booking_id: input.booking_id,
      master_id: master.id,
      sales_agent_id,
      type: delta > 0 ? SettlementType.REFUND : SettlementType.COLLECTION,
      amount: Math.abs(delta),
      amount_settled: 0,
      currency_id: input.currency_id,
      status: SettlementStatus.PENDING,
      due_date: input.due_date,
      source: 'COST_ADJUSTMENT',
      party_type: SettlementParty.SUPPLIER,
      supplier_id: input.supplier_id,
    });
    await this.recomputeMaster(master.id);
    return deltaRow;
  }

  /**
   * Void a previously recorded payment. Admin-only at the controller
   * layer; this method assumes the gate has already passed. Voided
   * payments stay in the ledger (audit) but stop contributing to the
   * parent's amount_settled. If the parent had been flipped to
   * SETTLED by this payment, the void reopens it to PARTIAL or
   * PENDING and clears the settled_date.
   *
   * Refuses to void a payment that's already voided (idempotency
   * guard). `reason` is mandatory at the DTO layer.
   */
  async voidPayment(
    paymentId: string,
    reason: string,
  ): Promise<{ settlement: PaymentSettlementEntity; payment: SettlementPaymentEntity }> {
    const payment = await this.payments.findById(paymentId);
    if (!payment) throw new NotFoundException('Payment not found');
    if (payment.is_void) throw new BadRequestException('Payment is already voided');
    if (!reason || !reason.trim()) {
      throw new BadRequestException('void_reason is required');
    }
    const settlement = await this.settlements.findById(payment.settlement_id);
    if (!settlement) throw new NotFoundException('Parent settlement not found');

    const userId = this.cls.get<string>(CLS_USER_ID) || null;
    await this.payments.update(paymentId, {
      is_void: true,
      voided_at: new Date(),
      voided_by: userId,
      void_reason: reason.trim(),
    });

    // Rewind the parent's amount_settled. Sum only non-void
    // payments — DB is the source of truth in case the parent was
    // mutated by other means.
    const ledger = await this.payments.findBySettlement(payment.settlement_id);
    const remaining = ledger
      .filter((p) => p.id !== paymentId && !p.is_void)
      .reduce((sum, p) => sum + Number(p.paid_amount), 0);
    const stillSettled = remaining >= Number(settlement.amount) - 0.005;
    const stillPartial = remaining > 0.005;
    await this.settlements.update(payment.settlement_id, {
      amount_settled: remaining,
      status: stillSettled
        ? SettlementStatus.SETTLED
        : stillPartial
          ? SettlementStatus.PARTIAL
          : SettlementStatus.PENDING,
      settled_date: stillSettled ? settlement.settled_date : null,
    });

    if (settlement.party_type !== SettlementParty.SUPPLIER) {
      await this.recomputeBookingPaymentStatus(settlement.booking_id);
    }
    if (settlement.master_id) {
      await this.recomputeMaster(settlement.master_id);
    }

    const refreshedSettlement = await this.settlements.findById(payment.settlement_id);
    const refreshedPayment = await this.payments.findById(paymentId);
    return { settlement: refreshedSettlement!, payment: refreshedPayment! };
  }

  // ────────────────────────────────────────────────────────────────────
  // Rollup — recompute bookings.payment_status from the active set.
  // ────────────────────────────────────────────────────────────────────

  /**
   * Recompute and persist `bookings.payment_status` from the active
   * settlement set. Rules (matches BookingPaymentStatus jsdoc):
   *
   *   refund_due > 0       → REFUND_DUE (wins over PAID/PARTIAL)
   *   else expected == 0   → UNPAID
   *   else received == 0   → UNPAID
   *   else received < exp  → PARTIAL
   *   else                 → PAID
   *
   * Where:
   *   expected   = Σ amount on active COLLECTIONs
   *   received   = Σ amount_settled on active COLLECTIONs
   *   refund_due = Σ (amount − amount_settled) on active REFUNDs
   *
   * Bypasses the booking module's update flow on purpose — we don't
   * want a payment_status change to count as an itinerary edit for
   * audit / CR / settlement-cascade purposes. Direct repo update via
   * the underlying manager keeps the change quiet but still audited
   * by the standard updated_by / updated_at columns.
   */
  async recomputeBookingPaymentStatus(bookingId: string): Promise<BookingPaymentStatus> {
    const rows = await this.settlements.findActiveForBooking(bookingId);
    let expected = 0;
    let received = 0;
    let refundDue = 0;
    for (const r of rows) {
      // The booking-level payment_status pill is a CUSTOMER rollup only
      // (see PaymentSettlementEntity jsdoc). Supplier payouts/refunds
      // live on the separate supplier axis — including them here made a
      // supplier refund flip the customer pill to REFUND_DUE.
      if (r.party_type !== SettlementParty.CUSTOMER) continue;
      const amt = Number(r.amount);
      const settled = Number(r.amount_settled);
      if (r.type === SettlementType.COLLECTION) {
        expected += amt;
        received += settled;
      } else {
        refundDue += amt - settled;
      }
    }

    let next: BookingPaymentStatus;
    if (refundDue > 0.005) next = BookingPaymentStatus.REFUND_DUE;
    else if (expected <= 0.005) next = BookingPaymentStatus.UNPAID;
    else if (received <= 0.005) next = BookingPaymentStatus.UNPAID;
    else if (received + 0.005 < expected) next = BookingPaymentStatus.PARTIAL;
    else next = BookingPaymentStatus.PAID;

    // Direct UPDATE on bookings — the settlements repo's manager is
    // tenant-scoped at the FROM-side, so we add the tenant filter
    // explicitly here to keep the same guarantee.
    const tenantId = this.settlements.currentTenantId();
    const userId = this.cls.get<string>(CLS_USER_ID) || null;
    const manager = this.settlements.manager;
    await manager.query(
      `UPDATE bookings
          SET payment_status = $1,
              updated_at = NOW(),
              updated_by = $2
        WHERE id = $3
          AND tenant_id = $4
          AND payment_status <> $1`,
      [next, userId, bookingId, tenantId],
    );

    return next;
  }

  // ────────────────────────────────────────────────────────────────────
  // Booking Settlement Master (one per booking)
  //
  // Every payment_settlement row is a CHILD of a per-booking master.
  // The master is created lazily by `ensureMaster` on first child
  // creation. Every subsequent mutation calls `recomputeMaster` to
  // keep the customer/supplier rollup totals + statuses + margin in
  // sync. Supplier-side breakdown across multiple suppliers on the
  // same booking lives at the children level — drill into the master
  // for the per-supplier view.
  //
  // HOLD is admin-only via `placeOnHold` / `releaseHold`. While HOLD,
  // auto-delta paths (recordItineraryDelta, adjustSupplierPayable,
  // recordSupplierPayable) throw ForbiddenException instead of silently
  // mutating the child set.
  // ────────────────────────────────────────────────────────────────────

  /**
   * Find-or-create the master row for a booking. Idempotent — guards
   * against concurrent first-create collisions on the partial unique
   * index by catching 23505 and re-reading.
   *
   * `currency_id` is required at create time but ignored on re-find —
   * the existing master's currency wins.
   */
  async ensureMaster(input: {
    booking_id: string;
    currency_id: string;
  }): Promise<BookingSettlementMasterEntity> {
    const existing = await this.masters.findActiveByBooking(input.booking_id);
    if (existing) return existing;

    try {
      return await this.masters.save({
        booking_id: input.booking_id,
        currency_id: input.currency_id,
        overall_status: OverallSettlementStatus.PENDING,
        last_activity_at: new Date(),
      });
    } catch (err: unknown) {
      // 23505 — partial-unique violation. Race: another writer created
      // the master between our `findActiveByBooking` and `save`. Re-read.
      const e = err as { code?: string };
      if (e?.code === '23505') {
        const refound = await this.masters.findActiveByBooking(input.booking_id);
        if (refound) return refound;
      }
      throw err;
    }
  }

  /**
   * Recompute the master's rollup (customer_*, supplier_*, margin)
   * from its active children. HOLD masters are NEVER auto-recomputed
   * — admin must `releaseHold` first. Cheap: one aggregate query.
   */
  async recomputeMaster(masterId: string): Promise<BookingSettlementMasterEntity | null> {
    const master = await this.masters.findById(masterId);
    if (!master) return null;
    if (master.overall_status === OverallSettlementStatus.HOLD) {
      // HOLD freezes the rollup. Touch only last_activity_at so list
      // recency still moves; rollup fields stay frozen.
      await this.masters.update(masterId, { last_activity_at: new Date() });
      return (await this.masters.findById(masterId))!;
    }

    const rollup = await this.masters.deriveRollup(masterId);
    await this.masters.update(masterId, {
      ...rollup,
      last_activity_at: new Date(),
    });
    return (await this.masters.findById(masterId))!;
  }

  /**
   * Admin places a master on HOLD. Pauses all auto-delta paths on this
   * booking until released. Reason is mandatory (DB CHECK constraint).
   * Existing children remain individually settle-able by admin — HOLD
   * doesn't block manual finance work.
   */
  async placeOnHold(masterId: string, reason: string): Promise<BookingSettlementMasterEntity> {
    const master = await this.masters.findById(masterId);
    if (!master) throw new NotFoundException('Settlement master not found');
    const cleaned = (reason || '').trim();
    if (!cleaned) throw new BadRequestException('reason is required');
    if (master.overall_status === OverallSettlementStatus.HOLD) {
      throw new BadRequestException('Master is already on hold');
    }

    const userId = this.cls.get<string>(CLS_USER_ID) || null;
    await this.masters.update(masterId, {
      overall_status: OverallSettlementStatus.HOLD,
      hold_reason: cleaned,
      hold_set_by: userId,
      hold_set_at: new Date(),
      last_activity_at: new Date(),
    });
    return (await this.masters.findById(masterId))!;
  }

  /**
   * Admin releases a held master. Clears the hold_* stamps, then
   * recomputes status from current children. Auto-deltas that would
   * have landed while held are NOT back-filled — they need re-triggering
   * by the operator (re-save the itinerary edit). Deliberate: silent
   * retroactive insertion would audit-trail the operator for a change
   * they didn't make.
   */
  async releaseHold(masterId: string): Promise<BookingSettlementMasterEntity> {
    const master = await this.masters.findById(masterId);
    if (!master) throw new NotFoundException('Settlement master not found');
    if (master.overall_status !== OverallSettlementStatus.HOLD) {
      throw new BadRequestException('Master is not on hold');
    }

    await this.masters.update(masterId, {
      hold_reason: null,
      hold_set_by: null,
      hold_set_at: null,
    });
    await this.recomputeMaster(masterId);
    return (await this.masters.findById(masterId))!;
  }

  /** Master list — paginated for the Settlements admin page. */
  async findMasterPaginated(query: import('./dto/master-list-query.dto').MasterListQueryDto) {
    return this.masters.findPaginatedList(query);
  }

  /** Master detail — master + children for the detail page. */
  async findMasterDetail(id: string) {
    const master = await this.masters.findDetailById(id);
    if (!master) return null;
    const children = await this.settlements.find({
      where: { master_id: id },
      // sales_agent → name shown as "Sold by X" pill on the detail page
      // header. All children on a single master share the same agent
      // (snapshotted from the booking's query at create time) so the
      // FE just reads the first child's value.
      relations: { currency: true, supplier: true, sales_agent: true },
      order: { created_at: 'ASC' },
    });
    return { master, children };
  }

  /**
   * Guard helper — throws if the booking's master is currently HOLD.
   * Called by every auto-delta path BEFORE writing a child row so a
   * held master never accumulates silent mutations. The first write
   * for a booking has no master yet (ensureMaster runs in the same
   * call) — returns silently in that case.
   */
  private async assertMasterNotOnHold(booking_id: string): Promise<void> {
    const existing = await this.masters.findActiveByBooking(booking_id);
    if (existing && existing.overall_status === OverallSettlementStatus.HOLD) {
      throw new ForbiddenException(
        `Settlement master for booking is on HOLD — release the hold before raising further settlements`,
      );
    }
  }

  /**
   * Snapshot the sales agent for the booking at settlement-create time.
   * Read from `query.assigned_to` via the booking's query relation.
   * Returns null when the booking has no linked query (defensive — the
   * settlement still records, just without agent attribution).
   *
   * Frozen on the child row so commission attribution doesn't shift if
   * the query is later reassigned. Cached lookup per-call so multiple
   * settlement creates on the same booking save (rare) don't re-query.
   */
  private async snapshotSalesAgent(booking_id: string): Promise<string | null> {
    // Primary sales agent = first user in the query's `sales_team`
    // JSONB array. Multi-agent splits are a future commission concern
    // and don't affect the settlement-level attribution snapshot.
    const rows = (await this.settlements.manager.query(
      `SELECT (q.sales_team ->> 0)::uuid AS agent_id
         FROM bookings b
         JOIN queries q ON q.id = b.query_id
        WHERE b.id = $1
          AND jsonb_array_length(COALESCE(q.sales_team, '[]'::jsonb)) > 0
        LIMIT 1`,
      [booking_id],
    )) as Array<{ agent_id: string | null }> | undefined;
    return rows?.[0]?.agent_id ?? null;
  }

  // ════════════════════════════════════════════════════════════════════
  // Payment Collection (Phase A — 2026-06-13)
  //
  // Customer-centric flow: ops opens the Payment Collection screen,
  // picks a customer (guest_mobile), enters a single received amount +
  // mode + ref, then allocates across N unsettled settlements (one
  // amount per row in the outstanding grid). Submitting creates ONE
  // payment_collections row + N settlement_payments rows under a
  // shared receipt_no.
  //
  // Stakeholder constraints (locked 2026-06-13):
  //   - Customer key  = bookings.guest_mobile (no customer master)
  //   - Currency      = same-currency only per collection event
  //   - Excess        = stored on the collection row as `excess_amount`
  //                     + `excess_treatment` ('CREDIT'|'ADVANCE'). The
  //                     credit-ledger drawdown is Phase D.
  //
  // Reuses recordPayment's invariants per allocation (outstanding
  // guard, settlement-flip, master + booking rollup) but skips its
  // receipt-allocation since the receipt belongs to the collection.
  // ════════════════════════════════════════════════════════════════════

  /**
   * Outstanding for a customer, aggregated per BOOKING (bookings is the
   * anchor table), optionally filtered to a single currency. Powers the
   * Collect Payment grid. One row per (booking, currency) with the
   * still-open per-line settlements carried in `lines` — see
   * {@link buildCollectOutstandingByBookingSql}.
   *
   * Match policy: bookings.guest_mobile is canonicalised to the raw
   * string the caller provides — no normalisation here. The FE
   * autocomplete is responsible for handing us back the exact value
   * stored on the booking.
   *
   * Returns CUSTOMER-side COLLECTION only (refunds and supplier-side
   * payouts have their own surfaces). Fully-settled bookings drop out
   * via the HAVING clause.
   */
  async findCustomerOutstanding(input: {
    mobile: string;
    currency_id?: string;
    exclude_collection_id?: string;
  }): Promise<CollectOutstandingBookingRow[]> {
    const tenantId = this.settlements.currentTenantId();
    const params: unknown[] = [
      tenantId,
      input.mobile,
      input.exclude_collection_id || NIL_COLLECTION_ID,
    ];
    let currencyFilter = '';
    if (input.currency_id) {
      params.push(input.currency_id);
      currencyFilter = `AND ps.currency_id = $${params.length}`;
    }
    const rows = (await this.settlements.manager.query(
      buildCollectOutstandingByBookingSql('q.guest_mobile', currencyFilter),
      params,
    )) as Array<Record<string, unknown>>;
    return rows.map(mapCollectOutstandingBookingRow);
  }

  /**
   * One Payment Collection screen submission.
   *
   * Validates the entire payload before writing — sum, per-allocation
   * outstanding, currency match, customer match, excess treatment. The
   * write happens inside a single transaction so a half-applied
   * collection can't leave the ledger inconsistent.
   *
   * Receipt: ONE receipt_no allocated under the RCP prefix; every
   * child settlement_payment inherits the same number so a printed
   * receipt covers the whole allocation. The collection row stores
   * its own copy for fast retrieval on the receipt page.
   */
  /**
   * Validate a Collect Payment allocation without writing — used by
   * Collect-for-MD so the invoice is not hit until MD confirm.
   * Allocations must equal received exactly (no leftover / no credit).
   */
  async assertExactCollectAllocations(input: {
    party_type?: CollectionPartyType;
    customer_mobile?: string | null;
    source_id?: string | null;
    customer_name?: string | null;
    payment_date: string;
    mode: SettlementPaymentMode;
    currency_id: string;
    received_amount: number;
    allocations: Array<{ settlement_id: string; amount: number }>;
  }): Promise<void> {
    const partyType = input.party_type ?? CollectionPartyType.CUSTOMER;
    if (partyType === CollectionPartyType.CUSTOMER) {
      if (!input.customer_mobile) {
        throw new BadRequestException('customer_mobile is required when party_type = CUSTOMER');
      }
    } else if (!input.source_id) {
      throw new BadRequestException('source_id is required when party_type = SOURCE');
    }
    const received = Number(input.received_amount);
    if (!Number.isFinite(received) || received <= 0) {
      throw new BadRequestException('received_amount must be greater than 0');
    }
    let allocatedTotal = 0;
    for (const a of input.allocations) {
      if (!Number.isFinite(a.amount) || a.amount <= 0) {
        throw new BadRequestException('Allocation amounts must be greater than 0');
      }
      allocatedTotal += Number(a.amount);
    }
    if (Math.abs(allocatedTotal - received) > 0.005) {
      throw new BadRequestException(
        `Adjust total (${allocatedTotal.toFixed(2)}) must equal Received (${received.toFixed(2)})`,
      );
    }
    if (input.allocations.length === 0) {
      throw new BadRequestException('At least one allocation is required');
    }
    for (const a of input.allocations) {
      const settlement = await this.settlements.findById(a.settlement_id);
      if (!settlement) {
        throw new NotFoundException(`Settlement ${a.settlement_id} not found`);
      }
      if (settlement.status === SettlementStatus.SETTLED) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is already settled`);
      }
      if (settlement.party_type !== SettlementParty.CUSTOMER) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not a customer-side settlement`,
        );
      }
      if (settlement.type !== SettlementType.COLLECTION) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is not a COLLECTION`);
      }
      if (settlement.currency_id !== input.currency_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} currency does not match the collection currency`,
        );
      }
      const outstanding = Number(settlement.amount) - Number(settlement.amount_settled);
      if (Number(a.amount) > outstanding + 0.005) {
        throw new BadRequestException(
          `Allocation for settlement ${a.settlement_id} exceeds outstanding`,
        );
      }
      const booking = await this.bookingRepo.findOne({
        where: { id: settlement.booking_id },
        select: { id: true, query_id: true } as never,
      });
      if (!booking?.query_id) {
        throw new BadRequestException(`Settlement ${a.settlement_id} has no parent query`);
      }
      const parent = await this.queryRepo.findOne({
        where: { id: booking.query_id },
        select: { id: true, guest_mobile: true, source_id: true } as never,
      });
      if (!parent) {
        throw new BadRequestException(`Settlement ${a.settlement_id} has no parent query`);
      }
      if (partyType === CollectionPartyType.CUSTOMER) {
        if (parent.guest_mobile !== input.customer_mobile) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} does not belong to customer ${input.customer_mobile}`,
          );
        }
      } else if (parent.source_id !== input.source_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not channelled via the selected agent`,
        );
      }
    }
  }

  /**
   * Debit/credit bank accounts for Collect Payment and Pay Supplier.
   * Every payment mode accepts debit only, credit only, both, or neither.
   * When both are set they must be distinct, active, and tenant-scoped.
   */
  async resolveCollectBankAccountIds(input: {
    mode: SettlementPaymentMode;
    debit_bank_account_id?: string | null;
    credit_bank_account_id?: string | null;
  }): Promise<{
    debit_bank_account_id: string | null;
    credit_bank_account_id: string | null;
  }> {
    const debitId = input.debit_bank_account_id ?? null;
    const creditId = input.credit_bank_account_id ?? null;
    if (debitId && creditId && debitId === creditId) {
      throw new BadRequestException('Debit and credit must use different bank accounts');
    }
    await Promise.all([
      debitId ? this.bankAccounts.requireActive(debitId) : Promise.resolve(null),
      creditId ? this.bankAccounts.requireActive(creditId) : Promise.resolve(null),
    ]);
    return { debit_bank_account_id: debitId, credit_bank_account_id: creditId };
  }

  async collectPayment(input: {
    /** Defaults to CUSTOMER. SOURCE means an agent / trip source is
     *  wiring on behalf of customers — credit ledger keys differently
     *  and outstanding rows are scoped by `query.source_id` instead of
     *  guest_mobile. */
    party_type?: CollectionPartyType;
    customer_mobile?: string | null;
    /** Required when party_type='SOURCE'. */
    source_id?: string | null;
    customer_name?: string | null;
    payment_date: string;
    /** Defaults to BILL_TO_BILL. ON_ACCOUNT is a deliberate deposit:
     *  no allocations, whole amount onto the party ledger. */
    payment_type?: SettlementPaymentType | null;
    mode: SettlementPaymentMode;
    ref_no?: string | null;
    debit_bank_account_id?: string | null;
    credit_bank_account_id?: string | null;
    currency_id: string;
    received_amount: number;
    receive_currency_id?: string | null;
    exchange_rate?: number | null;
    exchange_rate_quote?: number | null;
    received_amount_receive_currency?: number | null;
    roe?: number | null;
    /** §12d Gap #2 — drawdown from the party's credit ledger applied
     *  alongside the cash. Defaults to 0. Must be ≤ the party's
     *  current ledger balance in the chosen currency. */
    credit_applied?: number | null;
    allocations: Array<{ settlement_id: string; amount: number }>;
    excess_treatment?: CollectionExcessTreatment | null;
    notes?: string | null;
    collected_by_type?: 'OWN' | 'SUPPLIER' | null;
    collector_name?: string | null;
    collector_supplier_id?: string | null;
    collector_driver_id?: string | null;
  }): Promise<{
    collection: PaymentCollectionEntity;
    payments: SettlementPaymentEntity[];
  }> {
    // ── Party normalisation ─────────────────────────────────────
    const partyType = input.party_type ?? CollectionPartyType.CUSTOMER;
    if (partyType === CollectionPartyType.CUSTOMER) {
      if (!input.customer_mobile) {
        throw new BadRequestException('customer_mobile is required when party_type = CUSTOMER');
      }
    } else {
      if (!input.source_id) {
        throw new BadRequestException('source_id is required when party_type = SOURCE');
      }
    }
    const received = Number(input.received_amount);
    if (!Number.isFinite(received) || received <= 0) {
      throw new BadRequestException('received_amount must be greater than 0');
    }
    const creditApplied = Number(input.credit_applied ?? 0);
    if (!Number.isFinite(creditApplied) || creditApplied < 0) {
      throw new BadRequestException('credit_applied must be a non-negative number');
    }
    const paymentType = input.payment_type ?? SettlementPaymentType.BILL_TO_BILL;
    if (paymentType === SettlementPaymentType.ON_ACCOUNT && input.allocations.length > 0) {
      throw new BadRequestException(
        'On Account receipts cannot allocate against invoices — switch to Bill to Bill',
      );
    }

    let allocatedTotal = 0;
    for (const a of input.allocations) {
      if (!Number.isFinite(a.amount) || a.amount <= 0) {
        throw new BadRequestException('Allocation amounts must be greater than 0');
      }
      allocatedTotal += Number(a.amount);
    }
    // Allocation cap = cash received + credit drawdown. 0.005 fudge
    // matches recordPayment's decimal-display tolerance.
    const moneyIn = +(received + creditApplied).toFixed(2);
    if (allocatedTotal > moneyIn + 0.005) {
      throw new BadRequestException(
        `Sum of allocations (${allocatedTotal.toFixed(2)}) exceeds received + credit (${moneyIn.toFixed(2)})`,
      );
    }
    const excess = +(moneyIn - allocatedTotal).toFixed(2);
    if (excess > 0.005 && !input.excess_treatment) {
      throw new BadRequestException(
        `Received + credit exceeds allocated by ${excess.toFixed(2)} — choose how to treat the excess (CREDIT or ADVANCE)`,
      );
    }
    if (input.allocations.length === 0 && !input.excess_treatment) {
      throw new BadRequestException(
        'No allocations provided — choose an excess treatment (CREDIT or ADVANCE) to record as advance',
      );
    }

    const bankSides = await this.resolveCollectBankAccountIds({
      mode: input.mode,
      debit_bank_account_id: input.debit_bank_account_id,
      credit_bank_account_id: input.credit_bank_account_id,
    });

    // Pre-resolve the party's ledger key so we can both check balance
    // before draining (DEBIT path) AND reuse the same lookup at the
    // bottom for the CREDIT path. Customer mobiles are normalised to
    // digits-only so "+91 98765-43210" and "+919876543210" hit the
    // same ledger row. Audit finding P1-#3.
    const ledgerPartyType: CreditLedgerPartyType =
      partyType === CollectionPartyType.CUSTOMER
        ? CreditLedgerPartyType.CUSTOMER
        : CreditLedgerPartyType.SOURCE;
    const partyKey =
      partyType === CollectionPartyType.CUSTOMER
        ? normaliseMobileForLedger(input.customer_mobile!)
        : input.source_id!;
    if (creditApplied > 0.005) {
      const ledger = await this.creditLedger.findByParty(
        ledgerPartyType,
        partyKey,
        input.currency_id,
      );
      const available = Number(ledger?.balance ?? 0);
      if (creditApplied > available + 0.005) {
        throw new BadRequestException(
          `credit_applied (${creditApplied.toFixed(2)}) exceeds available credit (${available.toFixed(2)})`,
        );
      }
    }

    // Pre-fetch all target settlements to validate before writing.
    // Tenant scoping is auto via TenantAwareRepository.find.
    const settlementMap = new Map<string, PaymentSettlementEntity>();
    for (const a of input.allocations) {
      const settlement = await this.settlements.findById(a.settlement_id);
      if (!settlement) {
        throw new NotFoundException(`Settlement ${a.settlement_id} not found`);
      }
      if (settlement.status === SettlementStatus.SETTLED) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is already settled`);
      }
      if (settlement.party_type !== SettlementParty.CUSTOMER) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not a customer-side settlement`,
        );
      }
      if (settlement.type !== SettlementType.COLLECTION) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not a COLLECTION (refunds go through the refund flow)`,
        );
      }
      if (settlement.currency_id !== input.currency_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} currency does not match the collection currency`,
        );
      }
      const outstanding = Number(settlement.amount) - Number(settlement.amount_settled);
      if (Number(a.amount) > outstanding + 0.005) {
        throw new BadRequestException(
          `Allocation for settlement ${a.settlement_id} (${Number(a.amount).toFixed(2)}) exceeds outstanding (${outstanding.toFixed(2)})`,
        );
      }
      // Party-match check via booking → query. Guest + source fields
      // live on queries; bookings just FK to the parent query.
      //   CUSTOMER mode → q.guest_mobile must match input.customer_mobile
      //   SOURCE mode   → q.source_id must match input.source_id
      // Server-side check so an FE-spoofed allocation can't move money
      // between unrelated parties.
      const booking = await this.bookingRepo.findOne({
        where: { id: settlement.booking_id },
        select: { id: true, query_id: true } as any,
      });
      if (!booking?.query_id) {
        throw new BadRequestException(`Settlement ${a.settlement_id} has no parent query`);
      }
      const parent = await this.queryRepo.findOne({
        where: { id: booking.query_id },
        select: { id: true, guest_mobile: true, source_id: true } as any,
      });
      if (!parent) {
        throw new BadRequestException(`Settlement ${a.settlement_id} has no parent query`);
      }
      if (partyType === CollectionPartyType.CUSTOMER) {
        if (parent.guest_mobile !== input.customer_mobile) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} does not belong to customer ${input.customer_mobile}`,
          );
        }
      } else {
        if (parent.source_id !== input.source_id) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} is not channelled via the selected agent`,
          );
        }
      }
      settlementMap.set(a.settlement_id, settlement);
    }

    // ── Receipt allocation ───────────────────────────────────────
    // One receipt_no for the whole collection. Year derived from
    // paid_date so back-dated payments land in the correct bucket.
    const year = (() => {
      const m = /^(\d{4})/.exec(input.payment_date);
      return m ? Number(m[1]) : new Date(input.payment_date).getUTCFullYear();
    })();
    const { receipt_no, receipt_year } = await allocateReceiptNumber(
      this.collections.manager,
      this.collections.currentTenantId(),
      year,
      'RCP',
    );

    // ── Persist (single transaction) ─────────────────────────────
    const written = await this.collections.manager.transaction(async () => {
      // The TenantAwareRepository writes pick tenant_id + created_by/
      // updated_by from CLS automatically, so we don't pass them here.
      const collection = await this.collections.save({
        receipt_no,
        receipt_year,
        party_type: partyType,
        customer_mobile: partyType === CollectionPartyType.CUSTOMER ? input.customer_mobile! : null,
        source_id: partyType === CollectionPartyType.SOURCE ? input.source_id! : null,
        customer_name: input.customer_name ?? null,
        payment_date: input.payment_date,
        payment_type: paymentType,
        mode: input.mode,
        ref_no: input.ref_no ?? null,
        debit_bank_account_id: bankSides.debit_bank_account_id,
        credit_bank_account_id: bankSides.credit_bank_account_id,
        currency_id: input.currency_id,
        received_amount: received,
        receive_currency_id: input.receive_currency_id ?? null,
        exchange_rate:
          input.exchange_rate != null && Number.isFinite(Number(input.exchange_rate))
            ? Number(input.exchange_rate)
            : null,
        exchange_rate_quote:
          input.exchange_rate_quote != null && Number.isFinite(Number(input.exchange_rate_quote))
            ? Number(input.exchange_rate_quote)
            : null,
        received_amount_receive_currency:
          input.received_amount_receive_currency != null &&
          Number.isFinite(Number(input.received_amount_receive_currency))
            ? Number(input.received_amount_receive_currency)
            : null,
        roe: input.roe != null && Number.isFinite(Number(input.roe)) ? Number(input.roe) : null,
        credit_applied: creditApplied > 0.005 ? creditApplied : 0,
        allocated_amount: allocatedTotal,
        excess_amount: excess > 0.005 ? excess : 0,
        excess_treatment: excess > 0.005 ? (input.excess_treatment ?? null) : null,
        notes: input.notes ?? null,
        collected_by_type: input.collected_by_type ?? null,
        collector_name: input.collector_name ?? null,
        collector_supplier_id: input.collector_supplier_id ?? null,
        collector_driver_id: input.collector_driver_id ?? null,
      });

      const payments: SettlementPaymentEntity[] = [];
      const touchedBookings = new Set<string>();
      const touchedMasters = new Set<string>();
      for (const a of input.allocations) {
        const settlement = settlementMap.get(a.settlement_id)!;
        const taxSnapshot = await this.resolvePaymentSnapshot(settlement);
        const payment = await this.payments.save({
          settlement_id: a.settlement_id,
          paid_amount: Number(a.amount),
          paid_date: input.payment_date,
          mode: input.mode,
          ref_no: input.ref_no ?? null,
          notes: input.notes ?? null,
          receipt_no,
          receipt_year,
          tax_snapshot: taxSnapshot,
          collection_id: collection.id,
        });
        // Audit P1-#6: atomic conditional UPDATE — the headroom check
        // (amount - amount_settled >= applied) is in the WHERE clause
        // so two concurrent collects against the same settlement can't
        // both succeed past the cap. The second one matches zero rows
        // and we throw, rolling back the tx (including this payment row).
        // Audit P1-#6: atomic conditional UPDATE. Status literals
        // need an explicit cast to the Postgres enum type because
        // CASE expressions don't auto-coerce — otherwise the write
        // fails with "is of type X but expression is of type text".
        const settlementUpdateRows = rowsFromUpdateReturning<{ amount_settled: string; status: string }>(
          await this.collections.manager.query(
            `UPDATE payment_settlements
                SET amount_settled = LEAST(amount, amount_settled + $1::numeric),
                    status = CASE
                      WHEN amount_settled + $1::numeric >= amount - 0.005 THEN 'SETTLED'::payment_settlements_status_enum
                      ELSE 'PARTIAL'::payment_settlements_status_enum
                    END,
                    settled_date = CASE
                      WHEN amount_settled + $1::numeric >= amount - 0.005 THEN $4::date
                      ELSE NULL
                    END,
                    updated_at = now()
              WHERE id = $2
                AND tenant_id = $3
                AND amount - amount_settled >= $1::numeric - 0.005
            RETURNING amount_settled, status`,
            [
              Number(a.amount),
              a.settlement_id,
              this.collections.currentTenantId(),
              input.payment_date,
            ],
          ),
        );
        if (settlementUpdateRows.length === 0) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} was concurrently updated — outstanding no longer covers ${Number(a.amount).toFixed(2)}. Reload and retry.`,
          );
        }
        payments.push(payment);
        touchedBookings.add(settlement.booking_id);
        if (settlement.master_id) touchedMasters.add(settlement.master_id);
      }

      // Recompute booking + master rollups once per touched booking /
      // master, not per allocation — same booking with N allocations
      // would otherwise rerun the same UPDATE N times.
      for (const bookingId of touchedBookings) {
        await this.recomputeBookingPaymentStatus(bookingId);
      }
      for (const masterId of touchedMasters) {
        await this.recomputeMaster(masterId);
      }

      // ── Credit-ledger DEBIT path (credit_applied > 0) ─────────
      // Audit P1-#4: atomic conditional UPDATE — the balance check is
      // baked into the WHERE clause so two concurrent collects can't
      // both drain the same row. Postgres serialises the UPDATEs and
      // the second one matches zero rows when the first wins.
      if (creditApplied > 0.005) {
        const ledgerDebitRows = rowsFromUpdateReturning<{ id: string; balance: string }>(
          await this.collections.manager.query(
            `UPDATE customer_credit_ledger
                SET balance = balance - $1::numeric,
                    last_collection_id = $2,
                    updated_at = now()
              WHERE tenant_id = $3
                AND party_type = $4
                AND party_key = $5
                AND currency_id = $6
                AND is_deleted = false
                AND balance >= $1::numeric - 0.005
            RETURNING id, balance`,
            [
              creditApplied,
              collection.id,
              this.collections.currentTenantId(),
              ledgerPartyType,
              partyKey,
              input.currency_id,
            ],
          ),
        );
        if (ledgerDebitRows.length === 0) {
          throw new BadRequestException(
            `Credit drained concurrently or absent — requested ${creditApplied.toFixed(2)} could not be deducted. Reload and retry.`,
          );
        }
        const { id: ledgerId, balance: balanceStr } = ledgerDebitRows[0];
        const balanceAfter = +Number(balanceStr).toFixed(2);
        await this.collections.manager.query(
          `INSERT INTO customer_credit_ledger_movements
             (tenant_id, ledger_id, direction, amount, balance_after,
              collection_id, created_by)
           VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [
            this.collections.currentTenantId(),
            ledgerId,
            CreditLedgerDirection.DEBIT,
            creditApplied,
            balanceAfter,
            collection.id,
            this.cls.get<string>(CLS_USER_ID) ?? null,
          ],
        );
      }

      // ── Credit-ledger CREDIT path (excess + treatment=CREDIT) ─
      // ADVANCE treatment doesn't touch the ledger — that bucket is
      // tracked on the collection row alone for legacy compatibility.
      // CREDIT mirrors excess onto the party's ledger so the next
      // receipt can auto-apply it via the DEBIT path above.
      if (excess > 0.005 && input.excess_treatment === CollectionExcessTreatment.CREDIT) {
        // Snapshot the display label for the ledger header. Customer
        // gets the receipt name (or mobile fallback) — keep it stable
        // even if the guest later changes their name on a future query.
        // Agent gets the trip_source.name resolved here so we don't
        // re-query at read time.
        let partyLabel: string;
        if (partyType === CollectionPartyType.CUSTOMER) {
          partyLabel = input.customer_name?.trim() || input.customer_mobile!;
        } else {
          const src = await this.collections.manager.findOne(TripSourceEntity, {
            where: { id: input.source_id! } as any,
            select: { id: true, name: true } as any,
          });
          partyLabel = src?.name ?? 'Agent';
        }

        const existing = await this.creditLedger.findByParty(
          ledgerPartyType,
          partyKey,
          input.currency_id,
        );
        let ledgerId: string;
        let balanceAfter: number;
        if (existing) {
          balanceAfter = +(Number(existing.balance) + excess).toFixed(2);
          await this.creditLedger.update(existing.id, {
            balance: balanceAfter,
            party_label: partyLabel,
            last_collection_id: collection.id,
          });
          ledgerId = existing.id;
        } else {
          const fresh = await this.creditLedger.save({
            party_type: ledgerPartyType,
            party_key: partyKey,
            party_label: partyLabel,
            currency_id: input.currency_id,
            balance: excess,
            last_collection_id: collection.id,
          });
          ledgerId = fresh.id;
          balanceAfter = excess;
        }
        // Movement row is the immutable audit trail. Insert via raw
        // SQL so tenant_id is set explicitly (the entity skips
        // BaseEntity's CLS auto-fill since the table has no
        // updated_at / is_deleted columns).
        await this.collections.manager.query(
          `INSERT INTO customer_credit_ledger_movements
             (tenant_id, ledger_id, direction, amount, balance_after,
              collection_id, created_by)
           VALUES ($1, $2, $3, $4, $5, $6, $7)`,
          [
            this.collections.currentTenantId(),
            ledgerId,
            CreditLedgerDirection.CREDIT,
            excess,
            balanceAfter,
            collection.id,
            this.cls.get<string>(CLS_USER_ID) ?? null,
          ],
        );
      }

      return { collection, payments };
    });

    return written;
  }

  /**
   * Single collection with its child allocations. Powers the receipt
   * page and the audit drill-down. Returns flat row shapes — the FE
   * (or controller) decorates with joins it needs.
   *
   * Allocation rows are enriched with booking_number, service_type,
   * and travel_date so the receipt template can render a readable
   * "what was paid for" column without per-row joins on the FE.
   */
  async findCollectionDetail(id: string): Promise<{
    collection: PaymentCollectionEntity & {
      source_name?: string | null;
      debit_bank_name?: string | null;
      debit_account_name?: string | null;
      credit_bank_name?: string | null;
      credit_account_name?: string | null;
    };
    payments: Array<
      SettlementPaymentEntity & {
        booking_number: string | null;
        source_item_type: string | null;
        service_name: string | null;
        travel_date: string | null;
        guest_name: string | null;
      }
    >;
  } | null> {
    const collection = await this.collections.findById(id);
    if (!collection) return null;
    // Decorate with the agent's display name on SOURCE-mode receipts
    // so the FE can render "Received From <agent>" without a second
    // round-trip. Customer receipts already carry customer_name on
    // the entity itself.
    let sourceName: string | null = null;
    if (collection.party_type === CollectionPartyType.SOURCE && collection.source_id) {
      const src = await this.collections.manager.findOne(TripSourceEntity, {
        where: { id: collection.source_id } as any,
        select: { id: true, name: true } as any,
      });
      sourceName = src?.name ?? null;
    }
    const [debitAcc, creditAcc] = await Promise.all([
      collection.debit_bank_account_id
        ? this.bankAccounts.findByIdWithBankOrNull(collection.debit_bank_account_id)
        : Promise.resolve(null),
      collection.credit_bank_account_id
        ? this.bankAccounts.findByIdWithBankOrNull(collection.credit_bank_account_id)
        : Promise.resolve(null),
    ]);
    // Resolve a human-readable service_name for the receipt's Service
    // column. UAT 2026-06-30: receipt was rendering the raw enum
    // ('booking_transport_item') because the FE had nothing else to
    // print. Branch on source_item_type and LEFT JOIN to the matching
    // line + master:
    //   transport → 'from_city → to_city'
    //   activity  → activities.name
    //   hotel     → hotels.name
    //   special   → booking_special_items.service_name (free-text)
    //   NULL/other → 'Booking total' (INITIAL row covers the whole
    //                booking, not a specific line)
    const rows = (await this.payments.manager.query(
      `SELECT sp.id, sp.settlement_id, sp.paid_amount, sp.paid_date,
              sp.mode, sp.ref_no, sp.notes, sp.receipt_no, sp.receipt_year,
              sp.is_void, sp.collection_id, sp.created_at,
              ps.booking_id AS booking_id,
              ps.source_item_type AS source_item_type,
              b.booking_number AS booking_number,
              q.start_date AS travel_date,
              q.guest_name AS guest_name,
              CASE ps.source_item_type
                WHEN 'booking_transport_item' THEN COALESCE(ts.from_city || ' → ' || ts.to_city, 'Transport')
                WHEN 'booking_activity_item' THEN COALESCE(act.name, 'Activity')
                WHEN 'booking_hotel_item' THEN COALESCE(htl.name, 'Hotel')
                WHEN 'booking_special_item' THEN COALESCE(NULLIF(TRIM(spi.service_name), ''), 'Special')
                ELSE 'Booking total'
              END AS service_name
         FROM settlement_payments sp
         INNER JOIN payment_settlements ps ON ps.id = sp.settlement_id
         INNER JOIN bookings b ON b.id = ps.booking_id
         LEFT JOIN queries q ON q.id = b.query_id
         LEFT JOIN booking_transport_items bti
                ON ps.source_item_type = 'booking_transport_item'
               AND bti.id = ps.source_item_id
         LEFT JOIN transport_services ts ON ts.id = bti.transport_service_id
         LEFT JOIN booking_activity_items bai
                ON ps.source_item_type = 'booking_activity_item'
               AND bai.id = ps.source_item_id
         LEFT JOIN activities act ON act.id = bai.activity_id
         LEFT JOIN booking_hotel_items bhi
                ON ps.source_item_type = 'booking_hotel_item'
               AND bhi.id = ps.source_item_id
         LEFT JOIN hotels htl ON htl.id = bhi.hotel_id
         LEFT JOIN booking_special_items spi
                ON ps.source_item_type = 'booking_special_item'
               AND spi.id = ps.source_item_id
        WHERE sp.tenant_id = $1
          AND sp.collection_id = $2
          AND sp.is_deleted = false
        ORDER BY sp.created_at ASC`,
      [this.payments.currentTenantId(), id],
    )) as Array<Record<string, unknown>>;
    const payments = rows.map((r) => ({
      ...(r as any),
      paid_amount: Number(r.paid_amount),
    }));
    return {
      collection: Object.assign(collection, {
        source_name: sourceName,
        debit_bank_name: debitAcc?.bank?.name ?? null,
        debit_account_name: debitAcc?.account_name ?? null,
        credit_bank_name: creditAcc?.bank?.name ?? null,
        credit_account_name: creditAcc?.account_name ?? null,
      }),
      payments,
    };
  }

  /**
   * Customer balance rollup across all CUSTOMER COLLECTION rows for
   * this mobile. Header KPI on the Collection screen + Phase D's
   * Customer Outstanding Report.
   *
   *   expected  — sum of payment_settlements.amount (active COLLECTIONS)
   *   received  — sum of amount_settled
   *   balance   — expected − received (>0 = customer owes)
   *   credit    — sum of payment_collections.excess_amount for this
   *               customer where excess_treatment is set. Phase D
   *               will draw from this on the next collection; for now
   *               it surfaces "this customer has X in credit on file".
   */
  async findCustomerBalance(input: { mobile: string; currency_id?: string }): Promise<{
    mobile: string;
    currency_id: string | null;
    expected: number;
    received: number;
    balance: number;
    credit: number;
  }> {
    const tenantId = this.settlements.currentTenantId();
    const settParams: unknown[] = [tenantId, input.mobile];
    let settCurrency = '';
    if (input.currency_id) {
      settParams.push(input.currency_id);
      settCurrency = `AND ps.currency_id = $${settParams.length}`;
    }
    const settRows = (await this.settlements.manager.query(
      `SELECT COALESCE(SUM(ps.amount), 0) AS expected,
              COALESCE(SUM(ps.amount_settled), 0) AS received
         FROM payment_settlements ps
         INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false
         INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        WHERE ps.tenant_id = $1
          AND ps.is_deleted = false
          AND ps.party_type = 'CUSTOMER'
          AND ps.type = 'COLLECTION'
          AND q.guest_mobile = $2
          ${settCurrency}`,
      settParams,
    )) as Array<{ expected: string; received: string }>;

    // Audit P0-#1 / P0-#2: credit comes from customer_credit_ledger,
    // NOT a SUM of historical excess_amounts on payment_collections.
    // The ledger correctly nets CREDIT (excess parked in) against
    // DEBIT (drawn down on subsequent receipts), and excludes ADVANCE
    // treatments which are not drawdownable. Key is the digits-only
    // normalised mobile so format variations resolve to one row.
    const partyKey = normaliseMobileForLedger(input.mobile);
    const credParams: unknown[] = [tenantId, partyKey];
    let credCurrency = '';
    if (input.currency_id) {
      credParams.push(input.currency_id);
      credCurrency = `AND ccl.currency_id = $${credParams.length}`;
    }
    const credRows = (await this.collections.manager.query(
      `SELECT COALESCE(SUM(ccl.balance), 0) AS credit
         FROM customer_credit_ledger ccl
        WHERE ccl.tenant_id = $1
          AND ccl.is_deleted = false
          AND ccl.party_type = 'CUSTOMER'
          AND ccl.party_key = $2
          ${credCurrency}`,
      credParams,
    )) as Array<{ credit: string }>;

    const expected = Number(settRows[0]?.expected ?? 0);
    const received = Number(settRows[0]?.received ?? 0);
    const credit = Number(credRows[0]?.credit ?? 0);
    return {
      mobile: input.mobile,
      currency_id: input.currency_id ?? null,
      expected,
      received,
      balance: +(expected - received).toFixed(2),
      credit,
    };
  }

  /**
   * §12d — Customer-name autocomplete feed for the Payment Collection
   * screen. Returns distinct (guest_name, guest_mobile, guest_email)
   * triples from active queries whose guest_name matches the typed
   * substring. Mobile stays the lookup key for outstanding / balance,
   * so on select the FE silently fills mobile + email + uses the
   * existing endpoints.
   *
   * Cap at 20 results so a 2-character query doesn't return the whole
   * customer base. ORDER BY name keeps it deterministic across calls.
   */
  async searchCustomersByName(input: { q: string }): Promise<
    Array<{
      guest_name: string;
      guest_mobile: string;
      guest_email: string | null;
    }>
  > {
    const tenantId = this.settlements.currentTenantId();
    const rows = (await this.settlements.manager.query(
      `SELECT DISTINCT ON (q.guest_name, q.guest_mobile)
              q.guest_name AS guest_name,
              q.guest_mobile AS guest_mobile,
              q.guest_email AS guest_email
         FROM queries q
        WHERE q.tenant_id = $1
          AND q.is_deleted = false
          AND q.guest_name ILIKE $2
        ORDER BY q.guest_name ASC, q.guest_mobile ASC
        LIMIT 20`,
      [tenantId, `%${input.q}%`],
    )) as Array<Record<string, unknown>>;
    return rows.map((r) => ({
      guest_name: String(r.guest_name ?? ''),
      guest_mobile: String(r.guest_mobile ?? ''),
      guest_email: (r.guest_email as string) ?? null,
    }));
  }

  /**
   * §12d Agent mode — outstanding customer-side settlements for every
   * booking whose `query.source_id = :sourceId`. Same SQL shape as
   * `findCustomerOutstanding` with the guest_mobile filter swapped to
   * source_id. Read-only feed for the Payment Collection grid when
   * the screen's Party toggle is on "From Agent".
   */
  async findSourceOutstanding(input: {
    source_id: string;
    currency_id?: string;
    exclude_collection_id?: string;
  }): Promise<CollectOutstandingBookingRow[]> {
    const tenantId = this.settlements.currentTenantId();
    const params: unknown[] = [
      tenantId,
      input.source_id,
      input.exclude_collection_id || NIL_COLLECTION_ID,
    ];
    let currencyFilter = '';
    if (input.currency_id) {
      params.push(input.currency_id);
      currencyFilter = `AND ps.currency_id = $${params.length}`;
    }
    const rows = (await this.settlements.manager.query(
      buildCollectOutstandingByBookingSql('q.source_id', currencyFilter),
      params,
    )) as Array<Record<string, unknown>>;
    return rows.map(mapCollectOutstandingBookingRow);
  }

  /**
   * §12d Agent balance — total expected / received across every
   * booking sourced from this agent in the chosen currency, plus the
   * credit held on the agent's ledger row.
   *
   * Credit pulls from customer_credit_ledger keyed by
   * (party_type='SOURCE', party_key=source_id, currency_id). No row =
   * zero credit; we don't synthesise a header until the first excess.
   */
  async findSourceBalance(input: { source_id: string; currency_id?: string }): Promise<{
    source_id: string;
    currency_id: string | null;
    expected: number;
    received: number;
    balance: number;
    credit: number;
  }> {
    const tenantId = this.settlements.currentTenantId();
    const settParams: unknown[] = [tenantId, input.source_id];
    let settCurrency = '';
    if (input.currency_id) {
      settParams.push(input.currency_id);
      settCurrency = `AND ps.currency_id = $${settParams.length}`;
    }
    const settRows = (await this.settlements.manager.query(
      `SELECT COALESCE(SUM(ps.amount), 0) AS expected,
              COALESCE(SUM(ps.amount_settled), 0) AS received
         FROM payment_settlements ps
         INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false
         INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        WHERE ps.tenant_id = $1
          AND ps.is_deleted = false
          AND ps.party_type = 'CUSTOMER'
          AND ps.type = 'COLLECTION'
          AND q.source_id = $2
          ${settCurrency}`,
      settParams,
    )) as Array<{ expected: string; received: string }>;

    const credParams: unknown[] = [tenantId, input.source_id];
    let credCurrency = '';
    if (input.currency_id) {
      credParams.push(input.currency_id);
      credCurrency = `AND ccl.currency_id = $${credParams.length}`;
    }
    const credRows = (await this.settlements.manager.query(
      `SELECT COALESCE(SUM(ccl.balance), 0) AS credit
         FROM customer_credit_ledger ccl
        WHERE ccl.tenant_id = $1
          AND ccl.is_deleted = false
          AND ccl.party_type = 'SOURCE'
          AND ccl.party_key = $2
          ${credCurrency}`,
      credParams,
    )) as Array<{ credit: string }>;

    const expected = Number(settRows[0]?.expected ?? 0);
    const received = Number(settRows[0]?.received ?? 0);
    const credit = Number(credRows[0]?.credit ?? 0);
    return {
      source_id: input.source_id,
      currency_id: input.currency_id ?? null,
      expected,
      received,
      balance: +(expected - received).toFixed(2),
      credit,
    };
  }

  /**
   * §12e Pay Supplier — outstanding supplier-side settlements for one
   * supplier. Mirrors `findCustomerOutstanding` but party_type=SUPPLIER
   * and joined on `supplier_id` rather than guest_mobile. Powers the
   * Pay Supplier screen's grid.
   *
   * Type=REFUND on a SUPPLIER row means "money we owe the supplier"
   * (vendor payables). Settled = paid. We return rows where amount >
   * amount_settled so the FE can compute outstanding per line.
   */
  async findSupplierOutstanding(input: { supplier_id: string; currency_id?: string }): Promise<
    Array<{
      settlement_id: string;
      booking_id: string;
      booking_number: string | null;
      guest_name: string | null;
      service_type: string | null;
      source_item_type: string | null;
      // Actual date of the service line this payable is for (transport
      // leg date, activity date, hotel check-in, special date) — resolved
      // from the linked booking item, NOT the booking's travel-start.
      service_date: string | null;
      due_date: string;
      amount: number;
      amount_settled: number;
      outstanding: number;
      currency_id: string;
      currency_code: string;
      destination_name: string | null;
    }>
  > {
    const tenantId = this.settlements.currentTenantId();
    const params: unknown[] = [tenantId, input.supplier_id];
    let currencyFilter = '';
    if (input.currency_id) {
      params.push(input.currency_id);
      currencyFilter = `AND ps.currency_id = $${params.length}`;
    }
    // `source_item_type` stores the linked item's table name and
    // `source_item_id` its id, so LEFT-JOIN each item table on the id
    // (guarded by the type) and COALESCE their date columns down to one
    // service_date. Transport/activity/special expose `date`; hotels use
    // `check_in_date`.
    const rows = (await this.settlements.manager.query(
      `SELECT
          ps.id              AS settlement_id,
          ps.booking_id      AS booking_id,
          b.booking_number   AS booking_number,
          q.guest_name       AS guest_name,
          ps.source_item_type AS source_item_type,
          ps.due_date        AS due_date,
          ps.amount          AS amount,
          ps.amount_settled  AS amount_settled,
          (ps.amount - ps.amount_settled) AS outstanding,
          ps.currency_id     AS currency_id,
          c.code             AS currency_code,
          COALESCE(
            bti.date, bai.date, bhi.check_in_date, bsi.date,
            -- Fallback for payables whose original service line was
            -- deleted/replaced by a later itinerary edit (source_item_id
            -- orphaned): use the booking's current item of the same type
            -- (earliest date) as a best-effort service date.
            CASE ps.source_item_type
              WHEN 'booking_transport_item' THEN (
                SELECT MIN(x.date) FROM booking_transport_items x
                 WHERE x.booking_id = ps.booking_id AND x.is_deleted = false)
              WHEN 'booking_activity_item' THEN (
                SELECT MIN(x.date) FROM booking_activity_items x
                 WHERE x.booking_id = ps.booking_id AND x.is_deleted = false)
              WHEN 'booking_hotel_item' THEN (
                SELECT MIN(x.check_in_date) FROM booking_hotel_items x
                 WHERE x.booking_id = ps.booking_id AND x.is_deleted = false)
              WHEN 'booking_special_item' THEN (
                SELECT MIN(x.date) FROM booking_special_items x
                 WHERE x.booking_id = ps.booking_id AND x.is_deleted = false)
            END
          )                  AS service_date,
          d.name             AS destination_name,
          ps.source          AS service_type
        FROM payment_settlements ps
        INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false
        INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        LEFT JOIN currencies c ON c.id = ps.currency_id
        LEFT JOIN destinations d ON d.id = q.destination_id
        LEFT JOIN booking_transport_items bti
               ON bti.id = ps.source_item_id
              AND ps.source_item_type = 'booking_transport_item'
        LEFT JOIN booking_activity_items bai
               ON bai.id = ps.source_item_id
              AND ps.source_item_type = 'booking_activity_item'
        LEFT JOIN booking_hotel_items bhi
               ON bhi.id = ps.source_item_id
              AND ps.source_item_type = 'booking_hotel_item'
        LEFT JOIN booking_special_items bsi
               ON bsi.id = ps.source_item_id
              AND ps.source_item_type = 'booking_special_item'
       WHERE ps.tenant_id = $1
         AND ps.is_deleted = false
         AND ps.party_type = 'SUPPLIER'
         AND ps.type = 'REFUND'
         AND ps.status <> 'SETTLED'
         AND ps.amount > ps.amount_settled
         AND ps.supplier_id = $2
         ${currencyFilter}
       ORDER BY ps.due_date ASC, ps.created_at ASC`,
      params,
    )) as Array<Record<string, unknown>>;
    return rows.map((r) => ({
      settlement_id: String(r.settlement_id),
      booking_id: String(r.booking_id),
      booking_number: (r.booking_number as string) ?? null,
      guest_name: (r.guest_name as string) ?? null,
      service_type: (r.service_type as string) ?? null,
      source_item_type: (r.source_item_type as string) ?? null,
      service_date: (r.service_date as string) ?? null,
      due_date: String(r.due_date),
      amount: Number(r.amount),
      amount_settled: Number(r.amount_settled),
      outstanding: Number(r.outstanding),
      currency_id: String(r.currency_id),
      currency_code: (r.currency_code as string) ?? '',
      destination_name: (r.destination_name as string) ?? null,
    }));
  }

  /**
   * §12e Supplier balance — total owed / paid / outstanding across
   * every supplier-side REFUND row for this supplier in the chosen
   * currency, plus the prepayment the supplier is currently holding
   * for us. Shape matches the customer/source balance so the FE
   * renders both sides with the same component.
   */
  async findSupplierBalance(input: { supplier_id: string; currency_id?: string }): Promise<{
    supplier_id: string;
    currency_id: string | null;
    expected: number;
    received: number;
    balance: number;
    credit: number;
  }> {
    const tenantId = this.settlements.currentTenantId();
    const params: unknown[] = [tenantId, input.supplier_id];
    let currencyFilter = '';
    if (input.currency_id) {
      params.push(input.currency_id);
      currencyFilter = `AND ps.currency_id = $${params.length}`;
    }
    const rows = (await this.settlements.manager.query(
      `SELECT COALESCE(SUM(ps.amount), 0) AS expected,
              COALESCE(SUM(ps.amount_settled), 0) AS received
         FROM payment_settlements ps
         -- Exclude soft-deleted bookings: a partially/fully-paid supplier
         -- settlement survives the booking soft-delete cascade, so without
         -- this join its amount would still inflate the supplier balance.
         -- Mirrors findCustomerBalance / findBySourceBalance.
         INNER JOIN bookings b ON b.id = ps.booking_id AND b.is_deleted = false
        WHERE ps.tenant_id = $1
          AND ps.is_deleted = false
          AND ps.party_type = 'SUPPLIER'
          AND ps.type = 'REFUND'
          AND ps.supplier_id = $2
          ${currencyFilter}`,
      params,
    )) as Array<{ expected: string; received: string }>;
    const expected = Number(rows[0]?.expected ?? 0);
    const received = Number(rows[0]?.received ?? 0);

    // Prepayment on file — same ledger table as the customer side,
    // keyed on (SUPPLIER, supplier_id, currency). Nets CREDIT against
    // DEBIT by construction since the header holds the running balance.
    const credParams: unknown[] = [tenantId, input.supplier_id];
    let credCurrency = '';
    if (input.currency_id) {
      credParams.push(input.currency_id);
      credCurrency = `AND ccl.currency_id = $${credParams.length}`;
    }
    const credRows = (await this.settlements.manager.query(
      `SELECT COALESCE(SUM(ccl.balance), 0) AS credit
         FROM customer_credit_ledger ccl
        WHERE ccl.tenant_id = $1
          AND ccl.is_deleted = false
          AND ccl.party_type = 'SUPPLIER'
          AND ccl.party_key = $2
          ${credCurrency}`,
      credParams,
    )) as Array<{ credit: string }>;

    return {
      supplier_id: input.supplier_id,
      currency_id: input.currency_id ?? null,
      expected,
      received,
      balance: +(expected - received).toFixed(2),
      credit: +Number(credRows[0]?.credit ?? 0).toFixed(2),
    };
  }

  /**
   * §12e Pay Supplier — one supplier-side payout that allocates a
   * single paid amount across N supplier-side REFUND settlements.
   * Mirrors `collectPayment` end-to-end: validate everything before
   * touching the DB, then write the payout header + each child
   * settlement_payment + flip parent settlement status + recompute
   * booking + master rollups, all in one transaction.
   *
   * Excess (paid + credit > allocated) lands on the supplier's
   * prepayment ledger when `excess_treatment = PREPAYMENT` — the
   * supplier-side mirror of the customer credit ledger, keyed on
   * `(SUPPLIER, supplier_id, currency_id)`. The next voucher for that
   * pair offers the balance back via `credit_applied`. ADVANCE keeps
   * the old behaviour: recorded on the voucher, not drawdownable.
   */
  async paySupplier(input: {
    supplier_id: string;
    supplier_name?: string | null;
    payment_date: string;
    /** Defaults to BILL_TO_BILL. ON_ACCOUNT is a deliberate deposit:
     *  no allocations, whole amount onto the supplier ledger. */
    payment_type?: SettlementPaymentType | null;
    mode: SettlementPaymentMode;
    ref_no?: string | null;
    debit_bank_account_id?: string | null;
    credit_bank_account_id?: string | null;
    currency_id: string;
    paid_amount: number;
    /** Prepayment drawn from the supplier's ledger, spent alongside
     *  the fresh cash in `paid_amount`. */
    credit_applied?: number | null;
    allocations: Array<{ settlement_id: string; amount: number }>;
    excess_treatment?: PayoutExcessTreatment | null;
    notes?: string | null;
  }): Promise<{
    payout: SupplierPayoutEntity;
    payments: SettlementPaymentEntity[];
  }> {
    const paid = Number(input.paid_amount);
    if (!Number.isFinite(paid) || paid <= 0) {
      throw new BadRequestException('paid_amount must be greater than 0');
    }
    const creditApplied = Number(input.credit_applied ?? 0);
    if (!Number.isFinite(creditApplied) || creditApplied < 0) {
      throw new BadRequestException('credit_applied must be a non-negative number');
    }
    const paymentType = input.payment_type ?? SettlementPaymentType.BILL_TO_BILL;
    if (paymentType === SettlementPaymentType.ON_ACCOUNT && input.allocations.length > 0) {
      throw new BadRequestException(
        'On Account payouts cannot allocate against bills — switch to Bill to Bill',
      );
    }

    let allocatedTotal = 0;
    for (const a of input.allocations) {
      if (!Number.isFinite(a.amount) || a.amount <= 0) {
        throw new BadRequestException('Allocation amounts must be greater than 0');
      }
      allocatedTotal += Number(a.amount);
    }
    // Allocation cap = fresh cash + prepayment drawdown, mirroring
    // collectPayment's `moneyIn`.
    const moneyOut = +(paid + creditApplied).toFixed(2);
    if (allocatedTotal > moneyOut + 0.005) {
      throw new BadRequestException(
        `Sum of allocations (${allocatedTotal.toFixed(2)}) exceeds paid + prepayment (${moneyOut.toFixed(2)})`,
      );
    }
    const excess = +(moneyOut - allocatedTotal).toFixed(2);
    if (excess > 0.005 && !input.excess_treatment) {
      throw new BadRequestException(
        `Paid + prepayment exceeds allocated by ${excess.toFixed(2)} — choose how to treat the excess (PREPAYMENT or ADVANCE)`,
      );
    }
    if (input.allocations.length === 0 && !input.excess_treatment) {
      throw new BadRequestException(
        'No allocations provided — choose an excess treatment (PREPAYMENT or ADVANCE) to record as a deposit',
      );
    }
    // Draining the ledger only to park it straight back is a no-op that
    // would double-count on the movements log.
    if (creditApplied > 0.005 && excess > 0.005) {
      throw new BadRequestException(
        'Cannot apply prepayment and create new prepayment on the same voucher — reduce the applied amount',
      );
    }
    if (creditApplied > 0.005) {
      const ledger = await this.creditLedger.findByParty(
        CreditLedgerPartyType.SUPPLIER,
        input.supplier_id,
        input.currency_id,
      );
      const available = Number(ledger?.balance ?? 0);
      if (creditApplied > available + 0.005) {
        throw new BadRequestException(
          `credit_applied (${creditApplied.toFixed(2)}) exceeds available prepayment (${available.toFixed(2)})`,
        );
      }
    }

    const bankSides = await this.resolveCollectBankAccountIds({
      mode: input.mode,
      debit_bank_account_id: input.debit_bank_account_id,
      credit_bank_account_id: input.credit_bank_account_id,
    });

    // Pre-validate every settlement before writing — same pattern as
    // collectPayment. Catches stale rows, cross-supplier spoofs, and
    // over-allocation before any side effects land.
    const settlementMap = new Map<string, PaymentSettlementEntity>();
    for (const a of input.allocations) {
      const settlement = await this.settlements.findById(a.settlement_id);
      if (!settlement) {
        throw new NotFoundException(`Settlement ${a.settlement_id} not found`);
      }
      if (settlement.status === SettlementStatus.SETTLED) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is already settled`);
      }
      if (settlement.party_type !== SettlementParty.SUPPLIER) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not a supplier-side settlement`,
        );
      }
      if (settlement.type !== SettlementType.REFUND) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is not a supplier REFUND`);
      }
      if (settlement.supplier_id !== input.supplier_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} does not belong to the selected supplier`,
        );
      }
      if (settlement.currency_id !== input.currency_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} currency does not match the payout currency`,
        );
      }
      const outstanding = Number(settlement.amount) - Number(settlement.amount_settled);
      if (Number(a.amount) > outstanding + 0.005) {
        throw new BadRequestException(
          `Allocation for settlement ${a.settlement_id} (${Number(a.amount).toFixed(2)}) exceeds outstanding (${outstanding.toFixed(2)})`,
        );
      }
      settlementMap.set(a.settlement_id, settlement);
    }

    // Voucher number — PAY prefix in the shared settlement_receipt_sequences
    // table so the per-year counter stays per-prefix per-tenant.
    const year = (() => {
      const m = /^(\d{4})/.exec(input.payment_date);
      return m ? Number(m[1]) : new Date(input.payment_date).getUTCFullYear();
    })();
    const { receipt_no: voucher_no, receipt_year: voucher_year } = await allocateReceiptNumber(
      this.payouts.manager,
      this.payouts.currentTenantId(),
      year,
      'PAY',
    );

    const written = await this.payouts.manager.transaction(async () => {
      const payout = await this.payouts.save({
        voucher_no,
        voucher_year,
        supplier_id: input.supplier_id,
        supplier_name: input.supplier_name ?? null,
        payment_date: input.payment_date,
        payment_type: paymentType,
        mode: input.mode,
        ref_no: input.ref_no ?? null,
        debit_bank_account_id: bankSides.debit_bank_account_id,
        credit_bank_account_id: bankSides.credit_bank_account_id,
        currency_id: input.currency_id,
        paid_amount: paid,
        allocated_amount: allocatedTotal,
        credit_applied: creditApplied > 0.005 ? creditApplied : 0,
        excess_amount: excess > 0.005 ? excess : 0,
        excess_treatment: excess > 0.005 ? (input.excess_treatment ?? null) : null,
        notes: input.notes ?? null,
      });

      const payments: SettlementPaymentEntity[] = [];
      const touchedBookings = new Set<string>();
      const touchedMasters = new Set<string>();
      for (const a of input.allocations) {
        const settlement = settlementMap.get(a.settlement_id)!;
        const taxSnapshot = await this.resolvePaymentSnapshot(settlement);
        const payment = await this.payments.save({
          settlement_id: a.settlement_id,
          paid_amount: Number(a.amount),
          paid_date: input.payment_date,
          mode: input.mode,
          ref_no: input.ref_no ?? null,
          notes: input.notes ?? null,
          receipt_no: voucher_no,
          receipt_year: voucher_year,
          tax_snapshot: taxSnapshot,
          payout_id: payout.id,
        });
        // Audit (parity with collect P1-#6): atomic conditional
        // UPDATE so two concurrent payouts can't both succeed past
        // the cap on the same supplier-side settlement. Enum literals
        // cast explicitly — see collectPayment for the same fix.
        const settlementUpdateRows = rowsFromUpdateReturning<{ amount_settled: string; status: string }>(
          await this.payouts.manager.query(
            `UPDATE payment_settlements
                SET amount_settled = LEAST(amount, amount_settled + $1::numeric),
                    status = CASE
                      WHEN amount_settled + $1::numeric >= amount - 0.005 THEN 'SETTLED'::payment_settlements_status_enum
                      ELSE 'PARTIAL'::payment_settlements_status_enum
                    END,
                    settled_date = CASE
                      WHEN amount_settled + $1::numeric >= amount - 0.005 THEN $4::date
                      ELSE NULL
                    END,
                    updated_at = now()
              WHERE id = $2
                AND tenant_id = $3
                AND amount - amount_settled >= $1::numeric - 0.005
            RETURNING amount_settled, status`,
            [Number(a.amount), a.settlement_id, this.payouts.currentTenantId(), input.payment_date],
          ),
        );
        if (settlementUpdateRows.length === 0) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} was concurrently updated — outstanding no longer covers ${Number(a.amount).toFixed(2)}. Reload and retry.`,
          );
        }
        payments.push(payment);
        touchedBookings.add(settlement.booking_id);
        if (settlement.master_id) touchedMasters.add(settlement.master_id);
      }

      // Supplier-side payments don't move the booking's customer-facing
      // payment_status pill, but the master-level rollup tracks supplier
      // owed/paid for the by-supplier and by-service analytics.
      for (const bookingId of touchedBookings) {
        await this.recomputeBookingPaymentStatus(bookingId);
      }
      for (const masterId of touchedMasters) {
        await this.recomputeMaster(masterId);
      }

      await this.applyPayoutLedger({
        payoutId: payout.id,
        supplier_id: input.supplier_id,
        supplier_label: input.supplier_name?.trim() || null,
        currency_id: input.currency_id,
        creditApplied,
        excess,
        excess_treatment: input.excess_treatment ?? null,
        tenantId: this.payouts.currentTenantId(),
        userId: this.cls.get<string>(CLS_USER_ID) ?? null,
      });

      return { payout, payments };
    });

    return written;
  }

  /**
   * §12e prepayment ledger writer — supplier-side mirror of
   * `applyCollectionLedger`.
   *
   * DEBIT  drawdown: the supplier was holding our money and this
   *        voucher spends it. Balance check is inside the WHERE so two
   *        concurrent vouchers can't both drain the same row.
   * CREDIT capture: we over-paid and chose PREPAYMENT, so the supplier
   *        now holds the difference for the next bill.
   *
   * ADVANCE-treated excess deliberately skips the ledger — it stays a
   * note on the voucher, matching the customer side's ADVANCE bucket.
   */
  private async applyPayoutLedger(input: {
    payoutId: string;
    supplier_id: string;
    supplier_label: string | null;
    currency_id: string;
    creditApplied: number;
    excess: number;
    excess_treatment: PayoutExcessTreatment | null;
    tenantId: string;
    userId: string | null;
  }): Promise<void> {
    if (input.creditApplied > 0.005) {
      const ledgerDebitRows = rowsFromUpdateReturning<{ id: string; balance: string }>(
        await this.payouts.manager.query(
          `UPDATE customer_credit_ledger
              SET balance = balance - $1::numeric,
                  last_payout_id = $2,
                  updated_at = now()
            WHERE tenant_id = $3
              AND party_type = $4
              AND party_key = $5
              AND currency_id = $6
              AND is_deleted = false
              AND balance >= $1::numeric - 0.005
          RETURNING id, balance`,
          [
            input.creditApplied,
            input.payoutId,
            input.tenantId,
            CreditLedgerPartyType.SUPPLIER,
            input.supplier_id,
            input.currency_id,
          ],
        ),
      );
      if (ledgerDebitRows.length === 0) {
        throw new BadRequestException(
          `Prepayment drained concurrently or absent — requested ${input.creditApplied.toFixed(2)} could not be deducted. Reload and retry.`,
        );
      }
      const ledgerRow = ledgerDebitRows[0];
      await this.payouts.manager.query(
        `INSERT INTO customer_credit_ledger_movements
           (tenant_id, ledger_id, direction, amount, balance_after, payout_id, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7)`,
        [
          input.tenantId,
          ledgerRow.id,
          CreditLedgerDirection.DEBIT,
          input.creditApplied,
          +Number(ledgerRow.balance).toFixed(2),
          input.payoutId,
          input.userId,
        ],
      );
    }

    if (input.excess > 0.005 && input.excess_treatment === PayoutExcessTreatment.PREPAYMENT) {
      // Supplier name is snapshotted on the ledger header for display;
      // fall back to the master record when the caller didn't send one.
      let partyLabel = input.supplier_label;
      if (!partyLabel) {
        const sup = (await this.payouts.manager.query(
          `SELECT name FROM suppliers WHERE id = $1 AND tenant_id = $2`,
          [input.supplier_id, input.tenantId],
        )) as Array<{ name: string }>;
        partyLabel = sup[0]?.name ?? 'Supplier';
      }
      const existing = await this.creditLedger.findByParty(
        CreditLedgerPartyType.SUPPLIER,
        input.supplier_id,
        input.currency_id,
      );
      let ledgerId: string;
      let balanceAfter: number;
      if (existing) {
        balanceAfter = +(Number(existing.balance) + input.excess).toFixed(2);
        await this.creditLedger.update(existing.id, {
          balance: balanceAfter,
          party_label: partyLabel,
          last_payout_id: input.payoutId,
        });
        ledgerId = existing.id;
      } else {
        const fresh = await this.creditLedger.save({
          party_type: CreditLedgerPartyType.SUPPLIER,
          party_key: input.supplier_id,
          party_label: partyLabel,
          currency_id: input.currency_id,
          balance: input.excess,
          last_payout_id: input.payoutId,
        });
        ledgerId = fresh.id;
        balanceAfter = input.excess;
      }
      await this.payouts.manager.query(
        `INSERT INTO customer_credit_ledger_movements
           (tenant_id, ledger_id, direction, amount, balance_after, payout_id, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7)`,
        [
          input.tenantId,
          ledgerId,
          CreditLedgerDirection.CREDIT,
          input.excess,
          balanceAfter,
          input.payoutId,
          input.userId,
        ],
      );
    }
  }

  /**
   * Single payout with its child allocations. Powers the voucher
   * page and the audit drill-down. Returns flat row shapes — the FE
   * decorates with joins it needs.
   */
  async findPayoutDetail(id: string): Promise<{
    payout: SupplierPayoutEntity & {
      debit_bank_name?: string | null;
      debit_account_name?: string | null;
      credit_bank_name?: string | null;
      credit_account_name?: string | null;
    };
    payments: Array<
      SettlementPaymentEntity & {
        booking_number: string | null;
        source_item_type: string | null;
        travel_date: string | null;
        guest_name: string | null;
      }
    >;
  } | null> {
    const payout = await this.payouts.findById(id);
    if (!payout) return null;
    const [debitAcc, creditAcc] = await Promise.all([
      payout.debit_bank_account_id
        ? this.bankAccounts.findByIdWithBankOrNull(payout.debit_bank_account_id)
        : Promise.resolve(null),
      payout.credit_bank_account_id
        ? this.bankAccounts.findByIdWithBankOrNull(payout.credit_bank_account_id)
        : Promise.resolve(null),
    ]);
    const rows = (await this.payments.manager.query(
      `SELECT sp.id, sp.settlement_id, sp.paid_amount, sp.paid_date,
              sp.mode, sp.ref_no, sp.notes, sp.receipt_no, sp.receipt_year,
              sp.is_void, sp.payout_id, sp.created_at,
              ps.source_item_type AS source_item_type,
              b.booking_number AS booking_number,
              q.start_date AS travel_date,
              q.guest_name AS guest_name
         FROM settlement_payments sp
         INNER JOIN payment_settlements ps ON ps.id = sp.settlement_id
         INNER JOIN bookings b ON b.id = ps.booking_id
         LEFT JOIN queries q ON q.id = b.query_id
        WHERE sp.tenant_id = $1
          AND sp.payout_id = $2
          AND sp.is_deleted = false
        ORDER BY sp.created_at ASC`,
      [this.payments.currentTenantId(), id],
    )) as Array<Record<string, unknown>>;
    const payments = rows.map((r) => ({
      ...(r as any),
      paid_amount: Number(r.paid_amount),
    }));
    return {
      payout: {
        ...payout,
        debit_bank_name: debitAcc?.bank?.name ?? null,
        debit_account_name: debitAcc?.account_name ?? null,
        credit_bank_name: creditAcc?.bank?.name ?? null,
        credit_account_name: creditAcc?.account_name ?? null,
      },
      payments,
    };
  }

  // ── Payment History (Collect Payment listing / edit) ───────────

  async listOfficePaymentHistory(query: {
    search?: string;
    customer_mobile?: string;
    source_id?: string;
    page?: number;
    limit?: number;
    date_from?: string;
    date_to?: string;
    currency_id?: string;
  }): Promise<{
    items: PaymentHistoryListRow[];
    meta: PaymentHistoryListMeta;
  }> {
    const tenantId = this.collections.currentTenantId();
    const page = query.page && query.page > 0 ? query.page : 1;
    const limit = query.limit && query.limit > 0 ? Math.min(query.limit, 100) : 10;
    const offset = (page - 1) * limit;
    const params: unknown[] = [tenantId];
    const partyFilters: string[] = [];
    const filters: string[] = [
      'pc.tenant_id = $1',
      'pc.is_deleted = false',
      'pc.is_void = false',
      `NOT EXISTS (
         SELECT 1 FROM booking_cash_collection_batches b
          WHERE b.payment_collection_id = pc.id
            AND b.tenant_id = $1
            AND b.is_deleted = false
       )`,
      `NOT EXISTS (
         SELECT 1
           FROM settlement_payments sp
           INNER JOIN payment_settlements ps
             ON ps.id = sp.settlement_id
            AND ps.tenant_id = $1
            AND ps.is_deleted = false
           INNER JOIN bookings bk
             ON bk.id = ps.booking_id
            AND bk.tenant_id = $1
            AND bk.is_deleted = true
          WHERE sp.collection_id = pc.id
            AND sp.tenant_id = $1
            AND sp.is_deleted = false
       )`,
    ];
    if (query.customer_mobile) {
      params.push(query.customer_mobile);
      partyFilters.push(`(pc.party_type = 'CUSTOMER' AND pc.customer_mobile = $${params.length})`);
    }
    if (query.source_id) {
      params.push(query.source_id);
      partyFilters.push(`(pc.party_type = 'SOURCE' AND pc.source_id = $${params.length})`);
    }
    if (partyFilters.length) filters.push(`(${partyFilters.join(' OR ')})`);
    if (query.search?.trim()) {
      params.push(`%${query.search.trim()}%`);
      const n = params.length;
      filters.push(
        `(pc.customer_name ILIKE $${n} OR pc.customer_mobile ILIKE $${n} OR pc.receipt_no ILIKE $${n} OR ts.name ILIKE $${n})`,
      );
    }
    if (query.date_from) {
      params.push(query.date_from);
      filters.push(`pc.payment_date >= $${params.length}::date`);
    }
    if (query.date_to) {
      params.push(query.date_to);
      filters.push(`pc.payment_date <= $${params.length}::date`);
    }
    if (query.currency_id) {
      params.push(query.currency_id);
      filters.push(`COALESCE(pc.receive_currency_id, pc.currency_id) = $${params.length}`);
    }
    const where = filters.join(' AND ');
    const countRows = (await this.collections.manager.query(
      `SELECT COUNT(*)::int AS total
         FROM payment_collections pc
         LEFT JOIN trip_sources ts ON ts.id = pc.source_id AND ts.is_deleted = false
        WHERE ${where}`,
      params,
    )) as Array<{ total: number }>;
    const total = Number(countRows[0]?.total ?? 0);
    params.push(limit, offset);
    const rows = (await this.collections.manager.query(
      `SELECT pc.id,
              pc.payment_date::text AS payment_date,
              pc.party_type,
              pc.payment_type,
              COALESCE(NULLIF(pc.customer_name, ''), ts.name, pc.customer_mobile, '') AS party_name,
              COALESCE(rc.code, c.code, '') AS receive_currency_code,
              COALESCE(pc.received_amount_receive_currency, pc.received_amount) AS received_amount
         FROM payment_collections pc
         LEFT JOIN trip_sources ts ON ts.id = pc.source_id AND ts.is_deleted = false
         LEFT JOIN currencies c ON c.id = pc.currency_id
         LEFT JOIN currencies rc ON rc.id = pc.receive_currency_id
        WHERE ${where}
        ORDER BY pc.payment_date DESC, pc.created_at DESC
        LIMIT $${params.length - 1} OFFSET $${params.length}`,
      params,
    )) as Array<Record<string, unknown>>;
    return {
      items: rows.map((r) => ({
        id: String(r.id),
        payment_date: String(r.payment_date).slice(0, 10),
        party_type: r.party_type as CollectionPartyType,
        party_name: String(r.party_name ?? ''),
        payment_type: (r.payment_type as SettlementPaymentType) ?? SettlementPaymentType.BILL_TO_BILL,
        receive_currency_code: String(r.receive_currency_code ?? ''),
        received_amount: Number(r.received_amount),
        posted: false,
      })),
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) || 1 },
    };
  }

  async findOfficeHistoryDetail(id: string): Promise<PaymentHistoryDetail | null> {
    const data = await this.findCollectionDetail(id);
    if (!data) return null;
    const c = data.collection;
    return {
      kind: 'OFFICE',
      id: c.id,
      posted: true,
      payment_collection_id: c.id,
      receipt_no: c.receipt_no,
      party_type: c.party_type,
      customer_mobile: c.customer_mobile,
      source_id: c.source_id,
      source_name: c.source_name ?? null,
      customer_name: c.customer_name,
      payment_date: String(c.payment_date).slice(0, 10),
      payment_type: c.payment_type ?? SettlementPaymentType.BILL_TO_BILL,
      mode: c.mode,
      ref_no: c.ref_no,
      debit_bank_account_id: c.debit_bank_account_id,
      credit_bank_account_id: c.credit_bank_account_id,
      currency_id: c.currency_id,
      receive_currency_id: c.receive_currency_id,
      exchange_rate: c.exchange_rate != null ? Number(c.exchange_rate) : null,
      exchange_rate_quote: c.exchange_rate_quote != null ? Number(c.exchange_rate_quote) : null,
      received_amount: Number(c.received_amount),
      received_amount_receive_currency:
        c.received_amount_receive_currency != null
          ? Number(c.received_amount_receive_currency)
          : null,
      roe: c.roe != null ? Number(c.roe) : null,
      credit_applied: Number(c.credit_applied ?? 0),
      excess_treatment: c.excess_treatment,
      notes: c.notes,
      collected_by_type: c.collected_by_type,
      collector_name: c.collector_name,
      collector_supplier_id: c.collector_supplier_id,
      collector_driver_id: c.collector_driver_id,
      md_booking_id: null,
      md_transport_item_id: null,
      payments: data.payments
        .filter((p) => !p.is_void)
        .map((p) => ({
          settlement_id: p.settlement_id,
          paid_amount: Number(p.paid_amount),
          booking_id: (p as { booking_id?: string }).booking_id ?? null,
          booking_number: p.booking_number,
          guest_name: p.guest_name,
          guest_mobile: null,
          travel_date: p.travel_date,
        })),
    };
  }

  async updateCollection(
    id: string,
    input: {
      payment_date: string;
      mode: SettlementPaymentMode;
      ref_no?: string | null;
      debit_bank_account_id?: string | null;
      credit_bank_account_id?: string | null;
      currency_id: string;
      received_amount: number;
      receive_currency_id?: string | null;
      exchange_rate?: number | null;
      exchange_rate_quote?: number | null;
      received_amount_receive_currency?: number | null;
      roe?: number | null;
      credit_applied?: number | null;
      allocations: Array<{ settlement_id: string; amount: number }>;
      excess_treatment?: CollectionExcessTreatment | null;
      notes?: string | null;
      collected_by_type?: 'OWN' | 'SUPPLIER' | null;
      collector_name?: string | null;
      collector_supplier_id?: string | null;
      collector_driver_id?: string | null;
    },
  ): Promise<PaymentHistoryDetail> {
    const collection = await this.collections.findById(id);
    if (!collection) throw new NotFoundException('Collection not found');
    if (collection.is_void) throw new BadRequestException('Voided collections cannot be edited');

    const received = Number(input.received_amount);
    if (!Number.isFinite(received) || received <= 0) {
      throw new BadRequestException('received_amount must be greater than 0');
    }
    const creditApplied = Number(input.credit_applied ?? 0);
    if (!Number.isFinite(creditApplied) || creditApplied < 0) {
      throw new BadRequestException('credit_applied must be a non-negative number');
    }
    let allocatedTotal = 0;
    for (const a of input.allocations) {
      if (!Number.isFinite(a.amount) || a.amount <= 0) {
        throw new BadRequestException('Allocation amounts must be greater than 0');
      }
      allocatedTotal += Number(a.amount);
    }
    const moneyIn = +(received + creditApplied).toFixed(2);
    if (allocatedTotal > moneyIn + 0.005) {
      throw new BadRequestException(
        `Sum of allocations (${allocatedTotal.toFixed(2)}) exceeds received + credit (${moneyIn.toFixed(2)})`,
      );
    }
    const excess = +(moneyIn - allocatedTotal).toFixed(2);
    if (excess > 0.005 && !input.excess_treatment) {
      throw new BadRequestException(
        `Received + credit exceeds allocated by ${excess.toFixed(2)} — choose how to treat the excess (CREDIT or ADVANCE)`,
      );
    }
    if (input.allocations.length === 0 && !input.excess_treatment) {
      throw new BadRequestException(
        'No allocations provided — choose an excess treatment (CREDIT or ADVANCE) to record as advance',
      );
    }

    const partyType = collection.party_type;
    const bankSides = await this.resolveCollectBankAccountIds({
      mode: input.mode,
      debit_bank_account_id: input.debit_bank_account_id,
      credit_bank_account_id: input.credit_bank_account_id,
    });
    const existingPayments = await this.payments.findByCollection(id);
    const extraPaid = new Map<string, number>();
    for (const p of existingPayments) {
      extraPaid.set(p.settlement_id, (extraPaid.get(p.settlement_id) ?? 0) + Number(p.paid_amount));
    }
    const settlementMap = await this.loadAndValidateAllocations({
      partyType,
      customer_mobile: collection.customer_mobile,
      source_id: collection.source_id,
      currency_id: input.currency_id,
      allocations: input.allocations,
      extraPaid,
    });
    const tenantId = this.collections.currentTenantId();
    const userId = this.cls.get<string>(CLS_USER_ID) ?? null;

    await this.collections.manager.transaction(async () => {
      for (const old of existingPayments) {
        await this.rewindSettlementPaid(old.settlement_id, Number(old.paid_amount), tenantId);
        await this.payments.softDelete(old.id);
      }
      await this.reverseCreditLedgerForCollection(id, tenantId, userId);

      await this.collections.update(id, {
        payment_date: input.payment_date,
        mode: input.mode,
        ref_no: input.ref_no ?? null,
        debit_bank_account_id: bankSides.debit_bank_account_id,
        credit_bank_account_id: bankSides.credit_bank_account_id,
        currency_id: input.currency_id,
        received_amount: received,
        receive_currency_id: input.receive_currency_id ?? null,
        exchange_rate:
          input.exchange_rate != null && Number.isFinite(Number(input.exchange_rate))
            ? Number(input.exchange_rate)
            : null,
        exchange_rate_quote:
          input.exchange_rate_quote != null && Number.isFinite(Number(input.exchange_rate_quote))
            ? Number(input.exchange_rate_quote)
            : null,
        received_amount_receive_currency:
          input.received_amount_receive_currency != null &&
          Number.isFinite(Number(input.received_amount_receive_currency))
            ? Number(input.received_amount_receive_currency)
            : null,
        roe: input.roe != null && Number.isFinite(Number(input.roe)) ? Number(input.roe) : null,
        credit_applied: creditApplied > 0.005 ? creditApplied : 0,
        allocated_amount: allocatedTotal,
        // Editing away every allocation turns the receipt into a pure
        // deposit — keep the stated type in step with what it now is.
        payment_type:
          input.allocations.length > 0
            ? SettlementPaymentType.BILL_TO_BILL
            : SettlementPaymentType.ON_ACCOUNT,
        excess_amount: excess > 0.005 ? excess : 0,
        excess_treatment: excess > 0.005 ? (input.excess_treatment ?? null) : null,
        notes: input.notes ?? null,
        collected_by_type: input.collected_by_type ?? null,
        collector_name: input.collector_name ?? null,
        collector_supplier_id: input.collector_supplier_id ?? null,
        collector_driver_id: input.collector_driver_id ?? null,
      } as never);

      const touchedBookings = new Set<string>();
      const touchedMasters = new Set<string>();
      for (const a of input.allocations) {
        const settlement = settlementMap.get(a.settlement_id)!;
        const taxSnapshot = await this.resolvePaymentSnapshot(settlement);
        await this.payments.save({
          settlement_id: a.settlement_id,
          paid_amount: Number(a.amount),
          paid_date: input.payment_date,
          mode: input.mode,
          ref_no: input.ref_no ?? null,
          notes: input.notes ?? null,
          receipt_no: collection.receipt_no,
          receipt_year: collection.receipt_year,
          tax_snapshot: taxSnapshot,
          collection_id: collection.id,
        });
        const settlementUpdateRows = rowsFromUpdateReturning<{ amount_settled: string; status: string }>(
          await this.collections.manager.query(
            `UPDATE payment_settlements
                SET amount_settled = LEAST(amount, amount_settled + $1::numeric),
                    status = CASE
                      WHEN amount_settled + $1::numeric >= amount - 0.005 THEN 'SETTLED'::payment_settlements_status_enum
                      ELSE 'PARTIAL'::payment_settlements_status_enum
                    END,
                    settled_date = CASE
                      WHEN amount_settled + $1::numeric >= amount - 0.005 THEN $4::date
                      ELSE NULL
                    END,
                    updated_at = now()
              WHERE id = $2
                AND tenant_id = $3
                AND amount - amount_settled >= $1::numeric - 0.005
            RETURNING amount_settled, status`,
            [Number(a.amount), a.settlement_id, tenantId, input.payment_date],
          ),
        );
        if (settlementUpdateRows.length === 0) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} was concurrently updated — outstanding no longer covers ${Number(a.amount).toFixed(2)}. Reload and retry.`,
          );
        }
        touchedBookings.add(settlement.booking_id);
        if (settlement.master_id) touchedMasters.add(settlement.master_id);
      }
      for (const bookingId of touchedBookings) {
        await this.recomputeBookingPaymentStatus(bookingId);
      }
      for (const old of existingPayments) {
        const settlement = await this.settlements.findById(old.settlement_id);
        if (settlement?.booking_id) await this.recomputeBookingPaymentStatus(settlement.booking_id);
        if (settlement?.master_id) await this.recomputeMaster(settlement.master_id);
      }
      for (const masterId of touchedMasters) {
        await this.recomputeMaster(masterId);
      }

      const ledgerPartyType: CreditLedgerPartyType =
        partyType === CollectionPartyType.CUSTOMER
          ? CreditLedgerPartyType.CUSTOMER
          : CreditLedgerPartyType.SOURCE;
      const partyKey =
        partyType === CollectionPartyType.CUSTOMER
          ? normaliseMobileForLedger(collection.customer_mobile!)
          : collection.source_id!;
      await this.applyCollectionLedger({
        collectionId: id,
        ledgerPartyType,
        partyKey,
        partyType,
        customer_name: collection.customer_name,
        customer_mobile: collection.customer_mobile,
        source_id: collection.source_id,
        currency_id: input.currency_id,
        creditApplied,
        excess,
        excess_treatment: input.excess_treatment ?? null,
        tenantId,
        userId,
      });
    });

    const updated = await this.findOfficeHistoryDetail(id);
    if (!updated) throw new NotFoundException('Collection not found');
    return updated;
  }

  private async rewindSettlementPaid(
    settlementId: string,
    paid: number,
    tenantId: string,
  ): Promise<void> {
    await this.collections.manager.query(
      `UPDATE payment_settlements
          SET amount_settled = GREATEST(0, amount_settled - $1::numeric),
              status = CASE
                WHEN GREATEST(0, amount_settled - $1::numeric) <= 0.005
                  THEN 'PENDING'::payment_settlements_status_enum
                WHEN GREATEST(0, amount_settled - $1::numeric) >= amount - 0.005
                  THEN 'SETTLED'::payment_settlements_status_enum
                ELSE 'PARTIAL'::payment_settlements_status_enum
              END,
              settled_date = CASE
                WHEN GREATEST(0, amount_settled - $1::numeric) >= amount - 0.005 THEN settled_date
                ELSE NULL
              END,
              updated_at = now()
        WHERE id = $2 AND tenant_id = $3`,
      [paid, settlementId, tenantId],
    );
  }

  private async reverseCreditLedgerForCollection(
    collectionId: string,
    tenantId: string,
    userId: string | null,
  ): Promise<void> {
    const movements = (await this.collections.manager.query(
      `SELECT id, ledger_id, direction, amount
         FROM customer_credit_ledger_movements
        WHERE tenant_id = $1 AND collection_id = $2
        ORDER BY created_at DESC`,
      [tenantId, collectionId],
    )) as Array<{ id: string; ledger_id: string; direction: string; amount: string }>;
    for (const m of movements) {
      const amt = Number(m.amount);
      const reverseDir =
        m.direction === CreditLedgerDirection.CREDIT
          ? CreditLedgerDirection.DEBIT
          : CreditLedgerDirection.CREDIT;
      const sign = reverseDir === CreditLedgerDirection.DEBIT ? -1 : 1;
      const updated = (await this.collections.manager.query(
        `UPDATE customer_credit_ledger
            SET balance = balance + $1::numeric,
                updated_at = now()
          WHERE id = $2 AND tenant_id = $3 AND is_deleted = false
            AND ($1::numeric >= 0 OR balance >= -$1::numeric - 0.005)
        RETURNING balance`,
        [sign * amt, m.ledger_id, tenantId],
      )) as Array<{ balance: string }>;
      if (updated.length === 0) {
        throw new BadRequestException(
          'Credit ledger could not be reversed for this collection — reload and retry.',
        );
      }
      await this.collections.manager.query(
        `INSERT INTO customer_credit_ledger_movements
           (tenant_id, ledger_id, direction, amount, balance_after, collection_id, created_by, notes)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
        [
          tenantId,
          m.ledger_id,
          reverseDir,
          amt,
          Number(updated[0].balance),
          collectionId,
          userId,
          'Reversal for collection edit',
        ],
      );
    }
  }

  private async loadAndValidateAllocations(input: {
    partyType: CollectionPartyType;
    customer_mobile: string | null;
    source_id: string | null;
    currency_id: string;
    allocations: Array<{ settlement_id: string; amount: number }>;
    extraPaid?: Map<string, number>;
  }): Promise<Map<string, PaymentSettlementEntity>> {
    const settlementMap = new Map<string, PaymentSettlementEntity>();
    for (const a of input.allocations) {
      const settlement = await this.settlements.findById(a.settlement_id);
      if (!settlement) {
        throw new NotFoundException(`Settlement ${a.settlement_id} not found`);
      }
      const extra = input.extraPaid?.get(a.settlement_id) ?? 0;
      if (settlement.status === SettlementStatus.SETTLED && extra <= 0.005) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is already settled`);
      }
      if (settlement.party_type !== SettlementParty.CUSTOMER) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not a customer-side settlement`,
        );
      }
      if (settlement.type !== SettlementType.COLLECTION) {
        throw new BadRequestException(`Settlement ${a.settlement_id} is not a COLLECTION`);
      }
      if (settlement.currency_id !== input.currency_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} currency does not match the collection currency`,
        );
      }
      const outstanding =
        Number(settlement.amount) - Number(settlement.amount_settled) + extra;
      if (Number(a.amount) > outstanding + 0.005) {
        throw new BadRequestException(
          `Allocation for settlement ${a.settlement_id} (${Number(a.amount).toFixed(2)}) exceeds outstanding (${outstanding.toFixed(2)})`,
        );
      }
      const booking = await this.bookingRepo.findOne({
        where: { id: settlement.booking_id },
        select: { id: true, query_id: true } as never,
      });
      if (!booking?.query_id) {
        throw new BadRequestException(`Settlement ${a.settlement_id} has no parent query`);
      }
      const parent = await this.queryRepo.findOne({
        where: { id: booking.query_id },
        select: { id: true, guest_mobile: true, source_id: true } as never,
      });
      if (!parent) {
        throw new BadRequestException(`Settlement ${a.settlement_id} has no parent query`);
      }
      if (input.partyType === CollectionPartyType.CUSTOMER) {
        if (parent.guest_mobile !== input.customer_mobile) {
          throw new BadRequestException(
            `Settlement ${a.settlement_id} does not belong to customer ${input.customer_mobile}`,
          );
        }
      } else if (parent.source_id !== input.source_id) {
        throw new BadRequestException(
          `Settlement ${a.settlement_id} is not channelled via the selected agent`,
        );
      }
      settlementMap.set(a.settlement_id, settlement);
    }
    return settlementMap;
  }

  private async applyCollectionLedger(input: {
    collectionId: string;
    ledgerPartyType: CreditLedgerPartyType;
    partyKey: string;
    partyType: CollectionPartyType;
    customer_name: string | null;
    customer_mobile: string | null;
    source_id: string | null;
    currency_id: string;
    creditApplied: number;
    excess: number;
    excess_treatment: CollectionExcessTreatment | null;
    tenantId: string;
    userId: string | null;
  }): Promise<void> {
    if (input.creditApplied > 0.005) {
      const ledgerDebitRows = rowsFromUpdateReturning<{ id: string; balance: string }>(
        await this.collections.manager.query(
          `UPDATE customer_credit_ledger
              SET balance = balance - $1::numeric,
                  last_collection_id = $2,
                  updated_at = now()
            WHERE tenant_id = $3
              AND party_type = $4
              AND party_key = $5
              AND currency_id = $6
              AND is_deleted = false
              AND balance >= $1::numeric - 0.005
          RETURNING id, balance`,
          [
            input.creditApplied,
            input.collectionId,
            input.tenantId,
            input.ledgerPartyType,
            input.partyKey,
            input.currency_id,
          ],
        ),
      );
      if (ledgerDebitRows.length === 0) {
        throw new BadRequestException(
          `Credit drained concurrently or absent — requested ${input.creditApplied.toFixed(2)} could not be deducted. Reload and retry.`,
        );
      }
      const ledgerRow = ledgerDebitRows[0];
      await this.collections.manager.query(
        `INSERT INTO customer_credit_ledger_movements
           (tenant_id, ledger_id, direction, amount, balance_after, collection_id, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7)`,
        [
          input.tenantId,
          ledgerRow.id,
          CreditLedgerDirection.DEBIT,
          input.creditApplied,
          +Number(ledgerRow.balance).toFixed(2),
          input.collectionId,
          input.userId,
        ],
      );
    }
    if (input.excess > 0.005 && input.excess_treatment === CollectionExcessTreatment.CREDIT) {
      let partyLabel: string;
      if (input.partyType === CollectionPartyType.CUSTOMER) {
        partyLabel = input.customer_name?.trim() || input.customer_mobile!;
      } else {
        const src = await this.collections.manager.findOne(TripSourceEntity, {
          where: { id: input.source_id! } as never,
          select: { id: true, name: true } as never,
        });
        partyLabel = src?.name ?? 'Agent';
      }
      const existing = await this.creditLedger.findByParty(
        input.ledgerPartyType,
        input.partyKey,
        input.currency_id,
      );
      let ledgerId: string;
      let balanceAfter: number;
      if (existing) {
        balanceAfter = +(Number(existing.balance) + input.excess).toFixed(2);
        await this.creditLedger.update(existing.id, {
          balance: balanceAfter,
          party_label: partyLabel,
          last_collection_id: input.collectionId,
        });
        ledgerId = existing.id;
      } else {
        const fresh = await this.creditLedger.save({
          party_type: input.ledgerPartyType,
          party_key: input.partyKey,
          party_label: partyLabel,
          currency_id: input.currency_id,
          balance: input.excess,
          last_collection_id: input.collectionId,
        });
        ledgerId = fresh.id;
        balanceAfter = input.excess;
      }
      await this.collections.manager.query(
        `INSERT INTO customer_credit_ledger_movements
           (tenant_id, ledger_id, direction, amount, balance_after, collection_id, created_by)
         VALUES ($1, $2, $3, $4, $5, $6, $7)`,
        [
          input.tenantId,
          ledgerId,
          CreditLedgerDirection.CREDIT,
          input.excess,
          balanceAfter,
          input.collectionId,
          input.userId,
        ],
      );
    }
  }
}

export interface PaymentHistoryListRow {
  id: string;
  payment_date: string;
  party_type: CollectionPartyType;
  party_name: string;
  payment_type: SettlementPaymentType;
  receive_currency_code: string;
  received_amount: number;
  posted: boolean;
}

export interface PaymentHistoryListMeta {
  total: number;
  page: number;
  limit: number;
  totalPages: number;
}

export interface PaymentHistoryDetail {
  kind: 'OFFICE' | 'MD';
  id: string;
  posted: boolean;
  payment_collection_id: string | null;
  receipt_no: string | null;
  party_type: CollectionPartyType;
  customer_mobile: string | null;
  source_id: string | null;
  source_name: string | null;
  customer_name: string | null;
  payment_date: string;
  payment_type: SettlementPaymentType;
  mode: SettlementPaymentMode;
  ref_no: string | null;
  debit_bank_account_id: string | null;
  credit_bank_account_id: string | null;
  currency_id: string;
  receive_currency_id: string | null;
  exchange_rate: number | null;
  exchange_rate_quote: number | null;
  received_amount: number;
  received_amount_receive_currency: number | null;
  roe: number | null;
  credit_applied: number;
  excess_treatment: CollectionExcessTreatment | null;
  notes: string | null;
  collected_by_type: 'OWN' | 'SUPPLIER' | null;
  collector_name: string | null;
  collector_supplier_id: string | null;
  collector_driver_id: string | null;
  md_booking_id: string | null;
  md_transport_item_id: string | null;
  payments: Array<{
    settlement_id: string;
    paid_amount: number;
    booking_id: string | null;
    booking_number: string | null;
    guest_name: string | null;
    guest_mobile: string | null;
    travel_date: string | null;
  }>;
}

/**
 * §12d audit P1-#3 — collapse a customer mobile to a stable
 * digits-only key for the credit ledger. Drops spaces, hyphens,
 * parentheses, and a leading '+' so "+91 98765-43210",
 * "+919876543210", and "(91) 9876543210" all map to the same row.
 *
 * Empty / non-digit input falls back to the original string so we
 * never collapse a name-like value into "" by accident — callers
 * that pass non-mobile data still get something idempotent to key on.
 */
export function normaliseMobileForLedger(raw: string): string {
  const digits = (raw ?? '').replace(/[^0-9]/g, '');
  return digits.length > 0 ? digits : (raw ?? '').toLowerCase();
}