import { StudentRequestStatus, StudentRequestType } from '@prisma/client';
import { db } from '../../lib/prisma';
import { registerReport } from './reports.registry';
import { adherence } from '../roster/roster-adherence';

/**
 * Sprint 1 (TEPL) reports: yesterday's shift adherence, and the month's leave
 * register. Imported for its side effect by reports.service.
 */

const IST_MS = 330 * 60_000;
const istDay = (d: Date) => new Date(d.getTime() + IST_MS).toISOString().slice(0, 10);
const time = (d: Date | null) => (d ? new Date(d.getTime() + IST_MS).toISOString().slice(11, 16) : '');
const words = (s: string) => s.replace(/_/g, ' ').toLowerCase().replace(/^./, (c) => c.toUpperCase());
/** The instant an India day starts. */
const dayStart = (ymd: string) => new Date(Date.parse(`${ymd}T00:00:00.000Z`) - IST_MS);
/** The Monday of the week an India day falls in. */
const mondayOf = (ymd: string) => {
  const t = Date.parse(`${ymd}T00:00:00.000Z`);
  return new Date(t - ((new Date(t).getUTCDay() + 6) % 7) * 86_400_000).toISOString().slice(0, 10);
};
/** "45 min", "1 h 20 min", "3 h", from milliseconds. */
const durationText = (ms: number) => {
  const mins = Math.max(0, Math.round(ms / 60_000));
  if (mins < 60) return `${mins} min`;
  return mins % 60 ? `${Math.floor(mins / 60)} h ${mins % 60} min` : `${mins / 60} h`;
};

const STATUS_WORDS: Record<string, string> = {
  ON_TIME: 'On time',
  LATE: 'Late',
  NO_SWIPE_OUT: 'No swipe-out',
  ON_LEAVE: 'On leave',
  ON_LEAVE_IN_HOSTEL: 'On leave, in hostel',
  WEEKLY_OFF: 'Weekly off',
  OFF_OUT_AFTER_CURFEW: 'Off, out after curfew',
  UPCOMING: 'Not due yet',
  NO_DATA: 'No gate data',
};

registerReport({
  key: 'shift-adherence',
  title: 'Shift adherence (yesterday)',
  description: 'Each rostered employee against the gate: out on time, late, no swipe-out, on leave but in the hostel.',
  permission: 'VIEW_ATTENDANCE',
  feature: 'shiftRoster',
  async build({ propertyId, now }) {
    const date = istDay(new Date(now.getTime() - 86_400_000));
    const a = await adherence(date, { propertyId }, now);
    const rows = [...a.rows].sort((x, y) => Number(y.flagged) - Number(x.flagged) || x.name.localeCompare(y.name));
    return [
      {
        name: `Adherence ${date}`,
        columns: [{ header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Department', width: 18 }, { header: 'Room / bed', width: 12 }, { header: 'Shift' }, { header: 'Starts' }, { header: 'Status', width: 22 }, { header: 'Out at' }, { header: 'Back at' }, { header: 'Minutes late', width: 13 }, { header: 'Needs a look', width: 13 }],
        rows: rows.map((r) => [r.employeeCode ?? '', r.name, r.department ?? '', r.room ?? '', r.shift.code, r.shift.startTime ?? '', STATUS_WORDS[r.status], time(r.outAt), time(r.inAt), r.minutesLate, r.flagged ? 'Yes' : '']),
      },
      {
        name: 'Summary',
        columns: [{ header: 'What', width: 24 }, { header: 'People' }],
        rows: [
          ['Rostered', a.summary.rostered],
          ['On time', a.summary.onTime],
          ['Late', a.summary.late],
          ['No swipe-out', a.summary.noSwipeOut],
          ['On leave', a.summary.onLeave],
          ['On leave, in hostel', a.summary.onLeaveInHostel],
          ['Weekly off', a.summary.weeklyOff],
          ['Off, out after curfew', a.summary.offOutAfterCurfew],
          ['No gate data', a.summary.noData],
        ],
      },
    ];
  },
});

registerReport({
  key: 'leave-register',
  title: 'Leave register (this month)',
  description: 'Every leave touching this month: type, days, and who approved each step.',
  permission: 'MANAGE_STUDENTS',
  feature: 'leavePolicy',
  ranged: true,
  defaultDays: 'month',
  async build({ propertyId, now, range }) {
    const ist = new Date(now.getTime() + IST_MS);
    const from = range ? new Date(`${range.from}T00:00:00.000Z`) : new Date(Date.UTC(ist.getUTCFullYear(), ist.getUTCMonth(), 1));
    const to = range ? new Date(`${range.to}T00:00:00.000Z`) : new Date(Date.UTC(ist.getUTCFullYear(), ist.getUTCMonth() + 1, 0));
    const rows = await db.studentRequest.findMany({
      where: { type: StudentRequestType.LEAVE, status: { in: [StudentRequestStatus.OPEN, StudentRequestStatus.IN_PROGRESS, StudentRequestStatus.APPROVED, StudentRequestStatus.REJECTED] }, fromDate: { lte: to }, toDate: { gte: from }, ...(propertyId ? { student: { propertyId } } : {}) },
      include: { leaveType: { select: { code: true, name: true } }, student: { select: { employeeCode: true, user: { select: { name: true } }, department: { select: { name: true } } } } },
      orderBy: { fromDate: 'asc' },
      take: 5000,
    });
    const names = new Map((await db.user.findMany({ where: { id: { in: rows.flatMap((r) => [r.hostelApprovedById, r.plantApprovedById].filter((x): x is string => !!x)) } }, select: { id: true, name: true } })).map((u) => [u.id, u.name]));
    const stageWords: Record<string, string> = { HOSTEL: 'With the hostel', PLANT: 'With the plant', DONE: 'Decided' };
    return [
      {
        name: 'Leave',
        columns: [{ header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Department', width: 18 }, { header: 'Type', width: 18 }, { header: 'From', width: 13 }, { header: 'To', width: 13 }, { header: 'Days' }, { header: 'Status', width: 12 }, { header: 'Step', width: 16 }, { header: 'Hostel decided by', width: 20 }, { header: 'Plant decided by', width: 20 }, { header: 'Reason', width: 40 }],
        rows: rows.map((r) => [
          r.student.employeeCode ?? '',
          r.student.user.name,
          r.student.department?.name ?? '',
          r.leaveType?.name ?? 'Leave',
          r.fromDate,
          r.toDate,
          r.leaveDays ?? (r.fromDate && r.toDate ? Math.round((r.toDate.getTime() - r.fromDate.getTime()) / 86_400_000) + 1 : null),
          words(r.status),
          r.leaveStage ? stageWords[r.leaveStage] ?? r.leaveStage : '',
          r.hostelApprovedById ? names.get(r.hostelApprovedById) ?? '' : '',
          r.plantApprovedById ? names.get(r.plantApprovedById) ?? '' : '',
          r.body,
        ]),
      },
    ];
  },
});

// ── Sprint 2 (TEPL) ─────────────────────────────────────────────────────────

registerReport({
  key: 'discipline-register',
  title: 'Discipline register (90 days)',
  description: 'Every incident in the last 90 days, and the repeat offenders.',
  permission: 'MANAGE_STUDENTS',
  feature: 'discipline',
  ranged: true,
  defaultDays: 90,
  async build({ propertyId, now, range }) {
    const { listIncidents, summary } = await import('../discipline/discipline.service');
    const from = range ? dayStart(range.from) : new Date(now.getTime() - 90 * 86_400_000);
    const to = range ? new Date(dayStart(range.to).getTime() + 86_400_000) : now;
    const list = await listIncidents({ from, to, propertyId }, null);
    const rows = Array.isArray(list) ? list : list.items;
    const s = await summary(from, to, propertyId);
    return [
      {
        name: 'Incidents',
        columns: [{ header: 'When', width: 18 }, { header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Department', width: 16 }, { header: 'Category', width: 14 }, { header: 'Severity' }, { header: 'What happened', width: 50 }, { header: 'Witnesses', width: 24 }, { header: 'Action', width: 16 }, { header: 'Status' }, { header: 'Recorded by', width: 20 }, { header: 'In 90 days' }],
        rows: rows.map((r) => [r.occurredAt, r.student.employeeCode ?? '', r.student.user.name, r.student.department?.name ?? '', words(r.category), words(r.severity), r.description, r.witnesses ?? '', words(r.action), words(r.status), r.reportedBy ?? '', r.recentCount]),
      },
      {
        name: 'Repeat offenders',
        columns: [{ header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Department', width: 16 }, { header: 'Incidents in 90 days', width: 20 }],
        rows: s.repeatOffenders.map((p) => [p.employeeCode ?? '', p.name, p.department ?? '', p.incidents]),
      },
    ];
  },
});

registerReport({
  key: 'grievances',
  title: 'Grievances (30 days)',
  description: 'Complaints and repairs by category: how many, how fast, escalated, and how they were rated.',
  permission: 'MANAGE_STUDENTS',
  feature: 'grievanceDesk',
  async build({ propertyId }) {
    const { grievanceSummary } = await import('../requests/grievance.service');
    const s = await grievanceSummary(30, propertyId);
    const row = (label: string, x: typeof s.overall) => [label, x.raised, x.closed, x.open, x.avgHoursToClose, x.withinTarget, x.escalated, x.anonymous, x.avgRating, x.ratings];
    return [
      {
        name: 'By category',
        columns: [{ header: 'Category', width: 28 }, { header: 'Raised' }, { header: 'Closed' }, { header: 'Open' }, { header: 'Avg hours to close', width: 18 }, { header: '% within target', width: 16 }, { header: 'Escalated' }, { header: 'Anonymous' }, { header: 'Avg rating' }, { header: 'Ratings' }],
        rows: [row('All', s.overall), ...s.byCategory.map((c) => row(`${words(c.type)} · ${words(c.category)}`, c))],
      },
    ];
  },
});

registerReport({
  key: 'feedback',
  title: 'Employee feedback (6 months)',
  description: 'Average ratings by area and month, the comments, and the words that keep coming up.',
  permission: 'VIEW_ANALYTICS',
  feature: 'feedback',
  async build({ propertyId }) {
    const { summary, AREAS } = await import('../feedback/feedback.service');
    const s = await summary(6, propertyId);
    return [
      {
        name: 'By month',
        columns: [{ header: 'Month' }, { header: 'Responses' }, { header: 'Overall' }, ...AREAS.map((a) => ({ header: words(a), width: 13 }))],
        rows: s.months.map((m) => [m.period, m.responses, m.overall, ...AREAS.map((a) => m.areas[a])]),
      },
      {
        name: 'Comments',
        columns: [{ header: 'Month' }, { header: 'From', width: 22 }, { header: 'Overall' }, { header: 'Comment', width: 80 }],
        rows: s.comments.map((c) => [c.period, c.from ?? 'Anonymous', c.overall, c.comment]),
      },
      { name: 'Themes', columns: [{ header: 'Word', width: 20 }, { header: 'Comments mentioning it', width: 24 }], rows: s.themes.map((t) => [t.word, t.count]) },
    ];
  },
});

// ── Sprint 3 (TEPL) ─────────────────────────────────────────────────────────

registerReport({
  key: 'cleaning-frequency',
  title: 'Cleaning frequency (30 days)',
  description: 'Each area: tasks due, done, missed, and when it was last cleaned.',
  permission: 'MANAGE_HOUSEKEEPING',
  feature: 'cleaning',
  async build({ propertyId }) {
    const { frequencyReport } = await import('../cleaning/cleaning.service');
    const rows = await frequencyReport(30, propertyId);
    return [
      {
        name: 'Areas',
        columns: [{ header: 'Area', width: 26 }, { header: 'Kind', width: 12 }, { header: 'Floor' }, { header: 'Frequency', width: 14 }, { header: 'Due' }, { header: 'Done' }, { header: 'Missed' }, { header: '% done' }, { header: 'Last cleaned', width: 18 }],
        rows: rows.sort((a, b) => (a.rate ?? 101) - (b.rate ?? 101)).map((r) => [r.name, words(r.kind), r.floor, words(r.frequency), r.due, r.done, r.missed, r.rate, r.lastDone]),
      },
    ];
  },
});

registerReport({
  key: 'room-register',
  title: 'Room register and work orders',
  description: 'Every item in every room with its state, and the open work orders.',
  permission: 'MANAGE_ROOMS',
  feature: 'roomInventory',
  async build({ propertyId }) {
    const where = propertyId ? { propertyId } : {};
    const [assets, orders, rooms] = await Promise.all([
      db.roomAsset.findMany({ where: { ...where, status: { not: 'DISPOSED' } }, orderBy: [{ roomId: 'asc' }, { name: 'asc' }], take: 20000 }),
      db.workOrder.findMany({ where: { ...where, status: { in: ['OPEN', 'IN_PROGRESS'] } }, orderBy: { createdAt: 'asc' }, take: 5000 }),
      db.room.findMany({ where, select: { id: true, number: true, floor: true } }),
    ]);
    const room = new Map(rooms.map((r) => [r.id, r]));
    return [
      { name: 'Register', columns: [{ header: 'Room' }, { header: 'Floor' }, { header: 'Item', width: 24 }, { header: 'Tag', width: 14 }, { header: 'Qty' }, { header: 'State', width: 14 }, { header: 'Last audited', width: 18 }, { header: 'Note', width: 30 }], rows: assets.map((a) => [room.get(a.roomId)?.number ?? '', room.get(a.roomId)?.floor ?? '', a.name, a.tag ?? '', a.quantity, words(a.status), a.lastAuditedAt, a.note ?? '']) },
      { name: 'Open work orders', columns: [{ header: 'Raised', width: 18 }, { header: 'Room' }, { header: 'What', width: 40 }, { header: 'Priority' }, { header: 'Status', width: 12 }, { header: 'From', width: 10 }], rows: orders.map((o) => [o.createdAt, o.roomId ? room.get(o.roomId)?.number ?? '' : '', o.title, words(o.priority), words(o.status), words(o.source)]) },
    ];
  },
});

registerReport({
  key: 'store-stock',
  title: 'Store stock',
  description: 'Every store item, its stock, and what is below its reorder level.',
  permission: 'MANAGE_ROOMS',
  feature: 'roomInventory',
  async build({ propertyId }) {
    const items = await db.storeItem.findMany({ where: { isArchived: false, ...(propertyId ? { OR: [{ propertyId }, { propertyId: null }] } : {}) }, orderBy: [{ category: 'asc' }, { name: 'asc' }] });
    return [{ name: 'Stock', columns: [{ header: 'Item', width: 26 }, { header: 'Category', width: 14 }, { header: 'In stock' }, { header: 'Unit' }, { header: 'Reorder at' }, { header: 'Reorder now', width: 13 }], rows: items.map((i) => [i.name, words(i.category), i.quantity, i.unit, i.reorderLevel, i.quantity <= i.reorderLevel ? 'Yes' : '']) }];
  },
});

registerReport({
  key: 'laundry',
  title: 'Laundry (30 days)',
  description: 'Bags and clothes by employee: block, room, the day they were issued to the laundry and the day collected.',
  permission: 'MANAGE_HOUSEKEEPING',
  feature: 'laundry',
  ranged: true,
  defaultDays: 30,
  highlights(sheets) {
    const rows = sheets[0].rows;
    const sum = (i: number) => rows.reduce<number>((n, r) => n + (typeof r[i] === 'number' ? r[i] : 0), 0);
    return [
      { label: 'Employees', value: new Set(rows.map((r) => r[3] || r[2])).size },
      { label: 'Bags', value: sum(4) },
      { label: 'Clothes', value: sum(5) },
      { label: 'Bags not collected', value: rows.filter((r) => !r[7]).reduce<number>((n, r) => n + Number(r[4]), 0) },
    ];
  },
  async build({ propertyId, now, range }) {
    const from = dayStart(range?.from ?? istDay(new Date(now.getTime() - 29 * 86_400_000)));
    const to = new Date(dayStart(range?.to ?? istDay(now)).getTime() + 86_400_000);
    // Issued means the desk has taken the bag in: a request nobody brought down, or called off, is not in here.
    const orders = await db.laundryOrder.findMany({ where: { receivedAt: { gte: from, lt: to }, ...(propertyId ? { propertyId } : {}) }, orderBy: { receivedAt: 'asc' }, take: 20000, select: { propertyId: true, studentProfileId: true, items: true, receivedAt: true, deliveredAt: true } });
    const [people, blocks] = await Promise.all([
      db.studentProfile.findMany({ where: { id: { in: [...new Set(orders.map((o) => o.studentProfileId))] } }, select: { id: true, employeeCode: true, user: { select: { name: true } }, bed: { select: { room: { select: { number: true } } } } } }),
      db.property.findMany({ select: { id: true, name: true } }),
    ]);
    const who = new Map(people.map((p) => [p.id, p]));
    const block = new Map(blocks.map((b) => [b.id, b.name]));
    // Each order is one bag, so an employee's bags are those issued and collected on the same days.
    const groups = new Map<string, { o: (typeof orders)[number]; bags: number; items: number; issued: string; collected: string | null }>();
    for (const o of orders) {
      const issued = istDay(o.receivedAt!);
      const collected = o.deliveredAt ? istDay(o.deliveredAt) : null;
      const key = `${o.studentProfileId}|${issued}|${collected}`;
      const g = groups.get(key);
      if (g) {
        g.bags++;
        g.items += o.items;
      } else groups.set(key, { o, bags: 1, items: o.items, issued, collected });
    }
    const asDate = (ymd: string | null) => (ymd ? new Date(`${ymd}T00:00:00.000Z`) : null);
    const rows = [...groups.values()]
      .map((g) => {
        const p = who.get(g.o.studentProfileId);
        return [g.o.propertyId ? (block.get(g.o.propertyId) ?? '') : '', p?.bed?.room.number ?? '', p?.user.name ?? '', p?.employeeCode ?? '', g.bags, g.items, asDate(g.issued), asDate(g.collected)];
      })
      .sort((a, b) => Number(b[6]) - Number(a[6]) || String(a[0]).localeCompare(String(b[0])) || String(a[1]).localeCompare(String(b[1]), 'en', { numeric: true }));
    return [{ name: 'Laundry', columns: [{ header: 'Block name', width: 18 }, { header: 'Room number', width: 13 }, { header: 'Employee name', width: 24 }, { header: 'Employee ID', width: 14 }, { header: 'Bags', width: 8 }, { header: 'Clothes', width: 9 }, { header: 'Issue date', width: 13 }, { header: 'Collect date', width: 13 }], rows }];
  },
});

registerReport({
  key: 'staff-attendance',
  title: 'Staff attendance',
  description: 'Each member of staff by day: ID, plant, out time and in time. Emailed schedules cover the day before.',
  permission: 'MANAGE_STAFF',
  feature: 'staffAttendance',
  ranged: true,
  defaultDays: 1,
  highlights(sheets) {
    const rows = sheets[0].rows;
    const offset = sheets[0].columns.length - 6; // a Date column when more than one day is shown
    return [
      { label: 'Staff', value: new Set(rows.map((r) => r[1 + offset])).size },
      { label: 'Clocked in', value: rows.filter((r) => r[5 + offset]).length },
      { label: 'Not clocked in', value: rows.filter((r) => !r[5 + offset]).length },
      { label: 'Not clocked out', value: rows.filter((r) => r[5 + offset] && !r[4 + offset]).length },
    ];
  },
  async build({ now, range, scheduled }) {
    const { dayBoards, monthHours, istToday } = await import('../staff-attendance/staff-attendance.service');
    const today = istToday(now);
    // A morning email of a day that has barely begun says nothing: schedules send yesterday.
    const single = scheduled ? istToday(new Date(now.getTime() - 86_400_000)) : today;
    const from = range?.from ?? single;
    const to = range?.to ?? single;
    const many = from !== to;
    const rows: (string | number | Date | null)[][] = [];
    let sl = 1;
    const days: string[] = [];
    for (let t = Date.parse(`${from}T00:00:00.000Z`); t <= Date.parse(`${to}T00:00:00.000Z`); t += 86_400_000) {
      const day = new Date(t).toISOString().slice(0, 10);
      if (day > today) break;
      days.push(day);
    }
    // Every day's board from one read (Sprint L4), not a read per day.
    const boards = await dayBoards(days, now);
    for (const [i, board] of boards.entries()) {
      const t = Date.parse(`${days[i]}T00:00:00.000Z`);
      for (const r of board.rows) {
        // A login with no duty, no punch and no staff ID is not on the hostel's staff: the plant head, say.
        if (r.status === 'NO_DUTY' && !r.staffCode) continue;
        rows.push([...(many ? [new Date(t)] : []), sl++, r.name, r.staffCode ?? '', r.plantName ?? '', time(r.lastOut), time(r.firstIn)]);
      }
    }
    const month = (to > today ? today : to).slice(0, 7);
    const hours = await monthHours(month);
    return [
      { name: 'Attendance', columns: [...(many ? [{ header: 'Date', width: 13 }] : []), { header: 'Sl', width: 6 }, { header: 'Name', width: 24 }, { header: 'ID', width: 12 }, { header: 'Plant name', width: 20 }, { header: 'Out time', width: 10 }, { header: 'In time', width: 10 }], rows },
      { name: `Month ${month}`, columns: [{ header: 'Staff', width: 24 }, { header: 'Role', width: 20 }, { header: 'Days present', width: 13 }, { header: 'Hours', width: 10 }, { header: 'Late days', width: 11 }, { header: 'Absences', width: 11 }], rows: hours.map((r) => [r.name, r.roleName, r.daysPresent, r.hours, r.lateDays, r.absences]) },
    ];
  },
});

// ── Sprint 4 (TEPL) ─────────────────────────────────────────────────────────


registerReport({
  key: 'energy-consumption',
  title: 'Energy use (this month)',
  description: 'Units by block, floor and room this month, per occupied bed, with the last six months.',
  permission: 'MANAGE_PROPERTY',
  feature: 'energy',
  async build({ propertyId, now }) {
    const { kpis } = await import('../energy/energy-kpi.service');
    const k = await kpis(istDay(now).slice(0, 7), propertyId);
    return [
      { name: 'Blocks', columns: [{ header: 'Block', width: 18 }, { header: 'Units', width: 10 }, { header: 'Rooms', width: 10 }, { header: 'Common', width: 10 }, { header: 'Occupied beds', width: 14 }, { header: 'Occupancy %', width: 12 }, { header: 'Units per occupied bed', width: 20 }], rows: k.blocks.map((b) => [b.block, b.kwh, b.roomKwh, b.commonKwh, b.occupied, b.occupancy ?? '', b.perOccupiedBed ?? '']) },
      { name: 'Floors', columns: [{ header: 'Block', width: 18 }, { header: 'Floor' }, { header: 'Rooms' }, { header: 'Units', width: 10 }, { header: 'Occupied beds', width: 14 }, { header: 'Per occupied bed', width: 16 }], rows: k.floors.map((f) => [f.block ?? '', f.floor ?? '', f.rooms, f.kwh, f.occupied, f.perOccupiedBed ?? '']) },
      { name: 'Rooms', columns: [{ header: 'Block', width: 18 }, { header: 'Room' }, { header: 'Floor' }, { header: 'Meter', width: 14 }, { header: 'Units', width: 10 }, { header: 'Beds' }, { header: 'Occupied' }, { header: 'Per occupied bed', width: 16 }], rows: k.rooms.map((r) => [r.block ?? '', r.number, r.floor ?? '', r.meter, r.kwh, r.beds, r.occupied, r.perOccupiedBed ?? '']) },
      { name: 'Months', columns: [{ header: 'Month' }, { header: 'Units', width: 10 }, { header: 'Occupied beds', width: 14 }, { header: 'Per occupied bed', width: 16 }], rows: k.months.map((m) => [m.month, m.kwh, m.occupied, m.perOccupiedBed ?? '']) },
    ];
  },
});

registerReport({
  key: 'bus-trips',
  title: 'Bus trips (30 days)',
  description: 'Every trip the gate logged: out, back, passengers and late returns, with a summary per vehicle.',
  permission: 'MANAGE_GATE',
  feature: 'busLog',
  ranged: true,
  defaultDays: 30,
  async build({ propertyId, now, range }) {
    const { tripReport } = await import('../transport/transport.service');
    const r = await tripReport(range?.from ?? istDay(new Date(now.getTime() - 29 * 86_400_000)), range?.to ?? istDay(now), propertyId);
    return [
      { name: 'Vehicles', columns: [{ header: 'Vehicle', width: 16 }, { header: 'Trips' }, { header: 'Passengers out', width: 15 }, { header: 'Passengers in', width: 14 }, { header: 'Average minutes out', width: 19 }, { header: 'Late returns', width: 13 }], rows: r.vehicles.map((v) => [v.number, v.trips, v.passengersOut, v.passengersIn, v.avgMinutesOut ?? '', v.late]) },
      { name: 'Trips', columns: [{ header: 'Vehicle', width: 16 }, { header: 'Route', width: 22 }, { header: 'Left', width: 18 }, { header: 'Out' }, { header: 'Due back', width: 18 }, { header: 'Back', width: 18 }, { header: 'In' }, { header: 'Driver', width: 18 }, { header: 'Note', width: 30 }], rows: r.trips.map((t) => [t.vehicle.number, t.route ?? '', t.departedAt, t.passengersOut ?? '', t.expectedReturnAt, t.arrivedAt, t.passengersIn ?? '', t.driverName ?? '', t.note ?? '']) },
    ];
  },
});

registerReport({
  key: 'visitors',
  title: 'Visitors (30 days)',
  description: 'Visitors per day, by purpose, and the employees with the most visitors.',
  permission: 'MANAGE_GATE',
  feature: 'visitorReports',
  ranged: true,
  defaultDays: 30,
  async build({ propertyId, now, range }) {
    const { visitorReport } = await import('../gate/visitors.service');
    const r = await visitorReport(propertyId, range?.from ?? istDay(new Date(now.getTime() - 29 * 86_400_000)), range?.to ?? istDay(now));
    return [
      { name: 'Days', columns: [{ header: 'Day', width: 12 }, { header: 'Visitors' }, { header: 'Left' }, { header: 'Still in' }, { header: 'Pre-registered', width: 14 }, { header: 'No-shows' }], rows: r.days.map((d) => [d.day, d.visitors, d.left, d.stillIn, d.preRegistered, d.noShow]) },
      { name: 'Purposes', columns: [{ header: 'Purpose', width: 26 }, { header: 'Visitors' }], rows: r.byPurpose.map((p) => [p.purpose, p.count]) },
      { name: 'Most visited', columns: [{ header: 'Employee', width: 24 }, { header: 'Employee ID', width: 14 }, { header: 'Room', width: 20 }, { header: 'Visits' }], rows: r.mostVisited.map((m) => [m.name, m.employeeCode ?? '', m.room ?? '', m.visits]) },
    ];
  },
});

registerReport({
  key: 'outings',
  title: 'Outings (this week)',
  description: 'Who went out and when: out and back times, how long, and how many people each day.',
  permission: 'MANAGE_STUDENTS',
  alsoAllowed: ['MANAGE_GATE'],
  feature: 'quickOutings',
  ranged: true,
  defaultDays: 'week',
  highlights(sheets) {
    const rows = sheets[0].rows;
    const minutes = rows.filter((r) => r[9]).map((r) => (r[9] as Date).getTime() - (r[7] as Date).getTime());
    return [
      { label: 'People who went out', value: new Set(rows.map((r) => r[13])).size },
      { label: 'Outings', value: rows.length },
      { label: 'Average time out', value: minutes.length ? durationText(minutes.reduce((a, b) => a + b, 0) / minutes.length) : '—' },
      { label: 'Back late', value: rows.filter((r) => r[11] && r[9]).length },
      { label: 'Still out', value: rows.filter((r) => !r[9]).length },
    ];
  },
  async build({ propertyId, now, range }) {
    const { outingsBetween } = await import('../outings/outings.service');
    const to = range?.to ?? istDay(now);
    const from = range?.from ?? mondayOf(to);
    const rows = await outingsBetween(from, to, propertyId);
    const end = (r: (typeof rows)[number]) => r.inAt ?? now;
    const lateBy = (r: (typeof rows)[number]) => Math.max(0, end(r).getTime() - r.validTo.getTime());
    const away = (r: (typeof rows)[number]) => end(r).getTime() - r.outAt.getTime();
    const avg = (xs: number[]) => (xs.length ? durationText(xs.reduce((a, b) => a + b, 0) / xs.length) : '');
    const days = new Map<string, typeof rows>();
    for (const r of rows) days.set(istDay(r.outAt), [...(days.get(istDay(r.outAt)) ?? []), r]);
    const people = new Map<string, typeof rows>();
    for (const r of rows) people.set(r.studentProfileId, [...(people.get(r.studentProfileId) ?? []), r]);
    return [
      {
        name: 'Outings',
        columns: [{ header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Block', width: 16 }, { header: 'Room', width: 10 }, { header: 'Purpose', width: 16 }, { header: 'Where to', width: 20 }, { header: 'Approved by', width: 20 }, { header: 'Out at', width: 18 }, { header: 'Back by', width: 18 }, { header: 'Back at', width: 18 }, { header: 'Time out', width: 13 }, { header: 'Late by', width: 12 }, { header: 'Status', width: 10 }, { header: 'Person', hidden: true }],
        rows: rows.map((r) => [r.employeeCode ?? '', r.name, r.block ?? '', r.room ?? '', r.purpose ?? '', r.destination ?? '', r.approvedBy ?? '', r.outAt, r.validTo, r.inAt, durationText(away(r)), lateBy(r) > 0 ? durationText(lateBy(r)) : '', r.inAt ? 'Back' : lateBy(r) > 0 ? 'Overdue' : 'Out', r.studentProfileId]),
      },
      {
        name: 'By day',
        columns: [{ header: 'Day', width: 13 }, { header: 'People', width: 9 }, { header: 'Outings', width: 9 }, { header: 'Average time out', width: 16 }, { header: 'Back late', width: 10 }],
        rows: [...days.entries()].sort((a, b) => a[0].localeCompare(b[0])).map(([day, list]) => [new Date(`${day}T00:00:00.000Z`), new Set(list.map((r) => r.studentProfileId)).size, list.length, avg(list.map(away)), list.filter((r) => lateBy(r) > 0).length]),
      },
      {
        name: 'By employee',
        columns: [{ header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Block', width: 16 }, { header: 'Outings', width: 9 }, { header: 'Total time out', width: 15 }, { header: 'Longest', width: 13 }, { header: 'Back late', width: 10 }],
        rows: [...people.values()].sort((a, b) => b.length - a.length || a[0].name.localeCompare(b[0].name)).map((list) => [list[0].employeeCode ?? '', list[0].name, list[0].block ?? '', list.length, durationText(list.reduce((n, r) => n + away(r), 0)), durationText(Math.max(...list.map(away))), list.filter((r) => lateBy(r) > 0).length]),
      },
    ];
  },
});

registerReport({
  key: 'curfew-breaches',
  title: 'Out after curfew (30 days)',
  description: 'Each night, who was out after curfew with no pass, leave or shift, and when they came back.',
  permission: 'MANAGE_STUDENTS',
  feature: 'quickOutings',
  ranged: true,
  defaultDays: 30,
  async build({ propertyId, now, range }) {
    const since = new Date(`${range?.from ?? istDay(new Date(now.getTime() - 29 * 86_400_000))}T00:00:00.000Z`);
    const until = new Date(`${range?.to ?? istDay(now)}T00:00:00.000Z`);
    const rows = await db.curfewBreach.findMany({ where: { date: { gte: since, lte: until }, ...(propertyId ? { propertyId } : {}) }, orderBy: [{ date: 'desc' }, { outSince: 'asc' }], take: 10000 });
    const people = new Map((await db.studentProfile.findMany({ where: { id: { in: [...new Set(rows.map((r) => r.studentProfileId))] } }, select: { id: true, employeeCode: true, user: { select: { name: true } } } })).map((p) => [p.id, p]));
    return [{ name: 'Nights', columns: [{ header: 'Night', width: 12 }, { header: 'Employee ID', width: 14 }, { header: 'Employee', width: 24 }, { header: 'Out since', width: 18 }, { header: 'Back', width: 18 }], rows: rows.map((r) => [r.date, people.get(r.studentProfileId)?.employeeCode ?? '', people.get(r.studentProfileId)?.user.name ?? '', r.outSince, r.backAt]) }];
  },
});

// ── Sprint 5 (TEPL) ─────────────────────────────────────────────────────────

registerReport({
  key: 'events',
  title: 'Events (90 days)',
  description: 'Each event: when, where, registered, present, and its rating.',
  permission: 'MANAGE_EVENTS',
  feature: 'events',
  ranged: true,
  defaultDays: 90,
  async build({ propertyId, now, range }) {
    const { listEvents } = await import('../events/events.service');
    const from = new Date(`${range?.from ?? istDay(new Date(now.getTime() - 89 * 86_400_000))}T00:00:00.000Z`);
    const to = new Date(new Date(`${range?.to ?? istDay(now)}T00:00:00.000Z`).getTime() + 86_400_000);
    const rows = await listEvents({ from: new Date(from.getTime() - 330 * 60_000), to: new Date(to.getTime() - 330 * 60_000), propertyId, includeCancelled: true });
    return [{ name: 'Events', columns: [{ header: 'Starts', width: 18 }, { header: 'Event', width: 30 }, { header: 'Category', width: 12 }, { header: 'Venue', width: 20 }, { header: 'Status', width: 11 }, { header: 'Registered', width: 11 }, { header: 'Present', width: 9 }, { header: 'Ratings', width: 9 }, { header: 'Average', width: 9 }], rows: rows.map((e) => [e.startsAt, e.title, words(e.category), e.venue ?? '', words(e.status), e.registered, e.attended, e.ratings, e.avgRating ?? '']) }];
  },
});

registerReport({
  key: 'emergency-log',
  title: 'Emergencies and drills',
  description: 'Every alert and drill: when, what, how long it stayed open, and how many were accounted for.',
  permission: 'MANAGE_EMERGENCY',
  feature: 'emergency',
  async build({ propertyId }) {
    const { listAlerts } = await import('../emergency/emergency.service');
    const rows = await listAlerts({ propertyId });
    return [{ name: 'Alerts', columns: [{ header: 'Raised', width: 18 }, { header: 'Drill' }, { header: 'Kind', width: 12 }, { header: 'Title', width: 30 }, { header: 'Assembly point', width: 20 }, { header: 'Minutes open', width: 13 }, { header: 'Safe' }, { header: 'Needed help', width: 12 }, { header: 'Observations', width: 40 }], rows: rows.map((a) => [a.raisedAt, a.isDrill ? 'Yes' : '', words(a.kind), a.title, a.assemblyPoint ?? '', a.minutesOpen ?? 'Open', a.safe, a.needHelp, a.observations ?? '']) }];
  },
});

registerReport({
  key: 'canteen-consumption',
  title: 'Canteen meals (7 days)',
  description: 'Per day and meal: how many ate against how many were expected, and by diet.',
  permission: 'MANAGE_MESS',
  feature: 'canteen',
  ranged: true,
  defaultDays: 7,
  async build({ propertyId, now, range }) {
    const { consumption, MEALS } = await import('../canteen/canteen.service');
    const to = range?.to ?? istDay(now);
    const from = range?.from ?? istDay(new Date(Date.parse(`${to}T00:00:00.000Z`) - 6 * 86_400_000));
    const c = await consumption(from, to, propertyId);
    return [
      { name: 'By meal', columns: [{ header: 'Day', width: 12 }, ...MEALS.flatMap((m) => [{ header: `${words(m)} ate`, width: 13 }, { header: `${words(m)} expected`, width: 17 }])], rows: c.days.map((d) => [d.day, ...d.meals.flatMap((m) => [m.served, m.expected])]) },
      { name: 'Totals', columns: [{ header: 'Meal', width: 12 }, { header: 'Ate' }, { header: 'Expected', width: 10 }, { header: 'Turn-out %', width: 11 }], rows: c.totals.map((t) => [words(t.meal), t.served, t.expected, t.rate ?? '']) },
    ];
  },
});

registerReport({
  key: 'meal-ratings',
  title: 'Meal ratings (30 days)',
  description: 'Average rating per meal, day by day, and the comments.',
  permission: 'MANAGE_MESS',
  feature: 'canteen',
  ranged: true,
  defaultDays: 30,
  async build({ propertyId, range }) {
    const { ratingSummary } = await import('../canteen/canteen.service');
    const days = range ? Math.round((Date.parse(`${range.to}T00:00:00.000Z`) - Date.parse(`${range.from}T00:00:00.000Z`)) / 86_400_000) + 1 : 30;
    const r = await ratingSummary(days, propertyId, range?.to);
    return [
      { name: 'Meals', columns: [{ header: 'Meal', width: 12 }, { header: 'Ratings' }, { header: 'Average' }, { header: 'Rated 1–2', width: 11 }], rows: r.byMeal.map((m) => [words(m.meal), m.ratings, m.avg ?? '', m.low]) },
      { name: 'Days', columns: [{ header: 'Day', width: 12 }, { header: 'Ratings' }, { header: 'Average' }], rows: r.trend.map((d) => [d.day, d.ratings, d.avg ?? '']) },
      { name: 'Comments', columns: [{ header: 'Day', width: 12 }, { header: 'Meal', width: 12 }, { header: 'Rating' }, { header: 'Comment', width: 60 }], rows: r.comments.map((c) => [c.date, words(c.meal), c.rating, c.comment ?? '']) },
    ];
  },
});

// ── Sprint 6 (TEPL) ─────────────────────────────────────────────────────────

registerReport({
  key: 'leadership',
  title: 'Leadership summary',
  description: 'Each block and both together: occupancy, leave, shift adherence, satisfaction, grievances, energy, canteen and safety.',
  permission: 'VIEW_LEADERSHIP',
  feature: 'leadership',
  async build() {
    const { overview } = await import('../leadership/leadership.service');
    const o = await overview(6);
    const all = [...o.blocks, { name: 'Both blocks', ...o.combined }];
    const last = <T,>(xs: T[] | null | undefined) => (xs && xs.length ? xs[xs.length - 1] : undefined);
    return [
      {
        name: 'Now',
        columns: [{ header: 'Block', width: 16 }, { header: 'Beds' }, { header: 'Occupied', width: 10 }, { header: 'Occupancy %', width: 12 }, { header: 'Leave waiting', width: 13 }, { header: 'On time today %', width: 15 }, { header: 'Grievances open', width: 15 }, { header: 'Within target %', width: 15 }, { header: 'Satisfaction', width: 12 }, { header: 'Units per bed', width: 13 }, { header: 'Curfew (30 d)', width: 13 }, { header: 'Incidents (30 d)', width: 15 }],
        rows: all.map((b) => [b.name, b.beds, b.occupiedNow, b.occupancyNow ?? '', b.leave?.pending ?? '', last(b.attendance?.week)?.rate ?? '', b.grievances?.open ?? '', b.grievances?.withinTarget ?? '', last(b.satisfaction)?.overall ?? '', last(b.energy?.months)?.perOccupiedBed ?? '', b.safety.curfewBreaches30 ?? '', b.safety.incidents30 ?? '']),
      },
      {
        name: 'Months',
        columns: [{ header: 'Block', width: 16 }, { header: 'Month' }, { header: 'Occupancy %', width: 12 }, { header: 'Leave days', width: 11 }, { header: 'Satisfaction', width: 12 }, { header: 'Units per bed', width: 13 }],
        rows: all.flatMap((b) => o.months.map((m, i) => [b.name, m, b.occupancy[i]?.rate ?? '', b.leave?.months[i]?.days ?? '', b.satisfaction?.find((x) => x.month === m)?.overall ?? '', b.energy?.months.find((x) => x.month === m)?.perOccupiedBed ?? ''])),
      },
    ];
  },
});

registerReport({
  key: 'medical-access-log',
  title: 'Medical records access (90 days)',
  description: 'Who opened or changed which employee’s medical record, and when. No medical detail.',
  permission: 'MANAGE_MEDICAL',
  feature: 'medical',
  async build() {
    const { accessLog } = await import('../medical/medical.service');
    const rows = await accessLog({ days: 90 });
    return [{ name: 'Access', columns: [{ header: 'When', width: 18 }, { header: 'By', width: 22 }, { header: 'Employee', width: 22 }, { header: 'What', width: 12 }], rows: rows.map((r) => [r.at, r.by, r.employee, words(r.action)]) }];
  },
});

registerReport({
  key: 'special-needs',
  title: 'Employees with special needs',
  description: 'Each employee’s needs, their room, and whether it suits them.',
  permission: 'MANAGE_STUDENTS',
  feature: 'specialNeeds',
  async build({ propertyId }) {
    const { listProfiles } = await import('../special-needs/special-needs.service');
    const rows = await listProfiles(propertyId);
    return [{ name: 'Employees', columns: [{ header: 'Employee', width: 22 }, { header: 'Employee ID', width: 14 }, { header: 'Needs', width: 24 }, { header: 'Room', width: 20 }, { header: 'Accessible room', width: 15 }, { header: 'Evacuation help', width: 15 }, { header: 'Suits them', width: 11 }, { header: 'Why not', width: 40 }], rows: rows.map((r) => [r.name, r.employeeCode ?? '', r.categories.map(words).join(', '), r.room ?? '', r.needsAccessibleRoom ? 'Yes' : '', r.evacuationHelp ? 'Yes' : '', r.fits ? 'Yes' : 'No', r.reasons.join('; ')]) }];
  },
});

// ── Sprint 7 (TEPL) ─────────────────────────────────────────────────────────

const COMPLIANCE_STATE: Record<string, string> = { VALID: 'In date', DUE_SOON: 'Renew soon', EXPIRED: 'Expired', REVIEW_DUE: 'Review due', NO_DATE: 'No dates yet', ARCHIVED: 'Archived' };

registerReport({
  key: 'compliance-register',
  title: 'Compliance register',
  description: 'Every licence, permit, certificate, insurance and register by block: number, authority, dates, days left, owner and state; with open actions.',
  permission: 'VIEW_COMPLIANCE',
  alsoAllowed: ['MANAGE_COMPLIANCE'],
  feature: 'compliance',
  async build({ propertyId }) {
    const { listItems, listFindings } = await import('../compliance/compliance.service');
    const [items, findings] = await Promise.all([listItems({ propertyId }), listFindings({ propertyId, status: 'OPEN' })]);
    return [
      {
        name: 'Register',
        columns: [{ header: 'Block', width: 14 }, { header: 'Kind', width: 12 }, { header: 'Name', width: 40 }, { header: 'Authority', width: 32 }, { header: 'Number', width: 18 }, { header: 'Issued', width: 12 }, { header: 'Expires', width: 12 }, { header: 'Days left', width: 10 }, { header: 'Renewal in hand', width: 15 }, { header: 'Next review', width: 12 }, { header: 'Owner', width: 18 }, { header: 'Documents', width: 10 }, { header: 'State', width: 13 }],
        rows: items.map((i) => [i.block ?? 'Whole site', words(i.kind), i.name, i.authority ?? '', i.referenceNo ?? '', i.issuedOn ?? '', i.expiresOn ?? '', i.daysLeft ?? '', i.renewalStartedAt ? 'Yes' : '', i.reviewDueOn ?? '', i.owner ?? '', i.files, COMPLIANCE_STATE[i.state] ?? i.state]),
      },
      {
        name: 'Open actions',
        columns: [{ header: 'Block', width: 14 }, { header: 'Inspection', width: 30 }, { header: 'Inspected', width: 12 }, { header: 'Finding', width: 60 }, { header: 'Severity', width: 10 }, { header: 'Owner', width: 18 }, { header: 'Due', width: 12 }, { header: 'Overdue', width: 9 }],
        rows: findings.map((f) => [f.inspection?.block ?? 'Whole site', f.inspection?.inspector ?? '', f.inspection?.inspectedOn ?? '', f.description, words(f.severity), f.owner ?? '', f.dueOn ?? '', f.overdue ? 'Yes' : '']),
      },
    ];
  },
});

registerReport({
  key: 'compliance-inspections',
  title: 'Inspections and actions',
  description: 'Inspections and audits in the period, with every finding, its owner, due date and how it was closed.',
  permission: 'VIEW_COMPLIANCE',
  alsoAllowed: ['MANAGE_COMPLIANCE'],
  feature: 'compliance',
  ranged: true,
  defaultDays: 90,
  async build({ propertyId, range }) {
    const { listInspections, getInspection } = await import('../compliance/compliance.service');
    // The last year unless a range was asked for.
    const from = range ? new Date(`${range.from}T00:00:00.000Z`) : new Date(Date.now() - 365 * 86_400_000);
    const inspections = await listInspections({ propertyId, from, to: range ? new Date(`${range.to}T00:00:00.000Z`) : undefined });
    const details = await Promise.all(inspections.map((i) => getInspection(i.id)));
    return [
      {
        name: 'Inspections',
        columns: [{ header: 'Date', width: 12 }, { header: 'Block', width: 14 }, { header: 'Kind', width: 14 }, { header: 'Inspector', width: 30 }, { header: 'For', width: 30 }, { header: 'Result', width: 13 }, { header: 'Open', width: 8 }, { header: 'Closed', width: 8 }, { header: 'Summary', width: 60 }],
        rows: inspections.map((i) => [i.inspectedOn ?? '', i.block ?? 'Whole site', words(i.kind), i.inspector, i.item ?? '', words(i.result), i.open, i.closed, i.summary ?? '']),
      },
      {
        name: 'Findings',
        columns: [{ header: 'Inspected', width: 12 }, { header: 'Inspector', width: 30 }, { header: 'Finding', width: 60 }, { header: 'Severity', width: 10 }, { header: 'Owner', width: 18 }, { header: 'Due', width: 12 }, { header: 'Status', width: 9 }, { header: 'Closed by', width: 18 }, { header: 'What was done', width: 50 }],
        rows: details.flatMap((d) => d.findings.map((f) => [d.inspection.inspectedOn ?? '', d.inspection.inspector, f.description, words(f.severity), f.owner ?? '', f.dueOn ?? '', f.status === 'OPEN' ? (f.overdue ? 'Overdue' : 'Open') : 'Closed', f.closedBy ?? '', f.closureNote ?? ''])),
      },
    ];
  },
});
