-- billing: optional Stripe subscriptions plus a local prepaid credit ledger drawn down by metering.

-- One Stripe customer per account (user or org). Absent when Stripe is not configured.
CREATE TABLE billing_customers (
  account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  stripe_customer_id TEXT NOT NULL UNIQUE,
  subscription_id TEXT,
  subscription_status TEXT,
  price_id TEXT,
  current_period_end INTEGER,
  -- created time (ms) of the newest subscription event applied, so out-of-order deliveries never regress state.
  subscription_event_at INTEGER NOT NULL DEFAULT 0,
  cancel_at_period_end INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL
);

-- Webhook idempotency: an event id is processed at most once.
CREATE TABLE stripe_events (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  processed_at INTEGER NOT NULL
);

-- Prepaid credit per account and meter, in the meter's unit. Never period-scoped, so it survives rollover.
CREATE TABLE credit_balances (
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  balance REAL NOT NULL DEFAULT 0 CHECK (balance >= 0),
  updated_at INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (account_id, meter)
);

-- Every credit movement except draws: purchases, refund/dispute reversals and admin adjustments.
-- `ref` makes each movement idempotent (checkout session id, refund id, dispute id).
CREATE TABLE credit_ledger (
  ref TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('purchase', 'refund', 'dispute', 'adjust')),
  quantity REAL NOT NULL,
  -- For reversals: the amount owed back before clamping to the unspent balance, so later reversals of the
  -- same purchase never take more than their own share.
  requested REAL,
  -- For reversals: the purchase (its ref) being reversed.
  purchase_ref TEXT,
  pack TEXT,
  payment_intent TEXT,
  amount_cents INTEGER,
  nonce TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX credit_ledger_account ON credit_ledger (account_id, created_at DESC);

CREATE INDEX credit_ledger_payment ON credit_ledger (payment_intent);

-- Draws, one per usage event that went past the plan's included amount. Keyed by the event's idempotency key.
CREATE TABLE credit_draws (
  idempotency_key TEXT PRIMARY KEY,
  account_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  period TEXT NOT NULL,
  quantity REAL NOT NULL,
  nonce TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

-- How much of a period's usage was covered by credits, so overage and caps exclude it.
ALTER TABLE usage_periods ADD COLUMN credit_covered REAL NOT NULL DEFAULT 0;

-- Set by the attempt that inserted the event, so a credit draw runs only in the batch that recorded it.
ALTER TABLE usage_events ADD COLUMN nonce TEXT;

CREATE TRIGGER credit_balances_account_deleted AFTER DELETE ON accounts
BEGIN
  DELETE FROM credit_balances WHERE account_id = OLD.id;
END;