/**
 * Loyalty service — points, tiers, ledger.
 *
 * Restaurant-scoped (one wallet per (restaurant, customer) regardless of branch).
 *
 *  - `loyalty_settings` (one row per restaurant) holds earn/redeem/expiry rules.
 *  - `loyalty_tiers` are fully editable per restaurant; threshold can be
 *    "lifetime_points" or "rolling_12mo_spend" depending on
 *    `tier_threshold_mode` on settings.
 *  - `customer_points` is a denormalised wallet (balance / lifetime_points /
 *    rolling_12mo_spend / tier_id) maintained transactionally alongside
 *    `points_ledger` writes.
 *  - `points_ledger` is the source of truth — every change writes one row.
 *    A unique partial index on (order_id, type='earn') prevents double-earning
 *    the same order.
 */

import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import type { PgTransaction } from 'drizzle-orm/pg-core';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { registerHmrTimer, clearHmrTimer, isHmrTimerRegistered } from '@server/lib/hmrTimer';

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

export type EarnMode = 'per_dollar' | 'per_order' | 'per_item';
export type LedgerType = 'earn' | 'redeem' | 'adjust' | 'expire' | 'reverse';
export type TierMode = 'lifetime_points' | 'rolling_12mo_spend';

export interface LoyaltySettings {
  restaurant_id: string;
  enabled: boolean;
  earn_mode: EarnMode;
  earn_rate: number;
  redeem_points_per_unit: number;
  redeem_unit_value: number;
  expiry_months: number | null;
  min_redeem_points: number;
  eligibility_order_types: string[];
  eligibility_channels: string[];
  tier_threshold_mode: TierMode;
  notify_on_earn: boolean;
  notify_on_tier_up: boolean;
}

export interface LoyaltyTier {
  id: string;
  restaurant_id: string;
  name: string;
  threshold: number;
  earn_multiplier: number;
  auto_discount_pct: number;
  free_delivery: boolean;
  perks_text: string | null;
  badge_color: string | null;
  display_order: number;
}

export interface CustomerPoints {
  customer_id: string;
  restaurant_id: string;
  balance: number;
  lifetime_points: number;
  rolling_12mo_spend: number;
  tier_id: string | null;
}

export interface LedgerRow {
  id: string;
  type: LedgerType;
  points: number;
  order_id: string | null;
  reason: string | null;
  expires_at: string | null;
  created_at: string;
}

export interface CustomerLoyaltyView {
  enabled: boolean;
  settings: LoyaltySettings | null;
  wallet: CustomerPoints | null;
  tier: LoyaltyTier | null;
  next_tier: LoyaltyTier | null;
  progress_to_next: number; // 0..1
  ledger?: LedgerRow[];
}

const DEFAULT_TIERS: Array<Omit<LoyaltyTier, 'id' | 'restaurant_id'>> = [
  { name: 'Bronze', threshold: 0,    earn_multiplier: 1,    auto_discount_pct: 0, free_delivery: false, perks_text: 'Welcome to the loyalty program', badge_color: '#cd7f32', display_order: 0 },
  { name: 'Silver', threshold: 500,  earn_multiplier: 1.25, auto_discount_pct: 0, free_delivery: false, perks_text: '1.25× points on every order',     badge_color: '#9ca3af', display_order: 1 },
  { name: 'Gold',   threshold: 2000, earn_multiplier: 1.5,  auto_discount_pct: 5, free_delivery: true,  perks_text: '1.5× points, 5% off, free delivery', badge_color: '#facc15', display_order: 2 },
];

// ───────────────────────── settings ─────────────────────────

function defaultSettings(restaurantId: string): LoyaltySettings {
  return {
    restaurant_id: restaurantId,
    enabled: false,
    earn_mode: 'per_dollar',
    earn_rate: 1,
    redeem_points_per_unit: 100,
    redeem_unit_value: 5,
    expiry_months: null,
    min_redeem_points: 100,
    eligibility_order_types: ['dine-in', 'takeaway', 'delivery'],
    eligibility_channels: ['chat', 'voice', 'whatsapp', 'storefront'],
    tier_threshold_mode: 'lifetime_points',
    notify_on_earn: true,
    notify_on_tier_up: true,
  };
}

function mapSettings(row: Record<string, unknown>): LoyaltySettings {
  const arr = (v: unknown, fallback: string[]): string[] => {
    if (Array.isArray(v)) return v.filter((s): s is string => typeof s === 'string');
    if (typeof v === 'string') {
      try {
        const parsed = JSON.parse(v);
        if (Array.isArray(parsed)) return parsed.filter((s): s is string => typeof s === 'string');
      } catch { /* ignore */ }
    }
    return fallback;
  };
  return {
    restaurant_id: row.restaurant_id as string,
    enabled: row.enabled === true,
    earn_mode: (row.earn_mode as EarnMode) ?? 'per_dollar',
    earn_rate: Number(row.earn_rate ?? 1),
    redeem_points_per_unit: Number(row.redeem_points_per_unit ?? 100),
    redeem_unit_value: Number(row.redeem_unit_value ?? 5),
    expiry_months: row.expiry_months == null ? null : Number(row.expiry_months),
    min_redeem_points: Number(row.min_redeem_points ?? 100),
    eligibility_order_types: arr(row.eligibility_order_types, ['dine-in','takeaway','delivery']),
    eligibility_channels: arr(row.eligibility_channels, ['chat','voice','whatsapp','storefront']),
    tier_threshold_mode: (row.tier_threshold_mode as TierMode) ?? 'lifetime_points',
    notify_on_earn: row.notify_on_earn === undefined || row.notify_on_earn === null ? true : row.notify_on_earn === true,
    notify_on_tier_up: row.notify_on_tier_up === undefined || row.notify_on_tier_up === null ? true : row.notify_on_tier_up === true,
  };
}

export async function getSettings(restaurantId: string): Promise<LoyaltySettings> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT * FROM loyalty_settings WHERE restaurant_id = ${restaurantId} LIMIT 1
  `);
  const row = rows[0] as Record<string, unknown> | undefined;
  return row ? mapSettings(row) : defaultSettings(restaurantId);
}

export async function ensureDefaults(restaurantId: string): Promise<void> {
  await initDatabase();
  await db.execute(sql`
    INSERT INTO loyalty_settings (restaurant_id) VALUES (${restaurantId})
    ON CONFLICT (restaurant_id) DO NOTHING
  `);
  const { rows } = await db.execute(sql`SELECT COUNT(*)::int AS n FROM loyalty_tiers WHERE restaurant_id = ${restaurantId}`);
  const n = Number((rows[0] as { n: number } | undefined)?.n ?? 0);
  if (n === 0) {
    for (const t of DEFAULT_TIERS) {
      await db.execute(sql`
        INSERT INTO loyalty_tiers (restaurant_id, name, threshold, earn_multiplier, auto_discount_pct, free_delivery, perks_text, badge_color, display_order)
        VALUES (${restaurantId}, ${t.name}, ${t.threshold}, ${t.earn_multiplier}, ${t.auto_discount_pct}, ${t.free_delivery}, ${t.perks_text}, ${t.badge_color}, ${t.display_order})
      `);
    }
  }
}

export async function updateSettings(
  restaurantId: string,
  patch: Partial<Omit<LoyaltySettings, 'restaurant_id'>>
): Promise<LoyaltySettings> {
  await initDatabase();
  await ensureDefaults(restaurantId);

  const sets: SQL[] = [];
  if (patch.enabled !== undefined) sets.push(sql`enabled = ${!!patch.enabled}`);
  if (patch.earn_mode !== undefined) {
    if (!['per_dollar','per_order','per_item'].includes(patch.earn_mode)) {
      throw new ValidationError('Invalid earn_mode');
    }
    sets.push(sql`earn_mode = ${patch.earn_mode}`);
  }
  if (patch.earn_rate !== undefined) {
    const r = Number(patch.earn_rate);
    if (!Number.isFinite(r) || r < 0) throw new ValidationError('earn_rate must be ≥ 0');
    sets.push(sql`earn_rate = ${r}`);
  }
  if (patch.redeem_points_per_unit !== undefined) {
    const v = Math.floor(Number(patch.redeem_points_per_unit));
    if (!Number.isFinite(v) || v <= 0) throw new ValidationError('redeem_points_per_unit must be > 0');
    sets.push(sql`redeem_points_per_unit = ${v}`);
  }
  if (patch.redeem_unit_value !== undefined) {
    const v = Number(patch.redeem_unit_value);
    if (!Number.isFinite(v) || v <= 0) throw new ValidationError('redeem_unit_value must be > 0');
    sets.push(sql`redeem_unit_value = ${v}`);
  }
  if (patch.expiry_months !== undefined) {
    if (patch.expiry_months === null) sets.push(sql`expiry_months = NULL`);
    else {
      const v = Math.floor(Number(patch.expiry_months));
      if (!Number.isFinite(v) || v < 1) throw new ValidationError('expiry_months must be ≥ 1 or null');
      sets.push(sql`expiry_months = ${v}`);
    }
  }
  if (patch.min_redeem_points !== undefined) {
    const v = Math.max(0, Math.floor(Number(patch.min_redeem_points)));
    sets.push(sql`min_redeem_points = ${v}`);
  }
  if (patch.eligibility_order_types !== undefined) {
    const v = Array.isArray(patch.eligibility_order_types) ? patch.eligibility_order_types : [];
    sets.push(sql`eligibility_order_types = ${JSON.stringify(v)}::jsonb`);
  }
  if (patch.eligibility_channels !== undefined) {
    const v = Array.isArray(patch.eligibility_channels) ? patch.eligibility_channels : [];
    sets.push(sql`eligibility_channels = ${JSON.stringify(v)}::jsonb`);
  }
  if (patch.tier_threshold_mode !== undefined) {
    if (!['lifetime_points','rolling_12mo_spend'].includes(patch.tier_threshold_mode)) {
      throw new ValidationError('Invalid tier_threshold_mode');
    }
    sets.push(sql`tier_threshold_mode = ${patch.tier_threshold_mode}`);
  }
  if (patch.notify_on_earn !== undefined) sets.push(sql`notify_on_earn = ${!!patch.notify_on_earn}`);
  if (patch.notify_on_tier_up !== undefined) sets.push(sql`notify_on_tier_up = ${!!patch.notify_on_tier_up}`);

  if (sets.length > 0) {
    sets.push(sql`updated_at = NOW()`);
    await db.execute(sql`
      UPDATE loyalty_settings SET ${sql.join(sets, sql`, `)} WHERE restaurant_id = ${restaurantId}
    `);
  }
  return getSettings(restaurantId);
}

// ───────────────────────── tiers ─────────────────────────

function mapTier(row: Record<string, unknown>): LoyaltyTier {
  return {
    id: row.id as string,
    restaurant_id: row.restaurant_id as string,
    name: row.name as string,
    threshold: Number(row.threshold ?? 0),
    earn_multiplier: Number(row.earn_multiplier ?? 1),
    auto_discount_pct: Number(row.auto_discount_pct ?? 0),
    free_delivery: row.free_delivery === true,
    perks_text: (row.perks_text as string) ?? null,
    badge_color: (row.badge_color as string) ?? null,
    display_order: Number(row.display_order ?? 0),
  };
}

export async function listTiers(restaurantId: string): Promise<LoyaltyTier[]> {
  await initDatabase();
  await ensureDefaults(restaurantId);
  const { rows } = await db.execute(sql`
    SELECT * FROM loyalty_tiers WHERE restaurant_id = ${restaurantId}
    ORDER BY threshold ASC, display_order ASC, name ASC
  `);
  return (rows as Record<string, unknown>[]).map(mapTier);
}

export async function createTier(
  restaurantId: string,
  data: Partial<Omit<LoyaltyTier, 'id' | 'restaurant_id'>> & { name: string },
): Promise<LoyaltyTier> {
  await initDatabase();
  if (!data.name?.trim()) throw new ValidationError('Tier name is required');
  const { rows } = await db.execute(sql`
    INSERT INTO loyalty_tiers (restaurant_id, name, threshold, earn_multiplier, auto_discount_pct, free_delivery, perks_text, badge_color, display_order)
    VALUES (${restaurantId}, ${data.name.trim()}, ${Number(data.threshold ?? 0)},
            ${Number(data.earn_multiplier ?? 1)}, ${Number(data.auto_discount_pct ?? 0)},
            ${!!data.free_delivery}, ${data.perks_text ?? null}, ${data.badge_color ?? null},
            ${Number(data.display_order ?? 0)})
    RETURNING *
  `);
  return mapTier(rows[0] as Record<string, unknown>);
}

export async function updateTier(
  restaurantId: string,
  tierId: string,
  patch: Partial<Omit<LoyaltyTier, 'id' | 'restaurant_id'>>,
): Promise<LoyaltyTier> {
  await initDatabase();
  const sets: SQL[] = [];
  if (patch.name !== undefined) {
    if (!patch.name.trim()) throw new ValidationError('Tier name cannot be empty');
    sets.push(sql`name = ${patch.name.trim()}`);
  }
  if (patch.threshold !== undefined) sets.push(sql`threshold = ${Number(patch.threshold)}`);
  if (patch.earn_multiplier !== undefined) sets.push(sql`earn_multiplier = ${Number(patch.earn_multiplier)}`);
  if (patch.auto_discount_pct !== undefined) sets.push(sql`auto_discount_pct = ${Number(patch.auto_discount_pct)}`);
  if (patch.free_delivery !== undefined) sets.push(sql`free_delivery = ${!!patch.free_delivery}`);
  if (patch.perks_text !== undefined) sets.push(sql`perks_text = ${patch.perks_text}`);
  if (patch.badge_color !== undefined) sets.push(sql`badge_color = ${patch.badge_color}`);
  if (patch.display_order !== undefined) sets.push(sql`display_order = ${Number(patch.display_order)}`);
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);
  const { rows } = await db.execute(sql`
    UPDATE loyalty_tiers SET ${sql.join(sets, sql`, `)}
    WHERE id = ${tierId} AND restaurant_id = ${restaurantId}
    RETURNING *
  `);
  if (!rows[0]) throw new NotFoundError('Tier');
  return mapTier(rows[0] as Record<string, unknown>);
}

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

// ───────────────────────── wallet helpers ─────────────────────────

type Tx = PgTransaction<any, any, any>;
type DbLike = typeof db | Tx;

async function getOrCreateWallet(restaurantId: string, customerId: string, dbx: DbLike = db): Promise<CustomerPoints> {
  const { rows } = await dbx.execute(sql`
    INSERT INTO customer_points (restaurant_id, customer_id) VALUES (${restaurantId}, ${customerId})
    ON CONFLICT (restaurant_id, customer_id) DO UPDATE SET updated_at = customer_points.updated_at
    RETURNING *
  `);
  const r = rows[0] as Record<string, unknown>;
  return {
    customer_id: r.customer_id as string,
    restaurant_id: r.restaurant_id as string,
    balance: Number(r.balance ?? 0),
    lifetime_points: Number(r.lifetime_points ?? 0),
    rolling_12mo_spend: Number(r.rolling_12mo_spend ?? 0),
    tier_id: (r.tier_id as string) ?? null,
  };
}

async function pickTierFor(
  restaurantId: string,
  metric: number,
  dbx: DbLike = db,
): Promise<LoyaltyTier | null> {
  const { rows } = await dbx.execute(sql`
    SELECT * FROM loyalty_tiers
    WHERE restaurant_id = ${restaurantId} AND threshold <= ${metric}
    ORDER BY threshold DESC, display_order DESC
    LIMIT 1
  `);
  return rows[0] ? mapTier(rows[0] as Record<string, unknown>) : null;
}

/** Tier upgrades only (no downgrades) — re-evaluates wallet.tier_id. */
export async function recalculateTier(restaurantId: string, customerId: string, dbx: DbLike = db): Promise<LoyaltyTier | null> {
  const wallet = await getOrCreateWallet(restaurantId, customerId, dbx);
  const settings = await getSettings(restaurantId);
  const metric = settings.tier_threshold_mode === 'rolling_12mo_spend'
    ? wallet.rolling_12mo_spend
    : wallet.lifetime_points;
  const newTier = await pickTierFor(restaurantId, metric, dbx);
  if (!newTier) return null;
  // Only upgrade — never downgrade.
  if (wallet.tier_id) {
    const { rows: cur } = await dbx.execute(sql`SELECT threshold FROM loyalty_tiers WHERE id = ${wallet.tier_id} LIMIT 1`);
    const curThreshold = Number((cur[0] as { threshold?: number } | undefined)?.threshold ?? -1);
    if (newTier.threshold <= curThreshold) {
      // refresh existing tier shape
      const { rows: t } = await dbx.execute(sql`SELECT * FROM loyalty_tiers WHERE id = ${wallet.tier_id} LIMIT 1`);
      return t[0] ? mapTier(t[0] as Record<string, unknown>) : null;
    }
  }
  await dbx.execute(sql`
    UPDATE customer_points SET tier_id = ${newTier.id}, updated_at = NOW()
    WHERE restaurant_id = ${restaurantId} AND customer_id = ${customerId}
  `);
  return newTier;
}

// ───────────────────────── earn / redeem ─────────────────────────

export interface EarnContext {
  restaurantId: string;
  customerId: string | null;
  channel: string | null;
  deliveryType: string | null;
  subtotal: number;        // pre-tax, after discounts
  itemCount: number;
}

/** Compute (preview) the points an order would earn. Does not write. */
export async function previewEarn(ctx: EarnContext): Promise<{ points: number; multiplier: number; reason: string }> {
  const settings = await getSettings(ctx.restaurantId);
  if (!settings.enabled) return { points: 0, multiplier: 1, reason: 'loyalty_disabled' };
  if (!ctx.customerId) return { points: 0, multiplier: 1, reason: 'no_customer' };

  if (ctx.deliveryType && !settings.eligibility_order_types.includes(ctx.deliveryType)) {
    return { points: 0, multiplier: 1, reason: 'order_type_excluded' };
  }
  if (ctx.channel && settings.eligibility_channels.length > 0 && !settings.eligibility_channels.includes(ctx.channel)) {
    return { points: 0, multiplier: 1, reason: 'channel_excluded' };
  }

  let base = 0;
  switch (settings.earn_mode) {
    case 'per_dollar': base = Math.floor(Math.max(0, ctx.subtotal) * settings.earn_rate); break;
    case 'per_order':  base = Math.floor(settings.earn_rate); break;
    case 'per_item':   base = Math.floor(Math.max(0, ctx.itemCount) * settings.earn_rate); break;
  }

  // Tier multiplier (current tier of this customer)
  const wallet = await getOrCreateWallet(ctx.restaurantId, ctx.customerId);
  let multiplier = 1;
  if (wallet.tier_id) {
    const { rows } = await db.execute(sql`SELECT earn_multiplier FROM loyalty_tiers WHERE id = ${wallet.tier_id} LIMIT 1`);
    multiplier = Number((rows[0] as { earn_multiplier?: number } | undefined)?.earn_multiplier ?? 1) || 1;
  }
  const points = Math.max(0, Math.floor(base * multiplier));
  return { points, multiplier, reason: 'ok' };
}

interface CommitEarnInput {
  restaurantId: string;
  customerId: string;
  orderId: string;
  channel: string | null;
  deliveryType: string | null;
  subtotal: number;
  itemCount: number;
  total: number;
}

export interface EarnNotification {
  restaurantId: string;
  customerId: string;
  orderRef: string;
  pointsEarned: number;
  newBalance: number;
  notifyOnEarn: boolean;
  tierUp: {
    tierName: string;
    perksText: string | null;
    freeDelivery: boolean;
    autoDiscountPct: number;
    earnMultiplier: number;
  } | null;
  notifyOnTierUp: boolean;
}

export interface CommitEarnResult {
  points: number;
  ledger_id: string;
  /**
   * Pending email notifications. Dispatch via `dispatchEarnNotifications`
   * AFTER the enclosing transaction commits — emails must never be queued
   * for an earn that later rolled back.
   */
  notifications: EarnNotification | null;
}

/**
 * Commit points earned for a completed order, writing one ledger row and
 * updating the wallet. Idempotent on (order_id, type='earn') via the unique
 * partial index — repeat calls return null.
 */
export async function commitEarn(input: CommitEarnInput, dbx: DbLike = db): Promise<CommitEarnResult | null> {
  const settings = await getSettings(input.restaurantId);
  if (!settings.enabled) return null;
  const preview = await previewEarn({
    restaurantId: input.restaurantId,
    customerId: input.customerId,
    channel: input.channel,
    deliveryType: input.deliveryType,
    subtotal: input.subtotal,
    itemCount: input.itemCount,
  });
  if (preview.points <= 0) return null;

  // Idempotency check first
  const { rows: existing } = await dbx.execute(sql`
    SELECT id FROM points_ledger WHERE order_id = ${input.orderId} AND type = 'earn' LIMIT 1
  `);
  if (existing[0]) return null;

  const expiresAt = settings.expiry_months
    ? sql`NOW() + (${settings.expiry_months} || ' months')::interval`
    : sql`NULL`;

  await getOrCreateWallet(input.restaurantId, input.customerId, dbx);

  const { rows: ledRows } = await dbx.execute(sql`
    INSERT INTO points_ledger (restaurant_id, customer_id, type, points, order_id, reason, expires_at)
    VALUES (${input.restaurantId}, ${input.customerId}, 'earn', ${preview.points}, ${input.orderId},
            ${`Earned on order ${input.orderId.slice(0,8)}`}, ${expiresAt})
    RETURNING id
  `);
  const ledgerId = (ledRows[0] as { id: string }).id;

  await dbx.execute(sql`
    UPDATE customer_points
    SET balance = balance + ${preview.points},
        lifetime_points = lifetime_points + ${preview.points},
        rolling_12mo_spend = rolling_12mo_spend + ${input.total},
        updated_at = NOW()
    WHERE restaurant_id = ${input.restaurantId} AND customer_id = ${input.customerId}
  `);
  // Stamp earn count on the order so later reversals know how much to undo
  await dbx.execute(sql`
    UPDATE orders SET loyalty_points_earned = ${preview.points}
    WHERE id = ${input.orderId} AND restaurant_id = ${input.restaurantId}
  `);

  // Capture pre-recalc tier so we can detect a true upgrade (not just a
  // re-evaluation that returns the existing tier).
  const { rows: preTierRows } = await dbx.execute(sql`
    SELECT tier_id FROM customer_points
    WHERE restaurant_id = ${input.restaurantId} AND customer_id = ${input.customerId}
  `);
  const oldTierId = (preTierRows[0] as { tier_id?: string | null } | undefined)?.tier_id ?? null;

  const newTier = await recalculateTier(input.restaurantId, input.customerId, dbx);

  // Fetch the post-earn balance for the email body.
  const { rows: balRows } = await dbx.execute(sql`
    SELECT balance FROM customer_points
    WHERE restaurant_id = ${input.restaurantId} AND customer_id = ${input.customerId}
  `);
  const newBalance = Number((balRows[0] as { balance?: number } | undefined)?.balance ?? 0);

  const tierUpgraded = !!(newTier && newTier.id !== oldTierId);
  const notifications: EarnNotification | null =
    (settings.notify_on_earn || (settings.notify_on_tier_up && tierUpgraded))
      ? {
          restaurantId: input.restaurantId,
          customerId: input.customerId,
          orderRef: input.orderId.slice(0, 8).toUpperCase(),
          pointsEarned: preview.points,
          newBalance,
          notifyOnEarn: settings.notify_on_earn,
          tierUp: tierUpgraded && newTier ? {
            tierName: newTier.name,
            perksText: newTier.perks_text,
            freeDelivery: newTier.free_delivery,
            autoDiscountPct: Number(newTier.auto_discount_pct || 0),
            earnMultiplier: Number(newTier.earn_multiplier || 1),
          } : null,
          notifyOnTierUp: settings.notify_on_tier_up,
        }
      : null;

  return { points: preview.points, ledger_id: ledgerId, notifications };
}

/**
 * Dispatch loyalty earn/tier-up emails. MUST only be called after the
 * enclosing transaction (the one that called `commitEarn`) has committed,
 * otherwise emails could be queued for changes that later rolled back.
 *
 * Looks up the customer's email/name via the global `db` connection and
 * enqueues the templated emails — both points-earned and tier-up — based on
 * the per-restaurant notification toggles captured by `commitEarn`.
 */
export async function dispatchEarnNotifications(n: EarnNotification): Promise<void> {
  if (!n.notifyOnEarn && !(n.notifyOnTierUp && n.tierUp)) return;
  try {
    const { rows: cRows } = await db.execute(sql`
      SELECT name, email FROM customers
      WHERE id = ${n.customerId} AND restaurant_id = ${n.restaurantId} LIMIT 1
    `);
    const cust = cRows[0] as { name?: string; email?: string } | undefined;
    if (!cust?.email) return;
    // Lazy import avoids a circular dependency
    // (email/notify → outbox → render → DB → loyalty.service).
    const notify = await import('./email/notify');
    if (n.notifyOnEarn) {
      await notify.notifyLoyaltyPointsEarned({
        restaurantId: n.restaurantId,
        customerEmail: cust.email,
        customerName: cust.name || 'there',
        pointsEarned: n.pointsEarned,
        newBalance: n.newBalance,
        orderRef: n.orderRef,
      });
    }
    if (n.notifyOnTierUp && n.tierUp) {
      await notify.notifyLoyaltyTierUp({
        restaurantId: n.restaurantId,
        customerEmail: cust.email,
        customerName: cust.name || 'there',
        tierName: n.tierUp.tierName,
        perksText: n.tierUp.perksText,
        freeDelivery: n.tierUp.freeDelivery,
        autoDiscountPct: n.tierUp.autoDiscountPct,
        earnMultiplier: n.tierUp.earnMultiplier,
      });
    }
  } catch (e) {
    log.error({ err: e }, 'loyalty dispatchEarnNotifications failed');
  }
}

/**
 * Reverse points previously redeemed on an order (e.g. cancellation/refund).
 * Idempotent — skips if a matching reverse entry already exists.
 * Returns the number of points restored, or null if nothing was redeemed.
 */
export async function reverseRedeemForOrder(restaurantId: string, orderId: string, dbx: DbLike = db): Promise<{ points: number } | null> {
  const { rows } = await dbx.execute(sql`
    SELECT customer_id, points FROM points_ledger
    WHERE order_id = ${orderId} AND type = 'redeem' LIMIT 1
  `);
  const redeem = rows[0] as { customer_id: string; points: number } | undefined;
  if (!redeem) return null;

  // Skip if a redeem-reverse already exists for this order
  const { rows: rev } = await dbx.execute(sql`
    SELECT id FROM points_ledger WHERE order_id = ${orderId} AND type = 'reverse' AND points > 0 LIMIT 1
  `);
  if (rev[0]) return null;

  const restored = Math.abs(redeem.points);
  await dbx.execute(sql`
    INSERT INTO points_ledger (restaurant_id, customer_id, type, points, order_id, reason)
    VALUES (${restaurantId}, ${redeem.customer_id}, 'reverse', ${restored}, ${orderId},
            ${'Order cancelled — redeemed points restored'})
  `);
  await dbx.execute(sql`
    UPDATE customer_points
    SET balance = balance + ${restored}, updated_at = NOW()
    WHERE restaurant_id = ${restaurantId} AND customer_id = ${redeem.customer_id}
  `);
  return { points: restored };
}

/** Reverse points previously earned on an order (e.g. cancellation/refund). */
export async function reverseEarnForOrder(restaurantId: string, orderId: string, dbx: DbLike = db): Promise<{ points: number } | null> {
  const { rows } = await dbx.execute(sql`
    SELECT customer_id, points FROM points_ledger
    WHERE order_id = ${orderId} AND type = 'earn' LIMIT 1
  `);
  const earn = rows[0] as { customer_id: string; points: number } | undefined;
  if (!earn) return null;

  // Skip if a reverse already exists
  const { rows: rev } = await dbx.execute(sql`
    SELECT id FROM points_ledger WHERE order_id = ${orderId} AND type = 'reverse' LIMIT 1
  `);
  if (rev[0]) return null;

  await dbx.execute(sql`
    INSERT INTO points_ledger (restaurant_id, customer_id, type, points, order_id, reason)
    VALUES (${restaurantId}, ${earn.customer_id}, 'reverse', ${-Math.abs(earn.points)}, ${orderId},
            ${'Order cancelled — points reversed'})
  `);
  await dbx.execute(sql`
    UPDATE customer_points
    SET balance = GREATEST(0, balance - ${Math.abs(earn.points)}),
        lifetime_points = GREATEST(0, lifetime_points - ${Math.abs(earn.points)}),
        updated_at = NOW()
    WHERE restaurant_id = ${restaurantId} AND customer_id = ${earn.customer_id}
  `);
  return { points: Math.abs(earn.points) };
}

export interface RedeemPreview {
  allowed_points: number;
  discount_amount: number;
  ratio_points_per_unit: number;
  ratio_unit_value: number;
  reason: string;
}

/**
 * Compute how many points can be redeemed against a subtotal. Clamps at the
 * wallet balance, settings.min_redeem_points, and never produces a discount
 * larger than the subtotal itself.
 */
export async function previewRedeem(args: {
  restaurantId: string;
  customerId: string | null;
  pointsRequested: number;
  subtotal: number;
}): Promise<RedeemPreview> {
  const settings = await getSettings(args.restaurantId);
  const empty = (reason: string): RedeemPreview => ({
    allowed_points: 0, discount_amount: 0,
    ratio_points_per_unit: settings.redeem_points_per_unit,
    ratio_unit_value: settings.redeem_unit_value,
    reason,
  });
  if (!settings.enabled) return empty('loyalty_disabled');
  if (!args.customerId) return empty('no_customer');

  const wallet = await getOrCreateWallet(args.restaurantId, args.customerId);
  if (wallet.balance < settings.min_redeem_points) return empty('below_min');

  const requested = Math.max(0, Math.floor(args.pointsRequested || 0));
  if (requested <= 0) return empty('zero_requested');
  if (requested < settings.min_redeem_points) return empty('below_min');

  // Redemption value per point
  const valuePerPoint = settings.redeem_unit_value / settings.redeem_points_per_unit;
  const subtotal = Math.max(0, Number(args.subtotal) || 0);
  const maxPointsBySubtotal = Math.floor(subtotal / valuePerPoint);
  const allowed = Math.min(requested, wallet.balance, maxPointsBySubtotal);

  if (allowed < settings.min_redeem_points) return empty('below_min');

  // Round discount to cents
  const discount = Math.min(subtotal, Math.round(allowed * valuePerPoint * 100) / 100);

  return {
    allowed_points: allowed,
    discount_amount: discount,
    ratio_points_per_unit: settings.redeem_points_per_unit,
    ratio_unit_value: settings.redeem_unit_value,
    reason: 'ok',
  };
}

interface CommitRedeemInput {
  restaurantId: string;
  customerId: string;
  orderId: string;
  points: number;
  discount: number;
}

/** Commit a redemption (writes ledger row + decrements wallet). */
export async function commitRedeem(input: CommitRedeemInput, dbx: DbLike = db): Promise<void> {
  if (input.points <= 0) return;
  await getOrCreateWallet(input.restaurantId, input.customerId, dbx);

  // Verify balance still covers it (best-effort guard against races)
  const { rows: wRows } = await dbx.execute(sql`
    SELECT balance FROM customer_points
    WHERE restaurant_id = ${input.restaurantId} AND customer_id = ${input.customerId} FOR UPDATE
  `);
  const balance = Number((wRows[0] as { balance?: number } | undefined)?.balance ?? 0);
  if (balance < input.points) {
    throw new ValidationError('Insufficient points balance for redemption');
  }

  await dbx.execute(sql`
    INSERT INTO points_ledger (restaurant_id, customer_id, type, points, order_id, reason)
    VALUES (${input.restaurantId}, ${input.customerId}, 'redeem', ${-Math.abs(input.points)}, ${input.orderId},
            ${`Redeemed for $${input.discount.toFixed(2)} discount`})
  `);
  await dbx.execute(sql`
    UPDATE customer_points
    SET balance = balance - ${input.points}, updated_at = NOW()
    WHERE restaurant_id = ${input.restaurantId} AND customer_id = ${input.customerId}
  `);
}

// ───────────────────────── manual adjustment ─────────────────────────

export async function adjustPoints(
  restaurantId: string,
  customerId: string,
  delta: number,
  reason: string,
  createdBy?: string | null,
): Promise<{ delta: number; balance: number }> {
  await initDatabase();
  if (!Number.isFinite(delta) || delta === 0) throw new ValidationError('Delta must be a non-zero number');
  if (!reason?.trim()) throw new ValidationError('Reason is required');
  const intDelta = Math.trunc(delta);

  return db.transaction(async (tx) => {
    await getOrCreateWallet(restaurantId, customerId, tx);
    const { rows: wRows } = await tx.execute(sql`
      SELECT balance, lifetime_points FROM customer_points
      WHERE restaurant_id = ${restaurantId} AND customer_id = ${customerId} FOR UPDATE
    `);
    const cur = wRows[0] as { balance: number; lifetime_points: number };
    if (intDelta < 0 && cur.balance + intDelta < 0) {
      throw new ValidationError('Cannot reduce balance below zero');
    }
    await tx.execute(sql`
      INSERT INTO points_ledger (restaurant_id, customer_id, type, points, reason, created_by)
      VALUES (${restaurantId}, ${customerId}, 'adjust', ${intDelta}, ${reason.trim()}, ${createdBy ?? null})
    `);
    // Lifetime grows on positive adjustments, never shrinks on negative
    const lifetimeBump = intDelta > 0 ? intDelta : 0;
    await tx.execute(sql`
      UPDATE customer_points
      SET balance = balance + ${intDelta},
          lifetime_points = lifetime_points + ${lifetimeBump},
          updated_at = NOW()
      WHERE restaurant_id = ${restaurantId} AND customer_id = ${customerId}
    `);
    if (intDelta > 0) await recalculateTier(restaurantId, customerId, tx);
    const { rows: nw } = await tx.execute(sql`
      SELECT balance FROM customer_points WHERE restaurant_id = ${restaurantId} AND customer_id = ${customerId}
    `);
    return { delta: intDelta, balance: Number((nw[0] as { balance: number }).balance) };
  });
}

// ───────────────────────── ledger / view ─────────────────────────

export async function listLedger(
  restaurantId: string,
  customerId: string,
  page = 1,
  limit = 50,
): Promise<{ data: LedgerRow[]; total: number; page: number; limit: number; pages: number }> {
  await initDatabase();
  const offset = (page - 1) * limit;
  const { rows: countRows } = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM points_ledger
    WHERE restaurant_id = ${restaurantId} AND customer_id = ${customerId}
  `);
  const total = Number((countRows[0] as { n: number } | undefined)?.n ?? 0);
  const { rows } = await db.execute(sql`
    SELECT id, type, points, order_id, reason, expires_at, created_at
    FROM points_ledger
    WHERE restaurant_id = ${restaurantId} AND customer_id = ${customerId}
    ORDER BY created_at DESC
    LIMIT ${limit} OFFSET ${offset}
  `);
  return {
    data: (rows as Record<string, unknown>[]).map((r) => ({
      id: r.id as string,
      type: r.type as LedgerType,
      points: Number(r.points),
      order_id: (r.order_id as string) ?? null,
      reason: (r.reason as string) ?? null,
      expires_at: (r.expires_at as string) ?? null,
      created_at: r.created_at as string,
    })),
    total, page, limit, pages: Math.max(1, Math.ceil(total / limit)),
  };
}

export async function getCustomerLoyalty(
  restaurantId: string,
  customerId: string,
  opts: { includeLedger?: boolean } = {},
): Promise<CustomerLoyaltyView> {
  await initDatabase();
  const settings = await getSettings(restaurantId);
  const tiers = await listTiers(restaurantId);
  const wallet = await getOrCreateWallet(restaurantId, customerId);

  const metric = settings.tier_threshold_mode === 'rolling_12mo_spend'
    ? wallet.rolling_12mo_spend
    : wallet.lifetime_points;

  let currentTier: LoyaltyTier | null = null;
  let nextTier: LoyaltyTier | null = null;
  for (const t of tiers) {
    if (t.threshold <= metric) currentTier = t;
    else { nextTier = t; break; }
  }
  let progress = 1;
  if (nextTier) {
    const base = currentTier?.threshold ?? 0;
    const span = Math.max(1, nextTier.threshold - base);
    progress = Math.max(0, Math.min(1, (metric - base) / span));
  }

  let ledger: LedgerRow[] | undefined;
  if (opts.includeLedger) {
    const l = await listLedger(restaurantId, customerId, 1, 30);
    ledger = l.data;
  }

  return {
    enabled: settings.enabled,
    settings,
    wallet,
    tier: currentTier,
    next_tier: nextTier,
    progress_to_next: progress,
    ledger,
  };
}

// ───────────────────────── lookup by phone (storefront) ─────────────────────────

export async function lookupCustomerByPhone(restaurantId: string, phone: string): Promise<{ customer_id: string } | null> {
  await initDatabase();
  // Storefront phone inputs are free-form (E.164, local, formatted). Customer
  // records are stored verbatim. We match on a digit-only normalization on
  // both sides so "+1 (555) 123-4567" and "+15551234567" resolve to the same
  // customer. A strict exact-digits match is required; suffix matching is
  // intentionally omitted to prevent false-positive disclosure across numbers
  // that share the same trailing digits.
  const digits = phone.replace(/\D/g, '');
  if (digits.length < 6) return null;
  const { rows } = await db.execute(sql`
    SELECT id FROM customers
    WHERE restaurant_id = ${restaurantId}
      AND phone IS NOT NULL
      AND regexp_replace(phone, '[^0-9]', '', 'g') = ${digits}
    LIMIT 1
  `);
  const r = rows[0] as { id?: string } | undefined;
  return r?.id ? { customer_id: r.id } : null;
}

// ───────────────────────── expiry job ─────────────────────────

/**
 * Expire points using FIFO lot consumption per customer.
 *
 * Each `earn` row is a "lot" with an original points value, an expires_at
 * timestamp, and a remaining (unconsumed) balance. Subsequent ledger events
 * draw down lots in chronological order:
 *   - `redeem`, `adjust` (negative), `expire` (negative)  → FIFO consume from
 *     the oldest non-empty lot.
 *   - `reverse` with a matching order_id                  → debits the lot
 *     created by that order's earn (then FIFO if untraceable).
 *   - `adjust` (positive)                                 → ignored (treated
 *     as a non-expiring credit; never sits in a lot).
 *
 * We then expire only the `remaining` value of each lot whose `expires_at`
 * is past. The lot's `expires_at` is NULLed regardless so it isn't reprocessed
 * — even fully-consumed lots get their marker cleared.
 */
export async function expireDue(): Promise<{ expired_rows: number; expired_points: number }> {
  await initDatabase();
  const { rows: customers } = await db.execute(sql`
    SELECT DISTINCT restaurant_id, customer_id
    FROM points_ledger
    WHERE type = 'earn'
      AND expires_at IS NOT NULL
      AND expires_at < NOW()
    LIMIT 200
  `);

  let expiredRows = 0;
  let expiredPoints = 0;

  for (const c of customers as Array<{ restaurant_id: string; customer_id: string }>) {
    try {
      await db.transaction(async (tx) => {
        // Lock the wallet so concurrent redeem/earn can't race the expiry.
        await tx.execute(sql`
          SELECT balance FROM customer_points
          WHERE restaurant_id = ${c.restaurant_id} AND customer_id = ${c.customer_id}
          FOR UPDATE
        `);

        const { rows: events } = await tx.execute(sql`
          SELECT id, type, points, order_id, expires_at, created_at
          FROM points_ledger
          WHERE restaurant_id = ${c.restaurant_id} AND customer_id = ${c.customer_id}
          ORDER BY created_at ASC, id ASC
        `);

        type Lot = { id: string; orderId: string | null; expiresAt: Date | null; remaining: number };
        const lots: Lot[] = [];

        const consumeFifo = (amount: number) => {
          let need = amount;
          for (const lot of lots) {
            if (need <= 0) break;
            if (lot.remaining <= 0) continue;
            const take = Math.min(lot.remaining, need);
            lot.remaining -= take;
            need -= take;
          }
        };

        for (const e of events as Array<{
          id: string; type: string; points: number;
          order_id: string | null; expires_at: string | null; created_at: string;
        }>) {
          const pts = Number(e.points);
          if (e.type === 'earn') {
            lots.push({
              id: e.id,
              orderId: e.order_id,
              expiresAt: e.expires_at ? new Date(e.expires_at) : null,
              remaining: Math.max(0, pts),
            });
          } else if (e.type === 'reverse' && e.order_id) {
            const lot = lots.find((l) => l.orderId === e.order_id);
            const amt = Math.abs(pts);
            if (lot) lot.remaining = Math.max(0, lot.remaining - amt);
            else consumeFifo(amt);
          } else if (e.type === 'redeem' || e.type === 'expire' || (e.type === 'adjust' && pts < 0)) {
            consumeFifo(Math.abs(pts));
          }
          // positive adjust: non-expiring credit, kept outside the lot pool.
        }

        const nowMs = Date.now();
        const overdueLotIds: string[] = [];
        let toExpire = 0;
        for (const lot of lots) {
          if (lot.expiresAt && lot.expiresAt.getTime() < nowMs) {
            overdueLotIds.push(lot.id);
            if (lot.remaining > 0) toExpire += lot.remaining;
          }
        }

        if (overdueLotIds.length === 0) return;

        // Always clear the marker on overdue lots so we don't reprocess them.
        await tx.execute(sql`
          UPDATE points_ledger SET expires_at = NULL
          WHERE id IN (${sql.join(overdueLotIds.map((id) => sql`${id}::uuid`), sql`, `)})
        `);

        if (toExpire > 0) {
          await tx.execute(sql`
            INSERT INTO points_ledger (restaurant_id, customer_id, type, points, reason)
            VALUES (${c.restaurant_id}, ${c.customer_id}, 'expire', ${-toExpire},
                    ${`Expired ${toExpire} pts from ${overdueLotIds.length} lot(s)`})
          `);
          await tx.execute(sql`
            UPDATE customer_points
            SET balance = GREATEST(0, balance - ${toExpire}), updated_at = NOW()
            WHERE restaurant_id = ${c.restaurant_id} AND customer_id = ${c.customer_id}
          `);
          expiredRows += 1;
          expiredPoints += toExpire;
        }
      });
    } catch (err) {
      log.warn({ err, customerId: c.customer_id }, 'loyalty.expireDue failed for customer');
    }
  }
  return { expired_rows: expiredRows, expired_points: expiredPoints };
}

// ───────────────────────── analytics / stats ─────────────────────────

export type LoyaltyStatsPeriod = 'today' | 'week' | 'month' | '3months';

export interface LoyaltyStats {
  enabled: boolean;
  period: LoyaltyStatsPeriod;
  currentStart: string;
  totals: {
    points_outstanding: number;
    wallets: number;
    points_earned: number;
    points_redeemed: number;
    points_expired: number;
    points_adjusted: number;
    points_reversed: number;
    net_points_change: number;
  };
  redemption: {
    total_orders: number;
    redeemed_orders: number;
    redemption_rate: number; // 0..1
    total_discount: number;
    avg_discount_per_redemption: number;
    redemptions_count: number;
  };
  /** Strict rolling-day windows (independent of the period selector). */
  rolling: {
    last_30_days: { points_earned: number; points_redeemed: number };
    last_90_days: { points_earned: number; points_redeemed: number };
  };
  /**
   * Repeat-customer lift over the last 90 days.
   * Members = customers with a loyalty wallet (customer_points row).
   * Repeat rate = (customers with ≥2 orders) / (customers with ≥1 order)
   * within each cohort. `lift_ratio` is members/non-members (1 = parity).
   */
  repeat_lift: {
    member_customers: number;
    member_repeat_customers: number;
    member_repeat_rate: number;        // 0..1
    nonmember_customers: number;
    nonmember_repeat_customers: number;
    nonmember_repeat_rate: number;     // 0..1
    lift_ratio: number | null;         // null when nonmember rate is 0
    lift_pct: number | null;           // (member - nonmember) * 100
  };
  tier_distribution: Array<{
    tier_id: string | null;
    tier_name: string;
    badge_color: string | null;
    customers: number;
    threshold: number;
    display_order: number;
  }>;
  top_customers: Array<{
    customer_id: string;
    name: string | null;
    email: string | null;
    phone: string | null;
    tier_id: string | null;
    tier_name: string | null;
    badge_color: string | null;
    balance: number;
    lifetime_points: number;
    last_visit: string | null;
  }>;
}

function periodStart(period: LoyaltyStatsPeriod): Date {
  const now = new Date();
  if (period === 'today') return new Date(now.getFullYear(), now.getMonth(), now.getDate());
  if (period === 'week') {
    const d = new Date(now); d.setDate(now.getDate() - 6); d.setHours(0, 0, 0, 0); return d;
  }
  if (period === 'month') return new Date(now.getFullYear(), now.getMonth(), 1);
  return new Date(now.getFullYear(), now.getMonth() - 2, 1);
}

export async function getLoyaltyStats(
  restaurantId: string,
  period: LoyaltyStatsPeriod = 'month',
  branchId?: string,
): Promise<LoyaltyStats> {
  await initDatabase();
  const settings = await getSettings(restaurantId);
  const start = periodStart(period);
  const cs = start.toISOString();
  const branchCond = branchId ? sql` AND branch_id = ${branchId}` : sql``;

  const now = new Date();
  const d30 = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000).toISOString();
  const d90 = new Date(now.getTime() - 90 * 24 * 60 * 60 * 1000).toISOString();

  const [
    walletAgg,
    ledgerAgg,
    orderAgg,
    redeemAgg,
    tierDist,
    topCustomers,
    rolling30,
    rolling90,
    repeatAgg,
  ] = await Promise.all([
    db.execute(sql`
      SELECT COALESCE(SUM(balance), 0)::bigint AS points_outstanding,
             COUNT(*)::int AS wallets
      FROM customer_points WHERE restaurant_id = ${restaurantId}
    `),
    db.execute(sql`
      SELECT
        COALESCE(SUM(points) FILTER (WHERE type = 'earn'), 0)::bigint    AS earned,
        COALESCE(SUM(-points) FILTER (WHERE type = 'redeem'), 0)::bigint AS redeemed,
        COALESCE(SUM(-points) FILTER (WHERE type = 'expire'), 0)::bigint AS expired,
        COALESCE(SUM(points) FILTER (WHERE type = 'adjust'), 0)::bigint  AS adjusted,
        COALESCE(SUM(points) FILTER (WHERE type = 'reverse'), 0)::bigint AS reversed
      FROM points_ledger
      WHERE restaurant_id = ${restaurantId} AND created_at >= ${cs}::timestamptz
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS total_orders
      FROM orders
      WHERE restaurant_id = ${restaurantId}${branchCond}
        AND created_at >= ${cs}::timestamptz
        AND status NOT IN ('cancelled','failed')
    `),
    db.execute(sql`
      SELECT COUNT(*)::int AS redeemed_orders,
             COALESCE(SUM(loyalty_discount), 0)::numeric AS total_discount
      FROM orders
      WHERE restaurant_id = ${restaurantId}${branchCond}
        AND created_at >= ${cs}::timestamptz
        AND status NOT IN ('cancelled','failed')
        AND loyalty_points_redeemed > 0
    `),
    db.execute(sql`
      SELECT t.id AS tier_id, t.name AS tier_name, t.badge_color, t.threshold, t.display_order,
             COUNT(cp.customer_id)::int AS customers
      FROM loyalty_tiers t
      LEFT JOIN customer_points cp
        ON cp.tier_id = t.id AND cp.restaurant_id = ${restaurantId}
      WHERE t.restaurant_id = ${restaurantId}
      GROUP BY t.id, t.name, t.badge_color, t.threshold, t.display_order
      ORDER BY t.threshold ASC, t.display_order ASC
    `),
    db.execute(sql`
      SELECT cp.customer_id, cp.balance, cp.lifetime_points, cp.tier_id,
             c.name, c.email, c.phone, c.last_order_at,
             t.name AS tier_name, t.badge_color
      FROM customer_points cp
      JOIN customers c ON c.id = cp.customer_id AND c.restaurant_id = cp.restaurant_id
      LEFT JOIN loyalty_tiers t ON t.id = cp.tier_id
      WHERE cp.restaurant_id = ${restaurantId}
      ORDER BY cp.lifetime_points DESC, cp.balance DESC
      LIMIT 10
    `),
    db.execute(sql`
      SELECT
        COALESCE(SUM(points) FILTER (WHERE type = 'earn'), 0)::bigint    AS earned,
        COALESCE(SUM(-points) FILTER (WHERE type = 'redeem'), 0)::bigint AS redeemed
      FROM points_ledger
      WHERE restaurant_id = ${restaurantId} AND created_at >= ${d30}::timestamptz
    `),
    db.execute(sql`
      SELECT
        COALESCE(SUM(points) FILTER (WHERE type = 'earn'), 0)::bigint    AS earned,
        COALESCE(SUM(-points) FILTER (WHERE type = 'redeem'), 0)::bigint AS redeemed
      FROM points_ledger
      WHERE restaurant_id = ${restaurantId} AND created_at >= ${d90}::timestamptz
    `),
    db.execute(sql`
      WITH customer_orders AS (
        SELECT o.customer_id, COUNT(*)::int AS order_count
        FROM orders o
        WHERE o.restaurant_id = ${restaurantId}${branchCond}
          AND o.customer_id IS NOT NULL
          AND o.status NOT IN ('cancelled','failed')
          AND o.created_at >= ${d90}::timestamptz
        GROUP BY o.customer_id
      ),
      labeled AS (
        SELECT co.customer_id, co.order_count,
               (cp.customer_id IS NOT NULL) AS is_member
        FROM customer_orders co
        LEFT JOIN customer_points cp
          ON cp.customer_id = co.customer_id AND cp.restaurant_id = ${restaurantId}
      )
      SELECT
        COUNT(*) FILTER (WHERE is_member)::int                              AS member_customers,
        COUNT(*) FILTER (WHERE is_member AND order_count >= 2)::int         AS member_repeat_customers,
        COUNT(*) FILTER (WHERE NOT is_member)::int                          AS nonmember_customers,
        COUNT(*) FILTER (WHERE NOT is_member AND order_count >= 2)::int     AS nonmember_repeat_customers
      FROM labeled
    `),
  ]);

  const w = walletAgg.rows[0] as { points_outstanding: string | number; wallets: number };
  const l = ledgerAgg.rows[0] as Record<string, string | number>;
  const o = orderAgg.rows[0] as { total_orders: number };
  const r = redeemAgg.rows[0] as { redeemed_orders: number; total_discount: string | number };

  const earned = Number(l.earned ?? 0);
  const redeemed = Number(l.redeemed ?? 0);
  const expired = Number(l.expired ?? 0);
  const adjusted = Number(l.adjusted ?? 0);
  const reversed = Number(l.reversed ?? 0);

  const totalOrders = Number(o.total_orders ?? 0);
  const redeemedOrders = Number(r.redeemed_orders ?? 0);
  const totalDiscount = Number(r.total_discount ?? 0);

  // Count of redemption ledger rows in the period (one per redeem event)
  const { rows: rcRows } = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM points_ledger
    WHERE restaurant_id = ${restaurantId} AND type = 'redeem' AND created_at >= ${cs}::timestamptz
  `);
  const redemptionsCount = Number((rcRows[0] as { n?: number } | undefined)?.n ?? 0);

  const r30 = rolling30.rows[0] as { earned: string | number; redeemed: string | number };
  const r90 = rolling90.rows[0] as { earned: string | number; redeemed: string | number };
  const rep = repeatAgg.rows[0] as {
    member_customers: number;
    member_repeat_customers: number;
    nonmember_customers: number;
    nonmember_repeat_customers: number;
  };
  const memberCustomers = Number(rep.member_customers ?? 0);
  const memberRepeat = Number(rep.member_repeat_customers ?? 0);
  const nonmemberCustomers = Number(rep.nonmember_customers ?? 0);
  const nonmemberRepeat = Number(rep.nonmember_repeat_customers ?? 0);
  const memberRate = memberCustomers > 0 ? memberRepeat / memberCustomers : 0;
  const nonmemberRate = nonmemberCustomers > 0 ? nonmemberRepeat / nonmemberCustomers : 0;
  const liftRatio = nonmemberRate > 0 ? memberRate / nonmemberRate : null;
  const liftPct = nonmemberCustomers > 0 ? (memberRate - nonmemberRate) * 100 : null;

  return {
    enabled: settings.enabled,
    period,
    currentStart: cs,
    totals: {
      points_outstanding: Number(w.points_outstanding ?? 0),
      wallets: Number(w.wallets ?? 0),
      points_earned: earned,
      points_redeemed: redeemed,
      points_expired: expired,
      points_adjusted: adjusted,
      points_reversed: reversed,
      net_points_change: earned - redeemed - expired + adjusted + reversed,
    },
    redemption: {
      total_orders: totalOrders,
      redeemed_orders: redeemedOrders,
      redemption_rate: totalOrders > 0 ? redeemedOrders / totalOrders : 0,
      total_discount: totalDiscount,
      avg_discount_per_redemption: redeemedOrders > 0 ? totalDiscount / redeemedOrders : 0,
      redemptions_count: redemptionsCount,
    },
    rolling: {
      last_30_days: {
        points_earned: Number(r30.earned ?? 0),
        points_redeemed: Number(r30.redeemed ?? 0),
      },
      last_90_days: {
        points_earned: Number(r90.earned ?? 0),
        points_redeemed: Number(r90.redeemed ?? 0),
      },
    },
    repeat_lift: {
      member_customers: memberCustomers,
      member_repeat_customers: memberRepeat,
      member_repeat_rate: memberRate,
      nonmember_customers: nonmemberCustomers,
      nonmember_repeat_customers: nonmemberRepeat,
      nonmember_repeat_rate: nonmemberRate,
      lift_ratio: liftRatio,
      lift_pct: liftPct,
    },
    tier_distribution: (tierDist.rows as Record<string, unknown>[]).map((row) => ({
      tier_id: (row.tier_id as string) ?? null,
      tier_name: (row.tier_name as string) ?? 'Unknown',
      badge_color: (row.badge_color as string) ?? null,
      customers: Number(row.customers ?? 0),
      threshold: Number(row.threshold ?? 0),
      display_order: Number(row.display_order ?? 0),
    })),
    top_customers: (topCustomers.rows as Record<string, unknown>[]).map((row) => ({
      customer_id: row.customer_id as string,
      name: (row.name as string) ?? null,
      email: (row.email as string) ?? null,
      phone: (row.phone as string) ?? null,
      tier_id: (row.tier_id as string) ?? null,
      tier_name: (row.tier_name as string) ?? null,
      badge_color: (row.badge_color as string) ?? null,
      balance: Number(row.balance ?? 0),
      lifetime_points: Number(row.lifetime_points ?? 0),
      last_visit: (row.last_order_at as string) ?? null,
    })),
  };
}

// ───────────────────────── worker ─────────────────────────

const TIMER_KEY = 'loyalty-expiry';
// Fresh identity per module evaluation; same identity across repeated
// runtime calls from this module instance (so startExpiryWorker() stays
// idempotent). A Fast-Refresh re-import yields a new object and the
// helper replaces the prior timer.
const MODULE_TOKEN = {};
const ONE_DAY_MS = 24 * 60 * 60 * 1000;
// Defer first tick so server boot isn't blocked.
const FIRST_TICK_DELAY_MS = 60_000;

export function startExpiryWorker(): void {
  const wasRegistered = isHmrTimerRegistered(TIMER_KEY);
  registerHmrTimer({
    key: TIMER_KEY,
    moduleToken: MODULE_TOKEN,
    initialDelayMs: FIRST_TICK_DELAY_MS,
    intervalMs: ONE_DAY_MS,
    tick: async () => {
      try { await expireDue(); }
      catch (e) { log.warn({ err: e }, 'loyalty expiry tick failed'); }
    },
  });
  if (!wasRegistered) log.info('expiry worker started (interval=24h)');
}

export function stopExpiryWorker(): void {
  clearHmrTimer(TIMER_KEY);
}