foxhunt/migrations/044_advanced_performance_metrics.sql

-- ================================================================================================
-- Migration 044: Advanced Performance Metrics for Paper Trading
-- Adds Sortino ratio, Calmar ratio, VaR, CVaR, maximum drawdown calculations
-- ================================================================================================

-- ================================================================================================
-- Drop existing functions with conflicting names
-- ================================================================================================
DROP FUNCTION IF EXISTS calculate_sharpe_ratio(VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_max_drawdown(VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_sortino_ratio(VARCHAR, VARCHAR, INTEGER, DOUBLE PRECISION);
DROP FUNCTION IF EXISTS calculate_calmar_ratio(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_var_95(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_cvar_95(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS get_comprehensive_performance_metrics(VARCHAR, INTEGER);

-- ================================================================================================
-- Function: Calculate Sortino Ratio (Downside Risk-Adjusted Returns)
-- Similar to Sharpe but only considers downside volatility
-- ================================================================================================

CREATE OR REPLACE FUNCTION calculate_sortino_ratio(
    p_model_id VARCHAR(50),
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24,
    p_risk_free_rate DOUBLE PRECISION DEFAULT 0.0
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    v_avg_return DOUBLE PRECISION;
    v_downside_std DOUBLE PRECISION;
    v_sortino_ratio DOUBLE PRECISION;
BEGIN
    -- Calculate average return and downside standard deviation
    SELECT
        AVG(pnl),
        STDDEV(CASE WHEN pnl < 0 THEN pnl ELSE NULL END)
    INTO v_avg_return, v_downside_std
    FROM ensemble_predictions
    WHERE
        prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
        AND (p_symbol IS NULL OR symbol = p_symbol)
        AND actual_outcome IS NOT NULL
        AND (
            (p_model_id = 'DQN' AND dqn_vote IS NOT NULL) OR
            (p_model_id = 'PPO' AND ppo_vote IS NOT NULL) OR
            (p_model_id = 'MAMBA2' AND mamba2_vote IS NOT NULL) OR
            (p_model_id = 'TFT' AND tft_vote IS NOT NULL)
        );

    -- Calculate Sortino ratio (annualized)
    IF v_downside_std IS NOT NULL AND v_downside_std > 0 THEN
        v_sortino_ratio := ((v_avg_return - p_risk_free_rate) / v_downside_std) * SQRT(252);
    ELSE
        v_sortino_ratio := NULL;
    END IF;

    RETURN v_sortino_ratio;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION calculate_sortino_ratio IS 'Calculate Sortino ratio (downside risk-adjusted returns) for a model';

-- ================================================================================================
-- Function: Calculate Maximum Drawdown (Peak-to-Trough Decline)
-- ================================================================================================
CREATE OR REPLACE FUNCTION calculate_max_drawdown(
    p_model_id VARCHAR(50),
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    v_max_drawdown DOUBLE PRECISION;
BEGIN
    -- Calculate maximum drawdown using running cumulative P&L
    WITH cumulative_pnl AS (
        SELECT
            prediction_timestamp,
            SUM(pnl) OVER (ORDER BY prediction_timestamp) AS running_pnl
        FROM ensemble_predictions
        WHERE
            prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
            AND (p_symbol IS NULL OR symbol = p_symbol)
            AND actual_outcome IS NOT NULL
            AND (
                (p_model_id = 'DQN' AND dqn_vote IS NOT NULL) OR
                (p_model_id = 'PPO' AND ppo_vote IS NOT NULL) OR
                (p_model_id = 'MAMBA2' AND mamba2_vote IS NOT NULL) OR
                (p_model_id = 'TFT' AND tft_vote IS NOT NULL)
            )
    ),
    running_max AS (
        SELECT
            prediction_timestamp,
            running_pnl,
            MAX(running_pnl) OVER (ORDER BY prediction_timestamp) AS peak_pnl
        FROM cumulative_pnl
    ),
    drawdowns AS (
        SELECT
            (peak_pnl - running_pnl) / NULLIF(ABS(peak_pnl), 0) AS drawdown_pct
        FROM running_max
        WHERE peak_pnl > 0
    )
    SELECT MAX(drawdown_pct)
    INTO v_max_drawdown
    FROM drawdowns;

    RETURN COALESCE(v_max_drawdown, 0.0);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION calculate_max_drawdown IS 'Calculate maximum peak-to-trough decline for a model';

-- ================================================================================================
-- Function: Calculate Calmar Ratio (Return / Max Drawdown)
-- ================================================================================================
CREATE OR REPLACE FUNCTION calculate_calmar_ratio(
    p_model_id VARCHAR(50),
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    v_annualized_return DOUBLE PRECISION;
    v_max_drawdown DOUBLE PRECISION;
    v_calmar_ratio DOUBLE PRECISION;
BEGIN
    -- Calculate annualized return (sum of P&L over period, annualized)
    SELECT
        (SUM(pnl) / COUNT(*)) * 252  -- Annualize assuming 252 trading days
    INTO v_annualized_return
    FROM ensemble_predictions
    WHERE
        prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
        AND (p_symbol IS NULL OR symbol = p_symbol)
        AND actual_outcome IS NOT NULL
        AND (
            (p_model_id = 'DQN' AND dqn_vote IS NOT NULL) OR
            (p_model_id = 'PPO' AND ppo_vote IS NOT NULL) OR
            (p_model_id = 'MAMBA2' AND mamba2_vote IS NOT NULL) OR
            (p_model_id = 'TFT' AND tft_vote IS NOT NULL)
        );

    -- Get max drawdown
    v_max_drawdown := calculate_max_drawdown(p_model_id, p_symbol, p_window_hours);

    -- Calculate Calmar ratio
    IF v_max_drawdown IS NOT NULL AND v_max_drawdown > 0 THEN
        v_calmar_ratio := v_annualized_return / v_max_drawdown;
    ELSE
        v_calmar_ratio := NULL;
    END IF;

    RETURN v_calmar_ratio;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION calculate_calmar_ratio IS 'Calculate Calmar ratio (annualized return / max drawdown)';

-- ================================================================================================
-- Function: Calculate Value at Risk (VaR) - 95th Percentile Loss
-- ================================================================================================
CREATE OR REPLACE FUNCTION calculate_var_95(
    p_model_id VARCHAR(50),
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    v_var_95 DOUBLE PRECISION;
BEGIN
    -- Calculate 95th percentile of losses (5th percentile of returns)
    SELECT
        PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY pnl)
    INTO v_var_95
    FROM ensemble_predictions
    WHERE
        prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
        AND (p_symbol IS NULL OR symbol = p_symbol)
        AND actual_outcome IS NOT NULL
        AND (
            (p_model_id = 'DQN' AND dqn_vote IS NOT NULL) OR
            (p_model_id = 'PPO' AND ppo_vote IS NOT NULL) OR
            (p_model_id = 'MAMBA2' AND mamba2_vote IS NOT NULL) OR
            (p_model_id = 'TFT' AND tft_vote IS NOT NULL)
        );

    RETURN COALESCE(v_var_95, 0.0);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION calculate_var_95 IS 'Calculate Value at Risk (95th percentile loss)';

-- ================================================================================================
-- Function: Calculate Conditional VaR (CVaR) - Expected Loss Beyond VaR
-- ================================================================================================
CREATE OR REPLACE FUNCTION calculate_cvar_95(
    p_model_id VARCHAR(50),
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24
)
RETURNS DOUBLE PRECISION AS $$
DECLARE
    v_var_95 DOUBLE PRECISION;
    v_cvar_95 DOUBLE PRECISION;
BEGIN
    -- Get VaR (5th percentile)
    v_var_95 := calculate_var_95(p_model_id, p_symbol, p_window_hours);

    -- Calculate expected loss beyond VaR (conditional expectation)
    SELECT
        AVG(pnl)
    INTO v_cvar_95
    FROM ensemble_predictions
    WHERE
        prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
        AND (p_symbol IS NULL OR symbol = p_symbol)
        AND actual_outcome IS NOT NULL
        AND pnl <= v_var_95
        AND (
            (p_model_id = 'DQN' AND dqn_vote IS NOT NULL) OR
            (p_model_id = 'PPO' AND ppo_vote IS NOT NULL) OR
            (p_model_id = 'MAMBA2' AND mamba2_vote IS NOT NULL) OR
            (p_model_id = 'TFT' AND tft_vote IS NOT NULL)
        );

    RETURN COALESCE(v_cvar_95, 0.0);
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION calculate_cvar_95 IS 'Calculate Conditional VaR (expected loss beyond VaR threshold)';

-- ================================================================================================
-- Enhanced Performance Metrics Function (with all metrics)
-- Replaces get_real_performance_metrics with comprehensive metrics
-- ================================================================================================
CREATE OR REPLACE FUNCTION get_comprehensive_performance_metrics(
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24
)
RETURNS TABLE (
    model_id VARCHAR(50),
    total_predictions INTEGER,
    win_rate DOUBLE PRECISION,
    sharpe_ratio DOUBLE PRECISION,
    sortino_ratio DOUBLE PRECISION,
    calmar_ratio DOUBLE PRECISION,
    max_drawdown DOUBLE PRECISION,
    var_95 DOUBLE PRECISION,
    cvar_95 DOUBLE PRECISION,
    avg_pnl DOUBLE PRECISION,
    total_pnl BIGINT,
    total_trades INTEGER,
    avg_confidence DOUBLE PRECISION
) AS $$
DECLARE
    v_model_ids VARCHAR[] := ARRAY['DQN', 'PPO', 'MAMBA2', 'TFT'];
    v_model_id VARCHAR(50);
BEGIN
    -- Loop through each model and return comprehensive metrics
    FOREACH v_model_id IN ARRAY v_model_ids
    LOOP
        RETURN QUERY
        SELECT
            v_model_id AS model_id,
            COUNT(*)::INTEGER AS total_predictions,
            (COUNT(CASE WHEN actual_outcome = 'WIN' THEN 1 END)::DOUBLE PRECISION / NULLIF(COUNT(*), 0)) AS win_rate,

            -- Sharpe ratio (from existing calculation)
            (AVG(ep.pnl) / NULLIF(STDDEV(ep.pnl), 0)) * SQRT(252) AS sharpe_ratio,

            -- Sortino ratio (call function)
            calculate_sortino_ratio(v_model_id, p_symbol, p_window_hours) AS sortino_ratio,

            -- Calmar ratio (call function)
            calculate_calmar_ratio(v_model_id, p_symbol, p_window_hours) AS calmar_ratio,

            -- Maximum drawdown (call function)
            calculate_max_drawdown(v_model_id, p_symbol, p_window_hours) AS max_drawdown,

            -- VaR 95% (call function)
            calculate_var_95(v_model_id, p_symbol, p_window_hours) AS var_95,

            -- CVaR 95% (call function)
            calculate_cvar_95(v_model_id, p_symbol, p_window_hours) AS cvar_95,

            AVG(ep.pnl) AS avg_pnl,
            SUM(ep.pnl) AS total_pnl,
            COUNT(CASE WHEN actual_outcome IN ('WIN', 'LOSS', 'BREAKEVEN') THEN 1 END)::INTEGER AS total_trades,
            AVG(ep.ensemble_confidence) AS avg_confidence

        FROM ensemble_predictions ep
        WHERE
            ep.prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
            AND (p_symbol IS NULL OR ep.symbol = p_symbol)
            AND ep.actual_outcome IS NOT NULL
            AND (
                (v_model_id = 'DQN' AND ep.dqn_vote IS NOT NULL) OR
                (v_model_id = 'PPO' AND ep.ppo_vote IS NOT NULL) OR
                (v_model_id = 'MAMBA2' AND ep.mamba2_vote IS NOT NULL) OR
                (v_model_id = 'TFT' AND ep.tft_vote IS NOT NULL)
            )
        GROUP BY v_model_id
        HAVING COUNT(*) > 0;  -- Only return models with predictions

    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_comprehensive_performance_metrics IS 'Get comprehensive performance metrics including Sharpe, Sortino, Calmar, VaR, CVaR for all models';

-- ================================================================================================
-- Update model_performance_attribution with new fields
-- ================================================================================================
ALTER TABLE model_performance_attribution
ADD COLUMN IF NOT EXISTS var_95 DOUBLE PRECISION,
ADD COLUMN IF NOT EXISTS cvar_95 DOUBLE PRECISION,
ADD COLUMN IF NOT EXISTS calmar_ratio DOUBLE PRECISION;

COMMENT ON COLUMN model_performance_attribution.var_95 IS 'Value at Risk (95th percentile loss)';
COMMENT ON COLUMN model_performance_attribution.cvar_95 IS 'Conditional VaR (expected loss beyond VaR)';
COMMENT ON COLUMN model_performance_attribution.calmar_ratio IS 'Calmar ratio (annualized return / max drawdown)';

-- ================================================================================================
-- Update trigger function to calculate additional metrics
-- ================================================================================================
CREATE OR REPLACE FUNCTION update_model_performance_metrics()
RETURNS TRIGGER AS $$
DECLARE
    v_model_ids VARCHAR[] := ARRAY['DQN', 'PPO', 'MAMBA2', 'TFT'];
    v_model_id VARCHAR(50);
    v_window_hours INTEGER[] := ARRAY[1, 24, 168]; -- 1h, 24h, 1 week
    v_window INTEGER;
    v_total_predictions INTEGER;
    v_correct_predictions INTEGER;
    v_total_pnl BIGINT;
    v_total_trades INTEGER;
    v_winning_trades INTEGER;
    v_avg_pnl DOUBLE PRECISION;
    v_stddev_pnl DOUBLE PRECISION;
    v_sharpe_ratio DOUBLE PRECISION;
    v_sortino_ratio DOUBLE PRECISION;
    v_calmar_ratio DOUBLE PRECISION;
    v_max_drawdown DOUBLE PRECISION;
    v_var_95 DOUBLE PRECISION;
    v_cvar_95 DOUBLE PRECISION;
    v_win_rate DOUBLE PRECISION;
BEGIN
    -- Only recalculate if outcome was just recorded
    IF (TG_OP = 'UPDATE' AND NEW.actual_outcome IS NOT NULL AND OLD.actual_outcome IS NULL) THEN

        -- Loop through each model
        FOREACH v_model_id IN ARRAY v_model_ids
        LOOP
            -- Loop through each window
            FOREACH v_window IN ARRAY v_window_hours
            LOOP
                -- Calculate basic metrics
                SELECT
                    COUNT(*) AS total_predictions,
                    COUNT(CASE WHEN actual_outcome = 'WIN' THEN 1 END) AS correct_predictions,
                    COALESCE(SUM(pnl), 0) AS total_pnl,
                    COUNT(CASE WHEN actual_outcome IN ('WIN', 'LOSS', 'BREAKEVEN') THEN 1 END) AS total_trades,
                    COUNT(CASE WHEN actual_outcome = 'WIN' THEN 1 END) AS winning_trades,
                    AVG(pnl) AS avg_pnl,
                    STDDEV(pnl) AS stddev_pnl
                INTO
                    v_total_predictions, v_correct_predictions, v_total_pnl,
                    v_total_trades, v_winning_trades, v_avg_pnl, v_stddev_pnl
                FROM ensemble_predictions
                WHERE
                    prediction_timestamp >= NOW() - (v_window || ' hours')::INTERVAL
                    AND symbol = NEW.symbol
                    AND actual_outcome IS NOT NULL
                    AND (
                        (v_model_id = 'DQN' AND dqn_vote IS NOT NULL) OR
                        (v_model_id = 'PPO' AND ppo_vote IS NOT NULL) OR
                        (v_model_id = 'MAMBA2' AND mamba2_vote IS NOT NULL) OR
                        (v_model_id = 'TFT' AND tft_vote IS NOT NULL)
                    );

                -- Calculate Sharpe ratio (annualized)
                IF v_stddev_pnl IS NOT NULL AND v_stddev_pnl > 0 THEN
                    v_sharpe_ratio := (v_avg_pnl / v_stddev_pnl) * SQRT(252);
                ELSE
                    v_sharpe_ratio := NULL;
                END IF;

                -- Calculate win rate
                IF v_total_trades > 0 THEN
                    v_win_rate := v_winning_trades::DOUBLE PRECISION / v_total_trades;
                ELSE
                    v_win_rate := 0.0;
                END IF;

                -- Calculate advanced metrics
                v_sortino_ratio := calculate_sortino_ratio(v_model_id, NEW.symbol, v_window);
                v_calmar_ratio := calculate_calmar_ratio(v_model_id, NEW.symbol, v_window);
                v_max_drawdown := calculate_max_drawdown(v_model_id, NEW.symbol, v_window);
                v_var_95 := calculate_var_95(v_model_id, NEW.symbol, v_window);
                v_cvar_95 := calculate_cvar_95(v_model_id, NEW.symbol, v_window);

                -- Upsert into model_performance_attribution
                INSERT INTO model_performance_attribution (
                    model_id, symbol, window_hours,
                    total_predictions, correct_predictions, accuracy,
                    total_pnl, total_trades, winning_trades,
                    sharpe_ratio, sortino_ratio, calmar_ratio,
                    max_drawdown, var_95, cvar_95, win_rate,
                    prediction_timestamp
                )
                VALUES (
                    v_model_id, NEW.symbol, v_window,
                    v_total_predictions, v_correct_predictions,
                    CASE WHEN v_total_predictions > 0 THEN v_correct_predictions::DOUBLE PRECISION / v_total_predictions ELSE 0.0 END,
                    v_total_pnl, v_total_trades, v_winning_trades,
                    v_sharpe_ratio, v_sortino_ratio, v_calmar_ratio,
                    v_max_drawdown, v_var_95, v_cvar_95, v_win_rate,
                    NOW()
                )
                ON CONFLICT (id, prediction_timestamp) DO NOTHING;

            END LOOP;
        END LOOP;

    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Recreate trigger (drop and recreate to use updated function)
DROP TRIGGER IF EXISTS trg_update_model_performance ON ensemble_predictions;
CREATE TRIGGER trg_update_model_performance
    AFTER UPDATE ON ensemble_predictions
    FOR EACH ROW
    WHEN (NEW.actual_outcome IS NOT NULL AND OLD.actual_outcome IS NULL)
    EXECUTE FUNCTION update_model_performance_metrics();

-- ================================================================================================
-- Grant permissions
-- ================================================================================================
GRANT EXECUTE ON FUNCTION calculate_sortino_ratio TO foxhunt;
GRANT EXECUTE ON FUNCTION calculate_max_drawdown TO foxhunt;
GRANT EXECUTE ON FUNCTION calculate_calmar_ratio TO foxhunt;
GRANT EXECUTE ON FUNCTION calculate_var_95 TO foxhunt;
GRANT EXECUTE ON FUNCTION calculate_cvar_95 TO foxhunt;
GRANT EXECUTE ON FUNCTION get_comprehensive_performance_metrics TO foxhunt;

-- ================================================================================================
-- END MIGRATION 044
-- ================================================================================================