use chrono::NaiveDate;
use rusqlite::{Connection, params};
use std::collections::HashMap;

#[derive(Debug, Clone)]
pub struct SpendingTrend {
    pub category: String,
    pub monthly_data: HashMap<String, f64>, // "YYYY-MM" -> amount
    pub total: f64,
    pub average_monthly: f64,
    pub trend_direction: TrendDirection,
}

#[derive(Debug, Clone)]
pub struct NetWorthPoint {
    pub date: String, // "YYYY-MM"
    pub income: f64,
    pub expenses: f64,
    pub net: f64,
    pub cumulative_net: f64,
}

#[derive(Debug, Clone, PartialEq)]
pub enum TrendDirection {
    Increasing,
    Decreasing,
    Stable,
}

#[derive(Debug, Clone)]
pub struct CategoryInsights {
    pub category: String,
    pub total_spent: f64,
    pub transaction_count: usize,
    pub average_transaction: f64,
    pub monthly_average: f64,
    pub highest_month: (String, f64),
    pub lowest_month: (String, f64),
    pub frequency_days: f64, // average days between transactions
}

pub fn get_spending_trends(categories: &[&str], months: usize) -> anyhow::Result<Vec<SpendingTrend>> {
    let conn = Connection::open("spendr.db")?;
    let mut trends = Vec::new();
    
    // Calculate date range for the last N months
    let today = chrono::Utc::now().date_naive();
    let start_date = today - chrono::Duration::days((months * 30) as i64);
    
    for &category in categories {
        let mut stmt = conn.prepare(
            "SELECT strftime('%Y-%m', date) as month, SUM(ABS(amount)) as total
             FROM transactions 
             WHERE amount < 0 AND category = ? AND date >= ?
             GROUP BY month 
             ORDER BY month"
        )?;
        
        let rows = stmt.query_map(params![category, start_date.format("%Y-%m-%d").to_string()], |row| {
            Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
        })?;
        
        let mut monthly_data = HashMap::new();
        let mut total = 0.0;
        
        for row in rows {
            let (month, amount) = row?;
            monthly_data.insert(month, amount);
            total += amount;
        }
        
        let average_monthly = if monthly_data.is_empty() { 0.0 } else { total / monthly_data.len() as f64 };
        let trend_direction = calculate_trend_direction(&monthly_data);
        
        trends.push(SpendingTrend {
            category: category.to_string(),
            monthly_data,
            total,
            average_monthly,
            trend_direction,
        });
    }
    
    Ok(trends)
}

pub fn get_net_worth_over_time(months: usize) -> anyhow::Result<Vec<NetWorthPoint>> {
    let conn = Connection::open("spendr.db")?;
    let mut net_worth_points = Vec::new();
    
    // Calculate date range for the last N months
    let today = chrono::Utc::now().date_naive();
    let start_date = today - chrono::Duration::days((months * 30) as i64);
    
    let mut stmt = conn.prepare(
        "SELECT strftime('%Y-%m', date) as month,
                SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income,
                SUM(CASE WHEN amount < 0 THEN ABS(amount) ELSE 0 END) as expenses
         FROM transactions 
         WHERE date >= ?
         GROUP BY month 
         ORDER BY month"
    )?;
    
    let rows = stmt.query_map(params![start_date.format("%Y-%m-%d").to_string()], |row| {
        Ok((
            row.get::<_, String>(0)?,
            row.get::<_, f64>(1)?,
            row.get::<_, f64>(2)?,
        ))
    })?;
    
    let mut cumulative_net = 0.0;
    
    for row in rows {
        let (month, income, expenses) = row?;
        let net = income - expenses;
        cumulative_net += net;
        
        net_worth_points.push(NetWorthPoint {
            date: month,
            income,
            expenses,
            net,
            cumulative_net,
        });
    }
    
    Ok(net_worth_points)
}

pub fn get_category_insights(category: &str, months: usize) -> anyhow::Result<CategoryInsights> {
    let conn = Connection::open("spendr.db")?;
    
    // Calculate date range for the last N months
    let today = chrono::Utc::now().date_naive();
    let start_date = today - chrono::Duration::days((months * 30) as i64);
    
    // Get basic stats
    let mut stmt = conn.prepare(
        "SELECT COUNT(*) as count, 
                SUM(ABS(amount)) as total, 
                AVG(ABS(amount)) as avg_transaction
         FROM transactions 
         WHERE amount < 0 AND category = ? AND date >= ?"
    )?;
    
    let (transaction_count, total_spent, average_transaction) = stmt.query_row(
        params![category, start_date.format("%Y-%m-%d").to_string()], 
        |row| {
            Ok((
                row.get::<_, usize>(0)?,
                row.get::<_, f64>(1)?,
                row.get::<_, f64>(2)?,
            ))
        }
    )?;
    
    // Get monthly breakdown
    let mut stmt = conn.prepare(
        "SELECT strftime('%Y-%m', date) as month, SUM(ABS(amount)) as total
         FROM transactions 
         WHERE amount < 0 AND category = ? AND date >= ?
         GROUP BY month 
         ORDER BY total DESC"
    )?;
    
    let rows = stmt.query_map(params![category, start_date.format("%Y-%m-%d").to_string()], |row| {
        Ok((row.get::<_, String>(0)?, row.get::<_, f64>(1)?))
    })?;
    
    let mut monthly_totals = Vec::new();
    for row in rows {
        monthly_totals.push(row?);
    }
    
    let highest_month = monthly_totals.first().cloned().unwrap_or(("No data".to_string(), 0.0));
    let lowest_month = monthly_totals.last().cloned().unwrap_or(("No data".to_string(), 0.0));
    let monthly_average = if monthly_totals.is_empty() { 0.0 } else { 
        total_spent / monthly_totals.len() as f64 
    };
    
    // Calculate frequency (average days between transactions)
    let mut freq_stmt = conn.prepare(
        "SELECT date FROM transactions 
         WHERE amount < 0 AND category = ? AND date >= ?
         ORDER BY date"
    )?;
    
    let date_rows = freq_stmt.query_map(params![category, start_date.format("%Y-%m-%d").to_string()], |row| {
        let date_str: String = row.get(0)?;
        Ok(NaiveDate::parse_from_str(&date_str, "%Y-%m-%d").unwrap())
    })?;
    
    let mut dates = Vec::new();
    for date in date_rows {
        dates.push(date?);
    }
    
    let frequency_days = if dates.len() > 1 {
        let total_days = dates.last().unwrap().signed_duration_since(*dates.first().unwrap()).num_days() as f64;
        total_days / (dates.len() - 1) as f64
    } else {
        0.0
    };
    
    Ok(CategoryInsights {
        category: category.to_string(),
        total_spent,
        transaction_count,
        average_transaction,
        monthly_average,
        highest_month,
        lowest_month,
        frequency_days,
    })
}

pub fn generate_spending_recommendations(insights: &[CategoryInsights]) -> Vec<String> {
    let mut recommendations = Vec::new();
    
    for insight in insights {
        // High frequency spending recommendations
        if insight.frequency_days < 3.0 && insight.frequency_days > 0.0 {
            recommendations.push(format!(
                "🚨 {} Alert: You spend on {} every {:.1} days on average. Consider setting a weekly limit.",
                insight.category, insight.category.to_lowercase(), insight.frequency_days
            ));
        }
        
        // High average transaction recommendations
        if insight.average_transaction > 50.0 && insight.category == "Coffee" {
            recommendations.push(format!(
                "☕ Coffee Insight: Your average coffee purchase is ${:.2}. Consider brewing at home to save ~${:.0}/month.",
                insight.average_transaction, 
                insight.monthly_average * 0.7 // Estimate 70% savings
            ));
        }
        
        // Monthly spending pattern recommendations
        if insight.monthly_average > 200.0 && (insight.category == "Coffee" || insight.category == "Personal Care") {
            recommendations.push(format!(
                "💡 {}: You spend ${:.0}/month on {}. Setting a ${:.0} budget could save ${:.0}/year.",
                insight.category,
                insight.monthly_average,
                insight.category.to_lowercase(),
                insight.monthly_average * 0.8,
                (insight.monthly_average - insight.monthly_average * 0.8) * 12.0
            ));
        }
        
        // High variance recommendations
        let (high_month, high_amount) = &insight.highest_month;
        let (low_month, low_amount) = &insight.lowest_month;
        if high_amount - low_amount > insight.monthly_average {
            recommendations.push(format!(
                "📊 {} Variance: Your spending varies significantly (${:.0} in {} vs ${:.0} in {}). Aim for consistency.",
                insight.category, high_amount, high_month, low_amount, low_month
            ));
        }
    }
    
    recommendations
}

fn calculate_trend_direction(monthly_data: &HashMap<String, f64>) -> TrendDirection {
    if monthly_data.len() < 2 {
        return TrendDirection::Stable;
    }
    
    let mut sorted_months: Vec<_> = monthly_data.iter().collect();
    sorted_months.sort_by(|a, b| a.0.cmp(b.0));
    
    let first_half_avg = sorted_months[..sorted_months.len()/2].iter()
        .map(|(_, amount)| **amount)
        .sum::<f64>() / (sorted_months.len()/2) as f64;
        
    let second_half_avg = sorted_months[sorted_months.len()/2..].iter()
        .map(|(_, amount)| **amount)
        .sum::<f64>() / (sorted_months.len() - sorted_months.len()/2) as f64;
    
    let change_percent = (second_half_avg - first_half_avg) / first_half_avg * 100.0;
    
    if change_percent > 10.0 {
        TrendDirection::Increasing
    } else if change_percent < -10.0 {
        TrendDirection::Decreasing
    } else {
        TrendDirection::Stable
    }
}

pub fn create_simple_chart(data: &[(String, f64)], width: usize) -> Vec<String> {
    let mut chart = Vec::new();
    
    if data.is_empty() {
        return vec!["No data to display".to_string()];
    }
    
    let max_value = data.iter().map(|(_, v)| *v).fold(0.0, f64::max);
    
    for (label, value) in data {
        let bar_length = ((value / max_value) * width as f64) as usize;
        let bar = "█".repeat(bar_length);
        let remaining = " ".repeat(width.saturating_sub(bar_length));
        
        chart.push(format!(
            "{:<12} |{}{}| ${:.0}",
            if label.len() > 11 { &label[..11] } else { label },
            bar,
            remaining,
            value
        ));
    }
    
    chart
}

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

    #[test]
    fn test_trend_direction_calculation() {
        let mut data = HashMap::new();
        data.insert("2024-01".to_string(), 100.0);
        data.insert("2024-02".to_string(), 120.0);
        data.insert("2024-03".to_string(), 150.0);
        data.insert("2024-04".to_string(), 180.0);
        
        assert_eq!(calculate_trend_direction(&data), TrendDirection::Increasing);
    }

    #[test]
    fn test_simple_chart_creation() {
        let data = vec![
            ("Coffee".to_string(), 150.0),
            ("Dining".to_string(), 300.0),
        ];
        
        let chart = create_simple_chart(&data, 20);
        assert_eq!(chart.len(), 2);
        assert!(chart[0].contains("Coffee"));
        assert!(chart[1].contains("Dining"));
    }

    #[test]
    fn test_empty_chart() {
        let data = vec![];
        let chart = create_simple_chart(&data, 20);
        assert_eq!(chart, vec!["No data to display"]);
    }
}