client-core 0.1.0

use super::generator::generate_schema_diff;
/// 测试反引号标准化功能
/// 确保带反引号和不带反引号的 SQL 能够正确比较
use super::parser::parse_sql_tables;

#[test]
fn test_table_name_with_and_without_backticks() {
    // 测试表名：带反引号 vs 不带反引号
    let sql_with_backticks = "CREATE TABLE `user` (`id` INT PRIMARY KEY);";
    let sql_without_backticks = "CREATE TABLE user (id INT PRIMARY KEY);";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    // 两者都应该解析出 "user" 表（不带反引号）
    assert!(tables1.contains_key("user"), "应该包含 user 表");
    assert!(tables2.contains_key("user"), "应该包含 user 表");
    assert!(!tables1.contains_key("`user`"), "不应该有带反引号的表名");
    assert!(!tables2.contains_key("`user`"), "不应该有带反引号的表名");
}

#[test]
fn test_column_name_with_and_without_backticks() {
    // 测试列名：带反引号 vs 不带反引号
    let sql_with_backticks = "CREATE TABLE test (`id` INT, `name` VARCHAR(50));";
    let sql_without_backticks = "CREATE TABLE test (id INT, name VARCHAR(50));";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    let table1 = tables1.get("test").expect("应该有 test 表");
    let table2 = tables2.get("test").expect("应该有 test 表");

    // 检查列名都不带反引号
    assert_eq!(table1.columns[0].name, "id");
    assert_eq!(table1.columns[1].name, "name");
    assert_eq!(table2.columns[0].name, "id");
    assert_eq!(table2.columns[1].name, "name");
}

#[test]
fn test_index_column_with_and_without_backticks() {
    // 测试索引列名：带反引号 vs 不带反引号
    let sql_with_backticks =
        "CREATE TABLE test (`id` INT, `name` VARCHAR(50), INDEX idx_name (`name`));";
    let sql_without_backticks =
        "CREATE TABLE test (id INT, name VARCHAR(50), INDEX idx_name (name));";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    let table1 = tables1.get("test").expect("应该有 test 表");
    let table2 = tables2.get("test").expect("应该有 test 表");

    // 查找 idx_name 索引
    let index1 = table1
        .indexes
        .iter()
        .find(|i| i.name == "idx_name")
        .expect("应该有 idx_name 索引");
    let index2 = table2
        .indexes
        .iter()
        .find(|i| i.name == "idx_name")
        .expect("应该有 idx_name 索引");

    // 检查索引列名都不带反引号
    assert_eq!(index1.columns[0], "name");
    assert_eq!(index2.columns[0], "name");
}

#[test]
fn test_primary_key_with_and_without_backticks() {
    // 测试主键：带反引号 vs 不带反引号
    let sql_with_backticks =
        "CREATE TABLE test (`id` INT, `name` VARCHAR(50), PRIMARY KEY (`id`));";
    let sql_without_backticks = "CREATE TABLE test (id INT, name VARCHAR(50), PRIMARY KEY (id));";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    let table1 = tables1.get("test").expect("应该有 test 表");
    let table2 = tables2.get("test").expect("应该有 test 表");

    // 查找主键
    let pk1 = table1
        .indexes
        .iter()
        .find(|i| i.is_primary)
        .expect("应该有主键");
    let pk2 = table2
        .indexes
        .iter()
        .find(|i| i.is_primary)
        .expect("应该有主键");

    // 检查主键列名都不带反引号
    assert_eq!(pk1.columns[0], "id");
    assert_eq!(pk2.columns[0], "id");
}

#[test]
fn test_mysql_keywords_as_identifiers() {
    // 测试 MySQL 关键字作为标识符（必须用反引号）
    let sql = "CREATE TABLE `order` (`user` VARCHAR(50), `type` VARCHAR(20), `key` INT);";

    let tables = parse_sql_tables(sql).expect("解析失败");

    // 内部应该不带反引号
    assert!(tables.contains_key("order"), "应该包含 order 表");
    let table = tables.get("order").unwrap();
    assert_eq!(table.columns[0].name, "user");
    assert_eq!(table.columns[1].name, "type");
    assert_eq!(table.columns[2].name, "key");
}

#[test]
fn test_diff_with_same_structure_different_backticks() {
    // 测试相同结构但反引号不同的表不应产生差异
    let sql_mysql = "CREATE TABLE `custom_page_config` (
        `id` BIGINT NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255) NOT NULL,
        `description` VARCHAR(255),
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;";

    let sql_file = "CREATE TABLE custom_page_config (
        id BIGINT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        description VARCHAR(255),
        PRIMARY KEY (id)
    ) ENGINE=InnoDB;";

    let (diff_sql, _description) =
        generate_schema_diff(Some(sql_mysql), sql_file, Some("MySQL"), "文件")
            .expect("生成差异失败");

    // 应该没有实际差异
    let meaningful_lines: Vec<&str> = diff_sql
        .lines()
        .filter(|line| !line.trim().is_empty() && !line.trim().starts_with("--"))
        .collect();

    assert!(
        meaningful_lines.is_empty(),
        "相同结构的表不应产生差异，但生成了: {:?}",
        meaningful_lines
    );
}

#[test]
fn test_diff_with_multiple_tables_mixed_backticks() {
    // 测试多个表，混合使用反引号
    let sql_mysql = "
        CREATE TABLE `agent_config` (`id` INT, `name` VARCHAR(64), PRIMARY KEY (`id`));
        CREATE TABLE `agent_component_config` (`id` INT, `type` VARCHAR(32), PRIMARY KEY (`id`));
    ";

    let sql_file = "
        CREATE TABLE agent_config (id INT, name VARCHAR(64), PRIMARY KEY (id));
        CREATE TABLE agent_component_config (id INT, type VARCHAR(32), PRIMARY KEY (id));
    ";

    let (diff_sql, _description) =
        generate_schema_diff(Some(sql_mysql), sql_file, Some("MySQL"), "文件")
            .expect("生成差异失败");

    // 应该没有实际差异
    let meaningful_lines: Vec<&str> = diff_sql
        .lines()
        .filter(|line| !line.trim().is_empty() && !line.trim().starts_with("--"))
        .collect();

    assert!(
        meaningful_lines.is_empty(),
        "相同结构的多个表不应产生差异，但生成了: {:?}",
        meaningful_lines
    );
}

#[test]
fn test_column_level_primary_key_with_backticks() {
    // 测试列级别主键定义（带反引号）
    let sql_with_backticks = "CREATE TABLE test (`id` INT PRIMARY KEY, `name` VARCHAR(50));";
    let sql_without_backticks = "CREATE TABLE test (id INT PRIMARY KEY, name VARCHAR(50));";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    let table1 = tables1.get("test").expect("应该有 test 表");
    let table2 = tables2.get("test").expect("应该有 test 表");

    // 两者都应该有主键
    let pk1 = table1
        .indexes
        .iter()
        .find(|i| i.is_primary)
        .expect("应该有主键");
    let pk2 = table2
        .indexes
        .iter()
        .find(|i| i.is_primary)
        .expect("应该有主键");

    // 主键列名应该相同（不带反引号）
    assert_eq!(pk1.columns[0], "id");
    assert_eq!(pk2.columns[0], "id");
}

#[test]
fn test_composite_primary_key_with_backticks() {
    // 测试复合主键（带反引号）
    let sql_with_backticks =
        "CREATE TABLE test (`user_id` INT, `order_id` INT, PRIMARY KEY (`user_id`, `order_id`));";
    let sql_without_backticks =
        "CREATE TABLE test (user_id INT, order_id INT, PRIMARY KEY (user_id, order_id));";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    let table1 = tables1.get("test").expect("应该有 test 表");
    let table2 = tables2.get("test").expect("应该有 test 表");

    let pk1 = table1
        .indexes
        .iter()
        .find(|i| i.is_primary)
        .expect("应该有主键");
    let pk2 = table2
        .indexes
        .iter()
        .find(|i| i.is_primary)
        .expect("应该有主键");

    // 检查复合主键的列名
    assert_eq!(pk1.columns.len(), 2);
    assert_eq!(pk1.columns[0], "user_id");
    assert_eq!(pk1.columns[1], "order_id");

    assert_eq!(pk2.columns.len(), 2);
    assert_eq!(pk2.columns[0], "user_id");
    assert_eq!(pk2.columns[1], "order_id");
}

#[test]
fn test_unique_index_with_backticks() {
    // 测试唯一索引（带反引号）
    // 注意：sqlparser 可能会自动生成索引名，所以我们查找 is_unique 的索引
    let sql_with_backticks =
        "CREATE TABLE test (`id` INT, `email` VARCHAR(100), UNIQUE KEY `uk_email` (`email`));";
    let sql_without_backticks =
        "CREATE TABLE test (id INT, email VARCHAR(100), UNIQUE KEY uk_email (email));";

    let tables1 = parse_sql_tables(sql_with_backticks).expect("解析失败");
    let tables2 = parse_sql_tables(sql_without_backticks).expect("解析失败");

    let table1 = tables1.get("test").expect("应该有 test 表");
    let table2 = tables2.get("test").expect("应该有 test 表");

    // 查找唯一索引（通过 is_unique 标志）
    let uk1 = table1
        .indexes
        .iter()
        .find(|i| i.is_unique && !i.is_primary)
        .expect("应该有唯一索引");
    let uk2 = table2
        .indexes
        .iter()
        .find(|i| i.is_unique && !i.is_primary)
        .expect("应该有唯一索引");

    // 检查索引列名都不带反引号
    assert_eq!(uk1.columns[0], "email");
    assert_eq!(uk2.columns[0], "email");

    // 两个索引的名称应该相同（都被标准化了）
    assert_eq!(uk1.name, uk2.name);
}

#[test]
fn test_real_world_scenario_mysql_vs_file() {
    // 模拟真实场景：从 MySQL SHOW CREATE TABLE 获取的 SQL vs 文件中的 SQL
    let mysql_sql = "CREATE TABLE `agent_config` (
  `id` bigint AUTO_INCREMENT,
  `uid` varchar(64) NOT NULL,
  `_tenant_id` bigint DEFAULT 1 NOT NULL,
  `name` varchar(64) NOT NULL,
  `type` varchar(32) DEFAULT 'ChatBot' NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    let file_sql = "create table agent_config
(
    id            bigint auto_increment,
    uid           varchar(64)                        not null,
    _tenant_id    bigint                 default 1   not null,
    name          varchar(64)                        not null,
    type          varchar(32)            default 'ChatBot' not null,
    primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    let tables_mysql = parse_sql_tables(mysql_sql).expect("解析 MySQL SQL 失败");
    let tables_file = parse_sql_tables(file_sql).expect("解析文件 SQL 失败");

    // 两者都应该解析出 agent_config 表
    assert!(tables_mysql.contains_key("agent_config"));
    assert!(tables_file.contains_key("agent_config"));

    // 生成差异
    let (diff_sql, _description) =
        generate_schema_diff(Some(mysql_sql), file_sql, Some("MySQL"), "文件")
            .expect("生成差异失败");

    // 应该没有实际差异（忽略大小写和格式差异）
    let meaningful_lines: Vec<&str> = diff_sql
        .lines()
        .filter(|line| {
            let trimmed = line.trim();
            !trimmed.is_empty() && !trimmed.starts_with("--")
        })
        .collect();

    if !meaningful_lines.is_empty() {
        println!("生成的差异 SQL:\n{}", diff_sql);
    }

    // 注意：这个测试可能会因为大小写差异而失败，这是预期的
    // 主要目的是确保不会因为反引号产生大量的 ADD/DROP 操作
    assert!(
        !diff_sql.contains("DROP COLUMN `id`") && !diff_sql.contains("ADD COLUMN `id`"),
        "不应该删除和添加相同的列"
    );
}