use regex::Regex;
use serde::{ Serialize};
use serde_json::{to_value, Value as JsonValue};


pub fn get_table_name(sql: &str) -> Option<String> {
    let reg = Regex::new(r#"(?i)\bFROM\b\s+['"`]?[a-zA-Z0-9_]+['"`]?\b"#).unwrap();
    let mut last_table_name: Option<String> = None;
    let reg2 = Regex::new(r#"'|"|`"#).unwrap();
    for mat in reg.find_iter(sql) {
        if let Some(table_name) = mat.as_str().split_whitespace().nth(1) {
            let table_name = reg2.replace_all(table_name, "");
            last_table_name = Some(table_name.to_string());
        }
    }
    last_table_name
}

pub fn get_update_table_name(sql: &str) -> Option<String> {
    let reg = Regex::new(r#"(?i)\bFROM\b\s+['"`]?[a-zA-Z0-9_]+['"`]?\b"#).unwrap();
    let mut last_table_name: Option<String> = None;
    let reg2 = Regex::new(r#"'|"|`"#).unwrap();
    for mat in reg.find_iter(sql) {
        if let Some(table_name) = mat.as_str().split_whitespace().nth(1) {
            let table_name = reg2.replace_all(table_name, "");
            last_table_name = Some(table_name.to_string());
        }
    }
    last_table_name
}

pub fn generate_insert_sql<T: Serialize>(instance: &T, table_name: &str) -> String {
    let json_data: JsonValue = to_value(instance).unwrap();
    let columns: Vec<String> = json_data
        .as_object()
        .unwrap()
        .keys()
        .map(|k| format!("\"{}\"", k.trim_matches('"')))
        .collect();

    let values: Vec<String> = json_data
        .as_object()
        .unwrap()
        .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!("'{}'", s),
            JsonValue::Number(n) => n.to_string(),
            JsonValue::Bool(b) => b.to_string(),
            JsonValue::Null => "NULL".to_string(),
        })
        .collect();

    format!(
        "INSERT INTO {} ({}) VALUES ({});",
        table_name,
        columns.join(", "),
        values.join(", ")
    )
}

pub fn generate_delete_sql(table_name: &str, ids: Vec<String>, add_cond: Option<String>) -> String {
    if ids.is_empty() {
        panic!("IDs list cannot be empty");
    }
    let ids = ids.join("', '"); // 将 IDs 转换成逗号分隔的字符串
    if let Some(cond) = add_cond {
        format!(
            "DELETE FROM {} WHERE id IN ('{}') AND {}",
            table_name, ids, cond
        ) // 构建 SQL DELETE 语句
    } else {
        format!("DELETE FROM {} WHERE id IN ('{}')", table_name, ids) // 构建 SQL DELETE 语句
    }
}

pub fn generate_insert_or_replace_sql<T: Serialize>(instance: &T, table_name: &str) -> String {
    // 这里重点是,这个是插入或替换,而不是插入或更新
    let json_data: JsonValue = to_value(instance).unwrap();
    let columns: Vec<String> = json_data
        .as_object()
        .unwrap()
        .keys()
        .map(|k| format!("\"{}\"", k))
        .collect();

    let values: Vec<String> = json_data
        .as_object()
        .unwrap()
        .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!("'{}'", s),
            JsonValue::Number(n) => n.to_string(),
            JsonValue::Bool(b) => b.to_string(),
            JsonValue::Null => "NULL".to_string(),
        })
        .collect();

    format!(
        "INSERT OR REPLACE INTO {} ({}) VALUES ({});",
        table_name,
        columns.join(", "),
        values.join(", ")
    )
}

pub fn generate_select_sql(table_name: &str, columns: &[&str], where_clause: &str) -> String {
    let mut select_clause = "SELECT ".to_string();

    if columns.is_empty() {
        select_clause.push('*');
    } else {
        select_clause.push_str(&columns.join(", "));
    }

    let mut sql = select_clause + &format!(" FROM {}", table_name);

    if !where_clause.is_empty() {
        sql.push_str(&format!(" WHERE {}", where_clause));
    }
    println!("generate_select_sql:{}", sql);
    sql
}

pub fn generate_update_sql<T: Serialize>(
    // 这是根据一个struct 生成upadate
    instance: &T,
    table_name: &str,
    where_clause: &str,
) -> String {
    let json_data: JsonValue = to_value(instance).unwrap();
    let mut set_clause = String::new();
    json_data
        .as_object()
        .unwrap()
        .iter()
        .filter(|(key, _)| *key != "id") // 过滤掉键名为 "id" 的字段
        .for_each(|(key, value)| {
            let formatted_value = match value {
                JsonValue::Object(obj) => serde_json::to_string(obj).unwrap(),
                JsonValue::Array(arr) => serde_json::to_string(arr).unwrap(),
                JsonValue::String(s) => format!("'{}'", s),
                JsonValue::Number(n) => n.to_string(),
                JsonValue::Bool(b) => b.to_string(),
                JsonValue::Null => "NULL".to_string(),
            };
            set_clause.push_str(&format!("{} = {}, ", key, formatted_value));
        });
    // 在处理完所有字段后
    set_clause = set_clause.trim_end_matches(", ").to_string(); // 清除最后面的 ", "
    format!(
        "UPDATE {} SET {} WHERE {};",
        table_name, set_clause, where_clause
    )
}
