import { createHash, randomBytes } from 'node:crypto';
import { PassStatus, Prisma, PunchDirection, PunchType, Role, StudentRequestStatus, StudentRequestType } from '@prisma/client';
import { paginated, type Pagination } from '../../lib/pagination';
import { db, prismaUnscoped } from '../../lib/prisma';
import { getTenantIdOrThrow } from '../../lib/tenantContext';
import { tenantHasFeature } from '../../middleware/requireFeature';
import { inBackground, notifyPassAwaitingGuardian, notifyPassOverdue } from '../notifications/notifications.service';
import { BadRequestError, ConflictError, NotFoundError } from '../../lib/errors';
import { presignDownload } from '../uploads/uploads.service';
import { sendEmail } from '../../lib/email';
import { lateReturnEmail } from '../../lib/email-templates';
import { brandForTenant } from '../../lib/email-brand';

/**
 * Gate passes and the gate itself (Sprint 5).
 *
 * A pass is issued from an approved LEAVE request. Its QR carries a random
 * token — never a name, never an id a person could read — and only the
 * token's hash is stored. A guard scans it, sees who it belongs to, and taps
 * Out or In; each tap is a GatePunch. An hourly tick flags passes whose
 * return time has passed with no In, and tells the warden and the guardian.
 */

const DAY_MS = 86_400_000;
export const passTokenHash = (token: string) => createHash('sha256').update(token).digest('hex');
const hash = (token: string) => createHash('sha256').update(token).digest('hex');

export const passInclude = {
  student: { include: { user: { select: { id: true, name: true, email: true, phone: true } }, bed: { include: { room: { select: { number: true, floor: true } } } } } },
  request: { select: { id: true, subject: true, body: true, status: true, fromDate: true, toDate: true } },
} as const;

export function publicPass(p: { id: string; studentProfileId: string; validFrom: Date; validTo: Date; destination: string | null; status: PassStatus; guardianRequired: boolean; guardianApprovedAt: Date | null; guardianDeclinedAt: Date | null; guardianNote: string | null; outAt: Date | null; inAt: Date | null; lateFlaggedAt: Date | null; createdAt: Date; requestId: string | null; kind?: string; purpose?: string | null; approvedById: string; decidedAt?: Date | null; decisionNote?: string | null; student?: { employeeCode?: string | null; user: { name: string; email: string; phone?: string | null }; bed: { label: string; room: { number: string } } | null } }, now = new Date()) {
  // A pass never used, or an outing nobody answered in time, has run out.
  const expired = (p.status === PassStatus.ACTIVE || p.status === PassStatus.PENDING_WARDEN) && p.validTo.getTime() < now.getTime();
  return {
    id: p.id,
    requestId: p.requestId,
    /** LEAVE, or OUTING for a quick outing (Sprint 4, TEPL). */
    kind: p.kind ?? 'LEAVE',
    purpose: p.purpose ?? null,
    /** The warden's answer to an outing request. */
    decidedAt: p.decidedAt ?? null,
    decisionNote: p.decisionNote ?? null,
    studentProfileId: p.studentProfileId,
    student: p.student ? { name: p.student.user.name, email: p.student.user.email, phone: p.student.user.phone ?? null, employeeCode: p.student.employeeCode ?? null, room: p.student.bed ? `Room ${p.student.bed.room.number} · Bed ${p.student.bed.label}` : null } : undefined,
    validFrom: p.validFrom,
    validTo: p.validTo,
    destination: p.destination,
    status: expired ? PassStatus.EXPIRED : p.status,
    guardianRequired: p.guardianRequired,
    guardianApprovedAt: p.guardianApprovedAt,
    guardianDeclinedAt: p.guardianDeclinedAt,
    guardianNote: p.guardianNote,
    outAt: p.outAt,
    inAt: p.inAt,
    lateFlaggedAt: p.lateFlaggedAt,
    /** What the guard's next tap should be. */
    nextAction: p.status === PassStatus.ACTIVE && !expired ? 'OUT' : p.status === PassStatus.OUT || p.status === PassStatus.OVERDUE ? 'IN' : 'NONE',
    /** Colour band for the card: green valid, amber due back soon, red overdue. */
    band: p.status === PassStatus.OVERDUE ? 'red' : p.status === PassStatus.OUT && p.validTo.getTime() - now.getTime() < 3 * 3_600_000 ? 'amber' : p.status === PassStatus.ACTIVE || p.status === PassStatus.OUT ? 'green' : 'grey',
    createdAt: p.createdAt,
  };
}

/** The QR payload: a versioned, opaque string. */
export const qrFor = (passId: string, token: string) => `HCPASS1.${passId}.${token}`;

/**
 * Issue a pass for an approved LEAVE request. Validity defaults to the
 * request's dates (from 06:00 on the first day to 22:00 on the last, local
 * to UTC as the rest of the product). With guardian approval switched on
 * and a guardian linked, the pass waits for them.
 */
export async function issuePass(requestId: string, input: { destination?: string; validFrom?: Date; validTo?: Date }, approvedById: string) {
  const tenantId = getTenantIdOrThrow();
  const request = await db.studentRequest.findFirst({ where: { id: requestId }, include: { student: { select: { id: true, propertyId: true, property: { select: { guardianApprovesPasses: true } }, guardians: { select: { id: true } } } }, gatePass: true } });
  if (!request) throw new NotFoundError('Request not found');
  if (request.type !== StudentRequestType.LEAVE) throw new BadRequestError('Only a leave request can carry a gate pass');
  if (request.status !== StudentRequestStatus.APPROVED) throw new ConflictError('Approve the leave first');
  if (request.gatePass) return { pass: publicPass({ ...request.gatePass, student: undefined }), qr: null };
  const validFrom = input.validFrom ?? (request.fromDate ? new Date(request.fromDate.getTime() + 6 * 3_600_000) : new Date());
  const validTo = input.validTo ?? (request.toDate ? new Date(request.toDate.getTime() + 22 * 3_600_000) : new Date(validFrom.getTime() + DAY_MS));
  if (validTo <= validFrom) throw new BadRequestError('The pass must end after it starts');
  const guardianRequired = !!request.student.property?.guardianApprovesPasses && request.student.guardians.length > 0;
  const token = randomBytes(24).toString('hex');
  const pass = await db.gatePass.create({
    data: { tenantId, requestId, studentProfileId: request.student.id, validFrom, validTo, destination: input.destination?.slice(0, 120) ?? null, approvedById, guardianRequired, tokenHash: hash(token), status: guardianRequired ? PassStatus.PENDING_GUARDIAN : PassStatus.ACTIVE },
    include: passInclude,
  });
  if (guardianRequired) inBackground('push: pass awaiting guardian', () => notifyPassAwaitingGuardian(tenantId, pass.id));
  return { pass: publicPass(pass), qr: qrFor(pass.id, token) };
}

/** A fresh QR for an existing pass: the token rotates, the old QR dies. */
export async function rotateQr(passId: string, opts: { studentUserId?: string } = {}) {
  const pass = await db.gatePass.findFirst({ where: { id: passId }, include: passInclude });
  if (!pass || (opts.studentUserId && pass.student.user.id !== opts.studentUserId)) throw new NotFoundError('Pass not found');
  const open: PassStatus[] = [PassStatus.ACTIVE, PassStatus.OUT, PassStatus.OVERDUE, PassStatus.PENDING_GUARDIAN];
  if (!open.includes(pass.status)) throw new ConflictError('This pass is closed');
  const token = randomBytes(24).toString('hex');
  const updated = await db.gatePass.update({ where: { id: passId }, data: { tokenHash: hash(token) }, include: passInclude });
  return { pass: publicPass(updated), qr: qrFor(updated.id, token) };
}

export async function myPasses(userId: string) {
  const profile = await db.studentProfile.findFirst({ where: { userId }, select: { id: true } });
  if (!profile) throw new NotFoundError('No student profile for this account');
  const rows = await db.gatePass.findMany({ where: { studentProfileId: profile.id }, include: passInclude, orderBy: { validFrom: 'desc' }, take: 20 });
  return rows.map((p) => publicPass(p));
}

export async function listPasses(propertyId?: string, opts: { status?: PassStatus; limit?: number } = {}) {
  const rows = await db.gatePass.findMany({
    where: { ...(propertyId ? { student: { propertyId } } : {}), ...(opts.status ? { status: opts.status } : {}) },
    include: passInclude,
    orderBy: { validFrom: 'desc' },
    take: opts.limit ?? 100,
  });
  return rows.map((p) => publicPass(p));
}

export interface PassFilters {
  status?: PassStatus;
  /** India days, YYYY-MM-DD: passes whose validity overlaps from..to. */
  from?: string;
  to?: string;
  q?: string;
}

/**
 * The status a filter means is the one the list shows: EXPIRED and ACTIVE are
 * told apart by the clock (`publicPass`), not by the stored value, so asking
 * for either has to ask about the return time too.
 */
function passStatusWhere(status: PassStatus | undefined, now: Date): Prisma.GatePassWhereInput {
  if (!status) return {};
  const lapsable: PassStatus[] = [PassStatus.ACTIVE, PassStatus.PENDING_WARDEN];
  if (status === PassStatus.EXPIRED) return { OR: [{ status: PassStatus.EXPIRED }, { status: { in: lapsable }, validTo: { lt: now } }] };
  if (lapsable.includes(status)) return { status, validTo: { gte: now } };
  return { status };
}

/**
 * Gate passes a page at a time (Sprint L2), newest first, with the status,
 * day range and name search the desk filters by. The unpaged `listPasses`
 * stays as it was for the builds already on phones.
 */
export async function pagePasses(propertyId: string | undefined, filters: PassFilters, pagination: Pagination) {
  const now = new Date();
  const start = filters.from ? new Date(`${filters.from}T00:00:00+05:30`) : undefined;
  const end = filters.to ? new Date(new Date(`${filters.to}T00:00:00+05:30`).getTime() + DAY_MS) : undefined;
  const where: Prisma.GatePassWhereInput = {
    AND: [
      propertyId ? { student: { propertyId } } : {},
      passStatusWhere(filters.status, now),
      start ? { validTo: { gte: start } } : {},
      end ? { validFrom: { lt: end } } : {},
      filters.q ? { student: { user: { name: { contains: filters.q, mode: 'insensitive' } } } } : {},
    ],
  };
  const [rows, total] = await Promise.all([
    db.gatePass.findMany({ where, include: passInclude, orderBy: [{ validFrom: 'desc' }, { id: 'desc' }], skip: pagination.skip, take: pagination.limit }),
    db.gatePass.count({ where }),
  ]);
  return paginated(rows.map((p) => publicPass(p, now)), total, pagination);
}

export async function updatePass(passId: string, input: { validFrom?: Date; validTo?: Date; destination?: string | null }) {
  const pass = await db.gatePass.findFirst({ where: { id: passId } });
  if (!pass) throw new NotFoundError('Pass not found');
  if (pass.status === PassStatus.RETURNED || pass.status === PassStatus.CANCELLED) throw new ConflictError('This pass is closed');
  const validFrom = input.validFrom ?? pass.validFrom;
  const validTo = input.validTo ?? pass.validTo;
  if (validTo <= validFrom) throw new BadRequestError('The pass must end after it starts');
  return publicPass(await db.gatePass.update({ where: { id: passId }, data: { validFrom, validTo, ...(input.destination !== undefined ? { destination: input.destination } : {}) }, include: passInclude }));
}

export async function cancelPass(passId: string) {
  const pass = await db.gatePass.findFirst({ where: { id: passId } });
  if (!pass) throw new NotFoundError('Pass not found');
  if (pass.status === PassStatus.RETURNED || pass.status === PassStatus.CANCELLED) throw new ConflictError('This pass is closed');
  return publicPass(await db.gatePass.update({ where: { id: passId }, data: { status: PassStatus.CANCELLED }, include: passInclude }));
}

// ── The guard's screen ──────────────────────────────────────────────────────

/** Resolve a scanned QR to the person behind it, and what to do next. */
export async function scan(qr: string) {
  const m = /^HCPASS1\.([0-9a-f-]{36})\.([0-9a-f]{48})$/.exec(qr.trim());
  if (!m) throw new BadRequestError('That is not a HostelCare pass', 'BAD_QR');
  const pass = await db.gatePass.findFirst({ where: { id: m[1], tokenHash: hash(m[2]) }, include: passInclude });
  if (!pass) throw new NotFoundError('No such pass — it may have been reissued', 'PASS_NOT_FOUND');
  const student = pass.student;
  const photoUrl = student.photoUrl ? await presignDownload(getTenantIdOrThrow(), student.photoUrl).catch(() => null) : null;
  const pub = publicPass(pass);
  const reason = pass.status === PassStatus.PENDING_GUARDIAN ? 'Waiting for the guardian to approve' : pass.status === PassStatus.PENDING_WARDEN ? 'Waiting for the warden to approve' : pass.status === PassStatus.DECLINED ? (pass.kind === 'OUTING' ? 'Declined by the warden' : 'Declined by the guardian') : pass.status === PassStatus.CANCELLED ? 'Cancelled by the hostel' : pub.status === PassStatus.EXPIRED ? 'This pass has expired' : pass.status === PassStatus.RETURNED ? 'Already used — the student is back' : pass.validFrom.getTime() > Date.now() ? `Not valid until ${(await tenantHasFeature(getTenantIdOrThrow(), 'quickOutings')) ? pass.validFrom.toLocaleString('en-IN', { day: '2-digit', month: 'short', hour: 'numeric', minute: '2-digit', timeZone: 'Asia/Kolkata' }) : pass.validFrom.toISOString()}` : null;
  return {
    pass: pub,
    student: { id: student.id, name: student.user.name, phone: student.user.phone, room: pub.student?.room ?? null, photoUrl },
    allowed: pub.nextAction !== 'NONE' && !(pass.validFrom.getTime() > Date.now()),
    reason,
  };
}

/**
 * The tap. With a pass: Out moves it to OUT, In to RETURNED (an overdue
 * return is still a return). Without one: a plain movement for a student
 * walking in or out, which attendance still counts.
 */
export async function punch(input: { passId?: string; studentProfileId?: string; direction: PunchDirection; note?: string }, byUserId: string) {
  const tenantId = getTenantIdOrThrow();
  let studentProfileId = input.studentProfileId ?? null;
  let pass = input.passId ? await db.gatePass.findFirst({ where: { id: input.passId }, include: passInclude }) : null;
  if (input.passId && !pass) throw new NotFoundError('Pass not found');
  if (pass) studentProfileId = pass.studentProfileId;
  if (!studentProfileId) throw new BadRequestError('Say who is passing: a pass or a student');
  if (pass) {
    const pub = publicPass(pass);
    if (input.direction === PunchDirection.OUT && pub.nextAction !== 'OUT') throw new ConflictError(pub.status === PassStatus.OUT ? 'Already out on this pass' : `This pass is ${pub.status.toLowerCase().replace('_', ' ')}`);
    if (input.direction === PunchDirection.IN && pub.nextAction !== 'IN') throw new ConflictError('This pass has not been used to go out');
  }
  const at = new Date();
  const row = await db.gatePunch.create({ data: { tenantId, studentProfileId, passId: pass?.id ?? null, direction: input.direction, at, byUserId, source: 'GUARD', note: input.note?.slice(0, 200) ?? null } });
  if (pass) {
    pass = await db.gatePass.update({
      where: { id: pass.id },
      data: input.direction === PunchDirection.OUT ? { status: PassStatus.OUT, outAt: at } : { status: PassStatus.RETURNED, inAt: at },
      include: passInclude,
    });
  }
  return { punch: { id: row.id, direction: row.direction, at: row.at }, pass: pass ? publicPass(pass) : null };
}

/** The guard's day: who is out on a pass, who is overdue, recent movements. */
export async function gateSummary(propertyId?: string) {
  const now = new Date();
  const scope = propertyId ? { student: { propertyId } } : {};
  const [out, overdue, recent, visitorsIn, expected] = await Promise.all([
    db.gatePass.findMany({ where: { ...scope, status: PassStatus.OUT }, include: passInclude, orderBy: { validTo: 'asc' } }),
    db.gatePass.findMany({ where: { ...scope, status: PassStatus.OVERDUE }, include: passInclude, orderBy: { validTo: 'asc' } }),
    db.gatePunch.findMany({ where: { ...scope, at: { gte: new Date(now.getTime() - DAY_MS) } }, include: { student: { include: { user: { select: { name: true } }, bed: { include: { room: { select: { number: true } } } } } } }, orderBy: { at: 'desc' }, take: 50 }),
    db.visitor.count({ where: { ...(propertyId ? { propertyId } : {}), status: 'IN' } }),
    db.visitor.count({ where: { ...(propertyId ? { propertyId } : {}), status: 'EXPECTED', expectedFrom: { lte: new Date(now.getTime() + DAY_MS) } } }),
  ]);
  return {
    outNow: out.map((p) => publicPass(p, now)),
    overdue: overdue.map((p) => publicPass(p, now)),
    recent: recent.map((r) => ({ id: r.id, direction: r.direction, at: r.at, source: r.source, student: { id: r.studentProfileId, name: r.student.user.name, room: r.student.bed ? `Room ${r.student.bed.room.number} · Bed ${r.student.bed.label}` : null } })),
    visitorsIn,
    visitorsExpected: expected,
  };
}

// ── Late returns ────────────────────────────────────────────────────────────

/**
 * Hourly: a pass still OUT past its return time is flagged once, the
 * warden(s) and the guardian are emailed, and it shows in the overview.
 * Unscoped: no request context on the tick.
 */
export async function flagLateReturns(now = new Date()): Promise<{ flagged: number; emailed: number }> {
  const late = await prismaUnscoped.gatePass.findMany({
    where: { status: PassStatus.OUT, validTo: { lt: now }, lateFlaggedAt: null },
    include: { student: { include: { user: { select: { name: true, email: true } }, property: { select: { name: true } }, guardians: { include: { user: { select: { name: true, email: true } } } } } } },
    take: 200,
  });
  let emailed = 0;
  for (const p of late) {
    await prismaUnscoped.gatePass.update({ where: { id: p.id }, data: { status: PassStatus.OVERDUE, lateFlaggedAt: now } });
    await notifyPassOverdue(p.tenantId, p.id).catch((err) => console.error('[push] late return failed:', err)); // eslint-disable-line no-console
    const staff = await prismaUnscoped.user.findMany({ where: { tenantId: p.tenantId, role: { in: [Role.HOSTEL_ADMIN, Role.WARDEN] }, email: { not: '' } }, select: { email: true, name: true }, take: 10 });
    const brand = await brandForTenant(p.tenantId, p.student.property?.name);
    const firm = brand.firm;
    const recipients = [...staff.map((s) => ({ ...s, who: 'staff' as const })), ...p.student.guardians.map((g) => ({ ...g.user, who: 'guardian' as const }))];
    for (const r of recipients) {
      const mail = lateReturnEmail({ firm, brand, recipientName: r.name, studentName: p.student.user.name, dueBack: p.validTo.toLocaleString('en-IN', { day: '2-digit', month: 'short', hour: 'numeric', minute: '2-digit', timeZone: 'Asia/Kolkata' }), destination: p.destination, forGuardian: r.who === 'guardian' });
      const res = await sendEmail(r.email, mail.subject, mail.text, mail.html).catch(() => ({ sent: false, mode: 'error' as const }));
      if (res.sent || res.mode === 'log') emailed++;
    }
  }
  return { flagged: late.length, emailed };
}

// ── Attendance ──────────────────────────────────────────────────────────────

/**
 * The last N days as a strip: for each day, the first Out, the last In,
 * and whether the student was in for the night (the last movement before
 * curfew was an In, or there was no Out at all). Gate punches and the
 * biometric terminal's GATE punches count alike.
 */
export async function attendanceFor(studentProfileId: string, days = 30, curfew = '22:00') {
  const now = new Date();
  const start = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate() - (days - 1)));
  const [gate, device] = await Promise.all([
    db.gatePunch.findMany({ where: { studentProfileId, at: { gte: start } }, select: { direction: true, at: true }, orderBy: { at: 'asc' } }),
    db.biometricPunch.findMany({ where: { studentProfileId, punchedAt: { gte: start }, type: { in: [PunchType.GATE_IN, PunchType.GATE_OUT] } }, select: { type: true, punchedAt: true }, orderBy: { punchedAt: 'asc' } }),
  ]);
  const moves = [...gate.map((g) => ({ at: g.at, dir: g.direction })), ...device.map((d) => ({ at: d.punchedAt, dir: d.type === PunchType.GATE_IN ? PunchDirection.IN : PunchDirection.OUT }))].sort((a, b) => a.at.getTime() - b.at.getTime());
  const [ch, cm] = curfew.split(':').map(Number);
  const out: { date: string; firstOut: Date | null; lastIn: Date | null; moves: number; nightIn: boolean; today: boolean }[] = [];
  let lastDir: PunchDirection | null = null;
  for (let i = 0; i < days; i++) {
    const d = new Date(start.getTime() + i * DAY_MS);
    const key = d.toISOString().slice(0, 10);
    const dayMoves = moves.filter((m) => m.at.toISOString().slice(0, 10) === key);
    const curfewAt = new Date(Date.UTC(d.getUTCFullYear(), d.getUTCMonth(), d.getUTCDate(), ch, cm));
    const beforeCurfew = dayMoves.filter((m) => m.at.getTime() <= curfewAt.getTime());
    const lastBefore = beforeCurfew[beforeCurfew.length - 1]?.dir ?? lastDir;
    const nightIn = lastBefore !== PunchDirection.OUT;
    if (dayMoves.length) lastDir = dayMoves[dayMoves.length - 1].dir;
    out.push({ date: key, firstOut: dayMoves.find((m) => m.dir === PunchDirection.OUT)?.at ?? null, lastIn: [...dayMoves].reverse().find((m) => m.dir === PunchDirection.IN)?.at ?? null, moves: dayMoves.length, nightIn, today: key === now.toISOString().slice(0, 10) });
  }
  const nightsIn = out.filter((d) => d.nightIn && !d.today).length;
  return { days: out, summary: { nightsIn, nightsOut: out.length - 1 - nightsIn, currentlyOut: lastDir === PunchDirection.OUT } };
}

export async function myAttendance(userId: string, days = 30) {
  const profile = await db.studentProfile.findFirst({ where: { userId }, select: { id: true, property: { select: { curfewTime: true } } } });
  if (!profile) throw new NotFoundError('No student profile for this account');
  return attendanceFor(profile.id, days, profile.property?.curfewTime ?? '22:00');
}

/** Who is in tonight: every student with a bed, by their last movement. */
export async function tonight(propertyId?: string) {
  const students = await db.studentProfile.findMany({
    where: { bedId: { not: null }, ...(propertyId ? { propertyId } : {}) },
    include: {
      user: { select: { name: true } },
      bed: { include: { room: { select: { number: true } } } },
      gatePunches: { orderBy: { at: 'desc' }, take: 1, select: { direction: true, at: true, passId: true } },
      punches: { where: { type: { in: [PunchType.GATE_IN, PunchType.GATE_OUT] } }, orderBy: { punchedAt: 'desc' }, take: 1, select: { type: true, punchedAt: true } },
      gatePasses: { where: { status: { in: [PassStatus.OUT, PassStatus.OVERDUE] } }, take: 1, select: { id: true, validTo: true, status: true, destination: true } },
    },
    orderBy: { createdAt: 'asc' },
  });
  const rows = students.map((s) => {
    const g = s.gatePunches[0];
    const d = s.punches[0];
    const last = g && d ? (g.at > d.punchedAt ? { dir: g.direction, at: g.at } : { dir: d.type === PunchType.GATE_IN ? 'IN' : 'OUT', at: d.punchedAt }) : g ? { dir: g.direction, at: g.at } : d ? { dir: d.type === PunchType.GATE_IN ? 'IN' : 'OUT', at: d.punchedAt } : null;
    const onPass = s.gatePasses[0] ?? null;
    return { studentProfileId: s.id, name: s.user.name, room: s.bed ? `Room ${s.bed.room.number} · Bed ${s.bed.label}` : null, status: onPass ? (onPass.status === PassStatus.OVERDUE ? 'OVERDUE' : 'OUT_ON_PASS') : last ? last.dir : 'UNKNOWN', lastSeenAt: last?.at ?? null, pass: onPass };
  });
  return { summary: { in: rows.filter((r) => r.status === 'IN').length, out: rows.filter((r) => r.status === 'OUT' || r.status === 'OUT_ON_PASS').length, overdue: rows.filter((r) => r.status === 'OVERDUE').length, unknown: rows.filter((r) => r.status === 'UNKNOWN').length }, students: rows };
}

export const TONIGHT_FILTERS = ['IN', 'OUT', 'OVERDUE', 'UNKNOWN'] as const;

/**
 * Tonight's roll a page at a time (Sprint L3): the summary still counts the
 * whole property, while `status` (OUT takes in those out on a pass) and `q`
 * (name or room) narrow the rows.
 *
 * Since Sprint L4 this is worked out in SQL: each person's last gate punch,
 * last terminal gate punch and open pass are looked up beside them, and only
 * the summary's four counts and the page's rows come back. Same rows and
 * counts as `tonight`, which store builds still read in full; people who were
 * added at the same moment are ordered by id, where `tonight` leaves them in
 * whatever order the database returns.
 */
export async function pageTonight(propertyId: string | undefined, f: { status?: (typeof TONIGHT_FILTERS)[number]; q?: string }, pagination: Pagination) {
  const tenantId = getTenantIdOrThrow();
  const roll = Prisma.sql`
    WITH base AS (
      SELECT s."id", s."createdAt", u."name", ('Room ' || r."number" || ' · Bed ' || b."label") AS room,
        g."direction"::text AS g_dir, g."at" AS g_at, d."type"::text AS d_type, d."punchedAt" AS d_at,
        p."id" AS pass_id, p."validTo" AS pass_valid_to, p."status"::text AS pass_status, p."destination" AS pass_destination
      FROM "StudentProfile" s
      JOIN "User" u ON u."id" = s."userId"
      JOIN "Bed" b ON b."id" = s."bedId"
      JOIN "Room" r ON r."id" = b."roomId"
      LEFT JOIN LATERAL (SELECT "direction", "at" FROM "GatePunch" WHERE "studentProfileId" = s."id" ORDER BY "at" DESC LIMIT 1) g ON TRUE
      LEFT JOIN LATERAL (SELECT "type", "punchedAt" FROM "BiometricPunch" WHERE "studentProfileId" = s."id" AND "type" IN ('GATE_IN', 'GATE_OUT') ORDER BY "punchedAt" DESC LIMIT 1) d ON TRUE
      LEFT JOIN LATERAL (SELECT "id", "validTo", "status", "destination" FROM "GatePass" WHERE "studentProfileId" = s."id" AND "status" IN ('OUT', 'OVERDUE') ORDER BY "id" LIMIT 1) p ON TRUE
      WHERE s."tenantId" = ${tenantId} AND s."bedId" IS NOT NULL ${propertyId ? Prisma.sql`AND s."propertyId" = ${propertyId}` : Prisma.empty}
    ), seen AS (
      -- The later of the two punches; on a tie the terminal's, as tonight() decides it.
      SELECT *, CASE WHEN g_at IS NOT NULL AND (d_at IS NULL OR g_at > d_at) THEN g_dir WHEN d_at IS NOT NULL THEN CASE WHEN d_type = 'GATE_IN' THEN 'IN' ELSE 'OUT' END END AS last_dir,
        CASE WHEN g_at IS NOT NULL AND (d_at IS NULL OR g_at > d_at) THEN g_at ELSE d_at END AS last_at
      FROM base
    ), roll AS (
      SELECT *, CASE WHEN pass_id IS NOT NULL THEN CASE WHEN pass_status = 'OVERDUE' THEN 'OVERDUE' ELSE 'OUT_ON_PASS' END ELSE COALESCE(last_dir, 'UNKNOWN') END AS status FROM seen
    )`;
  const q = f.q?.toLowerCase();
  const filters = [
    f.status === 'OUT' ? Prisma.sql`status IN ('OUT', 'OUT_ON_PASS')` : f.status ? Prisma.sql`status = ${f.status}` : null,
    q ? Prisma.sql`(strpos(lower("name"), ${q}) > 0 OR strpos(lower(room), ${q}) > 0)` : null,
  ].filter((x): x is Prisma.Sql => x !== null);
  const where = filters.length ? Prisma.sql`WHERE ${Prisma.join(filters, ' AND ')}` : Prisma.empty;
  type Row = { id: string; name: string; room: string; status: string; last_at: Date | null; pass_id: string | null; pass_valid_to: Date | null; pass_status: string | null; pass_destination: string | null; matched: bigint };
  const [counts, rows] = await Promise.all([
    prismaUnscoped.$queryRaw<{ in: bigint; out: bigint; overdue: bigint; unknown: bigint }[]>(Prisma.sql`${roll}
      SELECT count(*) FILTER (WHERE status = 'IN') AS "in", count(*) FILTER (WHERE status IN ('OUT', 'OUT_ON_PASS')) AS "out",
        count(*) FILTER (WHERE status = 'OVERDUE') AS "overdue", count(*) FILTER (WHERE status = 'UNKNOWN') AS "unknown" FROM roll`),
    prismaUnscoped.$queryRaw<Row[]>(Prisma.sql`${roll}
      SELECT "id", "name", room, status, last_at, pass_id, pass_valid_to, pass_status, pass_destination, count(*) OVER () AS matched
      FROM roll ${where} ORDER BY "createdAt" ASC, "id" ASC LIMIT ${pagination.limit} OFFSET ${pagination.skip}`),
  ]);
  const c = counts[0];
  const summary = { in: Number(c?.in ?? 0), out: Number(c?.out ?? 0), overdue: Number(c?.overdue ?? 0), unknown: Number(c?.unknown ?? 0) };
  // A page past the end has no row to carry the count.
  const total = rows.length ? Number(rows[0].matched) : pagination.skip > 0 ? await countRoll(roll, where) : 0;
  const items = rows.map((r) => ({
    studentProfileId: r.id,
    name: r.name,
    room: r.room,
    status: r.status,
    lastSeenAt: r.last_at,
    pass: r.pass_id ? { id: r.pass_id, validTo: r.pass_valid_to, status: r.pass_status, destination: r.pass_destination } : null,
  }));
  return { summary, ...paginated(items, total, pagination) };
}

async function countRoll(roll: Prisma.Sql, where: Prisma.Sql) {
  const [r] = await prismaUnscoped.$queryRaw<{ n: bigint }[]>(Prisma.sql`${roll} SELECT count(*) AS n FROM roll ${where}`);
  return Number(r?.n ?? 0);
}
