-- schema.sql
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

CREATE TABLE IF NOT EXISTS meta (
  key TEXT PRIMARY KEY,
  value TEXT
);

-- Raw on-chain logs
CREATE TABLE IF NOT EXISTS raw_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tx_hash TEXT NOT NULL,
  log_index INTEGER NOT NULL,
  block_number INTEGER NOT NULL,
  address TEXT NOT NULL,
  topics TEXT,               -- JSON array string
  data TEXT,
  block_time INTEGER,        -- unix epoch from block header
  received_at INTEGER,
  UNIQUE(tx_hash, log_index)
);

-- Normalized ERC-20 transfers (POL only or all, your call)
CREATE TABLE IF NOT EXISTS transfers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  tx_hash TEXT NOT NULL,
  block_number INTEGER NOT NULL,
  log_index INTEGER NOT NULL,
  block_time INTEGER NOT NULL,
  from_address TEXT NOT NULL,
  to_address TEXT NOT NULL,
  amount_raw TEXT NOT NULL,     -- 256-bit safe (store as decimal string)
  amount_dec TEXT NOT NULL,     -- decimal string normalized
  token_address TEXT NOT NULL,
  received_at INTEGER,
  UNIQUE(tx_hash, log_index)
);

CREATE INDEX IF NOT EXISTS idx_transfers_block ON transfers(block_number);
CREATE INDEX IF NOT EXISTS idx_transfers_from ON transfers(from_address);
CREATE INDEX IF NOT EXISTS idx_transfers_to   ON transfers(to_address);
CREATE INDEX IF NOT EXISTS idx_transfers_token ON transfers(token_address);

-- Append-only snapshots; one row per update tick
CREATE TABLE IF NOT EXISTS netflow (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at INTEGER NOT NULL,       -- unix epoch
  cumulative_value TEXT NOT NULL -- decimal string (POL units)
);

-- Optional future multi-exchange support
CREATE TABLE IF NOT EXISTS exchange_addresses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  exchange_name TEXT NOT NULL,
  address TEXT NOT NULL,
  UNIQUE(exchange_name, address)
);
CREATE INDEX IF NOT EXISTS idx_exaddr_exchange ON exchange_addresses(exchange_name);