import { BadRequestException, Injectable, NotFoundException } from "@nestjs/common";
import {
  Prisma,
  TransactionCaptureSource,
  TransactionDomain,
} from "@jorgecuadros/database";
import { PrismaService } from "../prisma/prisma.service";
import {
  BatchCreateDto,
  CreateMovementDto,
  ResolveOutstandingDto,
} from "./movement.dto";

/**
 * Shared billing / statements module — plan step 6.
 *
 * SIGN CONVENTION (established from the migrated data, not assumed):
 * `transactions.amount` is a *signed* ledger amount.
 *   - negative = cargo (a charge: a utility bill, predial, trust fee, HOA due,
 *     insurance premium the office paid or billed on the customer's behalf).
 *     Every legacy `type_of_trx` on the charge side is negative without
 *     exception — WATER 3115/3117 negative, ELECTRIC 2191/2191, PROPERTY TAXES
 *     926/926, TRUST FEE 188/188.
 *   - positive = abono (a credit: CHECK DEPOSIT, CASH DEPOSIT, PAYPAL, and
 *     every `EFECTIVO` cash receipt).
 * So `SUM(amount)` is the balance: negative means the customer owes the office,
 * positive means the customer is in credit.
 *
 * CURRENCY IS NOT SUMMABLE. 912 of the 1269 customers with a ledger have
 * movements in both MXN and USD, and the charge side (`datos2`/`FEE ANUAL`/
 * `fee15`) is MXN-only while receipts arrive in both. Applying a historical
 * exchange rate to a 20-year ledger to produce one number would invent a figure
 * the source data never had, so every total in this module is reported *per
 * currency* and never collapsed. Balance filters and sorts therefore operate on
 * one caller-chosen currency at a time.
 */

/** Which side of the ledger a movement is on. */
export type LedgerDirection = "charge" | "credit";

/** Balance buckets for the receivables worklist, on the selected currency. */
export type BalanceFilter = "all" | "owing" | "credit" | "settled";

export type MovementSort =
  | "date_desc"
  | "date_asc"
  | "amount_desc"
  | "amount_asc"
  | "customer";

export type BalanceSort = "owing_desc" | "credit_desc" | "recent" | "customer";

export type LedgerCurrency = "MXN" | "USD";

export interface MovementParams {
  query?: string;
  page: number;
  pageSize: number;
  domain?: TransactionDomain;
  currency?: LedgerCurrency;
  direction?: LedgerDirection;
  typeId?: string;
  source?: string;
  customerId?: string;
  /** Restrict to captured-but-unpaid rows (the legacy NOPAGO worklist). */
  outstanding?: boolean;
  /** Groups a capture batch: every row cut against one physical check. */
  checkNumber?: string;
  /** Inclusive ISO date bounds on `transactionDate`. */
  from?: Date;
  to?: Date;
  sort: MovementSort;
}

/**
 * Non-client-supplied options for a capture. Kept out of the DTO on purpose:
 * these are set by the calling *module*, never by an HTTP body, so a client
 * can't label its own rows as machine-captured or forge a capture ref.
 * See `BillingService.createBatch` for the seam contract.
 */
export interface CaptureOptions {
  /** Defaults to BATCH for the HTTP path; the OCR pipeline passes OCR. */
  source?: TransactionCaptureSource;
  /** Per-line artifact ids, positionally parallel to `dto.lines`. */
  refs?: (string | undefined)[];
}

export interface BalanceParams {
  query?: string;
  page: number;
  pageSize: number;
  currency: LedgerCurrency;
  balance: BalanceFilter;
  /** Restricts the whole balance to one business line. */
  domain?: TransactionDomain;
  sort: BalanceSort;
}

/** Raw shape of the per-customer balance aggregate. */
interface BalanceRow {
  id: string;
  name: string;
  nameSource: string | null;
  nameMissing: number;
  city: string | null;
  state: string | null;
  movements: bigint | number | string;
  balanceMxn: Prisma.Decimal | null;
  balanceUsd: Prisma.Decimal | null;
  chargesMxn: Prisma.Decimal | null;
  creditsMxn: Prisma.Decimal | null;
  chargesUsd: Prisma.Decimal | null;
  creditsUsd: Prisma.Decimal | null;
  utilityMovements: bigint | number | string;
  insuranceMovements: bigint | number | string;
  lastMovement: Date | null;
}

/**
 * Raw-query counts come back in three shapes depending on the aggregate:
 * `COUNT(*)` as bigint, `SUM(bool)` as a decimal *string*, and plain numbers.
 * Normalize all of them before they reach the client as JSON.
 */
function num(v: bigint | number | string | null | undefined): number {
  if (v === null || v === undefined) return 0;
  return typeof v === "number" ? v : Number(v);
}

function dec(v: Prisma.Decimal | null | undefined): string {
  return (v ?? new Prisma.Decimal(0)).toFixed(2);
}

/**
 * Every aggregate (SUM/count/groupBy and the raw balance SQL) must exclude
 * voided rows, or a reversed movement keeps affecting the books. List views
 * still show voided rows struck-through — only totals drop them.
 */
const NOT_VOIDED: Prisma.TransactionWhereInput = { voidedAt: null };

/**
 * Outstanding ("NOPAGO") rows are captured but unpaid — the office recorded the
 * bill without funds to cover it. They are excluded from every *balance*
 * aggregate, exactly as the legacy `SALDOS ULTIMO 0` query did with its
 * `HAVING NOPAGO = 0`: the office hasn't paid the bill, so it isn't yet owed by
 * the customer. Resolving one (POST /billing/:id/resolve-outstanding) clears the
 * flag and the amount starts counting.
 *
 * This is deliberately narrower than NOT_VOIDED. Voided rows are excluded
 * everywhere; outstanding rows are excluded only from balances — the movement
 * browser still totals them, because "how much water did we capture in April"
 * means every captured row regardless of whether the check cleared.
 */
const NOT_OUTSTANDING: Prisma.TransactionWhereInput = { outstanding: false };

/**
 * Source tables excluded from the customer-facing statement.
 *
 * The legacy portal's `datosfreak` table was materialized from DATOS2 only
 * (`objects.json:1358`), so the customer's "current balance" never saw
 * EFECTIVO / EFECTIVO FM3 / CHEQUE FM3 / EFECTIVO_BACKUP cash receipts, nor
 * the IVA 2015 snapshot. The unified `transactions` table has all of them, so
 * the statement must drop them to match the legacy number the customer has
 * been quoted for years. The staff-facing balances worklist and movement
 * browser keep them — they're real money, just tracked separately
 * (FM3 = visa fee stream, EFECTIVO = cash receipt stream).
 */
const STATEMENT_EXCLUDED_SOURCE_TABLES: readonly string[] = [
  "EFECTIVO",
  "EFECTIVO_BACKUP",
  "EFECTIVO FM3",
  "CHEQUE FM3",
  "IVA 2015",
];

@Injectable()
export class BillingService {
  constructor(private readonly prisma: PrismaService) {}

  private movementWhere(p: MovementParams): Prisma.TransactionWhereInput {
    const and: Prisma.TransactionWhereInput[] = [];

    if (p.query && p.query.trim()) {
      const q = p.query.trim();
      and.push({
        OR: [
          { customer: { name: { contains: q } } },
          { reference: { contains: q } },
          { checkNumber: { contains: q } },
          { message: { contains: q } },
          { period: { contains: q } },
        ],
      });
    }
    if (p.domain) and.push({ domain: p.domain });
    if (p.currency) and.push({ currency: p.currency });
    // A charge is strictly negative and a credit strictly positive; the ~193
    // zero-amount rows are neither and are excluded from both sides on purpose.
    if (p.direction === "charge") and.push({ amount: { lt: 0 } });
    if (p.direction === "credit") and.push({ amount: { gt: 0 } });
    if (p.typeId) and.push({ typeId: p.typeId });
    if (p.source) and.push({ legacySourceTable: p.source });
    if (p.customerId) and.push({ customerId: p.customerId });
    if (p.outstanding !== undefined) and.push({ outstanding: p.outstanding });
    // Exact match, not `contains`: this is the by-check reconciliation lookup,
    // where "1234" must not drag in "51234".
    if (p.checkNumber) and.push({ checkNumber: p.checkNumber });
    if (p.from || p.to) {
      and.push({
        transactionDate: {
          ...(p.from ? { gte: p.from } : {}),
          ...(p.to ? { lte: p.to } : {}),
        },
      });
    }

    return and.length ? { AND: and } : {};
  }

  private movementOrderBy(
    sort: MovementSort,
  ): Prisma.TransactionOrderByWithRelationInput[] {
    switch (sort) {
      case "date_asc":
        return [{ transactionDate: "asc" }];
      case "amount_desc":
        return [{ amount: "desc" }];
      case "amount_asc":
        return [{ amount: "asc" }];
      case "customer":
        return [
          { customer: { nameMissing: "asc" } },
          { customer: { name: "asc" } },
          { transactionDate: "desc" },
        ];
      default:
        return [{ transactionDate: "desc" }];
    }
  }

  /** Cross-customer movement browser — every charge and credit, filterable. */
  async movements(params: MovementParams) {
    const where = this.movementWhere(params);

    const [total, rows] = await this.prisma.$transaction([
      this.prisma.transaction.count({ where }),
      this.prisma.transaction.findMany({
        where,
        skip: (params.page - 1) * params.pageSize,
        take: params.pageSize,
        orderBy: this.movementOrderBy(params.sort),
        select: {
          id: true,
          transactionDate: true,
          domain: true,
          amount: true,
          currency: true,
          reference: true,
          period: true,
          checkNumber: true,
          message: true,
          legacySourceTable: true,
          voidedAt: true,
          outstanding: true,
          type: { select: { nameEn: true, nameEs: true } },
          customer: {
            select: { id: true, name: true, nameSource: true, city: true },
          },
        },
      }),
    ]);

    // Totals for the *filtered set*, not just the page — the number staff read
    // off a filtered view ("how much did we bill for water in April") has to
    // cover everything the filter matched.
    const totals = await this.prisma.transaction.groupBy({
      by: ["currency"],
      where: { AND: [where, NOT_VOIDED] },
      _sum: { amount: true },
      _count: { _all: true },
    });
    const charges = await this.prisma.transaction.groupBy({
      by: ["currency"],
      where: { AND: [where, { amount: { lt: 0 } }, NOT_VOIDED] },
      _sum: { amount: true },
      _count: { _all: true },
    });
    const credits = await this.prisma.transaction.groupBy({
      by: ["currency"],
      where: { AND: [where, { amount: { gt: 0 } }, NOT_VOIDED] },
      _sum: { amount: true },
      _count: { _all: true },
    });
    const chargeMap = new Map(charges.map((c) => [c.currency, c]));
    const creditMap = new Map(credits.map((c) => [c.currency, c]));

    return {
      items: rows.map((r) => ({
        id: r.id,
        transactionDate: r.transactionDate,
        domain: r.domain,
        amount: r.amount,
        currency: r.currency,
        direction: r.amount.lessThan(0) ? "charge" : "credit",
        reference: r.reference,
        period: r.period,
        checkNumber: r.checkNumber,
        message: r.message,
        source: r.legacySourceTable,
        type: r.type,
        voided: r.voidedAt != null,
        outstanding: r.outstanding,
        customerId: r.customer.id,
        customerName: r.customer.name,
        customerNameSource: r.customer.nameSource,
        customerCity: r.customer.city,
      })),
      total,
      page: params.page,
      pageSize: params.pageSize,
      pageCount: Math.ceil(total / params.pageSize),
      totals: totals.map((t) => ({
        currency: t.currency,
        net: t._sum.amount,
        count: t._count._all,
        charges: chargeMap.get(t.currency)?._sum.amount ?? null,
        chargeCount: chargeMap.get(t.currency)?._count._all ?? 0,
        credits: creditMap.get(t.currency)?._sum.amount ?? null,
        creditCount: creditMap.get(t.currency)?._count._all ?? 0,
      })),
    };
  }

  /**
   * Receivables worklist: one row per customer with a ledger, carrying both
   * currency balances, filtered/sorted on the caller's chosen currency.
   *
   * Raw SQL rather than Prisma `groupBy` because this needs conditional sums
   * per currency *and* per direction in a single pass, plus ordering and
   * pagination on a computed balance — none of which groupBy expresses.
   */
  async balances(params: BalanceParams) {
    const { query, page, pageSize, currency, balance, domain, sort } = params;

    const filters: Prisma.Sql[] = [];
    if (domain) filters.push(Prisma.sql`t.domain = ${domain}`);
    const txFilter = filters.length
      ? Prisma.sql`AND ${Prisma.join(filters, " AND ")}`
      : Prisma.empty;

    const nameFilter =
      query && query.trim()
        ? Prisma.sql`AND (c.name LIKE ${`%${query.trim()}%`} OR c.city LIKE ${`%${query.trim()}%`})`
        : Prisma.empty;

    // The balance column the filter and sort act on.
    const bal =
      currency === "USD"
        ? Prisma.sql`SUM(CASE WHEN t.currency = 'USD' THEN t.amount ELSE 0 END)`
        : Prisma.sql`SUM(CASE WHEN t.currency = 'MXN' THEN t.amount ELSE 0 END)`;

    // "Owing" is a *negative* balance (see the sign convention above). The
    // 0.005 threshold keeps rounding dust out of both worklists.
    let having = Prisma.empty;
    if (balance === "owing") having = Prisma.sql`HAVING ${bal} < -0.005`;
    else if (balance === "credit") having = Prisma.sql`HAVING ${bal} > 0.005`;
    else if (balance === "settled")
      having = Prisma.sql`HAVING ${bal} BETWEEN -0.005 AND 0.005`;

    let orderBy: Prisma.Sql;
    switch (sort) {
      case "credit_desc":
        orderBy = Prisma.sql`ORDER BY ${bal} DESC`;
        break;
      case "recent":
        orderBy = Prisma.sql`ORDER BY MAX(t.transactionDate) DESC`;
        break;
      case "customer":
        orderBy = Prisma.sql`ORDER BY c.nameMissing ASC, c.name ASC`;
        break;
      default:
        // Deepest debt first — the point of the worklist.
        orderBy = Prisma.sql`ORDER BY ${bal} ASC`;
    }

    const rows = await this.prisma.$queryRaw<BalanceRow[]>`
      SELECT
        c.id,
        c.name,
        c.nameSource,
        c.nameMissing,
        c.city,
        c.state,
        COUNT(*)                                                                   AS movements,
        SUM(CASE WHEN t.currency = 'MXN' THEN t.amount ELSE 0 END)                 AS balanceMxn,
        SUM(CASE WHEN t.currency = 'USD' THEN t.amount ELSE 0 END)                 AS balanceUsd,
        SUM(CASE WHEN t.currency = 'MXN' AND t.amount < 0 THEN t.amount ELSE 0 END) AS chargesMxn,
        SUM(CASE WHEN t.currency = 'MXN' AND t.amount > 0 THEN t.amount ELSE 0 END) AS creditsMxn,
        SUM(CASE WHEN t.currency = 'USD' AND t.amount < 0 THEN t.amount ELSE 0 END) AS chargesUsd,
        SUM(CASE WHEN t.currency = 'USD' AND t.amount > 0 THEN t.amount ELSE 0 END) AS creditsUsd,
        SUM(t.domain = 'UTILITY')                                                  AS utilityMovements,
        SUM(t.domain = 'INSURANCE')                                                AS insuranceMovements,
        MAX(t.transactionDate)                                                     AS lastMovement
      FROM customers c
      JOIN transactions t ON t.customerId = c.id
      WHERE t.voidedAt IS NULL AND t.outstanding = 0 ${nameFilter} ${txFilter}
      GROUP BY c.id, c.name, c.nameSource, c.nameMissing, c.city, c.state
      ${having}
      ${orderBy}
      LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}
    `;

    const counted = await this.prisma.$queryRaw<{ total: bigint | number | string }[]>`
      SELECT COUNT(*) AS total FROM (
        SELECT c.id
        FROM customers c
        JOIN transactions t ON t.customerId = c.id
        -- Must match the page query's filters exactly, or the total disagrees
        -- with the rows. (The void exclusion was missing here before the
        -- outstanding work; a voided-only customer inflated the count.)
        WHERE t.voidedAt IS NULL AND t.outstanding = 0 ${nameFilter} ${txFilter}
        GROUP BY c.id
        ${having}
      ) x
    `;
    const total = num(counted[0]?.total);

    return {
      items: rows.map((r) => ({
        id: r.id,
        name: r.name,
        nameSource: r.nameSource,
        city: r.city,
        state: r.state,
        movements: num(r.movements),
        utilityMovements: num(r.utilityMovements),
        insuranceMovements: num(r.insuranceMovements),
        lastMovement: r.lastMovement,
        balances: [
          {
            currency: "MXN",
            balance: dec(r.balanceMxn),
            charges: dec(r.chargesMxn),
            credits: dec(r.creditsMxn),
          },
          {
            currency: "USD",
            balance: dec(r.balanceUsd),
            charges: dec(r.chargesUsd),
            credits: dec(r.creditsUsd),
          },
        ],
      })),
      total,
      page,
      pageSize,
      pageCount: Math.ceil(total / pageSize),
      currency,
    };
  }

  /** Top-line figures for the billing page header. */
  async stats() {
    const [movements, ledgerCustomers, byCurrency, byDomain] = await Promise.all([
      this.prisma.transaction.count({ where: NOT_VOIDED }),
      this.prisma.transaction
        .findMany({
          where: NOT_VOIDED,
          distinct: ["customerId"],
          select: { customerId: true },
        })
        .then((r) => r.length),
      this.prisma.transaction.groupBy({
        by: ["currency"],
        where: NOT_VOIDED,
        _sum: { amount: true },
        _count: { _all: true },
      }),
      this.prisma.transaction.groupBy({
        by: ["domain", "currency"],
        where: NOT_VOIDED,
        _sum: { amount: true },
        _count: { _all: true },
      }),
    ]);

    const charges = await this.prisma.transaction.groupBy({
      by: ["currency"],
      where: { AND: [{ amount: { lt: 0 } }, NOT_VOIDED] },
      _sum: { amount: true },
      _count: { _all: true },
    });
    const credits = await this.prisma.transaction.groupBy({
      by: ["currency"],
      where: { AND: [{ amount: { gt: 0 } }, NOT_VOIDED] },
      _sum: { amount: true },
      _count: { _all: true },
    });
    const chargeMap = new Map(charges.map((c) => [c.currency, c]));
    const creditMap = new Map(credits.map((c) => [c.currency, c]));

    // How many customers sit on each side of the line, per currency — the
    // headline for a receivables view. Counted in SQL; a customer can be
    // "owing" in MXN and "in credit" in USD, and both are true at once.
    const sides = await this.prisma.$queryRaw<
      {
        currency: string;
        owing: bigint | number | string;
        inCredit: bigint | number | string;
      }[]
    >`
      SELECT currency,
             SUM(bal < -0.005) AS owing,
             SUM(bal >  0.005) AS inCredit
      FROM (
        SELECT customerId, currency, SUM(amount) AS bal
        FROM transactions WHERE voidedAt IS NULL GROUP BY customerId, currency
      ) x
      GROUP BY currency
    `;
    const sideMap = new Map(sides.map((s) => [s.currency, s]));

    const [firstRow, lastRow] = await Promise.all([
      this.prisma.transaction.findFirst({
        where: NOT_VOIDED,
        orderBy: { transactionDate: "asc" },
        select: { transactionDate: true },
      }),
      this.prisma.transaction.findFirst({
        where: NOT_VOIDED,
        orderBy: { transactionDate: "desc" },
        select: { transactionDate: true },
      }),
    ]);

    // Customers whose ledger spans both business lines — the whole reason this
    // module is one view instead of two.
    const crossLine = await this.prisma.$queryRaw<{ n: bigint | number | string }[]>`
      SELECT COUNT(*) AS n FROM (
        SELECT customerId FROM transactions WHERE voidedAt IS NULL
        GROUP BY customerId HAVING COUNT(DISTINCT domain) > 1
      ) x
    `;

    return {
      movements,
      ledgerCustomers,
      crossLineCustomers: num(crossLine[0]?.n),
      firstMovement: firstRow?.transactionDate ?? null,
      lastMovement: lastRow?.transactionDate ?? null,
      byCurrency: byCurrency.map((c) => ({
        currency: c.currency,
        net: c._sum.amount,
        count: c._count._all,
        charges: chargeMap.get(c.currency)?._sum.amount ?? null,
        chargeCount: chargeMap.get(c.currency)?._count._all ?? 0,
        credits: creditMap.get(c.currency)?._sum.amount ?? null,
        creditCount: creditMap.get(c.currency)?._count._all ?? 0,
        owing: num(sideMap.get(c.currency)?.owing),
        inCredit: num(sideMap.get(c.currency)?.inCredit),
      })),
      byDomain: byDomain.map((d) => ({
        domain: d.domain,
        currency: d.currency,
        net: d._sum.amount,
        count: d._count._all,
      })),
    };
  }

  /** Filter dropdown options for the movement browser. */
  async facets() {
    const types = await this.prisma.transaction.groupBy({
      by: ["typeId"],
      where: { AND: [{ typeId: { not: null } }, NOT_VOIDED] },
      _count: { _all: true },
      orderBy: { _count: { typeId: "desc" } },
    });
    const typeRows = await this.prisma.typeTransaction.findMany({
      where: { id: { in: types.map((t) => t.typeId as string) } },
      select: { id: true, nameEn: true, nameEs: true },
    });
    const typeMap = new Map(typeRows.map((t) => [t.id, t]));

    const sources = await this.prisma.transaction.groupBy({
      by: ["legacySourceTable"],
      where: NOT_VOIDED,
      _count: { _all: true },
      orderBy: { _count: { legacySourceTable: "desc" } },
    });

    const years = await this.prisma.$queryRaw<
      { year: number; count: bigint | number | string }[]
    >`
      SELECT YEAR(transactionDate) AS year, COUNT(*) AS count
      FROM transactions WHERE voidedAt IS NULL GROUP BY year ORDER BY year DESC
    `;

    return {
      types: types
        .map((t) => {
          const row = typeMap.get(t.typeId as string);
          return {
            id: t.typeId as string,
            name: row?.nameEs || row?.nameEn || "—",
            count: t._count._all,
          };
        })
        .filter((t) => t.name !== "—"),
      sources: sources.map((s) => ({
        name: s.legacySourceTable ?? "—",
        count: s._count._all,
      })),
      years: years.map((y) => ({ year: Number(y.year), count: num(y.count) })),
    };
  }

  /**
   * One customer's statement across both business lines.
   *
   * Returns the *whole* ledger rather than a page of it: the heaviest customer
   * carries 365 movements (mean 26), and a running balance is meaningless if
   * the client only holds a slice. The running balance is accumulated per
   * currency in chronological order, then the list is handed back newest-first
   * with each row's balance-after already attached.
   */
  async statement(customerId: string) {
    const customer = await this.prisma.customer.findUnique({
      where: { id: customerId },
      select: {
        id: true,
        name: true,
        nameSource: true,
        addressLine1: true,
        city: true,
        state: true,
        phone: true,
        mobile: true,
        email: true,
        customerSince: true,
        preferredCurrency: true,
        status: true,
        _count: { select: { properties: true, policies: true } },
      },
    });

    if (!customer) {
      throw new NotFoundException(`Customer ${customerId} not found`);
    }

    const rows = await this.prisma.transaction.findMany({
      where: {
        customerId,
        // NULL-safe exclusion. `notIn` alone compiles to SQL `NOT IN`, and
        // `NULL NOT IN (...)` is NULL, not true — so every app-captured row
        // (which has no legacySourceTable) silently vanished from the
        // statement while still showing in the movement browser. Rows the app
        // books must appear on the customer's statement, so the null case is
        // spelled out.
        OR: [
          { legacySourceTable: null },
          {
            legacySourceTable: {
              notIn: STATEMENT_EXCLUDED_SOURCE_TABLES as string[],
            },
          },
        ],
      },
      orderBy: [{ transactionDate: "asc" }, { id: "asc" }],
      select: {
        id: true,
        transactionDate: true,
        domain: true,
        amount: true,
        currency: true,
        reference: true,
        period: true,
        checkNumber: true,
        message: true,
        legacySourceTable: true,
        voidedAt: true,
        outstanding: true,
        type: { select: { nameEn: true, nameEs: true } },
      },
    });

    const running = new Map<string, Prisma.Decimal>();
    const movements = rows.map((r) => {
      const voided = r.voidedAt != null;
      const prev = running.get(r.currency) ?? new Prisma.Decimal(0);
      // Neither a voided row nor an outstanding (unpaid) one moves the running
      // balance — both show tagged, with the balance unchanged from the previous
      // live movement. Outstanding rows start counting once resolved.
      const next = voided || r.outstanding ? prev : prev.plus(r.amount);
      running.set(r.currency, next);
      return {
        id: r.id,
        transactionDate: r.transactionDate,
        domain: r.domain,
        amount: r.amount,
        currency: r.currency,
        direction: r.amount.lessThan(0) ? "charge" : "credit",
        reference: r.reference,
        period: r.period,
        checkNumber: r.checkNumber,
        message: r.message,
        source: r.legacySourceTable,
        type: r.type,
        voided,
        outstanding: r.outstanding,
        /** Balance in this row's currency after applying it. */
        balanceAfter: next.toFixed(2),
      };
    });
    movements.reverse();

    // Per-currency summary, and the same split by business line so the two
    // ledgers are visibly one statement without being illegally added up.
    const perCurrency = new Map<
      string,
      {
        currency: string;
        charges: Prisma.Decimal;
        credits: Prisma.Decimal;
        chargeCount: number;
        creditCount: number;
        count: number;
        first: Date | null;
        last: Date | null;
      }
    >();
    const perDomain = new Map<
      string,
      {
        domain: TransactionDomain;
        currency: string;
        charges: Prisma.Decimal;
        credits: Prisma.Decimal;
        count: number;
      }
    >();

    for (const r of rows) {
      // Voided rows never enter a total; outstanding rows don't either until
      // they're resolved (legacy SALDOS ULTIMO 0's `HAVING NOPAGO = 0`).
      if (r.voidedAt != null || r.outstanding) continue;
      const c =
        perCurrency.get(r.currency) ??
        {
          currency: r.currency,
          charges: new Prisma.Decimal(0),
          credits: new Prisma.Decimal(0),
          chargeCount: 0,
          creditCount: 0,
          count: 0,
          first: null as Date | null,
          last: null as Date | null,
        };
      c.count += 1;
      if (r.amount.lessThan(0)) {
        c.charges = c.charges.plus(r.amount);
        c.chargeCount += 1;
      } else if (r.amount.greaterThan(0)) {
        c.credits = c.credits.plus(r.amount);
        c.creditCount += 1;
      }
      if (!c.first) c.first = r.transactionDate;
      c.last = r.transactionDate;
      perCurrency.set(r.currency, c);

      const dk = `${r.domain}|${r.currency}`;
      const d =
        perDomain.get(dk) ??
        {
          domain: r.domain,
          currency: r.currency,
          charges: new Prisma.Decimal(0),
          credits: new Prisma.Decimal(0),
          count: 0,
        };
      d.count += 1;
      if (r.amount.lessThan(0)) d.charges = d.charges.plus(r.amount);
      else if (r.amount.greaterThan(0)) d.credits = d.credits.plus(r.amount);
      perDomain.set(dk, d);
    }

    // Where the money goes, per charge type — the question a customer asks
    // when they query their balance.
    const byType = new Map<
      string,
      { name: string; currency: string; total: Prisma.Decimal; count: number }
    >();
    for (const r of rows) {
      if (r.voidedAt != null || r.outstanding) continue;
      if (!r.amount.lessThan(0)) continue;
      const name = r.type?.nameEs || r.type?.nameEn || "Sin clasificar";
      const key = `${name}|${r.currency}`;
      const e =
        byType.get(key) ??
        { name, currency: r.currency, total: new Prisma.Decimal(0), count: 0 };
      e.total = e.total.plus(r.amount);
      e.count += 1;
      byType.set(key, e);
    }

    return {
      customer: {
        ...customer,
        propertyCount: customer._count.properties,
        policyCount: customer._count.policies,
      },
      summary: [...perCurrency.values()].map((c) => ({
        currency: c.currency,
        charges: c.charges.toFixed(2),
        credits: c.credits.toFixed(2),
        balance: c.charges.plus(c.credits).toFixed(2),
        chargeCount: c.chargeCount,
        creditCount: c.creditCount,
        count: c.count,
        firstMovement: c.first,
        lastMovement: c.last,
      })),
      byDomain: [...perDomain.values()].map((d) => ({
        domain: d.domain,
        currency: d.currency,
        charges: d.charges.toFixed(2),
        credits: d.credits.toFixed(2),
        balance: d.charges.plus(d.credits).toFixed(2),
        count: d.count,
      })),
      byType: [...byType.values()]
        .map((t) => ({
          name: t.name,
          currency: t.currency,
          total: t.total.toFixed(2),
          count: t.count,
        }))
        .sort((a, b) => Number(a.total) - Number(b.total)),
      movements,
    };
  }

  // --- writes (append + void; never edit or delete a booked row) ------------

  async createMovement(dto: CreateMovementDto) {
    const customer = await this.prisma.customer.findUnique({
      where: { id: dto.customerId },
      select: { id: true },
    });
    if (!customer) throw new NotFoundException(`Customer ${dto.customerId} not found`);
    const date = new Date(dto.transactionDate);
    if (isNaN(date.getTime())) throw new BadRequestException("Fecha inválida");

    return this.prisma.transaction.create({
      data: {
        customerId: dto.customerId,
        domain: dto.domain,
        amount: dto.amount,
        transactionDate: date,
        currency: dto.currency,
        typeId: dto.typeId,
        period: dto.period,
        reference: dto.reference,
        checkNumber: dto.checkNumber,
        message: dto.message,
        outstanding: dto.outstanding ?? false,
        captureSource: "MANUAL",
      },
    });
  }

  /**
   * Batch capture by check — many customers' receipts against one physical
   * check. One `$transaction`, so a bad line rejects the whole batch rather
   * than leaving a half-captured check that reconciles against nothing.
   *
   * Returns the check-level total alongside the rows so the UI can show it
   * against the physical check amount, which is the entire point of the legacy
   * flow this replaces (`CAPTURA *` feeding `EDITA CHEQUE COUNT`).
   *
   * ── Integration seam for OCR auto-capture (RECEIPT_CAPTURE_SPEC §2) ────────
   * This method is the SINGLE write path for multi-row capture, and the OCR
   * pipeline is required to post through it rather than writing `Transaction`
   * rows itself — one validation path, one audit trail. Three guarantees exist
   * for that caller specifically, and must not be broken:
   *
   *   1. `items[i]` corresponds to `dto.lines[i]`. Prisma's array
   *      `$transaction` preserves order, so the caller can zip the result back
   *      onto its own records — which is how `StatementDocument.postedTransactionId`
   *      gets set after a confirmed batch posts.
   *   2. `opts.refs[i]` stamps `captureRef` on row `i` (a `StatementDocument.id`).
   *      Re-posting a ref that already has a live row is rejected, so a
   *      double-clicked "confirm" or a retried job cannot double-charge a
   *      customer. Voided rows don't block a re-post — a corrected statement
   *      must be re-postable after its bad row is voided.
   *   3. `opts.source` records the capture path; it is NOT accepted over HTTP,
   *      so a client cannot label its hand-keyed rows as machine-captured.
   *
   * Everything the OCR module adds on top (batches, per-document status, the
   * review queue) lives in its own module; nothing about it needs to change
   * this signature.
   */
  async createBatch(dto: BatchCreateDto, opts: CaptureOptions = {}) {
    const date = new Date(dto.transactionDate);
    if (isNaN(date.getTime())) throw new BadRequestException("Fecha inválida");

    // Validate every customer up front, in one query — a per-line lookup inside
    // the transaction would be N round-trips and would fail halfway through.
    const ids = [...new Set(dto.lines.map((l) => l.customerId))];
    const found = await this.prisma.customer.findMany({
      where: { id: { in: ids } },
      select: { id: true },
    });
    if (found.length !== ids.length) {
      const known = new Set(found.map((c) => c.id));
      const missing = ids.filter((id) => !known.has(id));
      throw new BadRequestException(
        `Cliente(s) no encontrado(s): ${missing.join(", ")}`,
      );
    }

    // Duplicate-post guard (seam guarantee 2). Only live rows block: a voided
    // row means the earlier post was reversed, so the corrected statement must
    // be allowed through.
    const refs = (opts.refs ?? []).filter((r): r is string => !!r);
    if (refs.length) {
      const clash = await this.prisma.transaction.findMany({
        where: { captureRef: { in: refs }, voidedAt: null },
        select: { captureRef: true },
      });
      if (clash.length) {
        const dupes = [...new Set(clash.map((c) => c.captureRef))];
        throw new BadRequestException(
          `Ya existen movimientos para: ${dupes.join(", ")}`,
        );
      }
    }

    const currency = dto.currency ?? "MXN";
    const source = opts.source ?? "BATCH";
    const created = await this.prisma.$transaction(
      dto.lines.map((line, i) =>
        this.prisma.transaction.create({
          data: {
            customerId: line.customerId,
            domain: dto.domain,
            amount: line.amount,
            transactionDate: date,
            currency,
            typeId: dto.typeId,
            checkNumber: dto.checkNumber,
            period: line.period,
            reference: line.reference,
            message: line.message,
            outstanding: line.outstanding ?? false,
            captureSource: source,
            captureRef: opts.refs?.[i],
          },
        }),
      ),
    );

    // Outstanding lines are captured but unfunded, so they don't belong in the
    // figure staff reconcile against the physical check.
    const total = created.reduce(
      (sum, t) => (t.outstanding ? sum : sum.plus(t.amount)),
      new Prisma.Decimal(0),
    );

    return {
      /** Parallel to `dto.lines` — see seam guarantee 1. */
      items: created,
      checkNumber: dto.checkNumber,
      currency,
      source,
      count: created.length,
      outstandingCount: created.filter((t) => t.outstanding).length,
      total: total.toFixed(2),
    };
  }

  /**
   * Resolve an outstanding row: the check was finally cut. Takes the resolution
   * date and check number and clears the flag, so the amount starts counting
   * toward the balance. Legacy: "se actualiza registro con fecha del día y el
   * cheque a pagar y quitas outstanding".
   */
  async resolveOutstanding(id: string, dto: ResolveOutstandingDto) {
    const tx = await this.prisma.transaction.findUnique({
      where: { id },
      select: { id: true, voidedAt: true, outstanding: true },
    });
    if (!tx) throw new NotFoundException(`Transaction ${id} not found`);
    if (tx.voidedAt) {
      throw new BadRequestException("El movimiento está anulado");
    }
    if (!tx.outstanding) {
      throw new BadRequestException("El movimiento no está pendiente de pago");
    }
    const date = new Date(dto.resolvedDate);
    if (isNaN(date.getTime())) throw new BadRequestException("Fecha inválida");

    return this.prisma.transaction.update({
      where: { id },
      data: {
        outstanding: false,
        checkNumber: dto.checkNumber,
        transactionDate: date,
      },
    });
  }

  /**
   * Every live movement cut against one check, plus its total — the
   * reconciliation view replacing `EDITA CHEQUE ALF/COUNT/NUM` and
   * `REPORTE POR CHEQUE`. Voided rows are dropped entirely (they reconcile
   * against nothing); outstanding rows are listed but excluded from the total,
   * since the check didn't fund them.
   */
  async byCheck(checkNumber: string) {
    const rows = await this.prisma.transaction.findMany({
      where: { checkNumber, voidedAt: null },
      orderBy: [{ transactionDate: "asc" }, { id: "asc" }],
      select: {
        id: true,
        transactionDate: true,
        domain: true,
        amount: true,
        currency: true,
        reference: true,
        period: true,
        message: true,
        outstanding: true,
        type: { select: { nameEn: true, nameEs: true } },
        customer: { select: { id: true, name: true, nameSource: true } },
      },
    });

    // Per currency: a check is one currency in practice, but the ledger has
    // both and this module never sums across them.
    const totals = new Map<string, { currency: string; total: Prisma.Decimal; count: number }>();
    for (const r of rows) {
      if (r.outstanding) continue;
      const e =
        totals.get(r.currency) ??
        { currency: r.currency, total: new Prisma.Decimal(0), count: 0 };
      e.total = e.total.plus(r.amount);
      e.count += 1;
      totals.set(r.currency, e);
    }

    return {
      checkNumber,
      items: rows.map((r) => ({
        id: r.id,
        transactionDate: r.transactionDate,
        domain: r.domain,
        amount: r.amount,
        currency: r.currency,
        direction: r.amount.lessThan(0) ? "charge" : "credit",
        reference: r.reference,
        period: r.period,
        message: r.message,
        outstanding: r.outstanding,
        type: r.type,
        customerId: r.customer.id,
        customerName: r.customer.name,
        customerNameSource: r.customer.nameSource,
      })),
      count: rows.length,
      outstandingCount: rows.filter((r) => r.outstanding).length,
      totals: [...totals.values()].map((t) => ({
        currency: t.currency,
        total: t.total.toFixed(2),
        count: t.count,
      })),
    };
  }

  /** Reverse a movement by marking it voided; it stops counting toward totals. */
  async voidMovement(id: string, userId: string) {
    const tx = await this.prisma.transaction.findUnique({
      where: { id },
      select: { id: true, voidedAt: true },
    });
    if (!tx) throw new NotFoundException(`Transaction ${id} not found`);
    if (tx.voidedAt) throw new BadRequestException("El movimiento ya está anulado");
    return this.prisma.transaction.update({
      where: { id },
      data: { voidedAt: new Date(), voidedById: userId },
    });
  }
}
