-- Stars local ledger: per-user balance + transaction history. Previously
-- payments.getStarsStatus always returned a zero balance, getStarsTransactions
-- was unregistered and sendPaidReaction always returned BALANCE_TOO_LOW -- no
-- balance was persisted at all.
-- Local ledger (not real payments): the starting balance is granted lazily on
-- first read (the granted boolean makes it idempotent; both new and existing
-- accounts are covered, with no backfill migration).
-- Debit atomicity is guaranteed by the store layer's withTx (SELECT ... FOR UPDATE
-- + CHECK(balance>=0) + UPDATE + INSERT).
CREATE TABLE public.stars_balances (
    user_id bigint NOT NULL,
    balance bigint DEFAULT 0 NOT NULL,
    granted boolean DEFAULT false NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    CONSTRAINT stars_balances_pkey PRIMARY KEY (user_id),
    CONSTRAINT stars_balances_balance_nonneg CHECK ((balance >= 0))
);

-- Ledger rows: amount is signed (credit > 0 / debit < 0); peer_* is the
-- counterparty (empty when there is none, e.g. grant/topup).
CREATE TABLE public.stars_transactions (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    user_id bigint NOT NULL,
    peer_type text DEFAULT '' NOT NULL,
    peer_id bigint DEFAULT 0 NOT NULL,
    amount bigint NOT NULL,
    reason text DEFAULT 'adjust' NOT NULL,
    title text DEFAULT '' NOT NULL,
    description text DEFAULT '' NOT NULL,
    date integer DEFAULT 0 NOT NULL,
    CONSTRAINT stars_transactions_pkey PRIMARY KEY (id)
);

-- keyset pagination: WHERE user_id=$1 [AND id < cursor] ORDER BY id DESC LIMIT n.
CREATE INDEX stars_transactions_user_id_idx ON public.stars_transactions USING btree (user_id, id DESC);