-- Migration 007: ML v2 — Add outcome tracking & ML metadata columns to trading_signals
-- Purpose: Support adaptive learning, direction classifier tracking, regime-aware signals
-- Date: 2026-03-01
-- Database: MySQL

-- Add new columns to trading_signals
-- MySQL doesn't support IF NOT EXISTS on ALTER TABLE ADD COLUMN,
-- so we use a stored procedure to check first.

DELIMITER //
CREATE PROCEDURE migrate_007_ml_v2()
BEGIN
    -- actual_outcome ENUM
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'trading_signals' AND COLUMN_NAME = 'actual_outcome'
    ) THEN
        ALTER TABLE trading_signals ADD COLUMN actual_outcome ENUM('win','loss','breakeven','pending') DEFAULT 'pending';
    END IF;

    -- actual_return_pct
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'trading_signals' AND COLUMN_NAME = 'actual_return_pct'
    ) THEN
        ALTER TABLE trading_signals ADD COLUMN actual_return_pct DECIMAL(8,4) DEFAULT NULL;
    END IF;

    -- was_correct
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'trading_signals' AND COLUMN_NAME = 'was_correct'
    ) THEN
        ALTER TABLE trading_signals ADD COLUMN was_correct TINYINT(1) DEFAULT NULL;
    END IF;

    -- direction_probability
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'trading_signals' AND COLUMN_NAME = 'direction_probability'
    ) THEN
        ALTER TABLE trading_signals ADD COLUMN direction_probability DECIMAL(5,4) DEFAULT NULL;
    END IF;

    -- regime
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'trading_signals' AND COLUMN_NAME = 'regime'
    ) THEN
        ALTER TABLE trading_signals ADD COLUMN regime VARCHAR(20) DEFAULT NULL;
    END IF;

    -- mtf_confirmed
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'trading_signals' AND COLUMN_NAME = 'mtf_confirmed'
    ) THEN
        ALTER TABLE trading_signals ADD COLUMN mtf_confirmed TINYINT(1) DEFAULT 0;
    END IF;
END //
DELIMITER ;

CALL migrate_007_ml_v2();
DROP PROCEDURE IF EXISTS migrate_007_ml_v2;

-- Indexes for performance
CREATE INDEX idx_signal_outcome ON trading_signals (coin_id, actual_outcome, actual_return_pct);
CREATE INDEX idx_signal_regime ON trading_signals (regime, signal_type, created_at);