// server/src/lib/dashboard-metrics.test.ts
import { describe, it, expect } from 'vitest';
import {
  minutesBetween,
  weekdayKey,
  summarizeTodayFlow,
  computeChairUtilization,
  computeAcceptanceRate,
  bucketRevenueByTreatment,
} from './dashboard-metrics';

describe('minutesBetween', () => {
  it('computes minute span of two HH:MM[:SS] strings', () => {
    expect(minutesBetween('09:00', '17:00')).toBe(480);
    expect(minutesBetween('09:00:00', '17:30:00')).toBe(510);
  });
  it('returns 0 for null/invalid/negative', () => {
    expect(minutesBetween(null, '17:00')).toBe(0);
    expect(minutesBetween('17:00', '09:00')).toBe(0);
    expect(minutesBetween('bad', '17:00')).toBe(0);
  });
});

describe('weekdayKey', () => {
  it('maps an ISO date string to a lowercase day key', () => {
    // 2026-06-01 is a Monday
    expect(weekdayKey('2026-06-01')).toBe('monday');
    expect(weekdayKey('2026-05-31')).toBe('sunday');
  });
});

describe('summarizeTodayFlow', () => {
  it('counts by mapped status and excludes requested from total', () => {
    const flow = summarizeTodayFlow([
      { status: 'scheduled' }, { status: 'confirmed' }, { status: 'in_progress' },
      { status: 'completed' }, { status: 'cancelled' }, { status: 'no_show' },
      { status: 'missed' }, { status: 'requested' },
    ]);
    expect(flow).toEqual({
      appointments: 7, scheduled: 2, checkedIn: 1, completed: 1, cancelled: 1, noShows: 2,
    });
  });
});

describe('computeChairUtilization', () => {
  it('returns percentage capped at 100 with detail', () => {
    const r = computeChairUtilization({ bookedMinutes: 240, activeChairs: 2, openMinutes: 480 });
    expect(r).toEqual({
      chairUtilization: 25,
      chairUtilizationDetail: { bookedMinutes: 240, capacityMinutes: 960, activeChairs: 2, openMinutes: 480 },
    });
  });
  it('caps at 100 when overbooked', () => {
    expect(computeChairUtilization({ bookedMinutes: 2000, activeChairs: 1, openMinutes: 480 }).chairUtilization).toBe(100);
  });
  it('returns null when chairs or hours are zero', () => {
    expect(computeChairUtilization({ bookedMinutes: 100, activeChairs: 0, openMinutes: 480 })).toEqual({ chairUtilization: null, chairUtilizationDetail: null });
    expect(computeChairUtilization({ bookedMinutes: 100, activeChairs: 2, openMinutes: 0 })).toEqual({ chairUtilization: null, chairUtilizationDetail: null });
  });
});

describe('computeAcceptanceRate', () => {
  it('treats approved/in_progress/completed as accepted; proposed+cancelled count against', () => {
    const r = computeAcceptanceRate([
      { status: 'approved' }, { status: 'in_progress' }, { status: 'completed' },
      { status: 'proposed' }, { status: 'cancelled' },
    ]);
    expect(r).toEqual({ treatmentAcceptance: 60, accepted: 3, proposed: 1, total: 5 });
  });
  it('returns null rate when no plans', () => {
    expect(computeAcceptanceRate([])).toEqual({ treatmentAcceptance: null, accepted: 0, proposed: 0, total: 0 });
  });
});

describe('bucketRevenueByTreatment', () => {
  it('sums by category, sorts desc, and rolls overflow into Other', () => {
    const rows = [
      { category: 'Scaling', amount: 100 }, { category: 'Scaling', amount: 50 },
      { category: 'Crowns', amount: 300 }, { category: 'Implants', amount: 200 },
      { category: 'A', amount: 9 }, { category: 'B', amount: 8 }, { category: 'C', amount: 7 },
      { category: 'D', amount: 6 }, { category: 'E', amount: 5 }, { category: 'F', amount: 4 },
    ];
    const out = bucketRevenueByTreatment(rows, 8);
    expect(out[0]).toEqual({ category: 'Crowns', amount: 300 });
    expect(out.length).toBe(8);
    expect(out[out.length - 1]).toEqual({ category: 'Other', amount: 9 }); // E(5)+F(4)
  });
  it('returns empty array for no rows', () => {
    expect(bucketRevenueByTreatment([], 8)).toEqual([]);
  });
});

// server/src/lib/dashboard-metrics.ts
// Pure, dependency-free helpers for the owner dashboard summary.
// Kept free of DB/IO so they are unit-testable in isolation.

export type ApptStatus =
  | 'requested' | 'scheduled' | 'confirmed' | 'in_progress'
  | 'completed' | 'cancelled' | 'no_show' | 'missed';

const DAY_KEYS = ['sunday','monday','tuesday','wednesday','thursday','friday','saturday'] as const;
export type DayKey = typeof DAY_KEYS[number];

/** Minutes between two 'HH:MM' or 'HH:MM:SS' strings. 0 if invalid/negative/null. */
export function minutesBetween(open: string | null | undefined, close: string | null | undefined): number {
  const toMin = (t?: string | null): number | null => {
    if (!t) return null;
    const m = /^(\d{1,2}):(\d{2})/.exec(t);
    if (!m) return null;
    const h = Number(m[1]); const mm = Number(m[2]);
    if (!Number.isFinite(h) || !Number.isFinite(mm)) return null;
    return h * 60 + mm;
  };
  const a = toMin(open); const b = toMin(close);
  if (a == null || b == null) return 0;
  return Math.max(0, b - a);
}

/** Lowercase day-of-week key for a 'YYYY-MM-DD' date (parsed as PKT-local midday to avoid TZ edges). */
export function weekdayKey(dateStr: string): DayKey {
  const d = new Date(`${dateStr}T12:00:00+05:00`);
  return DAY_KEYS[d.getUTCDay() === 0 ? 0 : d.getUTCDay()] ?? 'monday';
}

export interface TodayFlow {
  appointments: number; scheduled: number; checkedIn: number;
  completed: number; cancelled: number; noShows: number;
}

/** Counts today's appts by mapped status. 'requested' is excluded from the day total. */
export function summarizeTodayFlow(appts: Array<{ status: ApptStatus | string }>): TodayFlow {
  const f: TodayFlow = { appointments: 0, scheduled: 0, checkedIn: 0, completed: 0, cancelled: 0, noShows: 0 };
  for (const a of appts) {
    const s = a.status;
    if (s === 'requested') continue;
    f.appointments += 1;
    if (s === 'scheduled' || s === 'confirmed') f.scheduled += 1;
    else if (s === 'in_progress') f.checkedIn += 1;
    else if (s === 'completed') f.completed += 1;
    else if (s === 'cancelled') f.cancelled += 1;
    else if (s === 'no_show' || s === 'missed') f.noShows += 1;
  }
  return f;
}

export interface ChairUtil {
  chairUtilization: number | null;
  chairUtilizationDetail: { bookedMinutes: number; capacityMinutes: number; activeChairs: number; openMinutes: number } | null;
}

export function computeChairUtilization(input: { bookedMinutes: number; activeChairs: number; openMinutes: number }): ChairUtil {
  const { bookedMinutes, activeChairs, openMinutes } = input;
  const capacityMinutes = activeChairs * openMinutes;
  if (activeChairs <= 0 || openMinutes <= 0 || capacityMinutes <= 0) {
    return { chairUtilization: null, chairUtilizationDetail: null };
  }
  const pct = Math.min(100, Math.round((bookedMinutes / capacityMinutes) * 100));
  return { chairUtilization: pct, chairUtilizationDetail: { bookedMinutes, capacityMinutes, activeChairs, openMinutes } };
}

export interface Acceptance { treatmentAcceptance: number | null; accepted: number; proposed: number; total: number; }

export function computeAcceptanceRate(plans: Array<{ status: string }>): Acceptance {
  let accepted = 0, proposed = 0, total = 0;
  for (const p of plans) {
    if (p.status === 'approved' || p.status === 'in_progress' || p.status === 'completed') { accepted += 1; total += 1; }
    else if (p.status === 'proposed') { proposed += 1; total += 1; }
    else if (p.status === 'cancelled') { total += 1; }
  }
  return { treatmentAcceptance: total > 0 ? Math.round((accepted / total) * 100) : null, accepted, proposed, total };
}

/** Sum amounts per category, sort desc, keep top (n-1) and roll the rest into 'Other'. */
export function bucketRevenueByTreatment(rows: Array<{ category: string; amount: number }>, topN = 8): Array<{ category: string; amount: number }> {
  if (!rows.length) return [];
  const byCat = new Map<string, number>();
  for (const r of rows) {
    const key = (r.category && r.category.trim()) || 'Uncategorized';
    byCat.set(key, (byCat.get(key) ?? 0) + (Number(r.amount) || 0));
  }
  const sorted = [...byCat.entries()].map(([category, amount]) => ({ category, amount })).sort((a, b) => b.amount - a.amount);
  if (sorted.length <= topN) return sorted;
  const head = sorted.slice(0, topN - 1);
  const otherAmount = sorted.slice(topN - 1).reduce((s, x) => s + x.amount, 0);
  return [...head, { category: 'Other', amount: otherAmount }];
}

git add server/src/lib/dashboard-metrics.ts server/src/lib/dashboard-metrics.test.ts
git commit -m "feat(stats): pure dashboard-metrics helpers + tests"

import {
  minutesBetween, weekdayKey, summarizeTodayFlow,
  computeChairUtilization, computeAcceptanceRate, bucketRevenueByTreatment,
} from '../lib/dashboard-metrics';

    // ===== Dental owner-dashboard additions =====
    const monthStartDateStr = monthStartStr; // 'YYYY-MM-DD' for date columns
    const sevenDaysAgo = new Date(today); sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);
    const sevenDaysAgoStr = sevenDaysAgo.toISOString().split('T')[0];
    const todayWeekday = weekdayKey(todayStr);

    const [
      todayApptsAll,            // all today's appts (status only) for flow counts
      collectionsTodayRow,      // receipts today
      pendingRows,              // invoices with outstanding balance
      activeRoomsRow,           // count active rooms
      operatingRows,            // clinic operating hours (all days)
      acceptancePlans,          // treatment plans last 90d
      recallDueRow, recallOverdueRow,
      labDueRow,
      missedRow,
      inventoryLowRow,
      newPatientsRow,
      revenueItemRows,          // invoice_items this month + description
      clinicProcRows,           // procedure name -> category map
      weeklyRows,               // last 7 days appts (date+status) for booked/cxl/noshow
    ] = await Promise.all([
      db.select({ status: schema.appointments.status, durationMinutes: schema.appointments.durationMinutes })
        .from(schema.appointments)
        .where(and(eq(schema.appointments.clinicId, targetClinicId), eq(schema.appointments.appointmentDate, todayStr))),

      db.select({ total: sum(schema.receipts.amount) })
        .from(schema.receipts)
        .where(and(eq(schema.receipts.clinicId, targetClinicId), eq(schema.receipts.receiptDate, todayStr), eq(schema.receipts.status, 'issued'))),

      db.select({ balance: schema.invoices.balance })
        .from(schema.invoices)
        .where(and(eq(schema.invoices.clinicId, targetClinicId), inArray(schema.invoices.status, ['unpaid', 'partial', 'overdue']))),

      db.select({ count: count() }).from(schema.rooms)
        .where(and(eq(schema.rooms.clinicId, targetClinicId), eq(schema.rooms.isActive, true))),

      db.select({ dayOfWeek: schema.clinicOperatingHours.dayOfWeek, openTime: schema.clinicOperatingHours.openTime, closeTime: schema.clinicOperatingHours.closeTime, isClosed: schema.clinicOperatingHours.isClosed })
        .from(schema.clinicOperatingHours)
        .where(eq(schema.clinicOperatingHours.clinicId, targetClinicId)),

      db.select({ status: schema.treatmentPlans.status })
        .from(schema.treatmentPlans)
        .where(and(eq(schema.treatmentPlans.clinicId, targetClinicId), gte(schema.treatmentPlans.createdAt, new Date(`${sevenDaysAgoStr}T00:00:00+05:00`)))), // replaced below with 90d

      db.select({ count: count() }).from(schema.patientRecalls)
        .where(and(eq(schema.patientRecalls.clinicId, targetClinicId), lte(schema.patientRecalls.dueDate, todayStr), inArray(schema.patientRecalls.status, ['pending', 'contacted', 'overdue']))),
      db.select({ count: count() }).from(schema.patientRecalls)
        .where(and(eq(schema.patientRecalls.clinicId, targetClinicId), lte(schema.patientRecalls.dueDate, todayStr), inArray(schema.patientRecalls.status, ['pending', 'contacted', 'overdue']))),

      db.select({ count: count() }).from(schema.labCases)
        .where(and(eq(schema.labCases.clinicId, targetClinicId), lte(schema.labCases.dueDate, todayStr), inArray(schema.labCases.status, ['pending', 'received', 'in_progress', 'delayed']))),

      db.select({ count: count() }).from(schema.appointments)
        .where(and(eq(schema.appointments.clinicId, targetClinicId), inArray(schema.appointments.status, ['no_show', 'missed']), gte(schema.appointments.appointmentDate, sevenDaysAgoStr), lte(schema.appointments.appointmentDate, todayStr))),

      db.select({ count: count() }).from(schema.inventoryItems)
        .where(and(eq(schema.inventoryItems.clinicId, targetClinicId), sql`${schema.inventoryItems.quantity} <= GREATEST(COALESCE(${schema.inventoryItems.reorderPoint}, 0), ${schema.inventoryItems.minStock})`)),

      db.select({ count: count() }).from(schema.patients)
        .where(and(eq(schema.patients.clinicId, targetClinicId), isNull(schema.patients.deletedAt), gte(schema.patients.createdAt, monthStart))),

      db.select({ description: schema.invoiceItems.description, subtotal: schema.invoiceItems.subtotal })
        .from(schema.invoiceItems)
        .innerJoin(schema.invoices, eq(schema.invoiceItems.invoiceId, schema.invoices.id))
        .where(and(eq(schema.invoices.clinicId, targetClinicId), gte(schema.invoices.invoiceDate, monthStartDateStr), sql`${schema.invoices.status} <> 'cancelled'`)),

      db.select({ procedureName: schema.clinicProcedures.procedureName, category: schema.clinicProcedures.category })
        .from(schema.clinicProcedures)
        .where(eq(schema.clinicProcedures.clinicId, targetClinicId)),

      db.select({ date: schema.appointments.appointmentDate, status: schema.appointments.status })
        .from(schema.appointments)
        .where(and(eq(schema.appointments.clinicId, targetClinicId), gte(schema.appointments.appointmentDate, sevenDaysAgoStr), lte(schema.appointments.appointmentDate, todayStr))),
    ]);

    // Flow + chair utilization
    const todayFlow = summarizeTodayFlow(todayApptsAll as Array<{ status: string }>);
    const bookedMinutesToday = (todayApptsAll as Array<{ status: string; durationMinutes: number | null }>)
      .filter(a => !['cancelled', 'no_show', 'missed', 'requested'].includes(a.status))
      .reduce((s, a) => s + (a.durationMinutes ?? 0), 0);
    const activeChairs = parseInt(activeRoomsRow[0]?.count?.toString() || '0');
    const todayHours = operatingRows.find(r => r.dayOfWeek === todayWeekday);
    const openMinutesToday = todayHours && !todayHours.isClosed ? minutesBetween(todayHours.openTime, todayHours.closeTime) : 0;
    const chair = computeChairUtilization({ bookedMinutes: bookedMinutesToday, activeChairs, openMinutes: openMinutesToday });

    // Collections / pending
    const collectionsToday = parseFloat(collectionsTodayRow[0]?.total || '0');
    const pendingPaymentsAmount = pendingRows.reduce((s, r) => s + parseFloat(r.balance || '0'), 0);
    const pendingPaymentsCount = pendingRows.length;

    // Acceptance
    const acceptance = computeAcceptanceRate(acceptancePlans as Array<{ status: string }>);

    // Revenue by treatment: resolve category via procedureName map (case-insensitive)
    const catByName = new Map<string, string>();
    for (const p of clinicProcRows) catByName.set((p.procedureName || '').trim().toLowerCase(), (p.category || '').trim());
    const revenueRows = (revenueItemRows as Array<{ description: string; subtotal: string }>).map(r => ({
      category: catByName.get((r.description || '').trim().toLowerCase()) || 'Uncategorized',
      amount: parseFloat(r.subtotal || '0'),
    }));
    const revenueByTreatment = bucketRevenueByTreatment(revenueRows, 8);

    // Weekly load (last 7 days): booked / cancelled / no-shows + capacity from chairs*openMinutes(day)
    const dayOrder: string[] = [];
    const weekMap: Record<string, { booked: number; cancelled: number; noShows: number; capacity: number | null }> = {};
    for (let i = 6; i >= 0; i--) {
      const d = new Date(today); d.setDate(d.getDate() - i);
      const ds = d.toISOString().split('T')[0];
      const label = d.toLocaleString('en-US', { weekday: 'short' });
      dayOrder.push(label);
      const wk = weekdayKey(ds);
      const hrs = operatingRows.find(r => r.dayOfWeek === wk);
      const openMin = hrs && !hrs.isClosed ? minutesBetween(hrs.openTime, hrs.closeTime) : 0;
      const capacity = activeChairs > 0 && openMin > 0 ? Math.floor((activeChairs * openMin) / 30) : null;
      weekMap[ds] = { booked: 0, cancelled: 0, noShows: 0, capacity };
    }
    for (const row of weeklyRows as Array<{ date: string; status: string }>) {
      const bucket = weekMap[row.date]; if (!bucket) continue;
      if (row.status === 'cancelled') bucket.cancelled += 1;
      else if (row.status === 'no_show' || row.status === 'missed') bucket.noShows += 1;
      else if (row.status !== 'requested') bucket.booked += 1;
    }
    const weeklyAppointments = (() => {
      const out: Array<{ day: string; booked: number; cancelled: number; noShows: number; capacity: number | null }> = [];
      for (let i = 6; i >= 0; i--) {
        const d = new Date(today); d.setDate(d.getDate() - i);
        const ds = d.toISOString().split('T')[0];
        const label = d.toLocaleString('en-US', { weekday: 'short' });
        out.push({ day: label, ...weekMap[ds] });
      }
      return out;
    })();

    // Today's schedule (up to 8) with treatment type + payment status
    const scheduleRaw = await db
      .select({
        id: schema.appointments.id,
        patientFirst: schema.patients.firstName,
        patientLast: schema.patients.lastName,
        doctorId: schema.appointments.doctorId,
        time: schema.appointments.appointmentTime,
        treatmentType: schema.appointments.appointmentType,
        status: schema.appointments.status,
        invStatus: schema.invoices.status,
      })
      .from(schema.appointments)
      .leftJoin(schema.patients, eq(schema.appointments.patientId, schema.patients.id))
      .leftJoin(schema.invoices, eq(schema.invoices.appointmentId, schema.appointments.id))
      .where(and(
        eq(schema.appointments.clinicId, targetClinicId),
        eq(schema.appointments.appointmentDate, todayStr),
        sql`${schema.appointments.status} <> 'requested'`,
      ))
      .orderBy(schema.appointments.appointmentTime)
      .limit(8);

    const schedDoctorIds = scheduleRaw.map(r => r.doctorId).filter(Boolean) as string[];
    const schedDoctors = schedDoctorIds.length
      ? await db.select({ id: schema.users.id, firstName: schema.users.firstName, lastName: schema.users.lastName }).from(schema.users).where(inArray(schema.users.id, schedDoctorIds))
      : [];
    const paymentStatusOf = (invStatus: string | null): 'paid' | 'pending' | 'none' => {
      if (!invStatus || invStatus === 'cancelled') return 'none';
      if (invStatus === 'paid') return 'paid';
      return 'pending'; // unpaid, partial, overdue
    };
    const schedule = scheduleRaw.map(r => {
      const doc = schedDoctors.find(d => d.id === r.doctorId);
      return {
        id: r.id,
        patient: r.patientFirst ? `${r.patientFirst} ${r.patientLast ?? ''}`.trim() : 'Unknown',
        doctor: doc ? `Dr. ${doc.firstName} ${doc.lastName}` : 'TBD',
        time: r.time || '',
        treatmentType: r.treatmentType || '',
        status: r.status,
        paymentStatus: paymentStatusOf(r.invStatus),
      };
    });

    // Provider performance (this month)
    const [completedThisMonth, plansThisMonth, receiptsByDoctor] = await Promise.all([
      db.select({ doctorId: schema.appointments.doctorId, patientId: schema.appointments.patientId })
        .from(schema.appointments)
        .where(and(eq(schema.appointments.clinicId, targetClinicId), eq(schema.appointments.status, 'completed'), gte(schema.appointments.appointmentDate, monthStartDateStr), lte(schema.appointments.appointmentDate, todayStr))),
      db.select({ doctorId: schema.treatmentPlans.doctorId, status: schema.treatmentPlans.status })
        .from(schema.treatmentPlans)
        .where(and(eq(schema.treatmentPlans.clinicId, targetClinicId), gte(schema.treatmentPlans.createdAt, monthStart))),
      db.select({ doctorId: schema.appointments.doctorId, amount: schema.receipts.amount })
        .from(schema.receipts)
        .innerJoin(schema.invoices, eq(schema.receipts.invoiceId, schema.invoices.id))
        .innerJoin(schema.appointments, eq(schema.invoices.appointmentId, schema.appointments.id))
        .where(and(eq(schema.receipts.clinicId, targetClinicId), eq(schema.receipts.status, 'issued'), gte(schema.receipts.receiptDate, monthStartDateStr), lte(schema.receipts.receiptDate, todayStr))),
    ]);

    // Resolve doctor display names by the IDs that actually appear in the aggregates
    // (clinic-scoped already via the queries above). Robust to multi-clinic doctors
    // whose membership lives in primaryClinicId/assignments rather than users.clinicId.
    const provDoctorIds = Array.from(new Set([
      ...completedThisMonth.map(r => r.doctorId),
      ...plansThisMonth.map(r => r.doctorId),
      ...receiptsByDoctor.map(r => r.doctorId),
    ].filter(Boolean))) as string[];
    const clinicDoctors = provDoctorIds.length
      ? await db.select({ id: schema.users.id, firstName: schema.users.firstName, lastName: schema.users.lastName })
          .from(schema.users).where(inArray(schema.users.id, provDoctorIds))
      : [];

    type Prov = { id: string; name: string; patientsSeen: number; appointments: number; revenue: number; plansProposed: number; accepted: number; total: number };
    const provMap = new Map<string, Prov>();
    const ensure = (id: string) => {
      if (!provMap.has(id)) {
        const d = clinicDoctors.find(x => x.id === id);
        provMap.set(id, { id, name: d ? `Dr. ${d.firstName} ${d.lastName}` : 'Unknown', patientsSeen: 0, appointments: 0, revenue: 0, plansProposed: 0, accepted: 0, total: 0 });
      }
      return provMap.get(id)!;
    };
    const seenPatients = new Map<string, Set<string>>();
    for (const r of completedThisMonth) {
      if (!r.doctorId) continue;
      const p = ensure(r.doctorId); p.appointments += 1;
      if (!seenPatients.has(r.doctorId)) seenPatients.set(r.doctorId, new Set());
      if (r.patientId) seenPatients.get(r.doctorId)!.add(r.patientId);
    }
    for (const [id, set] of seenPatients) ensure(id).patientsSeen = set.size;
    for (const r of plansThisMonth) {
      if (!r.doctorId) continue; const p = ensure(r.doctorId);
      if (r.status === 'proposed') { p.plansProposed += 1; p.total += 1; }
      else if (r.status === 'approved' || r.status === 'in_progress' || r.status === 'completed') { p.accepted += 1; p.total += 1; }
      else if (r.status === 'cancelled') { p.total += 1; }
    }
    for (const r of receiptsByDoctor) { if (!r.doctorId) continue; ensure(r.doctorId).revenue += parseFloat(r.amount || '0'); }
    const providers = [...provMap.values()]
      .map(p => ({ id: p.id, name: p.name, patientsSeen: p.patientsSeen, appointments: p.appointments, revenue: p.revenue, plansProposed: p.plansProposed, acceptanceRate: p.total > 0 ? Math.round((p.accepted / p.total) * 100) : null }))
      .sort((a, b) => b.revenue - a.revenue);

      today: todayFlow,
      collections: { today: collectionsToday, month: revenueAmount, pending: pendingPaymentsAmount },
      operations: {
        chairUtilization: chair.chairUtilization,
        chairUtilizationDetail: chair.chairUtilizationDetail,
        treatmentAcceptance: acceptance.treatmentAcceptance,
        treatmentAcceptanceDetail: { accepted: acceptance.accepted, proposed: acceptance.proposed, total: acceptance.total, windowDays: 90 },
        recallDue: parseInt(recallDueRow[0]?.count?.toString() || '0'),
        newPatientsThisMonth: parseInt(newPatientsRow[0]?.count?.toString() || '0'),
      },
      schedule,
      revenueByTreatment,
      weeklyAppointments,
      actionItems: {
        recallsOverdue: parseInt(recallOverdueRow[0]?.count?.toString() || '0'),
        unacceptedPlans: 0, // set in Step 6
        pendingPayments: pendingPaymentsCount,
        pendingPaymentsAmount,
        labCasesDue: parseInt(labDueRow[0]?.count?.toString() || '0'),
        missedToCallback: parseInt(missedRow[0]?.count?.toString() || '0'),
        inventoryLowStock: parseInt(inventoryLowRow[0]?.count?.toString() || '0'),
      },
      providers,

      db.select({ count: count() }).from(schema.treatmentPlans)
        .where(and(eq(schema.treatmentPlans.clinicId, targetClinicId), eq(schema.treatmentPlans.status, 'proposed'), lte(schema.treatmentPlans.createdAt, sevenDaysAgo))),

git add server/src/routes/stats.ts
git commit -m "feat(stats): dental owner-dashboard sections on /stats/admin"

  // ===== Owner dashboard (dental) — additive =====
  today?: {
    appointments: number; scheduled: number; checkedIn: number;
    completed: number; cancelled: number; noShows: number;
  };
  collections?: { today: number; month: number; pending: number };
  operations?: {
    chairUtilization: number | null;
    chairUtilizationDetail: { bookedMinutes: number; capacityMinutes: number; activeChairs: number; openMinutes: number } | null;
    treatmentAcceptance: number | null;
    treatmentAcceptanceDetail: { accepted: number; proposed: number; total: number; windowDays: number };
    recallDue: number;
    newPatientsThisMonth: number;
  };
  schedule?: Array<{
    id: string; patient: string; doctor: string; time: string;
    treatmentType: string;
    status: string;
    paymentStatus: 'paid' | 'pending' | 'none';
  }>;
  revenueByTreatment?: Array<{ category: string; amount: number }>;
  weeklyAppointments?: Array<{ day: string; booked: number; cancelled: number; noShows: number; capacity: number | null }>;
  actionItems?: {
    recallsOverdue: number; unacceptedPlans: number;
    pendingPayments: number; pendingPaymentsAmount: number;
    labCasesDue: number; missedToCallback: number; inventoryLowStock: number;
  };
  providers?: Array<{
    id: string; name: string; patientsSeen: number; appointments: number;
    revenue: number; plansProposed: number; acceptanceRate: number | null;
  }>;

git add ui/src/lib/serverComm.ts
git commit -m "feat(ui): extend AdminStats with dental dashboard fields"

  adminDashboard: {
    summary: () => ['admin-dashboard', clinicScope()] as const,
  },

// ui/src/components/dashboard-widgets/dental/CssBar.tsx
import { cn } from '@/lib/utils';
import { ReactNode } from 'react';

/** Horizontal labelled bar (for revenue-by-treatment). Pure CSS, no chart lib. */
export function CssBarRow({ label, valueLabel, ratio, accent = 'primary' }: {
  label: string; valueLabel: string; ratio: number; accent?: 'primary' | 'green' | 'amber';
}) {
  const pct = Math.max(2, Math.min(100, Math.round(ratio * 100)));
  const bg = accent === 'green' ? 'bg-green-500/80' : accent === 'amber' ? 'bg-amber-500/80' : 'bg-primary/80';
  return (
    <div className="space-y-1">
      <div className="flex items-center justify-between text-xs">
        <span className="truncate text-foreground/80">{label}</span>
        <span className="font-medium tabular-nums">{valueLabel}</span>
      </div>
      <div className="h-2 w-full rounded-full bg-muted">
        <div className={cn('h-2 rounded-full transition-[width]', bg)} style={{ width: `${pct}%` }} />
      </div>
    </div>
  );
}

/** Vertical column for weekly-load (booked vs capacity). */
export function CssColumn({ heightPct, capacityPct, label, sub }: {
  heightPct: number; capacityPct?: number | null; label: string; sub?: ReactNode;
}) {
  return (
    <div className="flex flex-1 flex-col items-center gap-1">
      <div className="relative flex h-24 w-full items-end justify-center">
        {capacityPct != null && (
          <div className="absolute bottom-0 w-6 rounded-t bg-muted" style={{ height: `${Math.max(2, Math.min(100, capacityPct))}%` }} />
        )}
        <div className="relative w-6 rounded-t bg-primary/80" style={{ height: `${Math.max(2, Math.min(100, heightPct))}%` }} />
      </div>
      <span className="text-[11px] text-muted-foreground">{label}</span>
      {sub}
    </div>
  );
}

/** Compact inline stat chip for funnel/footer rows. */
export function StatChip({ icon, label, value, tone = 'default' }: {
  icon?: ReactNode; label: string; value: ReactNode; tone?: 'default' | 'green' | 'amber' | 'red';
}) {
  const toneCls = tone === 'green' ? 'text-green-600 dark:text-green-500'
    : tone === 'amber' ? 'text-amber-600 dark:text-amber-500'
    : tone === 'red' ? 'text-red-600 dark:text-red-500' : 'text-muted-foreground';
  return (
    <span className="inline-flex items-center gap-1 text-xs">
      {icon}
      <span className={cn('font-semibold tabular-nums', toneCls)}>{value}</span>
      <span className="text-muted-foreground">{label}</span>
    </span>
  );
}

git add ui/src/lib/queryKeys.ts ui/src/components/dashboard-widgets/dental/CssBar.tsx
git commit -m "feat(ui): dashboard query key + CSS bar primitives"

import { Card, CardContent, CardHeader, CardTitle, CardDescription } from '@/components/ui/card';
import { formatCurrency, formatCompactCurrency } from '@/lib/currency';
import { cn } from '@/lib/utils';
import type { AdminStats } from '@/lib/serverComm';

import { Calendar, UserCheck, CheckCircle2, XCircle, Ban } from 'lucide-react';
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card';
import { StatChip } from './CssBar';
import type { AdminStats } from '@/lib/serverComm';

export function TodayFlowCard({ today }: { today?: AdminStats['today'] }) {
  const t = today ?? { appointments: 0, scheduled: 0, checkedIn: 0, completed: 0, cancelled: 0, noShows: 0 };
  return (
    <Card>
      <CardHeader className="flex flex-row items-center justify-between space-y-0 pb-2">
        <CardTitle className="text-sm font-medium text-muted-foreground">Today's Appointments</CardTitle>
        <Calendar className="h-4 w-4 text-muted-foreground" />
      </CardHeader>
      <CardContent>
        <div className="text-2xl font-bold leading-tight">{t.appointments}</div>
        <div className="mt-2 flex flex-wrap gap-x-3 gap-y-1">
          <StatChip icon={<UserCheck className="h-3 w-3" />} value={t.checkedIn} label="in chair" />
          <StatChip icon={<CheckCircle2 className="h-3 w-3" />} value={t.completed} label="done" tone="green" />
          <StatChip icon={<XCircle className="h-3 w-3" />} value={t.cancelled} label="cxl" tone="amber" />
          <StatChip icon={<Ban className="h-3 w-3" />} value={t.noShows} label="no-show" tone="red" />
        </div>
      </CardContent>
    </Card>
  );
}

import { Banknote } from 'lucide-react';
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card';
import { formatCurrency } from '@/lib/currency';
import type { AdminStats } from '@/lib/serverComm';

export function CollectionsCard({ collections }: { collections?: AdminStats['collections'] }) {
  const c = collections ?? { today: 0, month: 0, pending: 0 };
  return (
    <Card>
      <CardHeader className="flex flex-row items-center justify-between space-y-0 pb-2">
        <CardTitle className="text-sm font-medium text-muted-foreground">Today's Collections</CardTitle>
        <Banknote className="h-4 w-4 text-muted-foreground" />
      </CardHeader>
      <CardContent>
        <div className="text-2xl font-bold leading-tight">{formatCurrency(c.today, { decimals: 0 })}</div>
        <p className="mt-1 text-xs text-muted-foreground">
          <span className="font-medium text-amber-600 dark:text-amber-500">{formatCurrency(c.pending, { decimals: 0 })}</span> pending
        </p>
      </CardContent>
    </Card>
  );
}

import { Armchair } from 'lucide-react';
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card';
import { cn } from '@/lib/utils';
import type { AdminStats } from '@/lib/serverComm';

export function ChairUtilizationCard({ operations }: { operations?: AdminStats['operations'] }) {
  const util = operations?.chairUtilization ?? null;
  const detail = operations?.chairUtilizationDetail ?? null;
  return (
    <Card>
      <CardHeader className="flex flex-row items-center justify-between space-y-0 pb-2">
        <CardTitle className="text-sm font-medium text-muted-foreground">Chair Utilization</CardTitle>
        <Armchair className="h-4 w-4 text-muted-foreground" />
      </CardHeader>
      <CardContent>
        {util == null ? (
          <>
            <div className="text-2xl font-bold leading-tight text-muted-foreground">—</div>
            <p className="mt-1 text-xs text-muted-foreground">Add chairs &amp; opening hours to track</p>
          </>
        ) : (
          <>
            <div className="text-2xl font-bold leading-tight">{util}%</div>
            <div className="mt-2 h-2 w-full rounded-full bg-muted">
              <div className={cn('h-2 rounded-full', util >= 75 ? 'bg-green-500/80' : util >= 40 ? 'bg-primary/80' : 'bg-amber-500/80')} style={{ width: `${util}%` }} />
            </div>
            {detail && (
              <p className="mt-1 text-xs text-muted-foreground">
                {Math.round(detail.bookedMinutes / 60)}h of {Math.round(detail.capacityMinutes / 60)}h chair-time
              </p>
            )}
          </>
        )}
      </CardContent>
    </Card>
  );
}

import { Ban } from 'lucide-react';
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card';
import { cn } from '@/lib/utils';
import type { AdminStats } from '@/lib/serverComm';

export function NoShowCard({ today }: { today?: AdminStats['today'] }) {
  const t = today ?? { appointments: 0, noShows: 0 } as NonNullable<AdminStats['today']>;
  const rate = t.appointments > 0 ? Math.round((t.noShows / t.appointments) * 100) : 0;
  return (
    <Card>
      <CardHeader className="flex flex-row items-center justify-between space-y-0 pb-2">
        <CardTitle className="text-sm font-medium text-muted-foreground">No-show Rate</CardTitle>
        <Ban className="h-4 w-4 text-muted-foreground" />
      </CardHeader>
      <CardContent>
        <div className={cn('text-2xl font-bold leading-tight', rate > 0 ? 'text-red-600 dark:text-red-500' : '')}>{rate}%</div>
        <p className="mt-1 text-xs text-muted-foreground">{t.noShows} of {t.appointments} today</p>
      </CardContent>
    </Card>
  );
}

import { ClipboardCheck } from 'lucide-react';
import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card';
import type { AdminStats } from '@/lib/serverComm';

export function AcceptanceCard({ operations }: { operations?: AdminStats['operations'] }) {
  const rate = operations?.treatmentAcceptance ?? null;
  const d = operations?.treatmentAcceptanceDetail;
  return (
    <Card>
      <CardHeader className="flex flex-row items-center justify-between space-y-0 pb-2">
        <CardTitle className="text-sm font-medium text-muted-foreground">Treatment Acceptance</CardTitle>
        <ClipboardCheck className="h-4 w-4 text-muted-foreground" />
      </CardHeader>
      <CardContent>
        {rate == null ? (
          <>
            <div className="text-2xl font-bold leading-tight text-muted-foreground">—</div>
            <p className="mt-1 text-xs text-muted-foreground">No treatment plans in last 90 days</p>
          </>
        ) : (
          <>
            <div className="text-2xl font-bold leading-tight">{rate}%</div>
            <p className="mt-1 text-xs text-muted-foreground">{d?.accepted ?? 0} of {d?.total ?? 0} plans (90d)</p>
          </>
        )}
      </CardContent>
    </Card>
  );
}

import { CalendarDays, ArrowUpRight, Clock } from 'lucide-react';
import { Card, CardContent, CardHeader, CardTitle, CardDescription } from '@/components/ui/card';
import { Badge } from '@/components/ui/badge';
import { Button } from '@/components/ui/button';
import { cn } from '@/lib/utils';
import type { AdminStats } from '@/lib/serverComm';

const STATUS_LABEL: Record<string, string> = {
  scheduled: 'Scheduled', confirmed: 'Waiting', in_progress: 'In chair',
  completed: 'Completed', cancelled: 'Cancelled', no_show: 'No-show', missed: 'Missed',
};
const STATUS_VARIANT: Record<string, 'default' | 'secondary' | 'outline' | 'destructive'> = {
  scheduled: 'outline', confirmed: 'default', in_progress: 'default',
  completed: 'secondary', cancelled: 'destructive', no_show: 'destructive', missed: 'destructive',
};

export function TodayScheduleCard({ schedule, onViewAll }: { schedule?: AdminStats['schedule']; onViewAll?: () => void }) {
  const rows = schedule ?? [];
  return (
    <Card className="h-full">
      <CardHeader className="flex flex-row items-center justify-between">
        <div className="space-y-1">
          <CardTitle className="text-base font-semibold">Today's Schedule</CardTitle>
          <CardDescription className="text-xs">{rows.length ? `Next ${rows.length} patients` : 'Patient flow'}</CardDescription>
        </div>
        {onViewAll && <Button variant="outline" size="sm" className="gap-1.5" onClick={onViewAll}>View all <ArrowUpRight className="h-3.5 w-3.5" /></Button>}
      </CardHeader>
      <CardContent>
        {rows.length === 0 ? (
          <div className="flex flex-col items-center justify-center py-10 text-center text-muted-foreground">
            <CalendarDays className="mb-3 h-9 w-9 text-muted-foreground/40" />
            <p className="text-sm font-medium">No appointments today</p>
            <p className="text-xs">Book an appointment to get started.</p>
          </div>
        ) : (
          <ul className="divide-y divide-border">
            {rows.map(r => (
              <li key={r.id} className="flex items-center gap-3 py-2.5">
                <div className="flex w-14 shrink-0 items-center gap-1 text-xs font-medium text-muted-foreground">
                  <Clock className="h-3 w-3" />{r.time?.slice(0, 5)}
                </div>
                <div className="min-w-0 flex-1">
                  <div className="truncate text-sm font-medium">{r.patient}</div>
                  <div className="truncate text-xs text-muted-foreground">{r.treatmentType || '—'} · {r.doctor}</div>
                </div>
                <span className={cn('h-2 w-2 shrink-0 rounded-full',
                  r.paymentStatus === 'paid' ? 'bg-green-500' : r.paymentStatus === 'pending' ? 'bg-amber-500' : 'bg-muted-foreground/30')}
                  title={`Payment: ${r.paymentStatus}`} />
                <Badge variant={STATUS_VARIANT[r.status] ?? 'outline'} className="shrink-0 font-normal">{STATUS_LABEL[r.status] ?? r.status}</Badge>
              </li>
            ))}
          </ul>
        )}
      </CardContent>
    </Card>
  );
}