import { InvoiceLineKind, InvoiceStatus, InvoiceType, PaymentRequestStatus, PaymentStatus, Prisma, Role } from '@prisma/client';
import { db, prismaUnscoped } from '../../lib/prisma';
import { getTenantIdOrThrow, runWithTenantContext } from '../../lib/tenantContext';
import { env } from '../../config/env';
import { BadRequestError, ConflictError, NotFoundError, ServiceUnavailableError } from '../../lib/errors';
import * as rzp from '../../lib/razorpay';
import * as notify from '../notifications/notifications.service';
import { onlineCollectionFor } from './payment-account.service';
import { effectiveStatus, markPaid, publicRequest } from './payment-requests.service';

/**
 * Collection hardening (Sprint 3): the parts that make online money safe
 * to switch on for real.
 *
 *   Reconciliation — webhooks get lost. Every few minutes the job asks
 *   Razorpay about links we still think are pending, and records any that
 *   were paid through the same path the webhook uses. Nightly it compares a
 *   whole day of gateway payments with the books and lists the difference.
 *
 *   Virtual accounts — a bank-account number and UPI id that belong to one
 *   student, so a plain NEFT/IMPS/UPI transfer lands on their invoice with
 *   the UTR as the reference, and nobody has to match a bank statement.
 *
 *   Refunds — the office asks Razorpay for one; the webhook reverses the
 *   payment in the books when it is processed.
 *
 *   Dashboard — what came in online today and this month, the links still
 *   open, failed attempts, what settles when, and what did not reconcile.
 */

const MIN = 60_000;
const DAY_MS = 86_400_000;

// ── Reconciliation ─────────────────────────────────────────────────────────

export interface LinkReconcileResult {
  checked: number;
  recovered: number;
  expired: number;
  errors: number;
}

/**
 * Re-check links Razorpay may have told us about while we were not
 * listening: pending ones older than five minutes, and ones that expired
 * in the last 48 hours (a payment can land right at expiry). A paid link is
 * recorded exactly as a webhook would record it — same lock, same checks.
 */
export async function reconcilePendingLinks(tenantId?: string, now = new Date(), minAgeMs = 5 * MIN): Promise<LinkReconcileResult> {
  const result: LinkReconcileResult = { checked: 0, recovered: 0, expired: 0, errors: 0 };
  if (!rzp.isRazorpayConfigured()) return result;
  const rows = await prismaUnscoped.paymentRequest.findMany({
    where: {
      ...(tenantId ? { tenantId } : {}),
      linkId: { not: null },
      OR: [
        { status: PaymentRequestStatus.PENDING, createdAt: { lt: new Date(now.getTime() - minAgeMs) }, OR: [{ lastPolledAt: null }, { lastPolledAt: { lt: new Date(now.getTime() - Math.min(9 * MIN, minAgeMs)) } }] },
        { status: PaymentRequestStatus.EXPIRED, expiresAt: { gt: new Date(now.getTime() - 48 * 60 * MIN) }, reconciledAt: null, OR: [{ lastPolledAt: null }, { lastPolledAt: { lt: new Date(now.getTime() - 60 * MIN) } }] },
      ],
    },
    orderBy: { createdAt: 'asc' },
    take: 200,
  });

  for (const row of rows) {
    result.checked++;
    try {
      const link = await rzp.fetchPaymentLink(row.linkId!);
      const captured = (link.payments ?? []).filter((p) => p.status === 'captured');
      if (link.status === 'paid' && captured.length > 0) {
        const p = captured[0];
        const outcome = await markPaid(row, { id: p.payment_id, amount: p.amount, status: p.status, method: p.method, created_at: p.created_at, notes: { paymentRequestId: row.id } }, { id: link.id, reference_id: link.reference_id });
        if (outcome === 'recorded') result.recovered++;
        await prismaUnscoped.paymentRequest.update({ where: { id: row.id }, data: { lastPolledAt: now, reconciledAt: now } });
      } else if (link.status === 'expired' || link.status === 'cancelled' || (row.status === PaymentRequestStatus.PENDING && row.expiresAt.getTime() < now.getTime() - 60 * MIN)) {
        const status = link.status === 'cancelled' ? PaymentRequestStatus.CANCELLED : PaymentRequestStatus.EXPIRED;
        const data: Prisma.PaymentRequestUpdateInput = { lastPolledAt: now };
        if (row.status === PaymentRequestStatus.PENDING) {
          data.status = status;
          result.expired++;
        }
        // An expired link that Razorpay confirms unpaid needs no second look.
        if (row.status === PaymentRequestStatus.EXPIRED || status === PaymentRequestStatus.EXPIRED) data.reconciledAt = now;
        await prismaUnscoped.paymentRequest.update({ where: { id: row.id }, data });
      } else {
        await prismaUnscoped.paymentRequest.update({ where: { id: row.id }, data: { lastPolledAt: now } });
      }
    } catch (err) {
      result.errors++;
      // eslint-disable-next-line no-console
      console.error(`[reconcile] link ${row.linkId} failed:`, err instanceof Error ? err.message : err);
    }
  }
  return result;
}

export interface MissingPayment {
  gatewayPaymentId: string;
  amount: number;
  method: string | null;
  createdAt: string;
  paymentRequestId: string | null;
  invoiceId: string | null;
  note: string;
}

/**
 * One day of gateway payments against the books. Anything captured at
 * Razorpay that the books do not have is listed; where the payment names a
 * request of ours that is still open, it is recorded on the spot. One run
 * row per tenant with activity, so each office sees only its own list.
 */
export async function reconcileDay(forDate: string, tenantId?: string): Promise<{ date: string; tenants: number; checked: number; recovered: number; missing: number }> {
  const summary = { date: forDate, tenants: 0, checked: 0, recovered: 0, missing: 0 };
  if (!rzp.isRazorpayConfigured()) return summary;
  const start = new Date(`${forDate}T00:00:00Z`);
  const from = Math.floor(start.getTime() / 1000);
  const to = from + 86_400 - 1;
  const payments = (await rzp.listPayments(from, to)).filter((p) => p.status === 'captured' || p.status === 'refunded');
  summary.checked = payments.length;

  const byTenant = new Map<string, MissingPayment[]>();
  const recovered = new Map<string, number>();
  for (const p of payments) {
    const notes = p.notes && !Array.isArray(p.notes) ? p.notes : {};
    const existing = await prismaUnscoped.payment.findUnique({ where: { gatewayPaymentId: p.id }, select: { id: true, tenantId: true } });
    if (existing) continue;
    let tid = notes.tenantId ?? null;
    let requestId = notes.paymentRequestId ?? null;
    const row = requestId ? await prismaUnscoped.paymentRequest.findUnique({ where: { id: requestId } }) : null;
    if (row) tid = row.tenantId;
    if (tenantId && tid !== tenantId) continue;
    if (!tid) continue; // not ours to show anyone: a payment outside HostelCare's notes
    if (row && row.status !== PaymentRequestStatus.PAID && p.status === 'captured') {
      const outcome = await markPaid(row, { id: p.id, amount: p.amount, status: p.status, method: p.method, order_id: p.order_id ?? undefined, created_at: p.created_at, notes }, null);
      if (outcome === 'recorded') {
        recovered.set(tid, (recovered.get(tid) ?? 0) + 1);
        await prismaUnscoped.paymentRequest.update({ where: { id: row.id }, data: { reconciledAt: new Date(), lastPolledAt: new Date() } });
        continue;
      }
    }
    if (!byTenant.has(tid)) byTenant.set(tid, []);
    byTenant.get(tid)!.push({
      gatewayPaymentId: p.id,
      amount: Math.round(p.amount / 100),
      method: p.method ?? null,
      createdAt: new Date(p.created_at * 1000).toISOString(),
      paymentRequestId: requestId,
      invoiceId: notes.invoiceId ?? null,
      note: row ? `Request is ${row.status.toLowerCase()}; gateway says ${p.status}` : requestId ? 'Request not found in HostelCare' : p.status === 'refunded' ? 'Refunded at the gateway' : 'No HostelCare request on this payment',
    });
  }

  const tenants = new Set([...byTenant.keys(), ...recovered.keys()]);
  if (tenantId) tenants.add(tenantId);
  for (const tid of tenants) {
    const missing = byTenant.get(tid) ?? [];
    summary.missing += missing.length;
    summary.recovered += recovered.get(tid) ?? 0;
    await prismaUnscoped.reconciliationRun.create({
      data: { tenantId: tid, kind: 'DAILY', forDate, checked: payments.length, recovered: recovered.get(tid) ?? 0, missing: missing as unknown as Prisma.InputJsonValue, finishedAt: new Date() },
    });
  }
  summary.tenants = tenants.size;
  return summary;
}

/** The office's "run now": both passes for this tenant, today and yesterday. */
export async function reconcileNow(): Promise<{ links: LinkReconcileResult; days: Awaited<ReturnType<typeof reconcileDay>>[] }> {
  const tenantId = getTenantIdOrThrow();
  if (!rzp.isRazorpayConfigured()) throw new ServiceUnavailableError('Online payments are not configured on this server yet', 'GATEWAY_NOT_CONFIGURED');
  // The office pressed the button: check every open link now, however young.
  const links = await reconcilePendingLinks(tenantId, new Date(), 0);
  const today = new Date().toISOString().slice(0, 10);
  const yesterday = new Date(Date.now() - DAY_MS).toISOString().slice(0, 10);
  const days = [await reconcileDay(yesterday, tenantId), await reconcileDay(today, tenantId)];
  return { links, days };
}

export async function listReconciliationRuns(date?: string, limit = 10) {
  return db.reconciliationRun.findMany({ where: { kind: 'DAILY', ...(date ? { forDate: date } : {}) }, orderBy: { startedAt: 'desc' }, take: limit });
}

// ── Scheduler ───────────────────────────────────────────────────────────────

let timer: NodeJS.Timeout | null = null;
let lastDailyFor: string | null = null;

/**
 * Every ten minutes: the pending-link pass. Once a day, after 02:00 UTC
 * (07:30 IST, before the office opens): yesterday's compare. Idempotent, so
 * several instances running it is merely wasteful, never wrong.
 */
export function startCollectionScheduler(): void {
  if (timer || env.DISABLE_COLLECTION_JOBS || !rzp.isRazorpayConfigured()) return;
  const tick = async () => {
    try {
      await reconcilePendingLinks();
    } catch (err) {
      // eslint-disable-next-line no-console
      console.error('[reconcile] link pass failed:', err);
    }
    const now = new Date();
    const yesterday = new Date(now.getTime() - DAY_MS).toISOString().slice(0, 10);
    if (now.getUTCHours() >= 2 && lastDailyFor !== yesterday) {
      const already = await prismaUnscoped.reconciliationRun.findFirst({ where: { kind: 'DAILY', forDate: yesterday } });
      lastDailyFor = yesterday;
      if (!already) {
        try {
          await reconcileDay(yesterday);
        } catch (err) {
          // eslint-disable-next-line no-console
          console.error('[reconcile] daily pass failed:', err);
        }
      }
    }
  };
  timer = setInterval(() => void tick(), 10 * MIN);
  timer.unref();
  setTimeout(() => void tick(), 45_000).unref();
}

// ── Refunds ─────────────────────────────────────────────────────────────────

/**
 * Ask Razorpay to refund an online payment, in full or in part. The books
 * change only when Razorpay says it is processed (webhook or the next
 * reconciliation), so the office sees "requested" until then.
 */
export async function requestRefund(paymentId: string, input: { amount?: number; reason: string }, requestedById: string) {
  const payment = await db.payment.findFirst({ where: { id: paymentId }, include: { invoice: { select: { periodMonth: true, studentProfileId: true } } } });
  if (!payment) throw new NotFoundError('Payment not found');
  if (!payment.gatewayPaymentId) throw new BadRequestError('Only online payments can be refunded through Razorpay. Reverse an offline payment with "Mark failed".', 'NOT_ONLINE');
  if (payment.status === PaymentStatus.FAILED) throw new ConflictError('This payment has already been reversed');
  if (payment.refundStatus === 'REQUESTED' || payment.refundStatus === 'PROCESSED') throw new ConflictError(`A refund is already ${payment.refundStatus.toLowerCase()} for this payment`);
  const amount = input.amount ?? payment.amount;
  if (amount <= 0 || amount > payment.amount) throw new BadRequestError(`The refund must be between ₹1 and ₹${payment.amount}`);
  // What the student paid may include a gateway charge they bore; a full
  // refund returns the whole captured amount, a part refund the rupees asked.
  const capturedPaise = (payment.gatewayPayload as { amount?: number } | null)?.amount ?? payment.amount * 100;
  const refundPaise = amount === payment.amount ? capturedPaise : amount * 100;
  let refund: rzp.RzpRefund;
  try {
    refund = await rzp.createRefund(payment.gatewayPaymentId, refundPaise, { tenantId: payment.tenantId, paymentId: payment.id, invoiceId: payment.invoiceId, reason: input.reason.slice(0, 120), requestedBy: requestedById });
  } catch (err) {
    if (err instanceof rzp.RazorpayError) throw new ServiceUnavailableError(`Razorpay refused the refund: ${err.message}`, 'GATEWAY_ERROR');
    throw err;
  }
  const updated = await db.payment.update({
    where: { id: payment.id },
    data: { refundId: refund.id, refundStatus: refund.status === 'processed' ? 'PROCESSED' : 'REQUESTED', refundAmount: amount, refundRequestedAt: new Date() },
  });
  return updated;
}

// ── Virtual accounts (Smart Collect) ────────────────────────────────────────

export function publicVirtualAccount(va: { accountNumber: string; ifsc: string; bankName: string | null; upiId: string | null; status: string; createdAt: Date } | null) {
  if (!va) return null;
  return { accountNumber: va.accountNumber, ifsc: va.ifsc, bankName: va.bankName, upiId: va.upiId, status: va.status, createdAt: va.createdAt };
}

export async function getVirtualAccount(studentProfileId: string) {
  return publicVirtualAccount(await db.virtualAccount.findFirst({ where: { studentProfileId, status: 'active' } }));
}

/** Create the student's virtual account at Razorpay, or return the one they have. */
export async function ensureVirtualAccount(studentProfileId: string) {
  const tenantId = getTenantIdOrThrow();
  const existing = await db.virtualAccount.findFirst({ where: { studentProfileId, status: 'active' } });
  if (existing) return publicVirtualAccount(existing);
  const settings = await db.tenantSettings.findFirst({ select: { virtualAccountsEnabled: true, billingFirmName: true } });
  if (!settings?.virtualAccountsEnabled) throw new BadRequestError('Bank-transfer accounts are switched off for this hostel', 'VIRTUAL_ACCOUNTS_OFF');
  const collection = await onlineCollectionFor(tenantId);
  if (!collection.enabled) throw new BadRequestError('Switch online payments on (with an active bank account) before issuing bank-transfer accounts', collection.reason ?? 'ONLINE_COLLECTION_OFF');
  const student = await db.studentProfile.findFirst({ where: { id: studentProfileId }, include: { user: { select: { name: true } } } });
  if (!student) throw new NotFoundError('Student not found');
  let va: rzp.VirtualAccountEntity;
  try {
    va = await rzp.createVirtualAccount({
      description: `${settings.billingFirmName?.trim() || 'HostelCare'} · ${student.user.name}`,
      notes: { tenantId, studentProfileId },
    });
  } catch (err) {
    if (err instanceof rzp.RazorpayError) throw new ServiceUnavailableError(`Razorpay could not create the account: ${err.message}`, 'GATEWAY_ERROR');
    throw err;
  }
  const bank = va.receivers.find((r) => r.account_number);
  const vpa = va.receivers.find((r) => r.address);
  if (!bank?.account_number || !bank.ifsc) throw new ServiceUnavailableError('Razorpay returned a virtual account without bank details', 'GATEWAY_ERROR');
  const row = await db.virtualAccount.create({
    data: { tenantId, studentProfileId, razorpayVaId: va.id, accountNumber: bank.account_number, ifsc: bank.ifsc, bankName: bank.bank_name ?? null, upiId: vpa?.address ?? null, status: 'active' },
  });
  return publicVirtualAccount(row);
}

export async function closeVirtualAccountFor(studentProfileId: string) {
  const row = await db.virtualAccount.findFirst({ where: { studentProfileId, status: 'active' } });
  if (!row) return null;
  try {
    await rzp.closeVirtualAccount(row.razorpayVaId);
  } catch {
    /* already closed, or gateway down — the row still closes; the job can retry */
  }
  return publicVirtualAccount(await db.virtualAccount.update({ where: { id: row.id }, data: { status: 'closed', closedAt: new Date() } }));
}

interface VaPaymentEntity {
  id: string;
  amount?: number;
  method?: string;
  status?: string;
  created_at?: number;
  acquirer_data?: Record<string, string | null> | null;
  bank_transfer?: { utr?: string } | null;
}

/**
 * Money arrived in a student's virtual account. Applied to their open
 * invoices oldest first; anything beyond what is owed becomes an "advance"
 * invoice settled on the spot, so the money is visible and can be adjusted
 * against the next bill. The UTR / RRN is the reference. Then the amount is
 * moved to the hostel's linked account through Route.
 */
export async function applyVirtualAccountCredit(razorpayVaId: string, payment: VaPaymentEntity): Promise<string> {
  const va = await prismaUnscoped.virtualAccount.findUnique({ where: { razorpayVaId } });
  if (!va) return 'ignored:unknown_virtual_account';
  if (await prismaUnscoped.payment.findUnique({ where: { gatewayPaymentId: payment.id } })) return 'duplicate:payment_exists';
  const amount = Math.round((payment.amount ?? 0) / 100);
  if (amount <= 0) return 'ignored:zero';
  const tenantId = va.tenantId;
  const student = await prismaUnscoped.studentProfile.findUnique({ where: { id: va.studentProfileId }, select: { userId: true } });
  const ctx = { tenantId, userId: student?.userId ?? 'webhook', role: Role.HOSTEL_ADMIN };
  const reference = payment.acquirer_data?.utr || payment.bank_transfer?.utr || payment.acquirer_data?.rrn || payment.acquirer_data?.bank_transaction_id || payment.id;
  const paidAt = payment.created_at ? new Date(payment.created_at * 1000) : new Date();
  const method = 'VIRTUAL_ACCOUNT';

  const created = await runWithTenantContext(ctx, () =>
    db.$transaction(async (tx) => {
      const open = await tx.invoice.findMany({
        where: { studentProfileId: va.studentProfileId, status: { not: InvoiceStatus.PAID } },
        include: { payments: { where: { status: PaymentStatus.CLEARED }, select: { amount: true } } },
        orderBy: [{ dueDate: 'asc' }, { createdAt: 'asc' }],
      });
      let left = amount;
      const ids: string[] = [];
      let first = true;
      for (const inv of open) {
        if (left <= 0) break;
        const balance = inv.amount - inv.payments.reduce((n, p) => n + p.amount, 0);
        if (balance <= 0) continue;
        const part = Math.min(balance, left);
        const p = await tx.payment.create({
          data: {
            tenantId,
            invoiceId: inv.id,
            amount: part,
            method,
            reference,
            note: `Bank transfer to virtual account${payment.method ? ` (${payment.method.toUpperCase()})` : ''}`,
            recordedById: ctx.userId,
            paidAt,
            gatewayProvider: 'razorpay',
            // The gateway id can sit on one row only; the others carry the UTR.
            gatewayPaymentId: first ? payment.id : null,
            gatewayPayload: { id: payment.id, amount: payment.amount ?? null, method: payment.method ?? null, created_at: payment.created_at ?? null, virtualAccountId: razorpayVaId, applied: part },
            feeAmount: 0,
            netAmount: part,
          },
          select: { id: true },
        });
        first = false;
        ids.push(p.id);
        await tx.invoice.update({ where: { id: inv.id }, data: part >= balance ? { status: InvoiceStatus.PAID, paidAt } : { status: InvoiceStatus.PARTIAL } });
        left -= part;
      }
      if (left > 0) {
        // Nothing (more) to apply it to: an advance, settled on the spot.
        const period = paidAt.toISOString().slice(0, 7);
        const adv = await tx.invoice.create({
          data: {
            tenantId,
            studentProfileId: va.studentProfileId,
            type: InvoiceType.OTHER,
            periodMonth: period,
            cycleMonths: 1,
            amount: left,
            status: InvoiceStatus.PAID,
            paidAt,
            dueDate: paidAt,
            lines: { create: [{ tenantId, kind: InvoiceLineKind.CHARGE, label: 'Advance received by bank transfer — adjust against the next invoice', amount: left, meta: { advance: true, reference } }] },
          },
          select: { id: true },
        });
        const p = await tx.payment.create({
          data: { tenantId, invoiceId: adv.id, amount: left, method, reference, note: 'Advance by bank transfer to virtual account', recordedById: ctx.userId, paidAt, gatewayProvider: 'razorpay', gatewayPaymentId: first ? payment.id : null, gatewayPayload: { id: payment.id, amount: payment.amount ?? null, virtualAccountId: razorpayVaId, applied: left, advance: true }, feeAmount: 0, netAmount: left },
          select: { id: true },
        });
        ids.push(p.id);
      }
      return ids;
    }),
  );

  // Route the money to the hostel. Best effort: a failure here is a
  // settlement problem for the dashboard, never a reason to lose the booking.
  const collection = await onlineCollectionFor(tenantId);
  if (collection.account?.razorpayAccountId && env.RAZORPAY_ROUTE_REQUIRED) {
    try {
      const t = await rzp.transferPayment(payment.id, { account: collection.account.razorpayAccountId, amountPaise: amount * 100, notes: { tenantId, virtualAccountId: razorpayVaId } });
      const transferId = t.items?.[0]?.id ?? null;
      if (transferId && created[0]) {
        const row = await prismaUnscoped.payment.findUnique({ where: { id: created[0] } });
        await prismaUnscoped.payment.update({ where: { id: created[0] }, data: { gatewayPayload: { ...((row?.gatewayPayload as object) ?? {}), transferId } } });
      }
    } catch (err) {
      // eslint-disable-next-line no-console
      console.error(`[virtual-account] transfer for ${payment.id} failed:`, err instanceof Error ? err.message : err);
    }
  }
  for (const id of created) notify.inBackground('virtual account credit', () => notify.notifyPaymentRecorded(tenantId, id));
  return `recorded:${created.length}`;
}

// ── Dashboard ───────────────────────────────────────────────────────────────

/** T+2 working days from the capture — Razorpay's standard cycle. An estimate, labelled as such by the client. */
export function expectedSettlementDate(paidAt: Date): string {
  const d = new Date(Date.UTC(paidAt.getUTCFullYear(), paidAt.getUTCMonth(), paidAt.getUTCDate()));
  let left = 2;
  while (left > 0) {
    d.setUTCDate(d.getUTCDate() + 1);
    const dow = d.getUTCDay();
    if (dow !== 0 && dow !== 6) left--;
  }
  return d.toISOString().slice(0, 10);
}

export async function collectionsDashboard(period: string, propertyId?: string) {
  const [y, m] = period.split('-').map(Number);
  const monthStart = new Date(Date.UTC(y, m - 1, 1));
  const monthEnd = new Date(Date.UTC(y, m, 1));
  const now = new Date();
  const dayStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));
  const studentScope = propertyId ? { student: { propertyId } } : {};
  const online = { gatewayProvider: 'razorpay' as const };
  const studentSel = { select: { id: true, user: { select: { name: true } }, bed: { select: { label: true, room: { select: { number: true } } } } } } as const;

  const [monthPayments, todayPayments, pending, failed, recentRuns, refunds] = await Promise.all([
    db.payment.findMany({ where: { ...online, paidAt: { gte: monthStart, lt: monthEnd }, invoice: studentScope }, select: { id: true, amount: true, method: true, status: true, feeAmount: true, netAmount: true, paidAt: true, reference: true, refundStatus: true, invoice: { select: { id: true, periodMonth: true, type: true, student: studentSel } } }, orderBy: { paidAt: 'desc' } }),
    db.payment.findMany({ where: { ...online, status: PaymentStatus.CLEARED, paidAt: { gte: dayStart }, invoice: studentScope }, select: { amount: true, method: true } }),
    db.paymentRequest.findMany({ where: { status: PaymentRequestStatus.PENDING, expiresAt: { gt: now }, ...(propertyId ? { student: { propertyId } } : {}) }, include: { student: studentSel, invoice: { select: { periodMonth: true, type: true } } }, orderBy: { createdAt: 'asc' }, take: 100 }),
    db.paymentRequest.findMany({ where: { latestError: { not: null }, status: { not: PaymentRequestStatus.PAID }, updatedAt: { gte: new Date(now.getTime() - 7 * DAY_MS) }, ...(propertyId ? { student: { propertyId } } : {}) }, include: { student: studentSel, invoice: { select: { periodMonth: true, type: true } } }, orderBy: { updatedAt: 'desc' }, take: 50 }),
    db.reconciliationRun.findMany({ where: { kind: 'DAILY' }, orderBy: { startedAt: 'desc' }, take: 7 }),
    db.payment.findMany({ where: { refundId: { not: null }, invoice: studentScope }, select: { id: true, amount: true, refundAmount: true, refundStatus: true, refundRequestedAt: true, refundedAt: true, invoice: { select: { periodMonth: true, student: studentSel } } }, orderBy: { refundRequestedAt: 'desc' }, take: 20 }),
  ]);

  const cleared = monthPayments.filter((p) => p.status === PaymentStatus.CLEARED);
  const sum = (xs: { amount: number }[]) => xs.reduce((n, p) => n + p.amount, 0);
  const links = cleared.filter((p) => p.method === 'ONLINE');
  const transfers = cleared.filter((p) => p.method === 'VIRTUAL_ACCOUNT');

  // Settlements: cleared online money from the last ten days, grouped by the day it should land.
  const settle = new Map<string, number>();
  for (const p of cleared) {
    if (p.paidAt.getTime() < now.getTime() - 10 * DAY_MS) continue;
    const d = expectedSettlementDate(p.paidAt);
    settle.set(d, (settle.get(d) ?? 0) + (p.netAmount ?? p.amount));
  }

  const latestRun = recentRuns[0] ?? null;
  const missing = (latestRun?.missing as MissingPayment[] | null) ?? [];
  const studentOf = (s: { user: { name: string }; bed: { label: string; room: { number: string } } | null } | null) => (s ? { name: s.user.name, room: s.bed ? `Room ${s.bed.room.number} · Bed ${s.bed.label}` : null } : null);

  return {
    period,
    today: { count: todayPayments.length, amount: sum(todayPayments) },
    month: {
      count: cleared.length,
      amount: sum(cleared),
      fees: cleared.reduce((n, p) => n + p.feeAmount, 0),
      net: cleared.reduce((n, p) => n + (p.netAmount ?? p.amount), 0),
      byLink: { count: links.length, amount: sum(links) },
      byTransfer: { count: transfers.length, amount: sum(transfers) },
    },
    pendingLinks: pending.map((r) => ({ ...publicRequest(r), ageMinutes: Math.round((now.getTime() - r.createdAt.getTime()) / MIN), student: studentOf(r.student), invoice: r.invoice })),
    failedAttempts: failed.map((r) => ({ id: r.id, status: effectiveStatus(r), error: r.latestError, at: r.updatedAt, grossAmount: r.grossAmount, student: studentOf(r.student), invoice: r.invoice })),
    recent: monthPayments.slice(0, 30).map((p) => ({ id: p.id, amount: p.amount, method: p.method, status: p.status, reference: p.reference, paidAt: p.paidAt, feeAmount: p.feeAmount, netAmount: p.netAmount, refundStatus: p.refundStatus, student: studentOf(p.invoice.student), invoice: { id: p.invoice.id, periodMonth: p.invoice.periodMonth, type: p.invoice.type } })),
    settlements: [...settle.entries()].sort(([a], [b]) => a.localeCompare(b)).map(([date, amount]) => ({ date, amount, estimated: true })),
    refunds: refunds.map((p) => ({ id: p.id, amount: p.amount, refundAmount: p.refundAmount, refundStatus: p.refundStatus, requestedAt: p.refundRequestedAt, refundedAt: p.refundedAt, student: studentOf(p.invoice.student), periodMonth: p.invoice.periodMonth })),
    reconciliation: latestRun ? { forDate: latestRun.forDate, ranAt: latestRun.finishedAt ?? latestRun.startedAt, checked: latestRun.checked, recovered: latestRun.recovered, missing } : null,
  };
}
