use super::differ::generate_mysql_diff;
use super::parser::parse_sql_tables;
use super::types::{TableColumn, TableDefinition, TableIndex};
use crate::error::DuckError;
use crate::mysql_executor::MySqlExecutor;
use tracing::info;

/// 生成SQL架构差异
/// 这个版本专注于生成实际可执行的MySQL差异SQL，而不是简单的文本比较
pub fn generate_schema_diff(
    from_sql: Option<&str>,
    to_sql: &str,
    from_version: Option<&str>,
    to_version: &str,
) -> Result<(String, String), DuckError> {
    match from_sql {
        None => {
            // 初始版本，返回完整的创建脚本
            info!("Generating complete database schema for initial version {}", to_version);
            let description = format!("Complete database schema for initial version {}", to_version);
            Ok((to_sql.to_string(), description))
        }
        Some(from_content) => {
            info!(
                "Starting to generate SQL diff from version {} to {}",
                from_version.unwrap_or("unknown"),
                to_version
            );

            // 如果内容完全相同，返回空差异
            if from_content.trim() == to_sql.trim() {
                info!("Version content is identical, no diff needed");
                return Ok((
                    String::new(),
                    format!(
                        "Version {} to {}: No changes",
                        from_version.unwrap_or("unknown"),
                        to_version
                    ),
                ));
            }

            // 解析两个SQL文件的表结构
            let from_tables = parse_sql_tables(from_content)?;
            let to_tables = parse_sql_tables(to_sql)?;

            // 生成差异SQL
            let (diff_sql, _stats) = generate_mysql_diff(&from_tables, &to_tables)?;

            let description = if diff_sql.trim().is_empty() {
                format!(
                    "Version {} to {}: only comments or format changes, no actual schema differences",
                    from_version.unwrap_or("unknown"),
                    to_version
                )
            } else {
                let lines_count = diff_sql
                    .lines()
                    .filter(|line| !line.trim().is_empty() && !line.trim().starts_with("--"))
                    .count();

                // 分析差异类型
                let mut change_types = Vec::new();
                if diff_sql.contains("CREATE TABLE") {
                    change_types.push("new tables");
                }
                if diff_sql.contains("DROP TABLE") {
                    change_types.push("dropped tables");
                }
                if diff_sql.contains("ALTER TABLE") && diff_sql.contains("ADD COLUMN") {
                    change_types.push("new columns");
                }
                if diff_sql.contains("ALTER TABLE") && diff_sql.contains("DROP COLUMN") {
                    change_types.push("dropped columns");
                }
                if diff_sql.contains("ALTER TABLE") && diff_sql.contains("MODIFY COLUMN") {
                    change_types.push("modified columns");
                }
                if diff_sql.contains("ALTER TABLE") && diff_sql.contains("ADD KEY") {
                    change_types.push("new indexes");
                }
                if diff_sql.contains("ALTER TABLE") && diff_sql.contains("DROP KEY") {
                    change_types.push("dropped indexes");
                }

                let change_summary = if change_types.is_empty() {
                    "schema changes".to_string()
                } else {
                    change_types.join(", ")
                };

                format!(
                    "Version {} to {}: {} - generated {} lines of executable diff SQL",
                    from_version.unwrap_or("unknown"),
                    to_version,
                    change_summary,
                    lines_count
                )
            };

            info!("Diff generation completed: {}", description);
            Ok((diff_sql, description))
        }
    }
}

/// 基于在线数据库架构与模板SQL生成差异（Live Diff）
/// 返回：SchemaDiffResult 结构体，包含差异SQL、描述、在线架构SQL和执行标记
pub async fn generate_live_schema_diff(
    executor: &MySqlExecutor,
    to_sql: &str,
    to_version: &str,
) -> Result<super::types::SchemaDiffResult, DuckError> {
    info!("Starting to generate online schema to {} SQL diff", to_version);

    // 解析目标模板
    let to_tables = parse_sql_tables(to_sql)?;

    // 抓取在线架构并生成差异（同时获取原始 SQL）
    let (live_tables, live_sql) = executor
        .fetch_live_schema_with_sql()
        .await
        .map_err(|e| DuckError::custom(format!("Failed to fetch online schema: {e}")))?;

    let (diff_sql, stats) = generate_mysql_diff(&live_tables, &to_tables)?;

    // 使用统计信息判断是否有可执行SQL和警告
    let has_executable_sql = stats.has_executable_operations();
    let has_warnings = stats.has_dangerous_operations();

    let description = if !stats.has_changes() {
        format!("Online schema to {to_version}: no actual schema differences")
    } else if !has_executable_sql && has_warnings {
        format!("Online schema to {to_version}: only includes delete operation warnings, no executable SQL")
    } else {
        let executable_lines = diff_sql
            .lines()
            .filter(|line| !line.trim().is_empty() && !line.trim().starts_with("--"))
            .count();

        format!(
            "Online schema to {}: {} - generated {} lines of executable diff SQL",
            to_version,
            stats.summary(),
            executable_lines
        )
    };

    info!("Live Diff completed: {}", description);

    Ok(super::types::SchemaDiffResult {
        diff_sql,
        description,
        live_sql: Some(live_sql),
        has_executable_sql,
        has_warnings,
    })
}

/// 格式化默认值用于SQL输出，正确处理不同类型的值
fn format_default_value_for_sql(default: &str) -> String {
    // 检查是否是MySQL关键字/函数（不需要引号）
    let mysql_keywords = [
        "CURRENT_TIMESTAMP",
        "NOW()",
        "CURRENT_DATE",
        "CURRENT_TIME",
        "LOCALTIMESTAMP",
        "LOCALTIME",
        "NULL",
        "TRUE",
        "FALSE",
    ];

    let upper_default = default.to_uppercase();

    // 如果是MySQL关键字，直接返回（不加引号）
    if mysql_keywords.contains(&upper_default.as_str()) {
        return default.to_string();
    }

    // 如果是纯数字（可能包含负号和小数点），直接返回
    if default
        .chars()
        .all(|c| c.is_ascii_digit() || c == '-' || c == '.')
    {
        return default.to_string();
    }

    // 如果已经是引号包围的，直接返回
    if (default.starts_with('\'') && default.ends_with('\''))
        || (default.starts_with('"') && default.ends_with('"'))
    {
        return default.to_string();
    }

    // 其他情况作为字符串处理，添加单引号
    format!("'{}'", default)
}

/// 生成CREATE TABLE SQL
pub fn generate_create_table_sql(table: &TableDefinition) -> String {
    let mut sql = format!("CREATE TABLE `{}` (", table.name);

    // 添加列定义
    let mut parts = Vec::new();
    for column in &table.columns {
        parts.push(format!("  {}", generate_column_sql(column)));
    }

    // 添加索引定义
    for index in &table.indexes {
        parts.push(format!("  {}", generate_index_sql(index)));
    }

    sql.push_str(&parts.join(",\n"));
    sql.push_str("\n)");

    // 添加表选项
    if let Some(engine) = &table.engine {
        sql.push_str(&format!(" ENGINE={engine}"));
    }
    if let Some(charset) = &table.charset {
        sql.push_str(&format!(" DEFAULT CHARSET={charset}"));
    }

    sql.push(';');
    sql
}

/// 生成列定义SQL
pub fn generate_column_sql(column: &TableColumn) -> String {
    let mut sql = format!("`{}` {}", column.name, column.data_type);

    if !column.nullable {
        sql.push_str(" NOT NULL");
    }

    if let Some(default) = &column.default_value {
        sql.push_str(&format!(
            " DEFAULT {}",
            format_default_value_for_sql(default)
        ));
    }

    if column.auto_increment {
        sql.push_str(" AUTO_INCREMENT");
    }

    if let Some(comment) = &column.comment {
        sql.push_str(&format!(" COMMENT '{comment}'"));
    }

    sql
}

/// 生成索引定义SQL
pub fn generate_index_sql(index: &TableIndex) -> String {
    if index.is_primary {
        format!(
            "PRIMARY KEY ({})",
            index
                .columns
                .iter()
                .map(|c| format!("`{c}`"))
                .collect::<Vec<_>>()
                .join(", ")
        )
    } else if index.is_unique {
        format!(
            "UNIQUE KEY `{}` ({})",
            index.name,
            index
                .columns
                .iter()
                .map(|c| format!("`{c}`"))
                .collect::<Vec<_>>()
                .join(", ")
        )
    } else {
        format!(
            "KEY `{}` ({})",
            index.name,
            index
                .columns
                .iter()
                .map(|c| format!("`{c}`"))
                .collect::<Vec<_>>()
                .join(", ")
        )
    }
}
