use json::{object, JsonValue};

static DISABLE_FIELD: &[&str] = &[
    "default",
    "select",
    "delete",
    "insert",
    "update",
    "order",
    "group",
    "user",
    "password",
    "desc",
    "index",
    "from",
    "host",
    "user",
    "read",
    "partition",
];

/// SQL injection prevention - security critical module
pub mod sql_safety {
    static SQL_KEYWORDS: &[&str] = &[
        "select", "insert", "update", "delete", "drop", "truncate", "alter", "create", "exec",
        "execute", "union",
    ];

    static DANGEROUS_PATTERNS: &[&str] = &["--", "/*", "*/", ";", "xp_", "sp_", "0x"];

    #[inline]
    pub fn validate_table_name(name: &str) -> bool {
        if name.is_empty() || name.len() > 128 {
            return false;
        }
        let bytes = name.as_bytes();
        let first = bytes[0];
        if !first.is_ascii_alphabetic() && first != b'_' {
            return false;
        }
        for &b in bytes {
            if !b.is_ascii_alphanumeric() && b != b'_' {
                return false;
            }
        }
        let lower = name.to_lowercase();
        if SQL_KEYWORDS.iter().any(|kw| lower == *kw) {
            return false;
        }
        !DANGEROUS_PATTERNS.iter().any(|p| lower.contains(p))
    }

    #[inline]
    pub fn validate_field_name(name: &str) -> bool {
        if name.is_empty() || name.len() > 256 {
            return false;
        }
        for part in name.split('.') {
            if part.is_empty() {
                return false;
            }
            let bytes = part.as_bytes();
            let first = bytes[0];
            if !first.is_ascii_alphabetic() && first != b'_' {
                return false;
            }
            for &b in bytes {
                if !b.is_ascii_alphanumeric() && b != b'_' {
                    return false;
                }
            }
        }
        let lower = name.to_lowercase();
        if SQL_KEYWORDS.iter().any(|kw| lower == *kw) {
            return false;
        }
        !DANGEROUS_PATTERNS.iter().any(|p| lower.contains(p))
    }

    #[inline]
    pub fn escape_string(value: &str) -> String {
        value
            .replace('\\', "\\\\")
            .replace('\'', "''")
            .replace('\0', "")
            .replace('\n', "\\n")
            .replace('\r', "\\r")
    }

    #[inline]
    pub fn validate_compare_orator(op: &str) -> bool {
        matches!(
            op.to_lowercase().as_str(),
            "=" | "!="
                | "<>"
                | "<"
                | ">"
                | "<="
                | ">="
                | "like"
                | "not like"
                | "in"
                | "not in"
                | "notin"
                | "between"
                | "is"
                | "isnot"
                | "is not"
                | "set"
                | "location"
                | "notlike"
                | "json_contains"
        )
    }

    #[cfg(test)]
    mod tests {
        use super::*;

        #[test]
        fn test_validate_table_name_valid() {
            assert!(validate_table_name("users"));
            assert!(validate_table_name("user_profiles"));
            assert!(validate_table_name("_private_table"));
            assert!(validate_table_name("Table123"));
            assert!(validate_table_name("batch_insert_perf"));
            assert!(validate_table_name("my_update_log"));
        }

        #[test]
        fn test_validate_table_name_invalid() {
            assert!(!validate_table_name(""));
            assert!(!validate_table_name("123table"));
            assert!(!validate_table_name("user-name"));
            assert!(!validate_table_name("table.name"));
            assert!(!validate_table_name("table name"));
        }

        #[test]
        fn test_validate_table_name_sql_keywords() {
            assert!(!validate_table_name("select"));
            assert!(!validate_table_name("SELECT"));
            assert!(!validate_table_name("insert"));
            assert!(!validate_table_name("drop"));
            assert!(!validate_table_name("union"));
        }

        #[test]
        fn test_validate_table_name_dangerous_patterns() {
            assert!(!validate_table_name("table;drop"));
            assert!(!validate_table_name("table--comment"));
            assert!(!validate_table_name("xp_cmdshell"));
            assert!(!validate_table_name("sp_execute"));
        }

        #[test]
        fn test_validate_table_name_length() {
            let long_name = "a".repeat(128);
            assert!(validate_table_name(&long_name));
            let too_long = "a".repeat(129);
            assert!(!validate_table_name(&too_long));
        }

        #[test]
        fn test_validate_field_name_valid() {
            assert!(validate_field_name("id"));
            assert!(validate_field_name("user_name"));
            assert!(validate_field_name("_hidden"));
            assert!(validate_field_name("table1.field1"));
        }

        #[test]
        fn test_validate_field_name_invalid() {
            assert!(!validate_field_name(""));
            assert!(!validate_field_name("123field"));
            assert!(!validate_field_name("field-name"));
            assert!(!validate_field_name(".field"));
            assert!(!validate_field_name("field."));
        }

        #[test]
        fn test_escape_string() {
            assert_eq!(escape_string("hello"), "hello");
            assert_eq!(escape_string("it's"), "it''s");
            assert_eq!(escape_string("new\nline"), "new\\nline");
            assert_eq!(escape_string("carriage\rreturn"), "carriage\\rreturn");
        }

        #[test]
        fn test_escape_string_sql_injection() {
            assert_eq!(
                escape_string("'; DROP TABLE users; --"),
                "''; DROP TABLE users; --"
            );
            assert_eq!(escape_string("1' OR '1'='1"), "1'' OR ''1''=''1");
        }

        #[test]
        fn test_validate_compare_operator_valid() {
            assert!(validate_compare_orator("="));
            assert!(validate_compare_orator("!="));
            assert!(validate_compare_orator("like"));
            assert!(validate_compare_orator("LIKE"));
            assert!(validate_compare_orator("in"));
            assert!(validate_compare_orator("between"));
        }

        #[test]
        fn test_validate_compare_operator_invalid() {
            assert!(!validate_compare_orator(""));
            assert!(!validate_compare_orator("invalid"));
            assert!(!validate_compare_orator("=="));
            assert!(!validate_compare_orator("&&"));
        }
    }
}

#[inline]
pub fn quote_identifier(name: &str, mode: &str) -> String {
    match mode {
        "mysql" | "sqlite" => format!("`{}`", name),
        "pgsql" => format!("\"{}\"", name),
        "mssql" => format!("[{}]", name),
        _ => name.to_string(),
    }
}

#[cfg(feature = "db-mssql")]
pub mod mssql;
#[cfg(feature = "db-mysql")]
pub mod mysql;
#[cfg(feature = "db-mysql")]
pub mod mysql_transaction;
#[cfg(feature = "db-pgsql")]
pub mod pgsql;
#[cfg(feature = "db-pgsql")]
pub mod pgsql_transaction;
#[cfg(feature = "db-sqlite")]
pub mod sqlite;
#[cfg(feature = "db-sqlite")]
pub mod sqlite_transaction;

pub trait DbMode {
    /// 数据库是否存在
    fn database_tables(&mut self) -> JsonValue;
    /// 创建数据库
    fn database_create(&mut self, name: &str) -> bool;
    /// 备份
    fn backups(&mut self, _filename: &str) -> bool {
        false
    }
    fn truncate(&mut self, table: &str) -> bool;
}

#[derive(Debug, Clone)]
pub struct TableOptions {
    table_name: String,
    table_title: String,
    table_key: String,
    table_fields: JsonValue,
    table_unique: Vec<String>,
    table_index: Vec<Vec<String>>,
    table_partition: bool,
    table_partition_columns: JsonValue,
}
impl TableOptions {
    pub fn set_table_name(&mut self, name: &str) {
        self.table_name = name.to_string()
    }
    pub fn set_table_title(&mut self, name: &str) {
        self.table_title = name.to_string()
    }
    pub fn set_table_key(&mut self, name: &str) {
        self.table_key = name.to_string()
    }
    pub fn set_table_fields(&mut self, fields: JsonValue) {
        self.table_fields = fields;
    }
    pub fn set_table_unique(&mut self, unique: Vec<&str>) {
        self.table_unique = unique.iter().map(|s| s.to_string()).collect();
    }
    pub fn set_table_index(&mut self, index: Vec<Vec<&str>>) {
        self.table_index = index
            .iter()
            .map(|s| s.iter().map(|s| s.to_string()).collect())
            .collect();
    }
    pub fn set_table_partition(&mut self, index: bool) {
        self.table_partition = index;
    }
    pub fn set_table_partition_columns(&mut self, index: JsonValue) {
        self.table_partition_columns = index;
    }
}
impl Default for TableOptions {
    fn default() -> Self {
        Self {
            table_name: "".to_string(),
            table_title: "".to_string(),
            table_key: "".to_string(),
            table_fields: JsonValue::Null,
            table_unique: vec![],
            table_index: vec![],
            table_partition: false,
            table_partition_columns: JsonValue::Null,
        }
    }
}
pub trait Mode: DbMode {
    fn table_create(&mut self, options: TableOptions) -> JsonValue;
    fn table_update(&mut self, options: TableOptions) -> JsonValue;
    /// 获取表信息
    fn table_info(&mut self, table: &str) -> JsonValue;
    /// 判断表是否存在
    fn table_is_exist(&mut self, name: &str) -> bool;
    /// 当前表
    fn table(&mut self, name: &str) -> &mut Self;
    /// 别名
    fn change_table(&mut self, name: &str) -> &mut Self;

    /// 主键自增
    fn autoinc(&mut self) -> &mut Self;
    /// 自动时间戳
    fn timestamps(&mut self) -> &mut Self;
    /// 返回sql语句
    fn fetch_sql(&mut self) -> &mut Self;
    /// 排序
    fn order(&mut self, field: &str, by: bool) -> &mut Self;
    /// 分组
    fn group(&mut self, field: &str) -> &mut Self;
    /// 消除重复记录
    fn distinct(&mut self) -> &mut Self;
    /// JSON 字段
    fn json(&mut self, field: &str) -> &mut Self;
    /// location 字段
    fn location(&mut self, field: &str) -> &mut Self;
    /// 显示字段
    fn field(&mut self, field: &str) -> &mut Self;
    /// 原始表达式字段（不加表前缀，如 "SUM(amount) as total"、"CASE WHEN ... END as status_count"）
    fn field_raw(&mut self, expr: &str) -> &mut Self;
    /// 隐藏字段
    fn hidden(&mut self, name: &str) -> &mut Self;
    /// 查询
    fn where_and(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
    fn where_or(&mut self, field: &str, compare: &str, value: JsonValue) -> &mut Self;
    /// 原始 WHERE 条件（不加表前缀，如 "YEAR(created_at) = 2026"）
    fn where_raw(&mut self, expr: &str) -> &mut Self;

    /// 子查询 WHERE field IN (sub_sql)
    fn where_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self;
    /// 子查询 WHERE field NOT IN (sub_sql)
    fn where_not_in_sub(&mut self, field: &str, sub_sql: &str) -> &mut Self;

    fn where_exists(&mut self, sub_sql: &str) -> &mut Self;
    fn where_not_exists(&mut self, sub_sql: &str) -> &mut Self;

    /// 比较两个列
    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self;
    /// 更新指定列
    fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self;
    /// 分页
    fn page(&mut self, page: i32, limit: i32) -> &mut Self;
    /// 独立限制返回条数（不带分页偏移）
    fn limit(&mut self, count: i32) -> &mut Self;
    /// 查询指定列并返回数组
    fn column(&mut self, field: &str) -> JsonValue;
    /// 总数量
    fn count(&mut self) -> JsonValue;
    /// 最大值
    fn max(&mut self, field: &str) -> JsonValue;
    /// 最小值
    fn min(&mut self, field: &str) -> JsonValue;
    /// 合计
    fn sum(&mut self, field: &str) -> JsonValue;
    /// 平均值
    fn avg(&mut self, field: &str) -> JsonValue;
    /// HAVING 条件（用于 GROUP BY 后过滤，如 "COUNT(*) > 5"）
    fn having(&mut self, expr: &str) -> &mut Self;
    /// 获取集合
    fn select(&mut self) -> JsonValue;
    /// 获取单记录
    fn find(&mut self) -> JsonValue;
    /// 获取单一列值
    fn value(&mut self, field: &str) -> JsonValue;
    /// 添加
    fn insert(&mut self, data: JsonValue) -> JsonValue;
    /// 批量添加
    fn insert_all(&mut self, data: JsonValue) -> JsonValue;

    /// 插入或更新（冲突时更新）
    fn upsert(&mut self, data: JsonValue, conflict_fields: Vec<&str>) -> JsonValue;

    /// 更新
    fn update(&mut self, data: JsonValue) -> JsonValue;
    /// 批量更新
    fn update_all(&mut self, data: JsonValue) -> JsonValue;
    /// 删除
    fn delete(&mut self) -> JsonValue;

    /// 事务开始
    fn transaction(&mut self) -> bool;
    /// 事务提交
    fn commit(&mut self) -> bool;
    /// 事务回滚
    fn rollback(&mut self) -> bool;
    /// sql语句执行
    fn sql(&mut self, sql: &str) -> Result<JsonValue, String>;
    fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String>;
    /// 自增
    fn inc(&mut self, field: &str, num: f64) -> &mut Self;
    /// 自减
    fn dec(&mut self, field: &str, num: f64) -> &mut Self;

    /// 构造子查询
    fn buildsql(&mut self) -> String;

    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self;
    /// 连结
    /// * main_table 主表表名
    /// * main_fields 主表字段
    /// * right_table 关联表名
    /// * right_fields 关联字段名
    fn join(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self;

    /// 内连接--用来取交集
    ///
    /// * table 链表表名
    /// * main_fields 主表字段
    /// * second_fields 附表关联字段
    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self;

    /// 右连接
    fn join_right(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self;

    /// 全连接
    fn join_full(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self;

    fn union(&mut self, sub_sql: &str) -> &mut Self;
    fn union_all(&mut self, sub_sql: &str) -> &mut Self;
    fn lock_for_update(&mut self) -> &mut Self;
    fn lock_for_share(&mut self) -> &mut Self;
}

#[derive(Clone, Debug)]
pub struct Params {
    pub mode: String,
    pub autoinc: bool,
    pub table: String,
    pub where_and: Vec<String>,
    pub where_or: Vec<String>,
    pub where_column: String,
    pub update_column: Vec<String>,
    pub inc_dec: JsonValue,
    pub page: i32,
    pub limit: i32,
    pub fields: JsonValue,
    pub top: String,
    pub top2: String,
    pub order: JsonValue,
    pub group: JsonValue,
    pub distinct: bool,
    pub json: JsonValue,
    pub location: JsonValue,
    pub sql: bool,
    pub join: Vec<String>,
    pub join_inner: Vec<String>,
    pub join_table: String,
    pub having: Vec<String>,
    /// 独立 LIMIT（不带分页偏移）
    pub limit_only: i32,
    pub timestamps: bool,
    pub unions: Vec<String>,
    pub lock_mode: String,
}

impl Params {
    pub fn default(mode: &str) -> Self {
        Self {
            mode: mode.to_string(),
            autoinc: false,
            table: "".to_string(),
            where_and: vec![],
            where_or: vec![],
            where_column: "".to_string(),
            update_column: vec![],
            inc_dec: object! {},
            page: -1,
            limit: 10,
            fields: object! {},
            top: String::new(),
            top2: String::new(),
            order: object! {},
            group: object! {},
            distinct: false,
            json: object! {},
            location: object! {},
            sql: false,
            join: Vec::new(),
            join_inner: Vec::new(),
            join_table: "".to_string(),
            having: vec![],
            limit_only: -1,
            timestamps: false,
            unions: vec![],
            lock_mode: "".to_string(),
        }
    }
    pub fn where_sql(&mut self) -> String {
        let mut where_and_sql = vec![];
        let mut where_or_sql = vec![];
        let mut sql = vec![];

        for item in self.where_or.iter() {
            where_or_sql.push(item.clone());
        }
        if !where_or_sql.is_empty() {
            sql.push(format!(" ( {} ) ", where_or_sql.join(" OR ")));
        }

        for item in self.where_and.iter() {
            where_and_sql.push(item.clone());
        }
        if !where_and_sql.is_empty() {
            sql.push(where_and_sql.join(" AND "));
        }

        if !self.where_column.is_empty() {
            sql.push(self.where_column.clone());
        }

        if !sql.is_empty() {
            return format!("WHERE {}", sql.join(" AND "));
        }
        "".to_string()
    }
    pub fn page_limit_sql(&mut self) -> String {
        if self.page == -1 {
            if self.limit_only > 0 {
                return format!("LIMIT {}", self.limit_only);
            }
            return "".to_string();
        }
        match self.mode.as_str() {
            "mysql" => {
                format!(
                    "LIMIT {},{}",
                    self.page * self.limit - self.limit,
                    self.limit
                )
            }
            "sqlite" | "pgsql" => {
                format!(
                    "LIMIT {} OFFSET {}",
                    self.limit,
                    self.page * self.limit - self.limit
                )
            }
            _ => "".to_string(),
        }
    }
    pub fn fields(&mut self) -> String {
        let mut fields = vec![];
        for (_, value) in self.fields.entries() {
            match self.mode.as_str() {
                "mssql" => {
                    fields.push(format!("{value}"));
                }
                "mysql" => {
                    if let Some(s) = value.as_str() {
                        if DISABLE_FIELD.contains(&s) {
                            fields.push(format!("`{value}`"));
                        } else {
                            fields.push(format!("{value}"));
                        }
                    } else {
                        fields.push(format!("{value}"));
                    }
                }
                _ => {
                    fields.push(format!("{value}"));
                }
            }
        }
        let fields = {
            if fields.is_empty() {
                "*".into()
            } else {
                fields.join(",")
            }
        };
        match self.mode.as_str() {
            "mysql" => fields.to_string(),
            "sqlite" => fields.to_string(),
            "mssql" => fields.to_string(),
            _ => fields.to_string(),
        }
    }
    pub fn top(&mut self) -> String {
        match self.mode.as_str() {
            "mssql" => {
                let wheres = self.where_sql();
                if !self.top2.is_empty() {
                    let order = self.order();
                    if order.is_empty() {
                        self.top = format!(
                            "(select ROW_NUMBER() OVER(ORDER BY rand()) as ROW,* from {} {}) as ",
                            self.table, wheres
                        );
                    } else {
                        self.top = format!(
                            "(select ROW_NUMBER() OVER({}) as ROW,* from {} {}) as ",
                            order, self.table, wheres
                        );
                    }
                    return self.top.to_string();
                }
                self.top.to_string()
            }
            _ => "".to_string(),
        }
    }
    pub fn top2(&mut self) -> String {
        match self.mode.as_str() {
            "mssql" => {
                if self.where_and.is_empty() && self.where_or.is_empty() && !self.top2.is_empty() {
                    return format!("where {}", self.top2);
                }
                if (!self.where_and.is_empty() || !self.where_or.is_empty())
                    && !self.top2.is_empty()
                {
                    return format!("AND {}", self.top2);
                }
                self.top2.to_string()
            }
            _ => "".to_string(),
        }
    }
    pub fn table(&mut self) -> String {
        match self.mode.as_str() {
            "mssql" => {
                if !self.top2.is_empty() {
                    return "t".to_string();
                }
                self.table.to_string()
            }
            _ => self.table.to_string(),
        }
    }
    pub fn join(&mut self) -> String {
        match self.mode.as_str() {
            "mssql" => self.join.join(" "),
            _ => self.join.join(" "),
        }
    }

    // 当前只匹配了MySQL
    pub fn join_inner(&mut self) -> String {
        match self.mode.as_str() {
            "mysql" => {
                let mut join_inner = "".to_string();
                for item in self.join_inner.iter() {
                    join_inner = format!("{join_inner} {item}");
                }
                join_inner.to_string()
            }
            _ => "".to_string(),
        }
    }

    pub fn order(&mut self) -> String {
        let mut sql = vec![];
        for (field, item) in self.order.entries() {
            match self.mode.as_str() {
                "mssql" => {
                    if DISABLE_FIELD.contains(&field) {
                        sql.push(format!("[{field}] {item}"));
                    } else {
                        sql.push(format!("{field} {item}"));
                    }
                }

                "pgsql" => {
                    if DISABLE_FIELD.contains(&field) {
                        sql.push(format!("\"{field}\" {item}"));
                    } else {
                        sql.push(format!("{field} {item}"));
                    }
                }
                _ => {
                    if DISABLE_FIELD.contains(&field) {
                        sql.push(format!("`{field}` {item}"));
                    } else {
                        sql.push(format!("{field} {item}"));
                    }
                }
            }
        }
        if !sql.is_empty() {
            return format!("ORDER BY {}", sql.join(","));
        }
        "".to_string()
    }
    pub fn group(&mut self) -> String {
        let mut sql = vec![];
        for (_, field) in self.group.entries() {
            let field_str = field.to_string();
            if field_str.contains(".") {
                sql.push(format!("{}", field));
            } else {
                match self.mode.as_str() {
                    "pgsql" => {
                        if DISABLE_FIELD.contains(&&*field_str) {
                            sql.push(format!("\"{field}\""));
                        } else {
                            sql.push(format!("{}.{}", self.table, field));
                        }
                    }
                    "mssql" => {
                        if DISABLE_FIELD.contains(&&*field_str) {
                            sql.push(format!("[{field}]"));
                        } else {
                            sql.push(format!("{}.{}", self.table, field));
                        }
                    }
                    _ => {
                        if DISABLE_FIELD.contains(&&*field_str) {
                            sql.push(format!("`{field}`"));
                        } else {
                            sql.push(format!("{}.{}", self.table, field));
                        }
                    }
                }
            }
        }
        if !sql.is_empty() {
            return format!("GROUP BY {}", sql.join(","));
        }
        "".to_string()
    }
    pub fn having(&mut self) -> String {
        if self.having.is_empty() {
            return "".to_string();
        }
        format!("HAVING {}", self.having.join(" AND "))
    }
    pub fn distinct(&self) -> String {
        if self.distinct {
            "DISTINCT".to_string()
        } else {
            "".to_string()
        }
    }
    pub fn select_sql(&mut self) -> String {
        let base = format!(
            "SELECT {} {} FROM {} {} {} {} {} {} {} {} {} {}",
            self.distinct(),
            self.fields(),
            self.top(),
            self.table(),
            self.join(),
            self.join_inner(),
            self.where_sql(),
            self.top2(),
            self.group(),
            self.having(),
            self.order(),
            self.page_limit_sql()
        );
        if self.unions.is_empty() {
            if self.lock_mode.is_empty() {
                base
            } else {
                format!("{base} {}", self.lock_mode)
            }
        } else {
            let unions = self.unions.join(" ");
            if self.lock_mode.is_empty() {
                format!("{base} {unions}")
            } else {
                format!("{base} {unions} {}", self.lock_mode)
            }
        }
    }
}

#[cfg(test)]
mod params_tests {
    use super::*;
    use json::object;

    #[test]
    fn test_quote_identifier_mysql() {
        assert_eq!(quote_identifier("name", "mysql"), "`name`");
        assert_eq!(quote_identifier("user", "mysql"), "`user`");
    }

    #[test]
    fn test_quote_identifier_sqlite() {
        assert_eq!(quote_identifier("name", "sqlite"), "`name`");
        assert_eq!(quote_identifier("order", "sqlite"), "`order`");
    }

    #[test]
    fn test_quote_identifier_pgsql() {
        assert_eq!(quote_identifier("name", "pgsql"), "\"name\"");
        assert_eq!(quote_identifier("select", "pgsql"), "\"select\"");
    }

    #[test]
    fn test_quote_identifier_mssql() {
        assert_eq!(quote_identifier("name", "mssql"), "[name]");
        assert_eq!(quote_identifier("index", "mssql"), "[index]");
    }

    #[test]
    fn test_quote_identifier_unknown() {
        assert_eq!(quote_identifier("name", "unknown"), "name");
        assert_eq!(quote_identifier("field", ""), "field");
    }

    #[test]
    fn test_table_options_default() {
        let opts = TableOptions::default();
        assert_eq!(opts.table_name, "");
        assert_eq!(opts.table_title, "");
        assert_eq!(opts.table_key, "");
        assert!(opts.table_fields.is_null());
        assert!(opts.table_unique.is_empty());
        assert!(opts.table_index.is_empty());
        assert!(!opts.table_partition);
        assert!(opts.table_partition_columns.is_null());
    }

    #[test]
    fn test_table_options_set_table_name() {
        let mut opts = TableOptions::default();
        opts.set_table_name("users");
        assert_eq!(opts.table_name, "users");
    }

    #[test]
    fn test_table_options_set_table_title() {
        let mut opts = TableOptions::default();
        opts.set_table_title("User Table");
        assert_eq!(opts.table_title, "User Table");
    }

    #[test]
    fn test_table_options_set_table_key() {
        let mut opts = TableOptions::default();
        opts.set_table_key("id");
        assert_eq!(opts.table_key, "id");
    }

    #[test]
    fn test_table_options_set_table_fields() {
        let mut opts = TableOptions::default();
        let fields = object! { "name" => "varchar", "age" => "int" };
        opts.set_table_fields(fields);
        assert_eq!(opts.table_fields["name"], "varchar");
        assert_eq!(opts.table_fields["age"], "int");
    }

    #[test]
    fn test_table_options_set_table_unique() {
        let mut opts = TableOptions::default();
        opts.set_table_unique(vec!["email", "username"]);
        assert_eq!(
            opts.table_unique,
            vec!["email".to_string(), "username".to_string()]
        );
    }

    #[test]
    fn test_table_options_set_table_index() {
        let mut opts = TableOptions::default();
        opts.set_table_index(vec![vec!["name", "age"], vec!["email"]]);
        assert_eq!(opts.table_index.len(), 2);
        assert_eq!(
            opts.table_index[0],
            vec!["name".to_string(), "age".to_string()]
        );
        assert_eq!(opts.table_index[1], vec!["email".to_string()]);
    }

    #[test]
    fn test_table_options_set_table_partition() {
        let mut opts = TableOptions::default();
        assert!(!opts.table_partition);
        opts.set_table_partition(true);
        assert!(opts.table_partition);
    }

    #[test]
    fn test_table_options_set_table_partition_columns() {
        let mut opts = TableOptions::default();
        let cols = object! { "col1" => "range", "col2" => "hash" };
        opts.set_table_partition_columns(cols);
        assert_eq!(opts.table_partition_columns["col1"], "range");
        assert_eq!(opts.table_partition_columns["col2"], "hash");
    }

    #[test]
    fn test_params_default_mysql() {
        let p = Params::default("mysql");
        assert_eq!(p.mode, "mysql");
        assert!(!p.autoinc);
        assert_eq!(p.table, "");
        assert!(p.where_and.is_empty());
        assert!(p.where_or.is_empty());
        assert_eq!(p.where_column, "");
        assert!(p.update_column.is_empty());
        assert!(p.inc_dec.is_object());
        assert_eq!(p.page, -1);
        assert_eq!(p.limit, 10);
        assert!(p.fields.is_object());
        assert_eq!(p.top, "");
        assert_eq!(p.top2, "");
        assert!(p.order.is_object());
        assert!(p.group.is_object());
        assert!(!p.distinct);
        assert!(p.json.is_object());
        assert!(p.location.is_object());
        assert!(!p.sql);
        assert!(p.join.is_empty());
        assert!(p.join_inner.is_empty());
        assert_eq!(p.join_table, "");
    }

    #[test]
    fn test_params_default_pgsql() {
        let p = Params::default("pgsql");
        assert_eq!(p.mode, "pgsql");
        assert_eq!(p.page, -1);
        assert_eq!(p.limit, 10);
    }

    #[test]
    fn test_params_default_mssql() {
        let p = Params::default("mssql");
        assert_eq!(p.mode, "mssql");
    }

    #[test]
    fn test_params_default_sqlite() {
        let p = Params::default("sqlite");
        assert_eq!(p.mode, "sqlite");
    }

    #[test]
    fn test_where_sql_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.where_sql(), "");
    }

    #[test]
    fn test_where_sql_only_and() {
        let mut p = Params::default("mysql");
        p.where_and.push("a = 1".to_string());
        p.where_and.push("b = 2".to_string());
        assert_eq!(p.where_sql(), "WHERE a = 1 AND b = 2");
    }

    #[test]
    fn test_where_sql_only_or() {
        let mut p = Params::default("mysql");
        p.where_or.push("a = 1".to_string());
        p.where_or.push("b = 2".to_string());
        assert_eq!(p.where_sql(), "WHERE  ( a = 1 OR b = 2 ) ");
    }

    #[test]
    fn test_where_sql_and_plus_or() {
        let mut p = Params::default("mysql");
        p.where_and.push("c = 3".to_string());
        p.where_or.push("a = 1".to_string());
        p.where_or.push("b = 2".to_string());
        let result = p.where_sql();
        assert!(result.starts_with("WHERE"));
        assert!(result.contains("( a = 1 OR b = 2 )"));
        assert!(result.contains("c = 3"));
        assert!(result.contains(" AND "));
    }

    #[test]
    fn test_where_sql_with_where_column() {
        let mut p = Params::default("mysql");
        p.where_column = "x.id = y.id".to_string();
        assert_eq!(p.where_sql(), "WHERE x.id = y.id");
    }

    #[test]
    fn test_where_sql_all_three() {
        let mut p = Params::default("mysql");
        p.where_and.push("a = 1".to_string());
        p.where_or.push("b = 2".to_string());
        p.where_or.push("c = 3".to_string());
        p.where_column = "x.id = y.id".to_string();
        let result = p.where_sql();
        assert!(result.starts_with("WHERE"));
        assert!(result.contains("( b = 2 OR c = 3 )"));
        assert!(result.contains("a = 1"));
        assert!(result.contains("x.id = y.id"));
    }

    #[test]
    fn test_page_limit_sql_default_page_neg1() {
        let mut p = Params::default("mysql");
        assert_eq!(p.page_limit_sql(), "");
    }

    #[test]
    fn test_page_limit_sql_mysql() {
        let mut p = Params::default("mysql");
        p.page = 1;
        p.limit = 10;
        assert_eq!(p.page_limit_sql(), "LIMIT 0,10");
    }

    #[test]
    fn test_page_limit_sql_mysql_page2() {
        let mut p = Params::default("mysql");
        p.page = 2;
        p.limit = 20;
        assert_eq!(p.page_limit_sql(), "LIMIT 20,20");
    }

    #[test]
    fn test_page_limit_sql_sqlite() {
        let mut p = Params::default("sqlite");
        p.page = 1;
        p.limit = 10;
        assert_eq!(p.page_limit_sql(), "LIMIT 10 OFFSET 0");
    }

    #[test]
    fn test_page_limit_sql_sqlite_page3() {
        let mut p = Params::default("sqlite");
        p.page = 3;
        p.limit = 5;
        assert_eq!(p.page_limit_sql(), "LIMIT 5 OFFSET 10");
    }

    #[test]
    fn test_page_limit_sql_unknown_mode() {
        let mut p = Params::default("unknown");
        p.page = 1;
        p.limit = 10;
        assert_eq!(p.page_limit_sql(), "");
    }

    #[test]
    fn test_fields_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.fields(), "*");
    }

    #[test]
    fn test_fields_mysql_with_disable_field() {
        let mut p = Params::default("mysql");
        p.fields = object! { "f1" => "order", "f2" => "name" };
        let result = p.fields();
        assert!(result.contains("`order`"));
        assert!(result.contains("name"));
        assert!(!result.contains("`name`"));
    }

    #[test]
    fn test_fields_mysql_normal() {
        let mut p = Params::default("mysql");
        p.fields = object! { "f1" => "id", "f2" => "name" };
        let result = p.fields();
        assert!(result.contains("id"));
        assert!(result.contains("name"));
    }

    #[test]
    fn test_fields_mssql() {
        let mut p = Params::default("mssql");
        p.fields = object! { "f1" => "id", "f2" => "name" };
        let result = p.fields();
        assert!(result.contains("id"));
        assert!(result.contains("name"));
    }

    #[test]
    fn test_fields_other_mode() {
        let mut p = Params::default("pgsql");
        p.fields = object! { "f1" => "id", "f2" => "email" };
        let result = p.fields();
        assert!(result.contains("id"));
        assert!(result.contains("email"));
    }

    #[test]
    fn test_top_non_mssql() {
        let mut p = Params::default("mysql");
        assert_eq!(p.top(), "");
    }

    #[test]
    fn test_top_mssql_without_top2() {
        let mut p = Params::default("mssql");
        p.table = "users".to_string();
        assert_eq!(p.top(), "");
    }

    #[test]
    fn test_top_mssql_with_top2() {
        let mut p = Params::default("mssql");
        p.table = "users".to_string();
        p.top2 = "ROW BETWEEN 1 AND 10".to_string();
        let result = p.top();
        assert!(result.contains("ROW_NUMBER"));
        assert!(result.contains("users"));
        assert!(result.contains("as "));
    }

    #[test]
    fn test_top_mssql_with_top2_and_order() {
        let mut p = Params::default("mssql");
        p.table = "users".to_string();
        p.top2 = "ROW BETWEEN 1 AND 10".to_string();
        p.order = object! { "id" => "ASC" };
        let result = p.top();
        assert!(result.contains("ROW_NUMBER"));
        assert!(result.contains("ORDER BY"));
        assert!(result.contains("id ASC"));
    }

    #[test]
    fn test_top2_non_mssql() {
        let mut p = Params::default("mysql");
        p.top2 = "something".to_string();
        assert_eq!(p.top2(), "");
    }

    #[test]
    fn test_top2_mssql_no_where_with_top2() {
        let mut p = Params::default("mssql");
        p.top2 = "ROW BETWEEN 1 AND 10".to_string();
        let result = p.top2();
        assert_eq!(result, "where ROW BETWEEN 1 AND 10");
    }

    #[test]
    fn test_top2_mssql_with_where_and_top2() {
        let mut p = Params::default("mssql");
        p.where_and.push("a = 1".to_string());
        p.top2 = "ROW BETWEEN 1 AND 10".to_string();
        let result = p.top2();
        assert_eq!(result, "AND ROW BETWEEN 1 AND 10");
    }

    #[test]
    fn test_top2_mssql_empty_top2() {
        let mut p = Params::default("mssql");
        assert_eq!(p.top2(), "");
    }

    #[test]
    fn test_table_mssql_with_top2() {
        let mut p = Params::default("mssql");
        p.table = "users".to_string();
        p.top2 = "ROW BETWEEN 1 AND 10".to_string();
        assert_eq!(p.table(), "t");
    }

    #[test]
    fn test_table_mssql_without_top2() {
        let mut p = Params::default("mssql");
        p.table = "users".to_string();
        assert_eq!(p.table(), "users");
    }

    #[test]
    fn test_table_other_modes() {
        let mut p = Params::default("mysql");
        p.table = "orders".to_string();
        assert_eq!(p.table(), "orders");

        let mut p2 = Params::default("pgsql");
        p2.table = "products".to_string();
        assert_eq!(p2.table(), "products");
    }

    #[test]
    fn test_join_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.join(), "");
    }

    #[test]
    fn test_join_with_entries() {
        let mut p = Params::default("mysql");
        p.join
            .push("LEFT JOIN orders ON users.id = orders.user_id".to_string());
        p.join
            .push("LEFT JOIN items ON orders.id = items.order_id".to_string());
        let result = p.join();
        assert!(result.contains("LEFT JOIN orders ON users.id = orders.user_id"));
        assert!(result.contains("LEFT JOIN items ON orders.id = items.order_id"));
        assert!(result.contains(" "));
    }

    #[test]
    fn test_join_mssql() {
        let mut p = Params::default("mssql");
        p.join.push("JOIN t2 ON t1.id = t2.fk".to_string());
        let result = p.join();
        assert_eq!(result, "JOIN t2 ON t1.id = t2.fk");
    }

    #[test]
    fn test_join_inner_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.join_inner(), "");
    }

    #[test]
    fn test_join_inner_mysql() {
        let mut p = Params::default("mysql");
        p.join_inner
            .push("INNER JOIN orders ON users.id = orders.uid".to_string());
        p.join_inner
            .push("INNER JOIN items ON orders.id = items.oid".to_string());
        let result = p.join_inner();
        assert!(result.contains("INNER JOIN orders ON users.id = orders.uid"));
        assert!(result.contains("INNER JOIN items ON orders.id = items.oid"));
    }

    #[test]
    fn test_join_inner_non_mysql() {
        let mut p = Params::default("pgsql");
        p.join_inner
            .push("INNER JOIN orders ON users.id = orders.uid".to_string());
        assert_eq!(p.join_inner(), "");

        let mut p2 = Params::default("mssql");
        p2.join_inner
            .push("INNER JOIN orders ON users.id = orders.uid".to_string());
        assert_eq!(p2.join_inner(), "");
    }

    #[test]
    fn test_order_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.order(), "");
    }

    #[test]
    fn test_order_with_entries() {
        let mut p = Params::default("mysql");
        p.order = object! { "id" => "ASC", "name" => "DESC" };
        let result = p.order();
        assert!(result.starts_with("ORDER BY"));
        assert!(result.contains("id ASC"));
        assert!(result.contains("name DESC"));
    }

    #[test]
    fn test_order_mysql_disable_field() {
        let mut p = Params::default("mysql");
        p.order = object! { "order" => "ASC" };
        let result = p.order();
        assert!(result.contains("`order` ASC"));
    }

    #[test]
    fn test_order_pgsql_disable_field() {
        let mut p = Params::default("pgsql");
        p.order = object! { "order" => "DESC" };
        let result = p.order();
        assert!(result.contains("\"order\" DESC"));
    }

    #[test]
    fn test_order_mssql_disable_field() {
        let mut p = Params::default("mssql");
        p.order = object! { "order" => "ASC" };
        let result = p.order();
        assert!(result.contains("[order] ASC"));
    }

    #[test]
    fn test_group_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.group(), "");
    }

    #[test]
    fn test_group_with_entries() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.group = object! { "g1" => "status" };
        let result = p.group();
        assert!(result.starts_with("GROUP BY"));
        assert!(result.contains("users.status"));
    }

    #[test]
    fn test_group_disable_field() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.group = object! { "g1" => "order" };
        let result = p.group();
        assert!(result.contains("`order`"));
    }

    #[test]
    fn test_group_dotted_field() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.group = object! { "g1" => "orders.status" };
        let result = p.group();
        assert!(result.contains("orders.status"));
        assert!(!result.contains("users.orders.status"));
    }

    #[test]
    fn test_distinct_false() {
        let p = Params::default("mysql");
        assert_eq!(p.distinct(), "");
    }

    #[test]
    fn test_distinct_true() {
        let mut p = Params::default("mysql");
        p.distinct = true;
        assert_eq!(p.distinct(), "DISTINCT");
    }

    #[test]
    fn test_select_sql_basic() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        let result = p.select_sql();
        assert!(result.contains("SELECT"));
        assert!(result.contains("*"));
        assert!(result.contains("FROM"));
        assert!(result.contains("users"));
    }

    #[test]
    fn test_select_sql_with_where_and_order() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.where_and.push("age > 18".to_string());
        p.order = object! { "name" => "ASC" };
        p.page = 1;
        p.limit = 10;
        let result = p.select_sql();
        assert!(result.contains("SELECT"));
        assert!(result.contains("FROM"));
        assert!(result.contains("users"));
        assert!(result.contains("WHERE age > 18"));
        assert!(result.contains("ORDER BY name ASC"));
        assert!(result.contains("LIMIT 0,10"));
    }

    #[test]
    fn test_select_sql_with_distinct_and_group() {
        let mut p = Params::default("mysql");
        p.table = "orders".to_string();
        p.distinct = true;
        p.group = object! { "g1" => "status" };
        p.fields = object! { "f1" => "status", "f2" => "count" };
        let result = p.select_sql();
        assert!(result.contains("DISTINCT"));
        assert!(result.contains("GROUP BY"));
        assert!(result.contains("orders.status"));
    }

    #[test]
    fn test_having_empty() {
        let mut p = Params::default("mysql");
        assert_eq!(p.having(), "");
    }

    #[test]
    fn test_having_single() {
        let mut p = Params::default("mysql");
        p.having.push("COUNT(*) > 5".to_string());
        assert_eq!(p.having(), "HAVING COUNT(*) > 5");
    }

    #[test]
    fn test_having_multiple() {
        let mut p = Params::default("mysql");
        p.having.push("COUNT(*) > 5".to_string());
        p.having.push("SUM(amount) > 100".to_string());
        assert_eq!(p.having(), "HAVING COUNT(*) > 5 AND SUM(amount) > 100");
    }

    #[test]
    fn test_select_sql_with_having() {
        let mut p = Params::default("mysql");
        p.table = "orders".to_string();
        p.group = object! { "g1" => "status" };
        p.having.push("COUNT(*) > 1".to_string());
        let result = p.select_sql();
        assert!(result.contains("GROUP BY"));
        assert!(result.contains("HAVING COUNT(*) > 1"));
    }

    #[test]
    fn test_field_raw_in_select_sql() {
        let mut p = Params::default("mysql");
        p.table = "orders".to_string();
        p.fields["raw1"] = "SUM(amount) as total".into();
        p.fields["raw2"] = "COUNT(*) as cnt".into();
        let result = p.select_sql();
        assert!(result.contains("SUM(amount) as total"));
        assert!(result.contains("COUNT(*) as cnt"));
        assert!(!result.contains("orders.`SUM"));
    }

    #[test]
    fn test_where_raw_in_select_sql() {
        let mut p = Params::default("mysql");
        p.table = "orders".to_string();
        p.where_and.push("YEAR(created_at) = 2026".to_string());
        let result = p.select_sql();
        assert!(result.contains("WHERE YEAR(created_at) = 2026"));
    }

    #[test]
    fn test_limit_only_default() {
        let mut p = Params::default("mysql");
        assert_eq!(p.page_limit_sql(), "");
    }

    #[test]
    fn test_limit_only_mysql() {
        let mut p = Params::default("mysql");
        p.limit_only = 5;
        assert_eq!(p.page_limit_sql(), "LIMIT 5");
    }

    #[test]
    fn test_limit_only_sqlite() {
        let mut p = Params::default("sqlite");
        p.limit_only = 10;
        assert_eq!(p.page_limit_sql(), "LIMIT 10");
    }

    #[test]
    fn test_limit_only_pgsql() {
        let mut p = Params::default("pgsql");
        p.limit_only = 3;
        assert_eq!(p.page_limit_sql(), "LIMIT 3");
    }

    #[test]
    fn test_page_overrides_limit_only() {
        let mut p = Params::default("mysql");
        p.limit_only = 5;
        p.page = 2;
        p.limit = 10;
        assert_eq!(p.page_limit_sql(), "LIMIT 10,10");
    }

    #[test]
    fn test_limit_only_in_select_sql() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.limit_only = 3;
        let result = p.select_sql();
        assert!(result.contains("LIMIT 3"));
        assert!(!result.contains("OFFSET"));
    }

    #[test]
    fn test_where_in_sub() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.where_and
            .push("`status` IN (SELECT status FROM active_users)".to_string());
        let result = p.select_sql();
        assert!(result.contains("WHERE `status` IN (SELECT status FROM active_users)"));
    }

    #[test]
    fn test_where_not_in_sub() {
        let mut p = Params::default("pgsql");
        p.table = "users".to_string();
        p.where_and
            .push("\"id\" NOT IN (SELECT user_id FROM banned)".to_string());
        let result = p.select_sql();
        assert!(result.contains("WHERE \"id\" NOT IN (SELECT user_id FROM banned)"));
    }

    #[test]
    fn test_union_single() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.unions.push("UNION SELECT * FROM admins".to_string());
        let result = p.select_sql();
        assert!(result.contains("UNION SELECT * FROM admins"));
    }

    #[test]
    fn test_union_all() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.unions.push("UNION ALL SELECT * FROM guests".to_string());
        let result = p.select_sql();
        assert!(result.contains("UNION ALL SELECT * FROM guests"));
    }

    #[test]
    fn test_union_multiple() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.unions.push("UNION SELECT * FROM admins".to_string());
        p.unions.push("UNION ALL SELECT * FROM guests".to_string());
        let result = p.select_sql();
        assert!(result.contains("UNION SELECT * FROM admins"));
        assert!(result.contains("UNION ALL SELECT * FROM guests"));
    }

    #[test]
    fn test_no_union() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        let result = p.select_sql();
        assert!(!result.contains("UNION"));
    }

    #[test]
    fn test_timestamps_default_false() {
        let p = Params::default("mysql");
        assert!(!p.timestamps);
    }

    #[test]
    fn test_lock_for_update_in_select_sql() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.lock_mode = "FOR UPDATE".to_string();
        let result = p.select_sql();
        assert!(result.contains("FOR UPDATE"));
    }

    #[test]
    fn test_lock_for_share_in_select_sql() {
        let mut p = Params::default("pgsql");
        p.table = "users".to_string();
        p.lock_mode = "FOR SHARE".to_string();
        let result = p.select_sql();
        assert!(result.contains("FOR SHARE"));
    }

    #[test]
    fn test_no_lock_mode() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        let result = p.select_sql();
        assert!(!result.contains("FOR UPDATE"));
        assert!(!result.contains("FOR SHARE"));
    }

    #[test]
    fn test_where_exists_in_select_sql() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.where_and
            .push("EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)".to_string());
        let result = p.select_sql();
        assert!(result.contains("EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)"));
    }

    #[test]
    fn test_where_not_exists_in_select_sql() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.where_and
            .push("NOT EXISTS (SELECT 1 FROM banned WHERE banned.uid = users.id)".to_string());
        let result = p.select_sql();
        assert!(result.contains("NOT EXISTS (SELECT 1 FROM banned WHERE banned.uid = users.id)"));
    }

    #[test]
    fn test_lock_with_union() {
        let mut p = Params::default("mysql");
        p.table = "users".to_string();
        p.unions.push("UNION SELECT * FROM admins".to_string());
        p.lock_mode = "FOR UPDATE".to_string();
        let result = p.select_sql();
        assert!(result.contains("UNION SELECT * FROM admins"));
        assert!(result.contains("FOR UPDATE"));
    }

    #[test]
    fn test_lock_mode_default_empty() {
        let p = Params::default("mysql");
        assert!(p.lock_mode.is_empty());
    }

    #[test]
    fn test_unions_default_empty() {
        let p = Params::default("mysql");
        assert!(p.unions.is_empty());
    }
}
