use std::collections::HashMap;
use anyhow::Result;
use rusqlite::{Connection, params};
use tracing::{debug, warn};

/// 查询优化器
/// 提供查询性能分析和优化建议
pub struct QueryOptimizer;

impl QueryOptimizer {
    /// 分析查询性能
    pub fn analyze_query_performance(conn: &Connection, sql: &str, params: &[&dyn rusqlite::ToSql]) -> Result<QueryAnalysis> {
        let explain_sql = format!("EXPLAIN QUERY PLAN {}", sql);
        
        let mut stmt = conn.prepare(&explain_sql)?;
        let query_plan_rows = stmt.query_map(params, |row| {
            Ok(QueryPlanStep {
                id: row.get(0)?,
                parent: row.get(1)?,
                notused: row.get(2)?,
                detail: row.get(3)?,
            })
        })?;

        let mut steps = Vec::new();
        for step_result in query_plan_rows {
            steps.push(step_result?);
        }

        let suggestions = Self::generate_optimization_suggestions(sql, &steps);
        let cost = Self::estimate_query_cost(&steps);
        let analysis = QueryAnalysis {
            query: sql.to_string(),
            execution_plan: steps,
            optimization_suggestions: suggestions,
            estimated_cost: cost,
        };

        Ok(analysis)
    }

    /// 生成优化建议
    fn generate_optimization_suggestions(sql: &str, steps: &[QueryPlanStep]) -> Vec<OptimizationSuggestion> {
        let mut suggestions = Vec::new();

        // 检查是否使用了表扫描
        for step in steps {
            if step.detail.contains("SCAN TABLE") && !step.detail.contains("USING INDEX") {
                suggestions.push(OptimizationSuggestion {
                    suggestion_type: SuggestionType::AddIndex,
                    description: format!("表扫描检测到: {}，建议添加索引", step.detail),
                    impact: ImpactLevel::High,
                    recommended_action: "考虑为查询条件中的列添加索引".to_string(),
                });
            }
        }

        // 检查是否有排序操作
        if sql.contains("ORDER BY") {
            let has_index_sort = steps.iter().any(|step| step.detail.contains("USING INDEX") && step.detail.contains("ORDER BY"));
            if !has_index_sort {
                suggestions.push(OptimizationSuggestion {
                    suggestion_type: SuggestionType::OptimizeSort,
                    description: "检测到排序操作，可能需要优化".to_string(),
                    impact: ImpactLevel::Medium,
                    recommended_action: "考虑为ORDER BY子句中的列添加索引".to_string(),
                });
            }
        }

        // 检查是否有JOIN操作
        if sql.contains("JOIN") {
            suggestions.push(OptimizationSuggestion {
                suggestion_type: SuggestionType::OptimizeJoin,
                description: "检测到JOIN操作，确保连接条件有适当的索引".to_string(),
                impact: ImpactLevel::Medium,
                recommended_action: "确保JOIN条件中的列都有索引".to_string(),
            });
        }

        // 检查是否使用了LIKE操作
        if sql.contains("LIKE") {
            suggestions.push(OptimizationSuggestion {
                suggestion_type: SuggestionType::OptimizeLike,
                description: "检测到LIKE操作，可能影响性能".to_string(),
                impact: ImpactLevel::Low,
                recommended_action: "考虑使用全文搜索或限制LIKE模式".to_string(),
            });
        }

        suggestions
    }

    /// 估算查询成本
    fn estimate_query_cost(steps: &[QueryPlanStep]) -> QueryCost {
        let mut cost = QueryCost::default();

        for step in steps {
            if step.detail.contains("SCAN TABLE") {
                cost.table_scans += 1;
                cost.estimated_rows += 1000; // 估算值
            }
            
            if step.detail.contains("USING INDEX") {
                cost.index_lookups += 1;
                cost.estimated_rows += 10; // 估算值
            }
            
            if step.detail.contains("SORT") {
                cost.sort_operations += 1;
            }
            
            if step.detail.contains("JOIN") {
                cost.join_operations += 1;
            }
        }

        // 计算总体成本评分
        cost.total_cost = cost.table_scans * 100 + 
                         cost.index_lookups * 5 + 
                         cost.sort_operations * 20 + 
                         cost.join_operations * 15;

        cost
    }

    /// 获取慢查询建议
    pub fn get_slow_query_recommendations() -> Vec<SlowQueryRecommendation> {
        vec![
            SlowQueryRecommendation {
                query_pattern: "SELECT * FROM workflow_templates WHERE base_name = ?".to_string(),
                recommendation: "为base_name列添加索引".to_string(),
                expected_improvement: "查询时间减少80-90%".to_string(),
            },
            SlowQueryRecommendation {
                query_pattern: "SELECT * FROM workflow_execution_records ORDER BY created_at DESC".to_string(),
                recommendation: "为created_at列添加降序索引".to_string(),
                expected_improvement: "排序性能提升70-80%".to_string(),
            },
            SlowQueryRecommendation {
                query_pattern: "SELECT * FROM workflow_execution_records WHERE status = ? AND created_at > ?".to_string(),
                recommendation: "创建(status, created_at)复合索引".to_string(),
                expected_improvement: "查询时间减少85-95%".to_string(),
            },
            SlowQueryRecommendation {
                query_pattern: "SELECT COUNT(*) FROM workflow_execution_records WHERE workflow_template_id = ?".to_string(),
                recommendation: "为workflow_template_id列添加索引".to_string(),
                expected_improvement: "统计查询性能提升90%以上".to_string(),
            },
        ]
    }

    /// 生成查询优化报告
    pub fn generate_optimization_report(conn: &Connection) -> Result<OptimizationReport> {
        let mut report = OptimizationReport {
            database_stats: Self::get_database_statistics(conn)?,
            index_recommendations: Self::get_index_recommendations(),
            query_patterns: Self::get_common_query_patterns(),
            performance_tips: Self::get_performance_tips(),
        };

        Ok(report)
    }

    /// 获取数据库统计信息
    fn get_database_statistics(conn: &Connection) -> Result<DatabaseStatistics> {
        let mut stats = DatabaseStatistics::default();

        // 获取表行数
        let tables = vec!["workflow_templates", "workflow_execution_records", "workflow_execution_environments"];
        for table in tables {
            let count_sql = format!("SELECT COUNT(*) FROM {}", table);
            let count: i64 = conn.query_row(&count_sql, [], |row| row.get(0))?;
            stats.table_row_counts.insert(table.to_string(), count);
        }

        // 获取索引信息
        let mut stmt = conn.prepare("SELECT name, tbl_name FROM sqlite_master WHERE type = 'index' AND name NOT LIKE 'sqlite_%'")?;
        let index_rows = stmt.query_map([], |row| {
            Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
        })?;

        for index_row in index_rows {
            let (index_name, table_name) = index_row?;
            stats.indexes.entry(table_name).or_insert_with(Vec::new).push(index_name);
        }

        Ok(stats)
    }

    /// 获取索引建议
    fn get_index_recommendations() -> Vec<IndexRecommendation> {
        vec![
            IndexRecommendation {
                table_name: "workflow_templates".to_string(),
                column_names: vec!["base_name".to_string()],
                index_type: IndexType::BTree,
                reason: "频繁按base_name查询模板".to_string(),
                priority: Priority::High,
            },
            IndexRecommendation {
                table_name: "workflow_templates".to_string(),
                column_names: vec!["base_name".to_string(), "version".to_string()],
                index_type: IndexType::Composite,
                reason: "版本查询需要复合索引".to_string(),
                priority: Priority::High,
            },
            IndexRecommendation {
                table_name: "workflow_execution_records".to_string(),
                column_names: vec!["status".to_string(), "created_at".to_string()],
                index_type: IndexType::Composite,
                reason: "队列管理和状态查询".to_string(),
                priority: Priority::High,
            },
            IndexRecommendation {
                table_name: "workflow_execution_records".to_string(),
                column_names: vec!["workflow_template_id".to_string()],
                index_type: IndexType::BTree,
                reason: "模板相关查询".to_string(),
                priority: Priority::Medium,
            },
            IndexRecommendation {
                table_name: "workflow_execution_environments".to_string(),
                column_names: vec!["is_active".to_string(), "is_available".to_string(), "health_status".to_string()],
                index_type: IndexType::Composite,
                reason: "环境选择查询".to_string(),
                priority: Priority::High,
            },
        ]
    }

    /// 获取常见查询模式
    fn get_common_query_patterns() -> Vec<QueryPattern> {
        vec![
            QueryPattern {
                pattern: "模板版本查询".to_string(),
                sql_template: "SELECT * FROM workflow_templates WHERE base_name = ? ORDER BY version DESC".to_string(),
                frequency: QueryFrequency::High,
                optimization_notes: "需要base_name索引和version排序优化".to_string(),
            },
            QueryPattern {
                pattern: "执行历史查询".to_string(),
                sql_template: "SELECT * FROM workflow_execution_records WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?".to_string(),
                frequency: QueryFrequency::High,
                optimization_notes: "需要(user_id, created_at)复合索引".to_string(),
            },
            QueryPattern {
                pattern: "环境健康检查".to_string(),
                sql_template: "SELECT * FROM workflow_execution_environments WHERE is_active = 1 AND health_status = 'Healthy'".to_string(),
                frequency: QueryFrequency::Medium,
                optimization_notes: "需要(is_active, health_status)复合索引".to_string(),
            },
        ]
    }

    /// 获取性能优化提示
    fn get_performance_tips() -> Vec<PerformanceTip> {
        vec![
            PerformanceTip {
                category: "索引优化".to_string(),
                tip: "为经常用于WHERE、ORDER BY和JOIN的列创建索引".to_string(),
                impact: ImpactLevel::High,
            },
            PerformanceTip {
                category: "查询优化".to_string(),
                tip: "避免SELECT *，只查询需要的列".to_string(),
                impact: ImpactLevel::Medium,
            },
            PerformanceTip {
                category: "分页优化".to_string(),
                tip: "使用LIMIT和OFFSET进行分页，避免加载大量数据".to_string(),
                impact: ImpactLevel::Medium,
            },
            PerformanceTip {
                category: "缓存策略".to_string(),
                tip: "对频繁查询的数据使用缓存".to_string(),
                impact: ImpactLevel::High,
            },
            PerformanceTip {
                category: "连接池".to_string(),
                tip: "使用连接池减少数据库连接开销".to_string(),
                impact: ImpactLevel::Medium,
            },
        ]
    }
}

/// 查询分析结果
#[derive(Debug)]
pub struct QueryAnalysis {
    pub query: String,
    pub execution_plan: Vec<QueryPlanStep>,
    pub optimization_suggestions: Vec<OptimizationSuggestion>,
    pub estimated_cost: QueryCost,
}

/// 查询计划步骤
#[derive(Debug)]
pub struct QueryPlanStep {
    pub id: i32,
    pub parent: i32,
    pub notused: i32,
    pub detail: String,
}

/// 优化建议
#[derive(Debug)]
pub struct OptimizationSuggestion {
    pub suggestion_type: SuggestionType,
    pub description: String,
    pub impact: ImpactLevel,
    pub recommended_action: String,
}

/// 建议类型
#[derive(Debug)]
pub enum SuggestionType {
    AddIndex,
    OptimizeSort,
    OptimizeJoin,
    OptimizeLike,
    UseLimit,
    AvoidSelectStar,
}

/// 影响级别
#[derive(Debug)]
pub enum ImpactLevel {
    Low,
    Medium,
    High,
}

/// 查询成本
#[derive(Debug, Default)]
pub struct QueryCost {
    pub table_scans: i32,
    pub index_lookups: i32,
    pub sort_operations: i32,
    pub join_operations: i32,
    pub estimated_rows: i32,
    pub total_cost: i32,
}

/// 慢查询建议
#[derive(Debug)]
pub struct SlowQueryRecommendation {
    pub query_pattern: String,
    pub recommendation: String,
    pub expected_improvement: String,
}

/// 优化报告
#[derive(Debug)]
pub struct OptimizationReport {
    pub database_stats: DatabaseStatistics,
    pub index_recommendations: Vec<IndexRecommendation>,
    pub query_patterns: Vec<QueryPattern>,
    pub performance_tips: Vec<PerformanceTip>,
}

/// 数据库统计信息
#[derive(Debug, Default)]
pub struct DatabaseStatistics {
    pub table_row_counts: HashMap<String, i64>,
    pub indexes: HashMap<String, Vec<String>>,
}

/// 索引建议
#[derive(Debug)]
pub struct IndexRecommendation {
    pub table_name: String,
    pub column_names: Vec<String>,
    pub index_type: IndexType,
    pub reason: String,
    pub priority: Priority,
}

/// 索引类型
#[derive(Debug)]
pub enum IndexType {
    BTree,
    Composite,
    Unique,
}

/// 优先级
#[derive(Debug)]
pub enum Priority {
    Low,
    Medium,
    High,
}

/// 查询模式
#[derive(Debug)]
pub struct QueryPattern {
    pub pattern: String,
    pub sql_template: String,
    pub frequency: QueryFrequency,
    pub optimization_notes: String,
}

/// 查询频率
#[derive(Debug)]
pub enum QueryFrequency {
    Low,
    Medium,
    High,
}

/// 性能提示
#[derive(Debug)]
pub struct PerformanceTip {
    pub category: String,
    pub tip: String,
    pub impact: ImpactLevel,
}
