import { Prisma, Role, SubscriptionInvoiceType, SubscriptionStatus } from '@prisma/client';
import { prismaUnscoped } from '../../lib/prisma';
import { getSettings } from '../platform/platform.service';
import { BadRequestError, ConflictError, NotFoundError } from '../../lib/errors';
import { isPlaceholderEmail } from '../../lib/no-login';

/**
 * Platform subscription billing — what WE charge each hostel for its plan.
 *
 * Invoices are raised by hand (6 Oct 2026), the way GymCare's super admin
 * does it: pick a hostel and an invoice type, the amount comes from its plan
 * and can be overridden, a tax rate is added on top, and the invoice starts
 * as a draft. Sending it emails the PDF and issues it; payments — full,
 * partial, or on account with no invoice yet — settle it.
 *
 * Every query deliberately uses the UNSCOPED client: these are platform
 * records, not tenant data, and the routes are SUPER_ADMIN-only. The only
 * tenant-facing reader is the hostel owner's own plan card, which never sees
 * a draft.
 */

export const PAYMENT_METHODS = ['BANK_TRANSFER', 'UPI', 'CARD', 'CASH', 'CHEQUE', 'RAZORPAY', 'OTHER'] as const;
export type PaymentMethod = (typeof PAYMENT_METHODS)[number];

export const INVOICE_TYPES = Object.values(SubscriptionInvoiceType) as [SubscriptionInvoiceType, ...SubscriptionInvoiceType[]];

/** Months an invoice of each type covers; a one-time charge is one unit. */
export const TYPE_MONTHS: Record<SubscriptionInvoiceType, number> = {
  MONTHLY: 1,
  QUARTERLY: 3,
  HALF_YEARLY: 6,
  YEARLY: 12,
  ONE_TIME: 1,
};

export const TYPE_LABEL: Record<SubscriptionInvoiceType, string> = {
  MONTHLY: 'Monthly',
  QUARTERLY: 'Quarterly',
  HALF_YEARLY: 'Half-yearly',
  YEARLY: 'Yearly',
  ONE_TIME: 'One-time',
};

const DAY = 86_400_000;
const DEFAULT_TERMS_DAYS = 14;

/** A date-only value held at noon UTC, so it reads as the same calendar day everywhere. */
export function noonUtc(ymd: string): Date {
  return new Date(`${ymd}T12:00:00.000Z`);
}
const ymdOf = (d: Date) => d.toISOString().slice(0, 10);
/** Today in India, as a date-only value. */
const todayIst = () => noonUtc(new Date(Date.now() + 5.5 * 3_600_000).toISOString().slice(0, 10));

// ── Who is billing whom ─────────────────────────────────────────────────────

export interface Party {
  name: string;
  legalName?: string | null;
  email?: string | null;
  phone?: string | null;
  website?: string | null;
  gstin?: string | null;
  pan?: string | null;
  address?: string | null;
  city?: string | null;
  state?: string | null;
  country?: string | null;
  zip?: string | null;
}

/** The platform operator, from the super admin's billing profile. */
export async function sellerParty(): Promise<Party> {
  const s = await getSettings();
  return {
    name: s.billingCompanyName.trim() || s.platformName || 'HostelCare',
    email: s.billingEmail || s.supportEmail || null,
    phone: s.billingPhone || s.supportPhone || null,
    website: s.billingWebsite || s.websiteUrl || null,
    gstin: s.billingGstin || null,
    pan: s.billingPan || null,
    address: s.billingAddress || null,
    city: s.billingCity || null,
    state: s.billingState || null,
    country: s.billingCountry || null,
    zip: s.billingZip || null,
  };
}

/**
 * The hostel being billed: its registered billing identity when it has one
 * (firm name, GSTIN, address — the same details it prints on its own
 * invoices), otherwise its account name, first admin and first property.
 */
export async function buyerParty(tenantId: string): Promise<Party & { recipients: string[] }> {
  const tenant = await prismaUnscoped.tenant.findUnique({
    where: { id: tenantId },
    select: {
      name: true,
      users: { where: { role: Role.HOSTEL_ADMIN }, select: { email: true, phone: true }, orderBy: { createdAt: 'asc' } },
      properties: { select: { address: true, city: true, state: true }, orderBy: { createdAt: 'asc' }, take: 1 },
    },
  });
  if (!tenant) throw new NotFoundError('Tenant not found');
  const settings = await prismaUnscoped.tenantSettings.findFirst({
    where: { tenantId },
    select: { billingFirmName: true, billingGstin: true, billingAddress: true, billingPhone: true, billingEmail: true },
  });
  const admin = tenant.users.find((u) => !isPlaceholderEmail(u.email)) ?? null;
  const property = tenant.properties[0] ?? null;
  const recipients = [settings?.billingEmail?.trim(), admin?.email].filter((e): e is string => !!e && !isPlaceholderEmail(e));
  return {
    name: tenant.name,
    legalName: settings?.billingFirmName?.trim() || null,
    email: settings?.billingEmail?.trim() || admin?.email || null,
    phone: settings?.billingPhone?.trim() || admin?.phone || null,
    gstin: settings?.billingGstin?.trim() || null,
    address: settings?.billingAddress?.trim() || property?.address || null,
    city: settings?.billingAddress?.trim() ? null : property?.city ?? null,
    state: settings?.billingAddress?.trim() ? null : property?.state ?? null,
    country: 'India',
    recipients: [...new Set(recipients)],
  };
}

// ── Money ───────────────────────────────────────────────────────────────────

/** Tax is added on top of the subtotal and rounded to whole rupees. */
export function totals(subtotal: number, taxRate: number) {
  const taxAmount = Math.round((subtotal * taxRate) / 100);
  return { subtotal, taxRate, taxAmount, amount: subtotal + taxAmount };
}

/** What an invoice of this type costs on this plan: the monthly price × months. */
export function planSubtotal(monthlyPrice: number, type: SubscriptionInvoiceType) {
  return monthlyPrice * TYPE_MONTHS[type];
}

const paidOf = (payments: { amount: number }[]) => payments.reduce((n, p) => n + p.amount, 0);

/** An invoice plus its money position and whether it is past due. */
function withBalance<T extends { amount: number; status: SubscriptionStatus; dueDate: Date | null; payments: { amount: number }[] }>(inv: T) {
  const paid = paidOf(inv.payments);
  const balance = Math.max(0, inv.amount - paid);
  const open = inv.status !== SubscriptionStatus.VOID && inv.status !== SubscriptionStatus.DRAFT && balance > 0;
  return { ...inv, paid, balance, overdue: open && !!inv.dueDate && inv.dueDate.getTime() < todayIst().getTime() };
}

const tenantSelect = { select: { id: true, name: true } } as const;
const invoiceInclude = { tenant: tenantSelect, payments: { orderBy: { paidAt: 'desc' as const } } } as const;

/** The line an invoice prints: what was sold, for how long. */
export function lineDescription(inv: { planName: string; invoiceType: SubscriptionInvoiceType }) {
  const what = inv.planName && inv.planName !== 'Custom' ? `${inv.planName} plan` : 'HostelCare platform';
  return inv.invoiceType === SubscriptionInvoiceType.ONE_TIME ? `${what} — one-time charge` : `${what} — ${TYPE_LABEL[inv.invoiceType].toLowerCase()} subscription`;
}

/**
 * Bring an invoice's status in line with what has been paid against it.
 * A draft that receives money is issued by it; a cancelled one is never touched.
 */
async function reconcile(tx: Prisma.TransactionClient, invoiceId: string) {
  const inv = await tx.subscriptionInvoice.findUnique({ where: { id: invoiceId }, include: { payments: { orderBy: { paidAt: 'desc' } } } });
  if (!inv || inv.status === SubscriptionStatus.VOID) return;
  const paid = paidOf(inv.payments);
  const status =
    inv.amount > 0 && paid >= inv.amount
      ? SubscriptionStatus.PAID
      : paid > 0
        ? SubscriptionStatus.PARTIAL
        : inv.status === SubscriptionStatus.DRAFT
          ? SubscriptionStatus.DRAFT
          : SubscriptionStatus.PENDING;
  await tx.subscriptionInvoice.update({
    where: { id: invoiceId },
    data: { status, paidAt: status === SubscriptionStatus.PAID ? inv.payments[0]?.paidAt ?? new Date() : null },
  });
}

/**
 * The next `INV-<year>-<seq>`. A transaction-scoped advisory lock serialises
 * concurrent creates for the same year, and the (year, seq) unique index is
 * the backstop; the sequence is numeric, so it orders correctly past 9999.
 */
async function nextNumber(tx: Prisma.TransactionClient, year: number) {
  await tx.$executeRaw`SELECT pg_advisory_xact_lock(${7_100_000 + year})`;
  const last = await tx.subscriptionInvoice.aggregate({ where: { invoiceYear: year }, _max: { invoiceSeq: true } });
  const seq = (last._max.invoiceSeq ?? 0) + 1;
  return { invoiceYear: year, invoiceSeq: seq, invoiceNumber: `INV-${year}-${String(seq).padStart(4, '0')}` };
}

// ── Invoices ────────────────────────────────────────────────────────────────

export interface InvoiceInput {
  tenantId: string;
  invoiceType: SubscriptionInvoiceType;
  /** Before tax. Omitted: the plan's monthly price × the type's months. */
  subtotal?: number;
  taxRate?: number;
  issueDate?: Date;
  dueDate?: Date;
  periodStart?: Date | null;
  periodEnd?: Date | null;
  note?: string | null;
}

function checkDates(issueDate: Date, dueDate: Date, periodStart: Date | null, periodEnd: Date | null) {
  if (dueDate.getTime() < issueDate.getTime()) throw new BadRequestError('The due date cannot be before the issue date');
  if (periodStart && periodEnd && periodEnd.getTime() < periodStart.getTime()) throw new BadRequestError('The period cannot end before it starts');
}

/** Raise an invoice for one hostel. It starts as a draft with the next number. */
export async function createInvoice(input: InvoiceInput, actorId: string) {
  const tenant = await prismaUnscoped.tenant.findUnique({ where: { id: input.tenantId }, include: { planRef: true } });
  if (!tenant) throw new NotFoundError('Tenant not found');

  let subtotal = input.subtotal;
  if (subtotal === undefined) {
    if (!tenant.planRef || tenant.planRef.monthlyPrice <= 0) {
      throw new BadRequestError(`${tenant.name} has no priced plan — enter the amount`);
    }
    subtotal = planSubtotal(tenant.planRef.monthlyPrice, input.invoiceType);
  }
  if (subtotal < 1) throw new BadRequestError('The amount must be at least ₹1');

  const issueDate = input.issueDate ?? todayIst();
  const dueDate = input.dueDate ?? new Date(issueDate.getTime() + DEFAULT_TERMS_DAYS * DAY);
  const periodStart = input.periodStart ?? null;
  const periodEnd = input.periodEnd ?? null;
  checkDates(issueDate, dueDate, periodStart, periodEnd);

  const [buyer, seller] = await Promise.all([buyerParty(tenant.id), sellerParty()]);
  const { recipients: _r, ...buyerSnapshot } = buyer;
  const money = totals(subtotal, input.taxRate ?? 0);

  const invoice = await prismaUnscoped.$transaction(async (tx) => {
    const number = await nextNumber(tx, issueDate.getUTCFullYear());
    return tx.subscriptionInvoice.create({
      data: {
        tenantId: tenant.id,
        planId: tenant.planRef?.id,
        planName: tenant.planRef?.name ?? 'Custom',
        periodMonth: ymdOf(periodStart ?? issueDate).slice(0, 7),
        ...money,
        status: SubscriptionStatus.DRAFT,
        invoiceType: input.invoiceType,
        issueDate,
        dueDate,
        periodStart,
        periodEnd,
        note: input.note ?? null,
        buyerSnapshot: buyerSnapshot as Prisma.InputJsonValue,
        sellerSnapshot: seller as unknown as Prisma.InputJsonValue,
        createdById: actorId,
        ...number,
      },
      include: invoiceInclude,
    });
  });
  return withBalance(invoice);
}

async function loadInvoice(id: string) {
  const inv = await prismaUnscoped.subscriptionInvoice.findUnique({ where: { id }, include: invoiceInclude });
  if (!inv) throw new NotFoundError('Invoice not found');
  return inv;
}

export async function getInvoice(id: string) {
  const inv = await loadInvoice(id);
  // Older rows predate snapshots: show today's details for them.
  const buyer = (inv.buyerSnapshot as Party | null) ?? (await buyerParty(inv.tenantId));
  const seller = (inv.sellerSnapshot as Party | null) ?? (await sellerParty());
  return { ...withBalance(inv), buyer, seller, description: lineDescription(inv), months: TYPE_MONTHS[inv.invoiceType] };
}

/**
 * Change an invoice that is not yet paid or cancelled. The total may not drop
 * below what has already been collected on it. A draft also picks up the
 * current billing details of both sides.
 */
export async function updateInvoice(id: string, input: Partial<Omit<InvoiceInput, 'tenantId'>>) {
  const inv = await loadInvoice(id);
  if (inv.status === SubscriptionStatus.PAID) throw new ConflictError('Paid invoices cannot be edited');
  if (inv.status === SubscriptionStatus.VOID) throw new ConflictError('Cancelled invoices cannot be edited');

  const invoiceType = input.invoiceType ?? inv.invoiceType;
  const subtotal = input.subtotal ?? inv.subtotal;
  if (subtotal < 1) throw new BadRequestError('The amount must be at least ₹1');
  const issueDate = input.issueDate ?? inv.issueDate ?? inv.createdAt;
  const dueDate = input.dueDate ?? inv.dueDate ?? new Date(issueDate.getTime() + DEFAULT_TERMS_DAYS * DAY);
  const periodStart = input.periodStart !== undefined ? input.periodStart : inv.periodStart;
  const periodEnd = input.periodEnd !== undefined ? input.periodEnd : inv.periodEnd;
  checkDates(issueDate, dueDate, periodStart, periodEnd);

  const money = totals(subtotal, input.taxRate ?? inv.taxRate);
  const paid = paidOf(inv.payments);
  if (money.amount < paid) throw new ConflictError(`₹${paid.toLocaleString('en-IN')} has already been collected on this invoice; the total cannot be less than that`);

  const snapshots =
    inv.status === SubscriptionStatus.DRAFT
      ? await Promise.all([buyerParty(inv.tenantId), sellerParty()]).then(([{ recipients: _r, ...b }, s]) => ({
          buyerSnapshot: b as Prisma.InputJsonValue,
          sellerSnapshot: s as unknown as Prisma.InputJsonValue,
        }))
      : {};

  await prismaUnscoped.$transaction(async (tx) => {
    await tx.subscriptionInvoice.update({
      where: { id },
      data: {
        ...money,
        invoiceType,
        issueDate,
        dueDate,
        periodStart,
        periodEnd,
        periodMonth: ymdOf(periodStart ?? issueDate).slice(0, 7),
        ...(input.note !== undefined ? { note: input.note } : {}),
        ...snapshots,
      },
    });
    await reconcile(tx, id);
  });
  return getInvoice(id);
}

/** Issue a draft without emailing it (sending also issues). */
export async function issueInvoice(id: string) {
  const inv = await loadInvoice(id);
  if (inv.status !== SubscriptionStatus.DRAFT) return getInvoice(id);
  const [{ recipients: _r, ...buyer }, seller] = await Promise.all([buyerParty(inv.tenantId), sellerParty()]);
  await prismaUnscoped.subscriptionInvoice.update({
    where: { id },
    data: { status: SubscriptionStatus.PENDING, buyerSnapshot: buyer as Prisma.InputJsonValue, sellerSnapshot: seller as unknown as Prisma.InputJsonValue },
  });
  return getInvoice(id);
}

export async function markSent(id: string) {
  await prismaUnscoped.subscriptionInvoice.update({ where: { id }, data: { sentAt: new Date() } });
}

/** Cancel: kept on record, no longer billable. Anything collected blocks it. */
export async function voidInvoice(id: string) {
  const inv = await loadInvoice(id);
  if (inv.status === SubscriptionStatus.PAID) throw new ConflictError('Paid invoices cannot be cancelled');
  if (inv.payments.length > 0) throw new ConflictError('Payments are already recorded against this invoice — it cannot be cancelled');
  await prismaUnscoped.subscriptionInvoice.update({ where: { id }, data: { status: SubscriptionStatus.VOID } });
  return getInvoice(id);
}

/** Only a draft with nothing paid can be deleted outright. */
export async function deleteInvoice(id: string) {
  const inv = await loadInvoice(id);
  if (inv.status !== SubscriptionStatus.DRAFT) throw new ConflictError('Only draft invoices can be deleted; cancel an issued one instead');
  if (inv.payments.length > 0) throw new ConflictError('Payments are recorded against this draft — it cannot be deleted');
  await prismaUnscoped.subscriptionInvoice.delete({ where: { id } });
}

export interface InvoiceFilters {
  status?: SubscriptionStatus;
  type?: SubscriptionInvoiceType;
  tenantId?: string;
  /** Invoice number or hostel name. */
  q?: string;
  from?: Date;
  to?: Date;
  overdue?: boolean;
}

export async function listInvoices(filters: InvoiceFilters) {
  const q = filters.q?.trim();
  const invoices = await prismaUnscoped.subscriptionInvoice.findMany({
    where: {
      ...(filters.status ? { status: filters.status } : {}),
      ...(filters.type ? { invoiceType: filters.type } : {}),
      ...(filters.tenantId ? { tenantId: filters.tenantId } : {}),
      ...(filters.from || filters.to
        ? { issueDate: { ...(filters.from ? { gte: filters.from } : {}), ...(filters.to ? { lte: new Date(filters.to.getTime() + DAY / 2) } : {}) } }
        : {}),
      ...(q
        ? { OR: [{ invoiceNumber: { contains: q, mode: 'insensitive' as const } }, { tenant: { name: { contains: q, mode: 'insensitive' as const } } }] }
        : {}),
    },
    include: invoiceInclude,
    orderBy: [{ issueDate: 'desc' }, { invoiceYear: 'desc' }, { invoiceSeq: 'desc' }],
    take: 1000,
  });
  const rows = invoices.map(withBalance);
  return filters.overdue ? rows.filter((r) => r.overdue) : rows;
}

// ── Payments ────────────────────────────────────────────────────────────────

export interface PaymentInput {
  tenantId?: string;
  invoiceId?: string;
  amount: number;
  method: string;
  reference?: string | null;
  note?: string | null;
  paidAt?: Date;
}

/**
 * Record money received — against an invoice (full or partial) or on account
 * for a hostel with no invoice to set it against yet. Overpaying an invoice
 * is refused: the ledger is what the platform actually collected, and a typo
 * should surface as an error, not inflate revenue.
 */
export async function recordPayment(input: PaymentInput, recordedById: string) {
  if (input.paidAt && input.paidAt.getTime() > Date.now() + 36 * 3_600_000) throw new BadRequestError('The payment date cannot be in the future');
  return prismaUnscoped.$transaction(async (tx) => {
    let tenantId = input.tenantId;
    if (input.invoiceId) {
      const inv = await tx.subscriptionInvoice.findUnique({ where: { id: input.invoiceId }, include: { payments: true } });
      if (!inv) throw new NotFoundError('Invoice not found');
      if (tenantId && tenantId !== inv.tenantId) throw new BadRequestError('That invoice belongs to another hostel');
      if (inv.status === SubscriptionStatus.VOID) throw new ConflictError('Cannot record a payment against a cancelled invoice');
      const balance = inv.amount - paidOf(inv.payments);
      if (balance <= 0) throw new ConflictError('Invoice is already fully paid');
      if (input.amount > balance) throw new BadRequestError(`Payment exceeds the outstanding balance of ₹${balance.toLocaleString('en-IN')}`);
      tenantId = inv.tenantId;
    } else {
      if (!tenantId) throw new BadRequestError('Pick a hostel for a payment taken on account');
      const t = await tx.tenant.findUnique({ where: { id: tenantId }, select: { id: true } });
      if (!t) throw new NotFoundError('Tenant not found');
    }
    const payment = await tx.subscriptionPayment.create({
      data: {
        invoiceId: input.invoiceId ?? null,
        tenantId: tenantId!,
        amount: input.amount,
        method: input.method,
        reference: input.reference || null,
        note: input.note || null,
        ...(input.paidAt ? { paidAt: input.paidAt } : {}),
        recordedById,
      },
      include: { tenant: tenantSelect, invoice: { select: { id: true, invoiceNumber: true, amount: true, status: true } } },
    });
    if (input.invoiceId) await reconcile(tx, input.invoiceId);
    return payment;
  });
}

/** Take a payment off the ledger; the invoice it settled goes back to unpaid. */
export async function deletePayment(id: string) {
  await prismaUnscoped.$transaction(async (tx) => {
    const p = await tx.subscriptionPayment.findUnique({ where: { id } });
    if (!p) throw new NotFoundError('Payment not found');
    await tx.subscriptionPayment.delete({ where: { id } });
    if (p.invoiceId) await reconcile(tx, p.invoiceId);
  });
}

export interface PaymentFilters {
  tenantId?: string;
  method?: string;
  /** Reference, note, hostel name or invoice number. */
  q?: string;
  from?: Date;
  to?: Date;
}

/** The ledger, with the total of everything the filter matches. */
export async function listPayments(filters: PaymentFilters) {
  const q = filters.q?.trim();
  const where: Prisma.SubscriptionPaymentWhereInput = {
    ...(filters.tenantId ? { tenantId: filters.tenantId } : {}),
    ...(filters.method ? { method: filters.method } : {}),
    ...(filters.from || filters.to
      ? { paidAt: { ...(filters.from ? { gte: new Date(filters.from.getTime() - DAY / 2) } : {}), ...(filters.to ? { lt: new Date(filters.to.getTime() + DAY / 2) } : {}) } }
      : {}),
    ...(q
      ? {
          OR: [
            { reference: { contains: q, mode: 'insensitive' } },
            { note: { contains: q, mode: 'insensitive' } },
            { tenant: { name: { contains: q, mode: 'insensitive' } } },
            { invoice: { invoiceNumber: { contains: q, mode: 'insensitive' } } },
          ],
        }
      : {}),
  };
  const [payments, sum] = await Promise.all([
    prismaUnscoped.subscriptionPayment.findMany({
      where,
      include: { tenant: tenantSelect, invoice: { select: { id: true, invoiceNumber: true, periodMonth: true, amount: true, planName: true, status: true } } },
      orderBy: [{ paidAt: 'desc' }, { id: 'desc' }],
      take: 1000,
    }),
    prismaUnscoped.subscriptionPayment.aggregate({ where, _sum: { amount: true }, _count: true }),
  ]);
  return { payments, filteredTotal: sum._sum.amount ?? 0, count: sum._count };
}

/** Issued invoices that still have money owing, oldest due first. */
export async function outstanding(tenantId?: string) {
  const invoices = await prismaUnscoped.subscriptionInvoice.findMany({
    where: { status: { in: [SubscriptionStatus.PENDING, SubscriptionStatus.PARTIAL] }, ...(tenantId ? { tenantId } : {}) },
    include: invoiceInclude,
    orderBy: [{ dueDate: 'asc' }, { issueDate: 'asc' }],
  });
  const rows = invoices.map(withBalance).filter((r) => r.balance > 0);
  const overdue = rows.filter((r) => r.overdue);
  return {
    invoices: rows,
    totalOutstanding: rows.reduce((n, r) => n + r.balance, 0),
    overdueAmount: overdue.reduce((n, r) => n + r.balance, 0),
    overdueCount: overdue.length,
  };
}

/** What a payment for this hostel can be set against: anything not cancelled with a balance, drafts included. */
export async function invoiceOptions(tenantId: string) {
  const invoices = await prismaUnscoped.subscriptionInvoice.findMany({
    where: { tenantId, status: { not: SubscriptionStatus.VOID } },
    include: invoiceInclude,
    orderBy: [{ issueDate: 'desc' }, { invoiceSeq: 'desc' }],
    take: 200,
  });
  return invoices.map(withBalance).filter((r) => r.balance > 0);
}

/**
 * The KPI strip: collected this month, this year and in all, what is still
 * owed (and overdue) on issued invoices, money held on account, and the
 * committed monthly revenue of active hostels.
 */
export async function getSummary() {
  const now = new Date(Date.now() + 5.5 * 3_600_000); // India
  const monthStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1) - 5.5 * 3_600_000);
  const yearStart = new Date(Date.UTC(now.getUTCFullYear(), 0, 1) - 5.5 * 3_600_000);
  const [all, month, year, onAccount, billedAgg, open, tenants] = await Promise.all([
    prismaUnscoped.subscriptionPayment.aggregate({ _sum: { amount: true }, _count: true }),
    prismaUnscoped.subscriptionPayment.aggregate({ where: { paidAt: { gte: monthStart } }, _sum: { amount: true } }),
    prismaUnscoped.subscriptionPayment.aggregate({ where: { paidAt: { gte: yearStart } }, _sum: { amount: true } }),
    prismaUnscoped.subscriptionPayment.aggregate({ where: { invoiceId: null }, _sum: { amount: true } }),
    prismaUnscoped.subscriptionInvoice.aggregate({ where: { status: { notIn: [SubscriptionStatus.DRAFT, SubscriptionStatus.VOID] } }, _sum: { amount: true }, _count: true }),
    outstanding(),
    prismaUnscoped.tenant.findMany({ where: { status: 'ACTIVE' }, include: { planRef: { select: { monthlyPrice: true } } } }),
  ]);
  const drafts = await prismaUnscoped.subscriptionInvoice.count({ where: { status: SubscriptionStatus.DRAFT } });
  return {
    collectedAll: all._sum.amount ?? 0,
    collectedMonth: month._sum.amount ?? 0,
    collectedYear: year._sum.amount ?? 0,
    paymentCount: all._count,
    onAccount: onAccount._sum.amount ?? 0,
    billed: billedAgg._sum.amount ?? 0,
    invoiceCount: billedAgg._count,
    draftCount: drafts,
    outstanding: open.totalOutstanding,
    outstandingCount: open.invoices.length,
    overdueAmount: open.overdueAmount,
    overdueCount: open.overdueCount,
    mrr: tenants.reduce((n, t) => n + (t.planRef?.monthlyPrice ?? 0), 0),
    payingTenants: tenants.filter((t) => (t.planRef?.monthlyPrice ?? 0) > 0).length,
  };
}
