//! ML Performance Metrics Storage and Analysis
//!
//! This module provides tracking and analysis of ML model predictions
//! and their outcomes for performance evaluation.

use chrono::{DateTime, Utc};
use common::error::ErrorCategory;
use common::CommonError;
use rust_decimal::Decimal;
use serde::{Deserialize, Serialize};
use sqlx::PgPool;

/// ML Prediction record for tracking
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct MLPrediction {
    /// Model name (e.g., "DQN", "PPO", "MAMBA2", "TFT")
    pub model_name: String,
    /// Feature values used for prediction
    pub features: Vec<f32>,
    /// Predicted action: 0=Buy, 1=Sell, 2=Hold
    pub predicted_action: i16,
    /// Model confidence score (0.0-1.0)
    pub confidence: f32,
    /// Trading symbol
    pub symbol: String,
    /// Prediction timestamp
    pub timestamp: DateTime<Utc>,
}

/// Prediction outcome after actual result is known
#[derive(Debug, Clone)]
pub struct PredictionOutcome {
    /// ID of the prediction record
    pub prediction_id: i64,
    /// Actual action taken: 0=Buy, 1=Sell, 2=Hold
    pub actual_action: i16,
    /// Profit/Loss from this prediction
    pub pnl: Decimal,
    /// Outcome timestamp
    pub timestamp: DateTime<Utc>,
}

/// Accuracy statistics for a model
#[derive(Debug, Clone)]
pub struct AccuracyStats {
    /// Total number of predictions
    pub total_predictions: i64,
    /// Number of correct predictions
    pub correct_predictions: i64,
    /// Accuracy ratio (0.0-1.0)
    pub accuracy: f64,
}

/// Comprehensive performance metrics for a model
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct ComprehensiveMetrics {
    pub model_id: String,
    pub total_predictions: i32,
    pub win_rate: Option<f64>,
    pub sharpe_ratio: Option<f64>,
    pub sortino_ratio: Option<f64>,
    pub calmar_ratio: Option<f64>,
    pub max_drawdown: Option<f64>,
    pub var_95: Option<f64>,
    pub cvar_95: Option<f64>,
    pub avg_pnl: Option<f64>,
    pub total_pnl: Option<i64>,
    pub total_trades: i32,
    pub avg_confidence: Option<f64>,
}

/// ML Metrics Store for PostgreSQL persistence
pub struct MLMetricsStore {
    pool: PgPool,
}

impl MLMetricsStore {
    /// Create a new ML metrics store
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    /// Insert a new ML prediction record
    ///
    /// # Arguments
    /// * `prediction` - ML prediction to store
    ///
    /// # Returns
    /// * Prediction ID on success
    pub async fn insert_prediction(&self, prediction: &MLPrediction) -> Result<i64, CommonError> {
        let features_json = serde_json::to_value(&prediction.features)
            .map_err(|e| CommonError::internal(format!("Failed to serialize features: {}", e)))?;

        let result = sqlx::query!(
            r#"
            INSERT INTO ml_predictions (model_name, features, predicted_action, confidence, symbol, prediction_timestamp)
            VALUES ($1, $2, $3, $4, $5, $6)
            RETURNING id
            "#,
            prediction.model_name,
            features_json,
            prediction.predicted_action,
            prediction.confidence,
            prediction.symbol,
            prediction.timestamp,
        )
        .fetch_one(&self.pool)
        .await
        .map_err(|e| {
            CommonError::service(
                ErrorCategory::Database,
                format!("Failed to insert prediction: {}", e),
            )
        })?;

        Ok(result.id as i64)
    }

    /// Record the outcome of a prediction
    ///
    /// # Arguments
    /// * `outcome` - Prediction outcome to record
    pub async fn record_outcome(&self, outcome: &PredictionOutcome) -> Result<(), CommonError> {
        // Use rust_decimal::Decimal with SQLX (cast to sqlx::types::Decimal alias)
        let pnl_value: sqlx::types::Decimal = outcome.pnl;

        sqlx::query!(
            r#"
            UPDATE ml_predictions
            SET actual_action = $1, pnl = $2, outcome_recorded_at = $3
            WHERE id = $4
            "#,
            outcome.actual_action,
            pnl_value as sqlx::types::Decimal,
            outcome.timestamp,
            outcome.prediction_id as i32,
        )
        .execute(&self.pool)
        .await
        .map_err(|e| {
            CommonError::service(
                ErrorCategory::Database,
                format!("Failed to record outcome: {}", e),
            )
        })?;

        // Refresh materialized view for analytics
        self.refresh_performance_view().await?;

        Ok(())
    }

    /// Get accuracy statistics for a specific model
    ///
    /// # Arguments
    /// * `model_name` - Name of the model
    ///
    /// # Returns
    /// * Accuracy statistics
    pub async fn get_accuracy_stats(&self, model_name: &str) -> Result<AccuracyStats, CommonError> {
        let result = sqlx::query!(
            r#"
            SELECT 
                COUNT(*) as "total_predictions!",
                COUNT(actual_action) as "predictions_with_outcomes!: i64",
                SUM(CASE WHEN predicted_action = actual_action THEN 1 ELSE 0 END) as "correct_predictions!: i64"
            FROM ml_predictions
            WHERE model_name = $1 AND outcome_recorded_at IS NOT NULL
            "#,
            model_name,
        )
        .fetch_one(&self.pool)
        .await
        .map_err(|e| {
            CommonError::service(
                ErrorCategory::Database,
                format!("Failed to get accuracy stats: {}", e),
            )
        })?;

        let total = result.total_predictions;
        let correct = result.correct_predictions;
        let accuracy = if total > 0 {
            correct as f64 / total as f64
        } else {
            0.0
        };

        Ok(AccuracyStats {
            total_predictions: total,
            correct_predictions: correct,
            accuracy,
        })
    }

    /// Calculate Sharpe ratio for a model
    ///
    /// # Arguments
    /// * `model_name` - Name of the model
    ///
    /// # Returns
    /// * Annualized Sharpe ratio
    pub async fn calculate_sharpe_ratio(&self, model_name: &str) -> Result<f64, CommonError> {
        let result = sqlx::query!(
            r#"
            SELECT 
                AVG(pnl) as "avg_pnl",
                STDDEV(pnl) as "stddev_pnl"
            FROM ml_predictions
            WHERE model_name = $1 AND outcome_recorded_at IS NOT NULL
            "#,
            model_name,
        )
        .fetch_one(&self.pool)
        .await
        .map_err(|e| {
            CommonError::service(
                ErrorCategory::Database,
                format!("Failed to calculate Sharpe: {}", e),
            )
        })?;

        use rust_decimal::prelude::ToPrimitive;

        let avg_pnl = result.avg_pnl.and_then(|d| d.to_f64()).unwrap_or(0.0);
        let stddev_pnl = result.stddev_pnl.and_then(|d| d.to_f64()).unwrap_or(1.0);

        if stddev_pnl == 0.0 {
            return Ok(0.0);
        }

        // Annualized Sharpe ratio (assuming 252 trading days)
        let sharpe = (avg_pnl / stddev_pnl) * (252.0_f64).sqrt();

        Ok(sharpe)
    }

    /// Compare accuracy across all models
    ///
    /// # Returns
    /// * Vector of (model_name, accuracy) tuples sorted by accuracy
    pub async fn compare_model_accuracy(&self) -> Result<Vec<(String, f64)>, CommonError> {
        let results = sqlx::query!(
            r#"
            SELECT model_name, accuracy
            FROM ml_model_performance
            ORDER BY accuracy DESC
            "#,
        )
        .fetch_all(&self.pool)
        .await
        .map_err(|e| {
            CommonError::service(
                ErrorCategory::Database,
                format!("Failed to compare models: {}", e),
            )
        })?;

        let comparison: Vec<(String, f64)> = results
            .into_iter()
            .filter_map(|r| r.model_name.map(|name| (name, r.accuracy.unwrap_or(0.0))))
            .collect();

        Ok(comparison)
    }

    /// Refresh the materialized view for performance analytics
    async fn refresh_performance_view(&self) -> Result<(), CommonError> {
        sqlx::query!("SELECT refresh_ml_model_performance()")
            .execute(&self.pool)
            .await
            .map_err(|e| {
                CommonError::service(
                    ErrorCategory::Database,
                    format!("Failed to refresh view: {}", e),
                )
            })?;

        Ok(())
    }

    /// Get comprehensive performance metrics for all models
    ///
    /// # Arguments
    /// * `symbol` - Optional symbol filter (None = all symbols)
    /// * `window_hours` - Time window in hours (1, 24, or 168)
    ///
    /// # Returns
    /// * Vector of comprehensive metrics for each model
    pub async fn get_comprehensive_metrics(
        &self,
        symbol: Option<&str>,
        window_hours: i32,
    ) -> Result<Vec<ComprehensiveMetrics>, CommonError> {
        let metrics = sqlx::query_as::<_, ComprehensiveMetrics>(
            r#"
            SELECT * FROM get_comprehensive_performance_metrics($1, $2)
            "#,
        )
        .bind(symbol)
        .bind(window_hours)
        .fetch_all(&self.pool)
        .await
        .map_err(|e| {
            CommonError::service(
                ErrorCategory::Database,
                format!("Failed to get comprehensive metrics: {}", e),
            )
        })?;

        Ok(metrics)
    }

    /// Get real Sharpe ratio (replaces mock calculation)
    ///
    /// # Arguments
    /// * `model_name` - Name of the model
    /// * `symbol` - Optional symbol filter
    /// * `window_hours` - Time window in hours
    ///
    /// # Returns
    /// * Real Sharpe ratio from database
    pub async fn get_real_sharpe_ratio(
        &self,
        model_name: &str,
        symbol: Option<&str>,
        window_hours: i32,
    ) -> Result<f64, CommonError> {
        let metrics = self.get_comprehensive_metrics(symbol, window_hours).await?;

        let sharpe = metrics
            .iter()
            .find(|m| m.model_id == model_name)
            .and_then(|m| m.sharpe_ratio)
            .unwrap_or(0.0);

        Ok(sharpe)
    }

    /// Get performance summary for display in TLI
    ///
    /// # Arguments
    /// * `model_filter` - Optional model name filter (None = all models)
    /// * `symbol` - Optional symbol filter
    /// * `window_hours` - Time window in hours (default 24)
    ///
    /// # Returns
    /// * Vector of (model_name, metrics) tuples for TLI display
    pub async fn get_performance_summary(
        &self,
        model_filter: Option<&str>,
        symbol: Option<&str>,
        window_hours: i32,
    ) -> Result<Vec<ComprehensiveMetrics>, CommonError> {
        let mut metrics = self.get_comprehensive_metrics(symbol, window_hours).await?;

        // Apply model filter if specified
        if let Some(filter) = model_filter {
            metrics.retain(|m| m.model_id == filter);
        }

        Ok(metrics)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_accuracy_stats_creation() {
        let stats = AccuracyStats {
            total_predictions: 100,
            correct_predictions: 75,
            accuracy: 0.75,
        };

        assert_eq!(stats.total_predictions, 100);
        assert_eq!(stats.correct_predictions, 75);
        assert!((stats.accuracy - 0.75).abs() < 0.01);
    }

    #[test]
    fn test_ml_prediction_serialization() {
        let prediction = MLPrediction {
            model_name: "DQN".to_string(),
            features: vec![0.1, 0.2, 0.3],
            predicted_action: 0,
            confidence: 0.85,
            symbol: "ES.FUT".to_string(),
            timestamp: Utc::now(),
        };

        let json = serde_json::to_string(&prediction).expect("Failed to serialize");
        assert!(json.contains("DQN"));
        assert!(json.contains("ES.FUT"));
    }

    #[test]
    fn test_comprehensive_metrics_structure() {
        let metrics = ComprehensiveMetrics {
            model_id: "DQN".to_string(),
            total_predictions: 150,
            win_rate: Some(0.725),
            sharpe_ratio: Some(1.82),
            sortino_ratio: Some(2.10),
            calmar_ratio: Some(3.50),
            max_drawdown: Some(0.031),
            var_95: Some(-0.015),
            cvar_95: Some(-0.025),
            avg_pnl: Some(23.5),
            total_pnl: Some(3525),
            total_trades: 150,
            avg_confidence: Some(0.78),
        };

        assert_eq!(metrics.model_id, "DQN");
        assert_eq!(metrics.total_predictions, 150);
        assert!(metrics.sharpe_ratio.unwrap() > 1.5);
        assert!(metrics.win_rate.unwrap() > 0.7);
        assert!(metrics.max_drawdown.unwrap() < 0.05);
    }
}
