// pub mod sql_value;
#[cfg(any(feature = "postgres", feature = "mysql", feature = "sqlite"))]
mod buildq;
pub mod macros;
pub mod sql_from_json;
pub mod sql_from_simple;
pub mod parse_value;


#[cfg(any(feature = "postgres", feature = "mysql", feature = "sqlite"))]
pub use buildq::*;

use std::collections::HashMap;
use serde::{Deserialize, Serialize};
use serde_json::Value as JsonValue;
use crate::{BuildConditionItem, TableColumn, TableInfo, verify::is_valid_identifier};


#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
#[cfg_attr(any(feature = "postgres", feature = "mysql", feature = "sqlite"), derive(sqlx::FromRow))]
pub struct JsonCountRow {
    pub data: JsonValue,
    pub count: i64,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
#[cfg_attr(any(feature = "postgres", feature = "mysql", feature = "sqlite"), derive(sqlx::FromRow))]
pub struct JsonRow {
    pub data: JsonValue,
}


#[derive(Debug,Serialize,Deserialize,Clone)]
pub struct UpdateOrInsert<T> {
    pub is_update: bool,
    pub data:Option<Vec<T>>
}


pub fn merged_conditions(
    exist_conditions: &Option<Vec<BuildConditionItem>>,
    add_conditions: &Option<Vec<BuildConditionItem>>,
) -> Option<Vec<BuildConditionItem>> {
    match (exist_conditions, add_conditions) {
        (Some(existing), Some(additional)) => {
            let mut merged = existing.clone();
            merged.extend(additional.iter().cloned());
            Some(merged)
        }
        (Some(existing), None) => Some(existing.clone()),
        (None, Some(additional)) => Some(additional.clone()),
        (None, None) => None,
    }
}

pub fn get_subquery(sql: &str) -> Option<String> {
    let dialect = sqlparser::dialect::GenericDialect {};
    let ast = sqlparser::parser::Parser::parse_sql(&dialect, sql).unwrap();
    for stmt in ast {
        if let sqlparser::ast::Statement::Query(query) = stmt {
            let body = &query.body;
            if let sqlparser::ast::SetExpr::Select(select) = &**body {
                for table_with_joins in &select.from {
                    let relation = &table_with_joins.relation;
                    if let sqlparser::ast::TableFactor::Derived { subquery, .. } = relation {
                        let subquery_sql = subquery.to_string();
                        // println!("子查询 SQL: {}", subquery_sql);
                        return Some(subquery_sql);
                    }
                }
            }
        }
    }
    None
}

pub fn create_table(table_name: &str, columns: &[TableColumn]) -> anyhow::Result<String> {
    is_valid_identifier(table_name, "表名")?;

    let mut sql = format!("CREATE TABLE IF NOT EXISTS {} (", table_name);
    for (i, column) in columns.iter().enumerate() {
        is_valid_identifier(&column.name, "字段名")?;
        if i > 0 {
            sql += ", ";
        }
        sql += &column.name;
        sql += " ";
        sql += column.r#type.as_ref();

        if let Some(a) = &column.base_type {
            sql += &format!("({})", a);
        }

        if column.pk {
            sql += " PRIMARY KEY";
            continue;
        }
        if column.not_null {
            sql += " NOT NULL";
        }
        if !column.non_unique {
            sql += " UNIQUE";
        }

        if let Some(ref dflt_value) = column.dflt_value {
            // is_valid_identifier(dflt_value)?;
            sql += " DEFAULT ";
            sql += dflt_value;
        }
    }
    sql += ");";
    println!("创建的SQL:{}", sql);
    Ok(sql)
}

pub fn alter_table(
    table_name: &str,
    new_fields: &[TableColumn],
    old_fields: &[TableColumn],
) -> anyhow::Result<Vec<String>> {
    use std::collections::HashMap;

    is_valid_identifier(table_name, "表名")?;

    let mut sqls = vec![];

    // 将旧字段列表转为 map 方便查找
    let old_map: HashMap<_, _> = old_fields.iter().map(|f| (&f.name, f)).collect();

    for new_field in new_fields {
        is_valid_identifier(&new_field.name, "字段名")?;

        match old_map.get(&new_field.name) {
            Some(old_field) => {
                // 字段已存在，比较是否有差异
                if old_field.r#type != new_field.r#type
                    || old_field.pk != new_field.pk
                    || old_field.not_null != new_field.not_null
                    || old_field.non_unique != new_field.non_unique
                    || old_field.dflt_value != new_field.dflt_value
                {
                    // SQLite 不支持 ALTER COLUMN，提示用户
                    println!(
                        "字段 `{}` 有变更，但 SQLite 不支持直接修改字段类型/约束，请手动迁移。",
                        new_field.name
                    );
                }
            }
            None => {
                // 新字段，生成 ALTER TABLE ADD COLUMN
                let mut line = format!("ALTER TABLE {} ADD COLUMN {} ", table_name, new_field.name);

                line += &new_field.r#type;

                if new_field.not_null {
                    line += " NOT NULL";
                }

                if !new_field.non_unique {
                    line += " UNIQUE";
                }

                if let Some(ref dflt_value) = new_field.dflt_value {
                    line += " DEFAULT ";
                    line += dflt_value;
                }

                // 注意：SQLite 不允许 ADD COLUMN 为 PRIMARY KEY
                if new_field.pk {
                    println!(
                        "字段 `{}` 是 PRIMARY KEY，但 SQLite 不支持 ALTER TABLE 添加主键字段。",
                        new_field.name
                    );
                }

                sqls.push(line);
            }
        }
    }

    Ok(sqls)
}

pub fn compare_table(
    old_tables: &[TableInfo],
    new_tables: &[TableInfo],
) -> anyhow::Result<Option<Vec<TableInfo>>> {
    let mut old_map: HashMap<String, TableInfo> = HashMap::new();
    let mut new_map: HashMap<String, &TableInfo> = HashMap::new();
    let mut old_keys = vec![];
    let mut new_keys = vec![];

    let mut is_changed = false;
    for t in old_tables {
        old_map.insert(t.table_name.clone(), t.clone());
        old_keys.push(t.table_name.clone());
    }
    for t in new_tables {
        new_map.insert(t.table_name.clone(), t);
        new_keys.push(t.table_name.clone());
    }

    // println!("old_keys{:#?}",old_keys);
    // println!("new_keys{:#?}",new_keys);
    // 1. 检查新增表
    for table_name in new_keys.iter() {
        if !old_keys.contains(table_name) {
            println!("✅ 新增表: {}", table_name);
            let t = new_map.get(table_name).unwrap();
            old_map.insert(table_name.clone(), (*t).clone());
            is_changed = true
        }
    }

    // 2. 检查删除表
    for table_name in old_keys.iter() {
        if !new_keys.contains(table_name) {
            println!("❌ 删除表: {}", table_name);
            old_map.remove(table_name);
            is_changed = true
        }
    }
    // 此时old_map 与  new_map , table_name 相同, 且数量相同,     columns 需要进一步比较
    // 所以只要遍历new keys就可以了
    for table_name in new_keys.iter() {
        let mut table_changed = false;
        let mut old_table = old_map.get(table_name).unwrap().clone(); // 这个一定有
        let new_table = new_map.get(table_name).unwrap(); // 这个一定有

        let mut old_fields_map: HashMap<String, TableColumn> = old_table
            .columns
            .into_iter()
            .map(|f| (f.name.clone(), f))
            .collect();
        let old_field_keys: Vec<String> = old_fields_map.keys().cloned().collect();
        let new_fields_map: HashMap<String, &TableColumn> = new_table
            .columns
            .iter()
            .map(|f| (f.name.clone(), f))
            .collect();

        // A. 新增字段
        for field_name in new_fields_map.keys() {
            if !old_fields_map.contains_key(field_name) {
                println!("🟢 表 `{}` 新增字段: `{}`", table_name, field_name);
                let f = new_fields_map.get(field_name).unwrap();
                old_fields_map.insert(field_name.to_string(), (*f).clone());
                table_changed = true
            }
        }

        // B. 删除字段
        for field_name in old_field_keys.iter() {
            if !new_fields_map.contains_key(field_name) {
                println!("🟠 表 `{}` 删除字段: `{}`", table_name, field_name);
                old_fields_map.remove(field_name);
                table_changed = true
            }
        }

        // C. 修改字段   old_fields_map 此时已经同步数量和名称
        // 这里 old_field_keys 重写, 如果直接借用 old_fields_map.keys() ,内部不能修改 old_fields_map
        let old_field_keys = old_fields_map.keys().cloned().collect::<Vec<_>>();
        for field_name in old_field_keys.iter() {
            let mut item_changed = false;
            let mut old_field = old_fields_map.get(field_name).unwrap().clone(); // 这个一定有,上面同步了
            let cope_old_field = old_field.clone(); // 这个用于dev 开发测试
            let new_field = new_fields_map.get(field_name).unwrap(); // 这个一定有
            if old_field.pk != new_field.pk {
                old_field.pk = new_field.pk;
                item_changed = true
            }
            if old_field.not_null != new_field.not_null {
                old_field.not_null = new_field.not_null;
                item_changed = true
            }

            if old_field.dflt_value != new_field.dflt_value {
                old_field.dflt_value = new_field.dflt_value.clone();
                item_changed = true
            }

            if old_field.r#type != new_field.r#type && new_field.r#type == old_field.r#type {
                // 仅修改了自定义的字段类型,而没有修改变字段的实际类型
                old_field.r#type = new_field.r#type.clone();
                item_changed = true
            }

            if old_field.base_type != new_field.base_type {
                // 这是用sql 修改的情况
                old_field.base_type = new_field.base_type.clone();
                // old_field.r#type = native_str_to_field_type(&new_field.source_type);
                item_changed = true
            }

            if old_field.foreign_key_table != new_field.foreign_key_table
                || old_field.foreign_key_column != new_field.foreign_key_column
            {
                old_field.foreign_key_table = new_field.foreign_key_table.clone();
                old_field.foreign_key_column = new_field.foreign_key_column.clone();
                item_changed = true
            }

            if old_field.index_name != new_field.index_name {
                old_field.index_name = new_field.index_name.clone();
                item_changed = true
            }

            if old_field.non_unique != new_field.non_unique {
                old_field.non_unique = new_field.non_unique;
                item_changed = true
            }
            if old_field.description != new_field.description {
                old_field.description = new_field.description.clone();
                item_changed = true
            }
            if old_field.auto_increment != new_field.auto_increment {
                old_field.auto_increment = new_field.auto_increment;
                item_changed = true
            }
            if old_field.ordinal_position != new_field.ordinal_position {
                old_field.ordinal_position = new_field.ordinal_position;
                item_changed = true
            }

            if item_changed {
                println!(
                    "🟡 表 `{}` 字段 `{}` 改变:\n  旧: {:?}\n  新: {:?}",
                    table_name, field_name, cope_old_field, new_field
                );
                // let mut org_field = old_fields_map.get(field_name);
                old_fields_map.insert(field_name.to_string(), old_field);
                table_changed = true
            }
        }
        if table_changed {
            let columns: Vec<TableColumn> = old_fields_map.values().cloned().collect();
            old_table = TableInfo {
                table_name: table_name.to_string(),
                create_sql: new_table.create_sql.clone(),
                columns,
                table_comment: None,
            };
            old_map.insert(table_name.to_string(), old_table);
            is_changed = true;
        }
    }
    if is_changed {
        let mut res: Vec<TableInfo> = old_map.values().cloned().collect();
        for i in res.iter_mut() {
            i.columns.sort_by_key(|f| f.ordinal_position); // 去掉赋值，直接调用排序
        }
        return Ok(Some(res));
    }

    Ok(None)
}

