-- 001_init.sql — Essence ledger (idempotent).
--
-- Postgres-backed ledger for PYRE rounds, cleanup receipts, and Essence
-- contributions. All lamport amounts are BIGINT (u64-safe at the SQL layer);
-- the TypeScript layer marshals them as strings.
--
-- Safe to run repeatedly: every object uses IF NOT EXISTS.

CREATE TABLE IF NOT EXISTS rounds (
  id               BIGSERIAL PRIMARY KEY,
  status           TEXT        NOT NULL DEFAULT 'open'
                     CHECK (status IN ('open', 'closed')),
  essence_lamports BIGINT      NOT NULL DEFAULT 0,
  started_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  closed_at        TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS cleanup_receipts (
  id                     BIGSERIAL PRIMARY KEY,
  wallet                 TEXT        NOT NULL,
  tx_signature           TEXT        NOT NULL UNIQUE,
  kind                   TEXT        NOT NULL
                           CHECK (kind IN ('close', 'burn')),
  rent_returned_lamports BIGINT      NOT NULL,
  fee_lamports           BIGINT      NOT NULL DEFAULT 0,
  closed_accounts        JSONB       NOT NULL DEFAULT '[]',
  created_at             TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS essence_contributions (
  id           BIGSERIAL PRIMARY KEY,
  round_id     BIGINT      NOT NULL REFERENCES rounds(id),
  wallet       TEXT        NOT NULL,
  tx_signature TEXT        NOT NULL UNIQUE,
  lamports     BIGINT      NOT NULL,
  kind         TEXT        NOT NULL
                 CHECK (kind IN ('fee', 'contribution')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_essence_contributions_round_id
  ON essence_contributions (round_id);

CREATE INDEX IF NOT EXISTS idx_cleanup_receipts_wallet
  ON cleanup_receipts (wallet);