import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError, ConflictError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import type {
  CreateCouponInput,
  UpdateCouponInput,
  BulkImportInput,
  PreviewCouponInput,
} from '@server/validators/coupons.validator';

export type DbExec = Pick<typeof db, 'execute'>;

export interface CouponRecord {
  id: string;
  restaurant_id: string;
  branch_id: string;
  code: string;
  display_name: string | null;
  description: string | null;
  type: 'percent' | 'fixed' | 'bogo';
  value: number;
  bogo_config: Record<string, unknown>;
  valid_from: string | null;
  valid_until: string | null;
  total_redemption_cap: number | null;
  per_customer_cap: number | null;
  min_order_value: number;
  applicable_order_types: string[];
  applicable_channels: string[];
  applicable_branches: string[];
  stack_with_loyalty: boolean;
  stack_with_gift_card: boolean;
  status: 'active' | 'archived';
  created_at: string;
  updated_at: string;
  redemption_count?: number;
}

function rowToCoupon(r: Record<string, unknown>): CouponRecord {
  return {
    id: r.id as string,
    restaurant_id: r.restaurant_id as string,
    branch_id: r.branch_id as string,
    code: r.code as string,
    display_name: (r.display_name as string | null) ?? null,
    description: (r.description as string | null) ?? null,
    type: r.type as CouponRecord['type'],
    value: Number(r.value ?? 0),
    bogo_config: (r.bogo_config as Record<string, unknown>) ?? {},
    valid_from: (r.valid_from as string | null) ?? null,
    valid_until: (r.valid_until as string | null) ?? null,
    total_redemption_cap: r.total_redemption_cap == null ? null : Number(r.total_redemption_cap),
    per_customer_cap: r.per_customer_cap == null ? null : Number(r.per_customer_cap),
    min_order_value: Number(r.min_order_value ?? 0),
    applicable_order_types: (r.applicable_order_types as string[]) ?? [],
    applicable_channels: (r.applicable_channels as string[]) ?? [],
    applicable_branches: (r.applicable_branches as string[]) ?? [],
    stack_with_loyalty: r.stack_with_loyalty === true,
    stack_with_gift_card: r.stack_with_gift_card === true,
    status: r.status as CouponRecord['status'],
    created_at: r.created_at as string,
    updated_at: r.updated_at as string,
    redemption_count: r.redemption_count == null ? undefined : Number(r.redemption_count),
  };
}

// ─── CRUD ────────────────────────────────────────────────────────────────────

export interface ListCouponsFilters {
  restaurantId: string;
  branchId?: string;
  status?: 'active' | 'archived' | 'all';
  search?: string;
  page?: number;
  limit?: number;
}

export async function listCoupons(filters: ListCouponsFilters) {
  await initDatabase();
  const { restaurantId, branchId, status = 'all', search, page = 1, limit = 50 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`c.restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`c.branch_id = ${branchId}`);
  if (status !== 'all') conditions.push(sql`c.status = ${status}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(c.code ILIKE ${pattern} OR c.display_name ILIKE ${pattern})`);
  }
  const where = sql.join(conditions, sql` AND `);

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

  const { rows } = await db.execute(sql`
    SELECT c.*, COALESCE(rc.cnt, 0) AS redemption_count
    FROM coupons c
    LEFT JOIN (SELECT coupon_id, COUNT(*) AS cnt FROM coupon_redemptions GROUP BY coupon_id) rc
      ON rc.coupon_id = c.id
    WHERE ${where}
    ORDER BY c.created_at DESC
    LIMIT ${limit} OFFSET ${offset}
  `);
  return {
    data: (rows as Record<string, unknown>[]).map(rowToCoupon),
    total, page, limit, pages: Math.ceil(total / Math.max(1, limit)),
  };
}

export async function getCoupon(id: string, restaurantId: string): Promise<CouponRecord> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT c.*, COALESCE(rc.cnt, 0) AS redemption_count
    FROM coupons c
    LEFT JOIN (SELECT coupon_id, COUNT(*) AS cnt FROM coupon_redemptions WHERE coupon_id = ${id} GROUP BY coupon_id) rc
      ON rc.coupon_id = c.id
    WHERE c.id = ${id} AND c.restaurant_id = ${restaurantId}
    LIMIT 1
  `);
  if (!rows[0]) throw new NotFoundError('Coupon');
  return rowToCoupon(rows[0] as Record<string, unknown>);
}

export async function createCoupon(restaurantId: string, defaultBranchId: string | null, input: CreateCouponInput): Promise<CouponRecord> {
  await initDatabase();
  const branchId = input.branch_id ?? defaultBranchId;
  if (!branchId) throw new ValidationError('branch_id is required');
  // Verify branch belongs to restaurant
  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 ValidationError('Selected branch is not valid');

  // applicable_branches defaults to [branchId] when empty
  const applicableBranches = input.applicable_branches.length > 0 ? input.applicable_branches : [branchId];

  try {
    const { rows } = await db.execute(sql`
      INSERT INTO coupons (
        restaurant_id, branch_id, code, display_name, description, type, value, bogo_config,
        valid_from, valid_until, total_redemption_cap, per_customer_cap, min_order_value,
        applicable_order_types, applicable_channels, applicable_branches,
        stack_with_loyalty, stack_with_gift_card, status
      ) VALUES (
        ${restaurantId}, ${branchId}, ${input.code.toUpperCase()},
        ${input.display_name ?? null}, ${input.description ?? null},
        ${input.type}, ${input.value}, ${bindValue(input.bogo_config ?? {})},
        ${input.valid_from ?? null}, ${input.valid_until ?? null},
        ${input.total_redemption_cap ?? null}, ${input.per_customer_cap ?? null},
        ${input.min_order_value},
        ${bindValue(input.applicable_order_types)},
        ${bindValue(input.applicable_channels)},
        ${bindValue(applicableBranches)}::uuid[],
        ${input.stack_with_loyalty}, ${input.stack_with_gift_card}, ${input.status}
      ) RETURNING *
    `);
    return rowToCoupon(rows[0] as Record<string, unknown>);
  } catch (err) {
    if (err instanceof Error && /uniq_coupons_branch_code|duplicate key/i.test(err.message)) {
      throw new ConflictError(`A coupon with code "${input.code}" already exists in this branch`);
    }
    throw err;
  }
}

export async function updateCoupon(id: string, restaurantId: string, input: UpdateCouponInput): Promise<CouponRecord> {
  await initDatabase();
  const sets: SQL[] = [sql`updated_at = NOW()`];
  if (input.code !== undefined)               sets.push(sql`code = ${input.code.toUpperCase()}`);
  if (input.display_name !== undefined)       sets.push(sql`display_name = ${input.display_name ?? null}`);
  if (input.description !== undefined)        sets.push(sql`description = ${input.description ?? null}`);
  if (input.type !== undefined)               sets.push(sql`type = ${input.type}`);
  if (input.value !== undefined)              sets.push(sql`value = ${input.value}`);
  if (input.bogo_config !== undefined)        sets.push(sql`bogo_config = ${bindValue(input.bogo_config)}`);
  if (input.valid_from !== undefined)         sets.push(sql`valid_from = ${input.valid_from ?? null}`);
  if (input.valid_until !== undefined)        sets.push(sql`valid_until = ${input.valid_until ?? null}`);
  if (input.total_redemption_cap !== undefined) sets.push(sql`total_redemption_cap = ${input.total_redemption_cap ?? null}`);
  if (input.per_customer_cap !== undefined)   sets.push(sql`per_customer_cap = ${input.per_customer_cap ?? null}`);
  if (input.min_order_value !== undefined)    sets.push(sql`min_order_value = ${input.min_order_value}`);
  if (input.applicable_order_types !== undefined) sets.push(sql`applicable_order_types = ${bindValue(input.applicable_order_types)}`);
  if (input.applicable_channels !== undefined)    sets.push(sql`applicable_channels = ${bindValue(input.applicable_channels)}`);
  if (input.applicable_branches !== undefined)    sets.push(sql`applicable_branches = ${bindValue(input.applicable_branches)}::uuid[]`);
  if (input.stack_with_loyalty !== undefined)  sets.push(sql`stack_with_loyalty = ${input.stack_with_loyalty}`);
  if (input.stack_with_gift_card !== undefined) sets.push(sql`stack_with_gift_card = ${input.stack_with_gift_card}`);
  if (input.status !== undefined)              sets.push(sql`status = ${input.status}`);

  try {
    const { rows } = await db.execute(sql`
      UPDATE coupons SET ${sql.join(sets, sql`, `)}
      WHERE id = ${id} AND restaurant_id = ${restaurantId}
      RETURNING *
    `);
    if (!rows[0]) throw new NotFoundError('Coupon');
    return rowToCoupon(rows[0] as Record<string, unknown>);
  } catch (err) {
    if (err instanceof Error && /uniq_coupons_branch_code|duplicate key/i.test(err.message)) {
      throw new ConflictError('Another coupon already uses this code in this branch');
    }
    throw err;
  }
}

export async function archiveCoupon(id: string, restaurantId: string): Promise<CouponRecord> {
  return updateCoupon(id, restaurantId, { status: 'archived' });
}

export async function deleteCoupon(id: string, restaurantId: string): Promise<void> {
  await initDatabase();
  const result = await db.execute(sql`DELETE FROM coupons WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Coupon');
}

export async function duplicateCoupon(id: string, restaurantId: string): Promise<CouponRecord> {
  await initDatabase();
  const src = await getCoupon(id, restaurantId);
  let newCode = `${src.code}-COPY`;
  // Try to find a non-conflicting code
  for (let i = 0; i < 20; i++) {
    const { rows } = await db.execute(sql`
      SELECT 1 FROM coupons WHERE branch_id = ${src.branch_id} AND lower(code) = lower(${newCode}) LIMIT 1
    `);
    if (!rows[0]) break;
    newCode = `${src.code}-COPY-${i + 2}`;
  }
  return createCoupon(restaurantId, src.branch_id, {
    code: newCode,
    display_name: src.display_name ?? undefined,
    description: src.description ?? undefined,
    type: src.type,
    value: src.value,
    bogo_config: src.bogo_config,
    valid_from: src.valid_from,
    valid_until: src.valid_until,
    total_redemption_cap: src.total_redemption_cap,
    per_customer_cap: src.per_customer_cap,
    min_order_value: src.min_order_value,
    applicable_order_types: src.applicable_order_types as ('dine-in' | 'takeaway' | 'delivery')[],
    applicable_channels: src.applicable_channels as ('chat' | 'whatsapp' | 'storefront')[],
    applicable_branches: src.applicable_branches,
    branch_id: src.branch_id,
    stack_with_loyalty: src.stack_with_loyalty,
    stack_with_gift_card: src.stack_with_gift_card,
    status: 'active',
  });
}

export async function bulkImportCoupons(restaurantId: string, input: BulkImportInput) {
  await initDatabase();
  const results = { created: 0, failed: [] as Array<{ code: string; error: string }> };
  for (const row of input.rows) {
    try {
      await createCoupon(restaurantId, input.branch_id, { ...row, branch_id: input.branch_id });
      results.created++;
    } catch (err) {
      results.failed.push({
        code: row.code,
        error: err instanceof Error ? err.message : 'Unknown error',
      });
    }
  }
  return results;
}

// ─── REDEMPTIONS ─────────────────────────────────────────────────────────────

export async function listRedemptions(couponId: string, restaurantId: string, page = 1, limit = 50) {
  await initDatabase();
  const offset = (page - 1) * limit;
  // Verify coupon belongs to restaurant
  await getCoupon(couponId, restaurantId);

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

  const { rows } = await db.execute(sql`
    SELECT r.id, r.coupon_id, r.order_id, r.channel, r.discount_amount, r.created_at,
           r.customer_id, c.name AS customer_name, o.order_number
    FROM coupon_redemptions r
    LEFT JOIN customers c ON c.id = r.customer_id
    LEFT JOIN orders o ON o.id = r.order_id
    WHERE r.coupon_id = ${couponId}
    ORDER BY r.created_at DESC
    LIMIT ${limit} OFFSET ${offset}
  `);
  return { data: rows, total, page, limit, pages: Math.ceil(total / Math.max(1, limit)) };
}

export async function listRedemptionsAll(couponId: string, restaurantId: string) {
  await initDatabase();
  await getCoupon(couponId, restaurantId);
  const { rows } = await db.execute(sql`
    SELECT r.id, r.coupon_id, r.order_id, r.channel, r.discount_amount, r.created_at,
           r.customer_id, c.name AS customer_name, c.phone AS customer_phone,
           o.order_number, o.total AS order_total
    FROM coupon_redemptions r
    LEFT JOIN customers c ON c.id = r.customer_id
    LEFT JOIN orders o ON o.id = r.order_id
    WHERE r.coupon_id = ${couponId}
    ORDER BY r.created_at DESC
  `);
  return rows as Record<string, unknown>[];
}

// ─── ANALYTICS ───────────────────────────────────────────────────────────────

export interface CouponAnalyticsFilters {
  restaurantId: string;
  branchId?: string;
  from?: string; // ISO date
  to?: string;   // ISO date (exclusive upper bound recommended; we use inclusive end-of-day)
}

export interface CouponAnalytics {
  totals: {
    redemptions: number;
    discount_value: number;
    orders_discount_total: number;
    reconciled: boolean;
    /** Task #325 — total times the voice agent mentioned ANY active offer
     *  during a call in the requested window (sum of array lengths). */
    voice_mentions: number;
  };
  per_day: Array<{ date: string; redemptions: number; discount: number }>;
  top_coupons: Array<{
    coupon_id: string;
    code: string;
    display_name: string | null;
    redemptions: number;
    discount: number;
    /** Task #325 — voice mentions for this coupon in the same window. */
    voice_mentions?: number;
  }>;
  /** Task #325 — per-channel mentions vs redemptions, so operators can
   *  see how often the voice agent surfaced offers and whether those
   *  surfaces converted into redemptions. */
  per_channel: Array<{
    channel: string;
    mentions: number;
    redemptions: number;
    discount: number;
  }>;
  filters: { branch_id: string | null; from: string | null; to: string | null };
}

export async function getCouponAnalytics(filters: CouponAnalyticsFilters): Promise<CouponAnalytics> {
  await initDatabase();
  const { restaurantId, branchId, from, to } = filters;

  const conds: SQL[] = [sql`r.restaurant_id = ${restaurantId}`];
  if (branchId) conds.push(sql`r.branch_id = ${branchId}`);
  if (from) conds.push(sql`r.created_at >= ${from}`);
  if (to) conds.push(sql`r.created_at <= ${to}`);
  const where = sql.join(conds, sql` AND `);

  // Totals from coupon_redemptions
  const { rows: totalRows } = await db.execute(sql`
    SELECT COUNT(*)::int AS cnt, COALESCE(SUM(r.discount_amount), 0)::float AS total
    FROM coupon_redemptions r
    WHERE ${where}
  `);
  const total = totalRows[0] as { cnt: number; total: number };

  // Reconciliation: sum of orders.discount_amount where coupon_id is set
  const oConds: SQL[] = [sql`o.restaurant_id = ${restaurantId}`, sql`o.coupon_id IS NOT NULL`];
  if (branchId) oConds.push(sql`o.branch_id = ${branchId}`);
  if (from) oConds.push(sql`o.created_at >= ${from}`);
  if (to) oConds.push(sql`o.created_at <= ${to}`);
  const { rows: ordRows } = await db.execute(sql`
    SELECT COALESCE(SUM(o.discount_amount), 0)::float AS total
    FROM orders o WHERE ${sql.join(oConds, sql` AND `)}
  `);
  const ordersDiscount = Number((ordRows[0] as { total: number })?.total ?? 0);

  // Redemptions per day
  const { rows: dayRows } = await db.execute(sql`
    SELECT to_char(date_trunc('day', r.created_at), 'YYYY-MM-DD') AS date,
           COUNT(*)::int AS redemptions,
           COALESCE(SUM(r.discount_amount), 0)::float AS discount
    FROM coupon_redemptions r
    WHERE ${where}
    GROUP BY 1
    ORDER BY 1 ASC
  `);

  // Top 5 coupons
  const { rows: topRows } = await db.execute(sql`
    SELECT r.coupon_id, c.code, c.display_name,
           COUNT(*)::int AS redemptions,
           COALESCE(SUM(r.discount_amount), 0)::float AS discount
    FROM coupon_redemptions r
    JOIN coupons c ON c.id = r.coupon_id
    WHERE ${where}
    GROUP BY r.coupon_id, c.code, c.display_name
    ORDER BY redemptions DESC, discount DESC
    LIMIT 5
  `);

  // ─── Task #325: voice attribution ──────────────────────────────────────
  // sip_call_sessions.mentioned_offer_ids stores a JSONB array of coupon
  // UUIDs the voice agent surfaced during the call. We unnest those into
  // (session, coupon_id) rows so we can count mentions per coupon and
  // overall — restricted to the same restaurant / branch / time window.
  const sessionConds: SQL[] = [sql`s.restaurant_id = ${restaurantId}`];
  if (from) sessionConds.push(sql`s.started_at >= ${from}`);
  if (to) sessionConds.push(sql`s.started_at <= ${to}`);
  if (branchId) sessionConds.push(sql`pn.branch_id = ${branchId}`);
  const sessionWhere = sql.join(sessionConds, sql` AND `);

  const { rows: mentionRows } = await db.execute(sql`
    SELECT (mention_id)::uuid AS coupon_id, COUNT(*)::int AS mentions
      FROM sip_call_sessions s
      LEFT JOIN phone_numbers pn ON pn.id = s.phone_number_id,
           LATERAL jsonb_array_elements_text(COALESCE(s.mentioned_offer_ids, '[]'::jsonb)) AS mention_id
     WHERE ${sessionWhere}
       AND mention_id ~* '^[0-9a-f-]{36}$'
     GROUP BY mention_id
  `);
  const mentionsByCoupon = new Map<string, number>();
  let voiceMentionsTotal = 0;
  for (const row of mentionRows as Array<{ coupon_id: string; mentions: number }>) {
    const n = Number(row.mentions);
    mentionsByCoupon.set(row.coupon_id, n);
    voiceMentionsTotal += n;
  }

  // Per-channel redemption rollup (mentions are voice-only today).
  const { rows: channelRows } = await db.execute(sql`
    SELECT r.channel,
           COUNT(*)::int AS redemptions,
           COALESCE(SUM(r.discount_amount), 0)::float AS discount
      FROM coupon_redemptions r
     WHERE ${where}
     GROUP BY r.channel
  `);
  const perChannelMap = new Map<string, { mentions: number; redemptions: number; discount: number }>();
  for (const row of channelRows as Array<{ channel: string; redemptions: number; discount: number }>) {
    perChannelMap.set(row.channel, {
      mentions: 0,
      redemptions: Number(row.redemptions),
      discount: round2(Number(row.discount)),
    });
  }
  if (voiceMentionsTotal > 0) {
    const existing = perChannelMap.get('voice') ?? { mentions: 0, redemptions: 0, discount: 0 };
    existing.mentions = voiceMentionsTotal;
    perChannelMap.set('voice', existing);
  }
  const perChannel = Array.from(perChannelMap.entries())
    .map(([channel, v]) => ({ channel, ...v }))
    .sort((a, b) => (b.mentions + b.redemptions) - (a.mentions + a.redemptions));

  const redemptionsTotal = Number(total?.cnt ?? 0);
  const discountTotal = Number(total?.total ?? 0);
  const reconciled = Math.abs(discountTotal - ordersDiscount) < 0.01;

  return {
    totals: {
      redemptions: redemptionsTotal,
      discount_value: round2(discountTotal),
      orders_discount_total: round2(ordersDiscount),
      reconciled,
      voice_mentions: voiceMentionsTotal,
    },
    per_day: (dayRows as Array<{ date: string; redemptions: number; discount: number }>).map(d => ({
      date: d.date,
      redemptions: Number(d.redemptions),
      discount: round2(Number(d.discount)),
    })),
    top_coupons: (topRows as Array<{ coupon_id: string; code: string; display_name: string | null; redemptions: number; discount: number }>).map(t => ({
      coupon_id: t.coupon_id,
      code: t.code,
      display_name: t.display_name,
      redemptions: Number(t.redemptions),
      discount: round2(Number(t.discount)),
      voice_mentions: mentionsByCoupon.get(t.coupon_id) ?? 0,
    })),
    per_channel: perChannel,
    filters: { branch_id: branchId ?? null, from: from ?? null, to: to ?? null },
  };
}

// ─── PRICING ENGINE ──────────────────────────────────────────────────────────

export interface ApplyCouponItem {
  menu_item_id: string;
  name?: string;
  quantity: number;
  price: number;
  category_id?: string;
}

export interface ApplyCouponInput {
  branchId: string;
  restaurantId: string;
  items: ApplyCouponItem[];
  orderType: 'dine-in' | 'takeaway' | 'delivery';
  channel: 'chat' | 'whatsapp' | 'storefront' | string;
  customerId?: string | null;
  code: string;
  /** Optional: when supplied, applyCoupon runs inside this transaction
   *  and uses SELECT ... FOR UPDATE to prevent races on caps. */
  tx?: DbExec;
}

export interface ApplyCouponSuccess {
  ok: true;
  coupon: CouponRecord;
  discount: number;
  breakdown: {
    subtotal: number;
    discount: number;
    code: string;
    type: CouponRecord['type'];
    description: string;
  };
}

export interface ApplyCouponFailure {
  ok: false;
  message: string;
  reason: 'not_found' | 'archived' | 'expired' | 'not_yet_valid' | 'capped' | 'per_customer_capped'
        | 'channel_ineligible' | 'order_type_ineligible' | 'branch_ineligible'
        | 'below_minimum' | 'invalid_for_cart';
}

export type ApplyCouponResult = ApplyCouponSuccess | ApplyCouponFailure;

function fail(reason: ApplyCouponFailure['reason'], message: string): ApplyCouponFailure {
  return { ok: false, reason, message };
}

function calcSubtotal(items: ApplyCouponItem[]): number {
  return items.reduce((s, i) => s + Number(i.price) * Number(i.quantity), 0);
}

function round2(n: number): number {
  return Math.round(n * 100) / 100;
}

/** Computes the discount the supplied coupon would apply against the supplied
 *  cart. Pure pricing — no DB writes. Designed so future Loyalty + Gift Card
 *  engines can compose it. */
export async function applyCoupon(input: ApplyCouponInput): Promise<ApplyCouponResult> {
  await initDatabase();
  const code = input.code.trim();
  if (!code) return fail('not_found', 'Please enter a coupon code');

  const exec = input.tx ?? db;
  const lockClause = input.tx ? sql`FOR UPDATE` : sql``;
  // Cross-branch lookup: find any coupon in the restaurant matching the code
  // where the current branch is either the owning branch OR is listed in
  // applicable_branches. Prefer the owning branch when both exist.
  const { rows } = await exec.execute(sql`
    SELECT * FROM coupons
    WHERE restaurant_id = ${input.restaurantId}
      AND lower(code) = lower(${code})
      AND (
        branch_id = ${input.branchId}
        OR ${input.branchId}::uuid = ANY(applicable_branches)
      )
    ORDER BY (branch_id = ${input.branchId}) DESC, created_at ASC
    LIMIT 1
    ${lockClause}
  `);
  if (!rows[0]) return fail('not_found', `We couldn't find a coupon with code "${code.toUpperCase()}"`);

  const coupon = rowToCoupon(rows[0] as Record<string, unknown>);
  if (coupon.status !== 'active') return fail('archived', 'This coupon is no longer active');

  const now = Date.now();
  if (coupon.valid_from && now < new Date(coupon.valid_from).getTime()) {
    return fail('not_yet_valid', 'This coupon is not yet valid');
  }
  if (coupon.valid_until && now > new Date(coupon.valid_until).getTime()) {
    return fail('expired', 'This coupon has expired');
  }

  if (!coupon.applicable_channels.includes(input.channel)) {
    return fail('channel_ineligible', 'This coupon cannot be redeemed on this channel');
  }
  if (!coupon.applicable_order_types.includes(input.orderType)) {
    return fail('order_type_ineligible', `This coupon doesn't apply to ${input.orderType} orders`);
  }
  if (coupon.applicable_branches.length > 0 && !coupon.applicable_branches.includes(input.branchId)) {
    return fail('branch_ineligible', 'This coupon is not valid at this branch');
  }

  // Caps
  if (coupon.total_redemption_cap != null) {
    const { rows: cntRows } = await exec.execute(sql`
      SELECT COUNT(*)::int AS cnt FROM coupon_redemptions WHERE coupon_id = ${coupon.id}
    `);
    const used = Number((cntRows[0] as { cnt: number })?.cnt ?? 0);
    if (used >= coupon.total_redemption_cap) {
      return fail('capped', 'This coupon has reached its redemption limit');
    }
  }
  if (coupon.per_customer_cap != null && input.customerId) {
    const { rows: cRows } = await exec.execute(sql`
      SELECT COUNT(*)::int AS cnt FROM coupon_redemptions
      WHERE coupon_id = ${coupon.id} AND customer_id = ${input.customerId}
    `);
    const used = Number((cRows[0] as { cnt: number })?.cnt ?? 0);
    if (used >= coupon.per_customer_cap) {
      return fail('per_customer_capped', `You've already used this coupon ${used} time(s)`);
    }
  }

  const subtotal = round2(calcSubtotal(input.items));
  if (subtotal < Number(coupon.min_order_value)) {
    return fail('below_minimum', `Minimum order for this coupon is ${coupon.min_order_value.toFixed(2)}`);
  }

  let discount = 0;
  let description = '';
  switch (coupon.type) {
    case 'percent': {
      discount = round2(subtotal * (coupon.value / 100));
      description = `${coupon.value}% off`;
      break;
    }
    case 'fixed': {
      discount = round2(Math.min(coupon.value, subtotal));
      description = `${coupon.value.toFixed(2)} off`;
      break;
    }
    case 'bogo': {
      const cfg = coupon.bogo_config as {
        buy_target_type?: 'item' | 'category';
        buy_target_id?: string;
        get_target_type?: 'item' | 'category';
        get_target_id?: string;
        get_discount_kind?: 'free' | 'percent';
        get_discount_percent?: number;
      };
      const matchTarget = (it: ApplyCouponItem, type?: string, id?: string) => {
        if (!type || !id) return true;
        if (type === 'item') return it.menu_item_id === id;
        if (type === 'category') return it.category_id === id;
        return false;
      };
      const buyQty = input.items
        .filter((it) => matchTarget(it, cfg.buy_target_type, cfg.buy_target_id))
        .reduce((s, it) => s + it.quantity, 0);
      const getMatches = input.items.filter((it) => matchTarget(it, cfg.get_target_type, cfg.get_target_id));
      if (buyQty < 1 || getMatches.length === 0) {
        return fail('invalid_for_cart', 'Add the qualifying items to redeem this BOGO offer');
      }
      const cheapest = getMatches.reduce((m, it) => (it.price < m.price ? it : m), getMatches[0]);
      const pct = cfg.get_discount_kind === 'percent' ? Math.max(0, Math.min(100, Number(cfg.get_discount_percent ?? 100))) : 100;
      discount = round2(cheapest.price * (pct / 100));
      description = `Buy one${cfg.buy_target_id ? ' qualifying item' : ''}, get one${pct === 100 ? ' free' : ` ${pct}% off`}`;
      break;
    }
  }
  if (discount <= 0) {
    return fail('invalid_for_cart', 'This coupon does not apply to your current cart');
  }
  // Don't allow discount to exceed subtotal
  discount = Math.min(discount, subtotal);

  return {
    ok: true,
    coupon,
    discount,
    breakdown: { subtotal, discount, code: coupon.code, type: coupon.type, description },
  };
}

/**
 * Throws ValidationError when the coupon's stack flags forbid the other
 * discount types being applied on the same order. Used by both the chat /
 * WhatsApp `createOrder` flow and the storefront flow so the "compose
 * coupon + loyalty + gift card" rule is honored everywhere.
 *
 * Also honors the gift-card settings' `stack_with_coupons` /
 * `stack_with_loyalty` toggles (which mirror the coupon's flags from the
 * gift-card side).
 */
export function enforceStackingPolicy(args: {
  coupon: Pick<CouponRecord, 'code' | 'stack_with_loyalty' | 'stack_with_gift_card'> | null;
  loyaltyApplied: boolean;
  giftCardApplied: boolean;
  giftCardSettings?: { stack_with_coupons?: boolean; stack_with_loyalty?: boolean } | null;
}): void {
  const { coupon, loyaltyApplied, giftCardApplied, giftCardSettings } = args;
  if (coupon && loyaltyApplied && coupon.stack_with_loyalty === false) {
    throw new ValidationError(
      `Coupon "${coupon.code}" cannot be combined with loyalty points. Remove one and try again.`
    );
  }
  if (coupon && giftCardApplied && coupon.stack_with_gift_card === false) {
    throw new ValidationError(
      `Coupon "${coupon.code}" cannot be combined with a gift card. Remove one and try again.`
    );
  }
  if (giftCardApplied && giftCardSettings) {
    if (coupon && giftCardSettings.stack_with_coupons === false) {
      throw new ValidationError(
        `Gift cards cannot be combined with coupons at this restaurant.`
      );
    }
    if (loyaltyApplied && giftCardSettings.stack_with_loyalty === false) {
      throw new ValidationError(
        `Gift cards cannot be combined with loyalty points at this restaurant.`
      );
    }
  }
}

/** Persists a redemption row. Pass `tx` to run inside the order-create
 *  transaction so the redemption is rolled back if the order insert fails. */
export async function recordRedemption(args: {
  couponId: string;
  orderId: string | null;
  restaurantId: string;
  branchId: string;
  customerId: string | null;
  channel: string;
  discountAmount: number;
  tx?: DbExec;
}) {
  const exec = args.tx ?? db;
  await exec.execute(sql`
    INSERT INTO coupon_redemptions (coupon_id, order_id, restaurant_id, branch_id, customer_id, channel, discount_amount)
    VALUES (${args.couponId}, ${args.orderId}, ${args.restaurantId}, ${args.branchId},
            ${args.customerId}, ${args.channel}, ${args.discountAmount})
  `);
}

// ─── ACTIVE OFFERS LOOKUP (Task #323) ────────────────────────────────────────

export interface ActiveOfferSummary {
  id: string;
  code: string;
  display_name: string | null;
  description: string | null;
  type: CouponRecord['type'];
  value: number;
}

/**
 * Returns the top N currently-active coupons applicable to a given
 * channel for this restaurant + branch + (optional) customer. Used by
 * the voice personalization layer to let the agent mention live offers
 * naturally, but generic enough that chat / WhatsApp greeters can
 * reuse it later.
 *
 * Filters applied (in SQL, so it stays cheap on a hot path):
 *   - status = 'active'
 *   - within valid_from / valid_until window
 *   - branch eligibility (owning branch OR listed in applicable_branches)
 *   - channel listed in applicable_channels
 *   - under total_redemption_cap (when set)
 *   - under per_customer_cap for the supplied customer (when set)
 *
 * Result is intentionally capped (default 2) to keep the prompt small.
 */
export async function getActiveOffersForChannel(args: {
  restaurantId: string;
  branchId: string | null;
  channel: 'voice' | 'chat' | 'whatsapp' | 'storefront';
  customerId?: string | null;
  limit?: number;
}): Promise<ActiveOfferSummary[]> {
  await initDatabase();
  const limit = Math.max(1, Math.min(args.limit ?? 2, 5));
  const branchId = args.branchId;
  const customerId = args.customerId ?? null;

  const branchClause = branchId
    ? sql`AND (c.branch_id = ${branchId} OR ${branchId}::uuid = ANY(c.applicable_branches))`
    : sql``;

  const { rows } = await db.execute(sql`
    SELECT c.id, c.code, c.display_name, c.description, c.type, c.value
      FROM coupons c
      LEFT JOIN (
        SELECT coupon_id, COUNT(*)::int AS cnt
          FROM coupon_redemptions GROUP BY coupon_id
      ) rc_total ON rc_total.coupon_id = c.id
      LEFT JOIN (
        SELECT coupon_id, COUNT(*)::int AS cnt
          FROM coupon_redemptions
         WHERE ${customerId ? sql`customer_id = ${customerId}` : sql`false`}
         GROUP BY coupon_id
      ) rc_cust ON rc_cust.coupon_id = c.id
     WHERE c.restaurant_id = ${args.restaurantId}
       AND c.status = 'active'
       AND ${args.channel} = ANY(c.applicable_channels)
       AND (c.valid_from  IS NULL OR c.valid_from  <= NOW())
       AND (c.valid_until IS NULL OR c.valid_until >= NOW())
       AND (c.total_redemption_cap IS NULL OR COALESCE(rc_total.cnt, 0) < c.total_redemption_cap)
       AND (c.per_customer_cap IS NULL OR ${customerId ? sql`COALESCE(rc_cust.cnt, 0) < c.per_customer_cap` : sql`true`})
       ${branchClause}
     ORDER BY (c.valid_until IS NOT NULL) DESC, c.valid_until ASC NULLS LAST, c.created_at DESC
     LIMIT ${limit}
  `);

  return (rows as Record<string, unknown>[]).map((r) => ({
    id: r.id as string,
    code: r.code as string,
    display_name: (r.display_name as string | null) ?? null,
    description: (r.description as string | null) ?? null,
    type: r.type as CouponRecord['type'],
    value: Number(r.value ?? 0),
  }));
}

// ─── VOICE REDEMPTION VALIDATION (Task #325) ─────────────────────────────────

export type VoiceRedemptionValidation =
  | { ok: true; coupon: { id: string; code: string; display_name: string | null } }
  | { ok: false; reason:
      | 'not_found'        // code doesn't exist for this restaurant
      | 'inactive'         // status != active
      | 'wrong_channel'    // 'voice' not in applicable_channels
      | 'wrong_branch'     // branch not eligible
      | 'not_started'      // before valid_from
      | 'expired'          // past valid_until
      | 'total_cap_hit'    // total_redemption_cap reached
      | 'per_customer_cap_hit';
      message: string;
    };

/**
 * Lightweight, cart-free eligibility check for voice attribution.
 *
 * Unlike `applyCoupon`, this purposely ignores order shape (items,
 * order type, subtotal). Voice calls do not run the full pricing
 * engine — the caller is being told they can use a coupon at the
 * counter / pickup, and we only need to confirm the coupon itself
 * is real and currently usable on this channel/branch/customer so
 * we can attribute the acceptance.
 *
 * Restrictions that DO matter at the moment of acceptance and ARE
 * enforced here: status, channel, branch, schedule window, total
 * cap, per-customer cap.
 *
 * Restrictions that depend on the actual order (min_order_value,
 * BOGO/item/category constraints, dine-in vs takeaway vs delivery)
 * are deliberately NOT enforced here — they will be re-checked by
 * `applyCoupon` if/when the caller's order is later placed through
 * the normal flow.
 */
export async function validateCouponForVoiceRedemption(args: {
  restaurantId: string;
  branchId: string;
  customerId?: string | null;
  code: string;
}): Promise<VoiceRedemptionValidation> {
  await initDatabase();
  const code = args.code.trim();
  if (!code) return { ok: false, reason: 'not_found', message: "I didn't catch the coupon code." };
  const customerId = args.customerId ?? null;

  const { rows } = await db.execute(sql`
    SELECT c.id, c.code, c.display_name, c.status,
           c.valid_from, c.valid_until,
           c.applicable_channels, c.applicable_branches, c.branch_id,
           c.total_redemption_cap, c.per_customer_cap,
           COALESCE(rc_total.cnt, 0)::int AS total_redemptions,
           ${customerId ? sql`COALESCE(rc_cust.cnt, 0)::int` : sql`0::int`} AS customer_redemptions
      FROM coupons c
      LEFT JOIN (
        SELECT coupon_id, COUNT(*)::int AS cnt
          FROM coupon_redemptions GROUP BY coupon_id
      ) rc_total ON rc_total.coupon_id = c.id
      ${customerId ? sql`
      LEFT JOIN (
        SELECT coupon_id, COUNT(*)::int AS cnt
          FROM coupon_redemptions
         WHERE customer_id = ${customerId}
         GROUP BY coupon_id
      ) rc_cust ON rc_cust.coupon_id = c.id` : sql``}
     WHERE c.restaurant_id = ${args.restaurantId}
       AND UPPER(c.code) = UPPER(${code})
     LIMIT 1
  `);
  const r = rows[0] as Record<string, unknown> | undefined;
  if (!r) {
    return { ok: false, reason: 'not_found', message: `I couldn't find a coupon with the code "${code}".` };
  }

  const couponId = r.id as string;
  const couponCode = r.code as string;
  const displayName = (r.display_name as string | null) ?? null;
  const status = r.status as string;
  const validFrom = r.valid_from ? new Date(r.valid_from as string) : null;
  const validUntil = r.valid_until ? new Date(r.valid_until as string) : null;
  const channels = (r.applicable_channels as string[] | null) ?? [];
  const applicableBranches = (r.applicable_branches as string[] | null) ?? [];
  const ownerBranch = (r.branch_id as string | null) ?? null;
  const totalCap = r.total_redemption_cap as number | null;
  const perCustCap = r.per_customer_cap as number | null;
  const totalCount = Number(r.total_redemptions ?? 0);
  const custCount = Number(r.customer_redemptions ?? 0);

  if (status !== 'active') {
    return { ok: false, reason: 'inactive', message: `That coupon isn't active right now.` };
  }
  if (channels.length > 0 && !channels.includes('voice')) {
    return { ok: false, reason: 'wrong_channel', message: `That coupon can't be redeemed over the phone.` };
  }
  const now = new Date();
  if (validFrom && now < validFrom) {
    return { ok: false, reason: 'not_started', message: `That coupon isn't available yet.` };
  }
  if (validUntil && now > validUntil) {
    return { ok: false, reason: 'expired', message: `That coupon has expired.` };
  }
  const branchEligible =
    ownerBranch === args.branchId || applicableBranches.includes(args.branchId);
  if (!branchEligible) {
    return { ok: false, reason: 'wrong_branch', message: `That coupon isn't valid at this location.` };
  }
  if (totalCap != null && totalCount >= totalCap) {
    return { ok: false, reason: 'total_cap_hit', message: `That coupon has already been fully redeemed.` };
  }
  if (customerId && perCustCap != null && custCount >= perCustCap) {
    return { ok: false, reason: 'per_customer_cap_hit', message: `You've already redeemed that coupon the maximum number of times.` };
  }

  return { ok: true, coupon: { id: couponId, code: couponCode, display_name: displayName } };
}

export type { PreviewCouponInput };