CREATE TABLE gift_cards (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code         VARCHAR(32) NOT NULL UNIQUE,
  initial_value_cents INTEGER NOT NULL,
  currency     VARCHAR(3) NOT NULL DEFAULT 'USD',
  issued_to    VARCHAR(255),
  expires_at   TIMESTAMPTZ,
  is_active    BOOLEAN NOT NULL DEFAULT true,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE gift_card_ledger (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  card_id    UUID NOT NULL REFERENCES gift_cards(id),
  amount_cents INTEGER NOT NULL,  -- positive = credit, negative = debit
  type       VARCHAR(16) NOT NULL CHECK (type IN ('issue', 'redeem', 'refund', 'void')),
  order_id   UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

async function getBalance(cardCode: string): Promise<number> {
  const card = await db.giftCards.findByCode(cardCode.toUpperCase());
  if (!card || !card.is_active) throw new Error('CARD_NOT_FOUND');
  if (card.expires_at && card.expires_at < new Date()) throw new Error('CARD_EXPIRED');

  const result = await db.raw(
    'SELECT COALESCE(SUM(amount_cents), 0) AS balance FROM gift_card_ledger WHERE card_id = ?',
    [card.id]
  );
  return Math.max(0, parseInt(result.rows[0].balance, 10));
}

async function redeemGiftCard(code: string, orderId: string, orderTotalCents: number) {
  return db.transaction(async tx => {
    // Lock the card row to prevent concurrent redemptions
    const card = await tx.raw(
      'SELECT * FROM gift_cards WHERE UPPER(code) = ? FOR UPDATE',
      [code.toUpperCase()]
    ).then(r => r.rows[0]);

    if (!card || !card.is_active) throw new Error('CARD_NOT_FOUND');

    const balance = await getBalance(code);
    const appliedCents = Math.min(balance, orderTotalCents);
    if (appliedCents === 0) throw new Error('ZERO_BALANCE');

    await tx.giftCardLedger.insert({
      card_id: card.id,
      amount_cents: -appliedCents,
      type: 'redeem',
      order_id: orderId,
    });

    return { appliedCents, remainingBalance: balance - appliedCents };
  });
}