-- Centaur Quant Architecture — Production Telemetry Schema (v1.0)
-- System Finalization deliverable: canonical DDL aligned with the SQLite
-- schema created by Python/database/telemetry.py (Track B).
-- Target: PostgreSQL. For SQLite (dev), use INTEGER PRIMARY KEY and TEXT
-- for the ISO-8601 UTC timestamps; NUMERIC maps to REAL.

CREATE TABLE IF NOT EXISTS trades (
    ticket      BIGINT PRIMARY KEY,          -- MT5 position/order ticket
    symbol      TEXT        NOT NULL,
    direction   TEXT        NOT NULL,        -- 'buy' | 'sell'
    lot         NUMERIC(12,4) NOT NULL,
    entry_price NUMERIC(20,8) NOT NULL,
    ai_score    NUMERIC(6,2) NOT NULL DEFAULT 0,   -- advisory score at open
    open_time   TIMESTAMPTZ,                 -- ISO-8601 UTC
    close_time  TIMESTAMPTZ,                 -- set on close
    profit      NUMERIC(20,2),               -- net PnL in deposit currency
    r_multiple  NUMERIC(12,4)                -- net PnL / initial risk amount
);

CREATE INDEX IF NOT EXISTS idx_trades_symbol  ON trades(symbol);
CREATE INDEX IF NOT EXISTS idx_trades_ai      ON trades(ai_score);
CREATE INDEX IF NOT EXISTS idx_trades_outcome ON trades(r_multiple);

-- Calibration view: score -> outcome (the ML training signal)
CREATE OR REPLACE VIEW v_feedback AS
SELECT ticket, symbol, ai_score, profit, r_multiple, open_time, close_time
FROM trades
WHERE close_time IS NOT NULL;