//! Database Schema Types for ML Training Data
//!
//! This module defines Rust types that map to database tables for ML training data.
//! These types use sqlx::FromRow for automatic deserialization from PostgreSQL queries.
//!
//! ## Tables
//!
//! - **OrderBookSnapshot**: Level 2 order book data for microstructure analysis
//! - **TradeExecution**: Historical trades for volume and price discovery
//! - **MarketEvent**: External events for regime detection
//! - **MLFeatureCache**: Cached computed features (Phase 4)

use chrono::{DateTime, Utc};
use serde::{Deserialize, Serialize};
use sqlx::FromRow;

/// `Order` book snapshot from database
///
/// Maps to `order_book_snapshots` table. Contains Level 1 and Level 2 market data
/// for microstructure feature extraction.
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct OrderBookSnapshot {
    /// Database ID
    pub id: i64,

    /// Snapshot timestamp (UTC)
    pub timestamp: DateTime<Utc>,

    /// Trading symbol (e.g., "AAPL", "BTC-USD")
    pub symbol: String,

    /// Best bid price
    pub best_bid: rust_decimal::Decimal,

    /// Best ask price
    pub best_ask: rust_decimal::Decimal,

    /// Volume at best bid
    pub bid_volume: rust_decimal::Decimal,

    /// Volume at best ask
    pub ask_volume: rust_decimal::Decimal,

    /// Bid-ask spread in basis points
    pub spread_bps: i32,

    /// Mid price (average of best bid/ask)
    pub mid_price: rust_decimal::Decimal,

    /// `Order` book imbalance: (bid_vol - ask_vol) / (bid_vol + ask_vol)
    ///
    /// Range: -1.0 (ask-dominated) to 1.0 (bid-dominated)
    pub imbalance: f64,

    /// Level 2 bid data (top 5 levels) as `JSON`B
    pub bid_levels: Option<serde_json::Value>,

    /// Level 2 ask data (top 5 levels) as `JSON`B
    pub ask_levels: Option<serde_json::Value>,

    /// Exchange identifier
    pub exchange: Option<String>,

    /// Data quality score (0-100)
    pub data_quality: Option<i32>,

    /// Row creation timestamp
    pub created_at: DateTime<Utc>,
}

impl OrderBookSnapshot {
    /// Get best bid as f64 (for feature calculations)
    pub fn best_bid_f64(&self) -> f64 {
        self.best_bid.to_string().parse().unwrap_or(0.0)
    }

    /// Get best ask as f64 (for feature calculations)
    pub fn best_ask_f64(&self) -> f64 {
        self.best_ask.to_string().parse().unwrap_or(0.0)
    }

    /// Get mid price as f64
    pub fn mid_price_f64(&self) -> f64 {
        self.mid_price.to_string().parse().unwrap_or(0.0)
    }

    /// Get bid volume as f64
    pub fn bid_volume_f64(&self) -> f64 {
        self.bid_volume.to_string().parse().unwrap_or(0.0)
    }

    /// Get ask volume as f64
    pub fn ask_volume_f64(&self) -> f64 {
        self.ask_volume.to_string().parse().unwrap_or(0.0)
    }

    /// Check if data quality is acceptable (>= 80)
    pub fn is_high_quality(&self) -> bool {
        self.data_quality.unwrap_or(100) >= 80
    }

    /// Calculate total volume at best levels
    pub fn total_volume(&self) -> f64 {
        self.bid_volume_f64() + self.ask_volume_f64()
    }
}

/// `Trade` execution from database
///
/// Maps to `trade_executions` table. Contains historical trades for volume analysis
/// and price discovery patterns.
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct TradeExecution {
    /// Database ID
    pub id: i64,

    /// `Trade` timestamp (UTC)
    pub timestamp: DateTime<Utc>,

    /// Trading symbol
    pub symbol: String,

    /// Execution price
    pub price: rust_decimal::Decimal,

    /// `Trade` quantity
    pub quantity: rust_decimal::Decimal,

    /// `Trade` side: "buy" or "sell"
    pub side: String,

    /// Exchange trade ID
    pub trade_id: Option<String>,

    /// Exchange identifier
    pub exchange: Option<String>,

    /// Volume-weighted average price (VWAP)
    pub vwap: Option<rust_decimal::Decimal>,

    /// `Trade` intensity (trades per second)
    pub trade_intensity: Option<f64>,

    /// True if trade crossed spread (aggressive)
    pub aggressive_flag: Option<bool>,

    /// Data quality score (0-100)
    pub data_quality: Option<i32>,

    /// Row creation timestamp
    pub created_at: DateTime<Utc>,
}

impl TradeExecution {
    /// Get price as f64 (for feature calculations)
    pub fn price_f64(&self) -> f64 {
        self.price.to_string().parse().unwrap_or(0.0)
    }

    /// Get quantity as f64
    pub fn quantity_f64(&self) -> f64 {
        self.quantity.to_string().parse().unwrap_or(0.0)
    }

    /// Get VWAP as f64
    pub fn vwap_f64(&self) -> Option<f64> {
        self.vwap.as_ref().and_then(|v| v.to_string().parse().ok())
    }

    /// Check if trade is a buy
    pub fn is_buy(&self) -> bool {
        self.side.to_lowercase() == "buy"
    }

    /// Check if trade is a sell
    pub fn is_sell(&self) -> bool {
        self.side.to_lowercase() == "sell"
    }

    /// Check if data quality is acceptable
    pub fn is_high_quality(&self) -> bool {
        self.data_quality.unwrap_or(100) >= 80
    }

    /// Get signed quantity (positive for buy, negative for sell)
    pub fn signed_quantity(&self) -> f64 {
        let qty = self.quantity_f64();
        if self.is_buy() {
            qty
        } else {
            -qty
        }
    }
}

/// Market event from database
///
/// Maps to `market_events` table. Contains external events like news, earnings,
/// economic data for regime detection and sentiment analysis.
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct MarketEvent {
    /// Database ID
    pub id: i64,

    /// Event timestamp (UTC)
    pub timestamp: DateTime<Utc>,

    /// Event type: "news", "earnings", "economic_data", "halt", "circuit_breaker"
    pub event_type: String,

    /// Symbol (None for market-wide events)
    pub symbol: Option<String>,

    /// Event title
    pub title: Option<String>,

    /// Event description
    pub description: Option<String>,

    /// Data source
    pub source: Option<String>,

    /// Impact score (0.0-1.0 magnitude)
    pub impact_score: Option<f64>,

    /// Sentiment (-1.0 to 1.0)
    pub sentiment: Option<f64>,

    /// Additional structured data as `JSON`B
    pub metadata: serde_json::Value,

    /// Row creation timestamp
    pub created_at: DateTime<Utc>,
}

impl MarketEvent {
    /// Check if event is high impact (>= 0.7)
    pub fn is_high_impact(&self) -> bool {
        self.impact_score.unwrap_or(0.0) >= 0.7
    }

    /// Check if event is positive sentiment (>= 0.3)
    pub fn is_positive(&self) -> bool {
        self.sentiment.unwrap_or(0.0) >= 0.3
    }

    /// Check if event is negative sentiment (<= -0.3)
    pub fn is_negative(&self) -> bool {
        self.sentiment.unwrap_or(0.0) <= -0.3
    }

    /// Check if event is symbol-specific
    pub fn is_symbol_specific(&self) -> bool {
        self.symbol.is_some()
    }

    /// Check if event is market-wide
    pub fn is_market_wide(&self) -> bool {
        self.symbol.is_none()
    }
}

/// Cached ML features from database (Phase 4)
///
/// Maps to `ml_feature_cache` table. Contains pre-computed features to accelerate
/// training data loading.
#[derive(Debug, Clone, Serialize, Deserialize, FromRow)]
pub struct MLFeatureCache {
    /// Database ID
    pub id: i64,

    /// Feature timestamp (UTC)
    pub timestamp: DateTime<Utc>,

    /// Trading symbol
    pub symbol: String,

    /// Feature version for cache invalidation
    pub feature_version: String,

    /// Technical indicators as `JSON`B
    pub technical_indicators: serde_json::Value,

    /// Microstructure features as `JSON`B
    pub microstructure_features: serde_json::Value,

    /// Risk metrics as `JSON`B
    pub risk_metrics: serde_json::Value,

    /// Reference to order book snapshot
    pub order_book_snapshot_id: Option<i64>,

    /// Row creation timestamp
    pub created_at: DateTime<Utc>,
}

impl MLFeatureCache {
    /// Parse technical indicators into HashMap
    pub fn parse_technical_indicators(&self) -> Option<std::collections::HashMap<String, f64>> {
        serde_json::from_value(self.technical_indicators.clone()).ok()
    }

    /// Parse microstructure features into HashMap
    pub fn parse_microstructure_features(&self) -> Option<std::collections::HashMap<String, f64>> {
        serde_json::from_value(self.microstructure_features.clone()).ok()
    }

    /// Parse risk metrics into HashMap
    pub fn parse_risk_metrics(&self) -> Option<std::collections::HashMap<String, f64>> {
        serde_json::from_value(self.risk_metrics.clone()).ok()
    }
}

#[cfg(test)]
#[allow(clippy::unwrap_used, clippy::expect_used)]
mod tests {
    use super::*;

    #[test]
    fn test_order_book_snapshot_conversions() {
        let snapshot = OrderBookSnapshot {
            id: 1,
            timestamp: Utc::now(),
            symbol: "AAPL".to_string(),
            best_bid: rust_decimal::Decimal::new(15025, 2), // 150.25
            best_ask: rust_decimal::Decimal::new(15030, 2), // 150.30
            bid_volume: rust_decimal::Decimal::new(1000, 0),
            ask_volume: rust_decimal::Decimal::new(800, 0),
            spread_bps: 3,
            mid_price: rust_decimal::Decimal::new(150275, 3), // 150.275
            imbalance: 0.111,
            bid_levels: None,
            ask_levels: None,
            exchange: Some("NASDAQ".to_string()),
            data_quality: Some(95),
            created_at: Utc::now(),
        };

        assert_eq!(snapshot.best_bid_f64(), 150.25);
        assert_eq!(snapshot.best_ask_f64(), 150.30);
        assert!(snapshot.is_high_quality());
        assert_eq!(snapshot.total_volume(), 1800.0);
    }

    #[test]
    fn test_trade_execution_side_detection() {
        let trade = TradeExecution {
            id: 1,
            timestamp: Utc::now(),
            symbol: "AAPL".to_string(),
            price: rust_decimal::Decimal::new(15025, 2),
            quantity: rust_decimal::Decimal::new(100, 0),
            side: "buy".to_string(),
            trade_id: Some("12345".to_string()),
            exchange: Some("NASDAQ".to_string()),
            vwap: None,
            trade_intensity: Some(2.5),
            aggressive_flag: Some(true),
            data_quality: Some(100),
            created_at: Utc::now(),
        };

        assert!(trade.is_buy());
        assert!(!trade.is_sell());
        assert_eq!(trade.signed_quantity(), 100.0);
    }

    #[test]
    fn test_market_event_sentiment() {
        let event = MarketEvent {
            id: 1,
            timestamp: Utc::now(),
            event_type: "news".to_string(),
            symbol: Some("AAPL".to_string()),
            title: Some("Earnings Beat".to_string()),
            description: None,
            source: Some("Bloomberg".to_string()),
            impact_score: Some(0.8),
            sentiment: Some(0.7),
            metadata: serde_json::json!({}),
            created_at: Utc::now(),
        };

        assert!(event.is_high_impact());
        assert!(event.is_positive());
        assert!(!event.is_negative());
        assert!(event.is_symbol_specific());
    }
}
