import { v } from "convex/values";
import { query } from "./_generated/server";
import type { Doc, Id } from "./_generated/dataModel";
import { assertPermission, can } from "./lib/access";
import { getSettings } from "./lib/settings";
import { addDaysKey, addMonthsKey, nightsIn, parseKey, startOfMonthKey, startOfWeekKey, todayKey, nightsBetweenKeys, weekdayOf } from "./lib/days";
import { effectiveCheckOut } from "./lib/inventory";
import { withId, loader, isRevenue, pct, delta } from "./lib/shape";
import { resolveRange } from "./dashboard";
import { RESERVATION_SOURCE_META, RESERVATION_STATUS_META } from "../src/lib/domain";
import { fmtMoney } from "../src/lib/format";

const monthLabel = (k: string, year = false) => parseKey(k).toLocaleString("en", { month: "short", ...(year ? { year: "2-digit" } : {}), timeZone: "UTC" });
const shortDate = (k: string) => parseKey(k).toLocaleString("en", { day: "numeric", month: "short", timeZone: "UTC" });

async function overlapping(ctx: Parameters<typeof query>[0] extends never ? never : import("./_generated/server").QueryCtx, start: string, end: string, apartmentId?: string) {
  const rows = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", addDaysKey(start, -120)).lt("checkIn", end)).collect()).filter((r) => isRevenue(r.status) && r.checkOut > start && (!apartmentId || r.apartmentId === apartmentId));
  return rows;
}

export const financials = query({
  args: { range: v.optional(v.string()), from: v.optional(v.string()), to: v.optional(v.string()), apartmentId: v.optional(v.string()) },
  returns: v.any(),
  handler: async (ctx, opts) => {
    await assertPermission(ctx, "financials.view_revenue", "financials.view_profit");
    const settings = await getSettings(ctx);
    const { start, end, prevStart, prevEnd, days, today } = resolveRange(opts.range ?? "month", settings.timezone, opts.from, opts.to);
    const aptF = (r: { apartmentId?: Id<"apartments"> | undefined }) => !opts.apartmentId || r.apartmentId === opts.apartmentId;
    const customers = loader(ctx, "customers");
    const aptsL = loader(ctx, "apartments");
    const roles = await ctx.db.query("roles").collect();
    const adminRole = roles.find((r) => r.key === "ADMIN");
    const rs = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", start).lt("checkIn", end)).collect()).filter(aptF);
    const prevRs = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", prevStart).lt("checkIn", prevEnd)).collect()).filter(aptF);
    const expenses = (await ctx.db.query("expenses").withIndex("by_date", (q) => q.gte("date", start).lt("date", end)).collect()).filter((e) => !e.deletedAt && aptF(e));
    const prevExp = (await ctx.db.query("expenses").withIndex("by_date", (q) => q.gte("date", prevStart).lt("date", prevEnd)).collect()).filter((e) => !e.deletedAt && aptF(e)).reduce((s, e) => s + e.amount, 0);
    const cats = await ctx.db.query("expenseCategories").collect();
    const comAll = (await ctx.db.query("commissions").collect()).filter((c) => !["CANCELLED", "REVERSED"].includes(c.status));
    const comWith = await Promise.all(comAll.map(async (c) => { const r = await ctx.db.get(c.reservationId); return { ...c, checkIn: r?.checkIn ?? "", apartmentId: r?.apartmentId }; }));
    const commissions = comWith.filter((c) => c.checkIn >= start && c.checkIn < end && aptF(c));
    const prevCom = comWith.filter((c) => c.checkIn >= prevStart && c.checkIn < prevEnd && aptF(c)).reduce((s, c) => s + c.amount, 0);
    const apartments = (await ctx.db.query("apartments").collect()).filter((a) => !a.deletedAt && (!opts.apartmentId || a._id === opts.apartmentId));
    const workers = (await ctx.db.query("users").collect()).filter((u) => !u.deletedAt && u.roleId && u.roleId !== adminRole?._id);
    const rev = rs.filter((r) => isRevenue(r.status));
    const prevRev = prevRs.filter((r) => isRevenue(r.status));
    const revenue = rev.reduce((s, r) => s + r.totalAmount, 0);
    const prevRevenue = prevRev.reduce((s, r) => s + r.totalAmount, 0);
    const exp = expenses.reduce((s, e) => s + e.amount, 0);
    const com = commissions.reduce((s, c) => s + c.amount, 0);
    const profit = revenue - exp - com;
    const prevProfit = prevRevenue - prevExp - prevCom;
    const collected = rev.reduce((s, r) => s + r.amountPaid, 0);
    const allOverlap = await overlapping(ctx, start, end, opts.apartmentId);
    const occN = (list: { checkIn: string; checkOut: string; actualCheckOut?: string | null }[], s: string, e: string) => list.reduce((n, r) => n + nightsIn(r.checkIn, effectiveCheckOut(r), s, e), 0);
    const nights = occN(allOverlap, start, end);
    const available = apartments.length * days;
    const occupancy = pct(nights, available);
    const byCustomer = new Map<string, number>();
    for (const status of ["CONFIRMED", "CHECKED_IN", "CHECKED_OUT"]) for (const r of await ctx.db.query("reservations").withIndex("by_status_checkIn", (q) => q.eq("status", status)).collect()) byCustomer.set(r.customerId, (byCustomer.get(r.customerId) ?? 0) + 1);
    const repeatRate = pct([...byCustomer.values()].filter((n) => n > 1).length, byCustomer.size);
    const daily = Array.from({ length: Math.min(days, 92) }, (_, i) => { const d = addDaysKey(start, i); const n = addDaysKey(d, 1); const r = rev.filter((x) => x.checkIn >= d && x.checkIn < n).reduce((s, x) => s + x.totalAmount, 0); const e = expenses.filter((x) => x.date >= d && x.date < n).reduce((s, x) => s + x.amount, 0); return { label: `${parseKey(d).getUTCDate()}/${parseKey(d).getUTCMonth() + 1}`, key: d, revenue: r, expenses: e, profit: r - e }; });
    const monthStart = startOfMonthKey(today);
    const yFrom = addMonthsKey(monthStart, -23);
    const yTo = addMonthsKey(monthStart, 1);
    const yearRs = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", yFrom).lt("checkIn", yTo)).collect()).filter((r) => isRevenue(r.status) && aptF(r));
    const yearExp = (await ctx.db.query("expenses").withIndex("by_date", (q) => q.gte("date", yFrom).lt("date", yTo)).collect()).filter((e) => !e.deletedAt && aptF(e));
    const monthly = Array.from({ length: 12 }, (_, i) => { const s = addMonthsKey(monthStart, i - 11); const e = addMonthsKey(s, 1); const r = yearRs.filter((x) => x.checkIn >= s && x.checkIn < e).reduce((a, x) => a + x.totalAmount, 0); const ex = yearExp.filter((x) => x.date >= s && x.date < e).reduce((a, x) => a + x.amount, 0); const ly = yearRs.filter((x) => x.checkIn >= addMonthsKey(s, -12) && x.checkIn < addMonthsKey(e, -12)).reduce((a, x) => a + x.totalAmount, 0); return { label: monthLabel(s), revenue: r, expenses: ex, profit: r - ex, lastYear: ly }; });
    const bySource = Object.entries(RESERVATION_SOURCE_META).map(([k, m]) => { const list = rev.filter((r) => r.source === k); const n = list.reduce((s, r) => s + r.nights, 0); const t = list.reduce((s, r) => s + r.totalAmount, 0); return { key: k, name: m.label, color: m.color, count: list.length, revenue: t, adr: n ? t / n : 0 }; }).filter((x) => x.count > 0).sort((a, b) => b.revenue - a.revenue);
    const byCategory = Object.values(expenses.reduce<Record<string, { name: string; value: number }>>((m, e) => { const name = cats.find((c) => c._id === e.categoryId)?.name ?? "Other"; m[name] = m[name] ?? { name, value: 0 }; m[name].value += e.amount; return m; }, {})).sort((a, b) => b.value - a.value);
    const realized = rev.filter((r) => r.status !== "CONFIRMED").reduce((s, r) => s + r.totalAmount, 0);
    const expected = rev.filter((r) => r.status === "CONFIRMED").reduce((s, r) => s + r.totalAmount, 0);
    const cancelledValue = rs.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").reduce((s, r) => s + r.totalAmount, 0);
    const early = rs.filter((r) => r.earlyCheckout);
    const releasedNights = early.reduce((s, r) => s + r.releasedNights, 0);
    const lostRevenue = early.reduce((s, r) => s + r.releasedNights * r.nightlyPrice, 0);
    const recoveredNights = rev.reduce((s, r) => s + r.recoveredNights, 0);
    const recoveredRevenue = rev.reduce((s, r) => s + r.recoveredNights * r.nightlyPrice, 0);
    const prevRecoveredRevenue = prevRev.reduce((s, r) => s + r.recoveredNights * r.nightlyPrice, 0);
    const startMs = Date.parse(start + "T00:00:00Z");
    const endMs = Date.parse(end + "T00:00:00Z");
    const releaseEvents = (await ctx.db.query("inventoryEvents").withIndex("by_at", (q) => q.gte("at", startMs).lt("at", endMs)).order("desc").collect()).filter((e) => (e.action === "RELEASED" || e.action === "HOLD_RELEASED") && aptF(e));
    const createdSince = (await ctx.db.query("reservations").withIndex("by_checkOut", (q) => q.gte("checkOut", start)).collect()).filter((r) => isRevenue(r.status) && aptF(r));
    const reopened = await Promise.all(releaseEvents.map(async (e) => { const r = e.reservationId ? await ctx.db.get(e.reservationId) : null; return { id: e._id, apartment: (await aptsL(e.apartmentId))?.code ?? "", start: e.startDate, end: e.endDate, nights: e.nights, value: e.estimatedValue ?? 0, reason: e.previousState ?? null, reservation: r?.code ?? null, guest: r ? (await customers(r.customerId))?.fullName ?? null : null, at: new Date(e.at).toISOString(), rebooked: createdSince.some((x) => x._id !== e.reservationId && x.apartmentId === e.apartmentId && x.checkIn < e.endDate && x.checkOut > e.startDate) }; }));
    const earlyCheckouts = await Promise.all(early.map(async (r) => ({ id: r._id, code: r.code, guest: (await customers(r.customerId))?.fullName ?? "", apartment: (await aptsL(r.apartmentId))?.code ?? "", planned: r.checkOut, actual: r.actualCheckOut ?? r.checkOut, releasedNights: r.releasedNights, value: r.releasedNights * r.nightlyPrice })));
    const prevOcc = pct(occN(prevRev, prevStart, prevEnd), apartments.length * days);
    const comparison = {
      period: [
        { metric: "Revenue", current: revenue, previous: prevRevenue, money: true },
        { metric: "Expenses", current: exp, previous: prevExp, money: true, invert: true },
        { metric: "Profit", current: profit, previous: prevProfit, money: true },
        { metric: "Commissions", current: com, previous: prevCom, money: true, invert: true },
        { metric: "Recovered revenue", current: recoveredRevenue, previous: prevRecoveredRevenue, money: true },
        { metric: "Reservations", current: rev.length, previous: prevRev.length, money: false },
        { metric: "Nights sold", current: nights, previous: occN(prevRev, prevStart, prevEnd), money: false },
        { metric: "Occupancy %", current: occupancy, previous: prevOcc, money: false },
      ],
      source: Object.entries(RESERVATION_SOURCE_META).map(([k, m]) => ({ name: m.label, color: m.color, current: rev.filter((r) => r.source === k).reduce((s, r) => s + r.totalAmount, 0), previous: prevRev.filter((r) => r.source === k).reduce((s, r) => s + r.totalAmount, 0), count: rev.filter((r) => r.source === k).length })).filter((x) => x.current > 0 || x.previous > 0).sort((a, b) => b.current - a.current),
      worker: workers.map((w) => { const list = rev.filter((r) => r.createdById === w._id); return { id: w._id, name: w.fullName ?? "", role: roles.find((r) => r._id === w.roleId)?.name ?? "", current: list.reduce((s, r) => s + r.totalAmount, 0), previous: prevRev.filter((r) => r.createdById === w._id).reduce((s, r) => s + r.totalAmount, 0), count: list.length, cancelled: rs.filter((r) => r.createdById === w._id && (r.status === "CANCELLED" || r.status === "NO_SHOW")).length, commissions: commissions.filter((c) => c.workerId === w._id).reduce((s, c) => s + c.amount, 0) }; }).filter((x) => x.current > 0 || x.previous > 0 || x.count > 0).sort((a, b) => b.current - a.current),
    };
    const apartmentRows = apartments.map((a) => { const list = rev.filter((r) => r.apartmentId === a._id); const r = list.reduce((s, x) => s + x.totalAmount, 0); const e = expenses.filter((x) => x.apartmentId === a._id).reduce((s, x) => s + x.amount, 0); const n = occN(allOverlap.filter((x) => x.apartmentId === a._id), start, end); const prev = prevRev.filter((x) => x.apartmentId === a._id).reduce((s, x) => s + x.totalAmount, 0); return { id: a._id, code: a.code, name: a.name, revenue: r, previous: prev, expenses: e, profit: r - e, occupancy: pct(n, days), adr: n ? r / n : 0, reservations: list.length, nights: n }; }).sort((a, b) => b.revenue - a.revenue);
    return {
      range: { start, end, days, prevStart, prevEnd, today },
      kpis: { revenue, revenueDelta: delta(revenue, prevRevenue), expenses: exp, expensesDelta: delta(exp, prevExp), commissions: com, profit, profitDelta: delta(profit, prevProfit), margin: pct(profit, revenue), collected, outstanding: revenue - collected, occupancy, adr: nights ? revenue / nights : 0, revpar: available ? revenue / available : 0, avgStay: rev.length ? rev.reduce((s, r) => s + r.nights, 0) / rev.length : 0, cancellationRate: pct(rs.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length, rs.length), repeatRate, avgValue: rev.length ? revenue / rev.length : 0, reservations: rev.length, nights, mom: delta(monthly[11].revenue, monthly[10].revenue), yoy: delta(monthly[11].revenue, monthly[11].lastYear) },
      realization: { realized, expected, collected, outstanding: revenue - collected, cancelledValue },
      recovery: { releasedNights, lostRevenue, recoveredNights, recoveredRevenue, recoveredDelta: delta(recoveredRevenue, prevRecoveredRevenue), reopenedNights: reopened.reduce((s, e) => s + e.nights, 0), reopenedValue: reopened.reduce((s, e) => s + e.value, 0), rebookRate: pct(reopened.filter((e) => e.rebooked).length, reopened.length), reopened: reopened.slice(0, 12), earlyCheckouts: earlyCheckouts.slice(0, 12) },
      comparison,
      daily,
      monthly,
      bySource,
      byCategory,
      apartmentRows,
      apartments: (await ctx.db.query("apartments").collect()).filter((a) => !a.deletedAt).sort((a, b) => a.code.localeCompare(b.code)).map((a) => ({ id: a._id, code: a.code, name: a.name })),
    };
  },
});

export const intelligence = query({
  args: {},
  returns: v.any(),
  handler: async (ctx) => {
    await assertPermission(ctx, "analytics.view");
    const settings = await getSettings(ctx);
    const today = todayKey(settings.timezone);
    const d30 = addDaysKey(today, -30);
    const d90 = addDaysKey(today, -90);
    const monthStart = startOfMonthKey(today);
    const d30ms = Date.now() - 30 * 86_400_000;
    const customersRaw = (await ctx.db.query("customers").collect()).filter((c) => !c.deletedAt);
    const customers = await Promise.all(customersRaw.map(async (c) => ({ ...c, reservations: await ctx.db.query("reservations").withIndex("by_customer_checkIn", (q) => q.eq("customerId", c._id)).collect() })));
    const reservations = await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", addMonthsKey(monthStart, -11))).collect();
    const apartments = (await ctx.db.query("apartments").withIndex("by_active", (q) => q.eq("isActive", true)).collect()).filter((a) => !a.deletedAt);
    const blocks = (await ctx.db.query("apartmentBlocks").withIndex("by_start", (q) => q.gte("startDate", addDaysKey(today, -400)).lt("startDate", addDaysKey(today, 30))).collect()).filter((b) => b.endDate > today);
    const incidentsOpen = (await ctx.db.query("customerIncidents").withIndex("by_open", (q) => q.eq("resolvedAt", undefined)).collect()).length;
    const withStay = customers.map((c) => ({ ...c, stays: c.reservations.filter((r) => isRevenue(r.status)) }));
    const returning = withStay.filter((c) => c.stays.length > 1);
    const totalStays = withStay.reduce((s, c) => s + c.stays.length, 0);
    const topReturning = returning.map((c) => ({ id: c._id, code: c.code, name: c.fullName, stays: c.stays.length, nights: c.stays.reduce((s, r) => s + r.nights, 0), value: c.stays.reduce((s, r) => s + r.totalAmount, 0), last: c.stays.map((r) => r.checkIn).sort().reverse()[0] })).sort((a, b) => b.stays - a.stays || b.value - a.value).slice(0, 10);
    const byNationality = Object.entries(customers.reduce<Record<string, number>>((m, c) => ((m[c.nationality ?? "Unknown"] = (m[c.nationality ?? "Unknown"] ?? 0) + 1), m), {})).map(([name, value]) => ({ name, value })).sort((a, b) => b.value - a.value).slice(0, 8);
    const newPerMonth = Array.from({ length: 12 }, (_, i) => { const s = addMonthsKey(monthStart, i - 11); const e = addMonthsKey(s, 1); const sMs = Date.parse(s + "T00:00:00Z"); const eMs = Date.parse(e + "T00:00:00Z"); return { label: monthLabel(s), newCustomers: customers.filter((c) => c._creationTime >= sMs && c._creationTime < eMs).length, returningStays: reservations.filter((r) => isRevenue(r.status) && r.checkIn >= s && r.checkIn < e && returning.some((c) => c._id === r.customerId)).length }; });
    const rev = reservations.filter((r) => isRevenue(r.status));
    const todayMs = Date.parse(today + "T00:00:00Z");
    const leadTimes = rev.map((r) => Math.max(0, Math.round((Date.parse(r.checkIn + "T00:00:00Z") - r.createdAt) / 86_400_000)));
    const bySource = Object.entries(RESERVATION_SOURCE_META).map(([k, m]) => { const list = rev.filter((r) => r.source === k); return { key: k, name: m.label, color: m.color, count: list.length, revenue: list.reduce((s, r) => s + r.totalAmount, 0), cancelled: reservations.filter((r) => r.source === k && (r.status === "CANCELLED" || r.status === "NO_SHOW")).length }; }).filter((x) => x.count > 0 || x.cancelled > 0).sort((a, b) => b.count - a.count);
    const monthlyRes = Array.from({ length: 12 }, (_, i) => { const s = addMonthsKey(monthStart, i - 11); const e = addMonthsKey(s, 1); const list = reservations.filter((r) => r.checkIn >= s && r.checkIn < e); return { label: monthLabel(s), confirmed: list.filter((r) => isRevenue(r.status)).length, cancelled: list.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length, early: list.filter((r) => r.earlyCheckout).length }; });
    const weekdayCheckIns = Array.from({ length: 7 }, (_, d) => ({ label: ["Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"][d], value: rev.filter((r) => weekdayOf(r.checkIn) === d).length }));
    const nightsDist = [1, 2, 3, 4, 5, 6, 7].map((n) => ({ label: n === 7 ? "7+" : String(n), value: rev.filter((r) => (n === 7 ? r.nights >= 7 : r.nights === n)).length }));
    const stateOn = (aptId: Id<"apartments">, d: string) => { if (reservations.some((r) => r.apartmentId === aptId && ["CONFIRMED", "PENDING", "CHECKED_IN"].includes(r.status) && r.checkIn <= d && effectiveCheckOut(r) > d)) return "booked"; if (blocks.some((b) => b.apartmentId === aptId && b.startDate <= d && b.endDate > d)) return "blocked"; return "free"; };
    const freeTonight = apartments.filter((a) => stateOn(a._id, today) === "free").map((a) => ({ id: a._id, code: a.code, name: a.name, basePrice: a.basePrice }));
    const freeTomorrow = apartments.filter((a) => stateOn(a._id, addDaysKey(today, 1)) === "free").map((a) => ({ id: a._id, code: a.code, name: a.name, basePrice: a.basePrice }));
    const gaps7: { apartment: string; id: Id<"apartments">; start: string; nights: number; value: number }[] = [];
    for (const a of apartments) { let run = 0; let runStart: string | null = null; for (let i = 0; i < 7; i++) { const d = addDaysKey(today, i); const free = stateOn(a._id, d) === "free"; if (free) { if (!runStart) runStart = d; run++; } if ((!free || i === 6) && run > 0 && runStart) { gaps7.push({ apartment: a.code, id: a._id, start: runStart, nights: run, value: run * a.basePrice }); run = 0; runStart = null; } } }
    let booked30 = 0; let blocked30 = 0;
    for (let i = 0; i < 30; i++) { const d = addDaysKey(today, i); for (const a of apartments) { const s = stateOn(a._id, d); if (s === "booked") booked30++; else if (s === "blocked") blocked30++; } }
    const aptMatrix = apartments.map((a) => { const list = rev.filter((r) => r.apartmentId === a._id && r.checkIn >= d90); const nights = list.reduce((s, r) => s + r.nights, 0); const revenue = list.reduce((s, r) => s + r.totalAmount, 0); const cancelledN = reservations.filter((r) => r.apartmentId === a._id && r.checkIn >= d90 && (r.status === "CANCELLED" || r.status === "NO_SHOW")).length; let up = 0; for (let i = 0; i < 30; i++) if (stateOn(a._id, addDaysKey(today, i)) === "booked") up++; return { id: a._id, code: a.code, name: a.name, reservations: list.length, nights, revenue, adr: nights ? revenue / nights : 0, occupancy: pct(nights, 90), avgStay: list.length ? nights / list.length : 0, cancelRate: pct(cancelledN, list.length + cancelledN), upcoming30: pct(up, 30) }; }).sort((a, b) => b.revenue - a.revenue);
    return {
      customers: { total: customers.length, returning: returning.length, new30: customers.filter((c) => c._creationTime >= d30ms).length, repeatRate: pct(returning.length, withStay.filter((c) => c.stays.length > 0).length), avgStay: totalStays ? withStay.reduce((s, c) => s + c.stays.reduce((x, r) => x + r.nights, 0), 0) / totalStays : 0, cancelRate: pct(customers.reduce((s, c) => s + c.reservations.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length, 0), customers.reduce((s, c) => s + c.reservations.length, 0)), withBalance: withStay.filter((c) => c.reservations.some((r) => isRevenue(r.status) && r.totalAmount - r.amountPaid > 0.5)).length, riskFlags: customers.filter((c) => c.riskLevel !== "NORMAL").length, verified: customers.filter((c) => c.verificationStatus === "VERIFIED").length, incidentsOpen, topReturning, byNationality, newPerMonth },
      reservations: { bookingsToday: reservations.filter((r) => r.createdAt >= todayMs).length, upcoming: reservations.filter((r) => ["CONFIRMED", "PENDING"].includes(r.status) && r.checkIn > today).length, inHouse: reservations.filter((r) => r.status === "CHECKED_IN").length, completed90: reservations.filter((r) => r.status === "CHECKED_OUT" && r.checkOut >= d90).length, cancelled90: reservations.filter((r) => r.status === "CANCELLED" && r.checkIn >= d90).length, noShow90: reservations.filter((r) => r.status === "NO_SHOW" && r.checkIn >= d90).length, early90: reservations.filter((r) => r.earlyCheckout && r.checkIn >= d90).length, extended90: reservations.filter((r) => r.originalCheckOut && r.checkOut > r.originalCheckOut && r.checkIn >= d90).length, avgValue: rev.length ? rev.reduce((s, r) => s + r.totalAmount, 0) / rev.length : 0, avgNights: rev.length ? rev.reduce((s, r) => s + r.nights, 0) / rev.length : 0, avgLead: leadTimes.length ? leadTimes.reduce((a, b) => a + b, 0) / leadTimes.length : 0, bySource, monthlyRes, weekdayCheckIns, nightsDist },
      inventory: { freeTonight, freeTomorrow, gaps7: gaps7.sort((a, b) => b.nights - a.nights).slice(0, 12), occ30: pct(booked30, apartments.length * 30), blocked30, maintenanceBlocks: blocks.filter((b) => b.type === "MAINTENANCE").length, externalBlocks: blocks.filter((b) => b.type === "EXTERNAL").length, recovered90: reservations.filter((r) => r.checkIn >= d90).reduce((s, r) => s + r.recoveredNights, 0), released90: reservations.filter((r) => r.checkIn >= d90).reduce((s, r) => s + r.releasedNights, 0), aptMatrix },
      d30,
    };
  },
});

// ── Reports ──────────────────────────────────────────────────
export const report = query({
  args: { type: v.string(), range: v.optional(v.string()), from: v.optional(v.string()), to: v.optional(v.string()), apartment: v.optional(v.string()), worker: v.optional(v.string()), source: v.optional(v.string()), status: v.optional(v.string()) },
  returns: v.any(),
  handler: async (ctx, sp) => {
    const actor = await assertPermission(ctx, "financials.view_reports");
    const settings = await getSettings(ctx);
    const { start, end, days } = resolveRange(sp.range ?? "month", settings.timezone, sp.from, sp.to);
    const showProfit = can(actor, "financials.view_profit");
    const roles = await ctx.db.query("roles").collect();
    const adminRole = roles.find((r) => r.key === "ADMIN");
    const apartments = (await ctx.db.query("apartments").collect()).filter((a) => !a.deletedAt).sort((a, b) => a.code.localeCompare(b.code));
    const workers = (await ctx.db.query("users").collect()).filter((u) => !u.deletedAt && u.roleId && u.roleId !== adminRole?._id).sort((a, b) => (a.fullName ?? "").localeCompare(b.fullName ?? ""));
    const cats = await ctx.db.query("expenseCategories").collect();
    const customers = loader(ctx, "customers");
    const aptsL = loader(ctx, "apartments");
    const usersL = loader(ctx, "users");
    const inRange = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", start).lt("checkIn", end)).collect()).filter((r) => (!sp.apartment || r.apartmentId === sp.apartment) && (!sp.worker || r.createdById === sp.worker) && (!sp.source || r.source === sp.source) && (!sp.status || r.status === sp.status)).sort((a, b) => a.checkIn.localeCompare(b.checkIn));
    const expenses = (await ctx.db.query("expenses").withIndex("by_date", (q) => q.gte("date", start).lt("date", end)).collect()).filter((e) => !e.deletedAt && (!sp.apartment || e.apartmentId === sp.apartment)).sort((a, b) => a.date.localeCompare(b.date));
    const startMs = Date.parse(start + "T00:00:00Z");
    const endMs = Date.parse(end + "T00:00:00Z");
    const fd = (k: string) => shortDate(k);
    const type = sp.type;
    let report: { title: string; columns: unknown[]; rows: unknown[]; summary: unknown[]; chart?: unknown };
    if (type === "occupancy") {
      const overlap = await overlapping(ctx, start, end, sp.apartment);
      const list = apartments.filter((a) => !sp.apartment || a._id === sp.apartment);
      const rows = list.map((a) => { const n = overlap.filter((r) => r.apartmentId === a._id).reduce((s, r) => s + nightsIn(r.checkIn, r.checkOut, start, end), 0); const rev = overlap.filter((r) => r.apartmentId === a._id && r.checkIn >= start && r.checkIn < end).reduce((s, r) => s + r.totalAmount, 0); return { apartment: `${a.code} · ${a.name}`, nights: n, available: days, occupancy: pct(n, days), revenue: rev, adr: n ? Math.round(rev / n) : 0 }; });
      const totalN = rows.reduce((s, r) => s + r.nights, 0);
      report = { title: "Occupancy report", columns: [{ key: "apartment", label: "Apartment" }, { key: "nights", label: "Occupied nights", align: "right" }, { key: "available", label: "Available nights", align: "right" }, { key: "occupancy", label: "Occupancy %", align: "right" }, { key: "adr", label: "ADR", align: "right", money: true }, { key: "revenue", label: "Revenue", align: "right", money: true }], rows, summary: [{ label: "Occupancy", value: `${pct(totalN, list.length * days)}%` }, { label: "Occupied nights", value: totalN }, { label: "Available nights", value: list.length * days }], chart: { key: "occupancy", label: "Occupancy %", nameKey: "apartment" } };
    } else if (type === "revenue" || type === "profit") {
      const rs = inRange.filter((r) => isRevenue(r.status));
      const coms = await Promise.all((await ctx.db.query("commissions").collect()).filter((c) => !["CANCELLED", "REVERSED"].includes(c.status)).map(async (c) => ({ ...c, checkIn: (await ctx.db.get(c.reservationId))?.checkIn ?? "" })));
      const buckets = new Map<string, { label: string; revenue: number; expenses: number; commissions: number }>();
      const step = days > 62 ? "month" : days > 14 ? "week" : "day";
      const keyOf = (d: string) => (step === "month" ? d.slice(0, 7) : step === "week" ? startOfWeekKey(d) : d);
      const labelOf = (k: string) => (step === "month" ? monthLabel(k + "-01", true) : step === "week" ? `Week of ${fd(k)}` : fd(k));
      const add = (d: string, f: "revenue" | "expenses" | "commissions", val: number) => { const k = keyOf(d); const b = buckets.get(k) ?? { label: labelOf(k), revenue: 0, expenses: 0, commissions: 0 }; b[f] += val; buckets.set(k, b); };
      rs.forEach((r) => add(r.checkIn, "revenue", r.totalAmount));
      expenses.forEach((e) => add(e.date, "expenses", e.amount));
      coms.filter((c) => c.checkIn >= start && c.checkIn < end).forEach((c) => add(c.checkIn, "commissions", c.amount));
      const rows = [...buckets.entries()].sort(([a], [b]) => a.localeCompare(b)).map(([, b]) => ({ period: b.label, revenue: b.revenue, expenses: b.expenses, commissions: b.commissions, profit: b.revenue - b.expenses - b.commissions }));
      const tot = rows.reduce((s, r) => ({ revenue: s.revenue + r.revenue, expenses: s.expenses + r.expenses, commissions: s.commissions + r.commissions, profit: s.profit + r.profit }), { revenue: 0, expenses: 0, commissions: 0, profit: 0 });
      report = { title: type === "profit" ? "Profit report" : "Revenue report", columns: [{ key: "period", label: "Period" }, { key: "revenue", label: "Revenue", align: "right", money: true }, ...(type === "profit" && showProfit ? [{ key: "expenses", label: "Expenses", align: "right", money: true }, { key: "commissions", label: "Commissions", align: "right", money: true }, { key: "profit", label: "Net profit", align: "right", money: true }] : [])], rows, summary: [{ label: "Revenue", value: tot.revenue, money: true }, ...(type === "profit" && showProfit ? [{ label: "Expenses", value: tot.expenses, money: true }, { label: "Commissions", value: tot.commissions, money: true }, { label: "Net profit", value: tot.profit, money: true }, { label: "Margin", value: `${pct(tot.profit, tot.revenue)}%` }] : [])], chart: { key: type === "profit" ? "profit" : "revenue", label: type === "profit" ? "Net profit" : "Revenue", nameKey: "period", money: true } };
    } else if (type === "expenses") {
      const rows = await Promise.all(expenses.map(async (e) => ({ date: fd(e.date), code: e.code, category: cats.find((c) => c._id === e.categoryId)?.name ?? "", apartment: e.apartmentId ? (await aptsL(e.apartmentId))?.code ?? "General" : "General", description: e.description, vendor: e.vendor ?? "", method: e.paymentMethod, amount: e.amount, addedBy: (await usersL(e.addedById))?.fullName ?? "", isRecurring: e.isRecurring })));
      report = { title: "Expenses report", columns: [{ key: "date", label: "Date" }, { key: "code", label: "Code" }, { key: "category", label: "Category" }, { key: "apartment", label: "Apt" }, { key: "description", label: "Description" }, { key: "vendor", label: "Vendor" }, { key: "method", label: "Method" }, { key: "amount", label: "Amount", align: "right", money: true }, { key: "addedBy", label: "Added by" }], rows, summary: [{ label: "Entries", value: rows.length }, { label: "Total", value: rows.reduce((s, e) => s + e.amount, 0), money: true }, { label: "Recurring", value: rows.filter((e) => e.isRecurring).reduce((s, e) => s + e.amount, 0), money: true }] };
    } else if (type === "apartments") {
      const rsAll = await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", start).lt("checkIn", end)).collect();
      const expAll = (await ctx.db.query("expenses").withIndex("by_date", (q) => q.gte("date", start).lt("date", end)).collect()).filter((e) => !e.deletedAt);
      const rows = apartments.map((a) => { const list = rsAll.filter((r) => r.apartmentId === a._id); const rev = list.filter((r) => isRevenue(r.status)); const revenue = rev.reduce((s, r) => s + r.totalAmount, 0); const nights = rev.reduce((s, r) => s + r.nights, 0); const ex = expAll.filter((e) => e.apartmentId === a._id).reduce((s, e) => s + e.amount, 0); return { apartment: `${a.code} · ${a.name}`, reservations: list.length, confirmed: rev.length, cancelled: list.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length, nights, adr: nights ? Math.round(revenue / nights) : 0, revenue, expenses: ex, profit: revenue - ex }; });
      report = { title: "Apartment performance", columns: [{ key: "apartment", label: "Apartment" }, { key: "reservations", label: "Reservations", align: "right" }, { key: "confirmed", label: "Confirmed", align: "right" }, { key: "cancelled", label: "Cancelled", align: "right" }, { key: "nights", label: "Nights", align: "right" }, { key: "adr", label: "ADR", align: "right", money: true }, { key: "revenue", label: "Revenue", align: "right", money: true }, ...(showProfit ? [{ key: "expenses", label: "Expenses", align: "right", money: true }, { key: "profit", label: "Profit", align: "right", money: true }] : [])], rows, summary: [{ label: "Revenue", value: rows.reduce((s, r) => s + r.revenue, 0), money: true }, { label: "Best apartment", value: [...rows].sort((a, b) => b.revenue - a.revenue)[0]?.apartment ?? "—" }], chart: { key: "revenue", label: "Revenue", nameKey: "apartment", money: true } };
    } else if (type === "workers") {
      const created = (await ctx.db.query("reservations").withIndex("by_createdAt", (q) => q.gte("createdAt", startMs).lt("createdAt", endMs)).collect());
      const coms = (await ctx.db.query("commissions").collect()).filter((c) => c.createdAt >= startMs && c.createdAt < endMs);
      const checks = [...(await ctx.db.query("reservations").withIndex("by_status_checkIn", (q) => q.eq("status", "CHECKED_IN")).collect()), ...(await ctx.db.query("reservations").withIndex("by_status_checkIn", (q) => q.eq("status", "CHECKED_OUT")).collect())];
      const rows = workers.map((w) => { const list = created.filter((r) => r.createdById === w._id); const conf = list.filter((r) => isRevenue(r.status)); return { worker: w.fullName ?? "", created: list.length, confirmed: conf.length, cancelled: list.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length, conversion: pct(conf.length, list.length), revenue: conf.reduce((s, r) => s + r.totalAmount, 0), checkIns: checks.filter((c) => c.checkedInById === w._id && c.checkedInAt && c.checkedInAt >= startMs && c.checkedInAt < endMs).length, checkOuts: checks.filter((c) => c.checkedOutById === w._id && c.checkedOutAt && c.checkedOutAt >= startMs && c.checkedOutAt < endMs).length, commission: coms.filter((c) => c.workerId === w._id && !["CANCELLED", "REVERSED"].includes(c.status)).reduce((s, c) => s + c.amount, 0) }; });
      report = { title: "Worker performance", columns: [{ key: "worker", label: "Worker" }, { key: "created", label: "Created", align: "right" }, { key: "confirmed", label: "Confirmed", align: "right" }, { key: "cancelled", label: "Cancelled", align: "right" }, { key: "conversion", label: "Conversion %", align: "right" }, { key: "revenue", label: "Revenue generated", align: "right", money: true }, { key: "checkIns", label: "Check-ins", align: "right" }, { key: "checkOuts", label: "Check-outs", align: "right" }, { key: "commission", label: "Commission", align: "right", money: true }], rows, summary: [{ label: "Reservations created", value: rows.reduce((s, r) => s + r.created, 0) }, { label: "Commissions", value: rows.reduce((s, r) => s + r.commission, 0), money: true }], chart: { key: "confirmed", label: "Confirmed reservations", nameKey: "worker" } };
    } else if (type === "commissions") {
      const coms = (await ctx.db.query("commissions").collect()).filter((c) => c.createdAt >= startMs && c.createdAt < endMs && (!sp.worker || c.workerId === sp.worker)).sort((a, b) => a.createdAt - b.createdAt);
      const rows = await Promise.all(coms.map(async (c) => ({ date: new Date(c.createdAt).toISOString().slice(0, 10), code: c.code, worker: (await usersL(c.workerId))?.fullName ?? "", reservation: (await ctx.db.get(c.reservationId))?.code ?? "", trigger: c.triggerEvent.replace(/_/g, " ").toLowerCase(), status: c.status.toLowerCase(), amount: c.amount, paidAt: c.paidAt ? new Date(c.paidAt).toISOString().slice(0, 10) : "", _status: c.status })));
      report = { title: "Commission report", columns: [{ key: "date", label: "Created" }, { key: "code", label: "Code" }, { key: "worker", label: "Worker" }, { key: "reservation", label: "Reservation" }, { key: "trigger", label: "Trigger" }, { key: "status", label: "Status" }, { key: "amount", label: "Amount", align: "right", money: true }, { key: "paidAt", label: "Paid" }], rows, summary: [{ label: "Total", value: rows.filter((c) => !["CANCELLED", "REVERSED"].includes(c._status)).reduce((s, c) => s + c.amount, 0), money: true }, { label: "Pending", value: rows.filter((c) => c._status === "PENDING" || c._status === "APPROVED").reduce((s, c) => s + c.amount, 0), money: true }, { label: "Paid", value: rows.filter((c) => c._status === "PAID").reduce((s, c) => s + c.amount, 0), money: true }] };
    } else if (type === "customers") {
      const ids = new Set(inRange.map((r) => r.customerId));
      const rows = (await Promise.all([...ids].map(async (id) => { const c = await customers(id); if (!c || c.deletedAt) return null; const all = await ctx.db.query("reservations").withIndex("by_customer_checkIn", (q) => q.eq("customerId", id)).collect(); const ok = all.filter((r) => isRevenue(r.status)); return { code: c.code, name: c.fullName, phone: c.phone, nationality: c.nationality ?? "", idNumber: c.idNumber ?? "", stays: ok.length, nights: ok.reduce((s, r) => s + r.nights, 0), cancelled: all.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length, value: ok.reduce((s, r) => s + r.totalAmount, 0), lastStay: ok.length ? fd(ok.map((r) => r.checkIn).sort().reverse()[0]) : "", _last: c.lastName }; }))).filter((x): x is NonNullable<typeof x> => !!x).sort((a, b) => a._last.localeCompare(b._last));
      report = { title: "Customer report", columns: [{ key: "code", label: "Code" }, { key: "name", label: "Customer" }, { key: "phone", label: "Phone" }, { key: "nationality", label: "Nationality" }, { key: "idNumber", label: "ID" }, { key: "stays", label: "Stays", align: "right" }, { key: "nights", label: "Nights", align: "right" }, { key: "cancelled", label: "Cancelled", align: "right" }, { key: "value", label: "Lifetime value", align: "right", money: true }, { key: "lastStay", label: "Last stay" }], rows, summary: [{ label: "Customers", value: rows.length }, { label: "Returning", value: rows.filter((c) => c.stays > 1).length }] };
    } else {
      const rows = await Promise.all(inRange.map(async (r) => ({ code: r.code, customer: (await customers(r.customerId))?.fullName ?? "", apartment: (await aptsL(r.apartmentId))?.code ?? "", checkIn: fd(r.checkIn), checkOut: fd(r.checkOut), nights: r.nights, source: RESERVATION_SOURCE_META[r.source as keyof typeof RESERVATION_SOURCE_META]?.label ?? r.source, status: RESERVATION_STATUS_META[r.status as keyof typeof RESERVATION_STATUS_META]?.label ?? r.status, total: r.totalAmount, paid: r.amountPaid, createdBy: (await usersL(r.createdById))?.fullName ?? "" })));
      report = { title: "Reservation report", columns: [{ key: "code", label: "Code" }, { key: "customer", label: "Customer" }, { key: "apartment", label: "Apt" }, { key: "checkIn", label: "Check-in" }, { key: "checkOut", label: "Check-out" }, { key: "nights", label: "Nights", align: "right" }, { key: "source", label: "Source" }, { key: "status", label: "Status" }, { key: "total", label: "Total", align: "right", money: true }, { key: "paid", label: "Paid", align: "right", money: true }, { key: "createdBy", label: "Created by" }], rows, summary: [{ label: "Reservations", value: inRange.length }, { label: "Confirmed / completed", value: inRange.filter((r) => isRevenue(r.status)).length }, { label: "Cancelled / no-show", value: inRange.filter((r) => r.status === "CANCELLED" || r.status === "NO_SHOW").length }, { label: "Revenue", value: inRange.filter((r) => isRevenue(r.status)).reduce((s, r) => s + r.totalAmount, 0), money: true }] };
    }
    return { report, period: { start, end: addDaysKey(end, -1), days }, apartments: apartments.map((a) => ({ id: a._id, code: a.code, name: a.name })), workers: workers.map((w) => ({ id: w._id, fullName: w.fullName ?? "" })), businessName: settings.businessName, currency: settings.currency };
  },
});

// ── Data quality ─────────────────────────────────────────────
export const dataQuality = query({
  args: {},
  returns: v.any(),
  handler: async (ctx) => {
    await assertPermission(ctx, "data.quality", "settings.manage");
    const settings = await getSettings(ctx);
    const today = todayKey(settings.timezone);
    const currency = settings.currency;
    const customers = (await ctx.db.query("customers").collect()).filter((c) => !c.deletedAt);
    const aptsL = loader(ctx, "apartments");
    const custL = loader(ctx, "customers");
    const hasIdDoc = async (id: Id<"customers">) => (await ctx.db.query("documents").withIndex("by_customer", (q) => q.eq("customerId", id)).collect()).some((d) => !d.deletedAt && (d.category === "ID_FRONT" || d.category === "ID_BACK"));
    const upcoming14 = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", today).lte("checkIn", addDaysKey(today, 14))).collect()).filter((r) => r.status === "CONFIRMED" || r.status === "PENDING");
    const upcomingNoId: { id: Id<"customers">; code: string; fullName: string }[] = [];
    for (const cid of new Set(upcoming14.map((r) => r.customerId))) { const c = await custL(cid); if (c && !c.deletedAt && !(await hasIdDoc(cid))) upcomingNoId.push({ id: c._id, code: c.code, fullName: c.fullName }); }
    const inHouse = await ctx.db.query("reservations").withIndex("by_status_checkIn", (q) => q.eq("status", "CHECKED_IN")).collect();
    const inHouseNoContract: { id: Id<"reservations">; code: string; customer: string; apartment: string }[] = [];
    for (const r of inHouse) { const cs = await ctx.db.query("contracts").withIndex("by_reservation", (q) => q.eq("reservationId", r._id)).collect(); if (!cs.some((c) => c.status === "SIGNED")) inHouseNoContract.push({ id: r._id, code: r.code, customer: (await custL(r.customerId))?.fullName ?? "", apartment: (await aptsL(r.apartmentId))?.code ?? "" }); }
    const noSource = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", addDaysKey(today, -90))).collect()).filter((r) => r.source === "OTHER").length;
    const apartments = (await ctx.db.query("apartments").withIndex("by_active", (q) => q.eq("isActive", true)).collect()).filter((a) => !a.deletedAt);
    const aptsNoPhotos: { id: Id<"apartments">; code: string; name: string }[] = [];
    for (const a of apartments) if (!(await ctx.db.query("apartmentImages").withIndex("by_apartment", (q) => q.eq("apartmentId", a._id)).collect()).some((i) => !i.archivedAt)) aptsNoPhotos.push({ id: a._id, code: a.code, name: a.name });
    const checkedOut = (await ctx.db.query("reservations").withIndex("by_status_checkIn", (q) => q.eq("status", "CHECKED_OUT")).collect()).filter((r) => r.checkOut >= addDaysKey(today, -120));
    const unpaidOut = await Promise.all(checkedOut.filter((r) => r.totalAmount - r.amountPaid > 0.5).map(async (r) => ({ id: r._id, code: r.code, due: r.totalAmount - r.amountPaid, checkOut: r.checkOut, customer: (await custL(r.customerId))?.fullName ?? "" })));
    const badPhones = customers.filter((c) => !c.phone.startsWith("+")).slice(0, 50);
    const noNationality = customers.filter((c) => !c.nationality || !c.dateOfBirth).length;
    const upcoming7 = (await ctx.db.query("reservations").withIndex("by_checkIn", (q) => q.gte("checkIn", today).lte("checkIn", addDaysKey(today, 7))).collect()).filter((r) => r.status === "CONFIRMED");
    const unverifiedUpcoming: { id: Id<"customers">; code: string; fullName: string }[] = [];
    for (const cid of new Set(upcoming7.map((r) => r.customerId))) { const c = await custL(cid); if (c && !c.deletedAt && c.verificationStatus !== "VERIFIED") unverifiedUpcoming.push({ id: c._id, code: c.code, fullName: c.fullName }); }
    const orphanHolds = await Promise.all((await ctx.db.query("apartmentBlocks").withIndex("by_type", (q) => q.eq("type", "HOLD")).collect()).filter((h) => h.endDate < today).map(async (h) => ({ id: h._id, apartmentId: h.apartmentId, startDate: h.startDate, endDate: h.endDate, apartment: (await aptsL(h.apartmentId))?.code ?? "" })));
    const staleInquiries = await Promise.all((await ctx.db.query("reservations").withIndex("by_status_checkIn", (q) => q.eq("status", "INQUIRY").lt("checkIn", today)).take(50)).map(async (r) => ({ id: r._id, code: r.code, checkIn: r.checkIn, customer: (await custL(r.customerId))?.fullName ?? "" })));
    const groups = new Map<string, Doc<"customers">[]>();
    const push = (k: string, c: Doc<"customers">) => groups.set(k, [...(groups.get(k) ?? []), c]);
    for (const c of customers) { if (c.phoneKey.length === 8) push(`p:${c.phoneKey}`, c); if (c.idNumber && c.idNumber.trim().length >= 4) push(`i:${c.idNumber.trim().toUpperCase()}`, c); if (c.email) push(`e:${c.email.toLowerCase()}`, c); }
    const seenPair = new Set<string>();
    const dupItems: { label: string; sub?: string; href: string }[] = [];
    for (const [k, list] of groups) { const ids = [...new Set(list.map((c) => c._id))].sort(); if (ids.length < 2) continue; const key = ids.join("|"); if (seenPair.has(key)) continue; seenPair.add(key); const first = list[0]; dupItems.push({ label: [...new Map(list.map((c) => [c._id, c])).values()].map((c) => `${c.fullName} (${c.code})`).join(" ↔ "), sub: k.startsWith("p:") ? `same phone ${first.phone}` : k.startsWith("i:") ? `same ID ${first.idNumber}` : `same email ${first.email}`, href: `/customers/${first._id}` }); }
    const issues = [
      { key: "duplicates", title: "Possible duplicate customers", description: "Profiles sharing a phone number, ID number or email. Merge from the customer page.", severity: dupItems.length ? "important" : "normal", count: dupItems.length, href: "/customers?segment=duplicates", items: dupItems.slice(0, 12) },
      { key: "missing-id", title: "Upcoming guests without ID documents", description: "Arrivals in the next 14 days with no identity document on file.", severity: upcomingNoId.length ? "important" : "normal", count: upcomingNoId.length, href: "/customers?segment=missing_id", items: upcomingNoId.slice(0, 12).map((c) => ({ label: c.fullName, sub: c.code, href: `/customers/${c.id}?tab=documents` })) },
      { key: "unverified", title: "Unverified guests arriving this week", description: "Confirmed arrivals within 7 days whose identity is not marked verified.", severity: "normal", count: unverifiedUpcoming.length, href: "/customers?segment=unverified", items: unverifiedUpcoming.slice(0, 12).map((c) => ({ label: c.fullName, sub: c.code, href: `/customers/${c.id}?tab=risk` })) },
      { key: "contracts", title: "In-house guests without a signed contract", description: "Checked-in reservations with no signed contract version.", severity: inHouseNoContract.length ? "critical" : "normal", count: inHouseNoContract.length, href: "/documents", items: inHouseNoContract.slice(0, 12).map((r) => ({ label: `${r.customer} · ${r.apartment}`, sub: r.code, href: `/reservations/${r.id}` })) },
      { key: "unpaid", title: "Checked-out reservations with money due", description: "Completed stays in the last 120 days that were never fully paid.", severity: unpaidOut.length ? "critical" : "normal", count: unpaidOut.length, href: "/payments?filter=outstanding", items: unpaidOut.slice(0, 12).map((r) => ({ label: `${r.customer} · ${fmtMoney(r.due, currency)}`, sub: `${r.code} · left ${r.checkOut}`, href: `/reservations/${r.id}?tab=payments` })) },
      { key: "source", title: "Reservations with source “Other”", description: "Last 90 days. Knowing the channel is what makes source analytics true.", severity: noSource > 10 ? "important" : "normal", count: noSource, href: "/reservations?source=OTHER", items: [] },
      { key: "photos", title: "Apartments without photos", description: "Cards, the showroom and the gallery stay empty for these apartments.", severity: aptsNoPhotos.length ? "important" : "normal", count: aptsNoPhotos.length, href: "/apartments", items: aptsNoPhotos.map((a) => ({ label: `${a.code} · ${a.name}`, href: `/apartments/${a.id}?tab=photos` })) },
      { key: "phones", title: "Phone numbers not in international format", description: "Numbers that do not start with +. WhatsApp links and duplicate detection need E.164.", severity: "normal", count: badPhones.length, href: "/customers", items: badPhones.slice(0, 12).map((c) => ({ label: c.fullName, sub: c.phone, href: `/customers/${c._id}/edit` })) },
      { key: "profile", title: "Incomplete customer profiles", description: "Missing nationality or date of birth (needed for contracts and police forms).", severity: "normal", count: noNationality, href: "/customers", items: [] },
      { key: "holds", title: "Expired holds still on the calendar", description: "Hold blocks whose dates are in the past. They no longer block anything but clutter the ledger.", severity: "normal", count: orphanHolds.length, href: "/calendar?holds=1", items: orphanHolds.slice(0, 12).map((h) => ({ label: `${h.apartment} · ${h.startDate} → ${h.endDate}`, href: `/apartments/${h.apartmentId}?tab=timeline` })) },
      { key: "inquiries", title: "Stale inquiries", description: "Inquiries whose check-in date has passed. Convert or close them.", severity: "normal", count: staleInquiries.length, href: "/reservations?status=INQUIRY", items: staleInquiries.slice(0, 12).map((r) => ({ label: r.customer, sub: `${r.code} · ${r.checkIn}`, href: `/reservations/${r.id}` })) },
    ];
    const score = Math.max(0, 100 - issues.reduce((s, i) => s + (i.severity === "critical" ? 6 : i.severity === "important" ? 3 : 1) * Math.min(i.count, 5), 0));
    return { issues, score, checkedAt: new Date().toISOString(), totalCustomers: customers.length };
  },
});

void withId;
void nightsBetweenKeys;