import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';

import { childLogger } from '@server/logger';
const log = childLogger('svc.menu');

export async function listCategories(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`(branch_id = ${branchId} OR branch_id IS NULL)`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT * FROM menu_categories WHERE restaurant_id = $1 [AND (branch_id = $2 OR branch_id IS NULL)] ORDER BY display_order ASC, name ASC */
  const { rows } = await db.execute(sql`SELECT * FROM menu_categories WHERE ${where} ORDER BY display_order ASC, name ASC`);
  return rows;
}

export async function getCategory(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM menu_categories WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM menu_categories WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new NotFoundError('Menu category');
  return rows[0];
}

export async function createCategory(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const scheduleJson = data.schedule == null ? null : JSON.stringify(data.schedule);
  /* raw: INSERT INTO menu_categories (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO menu_categories (restaurant_id, branch_id, name, display_order, is_active, schedule)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${data.name as string},
            ${(data.display_order as number) ?? 0}, ${data.is_active ?? true},
            ${scheduleJson}::jsonb)
    RETURNING *
  `);
  return rows[0];
}

export async function updateCategory(
  id: string,
  restaurantId: string,
  data: Record<string, unknown>,
  actor?: MenuAuditActor,
) {
  await initDatabase();
  const allowed = ['name', 'display_order', 'is_active', 'schedule'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    if (k === 'schedule') {
      sets.push(sql`schedule = ${v == null ? null : JSON.stringify(v)}::jsonb`);
    } else {
      sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
    }
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');

  // Snapshot the audit-tracked fields BEFORE the update so we can diff them
  // and write one menu_audit_log row per actually-changed field.
  const { rows: beforeRows } = await db.execute(sql`
    SELECT branch_id, schedule, is_active
      FROM menu_categories
     WHERE id = ${id} AND restaurant_id = ${restaurantId}
  `);
  const before = beforeRows[0] as Record<string, unknown> | undefined;

  /* raw: UPDATE menu_categories SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE menu_categories SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Menu category');

  if (before) {
    const after = rows[0] as Record<string, unknown>;
    for (const field of ['schedule', 'is_active'] as const) {
      if (!(field in data)) continue;
      if (deepEqual(before[field], after[field])) continue;
      await writeMenuAuditLog({
        restaurantId,
        branchId: (after.branch_id as string | null) ?? null,
        menuCategoryId: id,
        action: 'category_update',
        field,
        before: before[field],
        after: after[field],
        actor,
      });
    }
  }

  return rows[0];
}

export async function deleteCategory(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM menu_categories WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM menu_categories WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (result.rowCount === 0) throw new NotFoundError('Menu category');
}

export interface MenuItemFilters {
  restaurantId: string;
  branchId?: string;
  categoryId?: string;
  available?: boolean;
  search?: string;
  page?: number;
  limit?: number;
}

export async function listItems(filters: MenuItemFilters) {
  await initDatabase();
  const { restaurantId, branchId, categoryId, available, search, page = 1, limit = 50 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`mi.restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`(mi.branch_id = ${branchId} OR mi.branch_id IS NULL)`);
  if (categoryId) conditions.push(sql`mi.category_id = ${categoryId}`);
  if (available !== undefined) conditions.push(sql`mi.is_available = ${available}`);
  if (search) conditions.push(sql`mi.name ILIKE ${`%${search}%`}`);

  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FROM menu_items mi WHERE ... */
  const { rows: countRows } = await db.execute(sql`SELECT COUNT(*) FROM menu_items mi WHERE ${where}`);
  const total = parseInt((countRows[0] as { count: string })?.count ?? '0', 10);

  /* raw: SELECT mi.*, mc.name as category_name FROM menu_items mi LEFT JOIN menu_categories mc ... WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT mi.*, mc.name as category_name FROM menu_items mi
    LEFT JOIN menu_categories mc ON mi.category_id = mc.id
    WHERE ${where}
    ORDER BY mc.display_order ASC, mi.sort_order ASC, mi.name ASC
    LIMIT ${limit} OFFSET ${offset}
  `);

  if (rows.length > 0) {
    const itemIds = rows.map((r: Record<string, unknown>) => r.id);
    /* raw: SELECT mg.*, json_agg(...) FILTER (...) AS options FROM menu_modifier_groups mg LEFT JOIN menu_modifier_options mo ... WHERE mg.menu_item_id IN (...) ... GROUP BY mg.id ORDER BY mg.display_order */
    const { rows: groups } = await db.execute(sql`
      SELECT mg.*, json_agg(
         json_build_object('id', mo.id, 'name', mo.name, 'price_delta', mo.price_delta,
           'is_default', mo.is_default, 'is_available', mo.is_available, 'display_order', mo.display_order)
         ORDER BY mo.display_order, mo.name
       ) FILTER (WHERE mo.id IS NOT NULL) AS options
       FROM menu_modifier_groups mg
       LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
       WHERE mg.menu_item_id IN (${sql.join((itemIds as string[]).map(id => sql`${id}::uuid`), sql`, `)}) AND mg.restaurant_id = ${restaurantId}
       GROUP BY mg.id
       ORDER BY mg.display_order
    `);
    const groupsByItem: Record<string, unknown[]> = {};
    for (const g of groups as Record<string, unknown>[]) {
      const itemId = g.menu_item_id as string;
      if (!groupsByItem[itemId]) groupsByItem[itemId] = [];
      groupsByItem[itemId].push(g);
    }
    for (const row of rows as Record<string, unknown>[]) {
      (row as Record<string, unknown>).modifier_groups = groupsByItem[row.id as string] ?? [];
    }
  }

  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getItem(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT mi.*, mc.name as category_name FROM menu_items mi LEFT JOIN menu_categories mc ... WHERE mi.id = $1 AND mi.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT mi.*, mc.name as category_name FROM menu_items mi
    LEFT JOIN menu_categories mc ON mi.category_id = mc.id
    WHERE mi.id = ${id} AND mi.restaurant_id = ${restaurantId}
  `);
  const row = rows[0] as Record<string, unknown> | undefined;
  if (!row) throw new NotFoundError('Menu item');

  /* raw: SELECT mg.*, json_agg(...) FILTER (...) AS options FROM menu_modifier_groups mg LEFT JOIN menu_modifier_options mo ... WHERE mg.menu_item_id = $1 AND mg.restaurant_id = $2 GROUP BY mg.id ORDER BY mg.display_order */
  const { rows: groups } = await db.execute(sql`
    SELECT mg.*, json_agg(
       json_build_object('id', mo.id, 'name', mo.name, 'price_delta', mo.price_delta,
         'is_default', mo.is_default, 'is_available', mo.is_available, 'display_order', mo.display_order)
       ORDER BY mo.display_order, mo.name
     ) FILTER (WHERE mo.id IS NOT NULL) AS options
     FROM menu_modifier_groups mg
     LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
     WHERE mg.menu_item_id = ${id} AND mg.restaurant_id = ${restaurantId}
     GROUP BY mg.id
     ORDER BY mg.display_order
  `);
  row.modifier_groups = groups;
  return row;
}

export async function createItem(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const scheduleJson = data.schedule == null ? null : JSON.stringify(data.schedule);
  /* raw: INSERT INTO menu_items (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO menu_items (restaurant_id, branch_id, category_id, name, description, price,
       modifiers, dietary_tags, image_url, is_available, sort_order, spice_level, is_veg,
       prep_time_minutes, calories, is_in_stock, stock_count, schedule,
       low_stock_threshold, daily_reset_count)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${(data.category_id as string) ?? null},
            ${data.name as string}, ${(data.description as string) ?? null}, ${(data.price as number) ?? 0},
            ${JSON.stringify(data.modifiers ?? [])}::jsonb, ${JSON.stringify(data.dietary_tags ?? [])}::jsonb,
            ${(data.image_url as string) ?? null}, ${data.is_available ?? true},
            ${(data.sort_order as number) ?? 0}, ${(data.spice_level as number) ?? 0}, ${data.is_veg ?? false},
            ${(data.prep_time_minutes as number) ?? 0}, ${(data.calories as number) ?? 0},
            ${data.is_in_stock ?? true},
            ${data.stock_count === undefined ? null : (data.stock_count as number | null)},
            ${scheduleJson}::jsonb,
            ${data.low_stock_threshold === undefined ? null : (data.low_stock_threshold as number | null)},
            ${data.daily_reset_count === undefined ? null : (data.daily_reset_count as number | null)})
    RETURNING *
  `);
  return rows[0];
}

export async function updateItem(
  id: string,
  restaurantId: string,
  data: Record<string, unknown>,
  actor?: MenuAuditActor,
) {
  await initDatabase();
  const allowed = [
    'category_id', 'name', 'description', 'price', 'modifiers', 'dietary_tags',
    'image_url', 'is_available', 'sort_order', 'spice_level', 'is_veg',
    'prep_time_minutes', 'calories', 'is_in_stock', 'stock_count', 'schedule',
    // Task #295: alert + reset thresholds. Both are nullable — explicit null
    // disables the corresponding behaviour.
    'low_stock_threshold', 'daily_reset_count',
  ];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    if (k === 'schedule') {
      sets.push(sql`schedule = ${v == null ? null : JSON.stringify(v)}::jsonb`);
      continue;
    }
    if (k === 'stock_count' || k === 'low_stock_threshold' || k === 'daily_reset_count') {
      // explicit null = disabled; bindValue handles null fine.
      sets.push(sql`${sql.raw(k)} = ${(v as number | null) ?? null}`);
      continue;
    }
    const val = (k === 'modifiers' || k === 'dietary_tags') ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  // Snapshot availability-relevant fields before the update so we can diff
  // them and emit one menu_audit_log row per actually-changed field. Done
  // outside a transaction — for an availability audit log, slightly stale
  // before-values are acceptable; correctness of the UPDATE itself is not
  // affected.
  const { rows: beforeRows } = await db.execute(sql`
    SELECT branch_id, is_in_stock, stock_count, schedule, is_available
      FROM menu_items
     WHERE id = ${id} AND restaurant_id = ${restaurantId}
  `);
  const before = beforeRows[0] as Record<string, unknown> | undefined;

  /* raw: UPDATE menu_items SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE menu_items SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Menu item');

  if (before) {
    const after = rows[0] as Record<string, unknown>;
    for (const field of ['is_in_stock', 'stock_count', 'schedule', 'is_available'] as const) {
      if (!(field in data)) continue;
      if (deepEqual(before[field], after[field])) continue;
      await writeMenuAuditLog({
        restaurantId,
        branchId: (after.branch_id as string | null) ?? null,
        menuItemId: id,
        action: 'item_update',
        field,
        before: before[field],
        after: after[field],
        actor,
      });
    }
  }

  return rows[0];
}

export async function deleteItem(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM menu_items WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM menu_items WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Menu item');
}

// ============================================================================
// Task #291: advanced availability
// ============================================================================

export type OrderabilityReason =
  | 'unknown_item'
  | 'disabled'
  | 'wrong_branch'
  | 'out_of_stock'
  | 'off_schedule';

export interface OrderabilityResult {
  orderable: boolean;
  reason?: OrderabilityReason;
  /** Friendly diagnostic, e.g. item name or schedule context. */
  message?: string;
  /**
   * For `off_schedule` rejections only: the next time the item becomes
   * available, formatted in the branch's local timezone. Includes the
   * weekday short label when the next opening is not today.
   * Examples: `"18:00"` (today), `"Tue 11:00"` (later this week).
   */
  available_from?: string;
}

interface ScheduleWindow {
  days?: number[];
  start?: string;
  end?: string;
}

/**
 * Returns true when the schedule is empty/missing (always-on) or the supplied
 * instant falls inside one of its windows. Day-of-week uses JS getDay()
 * convention (0=Sun..6=Sat) interpreted in the supplied timezone. Windows that
 * cross midnight (start > end) are honoured as overnight ranges.
 */
export function isWithinSchedule(
  schedule: unknown,
  at: Date,
  timezone: string
): boolean {
  if (!schedule) return true;
  if (!Array.isArray(schedule) || schedule.length === 0) return true;

  let parts: Intl.DateTimeFormatPart[];
  try {
    parts = new Intl.DateTimeFormat('en-US', {
      timeZone: timezone || 'UTC',
      weekday: 'short',
      hour: '2-digit',
      minute: '2-digit',
      hourCycle: 'h23',
    }).formatToParts(at);
  } catch {
    parts = new Intl.DateTimeFormat('en-US', {
      timeZone: 'UTC',
      weekday: 'short',
      hour: '2-digit',
      minute: '2-digit',
      hourCycle: 'h23',
    }).formatToParts(at);
  }
  const weekdayMap: Record<string, number> = { Sun: 0, Mon: 1, Tue: 2, Wed: 3, Thu: 4, Fri: 5, Sat: 6 };
  const wdStr = parts.find(p => p.type === 'weekday')?.value ?? 'Sun';
  const day = weekdayMap[wdStr] ?? 0;
  const hh = (parts.find(p => p.type === 'hour')?.value ?? '00').padStart(2, '0');
  const mm = (parts.find(p => p.type === 'minute')?.value ?? '00').padStart(2, '0');
  // Intl returns "24" for midnight in some locales; normalize to "00".
  const hour = hh === '24' ? '00' : hh;
  const time = `${hour}:${mm}`;

  for (const w of schedule as ScheduleWindow[]) {
    const days = Array.isArray(w?.days) ? w.days : [];
    if (!days.includes(day)) continue;
    const start = (w.start ?? '00:00');
    const end = (w.end ?? '23:59');
    if (start <= end) {
      if (time >= start && time <= end) return true;
    } else {
      // overnight window e.g. 22:00 → 02:00
      if (time >= start || time <= end) return true;
    }
  }
  return false;
}

const WEEKDAY_LABELS = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'];

/**
 * Returns the next time the supplied schedule opens, relative to `at` in the
 * given timezone. Returns null when no future window exists in the next 7
 * days (treat as "never opens"). Used to render an "Available from HH:MM"
 * badge on the storefront / voice narration when an item is currently
 * off_schedule.
 */
export function nextScheduleStart(
  schedule: unknown,
  at: Date,
  timezone: string
): { time: string; day_offset: number; weekday_short: string } | null {
  if (!schedule || !Array.isArray(schedule) || schedule.length === 0) return null;

  let parts: Intl.DateTimeFormatPart[];
  try {
    parts = new Intl.DateTimeFormat('en-US', {
      timeZone: timezone || 'UTC',
      weekday: 'short',
      hour: '2-digit',
      minute: '2-digit',
      hourCycle: 'h23',
    }).formatToParts(at);
  } catch {
    parts = new Intl.DateTimeFormat('en-US', {
      timeZone: 'UTC',
      weekday: 'short',
      hour: '2-digit',
      minute: '2-digit',
      hourCycle: 'h23',
    }).formatToParts(at);
  }
  const weekdayMap: Record<string, number> = { Sun: 0, Mon: 1, Tue: 2, Wed: 3, Thu: 4, Fri: 5, Sat: 6 };
  const wdStr = parts.find(p => p.type === 'weekday')?.value ?? 'Sun';
  const today = weekdayMap[wdStr] ?? 0;
  const hh = (parts.find(p => p.type === 'hour')?.value ?? '00').padStart(2, '0');
  const mm = (parts.find(p => p.type === 'minute')?.value ?? '00').padStart(2, '0');
  const hour = hh === '24' ? '00' : hh;
  const nowMinutes = parseInt(hour, 10) * 60 + parseInt(mm, 10);

  for (let offset = 0; offset < 7; offset++) {
    const day = (today + offset) % 7;
    let dayBest: { minutes: number; start: string } | null = null;
    for (const w of schedule as ScheduleWindow[]) {
      const days = Array.isArray(w?.days) ? w.days : [];
      if (!days.includes(day)) continue;
      const start = (w.start ?? '00:00');
      const [shStr, smStr] = start.split(':');
      const sh = parseInt(shStr ?? '0', 10);
      const sm = parseInt(smStr ?? '0', 10);
      if (Number.isNaN(sh) || Number.isNaN(sm)) continue;
      const startMinutes = sh * 60 + sm;
      // Skip today's already-passed windows so we never advertise a slot
      // the customer can no longer reach.
      if (offset === 0 && startMinutes <= nowMinutes) continue;
      if (!dayBest || startMinutes < dayBest.minutes) {
        dayBest = { minutes: startMinutes, start };
      }
    }
    if (dayBest) {
      return { time: dayBest.start, day_offset: offset, weekday_short: WEEKDAY_LABELS[day] };
    }
  }
  return null;
}

/**
 * Compose the "Available from …" badge string by combining the item and
 * category schedule constraints. Returns the soonest opening across both
 * windows so callers can show one badge that respects both gates.
 */
function computeAvailableFrom(
  itemSchedule: unknown,
  catSchedule: unknown,
  at: Date,
  timezone: string
): string | undefined {
  const candidates = [itemSchedule, catSchedule]
    .map(s => nextScheduleStart(s, at, timezone))
    .filter((x): x is { time: string; day_offset: number; weekday_short: string } => x !== null);
  if (candidates.length === 0) return undefined;
  // The "later" of the two gates is the actual reopening time — both must be
  // open simultaneously for the item to become orderable.
  candidates.sort((a, b) => a.day_offset - b.day_offset || a.time.localeCompare(b.time));
  const next = candidates[candidates.length - 1];
  return next.day_offset === 0 ? next.time : `${next.weekday_short} ${next.time}`;
}

/**
 * Per-row precedence evaluator shared by isItemOrderable (single) and
 * getOrderabilityMap (batch). Keeps the rejection-reason ordering in lockstep
 * across both call paths so the AI agents and the dashboard always agree.
 *
 * Precedence: disabled → wrong_branch → out_of_stock → off_schedule.
 */
function evaluateOrderabilityRow(
  row: Record<string, unknown>,
  branchId: string | null,
  at: Date
): OrderabilityResult {
  const itemName = (row.name as string) || 'Item';

  if (row.is_available === false) {
    return { orderable: false, reason: 'disabled', message: `${itemName} is disabled` };
  }
  if (row.cat_active === false) {
    return { orderable: false, reason: 'disabled', message: `${itemName} category is disabled` };
  }

  const itemBranchId = (row.item_branch_id as string | null) ?? null;
  const catBranchId = (row.cat_branch_id as string | null) ?? null;
  if (itemBranchId && itemBranchId !== branchId) {
    return { orderable: false, reason: 'wrong_branch', message: `${itemName} is not available at this branch` };
  }
  if (catBranchId && catBranchId !== branchId) {
    return { orderable: false, reason: 'wrong_branch', message: `${itemName} is not available at this branch` };
  }
  if (branchId && row.branch_override === false) {
    return { orderable: false, reason: 'wrong_branch', message: `${itemName} is not available at this branch` };
  }

  if (row.is_in_stock === false) {
    return { orderable: false, reason: 'out_of_stock', message: `${itemName} is sold out` };
  }
  if (row.stock_count !== null && row.stock_count !== undefined && Number(row.stock_count) <= 0) {
    return { orderable: false, reason: 'out_of_stock', message: `${itemName} is sold out` };
  }

  const tz = (row.branch_tz as string) || 'UTC';
  const itemOpen = isWithinSchedule(row.item_schedule, at, tz);
  const catOpen = isWithinSchedule(row.cat_schedule, at, tz);
  if (!itemOpen || !catOpen) {
    const availableFrom = computeAvailableFrom(row.item_schedule, row.cat_schedule, at, tz);
    const msg = availableFrom
      ? `${itemName} is available from ${availableFrom}`
      : `${itemName} is not on the menu right now`;
    return { orderable: false, reason: 'off_schedule', message: msg, available_from: availableFrom };
  }

  return { orderable: true };
}

/**
 * Determines whether an item is orderable for a given branch right now (or at
 * the supplied instant). Single-query — joins menu_items, menu_categories,
 * menu_item_branch_availability, and the branch row for timezone resolution.
 *
 * Precedence: unknown_item → disabled → wrong_branch → out_of_stock →
 * off_schedule → orderable.
 *
 * `branchId` may be null — used by callers that have no branch context (e.g.
 * an "all branches" view). In that case branch-pinned items / overrides count
 * as wrong_branch.
 */
export async function isItemOrderable(
  itemId: string,
  branchId: string | null,
  at: Date = new Date()
): Promise<OrderabilityResult> {
  await initDatabase();

  // The override + timezone joins must be parameterised against the supplied
  // branchId. Drizzle's `sql` template doesn't allow branching JOIN ON
  // expressions cleanly, so we run two slightly different queries.
  const { rows } = branchId
    ? await db.execute(sql`
        SELECT
          mi.id,
          mi.name,
          mi.is_available,
          mi.is_in_stock,
          mi.stock_count,
          mi.schedule       AS item_schedule,
          mi.branch_id      AS item_branch_id,
          mc.is_active      AS cat_active,
          mc.schedule       AS cat_schedule,
          mc.branch_id      AS cat_branch_id,
          mibav.is_available AS branch_override,
          b.timezone        AS branch_tz
        FROM menu_items mi
        LEFT JOIN menu_categories mc ON mc.id = mi.category_id
        LEFT JOIN menu_item_branch_availability mibav
          ON mibav.menu_item_id = mi.id AND mibav.branch_id = ${branchId}::uuid
        LEFT JOIN branches b ON b.id = ${branchId}::uuid
        WHERE mi.id = ${itemId}::uuid
        LIMIT 1
      `)
    : await db.execute(sql`
        SELECT
          mi.id,
          mi.name,
          mi.is_available,
          mi.is_in_stock,
          mi.stock_count,
          mi.schedule       AS item_schedule,
          mi.branch_id      AS item_branch_id,
          mc.is_active      AS cat_active,
          mc.schedule       AS cat_schedule,
          mc.branch_id      AS cat_branch_id,
          NULL::boolean     AS branch_override,
          NULL::text        AS branch_tz
        FROM menu_items mi
        LEFT JOIN menu_categories mc ON mc.id = mi.category_id
        WHERE mi.id = ${itemId}::uuid
        LIMIT 1
      `);

  const row = rows[0] as Record<string, unknown> | undefined;
  if (!row) return { orderable: false, reason: 'unknown_item' };
  return evaluateOrderabilityRow(row, branchId, at);
}

/**
 * Batch version of isItemOrderable — single query for many item ids. Used by
 * AI agents to filter the menu blob in the LLM system prompt without firing
 * one DB round-trip per item. Returns a Map keyed by item id; ids that don't
 * resolve to a row are mapped to `{ orderable: false, reason: 'unknown_item' }`.
 */
export async function getOrderabilityMap(
  itemIds: string[],
  branchId: string | null,
  at: Date = new Date()
): Promise<Map<string, OrderabilityResult>> {
  const result = new Map<string, OrderabilityResult>();
  if (!itemIds || itemIds.length === 0) return result;
  await initDatabase();
  const ids = Array.from(new Set(itemIds.filter(Boolean)));
  if (ids.length === 0) return result;

  const { rows } = branchId
    ? await db.execute(sql`
        SELECT
          mi.id,
          mi.name,
          mi.is_available,
          mi.is_in_stock,
          mi.stock_count,
          mi.schedule       AS item_schedule,
          mi.branch_id      AS item_branch_id,
          mc.is_active      AS cat_active,
          mc.schedule       AS cat_schedule,
          mc.branch_id      AS cat_branch_id,
          mibav.is_available AS branch_override,
          b.timezone        AS branch_tz
        FROM menu_items mi
        LEFT JOIN menu_categories mc ON mc.id = mi.category_id
        LEFT JOIN menu_item_branch_availability mibav
          ON mibav.menu_item_id = mi.id AND mibav.branch_id = ${branchId}::uuid
        LEFT JOIN branches b ON b.id = ${branchId}::uuid
        WHERE mi.id = ANY(${ids}::uuid[])
      `)
    : await db.execute(sql`
        SELECT
          mi.id,
          mi.name,
          mi.is_available,
          mi.is_in_stock,
          mi.stock_count,
          mi.schedule       AS item_schedule,
          mi.branch_id      AS item_branch_id,
          mc.is_active      AS cat_active,
          mc.schedule       AS cat_schedule,
          mc.branch_id      AS cat_branch_id,
          NULL::boolean     AS branch_override,
          NULL::text        AS branch_tz
        FROM menu_items mi
        LEFT JOIN menu_categories mc ON mc.id = mi.category_id
        WHERE mi.id = ANY(${ids}::uuid[])
      `);

  for (const row of rows as Array<Record<string, unknown>>) {
    result.set(row.id as string, evaluateOrderabilityRow(row, branchId, at));
  }
  for (const id of ids) {
    if (!result.has(id)) result.set(id, { orderable: false, reason: 'unknown_item' });
  }
  return result;
}

/**
 * Filter a list of items down to those orderable at the given branch right
 * now. Used by the AI agents' menu-context builder so the LLM never sees
 * disabled / out-of-stock / off-schedule / wrong-branch items in its system
 * prompt. Single batch DB query under the hood.
 */
export async function filterOrderableItems<T extends { id: string }>(
  items: T[],
  branchId: string | null,
  at: Date = new Date()
): Promise<T[]> {
  if (!items || items.length === 0) return [];
  const ids = items.map(i => i.id).filter(Boolean);
  if (ids.length === 0) return [];
  const map = await getOrderabilityMap(ids, branchId, at);
  return items.filter(i => map.get(i.id)?.orderable === true);
}

// ---------------- AI order-tool validation -------------------------------

export interface RequestedOrderItem {
  /** Free-text item name as supplied by the model / caller. */
  name: string;
  quantity: number;
}

export interface ValidatedOrderItem {
  menu_item_id: string;
  name: string;
  price: number;
  quantity: number;
  /** Canonical category id for the item — used to enforce per-agent menu_category_ids scopes. */
  category_id: string | null;
}

export interface OrderRejection {
  /** The originally-requested item name (or the matched canonical name). */
  name: string;
  reason: OrderabilityReason;
  /** Caller-facing natural-language explanation, verbatim from helpers. */
  message: string;
  itemId?: string | null;
}

export interface OrderValidationResult {
  ok: boolean;
  items?: ValidatedOrderItem[];
  rejections?: OrderRejection[];
}

/**
 * Resolves a list of menu-item names (case-insensitive) within the given
 * restaurant + branch scope. When a branch is supplied, prefers
 * branch-pinned matches over global ones. Returns a map keyed by lower(name).
 */
async function resolveMenuItemsByName(
  restaurantId: string,
  branchId: string | null,
  lowerNames: string[]
): Promise<Map<string, { id: string; name: string; price: number; category_id: string | null }>> {
  const out = new Map<string, { id: string; name: string; price: number; category_id: string | null }>();
  if (!lowerNames || lowerNames.length === 0) return out;
  const names = Array.from(new Set(lowerNames));
  await initDatabase();

  const { rows } = branchId
    ? await db.execute(sql`
        SELECT DISTINCT ON (LOWER(mi.name))
               mi.id, mi.name, mi.price, mi.category_id, mi.branch_id
          FROM menu_items mi
         WHERE mi.restaurant_id = ${restaurantId}
           AND (mi.branch_id = ${branchId}::uuid OR mi.branch_id IS NULL)
           AND LOWER(mi.name) IN (${sql.join(names.map(n => sql`${n}`), sql`, `)})
         ORDER BY LOWER(mi.name), (mi.branch_id = ${branchId}::uuid) DESC NULLS LAST, mi.id
      `)
    : await db.execute(sql`
        SELECT DISTINCT ON (LOWER(mi.name))
               mi.id, mi.name, mi.price, mi.category_id, mi.branch_id
          FROM menu_items mi
         WHERE mi.restaurant_id = ${restaurantId}
           AND LOWER(mi.name) IN (${sql.join(names.map(n => sql`${n}`), sql`, `)})
         ORDER BY LOWER(mi.name), mi.id
      `);

  for (const r of rows as Array<{ id: string; name: string; price: string | number; category_id: string | null }>) {
    out.set(r.name.toLowerCase(), {
      id: r.id,
      name: r.name,
      price: typeof r.price === 'number' ? r.price : parseFloat(String(r.price)),
      category_id: r.category_id ?? null,
    });
  }
  return out;
}

/**
 * Validates every item in an AI-tool order request against the menu and
 * branch availability. Aggregates ALL failures so the caller can name every
 * problem in a single response — never half-orders. The returned items[]
 * always carry the canonical menu_item_id and the authoritative server-side
 * price (no more silent price-0 inserts when the model invents a name).
 *
 * On rejection, the returned `items` is best-effort partial; consumers
 * should only use it when `ok === true`.
 */
export async function validateOrderItemsForAI(
  restaurantId: string,
  branchId: string | null,
  requested: RequestedOrderItem[],
  at: Date = new Date()
): Promise<OrderValidationResult> {
  if (!requested || requested.length === 0) {
    return {
      ok: false,
      rejections: [{ name: '(empty)', reason: 'unknown_item', message: 'No items were specified.', itemId: null }],
    };
  }

  const lowerNames = Array.from(
    new Set(
      requested
        .map(r => (r.name ?? '').trim().toLowerCase())
        .filter(Boolean)
    )
  );
  const resolved = await resolveMenuItemsByName(restaurantId, branchId, lowerNames);
  const ids = Array.from(new Set(Array.from(resolved.values()).map(r => r.id)));
  const orderMap = ids.length > 0
    ? await getOrderabilityMap(ids, branchId, at)
    : new Map<string, OrderabilityResult>();

  const validated: ValidatedOrderItem[] = [];
  const rejections: OrderRejection[] = [];

  for (const req of requested) {
    const rawName = (req.name ?? '').trim();
    const lname = rawName.toLowerCase();
    const qty = Math.max(1, Math.floor(Number(req.quantity) || 1));
    if (!rawName) {
      rejections.push({ name: '(unnamed)', reason: 'unknown_item', message: 'An item without a name was requested.', itemId: null });
      continue;
    }
    const row = resolved.get(lname);
    if (!row) {
      rejections.push({
        name: rawName,
        reason: 'unknown_item',
        message: `"${rawName}" isn't on our menu`,
        itemId: null,
      });
      continue;
    }
    const ord = orderMap.get(row.id) ?? { orderable: false, reason: 'unknown_item' as OrderabilityReason };
    if (!ord.orderable) {
      rejections.push({
        name: row.name,
        reason: ord.reason ?? 'unknown_item',
        message: ord.message ?? `${row.name} is unavailable`,
        itemId: row.id,
      });
      continue;
    }
    validated.push({
      menu_item_id: row.id,
      name: row.name,
      price: row.price,
      quantity: qty,
      category_id: row.category_id ?? null,
    });
  }

  if (rejections.length > 0) {
    return { ok: false, rejections, items: validated };
  }
  return { ok: true, items: validated };
}

/**
 * Joins rejection messages into a single caller-facing sentence so the
 * model can read it back to the customer without paraphrasing the wrong
 * thing. Names every offender so customers know what to swap.
 */
export function buildRejectionMessage(rejections: OrderRejection[]): string {
  const messages = rejections.map(r => r.message).filter(Boolean);
  if (messages.length === 0) return "I'm sorry — I couldn't place that order. Could you choose something else from the menu?";
  let joined: string;
  if (messages.length === 1) {
    joined = messages[0];
  } else if (messages.length === 2) {
    joined = `${messages[0]} and ${messages[1]}`;
  } else {
    const last = messages[messages.length - 1];
    joined = `${messages.slice(0, -1).join(', ')}, and ${last}`;
  }
  return `I'm sorry — ${joined}. Would you like to choose something else from the menu?`;
}

/** Short, customer-friendly label per rejection reason for UI badges. */
export function rejectionReasonLabel(reason: OrderabilityReason): string {
  switch (reason) {
    case 'out_of_stock': return 'Out of stock';
    case 'off_schedule': return 'Not available right now';
    case 'unknown_item': return 'Not on our menu';
    case 'disabled': return 'Currently unavailable';
    case 'wrong_branch': return 'Not at this branch';
    default: return 'Unavailable';
  }
}

export interface RejectionPayloadItem {
  name: string;
  reason: OrderabilityReason;
  reasonLabel: string;
  message: string;
  itemId: string | null;
}

export interface RejectionPayload {
  /** The same single-sentence string returned by buildRejectionMessage(). */
  message: string;
  items: RejectionPayloadItem[];
}

/**
 * Structured rejection payload so the chat widget can render one card
 * per offender (icon + reason badge + message) instead of a single wall
 * of text. The `message` field stays identical to buildRejectionMessage
 * so existing surfaces (voice, WhatsApp, the LLM context) keep working
 * when they only consume the string.
 */
export function buildRejectionPayload(rejections: OrderRejection[]): RejectionPayload {
  return {
    message: buildRejectionMessage(rejections),
    items: rejections.map(r => ({
      name: r.name,
      reason: r.reason,
      reasonLabel: rejectionReasonLabel(r.reason),
      message: r.message,
      itemId: r.itemId ?? null,
    })),
  };
}

/**
 * Structured server log line for AI order rejections so operators can see
 * WHY an order was refused, not just that it was. One line per offender.
 */
export function logAIOrderRejections(
  rejections: OrderRejection[],
  context: { restaurantId?: string | null; branchId?: string | null; sessionId?: string | null }
): void {
  for (const r of rejections) {
    log.warn(
      `[AI/Order] rejected: itemId=${r.itemId ?? 'null'} name="${r.name}" reason=${r.reason} branch=${context.branchId ?? 'null'} session=${context.sessionId ?? 'null'} restaurant=${context.restaurantId ?? 'null'}`
    );
  }
}

// ---------------- Branch availability override CRUD ----------------

export async function listBranchAvailability(itemId: string, restaurantId: string) {
  await initDatabase();
  // Confirm item belongs to restaurant before exposing override rows.
  const { rows: own } = await db.execute(sql`
    SELECT id FROM menu_items WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
  `);
  if (!own[0]) throw new NotFoundError('Menu item');
  const { rows } = await db.execute(sql`
    SELECT mibav.menu_item_id, mibav.branch_id, mibav.is_available,
           b.name AS branch_name
      FROM menu_item_branch_availability mibav
      JOIN branches b ON b.id = mibav.branch_id
     WHERE mibav.menu_item_id = ${itemId}::uuid
  `);
  return rows;
}

export async function upsertBranchAvailability(
  itemId: string,
  branchId: string,
  restaurantId: string,
  isAvailable: boolean,
  actor?: MenuAuditActor,
) {
  await initDatabase();
  // Item must belong to the restaurant; branch must belong too.
  const { rows: ok } = await db.execute(sql`
    SELECT mi.id
      FROM menu_items mi
      JOIN branches b ON b.restaurant_id = mi.restaurant_id
     WHERE mi.id = ${itemId}::uuid
       AND mi.restaurant_id = ${restaurantId}
       AND b.id = ${branchId}::uuid
     LIMIT 1
  `);
  if (!ok[0]) throw new NotFoundError('Menu item or branch');

  // Capture the prior override (if any) so we can include the before-value
  // in the audit row. Missing rows are recorded as before=null which the
  // history view renders as "no override".
  const { rows: priorRows } = await db.execute(sql`
    SELECT is_available FROM menu_item_branch_availability
     WHERE menu_item_id = ${itemId}::uuid AND branch_id = ${branchId}::uuid
  `);
  const priorBefore =
    priorRows[0] === undefined
      ? null
      : (priorRows[0] as { is_available: boolean }).is_available;

  const { rows } = await db.execute(sql`
    INSERT INTO menu_item_branch_availability (menu_item_id, branch_id, is_available, updated_at)
    VALUES (${itemId}::uuid, ${branchId}::uuid, ${isAvailable}, NOW())
    ON CONFLICT (menu_item_id, branch_id)
    DO UPDATE SET is_available = EXCLUDED.is_available, updated_at = NOW()
    RETURNING menu_item_id, branch_id, is_available
  `);

  if (priorBefore !== isAvailable) {
    await writeMenuAuditLog({
      restaurantId,
      branchId,
      menuItemId: itemId,
      action: 'branch_availability_set',
      field: 'is_available',
      before: priorBefore,
      after: isAvailable,
      actor,
    });
  }

  return rows[0];
}

export async function deleteBranchAvailability(
  itemId: string,
  branchId: string,
  restaurantId: string,
  actor?: MenuAuditActor,
) {
  await initDatabase();
  // Same ownership guard as upsert — protects cross-restaurant access via
  // override deletion.
  const { rows: ok } = await db.execute(sql`
    SELECT id FROM menu_items WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
  `);
  if (!ok[0]) throw new NotFoundError('Menu item');
  // Capture the override we are about to remove so the audit trail records
  // what the override was before it was cleared (after = null = "no override").
  const { rows: priorRows } = await db.execute(sql`
    SELECT is_available FROM menu_item_branch_availability
     WHERE menu_item_id = ${itemId}::uuid AND branch_id = ${branchId}::uuid
  `);
  const result = await db.execute(sql`
    DELETE FROM menu_item_branch_availability
     WHERE menu_item_id = ${itemId}::uuid AND branch_id = ${branchId}::uuid
  `);
  if ((result.rowCount ?? 0) > 0) {
    await writeMenuAuditLog({
      restaurantId,
      branchId,
      menuItemId: itemId,
      action: 'branch_availability_clear',
      field: 'is_available',
      before: (priorRows[0] as { is_available: boolean } | undefined)?.is_available ?? null,
      after: null,
      actor,
    });
  }
}

// ============================================================================
// Task #299: menu availability audit log
// ============================================================================

export interface MenuAuditActor {
  userId?: string | null;
  email?: string | null;
}

interface WriteMenuAuditLogInput {
  restaurantId: string;
  branchId?: string | null;
  menuItemId?: string | null;
  menuCategoryId?: string | null;
  action: 'item_update' | 'category_update' | 'branch_availability_set' | 'branch_availability_clear';
  field: string;
  before: unknown;
  after: unknown;
  actor?: MenuAuditActor;
}

/**
 * Best-effort audit writer. Failures are logged and swallowed — the audit
 * trail must never block a legitimate menu mutation, which is why this runs
 * after the underlying UPDATE succeeds rather than inside its transaction.
 */
async function writeMenuAuditLog(input: WriteMenuAuditLogInput): Promise<void> {
  try {
    await db.execute(sql`
      INSERT INTO menu_audit_log (
        restaurant_id, branch_id, menu_item_id, menu_category_id,
        actor_user_id, actor_email, action, field, before_value, after_value
      ) VALUES (
        ${input.restaurantId}::uuid,
        ${input.branchId ?? null},
        ${input.menuItemId ?? null},
        ${input.menuCategoryId ?? null},
        ${input.actor?.userId ?? null},
        ${input.actor?.email ?? null},
        ${input.action},
        ${input.field},
        ${JSON.stringify(input.before ?? null)}::jsonb,
        ${JSON.stringify(input.after ?? null)}::jsonb
      )
    `);
  } catch (err) {
    log.error({ err }, 'menu_audit_log insert failed');
  }
}

/** Cheap, structural equality good enough for our before/after diff. */
function deepEqual(a: unknown, b: unknown): boolean {
  if (a === b) return true;
  if (a == null || b == null) return a == null && b == null;
  if (typeof a !== typeof b) return false;
  if (typeof a !== 'object') return false;
  try {
    return JSON.stringify(a) === JSON.stringify(b);
  } catch {
    return false;
  }
}

/**
 * Recent audit entries for a single menu item (and its category, so schedule
 * changes that gate the item are surfaced too). Used by the item history
 * drawer in the dashboard and the `/api/menu/items/:id/audit-log` endpoint.
 */
export async function listMenuAuditLogForItem(
  itemId: string,
  restaurantId: string,
  limit = 50,
) {
  await initDatabase();
  // Restrict to the requesting restaurant — no cross-tenant snooping. We
  // also surface category changes for the item's category so a user can see
  // why their item went off the menu when the schedule lives on the parent.
  const { rows: ownRows } = await db.execute(sql`
    SELECT category_id FROM menu_items
     WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
  `);
  if (!ownRows[0]) throw new NotFoundError('Menu item');
  const categoryId = (ownRows[0] as { category_id: string | null }).category_id;

  const { rows } = await db.execute(sql`
    SELECT id, restaurant_id, branch_id, menu_item_id, menu_category_id,
           actor_user_id, actor_email, action, field,
           before_value, after_value, created_at
      FROM menu_audit_log
     WHERE restaurant_id = ${restaurantId}::uuid
       AND (
         menu_item_id = ${itemId}::uuid
         OR (${categoryId}::uuid IS NOT NULL AND menu_category_id = ${categoryId}::uuid)
       )
     ORDER BY created_at DESC
     LIMIT ${limit}
  `);
  return rows;
}

// ---------------- Low-stock dashboard widget (Task #305) ------------------

export interface LowStockRow {
  id: string;
  name: string;
  category_name: string | null;
  stock_count: number | null;
  low_stock_threshold: number | null;
  daily_reset_count: number | null;
  auto_disabled_at_zero: boolean;
  is_in_stock: boolean;
  last_low_stock_alert_at: string | null;
}

/**
 * Returns every menu item for the restaurant currently considered "low" —
 * either at/under its low_stock_threshold (and a threshold is configured) or
 * auto-disabled by hitting zero. Used by the dashboard low-stock widget so
 * owners can see and restock without scanning the full menu page.
 */
export async function listLowStockItems(restaurantId: string): Promise<LowStockRow[]> {
  await initDatabase();
  /* raw: SELECT mi.id, name, ... FROM menu_items mi LEFT JOIN menu_categories mc
     WHERE restaurant_id = $1 AND ((low_stock_threshold IS NOT NULL AND stock_count IS NOT NULL
     AND stock_count <= low_stock_threshold) OR auto_disabled_at_zero = true)
     ORDER BY auto_disabled_at_zero DESC, stock_count ASC, name ASC */
  const { rows } = await db.execute(sql`
    SELECT mi.id,
           mi.name,
           mc.name AS category_name,
           mi.stock_count,
           mi.low_stock_threshold,
           mi.daily_reset_count,
           mi.auto_disabled_at_zero,
           mi.is_in_stock,
           mi.last_low_stock_alert_at
      FROM menu_items mi
      LEFT JOIN menu_categories mc ON mc.id = mi.category_id
     WHERE mi.restaurant_id = ${restaurantId}
       AND (
         (mi.low_stock_threshold IS NOT NULL
           AND mi.stock_count IS NOT NULL
           AND mi.stock_count <= mi.low_stock_threshold)
         OR mi.auto_disabled_at_zero = true
       )
     ORDER BY mi.auto_disabled_at_zero DESC,
              mi.stock_count ASC NULLS LAST,
              mi.name ASC
  `);
  return rows as unknown as LowStockRow[];
}

/**
 * One-click restock for the dashboard widget. Sets stock_count to the
 * supplied target (or, when omitted, the item's daily_reset_count) and
 * mirrors the daily-reset clearing logic: re-enables in-stock, clears the
 * auto_disabled_at_zero flag and the low-stock cooldown so a future
 * crossing fires again. Throws ValidationError when no target can be
 * resolved (i.e. caller didn't pass one and the item has no daily_reset_count).
 */
export async function restockItem(
  itemId: string,
  restaurantId: string,
  opts: { target?: number | null } = {}
) {
  await initDatabase();

  const { rows: existing } = await db.execute(sql`
    SELECT id, daily_reset_count
      FROM menu_items
     WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
  `);
  const current = existing[0] as { id: string; daily_reset_count: number | null } | undefined;
  if (!current) throw new NotFoundError('Menu item');

  const explicit = opts.target;
  const target = explicit != null ? explicit : current.daily_reset_count;
  if (target == null) {
    throw new ValidationError(
      'No restock target supplied and this item has no daily reset count configured.'
    );
  }
  if (!Number.isFinite(target) || target < 0 || !Number.isInteger(target)) {
    throw new ValidationError('Restock target must be a non-negative integer.');
  }

  // Mirror the daily-reset semantics for is_in_stock: only auto-flip back
  // to true when the row was auto-disabled by hitting zero. A manually
  // toggled "off" must stay off across a restock — the operator can flip
  // it back themselves on the menu page.
  const { rows } = await db.execute(sql`
    UPDATE menu_items
       SET stock_count = ${target},
           is_in_stock = CASE WHEN auto_disabled_at_zero THEN true ELSE is_in_stock END,
           auto_disabled_at_zero = false,
           last_low_stock_alert_at = NULL,
           updated_at = NOW()
     WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
     RETURNING *
  `);
  return rows[0];
}

// ---------------- Atomic stock decrement (used by order-create flows) -----

export interface StockDecrementInput {
  menu_item_id?: string | null;
  name?: string | null;
  quantity: number;
}

/**
 * Per-item alert payload returned by `decrementStockForOrder` when an item
 * crosses its `low_stock_threshold` for the first time within the 24h
 * cooldown window, OR when stock_count drops to zero (sold-out).
 * Callers should enqueue email + in-app notifications AFTER the order
 * transaction commits — see `notifyLowStock`.
 *
 * Task #298: `kind` distinguishes between the two crossings so the
 * notification layer can pick the right template / title.
 */
export interface StockDecrementAlert {
  itemId: string;
  itemName: string;
  stockRemaining: number;
  threshold: number;
  kind: 'low_stock' | 'sold_out';
}

type Tx = Parameters<Parameters<typeof db.transaction>[0]>[0];

/**
 * Atomically reserves stock for the items in a new order. Run INSIDE the
 * order-insert transaction so a sold-out race rolls back the order. For each
 * item with finite stock_count: locks the row, decrements stock_count by
 * quantity, and flips is_in_stock=false when the stock hits zero. Items with
 * NULL stock_count (unlimited) are still gated by is_in_stock.
 *
 * Items are matched by menu_item_id when supplied; otherwise by
 * case-insensitive name within the restaurant + (branch | global). Names that
 * don't resolve to a menu_items row are skipped silently — the price-lookup
 * already does the same so the order can still be placed for off-menu rows.
 *
 * Task #295: also detects when stock_count crosses `low_stock_threshold`
 * (rate-limited by `last_low_stock_alert_at` to once per 24h per item) and
 * returns an alert payload. Callers fire the email + notification AFTER the
 * transaction commits so a rolled-back order never produces a phantom alert.
 * The DB row's `last_low_stock_alert_at` is stamped inside the same tx,
 * which (combined with the FOR UPDATE row lock) means concurrent
 * decrements correctly funnel down to a single alert per cooldown window.
 *
 * Sets `auto_disabled_at_zero=true` whenever stock hits zero so the daily
 * reset worker can re-enable that exact case (manual "off" stays off).
 */
export async function decrementStockForOrder(
  tx: Tx,
  restaurantId: string,
  branchId: string | null,
  items: StockDecrementInput[]
): Promise<StockDecrementAlert[]> {
  if (!items || items.length === 0) return [];

  // Task #298: load the restaurant-level fallback threshold so items with
  // low_stock_threshold IS NULL still trigger a "running low" alert. A
  // value of 0 means "no fallback" — only items that opt-in via their own
  // threshold will fire low-stock alerts.
  let restaurantDefaultThreshold = 0;
  {
    const { rows: rsRows } = await tx.execute(sql`
      SELECT default_low_stock_threshold FROM restaurants WHERE id = ${restaurantId}
    `);
    const v = (rsRows[0] as { default_low_stock_threshold?: number | null } | undefined)?.default_low_stock_threshold;
    restaurantDefaultThreshold = v == null ? 0 : Number(v);
  }

  // Aggregate quantities per (id-or-name) so the same item appearing twice in
  // a cart only consumes one row of stock.
  const byId = new Map<string, number>();
  const byName = new Map<string, number>();
  for (const it of items) {
    const qty = Math.max(0, Math.floor(Number(it.quantity) || 0));
    if (qty <= 0) continue;
    if (it.menu_item_id) {
      byId.set(it.menu_item_id, (byId.get(it.menu_item_id) ?? 0) + qty);
    } else if (it.name) {
      const key = String(it.name).trim().toLowerCase();
      if (key) byName.set(key, (byName.get(key) ?? 0) + qty);
    }
  }

  // Resolve name-matched rows up-front so we can reuse the same FOR UPDATE
  // path below.
  if (byName.size > 0) {
    const names = Array.from(byName.keys());
    const branchClause = branchId
      ? sql`AND (mi.branch_id = ${branchId}::uuid OR mi.branch_id IS NULL)`
      : sql``;
    const { rows } = await tx.execute(sql`
      SELECT id, LOWER(name) AS lname FROM menu_items mi
       WHERE restaurant_id = ${restaurantId}
         ${branchClause}
         AND LOWER(name) IN (${sql.join(names.map(n => sql`${n}`), sql`, `)})
    `);
    for (const r of rows as Array<{ id: string; lname: string }>) {
      const qty = byName.get(r.lname);
      if (qty == null) continue;
      byId.set(r.id, (byId.get(r.id) ?? 0) + qty);
    }
  }

  const alerts: StockDecrementAlert[] = [];

  for (const [itemId, qty] of byId.entries()) {
    // Lock the row first so we can return precise diagnostics. The atomic
    // UPDATE below still guards against TOCTOU because it only matches when
    // stock is sufficient.
    const { rows: [locked] } = await tx.execute(sql`
      SELECT name, is_in_stock, stock_count, low_stock_threshold,
             last_low_stock_alert_at, last_sold_out_alert_at
        FROM menu_items
       WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
       FOR UPDATE
    `);
    if (!locked) continue; // off-menu row — never resolved, skip silently
    const row = locked as {
      name: string;
      is_in_stock: boolean;
      stock_count: number | null;
      low_stock_threshold: number | null;
      last_low_stock_alert_at: Date | string | null;
      last_sold_out_alert_at: Date | string | null;
    };
    if (row.is_in_stock === false) {
      throw new ValidationError(`Sorry, ${row.name} is sold out`);
    }
    if (row.stock_count == null) continue; // unlimited — nothing to decrement

    const before = Number(row.stock_count);
    if (before < qty) {
      throw new ValidationError(
        before === 0
          ? `Sorry, ${row.name} is sold out`
          : `Sorry, only ${before} ${row.name} remaining`
      );
    }
    const after = before - qty;
    // Task #298: per-item threshold takes precedence; otherwise fall back
    // to the restaurant-wide default. Both `null` and `0` mean "no
    // low-stock alert" (sold-out alerts still fire independently below).
    const effectiveThreshold = row.low_stock_threshold != null
      ? Number(row.low_stock_threshold)
      : restaurantDefaultThreshold;
    // Fire a low-stock alert exactly when this decrement crosses (or sits
    // exactly at) the threshold for the first time AND the 24h cooldown
    // has elapsed. The pre-decrement check on `before > threshold`
    // ensures one alert per crossing — repeat orders below the threshold
    // do not re-fire until the cooldown lapses (and reset clears it).
    const lastLowAlert = row.last_low_stock_alert_at
      ? new Date(row.last_low_stock_alert_at as string).getTime()
      : 0;
    const lowCooldownOk = lastLowAlert === 0 || (Date.now() - lastLowAlert) > 24 * 60 * 60 * 1000;
    const willLowAlert = effectiveThreshold > 0
      && before > effectiveThreshold
      && after <= effectiveThreshold
      && after > 0
      && lowCooldownOk;

    // Task #298: sold-out alert fires whenever stock crosses 0, regardless
    // of whether a threshold is configured. Separate cooldown column so
    // it doesn't collide with the low-stock cooldown.
    const lastSoldAlert = row.last_sold_out_alert_at
      ? new Date(row.last_sold_out_alert_at as string).getTime()
      : 0;
    const soldCooldownOk = lastSoldAlert === 0 || (Date.now() - lastSoldAlert) > 24 * 60 * 60 * 1000;
    const willSoldOutAlert = before > 0 && after <= 0 && soldCooldownOk;

    await tx.execute(sql`
      UPDATE menu_items
         SET stock_count = ${after},
             is_in_stock = CASE WHEN ${after} <= 0 THEN false ELSE is_in_stock END,
             auto_disabled_at_zero = CASE WHEN ${after} <= 0 THEN true ELSE auto_disabled_at_zero END,
             last_low_stock_alert_at = CASE WHEN ${willLowAlert} THEN NOW() ELSE last_low_stock_alert_at END,
             last_sold_out_alert_at  = CASE WHEN ${willSoldOutAlert} THEN NOW() ELSE last_sold_out_alert_at END,
             updated_at = NOW()
       WHERE id = ${itemId}::uuid AND restaurant_id = ${restaurantId}
    `);

    if (willSoldOutAlert) {
      alerts.push({
        itemId,
        itemName: row.name,
        stockRemaining: 0,
        threshold: effectiveThreshold,
        kind: 'sold_out',
      });
    } else if (willLowAlert) {
      alerts.push({
        itemId,
        itemName: row.name,
        stockRemaining: after,
        threshold: effectiveThreshold,
        kind: 'low_stock',
      });
    }
  }

  return alerts;
}