foxhunt/migrations/043_add_outcome_tracking_fields.sql

-- ================================================================================================
-- Migration 043: Add Outcome Tracking Fields for Paper Trading
-- Adds actual_outcome, closed_at, and entry_price fields to ensemble_predictions
-- ================================================================================================

-- Add outcome tracking fields to ensemble_predictions table
ALTER TABLE ensemble_predictions
ADD COLUMN IF NOT EXISTS actual_outcome VARCHAR(10),  -- Actual trade outcome: WIN, LOSS, BREAKEVEN
ADD COLUMN IF NOT EXISTS closed_at TIMESTAMPTZ,       -- When the position was closed
ADD COLUMN IF NOT EXISTS entry_price BIGINT;          -- Entry price (in cents, same as executed_price)

-- Add check constraint for actual_outcome (conditional add to support re-runs)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'chk_actual_outcome'
        AND conrelid = 'ensemble_predictions'::regclass
    ) THEN
        ALTER TABLE ensemble_predictions
        ADD CONSTRAINT chk_actual_outcome
        CHECK (actual_outcome IS NULL OR actual_outcome IN ('WIN', 'LOSS', 'BREAKEVEN'));
    END IF;
END $$;

-- Add index for performance queries
CREATE INDEX IF NOT EXISTS idx_ensemble_predictions_outcome
ON ensemble_predictions (actual_outcome, closed_at DESC)
WHERE actual_outcome IS NOT NULL;

-- Add index for open positions (not yet closed)
CREATE INDEX IF NOT EXISTS idx_ensemble_predictions_open_positions
ON ensemble_predictions (symbol, prediction_timestamp DESC)
WHERE order_id IS NOT NULL AND closed_at IS NULL;

-- Add index for P&L queries with outcome
CREATE INDEX IF NOT EXISTS idx_ensemble_predictions_pnl_outcome
ON ensemble_predictions (symbol, actual_outcome, pnl DESC NULLS LAST)
WHERE pnl IS NOT NULL AND actual_outcome IS NOT NULL;

COMMENT ON COLUMN ensemble_predictions.actual_outcome IS 'Actual trade outcome after position close: WIN (pnl > 0), LOSS (pnl < 0), BREAKEVEN (pnl = 0)';
COMMENT ON COLUMN ensemble_predictions.closed_at IS 'Timestamp when position was closed and P&L realized';
COMMENT ON COLUMN ensemble_predictions.entry_price IS 'Actual entry price when order was filled (in cents, same unit as executed_price)';

-- ================================================================================================
-- Function: Update Model Performance Metrics (Trigger-Based)
-- Recalculates Sharpe ratio, win rate, and drawdown after each trade outcome
-- ================================================================================================
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;

COMMENT ON FUNCTION update_model_performance_metrics() IS 'Automatically recalculate model performance metrics when trade outcomes are recorded';

-- Create trigger to automatically update metrics
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();

COMMENT ON TRIGGER trg_update_model_performance ON ensemble_predictions IS 'Automatically recalculate Sharpe ratio, win rate after each trade outcome';

-- ================================================================================================
-- Function: Get Real-time Performance Metrics
-- Query function for TLI to display current model performance
-- ================================================================================================
CREATE OR REPLACE FUNCTION get_real_performance_metrics(
    p_symbol VARCHAR(20) DEFAULT NULL,
    p_window_hours INTEGER DEFAULT 24
)
RETURNS TABLE (
    model_id VARCHAR(50),
    accuracy DOUBLE PRECISION,
    sharpe_ratio DOUBLE PRECISION,
    win_rate DOUBLE PRECISION,
    total_pnl BIGINT,
    total_trades INTEGER,
    avg_confidence DOUBLE PRECISION
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        mpa.model_id,
        mpa.accuracy,
        mpa.sharpe_ratio,
        mpa.win_rate,
        mpa.total_pnl,
        mpa.total_trades,
        mpa.avg_confidence
    FROM model_performance_attribution mpa
    WHERE
        mpa.window_hours = p_window_hours
        AND mpa.prediction_timestamp >= NOW() - (p_window_hours || ' hours')::INTERVAL
        AND (p_symbol IS NULL OR mpa.symbol = p_symbol)
    ORDER BY mpa.sharpe_ratio DESC NULLS LAST;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_real_performance_metrics IS 'Get real-time model performance metrics for TLI display (no mock data)';

-- Grant permissions
GRANT EXECUTE ON FUNCTION update_model_performance_metrics TO foxhunt;
GRANT EXECUTE ON FUNCTION get_real_performance_metrics TO foxhunt;

-- ================================================================================================
-- END MIGRATION 043
-- ================================================================================================