-- Author: Simon-Pierre Boucher
-- Mail: contact@spboucher.ai
-- Canonical schema (auto-derived from indexer/db.py SCHEMA).
-- Runtime auto-applies this on connect; file kept for manual PostgreSQL setup.

CREATE TABLE IF NOT EXISTS chains (
    chain    TEXT PRIMARY KEY,
    family   TEXT,
    chain_id BIGINT
);

CREATE TABLE IF NOT EXISTS transfers (
    chain      TEXT    NOT NULL,
    block      BIGINT  NOT NULL,
    block_hash TEXT,
    tx_hash    TEXT    NOT NULL,
    log_index  INTEGER NOT NULL,
    timestamp  BIGINT,
    token      TEXT    NOT NULL,
    symbol     TEXT,
    "from"     TEXT,
    "to"       TEXT,
    amount     TEXT,
    decimals   INTEGER,
    PRIMARY KEY (chain, tx_hash, log_index)
);
CREATE INDEX IF NOT EXISTS idx_transfers_token_block ON transfers (chain, token, block);
CREATE INDEX IF NOT EXISTS idx_transfers_ts          ON transfers (symbol, timestamp);
CREATE INDEX IF NOT EXISTS idx_transfers_from        ON transfers ("from");
CREATE INDEX IF NOT EXISTS idx_transfers_to          ON transfers ("to");

CREATE TABLE IF NOT EXISTS cursors (
    chain          TEXT PRIMARY KEY,
    last_block     BIGINT NOT NULL,
    last_hash      TEXT,
    backfill_block BIGINT,
    head_block     BIGINT
);

CREATE TABLE IF NOT EXISTS rpc_health (
    chain      TEXT NOT NULL,
    url        TEXT NOT NULL,
    score      REAL,
    ok         INTEGER,
    fail       INTEGER,
    latency_ms REAL,
    cooldown_s REAL,
    updated    BIGINT,
    PRIMARY KEY (chain, url)
);

CREATE TABLE IF NOT EXISTS tokens (
    chain    TEXT NOT NULL,
    address  TEXT NOT NULL,
    symbol   TEXT,
    decimals INTEGER,
    native   INTEGER,
    PRIMARY KEY (chain, address)
);

CREATE TABLE IF NOT EXISTS supply_snapshots (
    chain     TEXT   NOT NULL,
    token     TEXT   NOT NULL,
    symbol    TEXT,
    supply    TEXT,
    decimals  INTEGER,
    timestamp BIGINT NOT NULL,
    PRIMARY KEY (chain, token, timestamp)
);
CREATE INDEX IF NOT EXISTS idx_supply_ts ON supply_snapshots (symbol, timestamp);