/**
 * Gift Cards service.
 *
 * Codes are stored as SHA-256 hashes (with the platform's pepper salt when
 * available, otherwise a fixed salt) — the cleartext code is only ever
 * visible at issuance time (returned once to the issuer + emailed/printed
 * for the recipient). On lookup we recompute the hash and compare.
 *
 * Balance changes are always recorded in `gift_card_ledger` in the same
 * transaction as the balance update.
 */
import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { randomBytes, createHash } from 'crypto';

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

const CODE_ALPHABET = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'; // no I/O/0/1 — voucher-readable
const MIN_CODE_LENGTH = 8;
const MAX_CODE_LENGTH = 20;

function codeSalt(): string {
  const explicit = process.env.GIFT_CARD_SALT;
  if (explicit && explicit.length >= 16) return explicit;
  const session = process.env.SESSION_SECRET;
  if (session && session.length >= 16) return session;
  // No safe fallback: gift card codes are stored as salted SHA-256 hashes,
  // and a weak/well-known salt would let an attacker with DB read access
  // brute-force valid codes. Refuse to operate rather than accept this risk.
  throw new Error(
    'gift_cards: missing GIFT_CARD_SALT (or SESSION_SECRET) of length >= 16. ' +
    'Set one of these environment variables to a strong random secret to enable gift card hashing.'
  );
}

export function hashCode(code: string): string {
  return createHash('sha256').update(`${codeSalt()}::${code.trim().toUpperCase()}`).digest('hex');
}

export function maskCode(prefix: string, last4: string, length: number): string {
  const bodyLen = Math.max(0, length - prefix.length - 4);
  return `${prefix}${'•'.repeat(bodyLen)}${last4}`;
}

export function generateCode(prefix: string, length: number): string {
  const safePrefix = (prefix || '').toUpperCase().replace(/[^A-Z0-9]/g, '').slice(0, 6);
  const bodyLen = Math.max(MIN_CODE_LENGTH - safePrefix.length, Math.min(MAX_CODE_LENGTH, length) - safePrefix.length);
  const buf = randomBytes(bodyLen);
  let body = '';
  for (let i = 0; i < bodyLen; i++) {
    body += CODE_ALPHABET[buf[i] % CODE_ALPHABET.length];
  }
  return `${safePrefix}${body}`;
}

// ---------- Settings ----------

export interface GiftCardSettings {
  restaurant_id: string;
  enabled: boolean;
  denominations: number[];
  custom_amount_enabled: boolean;
  custom_min: number;
  custom_max: number;
  currency: string;
  default_expiry_months: number | null;
  code_length: number;
  code_prefix: string;
  brand_logo_url: string | null;
  brand_primary_color: string;
  message_template: string;
  email_from_line: string | null;
  redemption_rule: 'subtotal' | 'total';
  stack_with_coupons: boolean;
  stack_with_loyalty: boolean;
  show_full_code_on_voucher: boolean;
}

function rowToSettings(row: Record<string, unknown>, fallbackCurrency: string): GiftCardSettings {
  const denomRaw = row.denominations;
  const denominations = Array.isArray(denomRaw)
    ? (denomRaw as unknown[]).map((d) => Number(d)).filter((n) => Number.isFinite(n) && n > 0)
    : [10, 25, 50, 100];
  return {
    restaurant_id: row.restaurant_id as string,
    enabled: row.enabled === true,
    denominations,
    custom_amount_enabled: row.custom_amount_enabled === true,
    custom_min: Number(row.custom_min ?? 5),
    custom_max: Number(row.custom_max ?? 500),
    currency: (row.currency as string | null) || fallbackCurrency,
    default_expiry_months: row.default_expiry_months == null ? null : Number(row.default_expiry_months),
    code_length: Number(row.code_length ?? 12),
    code_prefix: (row.code_prefix as string) || 'GC',
    brand_logo_url: (row.brand_logo_url as string | null) ?? null,
    brand_primary_color: (row.brand_primary_color as string) || '#f97316',
    message_template: (row.message_template as string) || 'You have received a gift card!',
    email_from_line: (row.email_from_line as string | null) ?? null,
    redemption_rule: (row.redemption_rule as 'subtotal' | 'total') || 'subtotal',
    stack_with_coupons: row.stack_with_coupons !== false,
    stack_with_loyalty: row.stack_with_loyalty !== false,
    show_full_code_on_voucher: row.show_full_code_on_voucher !== false,
  };
}

async function fetchRestaurantCurrency(restaurantId: string): Promise<string> {
  const { rows } = await db.execute(sql`SELECT currency FROM restaurants WHERE id = ${restaurantId}`);
  return ((rows[0] as { currency?: string } | undefined)?.currency || 'USD');
}

export async function getSettings(restaurantId: string): Promise<GiftCardSettings> {
  await initDatabase();
  const fallbackCurrency = await fetchRestaurantCurrency(restaurantId);
  const { rows } = await db.execute(sql`SELECT * FROM gift_card_settings WHERE restaurant_id = ${restaurantId}`);
  if (rows[0]) return rowToSettings(rows[0] as Record<string, unknown>, fallbackCurrency);
  // Auto-provision defaults
  await db.execute(sql`
    INSERT INTO gift_card_settings (restaurant_id, currency)
    VALUES (${restaurantId}, ${fallbackCurrency})
    ON CONFLICT (restaurant_id) DO NOTHING
  `);
  const { rows: rows2 } = await db.execute(sql`SELECT * FROM gift_card_settings WHERE restaurant_id = ${restaurantId}`);
  return rowToSettings(rows2[0] as Record<string, unknown>, fallbackCurrency);
}

export interface UpdateSettingsInput {
  enabled?: boolean;
  denominations?: number[];
  custom_amount_enabled?: boolean;
  custom_min?: number;
  custom_max?: number;
  default_expiry_months?: number | null;
  code_length?: number;
  code_prefix?: string;
  brand_logo_url?: string | null;
  brand_primary_color?: string;
  message_template?: string;
  email_from_line?: string | null;
  redemption_rule?: 'subtotal' | 'total';
  stack_with_coupons?: boolean;
  stack_with_loyalty?: boolean;
  show_full_code_on_voucher?: boolean;
}

export async function updateSettings(restaurantId: string, input: UpdateSettingsInput): Promise<GiftCardSettings> {
  await initDatabase();
  // Ensure row exists
  await getSettings(restaurantId);

  // Validate
  if (input.denominations !== undefined) {
    if (!Array.isArray(input.denominations) || input.denominations.length === 0) {
      throw new ValidationError('At least one denomination is required');
    }
    for (const d of input.denominations) {
      if (!(typeof d === 'number') || !Number.isFinite(d) || d <= 0) {
        throw new ValidationError('Denominations must be positive numbers');
      }
    }
  }
  if (input.code_length !== undefined) {
    if (input.code_length < MIN_CODE_LENGTH || input.code_length > MAX_CODE_LENGTH) {
      throw new ValidationError(`Code length must be between ${MIN_CODE_LENGTH} and ${MAX_CODE_LENGTH}`);
    }
  }
  if (input.code_prefix !== undefined) {
    const cleaned = input.code_prefix.toUpperCase().replace(/[^A-Z0-9]/g, '');
    if (cleaned.length > 6) throw new ValidationError('Code prefix must be 0–6 alphanumeric characters');
    input.code_prefix = cleaned || 'GC';
  }
  if (input.custom_min !== undefined && input.custom_max !== undefined && input.custom_min > input.custom_max) {
    throw new ValidationError('custom_min must be ≤ custom_max');
  }
  if (input.redemption_rule !== undefined && !['subtotal', 'total'].includes(input.redemption_rule)) {
    throw new ValidationError('redemption_rule must be "subtotal" or "total"');
  }

  await db.execute(sql`
    UPDATE gift_card_settings SET
      enabled = COALESCE(${input.enabled ?? null}, enabled),
      denominations = COALESCE(${input.denominations === undefined ? null : JSON.stringify(input.denominations)}::jsonb, denominations),
      custom_amount_enabled = COALESCE(${input.custom_amount_enabled ?? null}, custom_amount_enabled),
      custom_min = COALESCE(${input.custom_min ?? null}, custom_min),
      custom_max = COALESCE(${input.custom_max ?? null}, custom_max),
      default_expiry_months = ${input.default_expiry_months === undefined ? sql`default_expiry_months` : sql`${input.default_expiry_months}`},
      code_length = COALESCE(${input.code_length ?? null}, code_length),
      code_prefix = COALESCE(${input.code_prefix ?? null}, code_prefix),
      brand_logo_url = ${input.brand_logo_url === undefined ? sql`brand_logo_url` : sql`${input.brand_logo_url}`},
      brand_primary_color = COALESCE(${input.brand_primary_color ?? null}, brand_primary_color),
      message_template = COALESCE(${input.message_template ?? null}, message_template),
      email_from_line = ${input.email_from_line === undefined ? sql`email_from_line` : sql`${input.email_from_line}`},
      redemption_rule = COALESCE(${input.redemption_rule ?? null}, redemption_rule),
      stack_with_coupons = COALESCE(${input.stack_with_coupons ?? null}, stack_with_coupons),
      stack_with_loyalty = COALESCE(${input.stack_with_loyalty ?? null}, stack_with_loyalty),
      show_full_code_on_voucher = COALESCE(${input.show_full_code_on_voucher ?? null}, show_full_code_on_voucher),
      updated_at = NOW()
    WHERE restaurant_id = ${restaurantId}
  `);

  return getSettings(restaurantId);
}

// ---------- Issuance ----------

export interface IssueCardInput {
  restaurantId: string;
  amount: number;
  recipientName: string;
  recipientEmail?: string | null;
  senderName?: string | null;
  senderEmail?: string | null;
  personalMessage?: string | null;
  expiresAt?: string | null; // ISO date
  branchId?: string | null;
  byUserId?: string | null;
  /** When true, queue a branded delivery email to the recipient. */
  sendEmail?: boolean;
}

export interface IssuedCard {
  id: string;
  code: string; // CLEARTEXT — only returned once at issuance
  masked_code: string;
  original_amount: number;
  balance: number;
  currency: string;
  status: string;
  recipient_name: string;
  recipient_email: string | null;
  expires_at: string | null;
  created_at: string;
}

export async function issueCard(input: IssueCardInput): Promise<IssuedCard> {
  await initDatabase();
  const settings = await getSettings(input.restaurantId);

  if (!settings.enabled) throw new ValidationError('Gift cards are disabled for this restaurant');
  const amount = Math.round(Number(input.amount) * 100) / 100;
  if (!Number.isFinite(amount) || amount <= 0) throw new ValidationError('Amount must be greater than zero');
  const isPreset = settings.denominations.includes(amount);
  if (!isPreset) {
    if (!settings.custom_amount_enabled) throw new ValidationError(`Amount must be one of ${settings.denominations.join(', ')}`);
    if (amount < settings.custom_min || amount > settings.custom_max) {
      throw new ValidationError(`Custom amount must be between ${settings.custom_min} and ${settings.custom_max}`);
    }
  }
  if (!input.recipientName?.trim()) throw new ValidationError('Recipient name is required');
  if (input.sendEmail && !input.recipientEmail) throw new ValidationError('Recipient email is required to send the email');

  // Compute expiry
  let expiresAt: string | null = null;
  if (input.expiresAt) {
    expiresAt = input.expiresAt;
  } else if (settings.default_expiry_months && settings.default_expiry_months > 0) {
    const d = new Date();
    d.setMonth(d.getMonth() + settings.default_expiry_months);
    expiresAt = d.toISOString();
  }

  // Generate unique code (retry on rare hash collision)
  let plaintext = '';
  let codeHash = '';
  for (let attempt = 0; attempt < 5; attempt++) {
    plaintext = generateCode(settings.code_prefix, settings.code_length);
    codeHash = hashCode(plaintext);
    const { rows } = await db.execute(sql`SELECT 1 FROM gift_cards WHERE code_hash = ${codeHash} LIMIT 1`);
    if (rows.length === 0) break;
    if (attempt === 4) throw new Error('Could not generate a unique code after multiple attempts');
  }
  const last4 = plaintext.slice(-4);

  const inserted = await db.transaction(async (tx) => {
    const { rows: [row] } = await tx.execute(sql`
      INSERT INTO gift_cards (
        restaurant_id, code_hash, code_last4, code_prefix,
        original_amount, balance, currency,
        recipient_name, recipient_email, sender_name, sender_email, personal_message,
        status, issued_by_user_id, issued_branch_id, expires_at
      ) VALUES (
        ${input.restaurantId}, ${codeHash}, ${last4}, ${settings.code_prefix},
        ${amount}, ${amount}, ${settings.currency},
        ${input.recipientName.trim()}, ${input.recipientEmail ?? null},
        ${input.senderName ?? null}, ${input.senderEmail ?? null}, ${input.personalMessage ?? null},
        'active', ${input.byUserId ?? null}, ${input.branchId ?? null}, ${expiresAt}
      )
      RETURNING id, original_amount, balance, currency, status, recipient_name, recipient_email, expires_at, created_at
    `);
    const cardId = (row as { id: string }).id;
    await tx.execute(sql`
      INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, branch_id, entry_type, amount, balance_after, by_user_id, note)
      VALUES (${cardId}, ${input.restaurantId}, ${input.branchId ?? null}, 'issuance', ${amount}, ${amount}, ${input.byUserId ?? null}, 'Card issued')
    `);
    return row as Record<string, unknown>;
  });

  const issued: IssuedCard = {
    id: inserted.id as string,
    code: plaintext,
    masked_code: maskCode(settings.code_prefix, last4, settings.code_length),
    original_amount: Number(inserted.original_amount),
    balance: Number(inserted.balance),
    currency: inserted.currency as string,
    status: inserted.status as string,
    recipient_name: inserted.recipient_name as string,
    recipient_email: (inserted.recipient_email as string | null) ?? null,
    expires_at: (inserted.expires_at as string | null) ?? null,
    created_at: inserted.created_at as string,
  };

  if (input.sendEmail && input.recipientEmail) {
    try {
      const { enqueueGiftCardIssuedEmail } = await import('./gift-cards-email.service');
      await enqueueGiftCardIssuedEmail({
        restaurantId: input.restaurantId,
        branchId: input.branchId ?? null,
        cardId: issued.id,
        plaintextCode: plaintext,
      });
    } catch (err) {
      log.warn({ err }, 'gift-cards email enqueue failed');
    }
  }

  return issued;
}

// ---------- Listing / Detail ----------

export interface ListFilters {
  status?: string;
  search?: string;
  branchId?: string;
  /** ISO date (YYYY-MM-DD) or full ISO timestamp — inclusive lower bound on created_at */
  dateFrom?: string;
  /** ISO date (YYYY-MM-DD) or full ISO timestamp — exclusive upper bound on created_at */
  dateTo?: string;
  page?: number;
  limit?: number;
}

export async function listCards(restaurantId: string, filters: ListFilters = {}) {
  await initDatabase();
  const { status, search, branchId, dateFrom, dateTo } = filters;
  const page = Math.max(1, filters.page ?? 1);
  const limit = Math.min(100, Math.max(1, filters.limit ?? 20));
  const offset = (page - 1) * limit;
  const conds: ReturnType<typeof sql>[] = [sql`restaurant_id = ${restaurantId}`];
  if (status) conds.push(sql`status = ${status}`);
  if (branchId) conds.push(sql`issued_branch_id = ${branchId}`);
  if (dateFrom) conds.push(sql`created_at >= ${dateFrom}::timestamptz`);
  if (dateTo) {
    // If a bare YYYY-MM-DD is supplied, treat it as inclusive end-of-day
    const upper = /^\d{4}-\d{2}-\d{2}$/.test(dateTo) ? `${dateTo}T23:59:59.999Z` : dateTo;
    conds.push(sql`created_at <= ${upper}::timestamptz`);
  }
  if (search) {
    const q = `%${search}%`;
    conds.push(sql`(recipient_name ILIKE ${q} OR recipient_email ILIKE ${q} OR sender_name ILIKE ${q} OR code_last4 ILIKE ${q})`);
  }
  const where = sql.join(conds, sql` AND `);

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

  const { rows } = await db.execute(sql`
    SELECT id, code_prefix, code_last4, original_amount, balance, currency,
           recipient_name, recipient_email, sender_name, status,
           expires_at, created_at, issued_branch_id
    FROM gift_cards
    WHERE ${where}
    ORDER BY created_at DESC
    LIMIT ${limit} OFFSET ${offset}
  `);

  const items = (rows as Record<string, unknown>[]).map((r) => ({
    id: r.id as string,
    masked_code: maskCode(r.code_prefix as string, r.code_last4 as string, ((r.code_prefix as string) + (r.code_last4 as string)).length + 8),
    original_amount: Number(r.original_amount),
    balance: Number(r.balance),
    currency: r.currency as string,
    recipient_name: r.recipient_name as string,
    recipient_email: r.recipient_email as string | null,
    sender_name: r.sender_name as string | null,
    status: r.status as string,
    expires_at: r.expires_at as string | null,
    created_at: r.created_at as string,
    issued_branch_id: r.issued_branch_id as string | null,
  }));
  return { items, total, page, limit, pages: Math.max(1, Math.ceil(total / limit)) };
}

export async function getCard(restaurantId: string, cardId: string) {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT * FROM gift_cards WHERE id = ${cardId}::uuid AND restaurant_id = ${restaurantId}
  `);
  const card = rows[0] as Record<string, unknown> | undefined;
  if (!card) throw new NotFoundError('Gift card');
  return cardToObject(card);
}

export async function revealCardCode(restaurantId: string, cardId: string): Promise<string | null> {
  // Cleartext is never persisted; we cannot reveal it after issuance. Returning
  // null lets the UI show only the masked form (per security best practice).
  await getCard(restaurantId, cardId); // existence check
  return null;
}

export async function getCardLedger(restaurantId: string, cardId: string) {
  await initDatabase();
  // Verify card belongs to restaurant
  const { rows: chk } = await db.execute(sql`SELECT 1 FROM gift_cards WHERE id = ${cardId}::uuid AND restaurant_id = ${restaurantId}`);
  if (chk.length === 0) throw new NotFoundError('Gift card');
  const { rows } = await db.execute(sql`
    SELECT l.id, l.entry_type, l.amount, l.balance_after, l.note,
           l.branch_id, b.name AS branch_name,
           l.order_id, o.order_number,
           l.created_at, l.by_user_id
    FROM gift_card_ledger l
    LEFT JOIN branches b ON b.id = l.branch_id
    LEFT JOIN orders o ON o.id = l.order_id
    WHERE l.gift_card_id = ${cardId}::uuid
    ORDER BY l.created_at ASC
  `);
  return (rows as Record<string, unknown>[]).map((r) => ({
    id: r.id as string,
    entry_type: r.entry_type as string,
    amount: Number(r.amount),
    balance_after: Number(r.balance_after),
    note: r.note as string | null,
    branch_id: r.branch_id as string | null,
    branch_name: r.branch_name as string | null,
    order_id: r.order_id as string | null,
    order_number: r.order_number == null ? null : Number(r.order_number),
    created_at: r.created_at as string,
    by_user_id: r.by_user_id as string | null,
  }));
}

function cardToObject(card: Record<string, unknown>) {
  const prefix = card.code_prefix as string;
  const last4 = card.code_last4 as string;
  return {
    id: card.id as string,
    masked_code: maskCode(prefix, last4, prefix.length + last4.length + 8),
    code_prefix: prefix,
    code_last4: last4,
    original_amount: Number(card.original_amount),
    balance: Number(card.balance),
    currency: card.currency as string,
    recipient_name: card.recipient_name as string,
    recipient_email: card.recipient_email as string | null,
    sender_name: card.sender_name as string | null,
    sender_email: card.sender_email as string | null,
    personal_message: card.personal_message as string | null,
    status: card.status as string,
    issued_branch_id: card.issued_branch_id as string | null,
    expires_at: card.expires_at as string | null,
    voided_at: card.voided_at as string | null,
    void_reason: card.void_reason as string | null,
    last_used_at: card.last_used_at as string | null,
    created_at: card.created_at as string,
  };
}

// ---------- Redemption ----------

export interface PreviewRedeemInput {
  restaurantId: string;
  code: string;
  /** The amount that the gift card *could* offset, computed by the caller
   *  according to the configured redemption rule (subtotal or total). */
  amountDue: number;
}

export interface PreviewRedeemResult {
  card_id: string;
  applied: number;
  remaining_balance: number;
  currency: string;
}

/**
 * Tx-scoped variant: locks the row with FOR UPDATE so the calling
 * transaction owns the balance update. Throws ValidationError on any
 * issue. Returns the locked card row.
 */
export async function lockCardByCodeInTx(
  tx: { execute: typeof db.execute },
  restaurantId: string,
  code: string,
): Promise<Record<string, unknown>> {
  const trimmed = (code || '').trim().toUpperCase();
  if (!trimmed) throw new ValidationError('Gift card code is required');
  const settings = await getSettings(restaurantId);
  if (!settings.enabled) throw new ValidationError('Gift cards are currently disabled by the restaurant');
  const codeHash = hashCode(trimmed);
  const { rows } = await tx.execute(sql`
    SELECT * FROM gift_cards
    WHERE code_hash = ${codeHash} AND restaurant_id = ${restaurantId}
    FOR UPDATE
  `);
  const card = rows[0] as Record<string, unknown> | undefined;
  if (!card) throw new ValidationError('Invalid gift card code');
  if (card.status === 'void') throw new ValidationError('This gift card has been voided');
  if (card.status === 'expired') throw new ValidationError('This gift card has expired');
  if (card.expires_at && new Date(card.expires_at as string) < new Date()) {
    await tx.execute(sql`UPDATE gift_cards SET status='expired', updated_at=NOW() WHERE id = ${card.id as string}::uuid`);
    throw new ValidationError('This gift card has expired');
  }
  if (Number(card.balance) <= 0) throw new ValidationError('This gift card has no remaining balance');
  if ((card.currency as string) !== settings.currency) {
    throw new ValidationError(
      `This gift card is in ${card.currency} but this restaurant accepts ${settings.currency}`
    );
  }
  return card;
}

/**
 * Apply a redemption inside a caller-owned transaction. The card MUST have
 * been locked by lockCardByCodeInTx beforehand; we only use `card.id` /
 * `card.balance` here.
 */
export async function applyRedemptionInTx(
  tx: { execute: typeof db.execute },
  card: Record<string, unknown>,
  params: {
    restaurantId: string;
    branchId: string | null;
    orderId: string;
    requestedAmount: number;
    byUserId?: string | null;
    note?: string;
  },
): Promise<{ applied: number; remaining: number }> {
  const balance = Number(card.balance);
  const requested = Math.max(0, Math.round(Number(params.requestedAmount) * 100) / 100);
  if (requested <= 0) throw new ValidationError('Redemption amount must be greater than zero');
  const applied = Math.min(balance, requested);
  const remaining = Math.round((balance - applied) * 100) / 100;
  const newStatus = remaining <= 0 ? 'redeemed' : 'active';
  await tx.execute(sql`
    UPDATE gift_cards
    SET balance = ${remaining}, status = ${newStatus}, last_used_at = NOW(), updated_at = NOW()
    WHERE id = ${card.id as string}::uuid
  `);
  await tx.execute(sql`
    INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, branch_id, order_id, entry_type, amount, balance_after, by_user_id, note)
    VALUES (${card.id as string}::uuid, ${params.restaurantId}, ${params.branchId ?? null}, ${params.orderId}::uuid, 'redemption', ${applied}, ${remaining}, ${params.byUserId ?? null}, ${params.note ?? 'Applied to order'})
  `);
  return { applied, remaining };
}

async function findActiveCardByCode(restaurantId: string, code: string): Promise<Record<string, unknown>> {
  const trimmed = (code || '').trim().toUpperCase();
  if (!trimmed) throw new ValidationError('Gift card code is required');
  const settings = await getSettings(restaurantId);
  if (!settings.enabled) throw new ValidationError('Gift cards are currently disabled by the restaurant');
  const codeHash = hashCode(trimmed);
  const { rows } = await db.execute(sql`
    SELECT * FROM gift_cards WHERE code_hash = ${codeHash} AND restaurant_id = ${restaurantId} LIMIT 1
  `);
  const card = rows[0] as Record<string, unknown> | undefined;
  if (!card) throw new ValidationError('Invalid gift card code');
  if (card.status === 'void') throw new ValidationError('This gift card has been voided');
  if (card.status === 'expired') throw new ValidationError('This gift card has expired');
  if (card.status === 'redeemed' || Number(card.balance) <= 0) throw new ValidationError('This gift card has no remaining balance');
  if (card.expires_at && new Date(card.expires_at as string) < new Date()) {
    await db.execute(sql`UPDATE gift_cards SET status='expired', updated_at=NOW() WHERE id = ${card.id as string}::uuid`);
    throw new ValidationError('This gift card has expired');
  }
  if ((card.currency as string) !== settings.currency) {
    throw new ValidationError(
      `This gift card is in ${card.currency} but this restaurant accepts ${settings.currency}`
    );
  }
  return card;
}

export async function previewRedeem(input: PreviewRedeemInput): Promise<PreviewRedeemResult> {
  await initDatabase();
  const card = await findActiveCardByCode(input.restaurantId, input.code);
  const amountDue = Math.max(0, Math.round(Number(input.amountDue) * 100) / 100);
  const balance = Number(card.balance);
  const applied = Math.min(balance, amountDue);
  return {
    card_id: card.id as string,
    applied: Math.round(applied * 100) / 100,
    remaining_balance: Math.round((balance - applied) * 100) / 100,
    currency: card.currency as string,
  };
}

export interface CommitRedeemInput {
  restaurantId: string;
  code?: string;
  cardId?: string;
  orderId: string;
  branchId?: string | null;
  amount: number;
  byUserId?: string | null;
}

export interface CommitRedeemResult {
  card_id: string;
  applied: number;
  remaining_balance: number;
}

export async function commitRedeem(input: CommitRedeemInput): Promise<CommitRedeemResult> {
  await initDatabase();
  if (!input.code && !input.cardId) throw new ValidationError('Gift card code or id is required');

  return db.transaction(async (tx) => {
    let card: Record<string, unknown>;
    if (input.cardId) {
      const { rows } = await tx.execute(sql`
        SELECT * FROM gift_cards
        WHERE id = ${input.cardId}::uuid AND restaurant_id = ${input.restaurantId}
        FOR UPDATE
      `);
      const found = rows[0] as Record<string, unknown> | undefined;
      if (!found) throw new ValidationError('Invalid gift card');
      card = found;
    } else {
      const codeHash = hashCode(input.code!);
      const { rows } = await tx.execute(sql`
        SELECT * FROM gift_cards
        WHERE code_hash = ${codeHash} AND restaurant_id = ${input.restaurantId}
        FOR UPDATE
      `);
      const found = rows[0] as Record<string, unknown> | undefined;
      if (!found) throw new ValidationError('Invalid gift card code');
      card = found;
    }

    if (card.status === 'void') throw new ValidationError('This gift card has been voided');
    if (card.status === 'expired') throw new ValidationError('This gift card has expired');
    if (card.expires_at && new Date(card.expires_at as string) < new Date()) {
      await tx.execute(sql`UPDATE gift_cards SET status='expired', updated_at=NOW() WHERE id = ${card.id as string}::uuid`);
      throw new ValidationError('This gift card has expired');
    }
    const balance = Number(card.balance);
    if (balance <= 0) throw new ValidationError('This gift card has no remaining balance');

    const requested = Math.max(0, Math.round(Number(input.amount) * 100) / 100);
    if (requested <= 0) throw new ValidationError('Redemption amount must be greater than zero');
    const applied = Math.min(balance, requested);
    const remaining = Math.round((balance - applied) * 100) / 100;
    const newStatus = remaining <= 0 ? 'redeemed' : 'active';

    await tx.execute(sql`
      UPDATE gift_cards
      SET balance = ${remaining}, status = ${newStatus}, last_used_at = NOW(), updated_at = NOW()
      WHERE id = ${card.id as string}::uuid
    `);
    await tx.execute(sql`
      INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, branch_id, order_id, entry_type, amount, balance_after, by_user_id, note)
      VALUES (${card.id as string}::uuid, ${input.restaurantId}, ${input.branchId ?? null}, ${input.orderId}::uuid, 'redemption', ${applied}, ${remaining}, ${input.byUserId ?? null}, 'Applied to order')
    `);

    return {
      card_id: card.id as string,
      applied,
      remaining_balance: remaining,
    };
  });
}

// ---------- Void / Refund ----------

export async function voidCard(restaurantId: string, cardId: string, reason?: string, byUserId?: string | null) {
  await initDatabase();
  return db.transaction(async (tx) => {
    const { rows } = await tx.execute(sql`
      SELECT * FROM gift_cards
      WHERE id = ${cardId}::uuid AND restaurant_id = ${restaurantId}
      FOR UPDATE
    `);
    const card = rows[0] as Record<string, unknown> | undefined;
    if (!card) throw new NotFoundError('Gift card');
    if (card.status === 'void') throw new ValidationError('Card is already voided');
    const balance = Number(card.balance);
    await tx.execute(sql`
      UPDATE gift_cards
      SET status = 'void', balance = 0, voided_at = NOW(), void_reason = ${reason ?? null}, updated_at = NOW()
      WHERE id = ${cardId}::uuid
    `);
    if (balance > 0) {
      await tx.execute(sql`
        INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, entry_type, amount, balance_after, by_user_id, note)
        VALUES (${cardId}::uuid, ${restaurantId}, 'void', ${-balance}, 0, ${byUserId ?? null}, ${reason ?? null})
      `);
    } else {
      await tx.execute(sql`
        INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, entry_type, amount, balance_after, by_user_id, note)
        VALUES (${cardId}::uuid, ${restaurantId}, 'void', 0, 0, ${byUserId ?? null}, ${reason ?? null})
      `);
    }
    return { id: cardId, status: 'void', voided_amount: balance };
  });
}

export async function refundCard(restaurantId: string, cardId: string, amount: number, byUserId?: string | null, note?: string) {
  await initDatabase();
  const refund = Math.round(Number(amount) * 100) / 100;
  if (!Number.isFinite(refund) || refund <= 0) throw new ValidationError('Refund amount must be greater than zero');
  return db.transaction(async (tx) => {
    const { rows } = await tx.execute(sql`
      SELECT * FROM gift_cards
      WHERE id = ${cardId}::uuid AND restaurant_id = ${restaurantId}
      FOR UPDATE
    `);
    const card = rows[0] as Record<string, unknown> | undefined;
    if (!card) throw new NotFoundError('Gift card');
    if (card.status === 'void') throw new ValidationError('Cannot refund a voided card');
    const original = Number(card.original_amount);
    const balance = Number(card.balance);
    if (balance + refund > original) {
      throw new ValidationError(`Refund would exceed the card's original value (${original})`);
    }
    const newBalance = Math.round((balance + refund) * 100) / 100;
    const newStatus = newBalance > 0 ? 'active' : (card.status as string);
    await tx.execute(sql`
      UPDATE gift_cards SET balance = ${newBalance}, status = ${newStatus}, updated_at = NOW()
      WHERE id = ${cardId}::uuid
    `);
    await tx.execute(sql`
      INSERT INTO gift_card_ledger (gift_card_id, restaurant_id, entry_type, amount, balance_after, by_user_id, note)
      VALUES (${cardId}::uuid, ${restaurantId}, 'refund', ${refund}, ${newBalance}, ${byUserId ?? null}, ${note ?? null})
    `);
    return { id: cardId, balance: newBalance, status: newStatus };
  });
}

// ---------- Stats ----------

export async function getStats(restaurantId: string, branchId?: string | null) {
  await initDatabase();
  // Branch scoping mirrors listCards: when the caller is pinned (or has an
  // active topbar branch) the stats reflect only that branch's gift cards;
  // otherwise the whole restaurant aggregates as before.
  const branchClause = branchId
    ? sql`AND issued_branch_id = ${branchId}`
    : sql``;
  const { rows } = await db.execute(sql`
    SELECT
      COUNT(*)::int AS total,
      COUNT(*) FILTER (WHERE status = 'active')::int AS active,
      COUNT(*) FILTER (WHERE status = 'redeemed')::int AS redeemed,
      COUNT(*) FILTER (WHERE status = 'expired')::int AS expired,
      COUNT(*) FILTER (WHERE status = 'void')::int AS void,
      COALESCE(SUM(original_amount), 0)::numeric AS total_issued,
      COALESCE(SUM(balance) FILTER (WHERE status = 'active'), 0)::numeric AS outstanding
    FROM gift_cards WHERE restaurant_id = ${restaurantId} ${branchClause}
  `);
  const r = rows[0] as Record<string, unknown> | undefined;
  return {
    total: Number(r?.total ?? 0),
    active: Number(r?.active ?? 0),
    redeemed: Number(r?.redeemed ?? 0),
    expired: Number(r?.expired ?? 0),
    void: Number(r?.void ?? 0),
    total_issued: Number(r?.total_issued ?? 0),
    outstanding: Number(r?.outstanding ?? 0),
  };
}