const db = getReadDb(getDatabaseUrl());
const now = new Date();
const todayStr = now.toISOString().split('T')[0];
const ninetyAgo = new Date(now); ninetyAgo.setDate(ninetyAgo.getDate() - 90);
const ninetyAgoStr = ninetyAgo.toISOString().split('T')[0];
// Today's appointments + denormalised patient signals for risk scoring.
const todayAppts = await db
.select({
id: appointments.id,
patientId: appointments.patientId,
firstName: patients.firstName,
lastName: patients.lastName,
appointmentTime: appointments.appointmentTime,
appointmentType: appointments.appointmentType,
status: appointments.status,
doctorId: appointments.doctorId,
roomId: appointments.roomId,
createdAt: appointments.createdAt,
})
.from(appointments)
.innerJoin(patients, eq(patients.id, appointments.patientId))
.where(and(
eq(appointments.clinicId, clinicId),
eq(appointments.appointmentDate, todayStr),
))
.orderBy(appointments.appointmentTime);
// Per-patient 90d miss stats.
const patientIds = Array.from(new Set(todayAppts.map(a => a.patientId)));
const missStats = patientIds.length === 0 ? [] : await db
.select({
patientId: appointments.patientId,
total: sql<number>`COUNT(*)::int`,
missed: sql<number>`COUNT(*) FILTER (WHERE ${appointments.status} = 'missed')::int`,
firstAppt: sql<string>`MIN(${appointments.appointmentDate})::text`,
})
.from(appointments)
.where(and(
eq(appointments.clinicId, clinicId),
inArray(appointments.patientId, patientIds),
gte(appointments.appointmentDate, ninetyAgoStr),
))
.groupBy(appointments.patientId);
const statsByPatient = new Map(missStats.map(r => [r.patientId, r]));
// Balances per appointment.
const balances = await db
.select({
patientId: invoices.patientId,
balance: sql<string>`COALESCE(SUM(${invoices.balance}::numeric), 0)::text`,
})
.from(invoices)
.where(and(
eq(invoices.clinicId, clinicId),
sql`${invoices.balance}::numeric > 0`,
not(eq(invoices.status, 'cancelled')),
patientIds.length > 0 ? inArray(invoices.patientId, patientIds) : sql`false`,
))
.groupBy(invoices.patientId);
const balanceByPatient = new Map(balances.map(r => [r.patientId, parseFloat(r.balance)]));
// Recalls due today + overdue.
const recallCounts = await db
.select({
dueToday: sql<number>`COUNT(*) FILTER (WHERE ${patientRecalls.dueDate}::date = ${todayStr})::int`,
overdue: sql<number>`COUNT(*) FILTER (WHERE ${patientRecalls.dueDate}::date < ${todayStr})::int`,
})
.from(patientRecalls)
.where(and(eq(patientRecalls.clinicId, clinicId), eq(patientRecalls.status, 'pending')));
// Doctor coverage signal.
const docSchedules = await db
.select({ doctorId: doctorSchedules.doctorId })
.from(doctorSchedules)
.where(eq(doctorSchedules.clinicId, clinicId));
const doctorsWithSchedule = new Set(docSchedules.map(r => r.doctorId));
const activeDoctors = await db
.select({ id: users.id, firstName: users.firstName, lastName: users.lastName })
.from(users)
.where(and(eq(users.clinicId, clinicId), eq(users.role, 'doctor'), eq(users.isActive, true)));
// Inventory low/out signal (count only).
const inv = await db
.select({ outOfStock: sql<number>`COUNT(*) FILTER (WHERE quantity = 0 AND min_stock > 0)::int` })
.from(inventoryItems)
.where(eq(inventoryItems.clinicId, clinicId));
// Build derived per-appointment info.
const enriched = todayAppts.map(a => {
const stats = statsByPatient.get(a.patientId);
const isFirstVisit = !stats || stats.total === 1;
const leadDays = a.createdAt
? Math.max(0, Math.floor((new Date(todayStr).getTime() - new Date(a.createdAt).getTime()) / 86_400_000))
: 0;
const risk = computeNoShowRisk({
missed90d: stats?.missed ?? 0,
total90d: stats?.total ?? 0,
leadDaysFromBooking: leadDays,
isFirstVisit,
outstandingBalance: balanceByPatient.get(a.patientId) ?? 0,
lateArrivalsLast3: 0, // not tracked yet
});
const decrypted = decryptPatientPHI({ firstName: a.firstName, lastName: a.lastName, phone: null });
return {
id: a.id,
first: decrypted.firstName,
time: a.appointmentTime?.slice(0, 5) || '?',
type: a.appointmentType,
doctorAssigned: !!a.doctorId,
roomAssigned: !!a.roomId,
status: a.status,
noShowRisk: risk,
balance: balanceByPatient.get(a.patientId) ?? 0,
isFirstVisit,
missedLastTwo: (stats?.missed ?? 0) >= 2,
};
});
const totalToday = enriched.length;
const confirmed = enriched.filter(e => e.status === 'confirmed').length;
const unconfirmed = enriched.filter(e => e.status === 'scheduled').length;
const firstVisits = enriched.filter(e => e.isFirstVisit).length;
const withBalance = enriched.filter(e => e.balance > 0).length;
const highRisk = enriched.filter(e => e.noShowRisk >= 60).length;
const unassignedDoctor = enriched.filter(e => !e.doctorAssigned).length;
const topRiskPatients = enriched
.filter(e => e.noShowRisk >= 60)
.sort((a, b) => b.noShowRisk - a.noShowRisk)
.slice(0, 3)
.map(e => ({
first: e.first,
time: e.time,
reason: e.missedLastTwo ? 'missed last 2 visits' : 'high no-show risk',
}));
const topOwed = enriched
.filter(e => e.balance > 0)
.sort((a, b) => b.balance - a.balance)
.slice(0, 3)
.map(e => ({ first: e.first, balance: e.balance }));
const expectedCollections = enriched.reduce((acc, e) => acc + e.balance, 0);
const doctorScheduleGaps = activeDoctors
.filter(d => !doctorsWithSchedule.has(d.id))
.slice(0, 3)
.map(d => {
const dec = decryptPatientPHI({ firstName: d.firstName ?? '', lastName: d.lastName ?? '', phone: null });
return `No schedule defined for Dr. ${dec.firstName} ${dec.lastName} — slot picker will be unconstrained`;
});
const context = {
date: todayStr,
dayName: now.toLocaleDateString('en-US', { weekday: 'long', timeZone: 'Asia/Karachi' }),
receptionistName: opts.receptionistName ?? null,
appointments: {
totalToday, confirmed, unconfirmed, firstVisits, withBalance, highNoShowRisk: highRisk, unassignedDoctor,
topRiskPatients,
},
balances: { expectedCollections, patientsOwing: withBalance, topOwed },
recalls: { dueToday: recallCounts[0]?.dueToday ?? 0, overdue: recallCounts[0]?.overdue ?? 0 },
operations: {
doctorsAvailable: activeDoctors.length,
doctorScheduleGaps,
inventoryLow: inv[0]?.outOfStock ?? 0,
},
};
const result = await callAIJson<MorningBriefResult>({
agentName: 'reception-morning-brief-agent',
promptName: PROMPT_NAMES.receptionMorningBrief,
systemPrompt: PROMPTS.receptionMorningBrief,
input: JSON.stringify(context),
clinicId, userId,
temperature: 0.3, maxTokens: 1200,
metadata: { surface: 'reception-morning-brief' },
});
return {
greeting: result.data.greeting ?? '',
headline: result.data.headline ?? '',
priorityList: Array.isArray(result.data.priorityList) ? result.data.priorityList.slice(0, 6) : [],
operationalAlerts: Array.isArray(result.data.operationalAlerts) ? result.data.operationalAlerts.slice(0, 4) : [],
generatedAt: new Date().toISOString(),
};
