use json::{JsonValue, object};

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"
        )
    }

    #[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-sqlite")]
pub mod sqlite;
#[cfg(feature = "db-sqlite")]
pub mod sqlite_transaction;
#[cfg(feature = "db-mysql")]
pub mod mysql;
#[cfg(feature = "db-mysql")]
pub mod mysql_transaction;
#[cfg(feature = "db-mssql")]
pub mod mssql;
#[cfg(feature = "db-pgsql")]
pub mod pgsql;
#[cfg(feature = "db-pgsql")]
pub mod pgsql_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
    }
}

#[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;
    /// 返回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;
    /// 隐藏字段
    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;

    /// 比较两个列
    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 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;
    /// 获取集合
    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 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;
}

#[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,
}

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(),
        }
    }
    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 {
            return "".to_string();
        }
        match self.mode.as_str() {
            "mysql" => {
                format!("LIMIT {},{}", self.page * self.limit - self.limit, self.limit)
            }
            "sqlite" => {
                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() {
            if DISABLE_FIELD.contains(&&*field.clone().to_string()) {
                sql.push(format!("`{field}`"));
            } else if field.to_string().contains(".") {
                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 distinct(&self) -> String {
        if self.distinct {
            "DISTINCT".to_string()
        } else {
            "".to_string()
        }
    }
    pub fn select_sql(&mut self) -> String {
        format!("SELECT {} {} FROM {} {} {} {} {} {} {} {} {}", self.distinct(), self.fields(), self.top(), self.table(), self.join(), self.join_inner(), self.where_sql(), self.top2(), self.group(), self.order(), self.page_limit_sql())
    }
}