-- Initial schema for blackbeard.observer.
--
-- Migrations are sequentially versioned and immutable once committed
-- (architecture/generic.md §5): correct a mistake with a new file, never by
-- editing this one, or the runner's checksum diverges and it refuses to start.

-- One row per chain the observer watches. Config in `config.toml` is the source
-- of truth for which chains exist; this table holds what the *node* told us
-- about them (genesis, token) plus the observer's own bookkeeping, so a restart
-- does not have to re-discover everything before it can serve a page.
create table chain (
    id                        text        primary key,
    display_name              text        not null,
    mainnet                   boolean     not null,
    genesis                   text,
    token_symbol              text,
    token_decimals            smallint,
    target_block_time_seconds double precision not null,
    first_observed_at         timestamptz not null default now(),
    last_observed_at          timestamptz
);

-- Every block the observer has seen, with its author.
--
-- Keyed on (chain, height) rather than on the block hash, deliberately: this is
-- a proof-of-work chain and it reorgs. An upsert on this key means a block that
-- replaces another at the same height overwrites it, which is exactly the
-- semantics wanted — the leaderboard should reflect the canonical chain, not
-- the union of every fork the observer happened to witness.
create table block (
    chain       text        not null references chain (id) on delete cascade,
    height      bigint      not null,
    hash        text        not null,
    miner       text        not null,
    -- The author's own timestamp, from the block's Timestamp::set inherent. Set
    -- when the author BUILDS the proposal, so it measures proposal timing.
    authored_at timestamptz,
    -- When this observer first saw the block. The difference between the two is
    -- propagation plus poll lag when negative, a future-dated block when
    -- positive, and a withheld block when large and positive-going.
    observed_at timestamptz not null,
    -- Expected hashes to win this block. numeric, not bigint: difficulty is a
    -- U512 and routinely past 1e30, so every integer type Postgres has would
    -- overflow. numeric is arbitrary-precision and orders and sums correctly.
    difficulty  numeric,
    primary key (chain, height)
);

-- The leaderboard's hot query: the last N blocks of a chain, newest first.
create index block_chain_height_desc_idx on block (chain, height desc);

-- The per-miner history query: one miner's blocks over a time range.
create index block_chain_miner_observed_idx on block (chain, miner, observed_at desc);

-- Held telemetry attributions, so a restart does not strip every author of its
-- name for a few blocks.
--
-- That churn is not cosmetic: a row whose display flips from a node name to a
-- raw preimage and back splits the miner's series on every deploy, which is
-- precisely the continuity this site exists to provide.
create table miner_attribution (
    chain      text        not null references chain (id) on delete cascade,
    miner      text        not null,
    -- 'peer' for a libp2p peer id (stable across restarts and renames) or
    -- 'node' for a telemetry node id (per-connection, the fallback).
    node_kind  text        not null check (node_kind in ('peer', 'node')),
    node_key   text        not null,
    node_name  text,
    attempts   integer     not null default 0,
    attributed integer     not null default 0,
    updated_at timestamptz not null default now(),
    primary key (chain, miner)
);