-- BestTrading Database Schema
-- PostgreSQL 16 + TimescaleDB
-- Schema Version: 1

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- ============================================
-- SESSIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS sessions (
    id UUID PRIMARY KEY,
    exchange VARCHAR(32) NOT NULL DEFAULT 'kraken',
    pair VARCHAR(32) NOT NULL,
    start_time TIMESTAMPTZ NOT NULL,
    end_time TIMESTAMPTZ,
    schema_version INT NOT NULL DEFAULT 1,
    event_count BIGINT DEFAULT 0,
    trade_count BIGINT DEFAULT 0,
    book_update_count BIGINT DEFAULT 0,
    gap_count INT DEFAULT 0,
    reconnect_count INT DEFAULT 0,
    checksum_mismatch_count INT DEFAULT 0,
    status VARCHAR(32) DEFAULT 'active',
    tags TEXT[],
    stats JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_sessions_pair_time ON sessions(pair, start_time DESC);
CREATE INDEX idx_sessions_status ON sessions(status);

-- ============================================
-- MARKET EVENTS TABLE (TimescaleDB Hypertable)
-- ============================================
CREATE TABLE IF NOT EXISTS market_events (
    id UUID NOT NULL,
    session_id UUID NOT NULL REFERENCES sessions(id),
    timestamp TIMESTAMPTZ NOT NULL,
    exchange VARCHAR(32) NOT NULL,
    pair VARCHAR(32) NOT NULL,
    event_type VARCHAR(32) NOT NULL,
    data JSONB NOT NULL,
    sequence BIGINT,
    checksum INT,
    PRIMARY KEY (timestamp, id)
);

-- Convert to hypertable (chunk by day)
SELECT create_hypertable('market_events', 'timestamp',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

-- Indexes for market_events
CREATE INDEX idx_market_events_session ON market_events(session_id, timestamp);
CREATE INDEX idx_market_events_pair_type ON market_events(pair, event_type, timestamp);
CREATE INDEX idx_market_events_type ON market_events(event_type, timestamp);

-- ============================================
-- TRADES TABLE (denormalized for fast queries)
-- ============================================
CREATE TABLE IF NOT EXISTS trades (
    id UUID NOT NULL,
    session_id UUID NOT NULL REFERENCES sessions(id),
    timestamp TIMESTAMPTZ NOT NULL,
    pair VARCHAR(32) NOT NULL,
    trade_id VARCHAR(64),
    price NUMERIC(20, 10) NOT NULL,
    quantity NUMERIC(20, 10) NOT NULL,
    side VARCHAR(4) NOT NULL,
    ord_type VARCHAR(16),
    PRIMARY KEY (timestamp, id)
);

SELECT create_hypertable('trades', 'timestamp',
    chunk_time_interval => INTERVAL '1 day',
    if_not_exists => TRUE
);

CREATE INDEX idx_trades_pair ON trades(pair, timestamp);
CREATE INDEX idx_trades_session ON trades(session_id, timestamp);

-- ============================================
-- STATE SNAPSHOTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS state_snapshots (
    timestamp TIMESTAMPTZ NOT NULL,
    pair VARCHAR(32) NOT NULL,
    state JSONB NOT NULL,
    regime VARCHAR(32),
    entropy_h NUMERIC(10, 6),
    transition_ts NUMERIC(10, 6),
    data_quality NUMERIC(10, 6),
    PRIMARY KEY (timestamp, pair)
);

SELECT create_hypertable('state_snapshots', 'timestamp',
    chunk_time_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

CREATE INDEX idx_state_pair ON state_snapshots(pair, timestamp);
CREATE INDEX idx_state_regime ON state_snapshots(regime, timestamp);

-- ============================================
-- CALIBRATIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS calibrations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    timestamp TIMESTAMPTZ NOT NULL,
    pair VARCHAR(32) NOT NULL,
    window_type VARCHAR(8) NOT NULL, -- '1h', '4h', '24h'
    metrics JSONB NOT NULL,
    hourly_profile JSONB,
    version INT NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_calibrations_pair_window ON calibrations(pair, window_type, timestamp DESC);

-- ============================================
-- RISK SNAPSHOTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS risk_snapshots (
    timestamp TIMESTAMPTZ NOT NULL,
    state JSONB NOT NULL,
    kill_level INT DEFAULT 0,
    exposure_eur NUMERIC(20, 4),
    pnl_daily NUMERIC(20, 4),
    drawdown_current NUMERIC(10, 6),
    PRIMARY KEY (timestamp)
);

SELECT create_hypertable('risk_snapshots', 'timestamp',
    chunk_time_interval => INTERVAL '1 hour',
    if_not_exists => TRUE
);

-- ============================================
-- ORDERS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS orders (
    id UUID PRIMARY KEY,
    external_id VARCHAR(64),
    bot_id VARCHAR(64) NOT NULL,
    intent_id UUID,
    pair VARCHAR(32) NOT NULL,
    side VARCHAR(4) NOT NULL,
    order_type VARCHAR(16) NOT NULL,
    price NUMERIC(20, 10),
    quantity NUMERIC(20, 10) NOT NULL,
    filled_qty NUMERIC(20, 10) DEFAULT 0,
    avg_fill_price NUMERIC(20, 10),
    status VARCHAR(16) NOT NULL,
    fee NUMERIC(20, 10) DEFAULT 0,
    fee_currency VARCHAR(8),
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_orders_bot ON orders(bot_id, created_at);
CREATE INDEX idx_orders_status ON orders(status, created_at);
CREATE INDEX idx_orders_pair ON orders(pair, created_at);

-- ============================================
-- BOT INTENTS TABLE (audit)
-- ============================================
CREATE TABLE IF NOT EXISTS bot_intents (
    id UUID PRIMARY KEY,
    bot_id VARCHAR(64) NOT NULL,
    config_hash VARCHAR(64) NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    pair VARCHAR(32) NOT NULL,
    action VARCHAR(16) NOT NULL,
    order_type VARCHAR(16) NOT NULL,
    side VARCHAR(4) NOT NULL,
    price NUMERIC(20, 10),
    quantity NUMERIC(20, 10) NOT NULL,
    reason JSONB NOT NULL,
    state_snapshot JSONB,
    regime VARCHAR(32),
    executed BOOLEAN DEFAULT FALSE,
    execution_result JSONB
);

CREATE INDEX idx_intents_bot ON bot_intents(bot_id, timestamp);
CREATE INDEX idx_intents_pair ON bot_intents(pair, timestamp);

-- ============================================
-- POSITIONS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS positions (
    pair VARCHAR(32) PRIMARY KEY,
    asset VARCHAR(16) NOT NULL,
    quantity NUMERIC(20, 10) NOT NULL DEFAULT 0,
    avg_entry_price NUMERIC(20, 10),
    unrealized_pnl NUMERIC(20, 4) DEFAULT 0,
    realized_pnl NUMERIC(20, 4) DEFAULT 0,
    updated_at TIMESTAMPTZ NOT NULL
);

-- ============================================
-- BOT STATES TABLE (for persistence across restarts)
-- ============================================
CREATE TABLE IF NOT EXISTS bot_states (
    bot_id VARCHAR(64) PRIMARY KEY,
    mode VARCHAR(8) NOT NULL DEFAULT 'paper', -- 'paper' or 'live'
    pair VARCHAR(32) NOT NULL,
    status VARCHAR(16) NOT NULL,
    started_at TIMESTAMPTZ,
    stopped_at TIMESTAMPTZ,

    -- Position tracking
    position NUMERIC(20, 10) NOT NULL DEFAULT 0,
    avg_entry_price NUMERIC(20, 10) DEFAULT 0,
    unrealized_pnl NUMERIC(20, 4) DEFAULT 0,

    -- Performance
    realized_pnl NUMERIC(20, 4) DEFAULT 0,
    daily_pnl NUMERIC(20, 4) DEFAULT 0,
    trades_count INT DEFAULT 0,
    win_count INT DEFAULT 0,
    loss_count INT DEFAULT 0,

    -- Equity tracking (bank-grade)
    starting_capital NUMERIC(20, 4) NOT NULL,
    total_fees_paid NUMERIC(20, 8) DEFAULT 0,
    total_slippage NUMERIC(20, 8) DEFAULT 0,
    equity NUMERIC(20, 4) NOT NULL,

    -- Activity
    last_order_time TIMESTAMPTZ,
    last_signal_time TIMESTAMPTZ,
    pending_orders INT DEFAULT 0,

    -- Error tracking
    last_error TEXT,
    error_count INT DEFAULT 0,

    -- Metadata
    config JSONB,
    extended_state JSONB,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_bot_states_mode ON bot_states(mode, pair);
CREATE INDEX idx_bot_states_status ON bot_states(status);

-- ============================================
-- EXPERIMENT RUNS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS experiment_runs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bot_id VARCHAR(64) NOT NULL,
    config JSONB NOT NULL,
    config_hash VARCHAR(64) NOT NULL,
    sessions_used UUID[] NOT NULL,
    started_at TIMESTAMPTZ NOT NULL,
    ended_at TIMESTAMPTZ,
    status VARCHAR(16) DEFAULT 'running',
    metrics JSONB,
    walk_forward_results JSONB,
    stress_test_results JSONB,
    promoted BOOLEAN DEFAULT FALSE,
    promotion_timestamp TIMESTAMPTZ,
    notes TEXT
);

CREATE INDEX idx_experiments_bot ON experiment_runs(bot_id, started_at);
CREATE INDEX idx_experiments_status ON experiment_runs(status, started_at);
CREATE INDEX idx_experiments_promoted ON experiment_runs(promoted, started_at);

-- ============================================
-- BOT CONFIGS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS bot_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bot_id VARCHAR(64) NOT NULL,
    config_hash VARCHAR(64) NOT NULL UNIQUE,
    config JSONB NOT NULL,
    version INT NOT NULL DEFAULT 1,
    status VARCHAR(16) DEFAULT 'active', -- 'active', 'champion', 'retired', 'dead'
    created_at TIMESTAMPTZ DEFAULT NOW(),
    promoted_at TIMESTAMPTZ,
    retired_at TIMESTAMPTZ,
    performance_summary JSONB,
    enabled_regimes TEXT[]
);

CREATE INDEX idx_bot_configs_bot ON bot_configs(bot_id, status);
CREATE INDEX idx_bot_configs_hash ON bot_configs(config_hash);

-- ============================================
-- BOT CEMETERY TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS bot_cemetery (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bot_id VARCHAR(64) NOT NULL,
    config_hash VARCHAR(64) NOT NULL,
    config JSONB NOT NULL,
    death_timestamp TIMESTAMPTZ NOT NULL,
    cause VARCHAR(64) NOT NULL,
    cause_details JSONB,
    regime_at_death VARCHAR(32),
    state_at_death JSONB,
    metrics_at_death JSONB,
    premonitory_signals JSONB,
    lessons_learned TEXT[]
);

CREATE INDEX idx_cemetery_cause ON bot_cemetery(cause, death_timestamp);
CREATE INDEX idx_cemetery_bot ON bot_cemetery(bot_id, death_timestamp);

-- ============================================
-- AUDIT LOG TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    actor VARCHAR(64) NOT NULL, -- 'user:admin', 'system:autopilot', etc.
    action VARCHAR(64) NOT NULL,
    resource_type VARCHAR(32),
    resource_id VARCHAR(64),
    details JSONB,
    ip_address INET,
    user_agent TEXT
);

CREATE INDEX idx_audit_time ON audit_log(timestamp);
CREATE INDEX idx_audit_actor ON audit_log(actor, timestamp);
CREATE INDEX idx_audit_action ON audit_log(action, timestamp);

-- ============================================
-- ALERTS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS alerts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    severity VARCHAR(16) NOT NULL, -- 'info', 'warning', 'error', 'critical'
    category VARCHAR(32) NOT NULL,
    message TEXT NOT NULL,
    details JSONB,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_by VARCHAR(64),
    acknowledged_at TIMESTAMPTZ
);

CREATE INDEX idx_alerts_severity ON alerts(severity, timestamp);
CREATE INDEX idx_alerts_acknowledged ON alerts(acknowledged, timestamp);

-- ============================================
-- LEADERBOARD MATERIALIZED VIEW
-- ============================================
CREATE MATERIALIZED VIEW IF NOT EXISTS bot_leaderboard AS
SELECT
    bc.bot_id,
    bc.config_hash,
    bc.status,
    (bc.performance_summary->>'sharpe')::NUMERIC as sharpe,
    (bc.performance_summary->>'sortino')::NUMERIC as sortino,
    (bc.performance_summary->>'max_dd')::NUMERIC as max_dd,
    (bc.performance_summary->>'win_rate')::NUMERIC as win_rate,
    (bc.performance_summary->>'profit_factor')::NUMERIC as profit_factor,
    (bc.performance_summary->>'total_trades')::INT as total_trades,
    bc.enabled_regimes,
    bc.promoted_at,
    bc.created_at
FROM bot_configs bc
WHERE bc.status IN ('active', 'champion')
ORDER BY
    CASE WHEN bc.status = 'champion' THEN 0 ELSE 1 END,
    (bc.performance_summary->>'sharpe')::NUMERIC DESC NULLS LAST;

CREATE UNIQUE INDEX idx_leaderboard_hash ON bot_leaderboard(config_hash);

-- ============================================
-- COMPRESSION POLICIES (TimescaleDB)
-- ============================================
ALTER TABLE market_events SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'pair',
    timescaledb.compress_orderby = 'timestamp DESC'
);

SELECT add_compression_policy('market_events', INTERVAL '7 days', if_not_exists => TRUE);

ALTER TABLE trades SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'pair',
    timescaledb.compress_orderby = 'timestamp DESC'
);

SELECT add_compression_policy('trades', INTERVAL '7 days', if_not_exists => TRUE);

ALTER TABLE state_snapshots SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'pair',
    timescaledb.compress_orderby = 'timestamp DESC'
);

SELECT add_compression_policy('state_snapshots', INTERVAL '2 days', if_not_exists => TRUE);

-- ============================================
-- RETENTION POLICIES
-- ============================================
-- Keep detailed events for 90 days, then archive to Parquet
SELECT add_retention_policy('market_events', INTERVAL '90 days', if_not_exists => TRUE);
SELECT add_retention_policy('state_snapshots', INTERVAL '30 days', if_not_exists => TRUE);
SELECT add_retention_policy('risk_snapshots', INTERVAL '30 days', if_not_exists => TRUE);

-- Keep trades indefinitely (no retention policy)

-- ============================================
-- HELPER FUNCTIONS
-- ============================================
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sessions_updated_at
    BEFORE UPDATE ON sessions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- Function to get session index for fast seek
CREATE OR REPLACE FUNCTION get_session_time_index(p_session_id UUID)
RETURNS TABLE (
    minute_bucket TIMESTAMPTZ,
    first_event_id UUID,
    event_count BIGINT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        time_bucket('1 minute', timestamp) as minute_bucket,
        MIN(id) as first_event_id,
        COUNT(*) as event_count
    FROM market_events
    WHERE session_id = p_session_id
    GROUP BY time_bucket('1 minute', timestamp)
    ORDER BY minute_bucket;
END;
$$ LANGUAGE plpgsql;

-- Refresh leaderboard function
CREATE OR REPLACE FUNCTION refresh_leaderboard()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY bot_leaderboard;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- GRANT PERMISSIONS
-- ============================================
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bestrading;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO bestrading;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO bestrading;

-- Done
SELECT 'Database schema initialized successfully' as status;
