-- +goose Up

-- Credits are a BALANCE, not a subscription window (owner 05.08: "not subscriptions, buying tokens
-- like OpenRouter"). There is no reset, no period and no `resets_at`; the free tier is a `grant`
-- row and nothing else, which is why no code in this repository knows what a free tier is.
--
-- Money is whole micro-dollars everywhere. Never a float, never a decimal on the wire, and never a
-- sum in an API response, a screen or an INFO log (D39.84): the user sees a percentage of what is
-- left. These tables are private.

-- Append-only. A row is never updated or deleted: a mistake is corrected by another row, which is
-- what makes the sum reproducible after the fact.
create table credit_ledger (
    id       bigint generated always as identity primary key,
    user_id  text not null references users (id) on delete cascade,
    -- grant        — credit given (the whole of the free tier);
    -- hold         — reserved before a run is spawned, negative;
    -- hold_release — that reservation given back, positive;
    -- settlement   — what the attempt actually cost, negative;
    -- adjustment   — a correction, either sign, always with a note.
    kind     text not null check (kind in ('grant', 'hold', 'hold_release', 'settlement', 'adjustment')),
    -- Signed, so the balance is one SUM and cannot disagree with itself.
    amount_micro_usd bigint not null,
    -- Idempotency key, scoped to the ACCOUNT. Without user_id in it, one key spent on one account
    -- silently swallows the same key on another — the second account is told "granted" and credited
    -- nothing. An empty key is not a key: it would make every unkeyed write share one slot.
    source    text not null check (source <> ''),
    source_id text not null check (source_id <> ''),
    note      text not null default '',
    created_at timestamptz not null default now(),
    unique (user_id, source, source_id),
    -- A grant is never a debit and a settlement is never a credit: a sign error in the code that
    -- writes these fails at the write instead of silently topping up an account.
    constraint credit_ledger_sign check (
        (kind = 'grant' and amount_micro_usd > 0) or
        (kind = 'hold' and amount_micro_usd < 0) or
        (kind = 'hold_release' and amount_micro_usd > 0) or
        (kind = 'settlement' and amount_micro_usd <= 0) or
        (kind = 'adjustment' and amount_micro_usd <> 0)
    ),
    -- An adjustment without a reason is unauditable by construction.
    constraint credit_ledger_adjustment_has_note check (kind <> 'adjustment' or note <> '')
);

create index credit_ledger_user_idx on credit_ledger (user_id, id desc);

-- ⚠ Deleting an account deletes its ledger. "Append-only" above is within the life of an account:
-- there is no payment record to keep afterwards, and keeping a spending history of a deleted user
-- would be the worse default. If selling ever starts, this cascade is the first thing to revisit.

-- The balance cache. Written in the SAME transaction as the ledger row, never on its own; a test
-- asserts balance == sum(ledger) after every operation, because a cache that can drift from its
-- source is a second source of truth about money.
create table account_balances (
    user_id           text primary key references users (id) on delete cascade,
    balance_micro_usd bigint not null default 0,
    updated_at        timestamptz not null default now()
);

-- One reservation per engine attempt. The hold is what PROTECTS the balance together with the
-- per-book ceiling handed to the engine before it is spawned: the engine enforces the hard stop
-- itself, so an overspend is impossible even while the platform is blind. The spend event in the
-- stream is freshness only — enforcement must never be built on it, because the stream is
-- at-least-once and a crash truncates its tail.
create table reservations (
    engine_run_id     text primary key,
    user_id           text not null references users (id) on delete cascade,
    book_id           text not null,
    amount_micro_usd  bigint not null check (amount_micro_usd > 0),
    -- What the engine was told its ceiling was. Kept because "why did this run stop" is answered
    -- from here, not from the engine's config file, which the next run rewrites.
    ceiling_micro_usd bigint not null check (ceiling_micro_usd > 0),
    state             text not null check (state in ('open', 'settled', 'released')),
    opened_at         timestamptz not null default now(),
    closed_at         timestamptz,
    -- A closed reservation has a closing time and an open one does not. Without this the state and
    -- the timestamps drift apart and neither can be trusted.
    constraint reservations_closed_has_time check ((state = 'open') = (closed_at is null)),
    -- The payer must own the book. A plain reference to books(id) proves only that the book
    -- exists, which is a different question: charging one account for another's run would pass it.
    -- RESTRICT, not cascade: cascading here would remove the reservation while its `hold` row stays
    -- in the ledger — money debited with nothing left to release it, and the freed engine_run_id
    -- then lets the next hold find its ledger key already spent and reserve nothing while
    -- reporting that it did.
    foreign key (book_id, user_id) references books (id, owner_id) on delete restrict
);

create index reservations_user_open_idx on reservations (user_id) where state = 'open';
-- Both cascading parents get an index, same rule as 00002 (PD-11): without them every account or
-- book deletion scans this table.
create index reservations_user_idx on reservations (user_id);
create index reservations_book_idx on reservations (book_id);

-- +goose Down
drop table reservations;
drop table account_balances;
drop table credit_ledger;