/**
 * WhatsApp Business service — branch credential management, outbound send,
 * inbound idempotency, and message-log access.
 *
 * Tokens are AES-256-GCM encrypted via @server/utils/crypto before insert
 * and decrypted only inside this module (and the webhook handler when it
 * needs the app secret for HMAC verification).
 */

import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { encrypt, decrypt } from '@server/utils/crypto';
import { childLogger } from '@server/logger';
const log = childLogger('svc.whatsapp');

import {
  sendText, sendTemplate, verifyConnection, listApprovedTemplates,
  type WhatsAppCreds, type WhatsAppApiError, type TemplateComponent,
} from './whatsapp/cloud-api';

// Re-exported so callers (Marketing Automation, transactional flows, etc.)
// can construct template payloads without reaching into the cloud-api module.
export type { TemplateComponent } from './whatsapp/cloud-api';
import { randomBytes } from 'crypto';

export interface WhatsAppCredentialView {
  branch_id: string;
  branch_name: string;
  phone_number_id: string | null;
  waba_id: string | null;
  whatsapp_phone_number: string | null;
  display_name: string | null;
  is_active: boolean;
  configured: boolean;
  access_token_hint: string | null;
  app_secret_set: boolean;
  verify_token: string | null;
  webhook_url: string | null;
  created_at: string | null;
  updated_at: string | null;
  template_count: number;
  templates_synced_at: string | null;
}

interface CredsRow {
  branch_id: string;
  branch_name: string;
  restaurant_id: string;
  phone_number_id: string | null;
  waba_id: string | null;
  whatsapp_phone_number: string | null;
  display_name: string | null;
  access_token_encrypted: string | null;
  app_secret_encrypted: string | null;
  verify_token: string | null;
  is_active: boolean | null;
  created_at: string | null;
  updated_at: string | null;
  template_count: string | number | null;
  templates_synced_at: string | null;
}

function appOrigin(): string {
  // Public URL for webhook URLs surfaced in the UI. Falls back to a Replit
  // dev URL during local development if NEXT_PUBLIC_APP_URL is unset.
  const url = process.env.NEXT_PUBLIC_APP_URL?.replace(/\/+$/, '');
  if (url) return url;
  const dev = process.env.REPLIT_DEV_DOMAIN;
  if (dev) return `https://${dev}`;
  return 'http://localhost:5000';
}

function webhookUrlFor(branchId: string): string {
  return `${appOrigin()}/api/channels/whatsapp/webhook/${branchId}`;
}

function tokenHint(token: string): string {
  if (!token) return '';
  if (token.length <= 8) return '****';
  return token.slice(0, 4) + '...' + token.slice(-4);
}

function generateVerifyToken(): string {
  return randomBytes(24).toString('hex');
}

/**
 * List all branches (for the active restaurant) annotated with their
 * WhatsApp configuration status. Branches without credentials still appear
 * so the UI can show empty rows ready for connection.
 */
export async function listWhatsAppCredentialsForRestaurant(
  restaurantId: string
): Promise<WhatsAppCredentialView[]> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT b.id AS branch_id, b.name AS branch_name, b.restaurant_id,
           c.phone_number_id, c.waba_id, c.whatsapp_phone_number, c.display_name,
           c.access_token_encrypted, c.app_secret_encrypted, c.verify_token,
           c.is_active, c.created_at, c.updated_at,
           COALESCE(t.template_count, 0) AS template_count,
           COALESCE(c.templates_synced_at, t.templates_synced_at) AS templates_synced_at
    FROM branches b
    LEFT JOIN branch_whatsapp_credentials c ON c.branch_id = b.id
    LEFT JOIN (
      SELECT branch_id,
             COUNT(*)::int AS template_count,
             MAX(synced_at) AS templates_synced_at
      FROM whatsapp_templates
      GROUP BY branch_id
    ) t ON t.branch_id = b.id
    WHERE b.restaurant_id = ${restaurantId} AND b.is_active = true
    ORDER BY b.name ASC
  `);
  return (rows as unknown as CredsRow[]).map((r) => {
    const configured = Boolean(r.phone_number_id && r.access_token_encrypted);
    let hint: string | null = null;
    if (r.access_token_encrypted) {
      try { hint = tokenHint(decrypt(r.access_token_encrypted)); }
      catch { hint = '****'; }
    }
    return {
      branch_id: r.branch_id,
      branch_name: r.branch_name,
      phone_number_id: r.phone_number_id,
      waba_id: r.waba_id,
      whatsapp_phone_number: r.whatsapp_phone_number,
      display_name: r.display_name,
      is_active: r.is_active ?? false,
      configured,
      access_token_hint: hint,
      app_secret_set: Boolean(r.app_secret_encrypted),
      verify_token: r.verify_token,
      webhook_url: webhookUrlFor(r.branch_id),
      created_at: r.created_at,
      updated_at: r.updated_at,
      template_count: Number(r.template_count ?? 0),
      templates_synced_at: r.templates_synced_at ?? null,
    };
  });
}

export interface SaveCredentialsInput {
  phone_number_id: string;
  waba_id?: string | null;
  /** WhatsApp business phone number in E.164 format (e.g. +14155552671). */
  whatsapp_phone_number?: string | null;
  display_name?: string | null;
  /** Optional — leave undefined to keep the existing token. */
  access_token?: string;
  /** Optional — leave undefined to keep the existing app secret. */
  app_secret?: string;
  is_active?: boolean;
}

/**
 * Insert or update a branch's WhatsApp credentials. The verify_token is
 * generated on first save and preserved across updates so the operator
 * does not have to re-paste it into Meta after each edit.
 */
export async function upsertWhatsAppCredentials(
  restaurantId: string,
  branchId: string,
  input: SaveCredentialsInput
): Promise<WhatsAppCredentialView> {
  await initDatabase();

  // Verify the branch belongs to this restaurant — never trust client input.
  const { rows: bRows } = await db.execute(sql`
    SELECT id FROM branches WHERE id = ${branchId} AND restaurant_id = ${restaurantId} LIMIT 1
  `);
  if (!bRows[0]) throw new Error('Branch not found');

  const { rows: existingRows } = await db.execute(sql`
    SELECT access_token_encrypted, app_secret_encrypted, verify_token,
           unsigned_grace_until
    FROM branch_whatsapp_credentials WHERE branch_id = ${branchId} LIMIT 1
  `);
  const existing = existingRows[0] as
    | {
        access_token_encrypted: string | null;
        app_secret_encrypted: string | null;
        verify_token: string | null;
        unsigned_grace_until: string | Date | null;
      }
    | undefined;

  if (!input.phone_number_id?.trim()) throw new Error('phone_number_id is required');
  if (!existing && !input.access_token?.trim()) {
    throw new Error('access_token is required on first save');
  }

  const encryptedToken = input.access_token?.trim()
    ? encrypt(input.access_token.trim())
    : existing?.access_token_encrypted ?? null;
  if (!encryptedToken) throw new Error('access_token is required');

  const encryptedSecret = input.app_secret?.trim()
    ? encrypt(input.app_secret.trim())
    : existing?.app_secret_encrypted ?? null;

  const verifyToken = existing?.verify_token ?? generateVerifyToken();
  const isActive = input.is_active ?? true;

  // Secure-by-default unsigned grace window. The webhook accepts unsigned
  // POSTs only when:
  //   (a) the operator hasn't supplied an app_secret yet, AND
  //   (b) we're still inside the 24h grace window from the first save.
  // The moment a secret is provided, the grace window is cleared so future
  // unsigned deliveries are dropped — even if the secret is later removed.
  // Resolved upfront in JS (not in SQL) so we don't have to reference the
  // existing column from inside the INSERT VALUES list, which Postgres
  // does not allow on a not-yet-existing row.
  let graceUntil: Date | null;
  if (encryptedSecret) {
    graceUntil = null; // secret present: no grace window needed
  } else if (existing) {
    // Preserve whatever grace timestamp the row already has (could be null
    // if a secret was previously provided then removed — staying null is
    // the secure default).
    const raw = existing.unsigned_grace_until;
    graceUntil = raw instanceof Date ? raw : raw ? new Date(raw) : null;
  } else {
    // First save without a secret: open a fresh 1h grace window.
    graceUntil = new Date(Date.now() + 60 * 60 * 1000);
  }
  const graceUntilIso = graceUntil ? graceUntil.toISOString() : null;

  await db.execute(sql`
    INSERT INTO branch_whatsapp_credentials
      (branch_id, restaurant_id, phone_number_id, waba_id, whatsapp_phone_number,
       display_name, access_token_encrypted, app_secret_encrypted, verify_token,
       is_active, unsigned_grace_until, updated_at)
    VALUES
      (${branchId}, ${restaurantId}, ${input.phone_number_id.trim()},
       ${input.waba_id?.trim() || null},
       ${input.whatsapp_phone_number?.trim() || null},
       ${input.display_name?.trim() || null},
       ${encryptedToken}, ${encryptedSecret}, ${verifyToken}, ${isActive},
       ${graceUntilIso}, NOW())
    ON CONFLICT (branch_id) DO UPDATE SET
      phone_number_id = EXCLUDED.phone_number_id,
      waba_id = EXCLUDED.waba_id,
      whatsapp_phone_number = EXCLUDED.whatsapp_phone_number,
      display_name = EXCLUDED.display_name,
      access_token_encrypted = EXCLUDED.access_token_encrypted,
      app_secret_encrypted = EXCLUDED.app_secret_encrypted,
      is_active = EXCLUDED.is_active,
      unsigned_grace_until = EXCLUDED.unsigned_grace_until,
      updated_at = NOW()
  `);

  const all = await listWhatsAppCredentialsForRestaurant(restaurantId);
  const found = all.find((r) => r.branch_id === branchId);
  if (!found) throw new Error('Failed to load saved credentials');
  return found;
}

export async function deleteWhatsAppCredentials(restaurantId: string, branchId: string): Promise<void> {
  await initDatabase();
  await db.execute(sql`
    DELETE FROM branch_whatsapp_credentials
    WHERE branch_id = ${branchId} AND restaurant_id = ${restaurantId}
  `);
}

export interface ResolvedBranchCreds {
  branchId: string;
  restaurantId: string;
  phoneNumberId: string;
  wabaId: string | null;
  whatsappPhoneNumber: string | null;
  displayName: string | null;
  accessToken: string;
  appSecret: string | null;
  verifyToken: string;
  isActive: boolean;
  /** Timestamp until which unsigned POSTs are tolerated; null once a secret is set. */
  unsignedGraceUntil: Date | null;
}

export async function getBranchCredsByBranchId(branchId: string): Promise<ResolvedBranchCreds | null> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT branch_id, restaurant_id, phone_number_id, waba_id, whatsapp_phone_number,
           display_name, access_token_encrypted, app_secret_encrypted, verify_token,
           is_active, unsigned_grace_until
    FROM branch_whatsapp_credentials WHERE branch_id = ${branchId} LIMIT 1
  `);
  const r = rows[0] as Record<string, unknown> | undefined;
  if (!r) return null;
  let token: string;
  try { token = decrypt(r.access_token_encrypted as string); }
  catch { return null; }
  let appSecret: string | null = null;
  if (r.app_secret_encrypted) {
    try { appSecret = decrypt(r.app_secret_encrypted as string); } catch { appSecret = null; }
  }
  const graceRaw = r.unsigned_grace_until;
  const graceUntil = graceRaw instanceof Date
    ? graceRaw
    : graceRaw ? new Date(String(graceRaw)) : null;
  return {
    branchId: r.branch_id as string,
    restaurantId: r.restaurant_id as string,
    phoneNumberId: r.phone_number_id as string,
    wabaId: (r.waba_id as string | null) ?? null,
    whatsappPhoneNumber: (r.whatsapp_phone_number as string | null) ?? null,
    displayName: (r.display_name as string | null) ?? null,
    accessToken: token,
    appSecret,
    verifyToken: r.verify_token as string,
    isActive: Boolean(r.is_active),
    unsignedGraceUntil: graceUntil && !isNaN(graceUntil.getTime()) ? graceUntil : null,
  };
}

/** Test the saved credentials by calling Graph (used by the UI Test button). */
export async function testWhatsAppConnection(
  restaurantId: string,
  branchId: string
): Promise<
  | { ok: true; displayPhoneNumber?: string; verifiedName?: string; wabaName?: string }
  | { ok: false; error: WhatsAppApiError | { message: string; status: number } }
> {
  const creds = await getBranchCredsByBranchId(branchId);
  if (!creds || creds.restaurantId !== restaurantId) {
    return { ok: false, error: { status: 404, message: 'No WhatsApp credentials saved for this branch' } };
  }
  return verifyConnection({
    phoneNumberId: creds.phoneNumberId,
    accessToken: creds.accessToken,
    wabaId: creds.wabaId || undefined,
  });
}

// ------ Outbound send + log ------

export interface SendOptions {
  conversationId?: string | null;
  /** When true, surface API errors instead of swallowing them (UI test send). */
  throwOnError?: boolean;
  /**
   * When set, sendWhatsAppMessage delivers a pre-approved template instead
   * of freeform text (the `body` argument is then used only for the audit
   * log). Use this for messages outside the 24h customer-service window.
   */
  template?: {
    name: string;
    languageCode: string;
    components?: TemplateComponent[];
  };
}

/**
 * Defensive sanitization for any text crossing the WhatsApp boundary. Strips
 * the trailing `{"quickReplies":[...]}` JSON the chat agent may emit (intended
 * for the in-app widget) plus any leading/trailing whitespace. Centralized
 * here so every outbound call site benefits — the system prompt suppression
 * is best-effort but the model can still slip up.
 */
export function sanitizeForWhatsApp(text: string): string {
  return text
    .replace(/\s*\{\s*"quickReplies"\s*:\s*\[[^\]]*\]\s*\}\s*$/m, '')
    .trim();
}

/**
 * Send a freeform text message via the branch's WhatsApp credentials.
 * Always logs the attempt in whatsapp_message_log so operators can audit.
 * Returns the Meta message id when successful, or null when delivery
 * failed (the row is logged with status='failed' and the error captured).
 */
export async function sendWhatsAppMessage(
  branchId: string,
  toE164: string,
  body: string,
  opts: SendOptions = {}
): Promise<{ messageId: string | null; error?: string }> {
  const creds = await getBranchCredsByBranchId(branchId);
  if (!creds) {
    const msg = 'No WhatsApp credentials configured for branch';
    if (opts.throwOnError) throw new Error(msg);
    return { messageId: null, error: msg };
  }
  if (!creds.isActive) {
    const msg = 'WhatsApp channel is disabled for this branch';
    if (opts.throwOnError) throw new Error(msg);
    return { messageId: null, error: msg };
  }
  const apiCreds: WhatsAppCreds = { phoneNumberId: creds.phoneNumberId, accessToken: creds.accessToken };

  // Dispatch: template path is taken when opts.template is set, otherwise
  // freeform text. The audit log records the same `body` either way so
  // operators can see what the customer would have read.
  const isTemplate = Boolean(opts.template);
  const safeBody = sanitizeForWhatsApp(body) || body;
  const logBody = isTemplate
    ? `[template:${opts.template!.name}/${opts.template!.languageCode}]`
    : safeBody;

  const result = isTemplate
    ? await sendTemplate(
        apiCreds,
        toE164,
        opts.template!.name,
        opts.template!.languageCode,
        opts.template!.components ?? []
      )
    : await sendText(apiCreds, toE164, safeBody);

  if (!result.ok) {
    await logOutbound({
      branchId,
      restaurantId: creds.restaurantId,
      conversationId: opts.conversationId ?? null,
      toE164,
      body: logBody,
      status: 'failed',
      waMessageId: null,
      error: result.error.message,
      messageType: isTemplate ? 'template' : 'text',
    });
    if (opts.throwOnError) {
      throw new Error(`WhatsApp ${isTemplate ? 'template ' : ''}send failed: ${result.error.message}`);
    }
    return { messageId: null, error: result.error.message };
  }
  await logOutbound({
    branchId,
    restaurantId: creds.restaurantId,
    conversationId: opts.conversationId ?? null,
    toE164,
    body: logBody,
    status: 'sent',
    waMessageId: result.result.messageId ?? null,
    error: null,
    messageType: isTemplate ? 'template' : 'text',
  });
  return { messageId: result.result.messageId ?? null };
}

/**
 * Send a pre-approved WhatsApp template message (HSM). Required for any
 * outbound delivery outside the 24-hour customer-initiated session window
 * — Meta will reject freeform text in that case. The template name and
 * language must already be approved in Business Manager. The components
 * array carries variable substitutions (header/body/buttons).
 *
 * Exposed as a stable helper so future surfaces (Marketing Automation in
 * Task #215, transactional confirmations, etc.) can send templated
 * messages without re-implementing the wire format.
 */
export async function sendWhatsAppTemplate(
  branchId: string,
  toE164: string,
  templateName: string,
  languageCode: string,
  components: TemplateComponent[] = [],
  opts: SendOptions = {}
): Promise<{ messageId: string | null; error?: string }> {
  return sendWhatsAppMessage(branchId, toE164, '', {
    ...opts,
    template: { name: templateName, languageCode, components },
  });
}

interface OutboundLog {
  branchId: string;
  restaurantId: string;
  conversationId: string | null;
  toE164: string;
  body: string;
  status: 'sent' | 'failed';
  waMessageId: string | null;
  error: string | null;
  messageType?: string;
}

async function logOutbound(row: OutboundLog): Promise<void> {
  try {
    await db.execute(sql`
      INSERT INTO whatsapp_message_log
        (branch_id, restaurant_id, conversation_id, direction, wa_message_id,
         to_e164, body, message_type, status, error)
      VALUES (${row.branchId}, ${row.restaurantId}, ${row.conversationId},
              'outbound', ${row.waMessageId}, ${row.toE164}, ${row.body},
              ${row.messageType ?? 'text'}, ${row.status}, ${row.error})
    `);
  } catch (err) {
    // Logging must never break delivery — we still returned the result above.
    log.error({ err }, 'WhatsApp outbound log failed');
  }
}

export interface InboundLogInput {
  branchId: string;
  restaurantId: string;
  conversationId: string | null;
  waMessageId: string;
  fromE164: string;
  body: string | null;
  messageType: string;
}

/**
 * Insert an inbound message row. Returns false when the wa_message_id has
 * already been recorded (idempotent — Meta replays delivery on retry).
 */
export async function logInboundIfNew(input: InboundLogInput): Promise<boolean> {
  await initDatabase();
  try {
    const { rows } = await db.execute(sql`
      INSERT INTO whatsapp_message_log
        (branch_id, restaurant_id, conversation_id, direction, wa_message_id,
         from_e164, body, message_type, status)
      VALUES (${input.branchId}, ${input.restaurantId}, ${input.conversationId},
              'inbound', ${input.waMessageId}, ${input.fromE164}, ${input.body},
              ${input.messageType}, 'received')
      ON CONFLICT (branch_id, wa_message_id)
        WHERE wa_message_id IS NOT NULL AND direction = 'inbound'
        DO NOTHING
      RETURNING id
    `);
    return rows.length > 0;
  } catch (err) {
    log.error({ err }, 'WhatsApp inbound log failed');
    // If the unique index variant fails (e.g. older PG), fall back to a manual
    // pre-check so we never re-process a duplicate event.
    const { rows: existing } = await db.execute(sql`
      SELECT id FROM whatsapp_message_log
      WHERE branch_id = ${input.branchId} AND wa_message_id = ${input.waMessageId}
        AND direction = 'inbound' LIMIT 1
    `);
    if (existing.length > 0) return false;
    await db.execute(sql`
      INSERT INTO whatsapp_message_log
        (branch_id, restaurant_id, conversation_id, direction, wa_message_id,
         from_e164, body, message_type, status)
      VALUES (${input.branchId}, ${input.restaurantId}, ${input.conversationId},
              'inbound', ${input.waMessageId}, ${input.fromE164}, ${input.body},
              ${input.messageType}, 'received')
    `);
    return true;
  }
}

/**
 * Apply Meta delivery-status callbacks (sent/delivered/read/failed) to the
 * matching outbound row. Best-effort: if the wa_message_id isn't in our
 * log (e.g. message sent before logging was wired), the UPDATE simply
 * touches no rows — never throws.
 */
export async function applyStatusUpdates(
  branchId: string,
  statuses: Array<{ id: string; status: string; timestamp?: string }>
): Promise<void> {
  for (const s of statuses) {
    if (!s.id || !s.status) continue;
    try {
      await db.execute(sql`
        UPDATE whatsapp_message_log
           SET status = ${s.status}
         WHERE branch_id = ${branchId}
           AND wa_message_id = ${s.id}
           AND direction = 'outbound'
      `);
    } catch (err) {
      log.error({ err }, 'WhatsApp status update failed');
    }
  }
}

export async function attachConversationToInbound(waMessageId: string, branchId: string, conversationId: string): Promise<void> {
  try {
    await db.execute(sql`
      UPDATE whatsapp_message_log SET conversation_id = ${conversationId}
      WHERE branch_id = ${branchId} AND wa_message_id = ${waMessageId} AND direction = 'inbound'
    `);
  } catch { /* best-effort linkage */ }
}

export interface MessageLogRow {
  id: string;
  branch_id: string;
  branch_name: string | null;
  conversation_id: string | null;
  direction: 'inbound' | 'outbound';
  from_e164: string | null;
  to_e164: string | null;
  body: string | null;
  message_type: string | null;
  status: string;
  error: string | null;
  created_at: string;
}

export async function listRecentMessages(
  restaurantId: string,
  branchId?: string | null,
  limit = 30
): Promise<MessageLogRow[]> {
  await initDatabase();
  const cap = Math.min(Math.max(limit, 1), 100);
  if (branchId) {
    const { rows } = await db.execute(sql`
      SELECT m.id, m.branch_id, b.name AS branch_name, m.conversation_id,
             m.direction, m.from_e164, m.to_e164, m.body, m.message_type,
             m.status, m.error, m.created_at
      FROM whatsapp_message_log m
      LEFT JOIN branches b ON b.id = m.branch_id
      WHERE m.restaurant_id = ${restaurantId} AND m.branch_id = ${branchId}
      ORDER BY m.created_at DESC LIMIT ${cap}
    `);
    return rows as unknown as MessageLogRow[];
  }
  const { rows } = await db.execute(sql`
    SELECT m.id, m.branch_id, b.name AS branch_name, m.conversation_id,
           m.direction, m.from_e164, m.to_e164, m.body, m.message_type,
           m.status, m.error, m.created_at
    FROM whatsapp_message_log m
    LEFT JOIN branches b ON b.id = m.branch_id
    WHERE m.restaurant_id = ${restaurantId}
    ORDER BY m.created_at DESC LIMIT ${cap}
  `);
  return rows as unknown as MessageLogRow[];
}

/**
 * Restaurant-level feature flag check. The webhook does NOT consult this
 * (an inbound message can arrive even after a downgrade — we still log it
 * and reply with a degraded message), but the UI APIs do, to gate writes.
 */
// ─── WhatsApp Template Sync ──────────────────────────────────────────────────

export interface WhatsAppTemplateRow {
  id: string;
  restaurant_id: string;
  branch_id: string;
  name: string;
  language: string;
  status: string;
  components: unknown[];
  synced_at: string;
}

/**
 * Fetch all APPROVED templates from Meta for the given branch's WABA and
 * upsert them into the local `whatsapp_templates` cache. Returns the full
 * updated list ordered by name.
 */
export async function syncWhatsAppTemplates(
  restaurantId: string,
  branchId: string
): Promise<{ ok: true; templates: WhatsAppTemplateRow[] } | { ok: false; error: string }> {
  await initDatabase();
  const creds = await getBranchCredsByBranchId(branchId);
  if (!creds || creds.restaurantId !== restaurantId) {
    return { ok: false, error: 'No WhatsApp credentials configured for this branch' };
  }
  if (!creds.wabaId) {
    return { ok: false, error: 'No WABA ID configured — add a WhatsApp Business Account ID in Settings → Channels → WhatsApp' };
  }

  const result = await listApprovedTemplates(creds.wabaId, creds.accessToken);
  if (!result.ok) {
    return { ok: false, error: result.error.message };
  }

  if (result.truncated) {
    log.warn(
      { wabaId: creds.wabaId, count: result.templates.length },
      'WhatsApp template sync hit the pagination safety cap — some templates were not fetched'
    );
  }

  for (const tpl of result.templates) {
    try {
      await db.execute(sql`
        INSERT INTO whatsapp_templates
          (restaurant_id, branch_id, name, language, status, components, synced_at)
        VALUES
          (${restaurantId}, ${branchId}, ${tpl.name}, ${tpl.language}, ${tpl.status},
           ${JSON.stringify(tpl.components)}::jsonb, now())
        ON CONFLICT (branch_id, name, language) DO UPDATE SET
          status     = EXCLUDED.status,
          components = EXCLUDED.components,
          synced_at  = now()
      `);
    } catch (err) {
      log.error({ err, tpl }, 'WhatsApp template upsert failed');
    }
  }

  // Persist the sync timestamp on the credential row so the UI can show
  // "last synced" even when the WABA has zero approved templates yet.
  try {
    await db.execute(sql`
      UPDATE branch_whatsapp_credentials
         SET templates_synced_at = now()
       WHERE branch_id = ${branchId} AND restaurant_id = ${restaurantId}
    `);
  } catch (err) {
    log.warn({ err, branchId }, 'WhatsApp template sync: failed to stamp templates_synced_at');
  }

  return listWhatsAppTemplates(restaurantId, branchId);
}

/**
 * Return the locally-cached APPROVED templates for a branch, ordered by name.
 */
export async function listWhatsAppTemplates(
  restaurantId: string,
  branchId: string
): Promise<{ ok: true; templates: WhatsAppTemplateRow[] } | { ok: false; error: string }> {
  await initDatabase();
  try {
    const { rows } = await db.execute(sql`
      SELECT id, restaurant_id, branch_id, name, language, status, components, synced_at
      FROM whatsapp_templates
      WHERE branch_id = ${branchId} AND restaurant_id = ${restaurantId}
      ORDER BY name ASC
    `);
    return { ok: true, templates: rows as unknown as WhatsAppTemplateRow[] };
  } catch (err) {
    log.error({ err }, 'listWhatsAppTemplates failed');
    return { ok: false, error: 'Failed to load cached templates' };
  }
}

export async function restaurantHasWhatsAppFeature(restaurantId: string): Promise<boolean> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT p.features FROM subscriptions s
    LEFT JOIN plans p ON p.id = s.plan_id
    WHERE s.restaurant_id = ${restaurantId}
    ORDER BY s.created_at DESC LIMIT 1
  `);
  const features = (rows[0] as { features?: unknown } | undefined)?.features;
  if (!Array.isArray(features)) return false;
  return features.includes('whatsapp_agent');
}