-- WARNING: This migration is for staging environments only. Do not apply to production.
--
-- =============================================================================
-- STAGING ML DEPLOYMENT TABLES
-- =============================================================================
-- Migration: 999_staging_ml_deployment.sql
-- Purpose: Create tables for ML model deployment and paper trading in staging
-- Date: 2025-10-18
-- Environment: STAGING ONLY
-- =============================================================================

-- Table: ml_models
-- Purpose: Track deployed ML models (DQN, PPO, TFT, MAMBA-2)
CREATE TABLE IF NOT EXISTS ml_models (
    id SERIAL PRIMARY KEY,
    model_id VARCHAR(50) NOT NULL UNIQUE,
    model_type VARCHAR(20) NOT NULL, -- DQN, PPO, TFT, MAMBA2, TLOB
    version VARCHAR(20) NOT NULL,
    checkpoint_path TEXT NOT NULL,
    deployment_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    status VARCHAR(20) NOT NULL DEFAULT 'active', -- active, inactive, deprecated
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ml_models_status ON ml_models(status);
CREATE INDEX IF NOT EXISTS idx_ml_models_type ON ml_models(model_type);
CREATE INDEX IF NOT EXISTS idx_ml_models_deployment_date ON ml_models(deployment_date DESC);

-- Table: paper_trading_config
-- Purpose: Configuration for paper trading with ML models
CREATE TABLE IF NOT EXISTS paper_trading_config (
    id SERIAL PRIMARY KEY,
    config_name VARCHAR(100) NOT NULL UNIQUE,
    enabled BOOLEAN NOT NULL DEFAULT true,
    initial_capital_usd BIGINT NOT NULL DEFAULT 100000,
    max_position_size_usd BIGINT NOT NULL DEFAULT 10000,
    max_positions INTEGER NOT NULL DEFAULT 5,
    slippage_bps INTEGER NOT NULL DEFAULT 2, -- basis points (0.02%)
    commission_per_trade NUMERIC(10, 2) NOT NULL DEFAULT 1.00,
    symbols TEXT[] NOT NULL DEFAULT ARRAY['ES.FUT', 'NQ.FUT'],
    ml_model_id VARCHAR(50), -- Reference to ml_models.model_id
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    FOREIGN KEY (ml_model_id) REFERENCES ml_models(model_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_paper_trading_config_enabled ON paper_trading_config(enabled);
CREATE INDEX IF NOT EXISTS idx_paper_trading_config_ml_model ON paper_trading_config(ml_model_id);

-- Table: paper_trading_orders (if not exists)
-- Purpose: Track paper trading orders generated from ML predictions
CREATE TABLE IF NOT EXISTS paper_trading_orders (
    id SERIAL PRIMARY KEY,
    order_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    prediction_id UUID, -- Reference to ensemble_predictions.id
    config_id INTEGER, -- Reference to paper_trading_config.id
    symbol VARCHAR(20) NOT NULL,
    action VARCHAR(10) NOT NULL, -- BUY, SELL, HOLD
    quantity INTEGER NOT NULL,
    order_price BIGINT, -- Price in cents
    executed_price BIGINT, -- Actual execution price with slippage
    commission NUMERIC(10, 2),
    slippage_bps INTEGER,
    pnl BIGINT, -- Profit/Loss in cents
    order_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    execution_timestamp TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, filled, cancelled
    metadata JSONB,
    FOREIGN KEY (config_id) REFERENCES paper_trading_config(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_paper_trading_orders_symbol ON paper_trading_orders(symbol);
CREATE INDEX IF NOT EXISTS idx_paper_trading_orders_status ON paper_trading_orders(status);
CREATE INDEX IF NOT EXISTS idx_paper_trading_orders_timestamp ON paper_trading_orders(order_timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_paper_trading_orders_prediction ON paper_trading_orders(prediction_id);

-- Table: paper_trading_positions (if not exists)
-- Purpose: Track current paper trading positions
CREATE TABLE IF NOT EXISTS paper_trading_positions (
    id SERIAL PRIMARY KEY,
    position_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    config_id INTEGER NOT NULL, -- Reference to paper_trading_config.id
    symbol VARCHAR(20) NOT NULL,
    quantity INTEGER NOT NULL,
    entry_price BIGINT NOT NULL, -- Price in cents
    current_price BIGINT, -- Current market price
    unrealized_pnl BIGINT, -- Unrealized P&L in cents
    realized_pnl BIGINT DEFAULT 0, -- Realized P&L in cents
    opened_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    closed_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) NOT NULL DEFAULT 'open', -- open, closed
    FOREIGN KEY (config_id) REFERENCES paper_trading_config(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_paper_trading_positions_symbol ON paper_trading_positions(symbol);
CREATE INDEX IF NOT EXISTS idx_paper_trading_positions_status ON paper_trading_positions(status);
CREATE INDEX IF NOT EXISTS idx_paper_trading_positions_config ON paper_trading_positions(config_id);

-- Table: paper_trading_performance (if not exists)
-- Purpose: Daily performance metrics for paper trading
CREATE TABLE IF NOT EXISTS paper_trading_performance (
    id SERIAL PRIMARY KEY,
    config_id INTEGER NOT NULL, -- Reference to paper_trading_config.id
    trading_date DATE NOT NULL,
    total_pnl BIGINT NOT NULL DEFAULT 0, -- Total P&L in cents
    winning_trades INTEGER NOT NULL DEFAULT 0,
    losing_trades INTEGER NOT NULL DEFAULT 0,
    total_trades INTEGER NOT NULL DEFAULT 0,
    win_rate NUMERIC(5, 4), -- Win rate as decimal (0.55 = 55%)
    sharpe_ratio NUMERIC(10, 6),
    max_drawdown NUMERIC(10, 6),
    avg_trade_pnl BIGINT,
    total_commission NUMERIC(10, 2),
    total_slippage_bps INTEGER,
    ending_capital BIGINT,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    UNIQUE (config_id, trading_date),
    FOREIGN KEY (config_id) REFERENCES paper_trading_config(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_paper_trading_performance_date ON paper_trading_performance(trading_date DESC);
CREATE INDEX IF NOT EXISTS idx_paper_trading_performance_config ON paper_trading_performance(config_id);

-- Table: model_inference_metrics (if not exists)
-- Purpose: Track ML model inference performance
CREATE TABLE IF NOT EXISTS model_inference_metrics (
    id SERIAL PRIMARY KEY,
    model_id VARCHAR(50) NOT NULL,
    inference_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    inference_latency_us INTEGER NOT NULL, -- Latency in microseconds
    batch_size INTEGER NOT NULL DEFAULT 1,
    gpu_memory_mb INTEGER,
    cpu_usage_percent NUMERIC(5, 2),
    prediction_confidence NUMERIC(5, 4),
    success BOOLEAN NOT NULL DEFAULT true,
    error_message TEXT,
    FOREIGN KEY (model_id) REFERENCES ml_models(model_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_model_inference_metrics_model ON model_inference_metrics(model_id);
CREATE INDEX IF NOT EXISTS idx_model_inference_metrics_timestamp ON model_inference_metrics(inference_timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_model_inference_metrics_latency ON model_inference_metrics(inference_latency_us);

-- Add comments for documentation
COMMENT ON TABLE ml_models IS 'Registry of deployed ML models for production trading';
COMMENT ON TABLE paper_trading_config IS 'Configuration for paper trading with ML model integration';
COMMENT ON TABLE paper_trading_orders IS 'Historical record of all paper trading orders';
COMMENT ON TABLE paper_trading_positions IS 'Current and historical paper trading positions';
COMMENT ON TABLE paper_trading_performance IS 'Daily performance metrics for paper trading accounts';
COMMENT ON TABLE model_inference_metrics IS 'Performance metrics for ML model inference operations';

-- Grant permissions (if needed)
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO foxhunt_staging;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO foxhunt_staging;
