-- Migration: Free-tier cost routing (Task #27)
-- Adds generosity pool store, grant audit log, and free-tier tracking columns.

-- ── timmy_config ─────────────────────────────────────────────────────────────
-- Key/value store for Timmy's internal config (e.g. generosity_pool_sats).

CREATE TABLE IF NOT EXISTS timmy_config (
  key         TEXT PRIMARY KEY,
  value       TEXT NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── nostr_identities: free-tier absorption columns ───────────────────────────
-- Rolling daily budget: how many sats Timmy has subsidised for this identity
-- today; resets when absorbed_reset_at is older than 24 h.

ALTER TABLE nostr_identities
  ADD COLUMN IF NOT EXISTS sats_absorbed_today INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS absorbed_reset_at   TIMESTAMPTZ NOT NULL DEFAULT NOW();

-- ── jobs: free-tier tracking columns ─────────────────────────────────────────
-- free_tier=TRUE  → Timmy absorbed part or all of this job's cost from the pool.
-- absorbed_sats   → how many sats Timmy absorbed (NULL or 0 for fully-paid jobs).

ALTER TABLE jobs
  ADD COLUMN IF NOT EXISTS free_tier      BOOLEAN  NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS absorbed_sats  INTEGER;

-- ── sessions: nostr identity FK ──────────────────────────────────────────────
-- Added in task #26; included here for completeness in migration-driven deploys.

ALTER TABLE sessions
  ADD COLUMN IF NOT EXISTS nostr_pubkey TEXT
    REFERENCES nostr_identities(pubkey);

-- ── free_tier_grants ─────────────────────────────────────────────────────────
-- Audit log: one row each time Timmy absorbs cost on behalf of an identity.

CREATE TABLE IF NOT EXISTS free_tier_grants (
  id                  TEXT PRIMARY KEY,
  pubkey              TEXT NOT NULL REFERENCES nostr_identities(pubkey),
  request_hash        TEXT NOT NULL,
  sats_absorbed       INTEGER NOT NULL,
  pool_balance_after  INTEGER NOT NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_free_tier_grants_pubkey
  ON free_tier_grants(pubkey);