-- refined-backend/schema.sql
-- The Market Vault: Persistent Storage for ReFinEd Market Data
-- Pattern: Follows federation-vault / thought-vault D1 conventions

-- Daily candle data (OHLCV) — the core persistent store
CREATE TABLE IF NOT EXISTS candles (
    ticker TEXT NOT NULL,
    date TEXT NOT NULL,              -- YYYY-MM-DD
    ts INTEGER NOT NULL,             -- Unix timestamp
    open REAL,
    high REAL NOT NULL,
    low REAL NOT NULL,
    close REAL NOT NULL,
    volume INTEGER,
    source TEXT DEFAULT 'finnhub',   -- finnhub, yfinance, simulation
    ingested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    batch_id TEXT,                   -- 'migration_v1' for initial seed, NULL for live
    PRIMARY KEY (ticker, date)
);

-- Metadata about each ticker's cache state
CREATE TABLE IF NOT EXISTS ticker_meta (
    ticker TEXT PRIMARY KEY,
    last_updated INTEGER NOT NULL,   -- Unix timestamp of last fetch
    resolution TEXT DEFAULT 'D',
    candle_count INTEGER DEFAULT 0,
    earliest_date TEXT,
    latest_date TEXT
);

-- User interest signal tracking (aggregate, non-PII)
CREATE TABLE IF NOT EXISTS interest_signals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticker TEXT NOT NULL,
    action TEXT NOT NULL,             -- 'quote', 'history', 'search', 'browse'
    recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for query performance
CREATE INDEX IF NOT EXISTS idx_candles_ticker ON candles(ticker);
CREATE INDEX IF NOT EXISTS idx_candles_date ON candles(date);
CREATE INDEX IF NOT EXISTS idx_signals_ticker ON interest_signals(ticker);
CREATE INDEX IF NOT EXISTS idx_signals_action ON interest_signals(action);