db-cores 0.1.0

use crate::builder::parse_value::{create_tpye_value, TypeJsonValue};
use crate::verify::{escape_sql_string, is_valid_identifier, is_valid_identifiers};
use regex::Regex;
use serde_json::json;
use serde_json::{ Value as JsonValue};
use sql_builder::SqlBuilder;


/// a=b ,a>1,a<=1
pub fn add_cond_handle(add_cond: &str) -> Option<JsonValue> {
    let re = Regex::new(r"(<=|>=|!=|=|<|>)").expect("正则错误!"); // 匹配判断符的正则表达式
    if let Some(op_match) = re.find(add_cond) {
        // 查找并分割第一个判断符
        let left = add_cond[..op_match.start()].trim(); // 分割操作符左边
        let right = add_cond[op_match.end()..].trim(); // 去掉右侧多余空格
        let operator = op_match.as_str(); // 获取操作符
        let values = right
            .parse::<i64>()
            .map(|v| json!(v))
            .or_else(|_| right.parse::<f64>().map(|v| json!(v)))
            .unwrap_or_else(|_| json!(right.to_owned()));

        let formatted_right = json!({
            "column": left.to_owned(),
            "operator": operator,
            "values": values,
            "logical": "AND"
        });

        println!("add_cond_handle 增加条件:{}", formatted_right);
        return Some(formatted_right);
    }

    None
}

pub fn get_sql_key(json_value: &JsonValue, key: &str) -> Option<String> {
    if let Some(value) = json_value.get(key) {
        if let Some(str_value) = value.as_str() {
            return Some(str_value.to_string());
        } else if let Some(num_value) = value.as_i64() {
            return Some(num_value.to_string());
        } else if let Some(num_value) = value.as_f64() {
            return Some(num_value.to_string());
        }
    }
    None
}

pub fn get_sql_field(json_value: &JsonValue, key: &str) -> Option<String> {
    if let Some(value) = json_value.get(key) {
        if let Some(str_value) = value.as_str() {
            let column = str_value.to_string();
            return Some(column.to_string());
        } else if let Some(num_value) = value.as_i64() {
            return Some(num_value.to_string());
        } else if let Some(num_value) = value.as_f64() {
            return Some(num_value.to_string());
        }
    }
    None
}

pub fn get_sql_values(json_value: &JsonValue, key: &str) -> Option<TypeJsonValue> {
    if let Some(value) = json_value.get(key) {
        if let Some(str_value) = create_tpye_value(value) {
            return Some(str_value);
        }
    }
    None
}

pub fn build_conditions_item(
    sql_builder: &mut SqlBuilder,
    conditions_item: JsonValue,
) -> anyhow::Result<()> {
    let column: String = get_sql_field(&conditions_item, "column").expect("Field is required");
    let operator: String = get_sql_key(&conditions_item, "operator").unwrap_or("=".to_string());
    let values: TypeJsonValue =
        get_sql_values(&conditions_item, "values").expect("Values are required");
    let logical: String = get_sql_key(&conditions_item, "logical").unwrap_or("AND".to_string());

    // 根据逻辑操作符决定使用 AND 或 OR
    match logical.as_str() {
        "AND" => match operator.as_str() {
            "LIKE_ANY" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.and_where_like_any(column, s);
                }
                _ => {
                    return Err(anyhow::anyhow!("LIKE_ANY 后跟随的值不正确".to_owned()));
                }
            },
            "=" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.and_where_eq(column, format!("'{}'", s));
                }
                TypeJsonValue::Number(f) => {
                    sql_builder.and_where_eq(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.and_where_eq(column, d);
                }
                TypeJsonValue::Bool(d) => {
                    sql_builder.and_where_eq(column, d as u8);
                }
                _ => {
                    return Err(anyhow::anyhow!("= 后跟随的值不正确".to_owned()));
                }
            },
            ">" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.and_where_gt(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.and_where_gt(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!("> 后跟随的值不正确".to_owned()));
                }
            },
            "<" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.and_where_lt(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.and_where_lt(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!("< 后跟随的值不正确".to_owned()));
                }
            },
            ">=" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.and_where_ge(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.and_where_ge(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!(">= 后跟随的值不正确".to_owned()));
                }
            },
            "<=" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.and_where_le(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.and_where_le(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!("<= 后跟随的值不正确".to_owned()));
                }
            },
            "!=" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.and_where_ne(column, s);
                }
                TypeJsonValue::Number(f) => {
                    sql_builder.and_where_ne(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.and_where_ne(column, d);
                }
                TypeJsonValue::Bool(d) => {
                    sql_builder.and_where_eq(column, d as u8);
                }
                _ => {
                    return Err(anyhow::anyhow!("!= 后跟随的值不正确".to_owned()));
                }
            },
            "BETWEEN" => match values {
                TypeJsonValue::Array(vals) => {
                    if vals.len() == 2 {
                        let start_value = vals.first();
                        let end_value = vals.get(1);

                        match (start_value, end_value) {
                            (
                                Some(TypeJsonValue::Number(start)),
                                Some(TypeJsonValue::Number(end)),
                            ) => {
                                sql_builder.and_where_between(column, start, end);
                            }
                            (
                                Some(TypeJsonValue::DateTime(start)),
                                Some(TypeJsonValue::DateTime(end)),
                            ) => {
                                sql_builder.and_where_between(column, start, end);
                            }
                            _ => {
                                return Err(anyhow::anyhow!("BETWEEN 后跟随的值不正确".to_owned()));
                            }
                        }
                    } else {
                        return Err(anyhow::anyhow!("BETWEEN 操作符需要两个值".to_owned()));
                    }
                }
                _ => {
                    return Err(anyhow::anyhow!("BETWEEN 操作符需要一个数组".to_owned()));
                }
            },
            "NOT_NULL" => {
                sql_builder.and_where_is_not_null(column);
            }
            "IS_NULL" => {
                sql_builder.and_where_is_null(column);
            }
            "IN" => match values {
                TypeJsonValue::Array(vals) => {
                    let in_values: Vec<String> = vals
                        .iter()
                        .filter_map(|v| {
                            if let TypeJsonValue::Number(s) = v {
                                let news = format!("{}", s);
                                Some(news)
                            } else if let TypeJsonValue::Str(s) = v {
                                let news = format!("'{}'", s);
                                Some(news)
                            } else {
                                None
                            }
                        })
                        .collect();
                    sql_builder.and_where_in(column, &in_values);
                }
                _ => {
                    return Err(anyhow::anyhow!("SQL  IN  语句参数不是数组".to_owned()));
                }
            },
            "NOT_IN" => match values {
                TypeJsonValue::Array(vals) => {
                    let not_in_values: Vec<String> = vals
                        .iter()
                        .filter_map(|v| {
                            if let TypeJsonValue::Str(s) = v {
                                let news = format!("'{}'", s);
                                Some(news)
                            } else {
                                None
                            }
                        })
                        .collect();
                    sql_builder.and_where_not_in(column, &not_in_values);
                }
                _ => {
                    return Err(anyhow::anyhow!("NOT_IN 参数不是数组".to_owned()));
                }
            },
            "NOT_BETWEEN" => match values {
                TypeJsonValue::Array(vals) => {
                    if vals.len() == 2 {
                        let start_value = vals.first();
                        let end_value = vals.get(1);

                        match (start_value, end_value) {
                            (
                                Some(TypeJsonValue::Number(start)),
                                Some(TypeJsonValue::Number(end)),
                            ) => {
                                sql_builder.and_where_not_between(column, start, end);
                            }
                            (
                                Some(TypeJsonValue::DateTime(start)),
                                Some(TypeJsonValue::DateTime(end)),
                            ) => {
                                sql_builder.and_where_not_between(column, start, end);
                            }
                            _ => {
                                return Err(anyhow::anyhow!("BETWEEN 参数格式不正确".to_owned()));
                            }
                        }
                    } else {
                        return Err(anyhow::anyhow!("BETWEEN 不是两个参数".to_owned()));
                    }
                }
                _ => {
                    return Err(anyhow::anyhow!("BETWEEN 参数应为一个数组".to_owned()));
                }
            },

            "IN_QUERY" => match values {
                TypeJsonValue::Object(_) => {
                    // 获取子查询的 JSON 对象
                    if let Some(query) = conditions_item.get("values") {
                        // 构建 SQL 子查询
                        if let Ok(q) = select_builder_from_json(query, None, None) {
                            // 使用 and_where_in_query 插入子查询
                            sql_builder.and_where_in_query(column, q.query().unwrap());
                        } else {
                            return Err(anyhow::anyhow!("IN_QUERY 后跟随的值不正确".to_owned()));
                        }
                    } else {
                        return Err(anyhow::anyhow!("IN_QUERY 后跟随的值不正确".to_owned()));
                    }
                }
                _ => return Err(anyhow::anyhow!("IN_QUERY 后跟随的值不是对象".to_owned())),
            },

            "NOT_IN_QUERY" => match values {
                TypeJsonValue::Object(_) => {
                    // 获取子查询的 JSON 对象
                    if let Some(query) = conditions_item.get("values") {
                        // 构建 SQL 子查询
                        if let Ok(q) = select_builder_from_json(query, None, None) {
                            // 使用 and_where_not_in_query 插入子查询
                            sql_builder.and_where_not_in_query(column, q.query().unwrap());
                        } else {
                            return Err(
                                anyhow::anyhow!("NOT_IN_QUERY 后跟随的值不正确".to_owned()),
                            );
                        }
                    } else {
                        return Err(anyhow::anyhow!("NOTIN QUERY 后跟随的值不正确".to_owned()));
                    }
                }
                _ => return Err(anyhow::anyhow!("NOT_IN_QUERY 后跟随的值不是对象".to_owned())),
            },
            "LIKE" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.and_where_like(column, s);
                }
                _ => {
                    return Err(anyhow::anyhow!("LIKE 后跟随的值不正确".to_owned()));
                }
            },

            _ => return Err(anyhow::anyhow!("SQL 不支持的条件操作符".to_owned())),
        },
        "OR" => match operator.as_str() {
            "LIKE_ANY" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.or_where_like_any(column, format!("'{}'", s));
                }
                _ => {
                    return Err(anyhow::anyhow!("LIKE 后跟随的值不正确".to_owned()));
                }
            },
            "=" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.or_where_eq(column, s);
                }
                TypeJsonValue::Number(f) => {
                    sql_builder.or_where_eq(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.or_where_eq(column, d);
                }
                TypeJsonValue::Bool(d) => {
                    sql_builder.and_where_eq(column, d as u8);
                }
                _ => {
                    return Err(anyhow::anyhow!("= 后跟随的值不正确".to_owned()));
                }
            },
            ">" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.or_where_gt(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.or_where_gt(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!("> 后跟随的值不正确".to_owned()));
                }
            },
            "<" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.or_where_lt(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.or_where_lt(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!("< 后跟随的值不正确".to_owned()));
                }
            },
            ">=" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.or_where_ge(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.or_where_ge(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!(">= 后跟随的值不正确".to_owned()));
                }
            },
            "<=" => match values {
                TypeJsonValue::Number(f) => {
                    sql_builder.or_where_le(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.or_where_le(column, d);
                }
                _ => {
                    return Err(anyhow::anyhow!("<= 后跟随的值不正确".to_owned()));
                }
            },
            "!=" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.or_where_ne(column, s);
                }
                TypeJsonValue::Number(f) => {
                    sql_builder.or_where_ne(column, f);
                }
                TypeJsonValue::DateTime(d) => {
                    sql_builder.or_where_ne(column, d);
                }
                TypeJsonValue::Bool(d) => {
                    sql_builder.and_where_eq(column, d as u8);
                }
                _ => {
                    return Err(anyhow::anyhow!("!= 后跟随的值不正确".to_owned()));
                }
            },
            "BETWEEN" => match values {
                TypeJsonValue::Array(vals) => {
                    if vals.len() == 2 {
                        let start_value = vals.first();
                        let end_value = vals.get(1);

                        match (start_value, end_value) {
                            (
                                Some(TypeJsonValue::Number(start)),
                                Some(TypeJsonValue::Number(end)),
                            ) => {
                                sql_builder.or_where_between(column, start, end);
                            }
                            (
                                Some(TypeJsonValue::DateTime(start)),
                                Some(TypeJsonValue::DateTime(end)),
                            ) => {
                                sql_builder.or_where_between(column, start, end);
                            }
                            _ => {
                                return Err(anyhow::anyhow!("BETWEEN 后跟随的值不正确".to_owned()));
                            }
                        }
                    } else {
                        return Err(anyhow::anyhow!("BETWEEN 操作符需要两个值".to_owned()));
                    }
                }
                _ => {
                    return Err(anyhow::anyhow!("BETWEEN 操作符需要一个数组".to_owned()));
                }
            },
            "NOT_NULL" => {
                sql_builder.or_where_is_not_null(column);
            }
            "IS_NULL" => {
                sql_builder.or_where_is_null(column);
            }
            "IN" => match values {
                TypeJsonValue::Array(vals) => {
                    let in_values: Vec<String> = vals
                        .iter()
                        .filter_map(|v| {
                            if let TypeJsonValue::Number(s) = v {
                                let news = format!("{}", s);
                                Some(news)
                            } else if let TypeJsonValue::Str(s) = v {
                                let news = format!("'{}'", s);
                                Some(news)
                            } else {
                                None
                            }
                        })
                        .collect();
                    sql_builder.or_where_in(column, &in_values);
                }
                _ => {
                    return Err(anyhow::anyhow!("IN 后跟随的值不正确".to_owned()));
                }
            },
            "NOT_IN" => match values {
                TypeJsonValue::Array(vals) => {
                    let not_in_values: Vec<String> = vals
                        .iter()
                        .filter_map(|v| {
                            if let TypeJsonValue::Number(s) = v {
                                let news = format!("{}", s);
                                Some(news)
                            } else if let TypeJsonValue::Str(s) = v {
                                let news = format!("'{}'", s);
                                Some(news)
                            } else {
                                None
                            }
                        })
                        .collect();
                    sql_builder.or_where_not_in(column, &not_in_values);
                }
                _ => {
                    return Err(anyhow::anyhow!("NOT_IN 后跟随的值不正确".to_owned()));
                }
            },
            "NOT_BETWEEN" => match values {
                TypeJsonValue::Array(vals) => {
                    if vals.len() == 2 {
                        let start_value = vals.first();
                        let end_value = vals.get(1);

                        match (start_value, end_value) {
                            (
                                Some(TypeJsonValue::Number(start)),
                                Some(TypeJsonValue::Number(end)),
                            ) => {
                                sql_builder.or_where_not_between(column, start, end);
                            }
                            (
                                Some(TypeJsonValue::DateTime(start)),
                                Some(TypeJsonValue::DateTime(end)),
                            ) => {
                                sql_builder.or_where_not_between(column, start, end);
                            }
                            _ => {
                                return Err(anyhow::anyhow!("BETWEEN 后跟随的值不正确".to_owned()));
                            }
                        }
                    } else {
                        return Err(anyhow::anyhow!("BETWEEN 操作符需要两个值".to_owned()));
                    }
                }
                _ => {
                    return Err(anyhow::anyhow!("BETWEEN 操作符需要一个数组".to_owned()));
                }
            },

            "IN_QUERY" => match values {
                TypeJsonValue::Object(_) => {
                    // 获取子查询的 JSON 对象
                    if let Some(query) = conditions_item.get("values") {
                        // 构建 SQL 子查询
                        if let Ok(q) = select_builder_from_json(query, None, None) {
                            // 使用 and_where_in_query 插入子查询
                            sql_builder.or_where_in_query(column, q.query().unwrap());
                        } else {
                            return Err(anyhow::anyhow!("IN_QUERY 后跟随的值不正确".to_owned()));
                        }
                    } else {
                        return Err(anyhow::anyhow!("IN_QUERY 后跟随的值不正确".to_owned()));
                    }
                }
                _ => return Err(anyhow::anyhow!("IN_QUERY 后跟随的值不是对象".to_owned())),
            },

            "NOT_IN_QUERY" => match values {
                TypeJsonValue::Object(_) => {
                    // 获取子查询的 JSON 对象
                    if let Some(query) = conditions_item.get("values") {
                        // 构建 SQL 子查询
                        if let Ok(q) = select_builder_from_json(query, None, None) {
                            // 使用 or_where_not_in_query 插入子查询
                            sql_builder.or_where_not_in_query(column, q.query().unwrap());
                        } else {
                            return Err(
                                anyhow::anyhow!("NOT_IN_QUERY 后跟随的值不正确".to_owned()),
                            );
                        }
                    } else {
                        return Err(anyhow::anyhow!("NOTIN QUERY 后跟随的值不正确".to_owned()));
                    }
                }
                _ => return Err(anyhow::anyhow!("NOT_IN_QUERY 后跟随的值不是对象".to_owned())),
            },
            "LIKE" => match values {
                TypeJsonValue::Str(s) => {
                    sql_builder.or_where_like(column, s);
                }
                _ => {
                    return Err(anyhow::anyhow!("LIKE 后跟随的值不正确".to_owned()));
                }
            },

            _ => return Err(anyhow::anyhow!("SQL 不支持的条件操作符".to_owned())),
        },
        _ => return Err(anyhow::anyhow!("SQL 逻辑操作符需要 AND , OR".to_owned())),
    }

    Ok(())
}

// 构建select 的 SqlBuilder
pub fn select_builder_from_json(
    json_data: &JsonValue,
    table_name: Option<String>,
    add_cond: Option<String>,
) -> anyhow::Result<SqlBuilder> {
    let mut _name = String::new();

    if let Some(name) = table_name {
        _name = name;
    } else {
        _name = get_sql_field(json_data, "table").expect("必须有table");
    }
    is_valid_identifier(&_name, "表名")?;

    let mut sql_builder: SqlBuilder = SqlBuilder::select_from(_name);

    if let Some(columns) = json_data["columns"].as_array() {
        for column in columns {
            let f = column
                .as_str()
                .ok_or(anyhow::anyhow!("字段格式错误".to_owned()))?;

            is_valid_identifier(f, "字段名")?;

            sql_builder.field(format!("\"{}\"", f));
        }
    }
    if let Some(where_clause) = json_data["wheres"].as_array() {
        for conditions_item in where_clause {
            if conditions_item.is_object() {
                build_conditions_item(&mut sql_builder, conditions_item.clone())
                    .unwrap_or_else(|_| panic!("构建条件项错误{:#?}", conditions_item))
            }
        }
    }

    if let Some(cond) = add_cond {
        let c = add_cond_handle(&cond).unwrap();
        build_conditions_item(&mut sql_builder, c).unwrap()
    }

    if let Some(order_by) = json_data["order_by"].as_array() {
        for order in order_by {
            let column = order["column"]
                .as_str()
                .ok_or(anyhow::anyhow!("字段格式错误".to_owned()))?;
            let direction = order["direction"]
                .as_bool()
                .ok_or(anyhow::anyhow!("order_by 的direction 需要bool".to_owned()))?;
            sql_builder.order_by(column, direction);
        }
    }

    if let Some(group_by) = json_data["group_by"].as_array() {
        for group in group_by {
            sql_builder.group_by(group.as_str().unwrap());
        }
    }

    if let Some(offset) = json_data["offset"].as_number() {
        sql_builder.offset(offset);
    }

    if let Some(limit) = json_data["limit"].as_number() {
        sql_builder.limit(limit);
    }

    Ok(sql_builder)
}

// json_data: {data: || results:, }
pub fn insert_from_json(
    json_data: &JsonValue,
    table_name: Option<String>,
) -> anyhow::Result<String> {
    // let obj = json_data.as_object().unwrap();
    let mut _name = String::new();
    if let Some(name) = table_name {
        _name = name.to_owned();
    } else {
        _name = get_sql_field(json_data, "table").expect("insert_from_json必须有table");
    }
    is_valid_identifier(&_name, "表名")?;

    let binding = json!([]);
    let data = json_data
        .get("data")
        .or_else(|| json_data.get("results"))
        .unwrap_or(&binding);
    if let Some(arr) = data.as_array() {
        if arr.is_empty() {
            return Err(anyhow::anyhow!("没有数据"));
        }
        let columns: Vec<String> = arr[0]
            .as_object()
            .unwrap()
            .keys()
            .map(|key| {
                format!("\"{}\"", key) // Return a String instead of &str
            })
            .collect();
        let mut _values = vec![];
        let mut sql_builder = SqlBuilder::insert_into(_name);

        is_valid_identifiers(&columns, "字段名")?;

        sql_builder.fields(&columns);

        for a in arr {
            if let Some(j) = a.as_object() {
                _values = j
                    .values()
                    .map(|v| match v {
                        JsonValue::Object(obj) => match serde_json::to_string(obj) {
                            Ok(s) => format!("'{}'", s),
                            Err(_) => panic!("Failed to convert object to JSON string"),
                        },
                        JsonValue::Array(arr) => match serde_json::to_string(arr) {
                            Ok(s) => format!("'{}'", s),
                            Err(_) => panic!("Failed to convert array to JSON string"),
                        },
                        JsonValue::String(s) => format!("'{}'", escape_sql_string(s)),
                        JsonValue::Number(n) => n.to_string(),
                        JsonValue::Bool(b) => {
                            if *b {
                                "1".to_string()
                            } else {
                                "0".to_string()
                            }
                        }
                        JsonValue::Null => "NULL".to_string(),
                    })
                    .collect();
                sql_builder.values(&_values);
            }
        }

        return Ok(sql_builder.sql().ok().unwrap_or_default());
    };
    Err(anyhow::anyhow!("不能以数字开头"))
}

pub fn delete_from_json(
    json_data: &JsonValue,
    table_name: Option<String>,
    add_cond: Option<String>,
) -> anyhow::Result<String> {
    let mut _name = String::new();
    if let Some(name) = table_name {
        _name = name.to_owned();
    } else {
        _name = get_sql_field(json_data, "table").expect("必须有table");
    }

    is_valid_identifier(&_name, "表名")?;
    let mut sql_builder = SqlBuilder::delete_from(_name);

    if let Some(where_clause) = json_data["wheres"].as_array() {
        for conditions_item in where_clause {
            if conditions_item.is_object() {
                if let Err(e) = build_conditions_item(&mut sql_builder, conditions_item.clone()) {
                    eprintln!("Error building conditions: {}", e);
                }
            }
        }
    }
    // 如果有附加条件，添加到查询中
    if let Some(cond) = add_cond {
        let c = add_cond_handle(&cond).unwrap();
        build_conditions_item(&mut sql_builder, c)?;
    }

    let sql = sql_builder.sql().ok().unwrap_or_default();
    Ok(sql)
}

pub fn update_from_json(
    json_data: &JsonValue,
    table_name: Option<String>,
    add_cond: Option<String>,
) -> anyhow::Result<String> {
    let mut _name = String::new();

    if let Some(name) = table_name {
        _name = name.to_owned();
    } else {
        _name = get_sql_field(json_data, "table").expect("必须有table");
    }
    is_valid_identifier(&_name, "表名")?;

    let mut sql_builder = SqlBuilder::update_table(_name);
    // 处理更新的数据
    if let Some(obj) = json_data["data"].as_object() {
        for (key, val) in obj.iter() {
            is_valid_identifier(key, "字段名")?;
            let formatted_value = match val {
                JsonValue::Object(o) => {
                    let sql = serde_json::to_string(o).unwrap();
                    format!("'{}'", escape_sql_string(&sql))
                }
                JsonValue::Array(arr) => {
                    let sql = serde_json::to_string(arr).unwrap();
                    format!("'{}'", escape_sql_string(&sql))
                }
                JsonValue::String(s) => format!("'{}'", escape_sql_string(s)),
                JsonValue::Number(n) => n.to_string(),
                JsonValue::Bool(b) => {
                    if *b {
                        "1".to_string()
                    } else {
                        "0".to_string()
                    }
                }
                JsonValue::Null => "NULL".to_string(),
            };
            sql_builder.set(format!("\"{}\"", key), formatted_value);
        }
    }

    // 处理 WHERE 条件
    if let Some(where_clause) = json_data["wheres"].as_array() {
        for conditions_item in where_clause {
            if conditions_item.is_object() {
                if let Err(e) = build_conditions_item(&mut sql_builder, conditions_item.clone()) {
                    eprintln!("Error building conditions: {}", e);
                }
            }
        }
    }
    // 添加额外的条件
    if let Some(cond) = add_cond {
        let c = add_cond_handle(&cond).unwrap();
        build_conditions_item(&mut sql_builder, c)?;
    }

    // 返回生成的 SQL
    let sql = sql_builder.sql().ok().unwrap_or_default();
    Ok(sql)
}

pub fn select_from_json(
    json: &JsonValue,
    table_name: Option<String>,
    add_cond: Option<String>,
) -> Option<String> {
    match select_builder_from_json(json, table_name, add_cond) {
        Ok(sql) => sql.sql().ok(),
        Err(_) => None,
    }
}

// 用于大批量插入
pub fn insert_from_json_arr(json_arr: &Vec<JsonValue>, table_name: &str) -> anyhow::Result<String> {
    // 检查表名
    is_valid_identifier(table_name, "表名")?;
    if json_arr.is_empty() {
        return Err(anyhow::anyhow!("没有数据"));
    }
    let columns: Vec<String> = json_arr[0]
        .as_object()
        .unwrap()
        .keys()
        .map(|key| {
            format!("\"{}\"", key) // Return a String instead of &str
        })
        .collect();
    let mut _values = vec![];
    let mut sql_builder = SqlBuilder::insert_into(table_name);

    // 检查字段名
    is_valid_identifiers(&columns, "字段名")?;
    sql_builder.fields(&columns);

    for a in json_arr {
        if let Some(j) = a.as_object() {
            _values = j
                .values()
                .map(|v| match v {
                    JsonValue::Object(obj) => match serde_json::to_string(obj) {
                        Ok(s) => format!("'{}'", s),
                        Err(_) => panic!("Failed to convert object to JSON string"),
                    },
                    JsonValue::Array(arr) => match serde_json::to_string(arr) {
                        Ok(s) => format!("'{}'", s),
                        Err(_) => panic!("Failed to convert array to JSON string"),
                    },
                    JsonValue::String(s) => format!("'{}'", escape_sql_string(s)),
                    JsonValue::Number(n) => n.to_string(),
                    JsonValue::Bool(b) => {
                        if *b {
                            "1".to_string()
                        } else {
                            "0".to_string()
                        }
                    }
                    JsonValue::Null => "NULL".to_string(),
                })
                .collect();
            sql_builder.values(&_values);
        }
    }
    Ok(sql_builder.sql().ok().unwrap_or_default())
}