-- Money. Every amount here is whole micro-USD and stays integral through the generated layer.
--
-- ⚠ The ::bigint casts on the AGGREGATES are load-bearing and were arrived at by measurement, not
-- taste. `sum(bigint)` is `numeric` in Postgres, and sqlc types a coalesced aggregate WITHOUT a cast
-- as `interface{}` — money with no type at all, which is the worst of the three outcomes. With the
-- cast it is `int64`: integral, and one explicit conversion away from money.MicroUSD in the domain
-- layer. What sqlc will NOT do either way is apply the `*.*_micro_usd` override to a computed
-- column — that override reaches real table columns only (measured; an alias-targeted override does
-- not reach them either). So the aggregates below are the one place in this file where the money
-- TYPE is restored by hand, and credits.go says so at each site.

-- name: Balance :one
-- What the account may still spend, read from the cache that every ledger write updates in its own
-- transaction.
--
-- ⚠ The coalesce is not a simplification, it is a CORRECTION forced by measurement. The column is
-- `not null` in the table, so sqlc types it non-nullable and does not notice that the LEFT JOIN can
-- still produce NULL — an account that exists with no ledger rows. Left as `b.balance_micro_usd`,
-- the generated Scan would ERROR on exactly that account instead of answering "no credit". The two
-- facts stay distinguishable without the pointer: a missing ACCOUNT is still no row at all, because
-- the outer `from users` decides that, and a missing BALANCE row is the zero this coalesce supplies.
select coalesce(b.balance_micro_usd, 0)::bigint as balance_micro_usd
  from users u left join account_balances b on b.user_id = u.id
 where u.id = sqlc.arg(user_id);

-- name: ReadAccount :one
-- The three are read in ONE snapshot on purpose: reading them separately reports drift that a
-- concurrent grant caused between the queries. LedgerSum exists to be COMPARED with Balance.
select coalesce((select ab.balance_micro_usd from account_balances ab
                  where ab.user_id = sqlc.arg(user_id)), 0)::bigint as balance_micro_usd,
       coalesce((select sum(cl.amount_micro_usd) from credit_ledger cl
                  where cl.user_id = sqlc.arg(user_id)), 0)::bigint as ledger_sum_micro_usd,
       coalesce((select sum(r.amount_micro_usd) from reservations r
                  where r.user_id = sqlc.arg(user_id) and r.state = 'open'), 0)::bigint as reserved_micro_usd
  from users u where u.id = sqlc.arg(user_id);

-- name: OpenReservations :many
-- Holds that were taken and never closed. Without this they are money that is gone from the balance
-- and invisible to everything that could give it back.
select engine_run_id, book_id, amount_micro_usd, ceiling_micro_usd, opened_at
  from reservations where user_id = sqlc.arg(user_id) and state = 'open' order by opened_at;

-- name: OpenReservation :execrows
-- The book must belong to the account being charged. The foreign key only proves the book exists,
-- which is not the same question — hence the `select ... from books where owner_id`, whose zero rows
-- ARE the refusal.
insert into reservations (engine_run_id, user_id, book_id, amount_micro_usd, ceiling_micro_usd, state, opened_at)
select sqlc.arg(engine_run_id), sqlc.arg(user_id), sqlc.arg(book_id), sqlc.arg(amount),
       sqlc.arg(amount), 'open', sqlc.arg(now)
  from books where id = sqlc.arg(book_id) and owner_id = sqlc.arg(user_id);

-- name: LockAttemptUnit :one
-- The reconciler's belief that an attempt never spawned is re-checked HERE, in the same transaction
-- as the money, against the row rather than against the sweep's snapshot.
select unit_name from run_attempts where id = sqlc.arg(id) for update;

-- name: LockAccountBalance :one
-- Every money operation starts here, so they all take their locks in the same order.
-- ⚠ Named LockAccountBalance and not LockBalance: sqlc derives an unexported const from the query
-- name, and `lockBalance` is already the hand-written helper this one serves. Same for
-- LockBookForUpdate, MarkReservationClosed and InsertLedgerEntry.
select balance_micro_usd from account_balances where user_id = sqlc.arg(user_id) for update;

-- name: AccountExists :one
-- No balance row is TWO different facts, and answering both with "insufficient credit" told an
-- operator who mistyped an account id that the account was broke (PD-82). Postgres refuses FOR
-- UPDATE on the nullable side of an outer join, so there is no one-query form of this question.
select true from users where id = sqlc.arg(id);

-- name: ReservationOwner :one
select user_id from reservations where engine_run_id = sqlc.arg(engine_run_id);

-- name: MarkReservationClosed :one
-- The state guard is what makes the unlocked owner read above safe: a reservation closed by someone
-- else in between makes this update match nothing.
update reservations set state = sqlc.arg(state), closed_at = sqlc.arg(now)::timestamptz
 where engine_run_id = sqlc.arg(engine_run_id) and state = 'open'
returning amount_micro_usd;

-- name: InsertLedgerEntry :execrows
-- The ledger is APPEND-ONLY in the code that writes it, not in the schema: no statement in this
-- package updates or deletes a row here, and TestTheLedgerIsAppendOnlyInTheCodeThatWritesIt walks
-- every SQL string to keep that true.
insert into credit_ledger (user_id, kind, amount_micro_usd, source, source_id, note, created_at)
values (sqlc.arg(user_id), sqlc.arg(kind), sqlc.arg(amount), sqlc.arg(source), sqlc.arg(source_id),
        sqlc.arg(note), sqlc.arg(now))
on conflict (user_id, source, source_id) do nothing;

-- name: MoveBalance :exec
-- Written in the SAME transaction as the ledger row above, always. A cache that can lag its source
-- is a second answer about money.
insert into account_balances (user_id, balance_micro_usd, updated_at)
values (sqlc.arg(user_id), sqlc.arg(amount), sqlc.arg(now))
on conflict (user_id) do update
   set balance_micro_usd = account_balances.balance_micro_usd + excluded.balance_micro_usd,
       updated_at = excluded.updated_at;

-- name: CreditHeldBy :one
-- ⚠ The book with the LARGEST hold, not the oldest: naming a book whose $0.03 hold was merely opened
-- first, beside another holding $9.60, sends the user to cancel a run that frees nothing. Ties go to
-- the older one, so the answer is stable between two reads.
select coalesce((select r1.book_id from reservations r1
                  where r1.user_id = sqlc.arg(user_id) and r1.state = 'open'
                    and r1.book_id <> sqlc.arg(except_book_id)
                  group by r1.book_id
                  order by sum(r1.amount_micro_usd) desc, min(r1.opened_at) limit 1), '')::text as book_id,
       coalesce((select sum(r2.amount_micro_usd) from reservations r2
                  where r2.user_id = sqlc.arg(user_id) and r2.state = 'open'
                    and r2.book_id <> sqlc.arg(except_book_id)), 0)::bigint as held_micro_usd;

-- name: LockBookForUpdate :one
-- The FIRST lock of every transaction in this package that touches more than one of:
-- books → runs → run_attempts → account_balances → reservations. A missing book is not an error
-- here: the caller's own statements find nothing and decide what that means.
select id from books where id = sqlc.arg(id) for update;

-- name: DeleteClosedReservationsOfBook :exec
-- Closed reservations carry no financial fact the ledger does not already hold. An OPEN one blocks
-- the book delete through the foreign key, which is the point.
delete from reservations where book_id = sqlc.arg(book_id) and state <> 'open';

-- name: DeleteBook :exec
delete from books where id = sqlc.arg(id);