Wave 1 (parallel) :  Task 1 · Task 2
Wave 2 (sequential): Task 3 · Task 4 · Task 5
Wave 3 (parallel) :  Task 6 · Task 7
Wave 4 (sequential): Task 8 · Task 9
Wave 5 (parallel) :  Task 10 · Task 11 · Task 12 · Task 13
Wave 6 (sequential): Task 14 · Task 15

CREATE TABLE IF NOT EXISTS whatsapp_events (
  id            TEXT PRIMARY KEY,
  clinic_id     TEXT NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  patient_id    TEXT REFERENCES patients(id) ON DELETE SET NULL,
  message_id    TEXT REFERENCES messages(id) ON DELETE SET NULL,
  type          TEXT NOT NULL,
  category      TEXT,
  template_name TEXT,
  cost_pkr      NUMERIC(10,4) NOT NULL DEFAULT 0,
  metadata      JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_wae_clinic_created  ON whatsapp_events (clinic_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_wae_clinic_patient  ON whatsapp_events (clinic_id, patient_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_wae_clinic_type     ON whatsapp_events (clinic_id, type, created_at DESC);

CREATE TABLE IF NOT EXISTS whatsapp_quick_replies (
  id          TEXT PRIMARY KEY,
  clinic_id   TEXT NOT NULL REFERENCES clinics(id) ON DELETE CASCADE,
  shortcut    TEXT NOT NULL,
  body        TEXT NOT NULL,
  position    INTEGER NOT NULL DEFAULT 0,
  created_by  TEXT REFERENCES users(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (clinic_id, shortcut)
);

CREATE INDEX IF NOT EXISTS idx_wqr_clinic_position ON whatsapp_quick_replies (clinic_id, position);

import { pgTable, text, numeric, jsonb, timestamp, index } from 'drizzle-orm/pg-core';
import { clinics } from './clinics';
import { patients } from './patients';
import { messages } from './messages';

export const whatsappEvents = pgTable('whatsapp_events', {
  id:           text('id').primaryKey(),
  clinicId:     text('clinic_id').notNull().references(() => clinics.id, { onDelete: 'cascade' }),
  patientId:    text('patient_id').references(() => patients.id, { onDelete: 'set null' }),
  messageId:    text('message_id').references(() => messages.id, { onDelete: 'set null' }),
  type:         text('type').notNull(),  // inbound_message | outbound_message | template_sent | delivered | read | failed | window_opened | window_closed | free_entry
  category:     text('category'),         // marketing | utility | authentication | auth_international | service | null
  templateName: text('template_name'),
  costPkr:      numeric('cost_pkr', { precision: 10, scale: 4 }).notNull().default('0'),
  metadata:     jsonb('metadata').notNull().default({}),
  createdAt:    timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  byClinicCreated: index('idx_wae_clinic_created').on(t.clinicId, t.createdAt),
  byClinicPatient: index('idx_wae_clinic_patient').on(t.clinicId, t.patientId, t.createdAt),
  byClinicType:    index('idx_wae_clinic_type').on(t.clinicId, t.type, t.createdAt),
}));

export type WhatsappEvent = typeof whatsappEvents.$inferSelect;
export type NewWhatsappEvent = typeof whatsappEvents.$inferInsert;

import { pgTable, text, integer, timestamp, unique, index } from 'drizzle-orm/pg-core';
import { clinics } from './clinics';
import { users } from './users';

export const whatsappQuickReplies = pgTable('whatsapp_quick_replies', {
  id:        text('id').primaryKey(),
  clinicId:  text('clinic_id').notNull().references(() => clinics.id, { onDelete: 'cascade' }),
  shortcut:  text('shortcut').notNull(),
  body:      text('body').notNull(),
  position:  integer('position').notNull().default(0),
  createdBy: text('created_by').references(() => users.id, { onDelete: 'set null' }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
}, (t) => ({
  uniqShortcut: unique('uq_wqr_clinic_shortcut').on(t.clinicId, t.shortcut),
  byClinicPos:  index('idx_wqr_clinic_position').on(t.clinicId, t.position),
}));

export type WhatsappQuickReply = typeof whatsappQuickReplies.$inferSelect;
export type NewWhatsappQuickReply = typeof whatsappQuickReplies.$inferInsert;

export * from './whatsapp_events';
export * from './whatsapp_quick_replies';

cd server && npx wrangler d1 --remote-no-thx execute || \
  npx drizzle-kit push --config drizzle.config.ts

cd server && npx tsc --noEmit 2>&1 | grep -E "whatsapp_(events|quick_replies)"

git add server/drizzle/0034_whatsapp_module.sql server/src/schema/whatsapp_events.ts server/src/schema/whatsapp_quick_replies.ts server/src/schema/index.ts
git commit -m "feat(whatsapp): migration 0034 — events + quick_replies tables"

// Last verified against developers.facebook.com/.../pricing on 2026-05-13.
// PKR amounts derived from Meta's USD rates × 285 PKR/USD baseline.
// Pakistan rates increased on 2026-04-01 (utility/auth $0.0054 → $0.01).
//
// Superadmin can override per-clinic via clinic_modules.config.rateCardOverride.

export const RATE_CARD_LAST_UPDATED = '2026-05-13';

export type WaCategory = 'marketing' | 'utility' | 'authentication' | 'auth_international' | 'service';
export type WaCountry = 'PK' | 'IN' | 'AE' | 'SA' | 'BD' | 'US' | 'GB' | 'DEFAULT';

const RATE_CARD_PKR: Record<WaCountry, Record<WaCategory, number>> = {
  PK:      { marketing: 13.50, utility: 2.85,  authentication: 2.85,  auth_international: 21.0, service: 0 },
  IN:      { marketing:  3.80, utility: 0.45,  authentication: 0.45,  auth_international: 14.0, service: 0 },
  AE:      { marketing: 10.20, utility: 2.30,  authentication: 2.30,  auth_international: 18.0, service: 0 },
  SA:      { marketing:  9.40, utility: 2.10,  authentication: 2.10,  auth_international: 17.0, service: 0 },
  BD:      { marketing:  3.50, utility: 0.40,  authentication: 0.40,  auth_international: 13.0, service: 0 },
  US:      { marketing:  7.40, utility: 1.80,  authentication: 1.80,  auth_international:  9.0, service: 0 },
  GB:      { marketing:  6.20, utility: 1.50,  authentication: 1.50,  auth_international:  7.5, service: 0 },
  DEFAULT: { marketing:  7.00, utility: 1.50,  authentication: 1.50,  auth_international: 10.0, service: 0 },
};

export interface PriceArgs {
  category: WaCategory;
  country: WaCountry;
  insideWindow: boolean;
  override?: Partial<Record<WaCategory, number>>;
}

/**
 * Returns the PKR cost the clinic owes Meta for a single delivered message.
 * Inside-window utility templates and all service messages are free since
 * Meta's 2025-07-01 update.
 */
export function priceForMessage({ category, country, insideWindow, override }: PriceArgs): number {
  if (category === 'service') return 0;
  if (category === 'utility' && insideWindow) return 0;
  if (override?.[category] !== undefined) return override[category]!;
  return RATE_CARD_PKR[country]?.[category] ?? RATE_CARD_PKR.DEFAULT[category];
}

/**
 * 24h customer service window state derived from the most recent inbound
 * message timestamp. `free-entry` is set by the caller if the conversation
 * began from a WhatsApp ad / FB CTA (webhook `referral` field present).
 */
export type WindowStatus = 'green' | 'amber' | 'red' | 'free-entry';

export interface WindowState {
  status: WindowStatus;
  opensAt: string | null;     // ISO
  closesAt: string | null;    // ISO
  secondsLeft: number;        // 0 if closed
}

export function computeWindowState(
  lastInboundAt: Date | null,
  now: Date = new Date(),
  freeEntry: boolean = false,
): WindowState {
  if (!lastInboundAt) {
    return { status: 'red', opensAt: null, closesAt: null, secondsLeft: 0 };
  }
  const windowMs = freeEntry ? 72 * 60 * 60 * 1000 : 24 * 60 * 60 * 1000;
  const closesAt = new Date(lastInboundAt.getTime() + windowMs);
  const secondsLeft = Math.max(0, Math.floor((closesAt.getTime() - now.getTime()) / 1000));
  if (secondsLeft <= 0) return { status: 'red', opensAt: lastInboundAt.toISOString(), closesAt: closesAt.toISOString(), secondsLeft: 0 };
  if (freeEntry) return { status: 'free-entry', opensAt: lastInboundAt.toISOString(), closesAt: closesAt.toISOString(), secondsLeft };
  if (secondsLeft <= 2 * 60 * 60) return { status: 'amber', opensAt: lastInboundAt.toISOString(), closesAt: closesAt.toISOString(), secondsLeft };
  return { status: 'green', opensAt: lastInboundAt.toISOString(), closesAt: closesAt.toISOString(), secondsLeft };
}

cd server && npx tsx -e "
import { priceForMessage, computeWindowState } from './src/lib/whatsapp-pricing';
console.assert(priceForMessage({ category: 'service', country: 'PK', insideWindow: false }) === 0, 'service=0');
console.assert(priceForMessage({ category: 'utility', country: 'PK', insideWindow: true }) === 0, 'utility-in-window=0');
console.assert(priceForMessage({ category: 'utility', country: 'PK', insideWindow: false }) === 2.85, 'utility-out=2.85');
console.assert(priceForMessage({ category: 'marketing', country: 'PK', insideWindow: true }) === 13.50, 'marketing=13.50');
const now = new Date('2026-05-13T12:00:00Z');
const s1 = computeWindowState(new Date('2026-05-13T00:00:00Z'), now);
console.assert(s1.status === 'green' && s1.secondsLeft === 12*3600, 'green @12h');
const s2 = computeWindowState(new Date('2026-05-12T23:30:00Z'), now);
console.assert(s2.status === 'amber', 'amber when <2h left');
const s3 = computeWindowState(new Date('2026-05-11T00:00:00Z'), now);
console.assert(s3.status === 'red', 'red after 24h');
console.assert(computeWindowState(null, now).status === 'red', 'red when no inbound');
const s4 = computeWindowState(new Date('2026-05-13T00:00:00Z'), now, true);
console.assert(s4.status === 'free-entry', 'free-entry on referral');
console.log('OK');
"

git add server/src/lib/whatsapp-pricing.ts
git commit -m "feat(whatsapp): rate card + 24h window state computation"

import { whatsappEvents } from '../schema/whatsapp_events';
import { priceForMessage, computeWindowState, type WaCategory } from '../lib/whatsapp-pricing';
import { getDatabase } from '../lib/db';

async function writeWhatsappEvent(args: {
  clinicId: string;
  patientId: string | null;
  messageId: string | null;
  type: 'inbound_message' | 'outbound_message' | 'template_sent' | 'delivered' | 'read' | 'failed' | 'window_opened' | 'window_closed' | 'free_entry';
  category?: WaCategory;
  templateName?: string;
  costPkr?: number;
  metadata?: Record<string, unknown>;
}): Promise<void> {
  const db = await getDatabase();
  await db.insert(whatsappEvents).values({
    id: crypto.randomUUID(),
    clinicId: args.clinicId,
    patientId: args.patientId,
    messageId: args.messageId,
    type: args.type,
    category: args.category ?? null,
    templateName: args.templateName ?? null,
    costPkr: String(args.costPkr ?? 0),
    metadata: args.metadata ?? {},
  });
}

// Detect if this opens a new 24h window: no inbound in the prior 24h?
const priorInboundCutoff = new Date(Date.now() - 24 * 60 * 60 * 1000);
const [priorInbound] = await db
  .select({ id: messages.id })
  .from(messages)
  .where(and(
    eq(messages.clinicId, clinicId),
    eq(messages.patientId, patientId),
    eq(messages.direction, 'inbound'),
    gte(messages.createdAt, priorInboundCutoff),
  ))
  .orderBy(desc(messages.createdAt))
  .limit(2);  // 2 because the row we just inserted is one of them

const isNewWindow = priorInbound.length <= 1;

await writeWhatsappEvent({
  clinicId,
  patientId,
  messageId: newMessageId,
  type: 'inbound_message',
});

if (isNewWindow) {
  await writeWhatsappEvent({
    clinicId,
    patientId,
    messageId: newMessageId,
    type: 'window_opened',
  });
}

// Free-entry detection — Meta passes referral in the webhook payload for
// chats started via WhatsApp ads / FB CTA buttons.
const referral = msg.referral;  // already in scope from webhook payload destructure
if (referral) {
  await writeWhatsappEvent({
    clinicId,
    patientId,
    messageId: newMessageId,
    type: 'free_entry',
    metadata: { source: referral.source_type, headline: referral.headline, body: referral.body },
  });
}

// Resolve the original outbound message to figure out cost.
const [origMsg] = await db
  .select({
    id: messages.id,
    patientId: messages.patientId,
    templateName: messages.templateName,
    category: messages.category,  // if column exists; else null
  })
  .from(messages)
  .where(and(eq(messages.clinicId, clinicId), eq(messages.providerMessageId, status.id)))
  .limit(1);

if (status.status === 'delivered' && origMsg) {
  // Compute cost from the original send — was it inside-window at send time?
  // For v1 we recompute: if there was an inbound from the same patient in the
  // 24h before the send timestamp (now − ~status delay), we treat as in-window.
  const sentInWindowCutoff = new Date(Date.now() - 24 * 60 * 60 * 1000);
  const [recentInbound] = await db
    .select({ id: messages.id })
    .from(messages)
    .where(and(
      eq(messages.clinicId, clinicId),
      eq(messages.patientId, origMsg.patientId),
      eq(messages.direction, 'inbound'),
      gte(messages.createdAt, sentInWindowCutoff),
    ))
    .limit(1);
  const insideWindow = !!recentInbound;

  const category = (origMsg.category as WaCategory | null) ?? (origMsg.templateName ? 'utility' : 'service');
  const cost = priceForMessage({ category, country: 'PK', insideWindow });

  await writeWhatsappEvent({
    clinicId,
    patientId: origMsg.patientId,
    messageId: origMsg.id,
    type: 'delivered',
    category,
    templateName: origMsg.templateName ?? undefined,
    costPkr: cost,
  });
} else if (status.status === 'read' && origMsg) {
  await writeWhatsappEvent({
    clinicId,
    patientId: origMsg.patientId,
    messageId: origMsg.id,
    type: 'read',
  });
} else if (status.status === 'failed' && origMsg) {
  await writeWhatsappEvent({
    clinicId,
    patientId: origMsg.patientId,
    messageId: origMsg.id,
    type: 'failed',
    metadata: { errors: status.errors ?? [] },
  });
}

cd server && npx tsc --noEmit 2>&1 | grep "whatsapp-webhook"

git add server/src/routes/whatsapp-webhook.ts
git commit -m "feat(whatsapp): write events on inbound, delivery, read, failed"

await writeWhatsappEvent({
  clinicId: options.clinicId,
  patientId: options.patientId ?? null,
  messageId: newMessageId,
  type: 'outbound_message',
  category: 'service',  // free-form text is always 'service' category
});

const category = inferTemplateCategory(options.templateName);
await writeWhatsappEvent({
  clinicId: options.clinicId,
  patientId: options.patientId ?? null,
  messageId: newMessageId,
  type: 'template_sent',
  category,
  templateName: options.templateName,
  // Cost not booked here — booked on delivery webhook.
});

function inferTemplateCategory(templateName: string): WaCategory {
  // Map known appointment templates → utility. Default unknown → marketing
  // (conservative — overcharges in the cost preview rather than undercharges).
  const utilityNames = new Set([
    'appoitmentconf',
    'appointmentremd',
    'appointment_cancellation_1',
    'appointment_reschedule',
    'missed_appointment',
  ]);
  if (utilityNames.has(templateName)) return 'utility';
  return 'marketing';
}

import { eq, and, sql, gte, lt, isNull, notExists, exists } from 'drizzle-orm';
import { getReadDb, getDatabase } from '../lib/db';
import { getDatabaseUrl, runWithEnv } from '../lib/env';
import { messages } from '../schema/messages';
import { whatsappEvents } from '../schema/whatsapp_events';
import { clinicModules } from '../schema/clinic_modules';

/**
 * Writes a `window_closed` event for every (clinic, patient) thread whose
 * last inbound message just crossed the 24h mark, AND for which we haven't
 * already written a `window_closed` event after that inbound.
 *
 * Runs on the existing 5x/day cron. Idempotent — running it twice in the
 * same day yields zero extra rows because the NOT EXISTS guard prevents
 * duplicates.
 */
export async function handleWhatsappWindowCloser(env: any): Promise<void> {
  return runWithEnv(env, async () => {
    const db = await getDatabase();

    // For each enabled WhatsApp clinic, find inbound messages where:
    //   created_at < now - 24h  AND
    //   created_at >= now - 25h  (only catch the boundary crossing — older
    //                              ones were caught on previous ticks)
    //   no later inbound from same patient
    //   no window_closed event referencing this message
    const result = await db.execute(sql`
      INSERT INTO whatsapp_events (id, clinic_id, patient_id, message_id, type, created_at)
      SELECT
        gen_random_uuid()::text,
        m.clinic_id,
        m.patient_id,
        m.id,
        'window_closed',
        NOW()
      FROM messages m
      JOIN clinic_modules cm
        ON cm.clinic_id = m.clinic_id
       AND cm.module_key = 'whatsapp'
       AND cm.is_enabled = true
      WHERE m.direction = 'inbound'
        AND m.type = 'whatsapp'
        AND m.created_at <  NOW() - INTERVAL '24 hours'
        AND m.created_at >= NOW() - INTERVAL '25 hours'
        AND NOT EXISTS (
          SELECT 1 FROM messages m2
          WHERE m2.clinic_id = m.clinic_id
            AND m2.patient_id = m.patient_id
            AND m2.direction = 'inbound'
            AND m2.type = 'whatsapp'
            AND m2.created_at > m.created_at
        )
        AND NOT EXISTS (
          SELECT 1 FROM whatsapp_events e
          WHERE e.message_id = m.id
            AND e.type = 'window_closed'
        )
    `);
    console.log('[whatsapp] window_closed events written:', result.rowCount ?? 0);
  });
}

import { handleWhatsappWindowCloser } from './scheduled/whatsapp-window-closer';

// 9. WHATSAPP WINDOW CLOSER — writes `window_closed` events for threads
//    whose 24h window just expired. Runs on every cron tick (cheap query).
await handleWhatsappWindowCloser(env);

cd server && npx tsc --noEmit 2>&1 | grep -E "whatsapp\.ts|scheduled\.ts|window-closer"

git add server/src/lib/whatsapp.ts server/src/scheduled.ts server/src/scheduled/whatsapp-window-closer.ts
git commit -m "feat(whatsapp): outbound + window_closed event writes"

import { Hono } from 'hono';
import { z } from 'zod';
import { and, asc, desc, eq, gte, lte, inArray, sql } from 'drizzle-orm';
import { getReadDb, getDatabase } from '../lib/db';
import { messages, patients, clinicModules, appointments } from '../schema';
import { whatsappEvents } from '../schema/whatsapp_events';
import { whatsappQuickReplies } from '../schema/whatsapp_quick_replies';
import { requirePermission } from '../middleware/permissions';
import { handleError, AppError } from '../lib/errors';
import { decryptPatientPHI } from '../lib/encryption';
import {
  computeWindowState,
  priceForMessage,
  RATE_CARD_LAST_UPDATED,
  type WaCategory,
} from '../lib/whatsapp-pricing';
import {
  sendTextMessage,
  sendTemplateMessage,
  getWhatsAppConfigForClinic,
} from '../lib/whatsapp';

const whatsappRoute = new Hono();
whatsappRoute.use('*', requirePermission('comms.whatsapp.module.access'));

// Helper — apply doctor scope: returns patient_id list the user is allowed to see.
async function scopedPatientIds(c: any): Promise<string[] | 'all'> {
  const user = c.get('user');
  const clinicId = c.get('clinicContext')?.currentClinicId ?? user.clinicId;
  if (user.role !== 'doctor') return 'all';
  const db = getReadDb();
  const rows = await db
    .selectDistinct({ patientId: appointments.patientId })
    .from(appointments)
    .where(and(eq(appointments.clinicId, clinicId), eq(appointments.doctorId, user.id)));
  return rows.map(r => r.patientId).filter(Boolean) as string[];
}

function clinicId(c: any): string {
  return c.get('clinicContext')?.currentClinicId ?? c.get('user').clinicId;
}

// GET /conversations — threads list with last-message preview + window state
whatsappRoute.get('/conversations', async (c) => {
  try {
    const cid = clinicId(c);
    const scoped = await scopedPatientIds(c);
    const db = getReadDb();

    // Latest message per patient
    const rows = await db.execute(sql`
      SELECT DISTINCT ON (m.patient_id)
        m.patient_id  AS patient_id,
        m.id          AS last_message_id,
        m.content     AS last_message_preview,
        m.direction   AS last_direction,
        m.status      AS last_status,
        m.created_at  AS last_created_at,
        (SELECT MAX(created_at) FROM messages mi
           WHERE mi.clinic_id = m.clinic_id
             AND mi.patient_id = m.patient_id
             AND mi.direction = 'inbound'
             AND mi.type = 'whatsapp') AS last_inbound_at,
        (SELECT COUNT(*) FROM messages mu
           WHERE mu.clinic_id = m.clinic_id
             AND mu.patient_id = m.patient_id
             AND mu.direction = 'inbound'
             AND mu.status = 'unread') AS unread_count
      FROM messages m
      WHERE m.clinic_id = ${cid}
        AND m.type = 'whatsapp'
        ${scoped === 'all' ? sql`` : sql`AND m.patient_id = ANY(${scoped})`}
      ORDER BY m.patient_id, m.created_at DESC
    `);

    // Hydrate patient names + sort by last_created_at desc
    const patientIds = (rows.rows as any[]).map(r => r.patient_id).filter(Boolean);
    const patientRows = patientIds.length
      ? await db.select().from(patients).where(inArray(patients.id, patientIds))
      : [];
    const byId = new Map(patientRows.map(p => [p.id, decryptPatientPHI(p)]));

    const threads = (rows.rows as any[]).map(r => {
      const p = byId.get(r.patient_id);
      const window = computeWindowState(r.last_inbound_at ? new Date(r.last_inbound_at) : null);
      return {
        patientId: r.patient_id,
        patientName: p ? `${p.firstName} ${p.lastName}` : 'Unknown',
        patientNumber: p?.patientNumber ?? '',
        lastMessage: {
          id: r.last_message_id,
          preview: (r.last_message_preview ?? '').slice(0, 80),
          direction: r.last_direction,
          status: r.last_status,
          createdAt: r.last_created_at,
        },
        unreadCount: Number(r.unread_count ?? 0),
        window,
      };
    }).sort((a, b) => new Date(b.lastMessage.createdAt).getTime() - new Date(a.lastMessage.createdAt).getTime());

    return c.json({ threads });
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /conversations/:patientId/messages — full thread history
whatsappRoute.get('/conversations/:patientId/messages', async (c) => {
  try {
    const cid = clinicId(c);
    const patientId = c.req.param('patientId');
    const scoped = await scopedPatientIds(c);
    if (scoped !== 'all' && !scoped.includes(patientId)) {
      throw new AppError(403, 'Patient out of doctor scope');
    }
    const db = getReadDb();
    const msgs = await db
      .select()
      .from(messages)
      .where(and(
        eq(messages.clinicId, cid),
        eq(messages.patientId, patientId),
        eq(messages.type, 'whatsapp'),
      ))
      .orderBy(asc(messages.createdAt))
      .limit(500);
    return c.json({ messages: msgs });
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /conversations/:patientId/window — window state for one thread
whatsappRoute.get('/conversations/:patientId/window', async (c) => {
  try {
    const cid = clinicId(c);
    const patientId = c.req.param('patientId');
    const db = getReadDb();
    const [latest] = await db
      .select({ createdAt: messages.createdAt })
      .from(messages)
      .where(and(
        eq(messages.clinicId, cid),
        eq(messages.patientId, patientId),
        eq(messages.type, 'whatsapp'),
        eq(messages.direction, 'inbound'),
      ))
      .orderBy(desc(messages.createdAt))
      .limit(1);
    return c.json(computeWindowState(latest?.createdAt ?? null));
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /events — paginated activity timeline
const eventsQuery = z.object({
  patientId: z.string().uuid().optional(),
  type: z.string().optional(),
  from: z.string().datetime().optional(),
  to: z.string().datetime().optional(),
  cursor: z.string().optional(),
  limit: z.coerce.number().min(1).max(100).default(50),
});

whatsappRoute.get('/events', async (c) => {
  try {
    const cid = clinicId(c);
    const scoped = await scopedPatientIds(c);
    const q = eventsQuery.parse(Object.fromEntries(new URL(c.req.url).searchParams));
    const db = getReadDb();
    const where = [eq(whatsappEvents.clinicId, cid)];
    if (q.patientId) where.push(eq(whatsappEvents.patientId, q.patientId));
    if (q.type) where.push(eq(whatsappEvents.type, q.type));
    if (q.from) where.push(gte(whatsappEvents.createdAt, new Date(q.from)));
    if (q.to)   where.push(lte(whatsappEvents.createdAt, new Date(q.to)));
    if (scoped !== 'all') where.push(inArray(whatsappEvents.patientId, scoped));
    if (q.cursor) where.push(lte(whatsappEvents.createdAt, new Date(q.cursor)));
    const rows = await db.select().from(whatsappEvents).where(and(...where)).orderBy(desc(whatsappEvents.createdAt)).limit(q.limit + 1);
    const hasMore = rows.length > q.limit;
    const items = rows.slice(0, q.limit);
    return c.json({
      events: items,
      nextCursor: hasMore ? items[items.length - 1].createdAt : null,
    });
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /templates — fetch live from Meta (cached 5 min)
whatsappRoute.get('/templates', async (c) => {
  try {
    const cid = clinicId(c);
    const cfg = await getWhatsAppConfigForClinic(cid);
    const cacheKey = `whatsapp:templates:${cid}`;
    const env = c.env as any;
    const kv = env.WHATSAPP_CACHE as KVNamespace | undefined;
    if (kv) {
      const cached = await kv.get(cacheKey, 'json');
      if (cached) return c.json({ templates: cached, cached: true });
    }
    const resp = await fetch(
      `https://graph.facebook.com/v21.0/${cfg.businessAccountId}/message_templates?limit=200`,
      { headers: { Authorization: `Bearer ${cfg.accessToken}` } },
    );
    if (!resp.ok) {
      const text = await resp.text();
      throw new AppError(502, `Meta template fetch failed: ${resp.status} ${text}`);
    }
    const body = await resp.json() as any;
    const templates = (body.data ?? []).map((t: any) => ({
      name: t.name,
      category: (t.category ?? '').toLowerCase(),  // marketing | utility | authentication
      language: t.language,
      status: t.status,  // APPROVED | PENDING | REJECTED
      components: t.components,
    }));
    if (kv) await kv.put(cacheKey, JSON.stringify(templates), { expirationTtl: 300 });
    return c.json({ templates, cached: false });
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /quick-replies
whatsappRoute.get('/quick-replies', async (c) => {
  try {
    const cid = clinicId(c);
    const db = getReadDb();
    const rows = await db.select().from(whatsappQuickReplies)
      .where(eq(whatsappQuickReplies.clinicId, cid))
      .orderBy(asc(whatsappQuickReplies.position), asc(whatsappQuickReplies.shortcut));
    return c.json({ quickReplies: rows });
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /analytics — single payload powering all Analytics-tab cards
whatsappRoute.get('/analytics', async (c) => {
  try {
    const cid = clinicId(c);
    const db = getReadDb();
    const thirtyDaysAgo = new Date(Date.now() - 30 * 86400 * 1000);

    // Aggregate cost + counts by category
    const costRows = await db.execute(sql`
      SELECT
        COALESCE(category, 'service') AS category,
        COUNT(*) FILTER (WHERE type IN ('outbound_message', 'template_sent', 'delivered')) AS sent,
        COUNT(*) FILTER (WHERE type = 'delivered') AS delivered,
        COUNT(*) FILTER (WHERE type = 'read') AS read,
        SUM(cost_pkr) AS cost
      FROM whatsapp_events
      WHERE clinic_id = ${cid} AND created_at >= ${thirtyDaysAgo.toISOString()}
      GROUP BY 1
    `);

    const monthCost = await db.execute(sql`
      SELECT SUM(cost_pkr)::numeric AS total
      FROM whatsapp_events
      WHERE clinic_id = ${cid}
        AND date_trunc('month', created_at) = date_trunc('month', NOW())
        AND type = 'delivered'
    `);

    // Busiest hours
    const hours = await db.execute(sql`
      SELECT EXTRACT(HOUR FROM created_at AT TIME ZONE 'Asia/Karachi') AS hour, COUNT(*) AS n
      FROM whatsapp_events
      WHERE clinic_id = ${cid}
        AND type IN ('inbound_message', 'outbound_message')
        AND created_at >= ${thirtyDaysAgo.toISOString()}
      GROUP BY 1 ORDER BY 1
    `);

    return c.json({
      lastUpdated: RATE_CARD_LAST_UPDATED,
      monthCostPkr: Number((monthCost.rows as any[])[0]?.total ?? 0),
      byCategory: costRows.rows,
      busiestHours: hours.rows,
    });
  } catch (err) {
    return handleError(c, err);
  }
});

// GET /cost/preview?category=&insideWindow=
whatsappRoute.get('/cost/preview', async (c) => {
  const category = (c.req.query('category') ?? 'service') as WaCategory;
  const insideWindow = c.req.query('insideWindow') === 'true';
  const cid = clinicId(c);
  const db = getReadDb();
  const [mod] = await db.select().from(clinicModules)
    .where(and(eq(clinicModules.clinicId, cid), eq(clinicModules.moduleKey, 'whatsapp')))
    .limit(1);
  const override = (() => {
    try { return mod?.config ? JSON.parse(mod.config).rateCardOverride : undefined; } catch { return undefined; }
  })();
  return c.json({
    pricePkr: priceForMessage({ category, country: 'PK', insideWindow, override }),
    currency: 'PKR',
    isFreeInWindow: category === 'service' || (category === 'utility' && insideWindow),
  });
});

const sendTextSchema = z.object({ body: z.string().min(1).max(4096) });

whatsappRoute.post('/conversations/:patientId/send', async (c) => {
  try {
    const cid = clinicId(c);
    const patientId = c.req.param('patientId');
    const { body } = sendTextSchema.parse(await c.req.json());
    const scoped = await scopedPatientIds(c);
    if (scoped !== 'all' && !scoped.includes(patientId)) throw new AppError(403, 'Patient out of doctor scope');

    // Server-side enforcement — refuse free-text outside the 24h window.
    const db = getReadDb();
    const [latest] = await db.select({ createdAt: messages.createdAt })
      .from(messages)
      .where(and(
        eq(messages.clinicId, cid),
        eq(messages.patientId, patientId),
        eq(messages.type, 'whatsapp'),
        eq(messages.direction, 'inbound'),
      ))
      .orderBy(desc(messages.createdAt))
      .limit(1);
    const state = computeWindowState(latest?.createdAt ?? null);
    if (state.status === 'red') {
      throw new AppError(409, 'Free-text window closed. Send an approved template instead.');
    }

    // Resolve patient WhatsApp phone
    const [patient] = await db.select().from(patients).where(eq(patients.id, patientId)).limit(1);
    if (!patient) throw new AppError(404, 'Patient not found');
    const decrypted = decryptPatientPHI(patient) as any;
    const phone = decrypted.whatsappPhone || decrypted.phone;
    if (!phone) throw new AppError(400, 'Patient has no phone on file');

    const sent = await sendTextMessage({
      clinicId: cid,
      patientId,
      to: phone,
      body,
      userId: c.get('user').id,
    });
    return c.json({ ok: true, messageId: sent.messageId });
  } catch (err) {
    return handleError(c, err);
  }
});

const sendTemplateSchema = z.object({
  templateName: z.string().min(1),
  languageCode: z.string().default('en'),
  components: z.array(z.any()).optional(),
});

whatsappRoute.post('/conversations/:patientId/send-template', async (c) => {
  try {
    const cid = clinicId(c);
    const patientId = c.req.param('patientId');
    const args = sendTemplateSchema.parse(await c.req.json());
    const scoped = await scopedPatientIds(c);
    if (scoped !== 'all' && !scoped.includes(patientId)) throw new AppError(403, 'Patient out of doctor scope');

    const db = getReadDb();
    const [patient] = await db.select().from(patients).where(eq(patients.id, patientId)).limit(1);
    if (!patient) throw new AppError(404, 'Patient not found');
    const decrypted = decryptPatientPHI(patient) as any;
    const phone = decrypted.whatsappPhone || decrypted.phone;
    if (!phone) throw new AppError(400, 'Patient has no phone on file');

    const sent = await sendTemplateMessage({
      clinicId: cid,
      patientId,
      to: phone,
      templateName: args.templateName,
      languageCode: args.languageCode,
      components: args.components,
      userId: c.get('user').id,
    });
    return c.json({ ok: true, messageId: sent.messageId });
  } catch (err) {
    return handleError(c, err);
  }
});

const quickReplySchema = z.object({
  shortcut: z.string().regex(/^\/[a-z0-9_-]+$/, 'Must look like /slug'),
  body: z.string().min(1).max(2000),
  position: z.number().int().nonnegative().default(0),
});

whatsappRoute.post('/quick-replies', async (c) => {
  try {
    const cid = clinicId(c);
    if (!c.get('permissions')?.has('comms.whatsapp.quick_replies.edit')) throw new AppError(403, 'forbidden');
    const args = quickReplySchema.parse(await c.req.json());
    const db = await getDatabase();
    const [row] = await db.insert(whatsappQuickReplies).values({
      id: crypto.randomUUID(),
      clinicId: cid,
      shortcut: args.shortcut,
      body: args.body,
      position: args.position,
      createdBy: c.get('user').id,
    }).returning();
    return c.json({ quickReply: row });
  } catch (err) {
    return handleError(c, err);
  }
});

whatsappRoute.patch('/quick-replies/:id', async (c) => {
  try {
    const cid = clinicId(c);
    if (!c.get('permissions')?.has('comms.whatsapp.quick_replies.edit')) throw new AppError(403, 'forbidden');
    const id = c.req.param('id');
    const args = quickReplySchema.partial().parse(await c.req.json());
    const db = await getDatabase();
    const [row] = await db.update(whatsappQuickReplies)
      .set({ ...args, updatedAt: new Date() })
      .where(and(eq(whatsappQuickReplies.id, id), eq(whatsappQuickReplies.clinicId, cid)))
      .returning();
    return c.json({ quickReply: row });
  } catch (err) {
    return handleError(c, err);
  }
});

whatsappRoute.delete('/quick-replies/:id', async (c) => {
  try {
    const cid = clinicId(c);
    if (!c.get('permissions')?.has('comms.whatsapp.quick_replies.edit')) throw new AppError(403, 'forbidden');
    const id = c.req.param('id');
    const db = await getDatabase();
    await db.delete(whatsappQuickReplies)
      .where(and(eq(whatsappQuickReplies.id, id), eq(whatsappQuickReplies.clinicId, cid)));
    return c.json({ ok: true });
  } catch (err) {
    return handleError(c, err);
  }
});

export default whatsappRoute;

import whatsappModuleRoute from './routes/whatsapp';
// …
protectedRoutes.route('/whatsapp-module', whatsappModuleRoute);

cd server && npx tsc --noEmit 2>&1 | grep -E "routes/whatsapp|api\.ts" | grep -v "messages\.ts"

git add server/src/routes/whatsapp.ts server/src/api.ts
git commit -m "feat(whatsapp): module API — conversations, events, templates, quick replies, analytics"

grep -rn "navItems\s*=\|NavEntry" /Users/ssh/Documents/Beta-App/odontoX/ui/src/components --include="*.tsx" | head -10

{
  id: 'whatsapp',
  label: 'WhatsApp',
  icon: 'MessageCircle',  // or whatever icon lib uses
  permission: 'comms.whatsapp.module.access',
  module: 'whatsapp_api',  // gated on clinic_modules.whatsapp_api being enabled
  route: '/whatsapp',
},

{
  key: 'comms.whatsapp',
  label: 'WhatsApp',
  children: [
    { key: 'comms.whatsapp.module.access',       label: 'Access the WhatsApp module',     defaultRoles: ['admin', 'receptionist', 'doctor'] },
    { key: 'comms.whatsapp.templates.send',      label: 'Send approved templates',          defaultRoles: ['admin', 'receptionist'] },
    { key: 'comms.whatsapp.quick_replies.edit',  label: 'Edit quick reply snippets',        defaultRoles: ['admin'] },
    { key: 'comms.whatsapp.analytics.view',      label: 'View WhatsApp analytics',          defaultRoles: ['admin'] },
  ],
},

cd ui && npx tsc --noEmit -p tsconfig.json 2>&1 | tail -5

git add ui/src/lib/permissionTree.ts ui/src/components/layout/AppLayout.tsx
git commit -m "feat(whatsapp): sidebar nav entry + permission keys"

// ─── WhatsApp Module ─────────────────────────────────────────────────────────

export interface WaWindowState {
  status: 'green' | 'amber' | 'red' | 'free-entry';
  opensAt: string | null;
  closesAt: string | null;
  secondsLeft: number;
}

export interface WaThread {
  patientId: string;
  patientName: string;
  patientNumber: string;
  lastMessage: { id: string; preview: string; direction: 'inbound' | 'outbound'; status: string; createdAt: string };
  unreadCount: number;
  window: WaWindowState;
}

export interface WaEvent {
  id: string;
  type: 'inbound_message' | 'outbound_message' | 'template_sent' | 'delivered' | 'read' | 'failed' | 'window_opened' | 'window_closed' | 'free_entry';
  category: 'marketing' | 'utility' | 'authentication' | 'auth_international' | 'service' | null;
  templateName: string | null;
  costPkr: string;  // numeric serialized as string
  metadata: Record<string, unknown>;
  createdAt: string;
  patientId: string | null;
  messageId: string | null;
}

export interface WaTemplate {
  name: string;
  category: string;
  language: string;
  status: 'APPROVED' | 'PENDING' | 'REJECTED';
  components: any[];
}

export interface WaQuickReply {
  id: string;
  shortcut: string;
  body: string;
  position: number;
}

const WA_BASE = '/api/v1/protected/whatsapp-module';

export async function getWaThreads(): Promise<WaThread[]> {
  const r = await fetchWithAuth(`${WA_BASE}/conversations`);
  const j = await r.json();
  return j.threads ?? [];
}

export async function getWaThreadMessages(patientId: string): Promise<Message[]> {
  const r = await fetchWithAuth(`${WA_BASE}/conversations/${patientId}/messages`);
  const j = await r.json();
  return j.messages ?? [];
}

export async function getWaWindow(patientId: string): Promise<WaWindowState> {
  const r = await fetchWithAuth(`${WA_BASE}/conversations/${patientId}/window`);
  return r.json();
}

export async function sendWaText(patientId: string, body: string): Promise<{ ok: boolean; messageId: string }> {
  const r = await fetchWithAuth(`${WA_BASE}/conversations/${patientId}/send`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ body }),
  });
  return r.json();
}

export async function sendWaTemplate(patientId: string, args: { templateName: string; languageCode?: string; components?: any[] }): Promise<{ ok: boolean; messageId: string }> {
  const r = await fetchWithAuth(`${WA_BASE}/conversations/${patientId}/send-template`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(args),
  });
  return r.json();
}

export async function getWaEvents(params: { patientId?: string; type?: string; from?: string; to?: string; cursor?: string; limit?: number } = {}): Promise<{ events: WaEvent[]; nextCursor: string | null }> {
  const qs = new URLSearchParams();
  for (const [k, v] of Object.entries(params)) if (v != null) qs.set(k, String(v));
  const r = await fetchWithAuth(`${WA_BASE}/events?${qs.toString()}`);
  return r.json();
}

export async function getWaTemplates(): Promise<{ templates: WaTemplate[]; cached: boolean }> {
  const r = await fetchWithAuth(`${WA_BASE}/templates`);
  return r.json();
}

export async function getWaQuickReplies(): Promise<WaQuickReply[]> {
  const r = await fetchWithAuth(`${WA_BASE}/quick-replies`);
  const j = await r.json();
  return j.quickReplies ?? [];
}

export async function createWaQuickReply(args: { shortcut: string; body: string; position?: number }): Promise<WaQuickReply> {
  const r = await fetchWithAuth(`${WA_BASE}/quick-replies`, {
    method: 'POST', headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(args),
  });
  return (await r.json()).quickReply;
}

export async function updateWaQuickReply(id: string, args: Partial<{ shortcut: string; body: string; position: number }>): Promise<WaQuickReply> {
  const r = await fetchWithAuth(`${WA_BASE}/quick-replies/${id}`, {
    method: 'PATCH', headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(args),
  });
  return (await r.json()).quickReply;
}

export async function deleteWaQuickReply(id: string): Promise<void> {
  await fetchWithAuth(`${WA_BASE}/quick-replies/${id}`, { method: 'DELETE' });
}

export interface WaAnalytics {
  lastUpdated: string;
  monthCostPkr: number;
  byCategory: Array<{ category: string; sent: number; delivered: number; read: number; cost: string }>;
  busiestHours: Array<{ hour: number; n: number }>;
}

export async function getWaAnalytics(): Promise<WaAnalytics> {
  const r = await fetchWithAuth(`${WA_BASE}/analytics`);
  return r.json();
}

export async function getWaCostPreview(args: { category: string; insideWindow: boolean }): Promise<{ pricePkr: number; currency: string; isFreeInWindow: boolean }> {
  const r = await fetchWithAuth(`${WA_BASE}/cost/preview?category=${args.category}&insideWindow=${args.insideWindow}`);
  return r.json();
}