import { InvoiceLineKind, InvoiceStatus, InvoiceType, MessBillingMode, PaymentStatus, PricingMode, Prisma, RateBasis } from '@prisma/client';
import { db, prismaUnscoped } from '../../lib/prisma';
import { billableDays } from '../mess/calendar.service';
import { getTenantIdOrThrow } from '../../lib/tenantContext';
import { monthRange, dueDateFor, periodIndex, periodEnd } from '../../lib/period';
import { BadRequestError, ConflictError, NotFoundError } from '../../lib/errors';
import { paginated, type Pagination } from '../../lib/pagination';
import { BILLING_RULE_FIELDS, gstLine, gstSplit, pendingChargeLines, prorataOnMoveIn, recurringChargeLines } from './billing-rules.service';

/** Only money that actually arrived counts. A reversed payment stays on record but is worth nothing. */
const CLEARED = { status: PaymentStatus.CLEARED } as const;

/**
 * The lines under an invoice's total — rent, charges, late fee, GST, a
 * move-out credit. Additive: invoices from before billing rules have none,
 * and clients fall back to the single amount. Oldest first, so the ledger
 * reads in the order it was written.
 */
const invoiceLinesInclude = {
  lines: { orderBy: { createdAt: 'asc' as const }, select: { id: true, kind: true, label: true, amount: true, meta: true, createdAt: true } },
} as const;

const invoiceStudentInclude = {
  ...invoiceLinesInclude,
  student: {
    include: {
      // phone (27 Oct 2026): a dormitory's receipt prints it. Additive.
      user: { select: { id: true, name: true, email: true, phone: true } },
      // Printed on the receipt. Additive: older clients ignore it.
      bed: { select: { label: true, room: { select: { number: true, floor: true } } } },
    },
  },
} as const;

/**
 * A row of the staff invoice list. The full payment rows, not just amounts:
 * the invoice drawer lists each payment with its method and date.
 */
const invoiceListInclude = {
  ...invoiceStudentInclude,
  payments: {
    where: CLEARED,
    select: { id: true, amount: true, method: true, note: true, reference: true, paidAt: true },
    orderBy: { paidAt: 'asc' as const },
  },
} as const;

/**
 * What a single student owes in rent for a month, and when it falls due.
 *
 * The property's pricing mode decides where rent comes from:
 *   BED  — the student's own bed is priced; every occupant is billed.
 *   ROOM — the room is let as a unit, so ONLY the room's responsible student
 *          (Room.billedStudentProfileId) is billed, for the room's rent. Their
 *          roommates owe nothing for rent; that is the intended behaviour, not
 *          a missed invoice.
 *
 * Returns null when the student should not be invoiced at all.
 */
function resolveRent(student: {
  id: string;
  bed: { monthlyRent: number | null; rentDueDay: number | null; room: RoomForRent } | null;
}): { monthly: number; dueDay: number; cycleMonths: number } | null {
  const bed = student.bed;
  if (!bed) return null;
  const room = bed.room;
  // A per-night room (dormitories) is invoiced by its stay, not by the month.
  if (room.rateBasis === RateBasis.DAILY) return null;
  // The fee cycle is the room's, whichever way the room is priced.
  const cycleMonths = Math.max(1, room.feeCycleMonths ?? 1);

  if (room.property.pricingMode === PricingMode.ROOM) {
    if (room.billedStudentProfileId !== student.id) return null;
    if (!room.monthlyRent || room.monthlyRent <= 0) return null;
    return { monthly: room.monthlyRent, dueDay: room.rentDueDay ?? bed.rentDueDay ?? 5, cycleMonths };
  }

  if (!bed.monthlyRent || bed.monthlyRent <= 0) return null;
  return { monthly: bed.monthlyRent, dueDay: bed.rentDueDay ?? 5, cycleMonths };
}

interface RoomForRent {
  monthlyRent: number | null;
  rentDueDay: number | null;
  feeCycleMonths: number;
  rateBasis?: RateBasis;
  billedStudentProfileId: string | null;
  property: { pricingMode: PricingMode };
}

/** Whether an invoice spanning [start, start+cycle) overlaps [period, period+cycle). */
function cyclesOverlap(aStart: string, aMonths: number, bStart: string, bMonths: number): boolean {
  const a0 = periodIndex(aStart);
  const a1 = a0 + Math.max(1, aMonths) - 1;
  const b0 = periodIndex(bStart);
  const b1 = b0 + Math.max(1, bMonths) - 1;
  return a0 <= b1 && b0 <= a1;
}

/**
 * Generate RENT invoices for the cycle that starts in `period`.
 *
 * One invoice per student the pricing mode says is billable — see
 * resolveRent. A room's fee cycle decides how much one invoice covers: a
 * monthly room (the default, and every room from before cycles existed) is
 * billed `monthlyRent` for this month, exactly as before; a quarterly room is
 * billed 3 × monthlyRent for this month and the two after it.
 *
 * Idempotent, and never double-bills a month: a student is skipped when any
 * existing RENT invoice overlaps the months this one would cover — so a
 * quarterly student billed in October is skipped in November and December,
 * and billed again in January. The admin runs "Generate rent" every month
 * as always; the cycle decides who is due.
 */
export async function generateRentInvoices(period: string) {
  const tenantId = getTenantIdOrThrow();
  // Fetch every student with a bed and let resolveRent decide: under ROOM
  // pricing the rent lives on the room, so filtering on bed.monthlyRent here
  // would skip exactly the students who should be billed.
  const students = await db.studentProfile.findMany({
    where: { bedId: { not: null } },
    include: {
      bed: { include: { room: { include: { property: { select: { id: true, pricingMode: true, ...BILLING_RULE_FIELDS } } } } } },
      invoices: { where: { type: InvoiceType.RENT }, select: { periodMonth: true, cycleMonths: true } },
    },
  });

  let created = 0;
  let coveredByCycle = 0;
  let notYetMovedIn = 0;
  const createdIds: string[] = [];
  for (const s of students) {
    const resolved = resolveRent(s);
    if (!resolved) continue;
    const { monthly, dueDay, cycleMonths } = resolved;
    const overlap = s.invoices.some((i) => cyclesOverlap(i.periodMonth, i.cycleMonths, period, cycleMonths));
    if (overlap) {
      if (!s.invoices.some((i) => i.periodMonth === period)) coveredByCycle++;
      continue;
    }

    // ── Billing rules (all off by default; see billing-rules.service) ──────
    const rules = s.bed!.room.property;
    const full = monthly * cycleMonths;
    const lines: { kind: InvoiceLineKind; label: string; amount: number; meta?: Prisma.InputJsonValue }[] = [];

    // 1. Pro-rata: a student who moved in part-way owes the days from then.
    let rentAmount = full;
    let coveredFrom: Date | null = null;
    let rentLabel = cycleMonths > 1 ? `Hostel rent × ${cycleMonths} months` : 'Hostel rent';
    if (rules.prorataEnabled) {
      const pr = prorataOnMoveIn(full, period, cycleMonths, s.movedInAt ?? s.createdAt);
      if (pr.days === 0) {
        notYetMovedIn++;
        continue;
      }
      if (pr.coveredFrom) {
        rentAmount = pr.amount;
        coveredFrom = pr.coveredFrom;
        rentLabel = `Hostel rent · ${pr.days} days from ${pr.coveredFrom.toISOString().slice(0, 10)}`;
      }
    }
    lines.push({ kind: InvoiceLineKind.RENT, label: rentLabel, amount: rentAmount, meta: { monthly, cycleMonths, full, ...(coveredFrom ? { prorated: true } : {}) } });

    // 2. Recurring charges from the property's catalogue, then any one-off
    //    charges the office parked on the student since the last invoice.
    const charges = await recurringChargeLines(rules.id, s.id, cycleMonths);
    lines.push(...charges);
    const pending = await pendingChargeLines(s.id);
    lines.push(...pending.map(({ studentChargeId: _id, ...l }) => l));

    // 3. GST on the accommodation, when the monthly rate is over the threshold.
    //    Prices that already include GST get no line: the tax is inside the rent.
    const gst = gstLine(rules, monthly, rentAmount);
    const tax = gst ? gstSplit(rules, rentAmount) : null;
    if (tax?.line) lines.push(tax.line);

    const amount = lines.reduce((n, l) => n + l.amount, 0);
    const inv = await db.invoice.create({
      data: {
        tenantId,
        studentProfileId: s.id,
        type: InvoiceType.RENT,
        periodMonth: period,
        cycleMonths,
        amount,
        taxAmount: tax?.taxAmount ?? 0,
        ...(tax ? { taxInclusive: tax.inclusive, taxRate: tax.rate, taxPaise: tax.taxPaise } : {}),
        coveredFrom,
        dueDate: dueDateFor(period, dueDay),
        lines: { create: lines.map((l) => ({ tenantId, ...l })) },
      },
      select: { id: true },
    });
    if (pending.length) {
      await db.studentCharge.updateMany({ where: { id: { in: pending.map((p) => p.studentChargeId) } }, data: { invoiceId: inv.id } });
    }
    createdIds.push(inv.id);
    created++;
  }
  return { created, coveredByCycle, notYetMovedIn, type: 'RENT' as const, period, createdIds };
}

/**
 * Generate MESS invoices for a month.
 *
 * Three modes, one shared notion of who ate when:
 *   billable days = days subscribed − days the kitchen was shut − days the
 *                   admin marked the student off
 *
 *   FIXED_MONTHLY — round(monthlyFee / openDays × billableDays). Dividing by
 *                   the days the mess was actually OPEN, not by the calendar
 *                   month, so closing for Diwali does not quietly cut the
 *                   kitchen's income — it just stops anyone being charged for
 *                   a day they could not eat.
 *   PER_DAY       — dailyRate × billableDays.
 *   ACTUAL_COST   — the month's MESS expenses ÷ total man-days, then × the
 *                   student's days. The mess breaks even by construction, and
 *                   the rate cannot be known until the costs are in.
 *
 * Idempotent per (student, MESS, month). Students billed 0 get no invoice.
 */
export async function generateMessInvoices(period: string) {
  const tenantId = getTenantIdOrThrow();
  const settings = await db.tenantSettings.findFirst({});
  const mode = settings?.messBillingMode ?? MessBillingMode.FIXED_MONTHLY;

  const days = await billableDays(period);
  if (days.openDays === 0) {
    throw new BadRequestError('The mess is marked closed for every day of that month, so there is nothing to bill');
  }

  // Rupees per billable day, however this tenant arrives at the number.
  let dailyRate: number;
  if (mode === MessBillingMode.FIXED_MONTHLY) {
    const monthlyFee = settings?.messMonthlyFee ?? 0;
    if (monthlyFee <= 0) throw new BadRequestError('Set a mess monthly fee before generating mess invoices');
    dailyRate = monthlyFee / days.openDays;
  } else if (mode === MessBillingMode.PER_DAY) {
    const rate = settings?.messDailyRate ?? 0;
    if (rate <= 0) throw new BadRequestError('Set a mess daily rate before generating mess invoices');
    dailyRate = rate;
  } else {
    if (days.manDays === 0) {
      throw new BadRequestError('Nobody was on the mess that month, so there is nothing to divide the cost between');
    }
    const { start, end } = monthRange(period);
    const spend = await db.expense.aggregate({
      where: { category: 'MESS', date: { gte: start, lte: end } },
      _sum: { amount: true },
    });
    const total = spend._sum.amount ?? 0;
    if (total <= 0) {
      throw new BadRequestError(
        'No mess expenses are recorded for that month. Under actual-cost billing the rate comes from what the mess spent.',
      );
    }
    dailyRate = total / days.manDays;
  }

  let created = 0;
  const createdIds: string[] = [];
  for (const [studentProfileId, billed] of days.byStudent) {
    const amount = Math.round(dailyRate * billed);
    if (amount <= 0) continue;

    const exists = await db.invoice.findFirst({
      where: { studentProfileId, type: InvoiceType.MESS, periodMonth: period },
    });
    if (exists) continue;

    const inv = await db.invoice.create({
      data: {
        tenantId,
        studentProfileId,
        type: InvoiceType.MESS,
        periodMonth: period,
        amount,
        dueDate: dueDateFor(period, 10),
      },
      select: { id: true },
    });
    createdIds.push(inv.id);
    created++;
  }
  return { created, type: 'MESS' as const, period, mode, openDays: days.openDays, manDays: days.manDays, createdIds };
}

/**
 * What a month WOULD cost, without writing anything.
 *
 * Under actual-cost the rate is not knowable in advance, and under any mode an
 * admin wants to see the number before committing it to invoices.
 */
export async function previewMessBilling(period: string) {
  const settings = await db.tenantSettings.findFirst({});
  const mode = settings?.messBillingMode ?? MessBillingMode.FIXED_MONTHLY;
  const days = await billableDays(period);

  const { start, end } = monthRange(period);
  const spend = await db.expense.aggregate({
    where: { category: 'MESS', date: { gte: start, lte: end } },
    _sum: { amount: true },
  });
  const messSpend = spend._sum.amount ?? 0;

  let dailyRate = 0;
  let blocked: string | null = null;
  if (days.openDays === 0) blocked = 'The mess is closed for every day of this month.';
  else if (mode === MessBillingMode.FIXED_MONTHLY) {
    if (!settings?.messMonthlyFee) blocked = 'No monthly fee is set.';
    else dailyRate = settings.messMonthlyFee / days.openDays;
  } else if (mode === MessBillingMode.PER_DAY) {
    if (!settings?.messDailyRate) blocked = 'No daily rate is set.';
    else dailyRate = settings.messDailyRate;
  } else if (days.manDays === 0) blocked = 'Nobody was on the mess this month.';
  else if (messSpend <= 0) blocked = 'No mess expenses are recorded for this month.';
  else dailyRate = messSpend / days.manDays;

  const projected = [...days.byStudent.values()].reduce((n, d) => n + Math.round(dailyRate * d), 0);
  return {
    period,
    mode,
    blocked,
    openDays: days.openDays,
    holidays: days.holidays,
    subscribers: days.byStudent.size,
    manDays: days.manDays,
    messSpend,
    dailyRate: Math.round(dailyRate * 100) / 100,
    projectedRevenue: projected,
    /** Positive means the mess makes money at this rate; negative means it loses. */
    projectedMargin: projected - messSpend,
  };
}

/** The filter set shared by the list and its summary, so the two agree. */
function invoiceWhere(filters: {
  period?: string;
  type?: InvoiceType;
  status?: InvoiceStatus;
  propertyId?: string;
  q?: string;
}) {
  return {
    ...(filters.period ? { periodMonth: filters.period } : {}),
    ...(filters.type ? { type: filters.type } : {}),
    ...(filters.status ? { status: filters.status } : {}),
    // One `student` condition holding both: as two spread keys, a search
    // replaced the property filter and listed every block's invoices.
    ...(filters.propertyId || filters.q
      ? {
          student: {
            ...(filters.propertyId ? { propertyId: filters.propertyId } : {}),
            // Search matches the student the invoice is for.
            ...(filters.q ? { user: { name: { contains: filters.q, mode: 'insensitive' as const } } } : {}),
          },
        }
      : {}),
  };
}

/**
 * The invoices `invoiceWhere` picks, as a SQL table `inv` with what each needs
 * for the totals: its amount, what has cleared on it (and through the
 * gateway), whether it is unpaid, and its due day as the UTC date the list
 * shows. Prisma's case-insensitive `contains` is ILIKE '%…%' (nothing in the
 * words escaped), so the search matches the same names.
 */
function invoiceSql(filters: { period?: string; type?: InvoiceType; status?: InvoiceStatus; propertyId?: string; q?: string }) {
  const tenantId = getTenantIdOrThrow();
  // As Prisma sends `contains`: the words between % signs, a % or _ in them left as it is.
  const like = filters.q ? `%${filters.q}%` : null;
  const student = filters.propertyId || like;
  const conds = [
    Prisma.sql`i."tenantId" = ${tenantId}`,
    filters.period ? Prisma.sql`i."periodMonth" = ${filters.period}` : null,
    filters.type ? Prisma.sql`i."type" = ${filters.type}::"InvoiceType"` : null,
    filters.status ? Prisma.sql`i."status" = ${filters.status}::"InvoiceStatus"` : null,
    filters.propertyId ? Prisma.sql`s."propertyId" = ${filters.propertyId}` : null,
    like ? Prisma.sql`u."name" ILIKE ${like}` : null,
  ].filter((c): c is Prisma.Sql => c !== null);
  return Prisma.sql`
    WITH inv AS (
      SELECT i."id", i."amount", i."status" <> 'PAID' AS unpaid, to_char(i."dueDate", 'YYYY-MM-DD') AS due,
        COALESCE(p.paid, 0) AS paid, COALESCE(p.online, 0) AS online
      FROM "Invoice" i
      ${student ? Prisma.sql`JOIN "StudentProfile" s ON s."id" = i."studentProfileId"` : Prisma.empty}
      ${like ? Prisma.sql`JOIN "User" u ON u."id" = s."userId"` : Prisma.empty}
      LEFT JOIN LATERAL (
        SELECT sum(pm."amount") AS paid, sum(pm."amount") FILTER (WHERE pm."gatewayProvider" IS NOT NULL AND pm."gatewayProvider" <> '') AS online
        FROM "Payment" pm WHERE pm."invoiceId" = i."id" AND pm."status" = 'CLEARED'
      ) p ON TRUE
      WHERE ${Prisma.join(conds, ' AND ')}
    )`;
}

/**
 * The stat cards above the invoice list: figures for the whole period, not
 * the page on screen.
 *
 * Overdue is derived the same way the client renders its chip — unpaid and
 * past the due date — so a card and the rows under it never disagree.
 */
export async function invoiceSummary(filters: {
  period?: string;
  type?: InvoiceType;
  status?: InvoiceStatus;
  propertyId?: string;
  q?: string;
}) {
  // Summed in SQL (Sprint L4) rather than by reading the period's invoices and their payments.
  const today = new Date().toISOString().slice(0, 10);
  const horizon = new Date(Date.now() + 7 * 86_400_000).toISOString().slice(0, 10);
  const inv = invoiceSql(filters);
  const [[t], soon] = await Promise.all([
    prismaUnscoped.$queryRaw<{ billed: bigint | null; collected: bigint | null; online: bigint | null; overdue_amount: bigint | null; overdue_count: bigint; unpaid_count: bigint; unpaid_amount: bigint | null }[]>(Prisma.sql`${inv}
      SELECT sum(amount) AS billed, sum(paid) AS collected, sum(online) AS online,
        sum(amount - paid) FILTER (WHERE unpaid AND due < ${today}) AS overdue_amount, count(*) FILTER (WHERE unpaid AND due < ${today}) AS overdue_count,
        count(*) FILTER (WHERE unpaid) AS unpaid_count, sum(GREATEST(0, amount - paid)) FILTER (WHERE unpaid) AS unpaid_amount
      FROM inv`),
    prismaUnscoped.$queryRaw<{ id: string; due: string }[]>(Prisma.sql`${inv}
      SELECT id, due FROM inv WHERE unpaid AND due <= ${horizon} ORDER BY due COLLATE "C" ASC, id COLLATE "C" ASC LIMIT ${DUE_SOON_ROWS}`),
  ]);
  const billed = Number(t?.billed ?? 0);
  const collected = Number(t?.collected ?? 0);
  const collectedOnline = Number(t?.online ?? 0);
  const overdueAmount = Number(t?.overdue_amount ?? 0);
  const overdueCount = Number(t?.overdue_count ?? 0);
  const unpaidCount = Number(t?.unpaid_count ?? 0);
  const unpaidAmount = Number(t?.unpaid_amount ?? 0);
  const dueSoon: { id: string; due: string }[] = soon;

  return {
    billed,
    collected,
    // Additive: the part of `collected` that came through the gateway.
    collectedOnline,
    outstanding: billed - collected,
    overdueAmount,
    overdueCount,
    rate: billed > 0 ? Math.round((collected / billed) * 100) : 0,
    // Additive (Sprint L1): what the dashboard used to download every invoice
    // of the month to work out — the unpaid count and what is still owed on
    // them, and the few rows of "Due this week".
    unpaidCount,
    unpaidAmount,
    dueSoon: await dueSoonRows(dueSoon),
  };
}

/** How many rows "Due this week" shows, on both clients. */
const DUE_SOON_ROWS = 6;

/**
 * The unpaid invoices due within the week, overdue ones first and soonest
 * first, as full list rows (student name, payments) so both clients render
 * them with the parser they already have for the invoice list.
 */
async function dueSoonRows(candidates: { id: string; due: string }[]) {
  const ids = candidates
    .sort((a, b) => a.due.localeCompare(b.due) || a.id.localeCompare(b.id))
    .slice(0, DUE_SOON_ROWS)
    .map((c) => c.id);
  if (ids.length === 0) return [];
  const rows = await db.invoice.findMany({ where: { id: { in: ids } }, include: invoiceListInclude });
  const at = new Map(ids.map((id, i) => [id, i]));
  return rows.sort((a, b) => at.get(a.id)! - at.get(b.id)!);
}

/** One invoice in the property, shaped as a row of the list. */
export async function getInvoice(id: string, propertyId?: string) {
  const invoice = await db.invoice.findFirst({
    where: { id, ...(propertyId ? { student: { propertyId } } : {}) },
    include: invoiceListInclude,
  });
  if (!invoice) throw new NotFoundError('Invoice not found');
  return invoice;
}

export async function listInvoices(
  filters: { period?: string; type?: InvoiceType; status?: InvoiceStatus; propertyId?: string; q?: string },
  pagination?: Pagination | null,
) {
  const where = invoiceWhere(filters);
  const include = invoiceListInclude;
  const orderBy = { createdAt: 'desc' as const };

  if (!pagination) return db.invoice.findMany({ where, include, orderBy });

  const [items, total] = await Promise.all([
    db.invoice.findMany({ where, include, orderBy, skip: pagination.skip, take: pagination.limit }),
    db.invoice.count({ where }),
  ]);
  return paginated(items, total, pagination);
}

/**
 * Record a payment against an invoice.
 *
 * The invoice moves PENDING → PARTIAL → PAID as money comes in. `paidAt`
 * lets the office record an offline payment on the day it was actually
 * received — cash handed over on Friday and entered on Monday is still a
 * Friday payment — and may not be in the future.
 */
export async function recordPayment(
  invoiceId: string,
  input: { amount: number; method: string; note?: string; reference?: string; paidAt?: Date },
  recordedById: string,
) {
  const tenantId = getTenantIdOrThrow();
  return db.$transaction(async (tx) => {
    const invoice = await tx.invoice.findFirst({ where: { id: invoiceId }, include: { payments: { where: CLEARED } } });
    if (!invoice) throw new NotFoundError('Invoice not found');
    if (invoice.status === InvoiceStatus.PAID) throw new ConflictError('Invoice is already paid');

    // Refuse more than the balance. An overpayment is nearly always a typo,
    // and the books have no place to put the excess.
    const balance = invoice.amount - invoice.payments.reduce((n, p) => n + p.amount, 0);
    if (input.amount > balance) {
      throw new BadRequestError(`That is more than the ₹${balance} still owed on this invoice`);
    }

    const payment = await tx.payment.create({
      data: {
        tenantId,
        invoiceId,
        amount: input.amount,
        method: input.method,
        note: input.note,
        reference: input.reference || null,
        ...(input.paidAt ? { paidAt: input.paidAt } : {}),
        recordedById,
      },
      select: { id: true },
    });

    const paidSoFar = invoice.payments.reduce((n, p) => n + p.amount, 0) + input.amount;
    const fullyPaid = paidSoFar >= invoice.amount;
    const updated = await tx.invoice.update({
      where: { id: invoiceId },
      data: fullyPaid
        ? { status: InvoiceStatus.PAID, paidAt: input.paidAt ?? new Date() }
        : { status: InvoiceStatus.PARTIAL },
      include: { ...invoiceStudentInclude, payments: { where: CLEARED, orderBy: { paidAt: 'asc' } } },
    });
    return { invoice: updated, paymentId: payment.id };
  });
}

/**
 * A recorded payment that did not go through — a bounced cheque, a UPI
 * debit reversed by the bank, a card charge-back.
 *
 * The row is kept (an office needs to see that a cheque bounced) but marked
 * FAILED, so every balance stops counting it, and the invoice falls back to
 * PARTIAL or PENDING. Idempotent on an already-failed payment.
 */
export async function failPayment(paymentId: string, reason: string | undefined) {
  return db.$transaction(async (tx) => {
    const payment = await tx.payment.findFirst({ where: { id: paymentId } });
    if (!payment) throw new NotFoundError('Payment not found');
    if (payment.status === PaymentStatus.FAILED) throw new ConflictError('That payment is already marked as failed');

    await tx.payment.update({
      where: { id: paymentId },
      data: { status: PaymentStatus.FAILED, failedAt: new Date(), failureReason: reason?.trim() || null },
    });

    const invoice = await tx.invoice.findFirst({ where: { id: payment.invoiceId }, include: { payments: { where: CLEARED } } });
    if (!invoice) throw new NotFoundError('Invoice not found');
    const paid = invoice.payments.reduce((n, p) => n + p.amount, 0);
    const status = paid >= invoice.amount ? InvoiceStatus.PAID : paid > 0 ? InvoiceStatus.PARTIAL : InvoiceStatus.PENDING;
    const updated = await tx.invoice.update({
      where: { id: invoice.id },
      data: { status, paidAt: status === InvoiceStatus.PAID ? invoice.paidAt : null },
      include: { ...invoiceStudentInclude, payments: { where: CLEARED, orderBy: { paidAt: 'asc' } } },
    });
    return { invoice: updated, paymentId };
  });
}

/** Every payment on an invoice, failed ones included — the office's view. */
export async function listInvoicePayments(invoiceId: string) {
  const invoice = await db.invoice.findFirst({ where: { id: invoiceId }, select: { id: true } });
  if (!invoice) throw new NotFoundError('Invoice not found');
  return db.payment.findMany({
    where: { invoiceId },
    orderBy: { paidAt: 'asc' },
    select: { id: true, amount: true, method: true, note: true, reference: true, paidAt: true, status: true, failedAt: true, failureReason: true, gatewayPaymentId: true, refundId: true, refundStatus: true, refundAmount: true, refundedAt: true },
  });
}

/**
 * Remove an invoice raised by mistake.
 *
 * Refused the moment any payment exists against it — money received must
 * always trace back to the invoice it settled. The payment would have to be
 * unwound first, and that is deliberately not offered here.
 */
export async function deleteInvoice(id: string) {
  const invoice = await db.invoice.findFirst({ where: { id }, include: { payments: { select: { id: true } } } });
  if (!invoice) throw new NotFoundError('Invoice not found');
  if (invoice.payments.length > 0) {
    throw new ConflictError('Payments have been recorded against this invoice, so it cannot be removed', 'INVOICE_HAS_PAYMENTS');
  }
  await db.invoice.delete({ where: { id } });
}

/** A student's own invoices with running balance (self-service). */
export interface MyDuesFilters {
  /** `YYYY`. Filters on the period the invoice bills for, not when it was raised. */
  year?: string;
  type?: InvoiceType;
  /** Only invoices with something still owing. */
  unpaidOnly?: boolean;
}

/**
 * A student's own invoices.
 *
 * Unpaginated by default so the existing clients keep working; pass `page` to
 * get an envelope. Filtering runs here rather than in a client because once
 * the list is paginated a client only holds one page, and filtering locally
 * would search the page instead of the ledger.
 *
 * `unpaidOnly` cannot be a plain `where` clause: the balance is derived from
 * the payments, not stored, so it is applied after the rows are shaped.
 */
export async function getMyDues(
  userId: string,
  filters: MyDuesFilters = {},
  pagination: Pagination | null = null,
) {
  const profile = await db.studentProfile.findFirst({ where: { userId } });
  if (!profile) throw new NotFoundError('No student profile for this account');

  const where: Prisma.InvoiceWhereInput = {
    studentProfileId: profile.id,
    ...(filters.year ? { periodMonth: { startsWith: filters.year } } : {}),
    ...(filters.type ? { type: filters.type } : {}),
  };

  const invoices = await db.invoice.findMany({
    where,
    include: { ...invoiceLinesInclude, payments: { where: CLEARED, select: { id: true, amount: true, method: true, note: true, reference: true, paidAt: true }, orderBy: { paidAt: 'asc' } } },
    orderBy: [{ periodMonth: 'desc' }, { type: 'asc' }],
  });

  const rows = invoices.map((inv) => {
    const paid = inv.payments.reduce((n, p) => n + p.amount, 0);
    return {
      id: inv.id,
      type: inv.type,
      periodMonth: inv.periodMonth,
      cycleMonths: inv.cycleMonths,
      periodEnd: periodEnd(inv.periodMonth, inv.cycleMonths),
      amount: inv.amount,
      paid,
      balance: Math.max(0, inv.amount - paid),
      status: inv.status,
      dueDate: inv.dueDate,
      paidAt: inv.paidAt,
      createdAt: inv.createdAt,
      payments: inv.payments,
      // Billing rules (additive): the lines behind the amount, and the GST within it.
      lines: inv.lines,
      taxAmount: inv.taxAmount,
      coveredFrom: inv.coveredFrom,
      coveredTo: inv.coveredTo,
    };
  });

  const shown = filters.unpaidOnly ? rows.filter((r) => r.balance > 0) : rows;
  if (!pagination) return shown;

  // Sliced after the balance filter, so a page is always `limit` rows of what
  // the student actually asked for.
  return paginated(
    shown.slice(pagination.skip, pagination.skip + pagination.limit),
    shown.length,
    pagination,
  );
}

/**
 * The four figures above the student's ledger.
 *
 * Computed over every invoice, not a page and not the current filter: the
 * cards answer "where do I stand", which paging or a year filter must not
 * change. Whole rupees.
 */
export async function getMyDuesStats(userId: string) {
  const profile = await db.studentProfile.findFirst({
    where: { userId },
    include: {
      bed: { include: { room: { include: { property: { select: { pricingMode: true } } } } } },
      messSubscriptions: { where: { endDate: null }, select: { id: true }, take: 1 },
    },
  });
  if (!profile) throw new NotFoundError('No student profile for this account');

  // What the student is charged going forward — the "current fee" a parent
  // asks about, as opposed to what has already been invoiced.
  const rent = resolveRent(profile);
  const settings = await db.tenantSettings.findFirst({
    select: { messBillingMode: true, messMonthlyFee: true, messDailyRate: true },
  });
  const onMess = profile.messSubscriptions.length > 0;
  const currentFee = {
    rent: rent
      ? {
          monthly: rent.monthly,
          cycleMonths: rent.cycleMonths,
          perCycle: rent.monthly * rent.cycleMonths,
          dueDay: rent.dueDay,
          pricingMode: profile.bed?.room.property.pricingMode ?? PricingMode.BED,
        }
      : null,
    // Under ROOM pricing a roommate who is not the billed student owes no
    // rent; say so rather than show nothing.
    rentBilledToSomeoneElse:
      !rent &&
      !!profile.bed &&
      profile.bed.room.property.pricingMode === PricingMode.ROOM &&
      !!profile.bed.room.billedStudentProfileId &&
      profile.bed.room.billedStudentProfileId !== profile.id,
    mess: {
      subscribed: onMess,
      mode: settings?.messBillingMode ?? MessBillingMode.FIXED_MONTHLY,
      monthlyFee: settings?.messMonthlyFee ?? 0,
      dailyRate: settings?.messDailyRate ?? 0,
    },
  };

  const invoices = await db.invoice.findMany({
    where: { studentProfileId: profile.id },
    select: {
      periodMonth: true,
      amount: true,
      type: true,
      dueDate: true,
      payments: { where: CLEARED, select: { amount: true } },
    },
  });

  const now = new Date();
  const thisYear = String(now.getFullYear());
  let outstanding = 0;
  let overdue = 0;
  let overdueCount = 0;
  let paidThisYear = 0;
  let next: { dueDate: Date; balance: number; type: InvoiceType; periodMonth: string } | null = null;

  for (const inv of invoices) {
    const paid = inv.payments.reduce((n, p) => n + p.amount, 0);
    const balance = Math.max(0, inv.amount - paid);
    if (inv.periodMonth.startsWith(thisYear)) paidThisYear += paid;
    if (balance === 0) continue;

    outstanding += balance;
    if (!inv.dueDate) continue;

    if (inv.dueDate < now) {
      overdue += balance;
      overdueCount += 1;
    } else if (!next || inv.dueDate < next.dueDate) {
      next = { dueDate: inv.dueDate, balance, type: inv.type, periodMonth: inv.periodMonth };
    }
  }

  return { outstanding, overdue, overdueCount, paidThisYear, next, currentFee };
}

/**
 * Every payment the student has made, newest first, with the invoice each
 * one settled — the payment history a student (or their parent) scrolls
 * when asking "what have we paid so far?".
 */
export async function getMyPayments(userId: string, pagination: Pagination | null = null) {
  const profile = await db.studentProfile.findFirst({ where: { userId } });
  if (!profile) throw new NotFoundError('No student profile for this account');

  const where: Prisma.PaymentWhereInput = { invoice: { studentProfileId: profile.id } };
  const select = {
    id: true,
    amount: true,
    method: true,
    reference: true,
    note: true,
    paidAt: true,
    status: true,
    failedAt: true,
    failureReason: true,
    invoice: { select: { id: true, type: true, periodMonth: true, cycleMonths: true, amount: true, status: true } },
  } as const;
  const orderBy = { paidAt: 'desc' as const };

  const shape = (p: { invoice: { periodMonth: string; cycleMonths: number } } & Record<string, unknown>) => ({
    ...p,
    invoice: { ...p.invoice, periodEnd: periodEnd(p.invoice.periodMonth, p.invoice.cycleMonths) },
  });

  if (!pagination) {
    const rows = await db.payment.findMany({ where, select, orderBy, take: 500 });
    return rows.map(shape);
  }
  const [items, total] = await Promise.all([
    db.payment.findMany({ where, select, orderBy, skip: pagination.skip, take: pagination.limit }),
    db.payment.count({ where }),
  ]);
  return paginated(items.map(shape), total, pagination);
}

/**
 * Every year the student has an invoice for, newest first.
 *
 * The year filter needs the full set of options, which a single page cannot
 * supply — so it is its own small query rather than something the client
 * derives from the rows on screen.
 */
export async function getMyDuesYears(userId: string): Promise<string[]> {
  const profile = await db.studentProfile.findFirst({ where: { userId } });
  if (!profile) throw new NotFoundError('No student profile for this account');

  const rows = await db.invoice.findMany({
    where: { studentProfileId: profile.id },
    select: { periodMonth: true },
    distinct: ['periodMonth'],
    orderBy: { periodMonth: 'desc' },
  });
  return [...new Set(rows.map((r) => r.periodMonth.slice(0, 4)))];
}

