CREATE TABLE pm_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    received_at_ms INTEGER NOT NULL,   -- local UTC ms at receipt
    subscription_id TEXT NOT NULL,     -- e.g. "btc-2026-04-17-12:15"
    event_type TEXT NOT NULL,          -- "book" | "price_change" | ...
    asset_id TEXT,                     -- token_id (YES or NO) if present
    condition_id TEXT,                 -- parent market if resolvable
    payload_json TEXT NOT NULL         -- raw event body as JSON
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE INDEX ix_pm_events_sub_time
    ON pm_events(subscription_id, received_at_ms);
CREATE INDEX ix_pm_events_asset_time
    ON pm_events(asset_id, received_at_ms);
CREATE INDEX ix_pm_events_asset_type_time
    ON pm_events(asset_id, event_type, received_at_ms);
CREATE INDEX ix_pm_events_type_time
    ON pm_events(event_type, received_at_ms);
CREATE TABLE cex_trades (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    received_at_ms INTEGER NOT NULL,   -- local UTC ms at receipt
    trade_time_ms INTEGER NOT NULL,    -- exchange timestamp
    symbol TEXT NOT NULL,              -- "BTCUSDT" etc.
    price REAL NOT NULL,
    size REAL NOT NULL,
    is_buyer_maker INTEGER NOT NULL    -- 1/0
);
CREATE INDEX ix_cex_trades_symbol_time
    ON cex_trades(symbol, received_at_ms);
CREATE INDEX ix_cex_trades_symbol_trade_time
    ON cex_trades(symbol, trade_time_ms);
CREATE TABLE markets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    scan_at_ms INTEGER NOT NULL,
    condition_id TEXT NOT NULL,
    question TEXT NOT NULL,
    end_date_iso TEXT,                 -- Gamma endDate, ISO 8601
    yes_token_id TEXT,
    no_token_id TEXT,
    symbol TEXT,                       -- BTC/ETH/SOL/XRP/DOGE for Bot G replay
    duration_minutes INTEGER,          -- 5 or 15 when inferable from question
    volume_24h_usd REAL,
    yes_price REAL,
    category TEXT DEFAULT 'crypto',
    raw_json TEXT                      -- full Gamma row for debugging
);
CREATE INDEX ix_markets_condition_scan
    ON markets(condition_id, scan_at_ms);
CREATE INDEX ix_markets_scan_time
    ON markets(scan_at_ms);
CREATE TABLE heartbeats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    emitted_at_ms INTEGER NOT NULL,
    source TEXT NOT NULL,              -- "pm", "cex", "discovery"
    subscription_id TEXT,              -- optional: specific subscription
    last_message_age_sec REAL,         -- seconds since last message on this source
    metadata_json TEXT
);
CREATE INDEX ix_heartbeats_source_time
    ON heartbeats(source, emitted_at_ms);
CREATE TABLE gaps (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source TEXT NOT NULL,
    subscription_id TEXT,
    gap_start_ms INTEGER NOT NULL,
    gap_end_ms INTEGER NOT NULL,
    duration_sec REAL NOT NULL,
    detected_at_ms INTEGER NOT NULL
);
CREATE INDEX ix_gaps_source_start
    ON gaps(source, gap_start_ms);
CREATE TABLE schema_version (version INTEGER PRIMARY KEY);
CREATE INDEX ix_markets_symbol_end_scan ON markets(symbol, end_date_iso, scan_at_ms);
CREATE INDEX ix_markets_duration_scan ON markets(duration_minutes, scan_at_ms);

-- table row counts --
pm_events: 27919423
sqlite_sequence: 4
cex_trades: 271566165
markets: 23750
heartbeats: 444797
gaps: 0
schema_version: 1
