import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { bindValue } from '@server/db/bind';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { dispatchWebhook } from './webhooks.service';
import * as loyalty from './loyalty.service';
import { applyCoupon, enforceStackingPolicy, recordRedemption, type ApplyCouponItem } from './coupons.service';
import { composeOrderPricing, computeGiftCardOffset, TAX_RATE as PRICING_TAX_RATE } from './pricing.service';
import { decrementStockForOrder, getOrderabilityMap, type StockDecrementAlert } from './menu.service';
import { notifyLowStock } from './email/notify';

import { isOpenNow } from '@server/utils/hours';
import { childLogger } from '@server/logger';
import { redactPhone } from '@server/logger/redact';
const log = childLogger('svc.storefront');

export interface StorefrontPublicProfile {
  branch: Record<string, unknown>;
  restaurant: Record<string, unknown>;
  feature_enabled: boolean;
}

/**
 * Resolves a public storefront branch by restaurant + branch slug. Returns
 * null when the slugs don't match. Caller must check `feature_enabled` and
 * `branch.storefront_enabled` to decide between 404 / 402 / 503.
 */
export async function getStorefrontProfile(
  restaurantSlug: string,
  branchSlug: string
): Promise<StorefrontPublicProfile | null> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT
      b.id              AS b_id,
      b.restaurant_id   AS b_restaurant_id,
      b.name            AS b_name,
      b.address         AS b_address,
      b.phone           AS b_phone,
      b.hours           AS b_hours,
      b.timezone        AS b_timezone,
      b.slug            AS b_slug,
      b.storefront_enabled,
      b.accepts_dine_in,
      b.accepts_takeaway,
      b.accepts_delivery,
      b.min_order_value,
      b.qr_style_json,
      b.storefront_message,
      r.id              AS r_id,
      r.name            AS r_name,
      r.slug            AS r_slug,
      r.logo_url        AS r_logo_url,
      r.currency        AS r_currency,
      r.cuisine_type    AS r_cuisine_type,
      r.description     AS r_description,
      r.phone           AS r_phone,
      r.address         AS r_address,
      EXISTS (
        SELECT 1 FROM subscriptions s
        JOIN plans p ON p.id = s.plan_id
        WHERE s.restaurant_id = r.id
          AND s.status IN ('active','trial')
          AND p.features ? 'storefront'
      ) AS feature_enabled
    FROM branches b
    JOIN restaurants r ON r.id = b.restaurant_id
    WHERE r.slug = ${restaurantSlug}
      AND b.slug = ${branchSlug}
      AND b.is_active = true
    LIMIT 1
  `);
  const row = rows[0] as Record<string, unknown> | undefined;
  if (!row) return null;
  return {
    feature_enabled: row.feature_enabled === true,
    branch: {
      id: row.b_id,
      restaurant_id: row.b_restaurant_id,
      name: row.b_name,
      address: row.b_address,
      phone: row.b_phone,
      hours: row.b_hours ?? {},
      timezone: row.b_timezone,
      slug: row.b_slug,
      storefront_enabled: row.storefront_enabled === true,
      accepts_dine_in: row.accepts_dine_in === true,
      accepts_takeaway: row.accepts_takeaway === true,
      accepts_delivery: row.accepts_delivery === true,
      min_order_value: Number(row.min_order_value ?? 0),
      qr_style_json: row.qr_style_json ?? {},
      storefront_message: row.storefront_message,
    },
    restaurant: {
      id: row.r_id,
      name: row.r_name,
      slug: row.r_slug,
      logo_url: row.r_logo_url,
      currency: row.r_currency ?? 'USD',
      cuisine_type: row.r_cuisine_type,
      description: row.r_description,
      phone: row.r_phone,
      address: row.r_address,
    },
  };
}

export interface StorefrontMenuCategory {
  id: string;
  name: string;
  description: string | null;
  display_order: number;
  items: Record<string, unknown>[];
}

export async function getStorefrontMenu(restaurantId: string, branchId: string): Promise<StorefrontMenuCategory[]> {
  await initDatabase();
  const { rows: cats } = await db.execute(sql`
    SELECT id, name, display_order
    FROM menu_categories
    WHERE restaurant_id = ${restaurantId}
      AND (branch_id = ${branchId} OR branch_id IS NULL)
      AND COALESCE(is_active, true) = true
    ORDER BY display_order ASC, name ASC
  `);

  // Drop items that are explicitly disabled at this branch via the
  // mibav override — those should NOT show up on the storefront for this
  // branch (they are not on the menu here at all). Items that are merely
  // out-of-stock or off-schedule are still returned so the UI can render
  // a "Sold out" / "Available from HH:MM" badge instead of silently
  // hiding them.
  const { rows: items } = await db.execute(sql`
    SELECT mi.id, mi.name, mi.description, mi.price, mi.image_url, mi.is_available,
           mi.dietary_tags, mi.is_veg, mi.spice_level, mi.calories, mi.prep_time_minutes,
           mi.category_id, mi.sort_order
    FROM menu_items mi
    LEFT JOIN menu_item_branch_availability mibav
      ON mibav.menu_item_id = mi.id AND mibav.branch_id = ${branchId}::uuid
    WHERE mi.restaurant_id = ${restaurantId}
      AND (mi.branch_id = ${branchId} OR mi.branch_id IS NULL)
      AND mi.is_available = true
      AND COALESCE(mibav.is_available, true) = true
    ORDER BY mi.sort_order ASC, mi.name ASC
  `);

  // Compute per-item orderability for the current branch & moment so
  // each item carries its own badge data. Single batched query.
  const itemIdList = (items as Record<string, unknown>[]).map((r) => r.id as string);
  const orderabilityMap = itemIdList.length > 0
    ? await getOrderabilityMap(itemIdList, branchId)
    : new Map<string, { orderable: boolean; reason?: string; message?: string; available_from?: string }>();

  let modifiersByItem: Record<string, unknown[]> = {};
  if (items.length > 0) {
    const itemIds = (items as Record<string, unknown>[]).map((r) => r.id as string);
    const { rows: groups } = await db.execute(sql`
      SELECT mg.id, mg.menu_item_id, mg.name, mg.is_required,
             mg.min_selections AS min_select, mg.max_selections AS max_select,
             mg.display_order,
             COALESCE(json_agg(
               json_build_object('id', mo.id, 'name', mo.name, 'price_delta', mo.price_delta,
                 'is_default', mo.is_default)
               ORDER BY mo.display_order, mo.name
             ) FILTER (WHERE mo.id IS NOT NULL AND mo.is_available = true), '[]'::json) 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.map((id) => sql`${id}::uuid`), sql`, `)})
        AND mg.restaurant_id = ${restaurantId}
      GROUP BY mg.id
      ORDER BY mg.display_order
    `);
    modifiersByItem = {};
    for (const g of groups as Record<string, unknown>[]) {
      const itemId = g.menu_item_id as string;
      (modifiersByItem[itemId] ||= []).push(g);
    }
  }

  const itemsByCat: Record<string, Record<string, unknown>[]> = {};
  for (const it of items as Record<string, unknown>[]) {
    const id = (it.category_id as string) ?? '_uncategorized';
    const ord = orderabilityMap.get(it.id as string);
    // Defensive filter: hide wrong_branch rows even if the mibav join
    // missed them (e.g. category-pinned items). Customers should never
    // see items that aren't part of this branch's menu.
    if (ord && ord.reason === 'wrong_branch') continue;
    (itemsByCat[id] ||= []).push({
      ...it,
      modifier_groups: modifiersByItem[it.id as string] ?? [],
      orderable: ord ? ord.orderable : true,
      unavailable_reason: ord && !ord.orderable ? ord.reason ?? null : null,
      unavailable_message: ord && !ord.orderable ? ord.message ?? null : null,
      available_from: ord?.available_from ?? null,
    });
  }

  const out: StorefrontMenuCategory[] = (cats as Record<string, unknown>[]).map((c) => ({
    id: c.id as string,
    name: c.name as string,
    description: (c.description as string) ?? null,
    display_order: Number(c.display_order ?? 0),
    items: itemsByCat[c.id as string] ?? [],
  })).filter((c) => c.items.length > 0);

  if (itemsByCat._uncategorized?.length) {
    out.push({ id: '_uncategorized', name: 'Other', description: null, display_order: 9999, items: itemsByCat._uncategorized });
  }
  return out;
}

export interface StorefrontOrderInput {
  customer_name: string;
  customer_phone: string;
  customer_email?: string;
  delivery_type: 'dine-in' | 'takeaway' | 'delivery';
  table_id?: string | null;
  table_number?: string | null;
  delivery_address_json?: Record<string, unknown> | null;
  special_instructions?: string | null;
  points_to_redeem?: number;
  items: Array<{
    menu_item_id: string;
    quantity: number;
    selected_modifier_option_ids?: string[];
    note?: string;
  }>;
  gift_card_code?: string | null;
  coupon_code?: string | null;
}

export interface StorefrontOrderResult {
  id: string;
  order_number: number;
  status: string;
  total: number;
  subtotal: number;
  tax: number;
  branch_id: string;
  loyalty_points_redeemed?: number;
  loyalty_points_earned?: number;
  loyalty_discount?: number;
  gift_card_applied?: number;
  gift_card_remaining_balance?: number;
  discount_amount?: number;
  coupon_code?: string | null;
}

// Tax rate now lives in pricing.service (imported as PRICING_TAX_RATE) so
// every order channel computes tax through the same shared composer.

/**
 * Validates + prices the cart server-side using authoritative menu prices, then
 * inserts an order with `source='storefront'` and `channel='storefront'`.
 * Customer is upserted by (restaurant_id, phone).
 */
export async function createStorefrontOrder(
  restaurantSlug: string,
  branchSlug: string,
  input: StorefrontOrderInput
): Promise<StorefrontOrderResult> {
  await initDatabase();

  const profile = await getStorefrontProfile(restaurantSlug, branchSlug);
  if (!profile) throw new NotFoundError('Storefront');
  if (!profile.feature_enabled) throw new ValidationError('Storefront is not available for this restaurant');
  const branch = profile.branch;
  if (!branch.storefront_enabled) throw new ValidationError('Storefront is currently disabled for this branch');

  // Opening hours check — enforced server-side so UI-bypass is not possible.
  // null means no hours configured → treat as always open.
  const openStatus = isOpenNow(
    branch.hours as Record<string, unknown> | null | undefined,
    branch.timezone as string | null | undefined,
  );
  if (openStatus === false) {
    throw new ValidationError('This branch is currently closed and not accepting orders');
  }

  // delivery_type allowed?
  const t = input.delivery_type;
  if (t === 'dine-in' && !branch.accepts_dine_in) throw new ValidationError('Dine-in orders are not accepted at this branch');
  if (t === 'takeaway' && !branch.accepts_takeaway) throw new ValidationError('Takeaway orders are not accepted at this branch');
  if (t === 'delivery' && !branch.accepts_delivery) throw new ValidationError('Delivery is not available at this branch');
  if (t === 'delivery' && !input.delivery_address_json) throw new ValidationError('Delivery address is required');
  if (t === 'dine-in' && !input.table_id && !input.table_number) {
    throw new ValidationError('Table is required for dine-in orders');
  }

  if (!Array.isArray(input.items) || input.items.length === 0) {
    throw new ValidationError('Cart is empty');
  }

  const restaurantId = branch.restaurant_id as string;
  const branchId = branch.id as string;

  // Resolve table if provided
  let tableId: string | null = (input.table_id as string) ?? null;
  let tableNumber: string | null = input.table_number ?? null;
  if (tableId) {
    const { rows: tRows } = await db.execute(sql`
      SELECT id, table_number FROM restaurant_tables
      WHERE id = ${tableId} AND branch_id = ${branchId} AND is_active = true
    `);
    if (!tRows[0]) throw new ValidationError('Selected table is not available');
    tableNumber = (tRows[0] as Record<string, unknown>).table_number as string;
  } else if (tableNumber && t === 'dine-in') {
    const { rows: tRows } = await db.execute(sql`
      SELECT id FROM restaurant_tables
      WHERE branch_id = ${branchId} AND table_number = ${tableNumber} AND is_active = true
    `);
    if (!tRows[0]) throw new ValidationError(`Table "${tableNumber}" is not available at this branch`);
    tableId = (tRows[0] as Record<string, unknown>).id as string;
  }
  if (t === 'dine-in' && !tableId) {
    throw new ValidationError('A table is required for dine-in orders');
  }

  // Server-side price recompute — strictly branch-scoped
  const itemIds = Array.from(new Set(input.items.map((i) => i.menu_item_id)));
  if (itemIds.length === 0) throw new ValidationError('Cart is empty');
  const { rows: menuRows } = await db.execute(sql`
    SELECT id, name, price, category_id, is_available
    FROM menu_items
    WHERE restaurant_id = ${restaurantId}
      AND (branch_id = ${branchId} OR branch_id IS NULL)
      AND is_available = true
      AND id IN (${sql.join(itemIds.map((id) => sql`${id}::uuid`), sql`, `)})
  `);
  const menuById: Record<string, { name: string; price: number; category_id: string | null }> = {};
  for (const m of menuRows as Record<string, unknown>[]) {
    menuById[m.id as string] = {
      name: m.name as string,
      price: Number(m.price ?? 0),
      category_id: (m.category_id as string | null) ?? null,
    };
  }
  // Reject if any requested item is missing (filtered out by branch/availability)
  for (const id of itemIds) {
    if (!menuById[id]) throw new ValidationError('One or more items are no longer available');
  }

  // Modifier groups for these items, with their options — used to validate option ownership and required/min/max
  type GroupRule = { id: string; menu_item_id: string; is_required: boolean; min: number; max: number; option_ids: Set<string>; options: Record<string, { name: string; price_delta: number }> };
  const groupsByItem: Record<string, GroupRule[]> = {};
  if (itemIds.length > 0) {
    const { rows: gRows } = await db.execute(sql`
      SELECT mg.id, mg.menu_item_id, mg.is_required, mg.min_selections, mg.max_selections,
             COALESCE(json_agg(
               json_build_object('id', mo.id, 'name', mo.name, 'price_delta', mo.price_delta)
               ORDER BY mo.display_order
             ) FILTER (WHERE mo.id IS NOT NULL AND mo.is_available = true), '[]'::json) AS options
      FROM menu_modifier_groups mg
      LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
      WHERE mg.restaurant_id = ${restaurantId}
        AND mg.menu_item_id IN (${sql.join(itemIds.map((id) => sql`${id}::uuid`), sql`, `)})
      GROUP BY mg.id
    `);
    for (const g of gRows as Record<string, unknown>[]) {
      const opts = (g.options as Array<{ id: string; name: string; price_delta: number | string }>) ?? [];
      const rule: GroupRule = {
        id: g.id as string,
        menu_item_id: g.menu_item_id as string,
        is_required: g.is_required === true,
        min: Number(g.min_selections ?? 0),
        max: g.max_selections == null ? Infinity : Number(g.max_selections),
        option_ids: new Set(opts.map((o) => o.id)),
        options: Object.fromEntries(opts.map((o) => [o.id, { name: o.name, price_delta: Number(o.price_delta) }])),
      };
      (groupsByItem[rule.menu_item_id] ||= []).push(rule);
    }
  }

  const lineItems: Array<Record<string, unknown>> = [];
  let subtotal = 0;
  for (const cartItem of input.items) {
    const m = menuById[cartItem.menu_item_id];
    if (!m) throw new ValidationError('Item is no longer available');
    const qty = Math.max(1, Math.floor(Number(cartItem.quantity) || 1));

    const requestedOpts = Array.from(new Set(cartItem.selected_modifier_option_ids ?? []));
    const itemGroups = groupsByItem[cartItem.menu_item_id] ?? [];

    // Bucket requested options by group; reject options that don't belong to this item
    const optsByGroup: Record<string, string[]> = {};
    for (const optId of requestedOpts) {
      const owner = itemGroups.find((g) => g.option_ids.has(optId));
      if (!owner) throw new ValidationError(`A selected option is not valid for "${m.name}"`);
      (optsByGroup[owner.id] ||= []).push(optId);
    }
    // Enforce required + min/max per group
    for (const g of itemGroups) {
      const picks = optsByGroup[g.id] ?? [];
      if (g.is_required && picks.length < Math.max(1, g.min)) {
        throw new ValidationError(`A required option is missing for "${m.name}"`);
      }
      if (g.min > 0 && picks.length > 0 && picks.length < g.min) {
        throw new ValidationError(`Choose at least ${g.min} option(s) for "${m.name}"`);
      }
      if (Number.isFinite(g.max) && picks.length > g.max) {
        throw new ValidationError(`Choose at most ${g.max} option(s) for "${m.name}"`);
      }
    }

    let unit = m.price;
    const modifiers: Array<{ id: string; name: string; price_delta: number }> = [];
    for (const groupId of Object.keys(optsByGroup)) {
      const g = itemGroups.find((gg) => gg.id === groupId)!;
      for (const optId of optsByGroup[groupId]) {
        const opt = g.options[optId];
        unit += opt.price_delta;
        modifiers.push({ id: optId, name: opt.name, price_delta: opt.price_delta });
      }
    }
    const lineTotal = Math.round(unit * qty * 100) / 100;
    subtotal += lineTotal;
    lineItems.push({
      menu_item_id: cartItem.menu_item_id,
      name: m.name,
      quantity: qty,
      price: Math.round(unit * 100) / 100,
      modifiers,
      note: cartItem.note ?? null,
      category_id: m.category_id,
    });
  }

  subtotal = Math.round(subtotal * 100) / 100;
  if (subtotal < Number(branch.min_order_value ?? 0)) {
    throw new ValidationError(`Minimum order value is ${branch.min_order_value}`);
  }
  const couponCodeRequested = input.coupon_code && input.coupon_code.trim() ? input.coupon_code.trim() : null;

  // Upsert customer (best effort on phone)
  let customerId: string | null = null;
  try {
    const { rows: existing } = await db.execute(sql`
      SELECT id FROM customers WHERE restaurant_id = ${restaurantId} AND phone = ${input.customer_phone} LIMIT 1
    `);
    if (existing[0]) {
      customerId = (existing[0] as Record<string, unknown>).id as string;
      await db.execute(sql`
        UPDATE customers SET name = COALESCE(${input.customer_name}, name),
          email = COALESCE(${input.customer_email ?? null}, email),
          updated_at = NOW()
        WHERE id = ${customerId}
      `);
    } else {
      const { rows: created } = await db.execute(sql`
        INSERT INTO customers (restaurant_id, name, email, phone, preferences, tags)
        VALUES (${restaurantId}, ${input.customer_name},
                ${input.customer_email ?? null}, ${input.customer_phone},
                '[]'::jsonb, '[]'::jsonb)
        RETURNING id
      `);
      customerId = (created[0] as Record<string, unknown>).id as string;
    }
  } catch (err) {
    // Structured warning so storefront orders without an attached customer surface in logs
    // for downstream observability (CRM/alerting). Order intake intentionally continues —
    // failing the order because of a CRM hiccup would be worse for the customer.
    log.warn(
      {
        err,
        restaurantId,
        branchId,
        phone: redactPhone(input.customer_phone),
      },
      'storefront customer upsert failed'
    );
  }

  const requestedPoints = Math.max(0, Math.floor(Number(input.points_to_redeem ?? 0)));

  // Up-front gift-card existence check so callers see a clean error if the
  // code is bogus. The real lock + cap happens inside the tx after coupon
  // and loyalty have shrunk the taxable base — using the raw subtotal here
  // makes this purely a "does this code resolve" check.
  let giftCardPreview: { code: string } | null = null;
  if (input.gift_card_code) {
    const { previewRedeem } = await import('./gift-cards.service');
    await previewRedeem({ restaurantId, code: input.gift_card_code, amountDue: subtotal });
    giftCardPreview = { code: input.gift_card_code };
  }

  // Allocate order_number with advisory lock (mirrors orders.service).
  // Coupon, loyalty + gift card application all run INSIDE the transaction
  // so cap checks (FOR UPDATE) and the redemption inserts are atomic with
  // the order insert — preventing double-spend under concurrent requests.
  // Task #295: low-stock alerts collected by decrementStockForOrder must
  // fire AFTER the tx commits — otherwise a rolled-back order would still
  // produce a phantom "running low" notification. We close over this from
  // the tx body and dispatch in the post-commit block below.
  let stockAlerts: StockDecrementAlert[] = [];
  const inserted = await db.transaction(async (tx) => {
    await tx.execute(sql`SELECT pg_advisory_xact_lock(hashtext(${restaurantId})::bigint)`);

    // -----------------------------------------------------------------
    // Shared pipeline: subtotal → coupon → loyalty → gift card → tax → total
    // (matches orders.service.createOrder so chat / WhatsApp / storefront
    // produce identical numbers for the same cart + redemptions.)
    // -----------------------------------------------------------------

    // 1) Coupon
    let appliedCouponId: string | null = null;
    let appliedCouponCode: string | null = null;
    let couponDiscount = 0;
    let appliedCoupon: { code: string; stack_with_loyalty: boolean; stack_with_gift_card: boolean } | null = null;
    if (couponCodeRequested) {
      const applyItems: ApplyCouponItem[] = lineItems.map((li) => ({
        menu_item_id: li.menu_item_id as string,
        name: li.name as string,
        quantity: li.quantity as number,
        price: li.price as number,
        category_id: (li.category_id as string | null) ?? undefined,
      }));
      const result = await applyCoupon({
        branchId, restaurantId,
        items: applyItems,
        orderType: t,
        channel: 'storefront',
        customerId,
        code: couponCodeRequested,
        tx,
      });
      if (!result.ok) throw new ValidationError(result.message);
      couponDiscount = result.discount;
      appliedCouponId = result.coupon.id;
      appliedCouponCode = result.coupon.code;
      appliedCoupon = {
        code: result.coupon.code,
        stack_with_loyalty: result.coupon.stack_with_loyalty,
        stack_with_gift_card: result.coupon.stack_with_gift_card,
      };
    }
    const subtotalAfterCoupon = Math.max(0, Math.round((subtotal - couponDiscount) * 100) / 100);

    // 2) Loyalty — preview using POST-COUPON subtotal so point caps reflect
    //    the actual taxable base.
    let loyaltyDiscount = 0;
    let loyaltyPointsRedeemed = 0;
    if (customerId && requestedPoints > 0) {
      try {
        const preview = await loyalty.previewRedeem({
          restaurantId,
          customerId,
          pointsRequested: requestedPoints,
          subtotal: subtotalAfterCoupon,
        });
        loyaltyDiscount = preview.discount_amount;
        loyaltyPointsRedeemed = preview.allowed_points;
      } catch (err) {
        log.warn({ err }, 'storefront loyalty preview failed');
      }
    }

    // Resolve gift card settings before the policy gate so we can fail
    // fast (no balance debit) when stacking is disallowed.
    let giftCardSettings: Awaited<ReturnType<typeof import('./gift-cards.service').getSettings>> | null = null;
    if (giftCardPreview) {
      const { getSettings } = await import('./gift-cards.service');
      giftCardSettings = await getSettings(restaurantId);
    }
    enforceStackingPolicy({
      coupon: appliedCoupon,
      loyaltyApplied: loyaltyDiscount > 0,
      giftCardApplied: !!giftCardPreview,
      giftCardSettings,
    });

    // 3) Gift card — cap against post-loyalty subtotal (gift cards now
    //    reduce taxable base). Lock + ledger writes happen here so
    //    balances roll back if the order insert fails.
    const subtotalAfterLoyalty = Math.max(0, Math.round((subtotalAfterCoupon - loyaltyDiscount) * 100) / 100);
    let giftCardId: string | null = null;
    let giftCardApplied = 0;
    let giftCardRemaining = 0;
    let giftCardBalance = 0;
    if (giftCardPreview) {
      const { lockCardByCodeInTx } = await import('./gift-cards.service');
      const settings = giftCardSettings!;
      const card = await lockCardByCodeInTx(tx, restaurantId, giftCardPreview.code);
      giftCardBalance = Number(card.balance);
      giftCardApplied = computeGiftCardOffset({
        subtotalAfterLoyalty,
        cardBalance: giftCardBalance,
        redemptionRule: settings.redemption_rule,
        taxRate: PRICING_TAX_RATE,
      });
      giftCardId = card.id as string;
    }

    // 4) Compose authoritative subtotal/tax/total from the three stages.
    const pricing = composeOrderPricing({
      subtotal,
      couponDiscount,
      loyaltyDiscount,
      giftCardApplied,
    });
    // Persist the PRE-discount subtotal so the dashboard + email
    // breakdown sums cleanly to total when each discount line is
    // subtracted separately. (Each discount component is stored in
    // its own column so line items render unambiguously.)
    const persistedSubtotal = pricing.subtotal;
    const tax = pricing.tax;
    const total = pricing.total;
    giftCardApplied = pricing.giftCardApplied;
    if (giftCardId) {
      giftCardRemaining = Math.round((giftCardBalance - giftCardApplied) * 100) / 100;
    }
    const discountAmount = pricing.couponDiscount;

    const { rows: [numRow] } = await tx.execute(sql`
      SELECT COALESCE(MAX(order_number), 0) + 1 AS next_num
      FROM orders WHERE restaurant_id = ${restaurantId}
    `);
    const nextNumber = parseInt((numRow as { next_num: string })?.next_num ?? '1', 10);
    const { rows: [order] } = await tx.execute(sql`
      INSERT INTO orders (
        restaurant_id, branch_id, customer_id, customer_name, customer_phone, customer_email,
        items, status, channel, source, delivery_type, table_number, table_id,
        subtotal, tax, total, special_instructions, delivery_address, delivery_address_json,
        order_number, loyalty_points_redeemed, loyalty_discount,
        gift_card_applied, gift_card_id,
        coupon_id, coupon_code, discount_amount
      ) VALUES (
        ${restaurantId}, ${branchId}, ${customerId},
        ${input.customer_name}, ${input.customer_phone}, ${input.customer_email ?? null},
        ${JSON.stringify(lineItems)}::jsonb, 'pending'::order_status,
        'storefront'::channel_type, 'storefront',
        ${t}, ${tableNumber}, ${tableId},
        ${persistedSubtotal}, ${tax}, ${total},
        ${input.special_instructions ?? null},
        ${formatAddress(input.delivery_address_json)},
        ${input.delivery_address_json ? JSON.stringify(input.delivery_address_json) : null}::jsonb,
        ${nextNumber}, ${loyaltyPointsRedeemed}, ${loyaltyDiscount},
        ${giftCardApplied}, ${giftCardId},
        ${appliedCouponId}, ${appliedCouponCode}, ${discountAmount}
      )
      RETURNING id, order_number, status, subtotal, tax, total, branch_id, gift_card_applied, coupon_code, discount_amount
    `);
    if (loyaltyPointsRedeemed > 0 && customerId && order) {
      await loyalty.commitRedeem({
        restaurantId,
        customerId,
        orderId: (order as { id: string }).id,
        points: loyaltyPointsRedeemed,
        discount: loyaltyDiscount,
      }, tx);
    }

    // Atomic stock reservation. lineItems carry menu_item_id so we can lock
    // by id directly. Throws ValidationError on sold-out — rolls back the
    // entire order insert above. Returned alerts are funneled through the
    // outer scope so we can fire them AFTER tx commits.
    stockAlerts = await decrementStockForOrder(
      tx,
      restaurantId,
      branchId ?? null,
      lineItems.map((li) => ({
        menu_item_id: (li.menu_item_id as string) ?? null,
        name: (li.name as string) ?? null,
        quantity: Number(li.quantity ?? 1),
      }))
    );

    // Now write the gift card redemption ledger row + balance update.
    if (giftCardId && giftCardApplied > 0) {
      const newStatus = giftCardRemaining <= 0 ? 'redeemed' : 'active';
      await tx.execute(sql`
        UPDATE gift_cards
        SET balance = ${giftCardRemaining}, status = ${newStatus}, last_used_at = NOW(), updated_at = NOW()
        WHERE id = ${giftCardId}::uuid
      `);
      await tx.execute(sql`
        INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, branch_id, order_id, entry_type, amount, balance_after, note)
        VALUES (${giftCardId}::uuid, ${restaurantId}, ${branchId}, ${(order as Record<string, unknown>).id as string}::uuid,
                'redemption', ${giftCardApplied}, ${giftCardRemaining}, 'Storefront order redemption')
      `);
    }

    if (appliedCouponId && order) {
      await recordRedemption({
        couponId: appliedCouponId,
        orderId: (order as Record<string, unknown>).id as string,
        restaurantId, branchId, customerId,
        channel: 'storefront',
        discountAmount,
        tx,
      });
    }

    return {
      order: order as Record<string, unknown>,
      giftCardApplied,
      giftCardRemaining,
      giftCardId,
      loyaltyPointsRedeemed,
      loyaltyDiscount,
    };
  });

  try {
    dispatchWebhook(restaurantId, 'order.created', inserted.order as Record<string, unknown>);
  } catch (err) {
    log.warn({ err }, 'storefront webhook dispatch failed');
  }

  // Task #295: post-commit low-stock notifications. Failures inside
  // notifyLowStock are already swallowed; the void here just makes the
  // fire-and-forget intent explicit for the linter.
  for (const a of stockAlerts) {
    void notifyLowStock({
      restaurantId,
      branchId: branchId ?? null,
      itemId: a.itemId,
      itemName: a.itemName,
      stockRemaining: a.stockRemaining,
      threshold: a.threshold,
      kind: a.kind,
    });
  }

  // Send the redemption receipt email (with remaining balance) to the
  // recipient on file. Failure must never break the order flow.
  if (inserted.giftCardApplied > 0 && inserted.giftCardId) {
    try {
      const { enqueueGiftCardRedeemedEmail } = await import('./gift-cards-email.service');
      await enqueueGiftCardRedeemedEmail({
        restaurantId,
        branchId,
        cardId: inserted.giftCardId,
        appliedAmount: inserted.giftCardApplied,
        remainingBalance: inserted.giftCardRemaining,
        orderRef: `#${inserted.order.order_number}`,
      });
    } catch (err) {
      log.warn({ err }, 'storefront gift card redeem email failed');
    }
  }

  return {
    id: inserted.order.id as string,
    order_number: Number(inserted.order.order_number),
    status: inserted.order.status as string,
    subtotal: Number(inserted.order.subtotal),
    tax: Number(inserted.order.tax),
    total: Number(inserted.order.total),
    branch_id: inserted.order.branch_id as string,
    loyalty_points_redeemed: inserted.loyaltyPointsRedeemed,
    loyalty_points_earned: Number((inserted.order as Record<string, unknown>).loyalty_points_earned ?? 0),
    loyalty_discount: inserted.loyaltyDiscount,
    gift_card_applied: inserted.giftCardApplied,
    gift_card_remaining_balance: inserted.giftCardRemaining,
    discount_amount: Number((inserted.order as Record<string, unknown>).discount_amount ?? 0),
    coupon_code: ((inserted.order as Record<string, unknown>).coupon_code as string | null) ?? null,
  };
}

/** Public preview of coupon application for the storefront cart. */
export async function previewStorefrontCoupon(
  restaurantSlug: string,
  branchSlug: string,
  args: {
    code: string;
    delivery_type: 'dine-in' | 'takeaway' | 'delivery';
    items: Array<{ menu_item_id: string; quantity: number }>;
  }
): Promise<{
  ok: true;
  code: string;
  discount: number;
  description: string;
  type: 'percent' | 'fixed' | 'bogo';
} | { ok: false; message: string; reason: string }> {
  await initDatabase();
  const profile = await getStorefrontProfile(restaurantSlug, branchSlug);
  if (!profile) throw new NotFoundError('Storefront');
  if (!profile.feature_enabled || !profile.branch.storefront_enabled) {
    throw new ValidationError('Storefront is not enabled for this branch');
  }
  const branchId = profile.branch.id as string;
  const restaurantId = profile.restaurant.id as string;

  const ids = args.items.map((i) => i.menu_item_id);
  if (ids.length === 0) return { ok: false, message: 'Cart is empty', reason: 'invalid_for_cart' };
  const { rows: itemRows } = await db.execute(sql`
    SELECT id, name, price, category_id FROM menu_items
    WHERE restaurant_id = ${restaurantId} AND id = ANY(${bindValue(ids)}::uuid[])
  `);
  const byId = new Map<string, Record<string, unknown>>();
  for (const r of itemRows as Record<string, unknown>[]) byId.set(r.id as string, r);

  const applyItems: ApplyCouponItem[] = [];
  for (const it of args.items) {
    const m = byId.get(it.menu_item_id);
    if (!m) continue;
    applyItems.push({
      menu_item_id: it.menu_item_id,
      name: m.name as string,
      quantity: it.quantity,
      price: Number(m.price),
      category_id: (m.category_id as string | null) ?? undefined,
    });
  }
  const result = await applyCoupon({
    branchId, restaurantId,
    items: applyItems,
    orderType: args.delivery_type,
    channel: 'storefront',
    customerId: null,
    code: args.code,
  });
  if (!result.ok) return { ok: false, message: result.message, reason: result.reason };
  return {
    ok: true,
    code: result.coupon.code,
    discount: result.discount,
    description: result.breakdown.description,
    type: result.coupon.type,
  };
}

function formatAddress(addr: Record<string, unknown> | null | undefined): string | null {
  if (!addr || typeof addr !== 'object') return null;
  const parts = [addr.line1, addr.line2, addr.city, addr.postal_code, addr.notes]
    .filter((p) => typeof p === 'string' && p.trim().length > 0);
  return parts.length ? parts.join(', ') : null;
}

export async function getStorefrontOrderStatus(
  restaurantSlug: string,
  branchSlug: string,
  orderId: string,
  customerPhone: string
): Promise<{
  id: string;
  order_number: number;
  status: string;
  total: number;
  created_at: string;
  loyalty_points_earned: number;
  loyalty_points_redeemed: number;
  loyalty_discount: number;
} | null> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT o.id, o.order_number, o.status, o.total, o.created_at,
           o.loyalty_points_earned, o.loyalty_points_redeemed, o.loyalty_discount
    FROM orders o
    JOIN branches b ON b.id = o.branch_id
    JOIN restaurants r ON r.id = b.restaurant_id
    WHERE r.slug = ${restaurantSlug}
      AND b.slug = ${branchSlug}
      AND o.id = ${orderId}::uuid
      AND o.customer_phone = ${customerPhone}
      AND o.source = 'storefront'
    LIMIT 1
  `);
  const r = rows[0] as Record<string, unknown> | undefined;
  if (!r) return null;
  return {
    id: r.id as string,
    order_number: Number(r.order_number),
    status: r.status as string,
    total: Number(r.total),
    created_at: r.created_at as string,
    loyalty_points_earned: Number(r.loyalty_points_earned ?? 0),
    loyalty_points_redeemed: Number(r.loyalty_points_redeemed ?? 0),
    loyalty_discount: Number(r.loyalty_discount ?? 0),
  };
}