-- TradingBite warehouse schema v1 (UTC timestamps)
-- Unique candle: (source, symbol, timeframe, timestamp_ms)

CREATE TABLE IF NOT EXISTS candles (
  source TEXT NOT NULL,
  symbol TEXT NOT NULL,
  timeframe TEXT NOT NULL,
  timestamp_ms INTEGER NOT NULL,
  timestamp_iso TEXT NOT NULL,
  open REAL NOT NULL,
  high REAL NOT NULL,
  low REAL NOT NULL,
  close REAL NOT NULL,
  volume REAL,
  volume_type TEXT NOT NULL,
  PRIMARY KEY (source, symbol, timeframe, timestamp_ms)
);

CREATE INDEX IF NOT EXISTS idx_candles_range
  ON candles (symbol, timeframe, timestamp_ms);

CREATE TABLE IF NOT EXISTS dataset_meta (
  source TEXT NOT NULL,
  symbol TEXT NOT NULL,
  timeframe TEXT NOT NULL,
  first_timestamp_ms INTEGER,
  last_timestamp_ms INTEGER,
  row_count INTEGER NOT NULL,
  volume_type TEXT NOT NULL,
  schema_version INTEGER NOT NULL,
  last_sync_at TEXT NOT NULL,
  import_notes TEXT,
  PRIMARY KEY (source, symbol, timeframe)
);

CREATE TABLE IF NOT EXISTS import_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  source TEXT NOT NULL,
  symbol TEXT NOT NULL,
  timeframe TEXT NOT NULL,
  range_from_iso TEXT NOT NULL,
  range_to_iso TEXT NOT NULL,
  rows_inserted INTEGER NOT NULL,
  rows_skipped_duplicate INTEGER NOT NULL,
  validation_errors INTEGER NOT NULL,
  imported_at TEXT NOT NULL
);