foxhunt/migrations/044_advanced_performance_metrics.down.sql

-- ================================================================================================
-- Migration 044 DOWN: Rollback Advanced Performance Metrics
-- Removes Sortino, Calmar, VaR, CVaR calculations
-- ================================================================================================

-- Revoke permissions
REVOKE EXECUTE ON FUNCTION get_comprehensive_performance_metrics FROM foxhunt;
REVOKE EXECUTE ON FUNCTION calculate_cvar_95 FROM foxhunt;
REVOKE EXECUTE ON FUNCTION calculate_var_95 FROM foxhunt;
REVOKE EXECUTE ON FUNCTION calculate_calmar_ratio FROM foxhunt;
REVOKE EXECUTE ON FUNCTION calculate_max_drawdown FROM foxhunt;
REVOKE EXECUTE ON FUNCTION calculate_sortino_ratio FROM foxhunt;

-- Drop trigger
DROP TRIGGER IF EXISTS trg_update_model_performance ON ensemble_predictions;

-- Drop updated function
DROP FUNCTION IF EXISTS update_model_performance_metrics();

-- Drop comprehensive metrics function
DROP FUNCTION IF EXISTS get_comprehensive_performance_metrics(VARCHAR, INTEGER);

-- Drop advanced metric calculation functions
DROP FUNCTION IF EXISTS calculate_cvar_95(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_var_95(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_calmar_ratio(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_max_drawdown(VARCHAR, VARCHAR, INTEGER);
DROP FUNCTION IF EXISTS calculate_sortino_ratio(VARCHAR, VARCHAR, INTEGER, DOUBLE PRECISION);

-- Remove new columns from model_performance_attribution
ALTER TABLE model_performance_attribution
DROP COLUMN IF EXISTS calmar_ratio,
DROP COLUMN IF EXISTS cvar_95,
DROP COLUMN IF EXISTS var_95;

-- Recreate original update_model_performance_metrics function (from migration 043)
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_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 metrics for this model and window
                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;

                -- 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, 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_win_rate,
                    NOW()
                )
                ON CONFLICT (id, prediction_timestamp) DO NOTHING;

            END LOOP;
        END LOOP;

    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Recreate trigger
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 for restored function
GRANT EXECUTE ON FUNCTION update_model_performance_metrics TO foxhunt;

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