-- schema.sql
CREATE SCHEMA IF NOT EXISTS cosmos;

CREATE TABLE IF NOT EXISTS cosmos.blocks (
                                             height        BIGINT PRIMARY KEY,
                                             time          TIMESTAMPTZ NOT NULL,
                                             hash          TEXT NOT NULL,
                                             proposer      TEXT NOT NULL,
                                             chain_id      TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS cosmos.block_signatures (
                                                       block_height  BIGINT NOT NULL,
                                                       validator     TEXT   NOT NULL,
                                                       signed        BOOLEAN NOT NULL,
                                                       PRIMARY KEY (block_height, validator)
);

CREATE TABLE IF NOT EXISTS cosmos.transactions (
                                                   hash          TEXT PRIMARY KEY,
                                                   block_height  BIGINT NOT NULL,
                                                   code          INTEGER NOT NULL,
                                                   gas_wanted    BIGINT NOT NULL,
                                                   gas_used      BIGINT NOT NULL,
                                                   memo          TEXT NOT NULL,
                                                   timestamp     TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_txs_block_height ON cosmos.transactions(block_height);

CREATE TABLE IF NOT EXISTS cosmos.messages (
                                               id            TEXT PRIMARY KEY,  -- "<txhash>-<idx>"
                                               tx_hash       TEXT NOT NULL,
                                               msg_index     INTEGER NOT NULL,
                                               type_url      TEXT NOT NULL,
                                               json_body     JSONB NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_msgs_tx ON cosmos.messages(tx_hash);

CREATE TABLE IF NOT EXISTS cosmos.events (
                                             id            TEXT PRIMARY KEY,  -- "<txhash>-<evtIdx>-<key>"
                                             tx_hash       TEXT NOT NULL,
                                             event_type    TEXT NOT NULL,
                                             attr_key      TEXT NOT NULL,
                                             attr_value    TEXT NOT NULL,
                                             created_at    TIMESTAMPTZ NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_events_tx ON cosmos.events(tx_hash);
CREATE INDEX IF NOT EXISTS idx_events_type ON cosmos.events(event_type);

CREATE TABLE IF NOT EXISTS cosmos.checkpoints (
                                                  key           TEXT PRIMARY KEY,      -- 'backfill' | 'live'
                                                  height        BIGINT NOT NULL
);