db-cores 0.1.0

use std::sync::LazyLock;
use chrono::{DateTime, NaiveDate, NaiveDateTime, NaiveTime, Utc};
use regex::Regex;
use serde::{Deserialize, Serialize};
use serde_json::{ Value as JsonValue};
use sqlx::{Database, Encode, QueryBuilder, Type};
use crate::common::{BuildConditionItem, Logical, Operator, SqlValue, WhereValue};

static REX_BIND_PLACEHOLDER: LazyLock<Regex> = LazyLock::new(|| Regex::new(r"(\?|\$\d+)").unwrap());
// chrono 类型	PostgreSQL 类型	备注 
// chrono::NaiveDate	DATE	只包含年月日（例如 2025-10-15）
// chrono::NaiveTime	TIME	只包含时分秒（例如 13:45:20）
// chrono::NaiveDateTime	TIMESTAMP	不包含时区
// chrono::DateTime<Utc>	TIMESTAMP WITH TIME ZONE	包含时区信息（存储时会转换为 UTC）
// chrono::DateTime<Local>	TIMESTAMP WITH TIME ZONE	插入时会转换为 UTC，读取时转换为本地时间


use crate::verify::{is_valid_identifier};

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct UpdateItem {
    pub column: String,
    pub value: SqlValue,
}

impl UpdateItem {
    pub fn from_value_t<T>(
        ins: T,
        ignore_fields: Option<Vec<String>>,
    ) -> anyhow::Result<Vec<UpdateItem>>
    where
        T: Serialize + Unpin + Send + 'static,
    {
        let value = serde_json::to_value(ins)?;
        let mut result = Vec::new();
        let ignores = ignore_fields.unwrap_or_default();
        if let Some(map) = value.as_object() {
            for (key, val) in map {
                if ignores.contains(key) {
                    continue;
                }
                let sql_value = value_to_sql_value(val)?;
                result.push(UpdateItem {
                    column: key.to_owned(),
                    value: sql_value,
                });
            }
        }
        Ok(result)
    }
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct InsertItems {
    pub columns: Vec<String>,
    pub values: Vec<Vec<SqlValue>>,
}

impl InsertItems {
    pub fn from_value_t<T>(ins: T) -> anyhow::Result<Self>
    where
        T: Serialize + Unpin + Send + 'static,
    {
        let value = serde_json::to_value(ins)?;
        let mut columns: Vec<String> = vec![];
        let mut values: Vec<SqlValue> = vec![];
        if let Some(row) = value.as_object() {
            columns = row.keys().map(|key| key.to_owned()).collect();
            values = row
                .values()
                .map(value_to_sql_value)
                .collect::<anyhow::Result<Vec<_>>>()?;
        };
        Ok(Self {
            columns,
            values: vec![values],
        })
    }
    pub fn from_value(json_value: &JsonValue) -> anyhow::Result<Self> {
        let mut columns: Vec<String> = vec![];
        let mut values: Vec<SqlValue> = vec![];
        if let Some(row) = json_value.as_object() {
            columns = row.keys().map(|key| key.to_owned()).collect();
            values = row
                .values()
                .map(value_to_sql_value)
                .collect::<anyhow::Result<Vec<_>>>()?;
        };
        Ok(Self {
            columns,
            values: vec![values],
        })
    }

    pub fn to_fields(json_value: &JsonValue) -> anyhow::Result<Vec<String>> {
        if let Some(row) = json_value.as_object() {
            let columns = row.keys().map(|key| key.to_owned()).collect();
            // .map(|key| {
            //     format!("\"{}\"", key) // Return a String instead of &str
            // })
            // .collect();
            return Ok(columns);
        };
        Err(anyhow::anyhow!("to_fields-> Invalid JSON value"))
    }
    pub fn to_values(json_value: &JsonValue) -> anyhow::Result<Vec<SqlValue>> {
        if let Some(row) = json_value.as_object() {
            let values = row
                .values()
                .map(value_to_sql_value)
                .collect::<anyhow::Result<Vec<_>>>()?;
            return Ok(values);
        };
        Ok(vec![])
    }

    pub fn from_json_arr(json_arr: &Vec<JsonValue>) -> anyhow::Result<Self> {
        if json_arr.is_empty() {
            return Err(anyhow::anyhow!("json_arr is empty"));
        }
        let mut values: Vec<Vec<SqlValue>> = vec![];
        let columns = InsertItems::to_fields(&json_arr[0])?;
        for value in json_arr {
            let r: Vec<SqlValue> = InsertItems::to_values(value)?;
            values.push(r);
        }
        Ok(Self { columns, values })
    }

    pub fn from_json_value(json_value: &JsonValue, columns: &[String]) -> anyhow::Result<Self> {
        let v: Vec<SqlValue> = InsertItems::to_values(json_value)?;
        Ok(Self {
            columns: columns.to_vec(),
            values: vec![v],
        })
    }
}

// #[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
// pub enum WhereValue {
//     Value(SqlValue),
//     List(Vec<SqlValue>),
// }

#[derive(Debug, Serialize, Deserialize, PartialEq, Clone)]
pub struct OrderItem {
    pub column: String,
    pub direction: bool,
}

#[derive(Debug, PartialEq, Clone, Serialize, Deserialize)]
pub struct SelectQuery {
    pub table_name: Option<String>,
    pub columns: Option<Vec<String>>, // 返回的字段
    pub wheres: Option<Vec<BuildConditionItem>>,
    pub order_by: Option<Vec<OrderItem>>, // 排序字段
    pub limit: Option<i64>,               // 返回记录数
    pub offset: Option<i64>,              // 偏移量

    pub db_name: Option<String>,
    pub connect_tag: Option<String>,
}

impl SelectQuery {
    pub fn new(table_name: &str, wheres: &Option<Vec<BuildConditionItem>>) -> Self {
        Self {
            table_name: Some(table_name.to_string()),
            columns: None,
            wheres: wheres.clone(),
            order_by: None,
            limit: None,
            offset: None,
            db_name: None,
            connect_tag: None,
        }
    }
}

#[derive(Debug, PartialEq, Clone, Serialize, Deserialize)]
pub struct InsertQurey {
    pub table_name: Option<String>,
    pub data: InsertItems,
    pub schema: Option<String>,
    pub connect_tag: Option<String>,
    pub db_name: Option<String>,
}

#[derive(Debug, PartialEq, Clone, Serialize, Deserialize)]
pub struct UpdateQuery {
    pub table_name: Option<String>,
    pub wheres: Option<Vec<BuildConditionItem>>,
    pub data: Vec<UpdateItem>,
    pub connect_tag: Option<String>,
    pub db_name: Option<String>,
}

#[derive(Debug, PartialEq, Clone, Serialize, Deserialize)]
pub struct DeleteQuery {
    pub table_name: Option<String>,
    pub wheres: Option<Vec<BuildConditionItem>>,
    pub ids: Option<Vec<String>>,
    pub id: Option<String>,

    pub connect_tag: Option<String>,
    pub db_name: Option<String>,
}

#[derive(Debug, PartialEq, Eq, Clone, Serialize, Deserialize)]
pub struct GetQuery {
    pub query: String,
}

impl GetQuery {
    pub fn parse(params_into_inner: JsonValue) -> SelectQuery {
        let query = serde_json::from_value::<GetQuery>(params_into_inner).unwrap();
        serde_json::from_str::<SelectQuery>(&query.query).unwrap()
    }
    pub fn parse_inner(&self) -> anyhow::Result<SelectQuery> {
        let res: SelectQuery = serde_json::from_str::<SelectQuery>(&self.query)?;
        Ok(res)
    }
}




pub fn push_sql_value<DB>(
    query: &mut QueryBuilder<DB>, // ✅ 显式写上两个 `'a`
    value: SqlValue,
)
// -> &'a mut QueryBuilder<'a, DB>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + Type<DB>,
    for<'c> i64: Encode<'c, DB> + Type<DB>,
    for<'c> f64: Encode<'c, DB> + Type<DB>,
    for<'c> String: Encode<'c, DB> + Type<DB>,
    for<'c> bool: Encode<'c, DB> + Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    match value {
        SqlValue::Num(v) => query.push_bind(v),
        SqlValue::Float(v) => query.push_bind(v),
        SqlValue::Str(v) => query.push_bind(v),
        SqlValue::Bool(v) => query.push_bind(v),
        SqlValue::Null => query.push("NULL"),
        SqlValue::Buff(v) => query.push_bind(v),
        SqlValue::UtcTime(v) => {
            if DB::NAME == "PostgreSQL" {
                query.push_bind(v)
            } else {
                query.push_bind(v.to_string())
            }
        }
        SqlValue::Time(naive_time) => {
            if DB::NAME == "PostgreSQL" {
                query.push_bind(naive_time)
            } else {
                query.push_bind(naive_time.to_string())
            }
        }
        SqlValue::Date(naive_date) => {
            if DB::NAME == "PostgreSQL" {
                query.push_bind(naive_date)
            } else {
                query.push_bind(naive_date.to_string())
            }
        }
        SqlValue::DateTime(naive_date_time) => {
            if DB::NAME == "PostgreSQL" {
                query.push_bind(naive_date_time)
            } else {
                query.push_bind(naive_date_time.to_string())
            }
        }
        SqlValue::Json(value) => query.push_bind(value),
    };

    // query
}

pub fn push_separated_value<DB>(
    separated: &mut sqlx::query_builder::Separated<'_, '_, DB, &'static str>,
    value: SqlValue,
)
// -> &'a mut QueryBuilder<'a, DB>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + Type<DB>,
    for<'c> i64: Encode<'c, DB> + Type<DB>,
    for<'c> f64: Encode<'c, DB> + Type<DB>,
    for<'c> String: Encode<'c, DB> + Type<DB>,
    for<'c> bool: Encode<'c, DB> + Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    match value {
        SqlValue::Num(v) => separated.push_bind(v),
        SqlValue::Float(v) => separated.push_bind(v),
        SqlValue::Str(v) => separated.push_bind(v),
        SqlValue::Bool(v) => separated.push_bind(v),
        SqlValue::Null => separated.push("NULL"),
        SqlValue::Buff(v) => separated.push_bind(v),
        SqlValue::UtcTime(v) => {
            if DB::NAME == "PostgreSQL" {
                separated.push_bind(v)
            } else {
                separated.push_bind(v.to_string())
            }
        }
        SqlValue::Time(v) => {
            if DB::NAME == "PostgreSQL" {
                separated.push_bind(v)
            } else {
                separated.push_bind(v.to_string())
            }
        }
        SqlValue::Date(naive_date) => {
            if DB::NAME == "PostgreSQL" {
                separated.push_bind(naive_date)
            } else {
                separated.push_bind(naive_date.to_string())
            }
        }
        SqlValue::DateTime(naive_date_time) => {
            if DB::NAME == "PostgreSQL" {
                separated.push_bind(naive_date_time)
            } else {
                separated.push_bind(naive_date_time.to_string())
            }
        }
        SqlValue::Json(value) => separated.push_bind(value),
    };

    // query
}

pub fn delete_build<'a, DB>(
    table_name: &str,
    wheres: Option<Vec<BuildConditionItem>>,
) -> anyhow::Result<QueryBuilder<'a, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("DELETE FROM ");
    query.push(format!("\"{}\"", &table_name.trim_matches('"')));

    where_build::<DB>(&mut query, wheres)?;

    let sql = query.sql();
    println!("创建的sql: {}", sql);
    Ok(query)
}

pub fn update_build<'a, DB>(
    table_name: &str,
    wheres: Option<Vec<BuildConditionItem>>,
    values: Vec<UpdateItem>,
    ignore_columns: Option<Vec<String>>,
) -> anyhow::Result<QueryBuilder<'a, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("UPDATE ");
    query
        .push(format!("\"{}\"", &table_name.trim_matches('"')))
        .push(" SET ");
    // 先过滤忽略字段，再计算长度，避免尾逗号错误
    let ignores = ignore_columns.unwrap_or_default();
    let values: Vec<UpdateItem> = values
        .into_iter()
        .filter(|f| !ignores.contains(&f.column))
        .collect();
    let items_len = values.len();
    for (index, f) in values.into_iter().enumerate() {
        is_valid_identifier(&f.column, "字段名")?;
        query.push(format!("\"{}\"", f.column)).push(" = ");
        push_sql_value(&mut query, f.value);
        if index < items_len - 1 {
            query.push(", ");
        }
    }

    where_build::<DB>(&mut query, wheres)?;

    let sql = query.sql();
    println!("创建的sql: {}", sql);
    Ok(query)
}



pub fn update_build_res<'a, DB>(
    table_name: &str,
    wheres: Option<Vec<BuildConditionItem>>,
    values: Vec<UpdateItem>,
    ignore_columns: Option<Vec<String>>,
) -> anyhow::Result<QueryBuilder<'a, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("UPDATE ");
    query
        .push(format!("\"{}\"", &table_name.trim_matches('"')))
        .push(" SET ");
    // 先过滤忽略字段，再计算长度，避免尾逗号错误
    let ignores = ignore_columns.unwrap_or_default();
    let values: Vec<UpdateItem> = values
        .into_iter()
        .filter(|f| !ignores.contains(&f.column))
        .collect();
    let items_len = values.len();
    for (index, f) in values.into_iter().enumerate() {
        is_valid_identifier(&f.column, "字段名")?;
        query.push(format!("\"{}\"", f.column)).push(" = ");
        push_sql_value(&mut query, f.value);
        if index < items_len - 1 {
            query.push(", ");
        }
    }

    where_build::<DB>(&mut query, wheres)?;

    query.push(" RETURNING *");

    let sql = query.sql();
    println!("创建的sql: {}", sql);
    Ok(query)
}

pub fn insert_build<'a, DB>(
    table_name: &str,
    values: InsertItems,
) -> anyhow::Result<QueryBuilder<'a, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> chrono::DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("INSERT INTO ");
    query
        .push(format!("\"{}\"", &table_name.trim_matches('"')))
        .push("(");

    let mut separated = query.separated(", ");

    for f in values.columns.iter() {
        is_valid_identifier(f, "字段名")?;
        separated.push(format!("\"{}\"", &f));
    }

    separated.push_unseparated(") ");

    query.push_values(
        values.values,
        |mut b: sqlx::query_builder::Separated<'_, '_, DB, &'static str>, value| {
            for i in value.into_iter() {
                push_separated_value(&mut b, i);
            }
        },
    );
    // 如果需要添加 ON CONFLICT / DUPLICATE KEY 语句
    let sql = query.sql();
    println!("创建的sql: {}", sql);
    Ok(query)
}

pub fn insert_or_update_build<'a, DB>(
    table_name: &str,
    values: InsertItems,
) -> anyhow::Result<QueryBuilder<'a, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("INSERT INTO ");
    // query.push(table_name).push("(");
    query
        .push(format!("\"{}\"", &table_name.trim_matches('"')))
        .push("(");

    let mut separated = query.separated(", ");

    for f in values.columns.iter() {
        is_valid_identifier(f, "字段名")?;
        // separated.push(f);
        separated.push(format!("\"{}\"", f));
    }

    separated.push_unseparated(") ");

    let update_values: Vec<SqlValue> = if values.values.len() == 1 {
        values.values[0].clone()
    } else {
        return Err(anyhow::anyhow!(
            "Unsupported DB type for upsert: {}",
            DB::NAME
        ));
    };

    query.push_values(values.values, |mut b, value| {
        for i in value.into_iter() {
            push_separated_value(&mut b, i);
        }
    });
    // 如果需要添加 ON CONFLICT / DUPLICATE KEY 语句
    println!("DB::NAME:{}", DB::NAME);
    if DB::NAME == "MySql" {
        query.push(" ON DUPLICATE KEY UPDATE ");
    } else if DB::NAME == "SQLite" || DB::NAME == "Postgres" {
        query.push(" ON CONFLICT( id ) DO UPDATE SET ");
    } else {
        return Err(anyhow::anyhow!(
            "Unsupported DB type for upsert: {}",
            DB::NAME
        ));
    }
    let keys_len = values.columns.len();
    for (index, f) in values.columns.iter().enumerate() {
        query.push(format!("\"{}\"", &f)).push(" = ");
        push_sql_value(&mut query, update_values[index].clone());
        if index < keys_len - 1 {
            query.push(", ");
        }
    }
    let sql = query.sql();
    println!("insert_or_update_build 创建的sql: {}", sql);
    Ok(query)
}

pub fn select_build<'a, DB>(
    table_name: &str,
    options: SelectQuery,
) -> anyhow::Result<QueryBuilder<'_, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let SelectQuery {
        columns,
        offset,
        wheres,
        table_name: _talbe_name,
        order_by,
        limit,
        ..
    } = options;

    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("SELECT ");
    if let Some(fields_vec) = columns {
        let mut separated = query.separated(", ");
        for column in fields_vec.iter() {
            is_valid_identifier(column.as_ref(), "字段名")?;
            // separated.push(column);
            separated.push(format!("\"{}\"", &column));
        }
    } else {
        query.push("*");
    }

    // query.push(" FROM ").push(table_name);
    query
        .push(" FROM ")
        .push(format!("\"{}\"", table_name.trim_matches('"')));
    // 如果有where条件，则构建where条件
    where_build::<DB>(&mut query, wheres)?;
    if let Some(orderby) = order_by {
        if !orderby.is_empty() {
            query.push(" ORDER BY ");
            let mut separated = query.separated(",");
            for o in orderby.iter() {
                is_valid_identifier(&o.column, "字段名")?;
                let order_str =
                    // format!(" {} {}", o.column, if o.direction { "DESC" } else { "ASC" });
                    format!("\"{}\" {}", o.column, if o.direction { "DESC" } else { "ASC" });
                separated.push(order_str);
            }
        }
    }

    if let Some(v) = limit {
        query.push(" LIMIT ").push(v);
    }

    if let Some(v) = offset {
        query.push(" OFFSET ").push(v);
    }

    let sql = query.sql();
    println!("创建的sql: {}", sql);
    Ok(query)
}

pub fn select_build_two<'a, DB>(
    table_name: &str,
    options: SelectQuery,
) -> anyhow::Result<(QueryBuilder<'_, DB>, QueryBuilder<'_, DB>)>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    is_valid_identifier(table_name, "表名")?;
    let SelectQuery {
        columns,
        offset,
        wheres,
        table_name: _talbe_name,
        order_by,
        limit,
        ..
    } = options;

    let mut query_select: QueryBuilder<'_, DB> = QueryBuilder::new("SELECT ");
    let mut query_count: QueryBuilder<'_, DB> = QueryBuilder::new("SELECT COUNT(*) FROM ");

    if let Some(fields_vec) = columns {
        let mut separated = query_select.separated(", ");
        for column in fields_vec.iter() {
            is_valid_identifier(column.as_ref(), "字段名")?;
            separated.push(format!("\"{}\"", &column));
        }
    } else {
        query_select.push("*");
    }

    // query.push(" FROM ").push(table_name);
    query_select
        .push(" FROM ")
        .push(format!("\"{}\"", table_name.trim_matches('"')));
    // 如果有where条件，则构建where条件
    query_count.push(format!("\"{}\"", table_name.trim_matches('"')));

    where_build::<DB>(&mut query_select, wheres.clone())?;
    where_build::<DB>(&mut query_count, wheres)?;

    if let Some(orderby) = order_by {
        if !orderby.is_empty() {
            query_select.push(" ORDER BY ");
            let mut separated = query_select.separated(",");
            for o in orderby.iter() {
                is_valid_identifier(&o.column, "字段名")?;
                let order_str =
                    // format!(" {} {}", o.column, if o.direction { "DESC" } else { "ASC" });
                    format!("\"{}\" {}", o.column, if o.direction { "DESC" } else { "ASC" });
                separated.push(order_str);
            }
        }
    }

    if let Some(v) = limit {
        query_select.push(" LIMIT ").push(v);
    }

    if let Some(v) = offset {
        query_select.push(" OFFSET ").push(v);
    }

    let sql = query_select.sql();
    println!("创建的sql: {}", sql);
    Ok((query_select, query_count))
}

pub fn where_build<'a, DB>(
    query: &mut QueryBuilder<'_, DB>,
    wheres: Option<Vec<BuildConditionItem>>,
) -> anyhow::Result<()>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    if let Some(conds) = wheres {
        if conds.is_empty() {
            return Ok(());
        }
        query.push(" WHERE ");
        let conds_len = conds.len();
        for (index, c) in conds.into_iter().enumerate() {
            // 检查操作符,以及字段名是否合法
            is_valid_identifier(&c.column, "字段名")?;
            // query.push(c.column);
            query.push(format!("\"{}\"", &c.column));

            match c.values {
                // 多个条件值的情况
                WhereValue::List(list) => {
                    if c.operator == Operator::In || c.operator == Operator::NotIn {
                        let op = c.operator.to_string().replace("_", " ");
                        query.push(" ").push(&op).push(" (");
                        let mut separated = query.separated(", ");
                        for v in list {
                            push_separated_value(&mut separated, v);
                        }
                        separated.push_unseparated(")");
                    } else if (c.operator == Operator::Between
                        || c.operator == Operator::NotBetween)
                        && list.len() == 2
                    {
                        let op = c.operator.to_string().replace("_", " ");
                        query.push(" ").push(&op).push(" ");
                        let value1 = list[0].clone();
                        let value2 = list[1].clone();
                        push_sql_value(query, value1);
                        query.push(" AND ");
                        push_sql_value(query, value2);
                    } else {
                        return Err(anyhow::format_err!("Invalid 条件值不合法"));
                    }
                }
                // 一个条件值的情况
                WhereValue::Value(v) => {
                    let mut op = c.operator.to_string().replace("_", " ");
                    let mut _is_like_any = if op == "LIKE ANY" {
                        op = "LIKE".to_owned();
                        true
                    } else {
                        false
                    };
                    query.push(" ").push(&op).push(" ");
                    push_sql_value(query, v);
                }
            };
            if index < conds_len - 1 {
                let logc = if c.logical == Logical::And {
                    " AND "
                } else {
                    " OR "
                };
                query.push(logc);
            }
        }
    }

    Ok(())
}

pub fn select_build_json_count<'a, DB>(
    table_name: &str,
    options: SelectQuery,
) -> anyhow::Result<QueryBuilder<'_, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    println!("创建的sql1");
    is_valid_identifier(table_name, "表名")?;
    let SelectQuery {
        columns,
        offset,
        wheres,
        table_name: _talbe_name,
        order_by,
        limit,
        ..
    } = options;
    let mut query: QueryBuilder<'_, DB> = QueryBuilder::new("WITH filtered_data AS ( SELECT ");

    if let Some(fields_vec) = columns {
        let mut separated = query.separated(", ");
        for column in fields_vec.iter() {
            is_valid_identifier(column.as_ref(), "字段名")?;
            separated.push(format!("\"{}\"", column));
        }
    } else {
        query.push("*");
    }

    // query.push(" FROM ").push(table_name);
    query
        .push(" FROM ")
        .push(format!("\"{}\"", table_name.trim_matches('"')));
    // 如果有where条件，则构建where条件
    where_build::<DB>(&mut query, wheres.clone())?;

    query.push("), total_count AS (SELECT COUNT(*) AS count FROM filtered_data) SELECT  row_to_json(t.*) AS data, c.count FROM (SELECT * FROM  filtered_data");

    if let Some(orderby) = order_by {
        if !orderby.is_empty() {
            query.push(" ORDER BY ");
            let mut separated = query.separated(",");
            for o in orderby.iter() {
                is_valid_identifier(&o.column, "字段名")?;
                let order_str = format!(
                    "\"{}\" {}",
                    o.column,
                    if o.direction { "DESC" } else { "ASC" }
                );
                separated.push(order_str);
            }
        }
    }
    if let Some(v) = limit {
        query.push(" LIMIT ").push(v);
    }

    if let Some(v) = offset {
        query.push(" OFFSET ").push(v);
    }

    query.push(") AS t CROSS JOIN total_count AS c");
    // 开始添加 COUNT(*)
    // query.push(") AS t CROSS JOIN (SELECT COUNT(*) as count");
    // // query.push(" FROM ").push(table_name);
    // query.push(" FROM ").push(format!("\"{}\"", table_name));
    // // 条件还是要添加到 COUNT中
    // let _ = where_build::<DB>(&mut query, wheres)?;
    // query.push(") AS c");

    let sql = query.sql();
    println!("创建的sql: {}", sql);

    Ok(query)
}

pub fn build_bind_sql<'a, DB>(sql: &str, params: Vec<SqlValue>) -> anyhow::Result<QueryBuilder<'a, DB>>
where
    DB: Database,
    for<'c> &'c str: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> i64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> f64: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> String: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> bool: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> Vec<u8>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> DateTime<Utc>: Encode<'c, DB> + sqlx::Type<DB>,
    for<'c> NaiveDate: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveTime: Encode<'c, DB> + Type<DB>,
    for<'c> NaiveDateTime: Encode<'c, DB> + Type<DB>,
    for<'c> JsonValue: Encode<'c, DB> + Type<DB>,
{
    // 按照 ? 拆分 sql 文本
    let re = &*REX_BIND_PLACEHOLDER;
    // let parts: Vec<&str> = sql.split('?').collect();
    let parts: Vec<&str> = re.split(sql).collect();
    let param_len = params.len();
    // 由于 split('?') 会把最后一段保留，因此如果有 n 个 ?，会有 n+1 个 parts
    if parts.len() != param_len + 1 {
        return Err(anyhow::format_err!(
            "参数数量与 SQL 中 ? 的数量不匹配: 发现 {} 个 ?, 但提供了 {} 个参数",
            parts.len() - 1,
            param_len
        ));
    }

    let mut builder: QueryBuilder<'a, DB> = QueryBuilder::new("");

    for (i, part) in parts.into_iter().enumerate() {
        builder.push(part);
        if i < param_len {
            let value = params[i].clone();
            match value {
                SqlValue::Str(v) => builder.push_bind(v),
                SqlValue::Num(v) => builder.push_bind(v),
                SqlValue::Float(v) => builder.push_bind(v),
                SqlValue::Bool(v) => builder.push_bind(v),
                SqlValue::Null => builder.push_bind("NULL"),
                SqlValue::Json(v) => builder.push_bind(v.to_string()),
                SqlValue::Date(v) =>builder.push_bind(v),
                SqlValue::DateTime(v) =>builder.push_bind(v),
                SqlValue::UtcTime(v) =>builder.push_bind(v),
                SqlValue::Time(v) =>builder.push_bind(v),
                SqlValue::Buff(v)=>builder.push_bind(v)
            };
        }
    }

    println!("builder_sql: {}", builder.sql());
    Ok(builder)
}


pub fn value_to_sql_value(v: &JsonValue) -> anyhow::Result<SqlValue> {
    match v {
        JsonValue::Object(obj) => serde_json::to_string(obj)
            .map(SqlValue::Str)
            .map_err(|e| anyhow::anyhow!("Failed to serialize JSON object: {e}")),
        JsonValue::Array(arr) => serde_json::to_string(arr)
            .map(SqlValue::Str)
            .map_err(|e| anyhow::anyhow!("Failed to serialize JSON array: {e}")),
        JsonValue::String(s) => Ok(SqlValue::Str(s.clone())),
        JsonValue::Number(n) => {
            if n.is_f64() {
                Ok(SqlValue::Float(n.as_f64().unwrap()))
            } else if n.is_i64() {
                Ok(SqlValue::Num(n.as_i64().unwrap()))
            } else if n.is_u64() {
                Ok(SqlValue::Num(n.as_u64().unwrap() as i64))
            } else {
                unreachable!("JsonNumber 不可能不是 i64/u64/f64") // unreachable 表示不可能到这里来
            }
        }
        JsonValue::Bool(b) => {
            Ok(SqlValue::Bool(*b))
            // if *b {
            //     Ok(SqlValue::Num(1))
            // } else {
            //     Ok(SqlValue::Num(0))
            // }
        }
        _ => Ok(SqlValue::Null),
    }
}

#[test]
fn rea() {
    // let columns = Some(vec!["selectb", "age", "updated_at"]);
    // let age = 18.;
    // let str1 = String::from("1900-01-01");
    // let orderby = Some(orderby!("name", true, "age", false));
    // let offset = Some(10);
    // let limit = Some(26);
    // let wheres = Some(wheres!("name" != 12, "age" = age, "updated_at" = str1));
    // let _ = select_build_json_count::<Postgres>("table_name",wheres,columns,orderby,limit,offset).unwrap();
}

// Rust 宏片段类型（Fragment Specifiers）大全
// 片段类型	作用/匹配内容	例子匹配
// ident	标识符，比如变量名、函数名、结构体名	foo, bar, MyStruct
// path	路径，比如模块路径、类型路径	std::io::Result, crate::foo
// expr	表达式，可以是复杂表达式或简单字面量	42, a + b, foo(1, 2)
// ty	类型	u32, String, Vec<i32>
// pat	模式匹配，比如 match 中的模式	Some(x), _, (a, b)
// stmt	语句	let x = 5;, println!("Hi");
// block	代码块	{ let x = 1; x + 2 }
// item	顶层项，比如函数、结构体定义	fn foo() {}, struct Foo;
// meta	元属性，比如 #[derive(Debug)]	derive(Debug), allow(unused)
// tt	Token Tree 单个或复合标记，能匹配操作符、符号、标点等	=, +, {}, ;, foo!()
// literal	字面量，比如数字、字符串、布尔值	"hello", 123, true, 'c'
// vis	可见性修饰符，比如 pub	pub, pub(crate)
// macro	宏调用	println!("Hi")
// format	格式字符串	"{} {}", a, b
// tt* 和 tt+	零个或多个（*）或者一个或多个（+）Token Tree	{ ... }、( ... )