// Copyright (c) 2026 Kirky.X
//
// Licensed under the MIT License
// See LICENSE file in the project root for full license information.

//! 多数据库集成测试
//!
//! 测试跨不同数据库类型（SQLite、PostgreSQL、MySQL）的功能，
//! 包括数据库特定SQL生成、连接健康检查、Schema差异等

use dbnexus::DbPool;
use dbnexus::config::DatabaseType;
use dbnexus::config::DbConfig;
use dbnexus::config::DbConfigBuilder;
use dbnexus::migration::{ColumnType, SqlGenerator};

#[path = "../../common/mod.rs"]
mod common;

/// TEST-MDB-001: SQLite连接测试
#[tokio::test]
async fn test_sqlite_connection() {
    let config = common::get_test_config();

    // 如果配置不是SQLite，跳过此测试
    if !config.url_sanitized().starts_with("sqlite") {
        return;
    }

    let pool = DbPool::with_config(config).await.expect("Failed to create SQLite pool");
    let session = pool.get_session("admin").await.expect("Failed to get session");
    assert_eq!(session.role(), "admin");
}

/// TEST-MDB-002: PostgreSQL连接测试
#[tokio::test]
async fn test_postgresql_connection() {
    // 测试PostgreSQL URL格式
    let test_url = "postgres://localhost/test";

    // 验证URL格式检测
    let pool = DbPool::new(test_url).await;
    // 如果无法连接，这是预期的（没有运行PostgreSQL）
    // 但我们应该验证代码路径是正确的
    assert!(pool.is_err() || pool.is_ok());
}

/// TEST-MDB-003: MySQL连接测试
#[tokio::test]
async fn test_mysql_connection() {
    // 测试MySQL URL格式
    let test_url = "mysql://localhost/test";

    // 验证URL格式检测
    let pool = DbPool::new(test_url).await;
    // 如果无法连接，这是预期的（没有运行MySQL）
    // 但我们应该验证代码路径是正确的
    assert!(pool.is_err() || pool.is_ok());
}

/// TEST-MDB-004: 数据库类型检测测试
#[test]
fn test_database_type_detection() {
    // SQLite
    assert_eq!(DatabaseType::Sqlite, detect_db_type("sqlite::memory:"));
    assert_eq!(DatabaseType::Sqlite, detect_db_type("sqlite:///tmp/test.db"));

    // PostgreSQL
    assert_eq!(DatabaseType::Postgres, detect_db_type("postgres://localhost/test"));
    assert_eq!(DatabaseType::Postgres, detect_db_type("postgresql://localhost/test"));

    // MySQL
    assert_eq!(DatabaseType::MySql, detect_db_type("mysql://localhost/test"));
}

/// TEST-MDB-005: SQLite特定SQL生成测试
#[test]
fn test_sqlite_sql_generation() {
    let generator = SqlGenerator::new(DatabaseType::Sqlite);

    // Boolean类型 - SQLite使用INTEGER
    assert_eq!(generator.generate_column_def(&ColumnType::Boolean), "INTEGER");

    // String类型 - SQLite统一使用TEXT
    assert_eq!(generator.generate_column_def(&ColumnType::String(None)), "TEXT");
    assert_eq!(generator.generate_column_def(&ColumnType::String(Some(255))), "TEXT");

    // DateTime类型 - SQLite使用TEXT
    assert_eq!(generator.generate_column_def(&ColumnType::DateTime), "TEXT");

    // JSON类型 - SQLite使用TEXT
    assert_eq!(generator.generate_column_def(&ColumnType::Json), "TEXT");
}

/// TEST-MDB-006: PostgreSQL特定SQL生成测试
#[test]
fn test_postgresql_sql_generation() {
    let generator = SqlGenerator::new(DatabaseType::Postgres);

    // Boolean类型
    assert_eq!(generator.generate_column_def(&ColumnType::Boolean), "BOOLEAN");

    // String类型
    assert_eq!(generator.generate_column_def(&ColumnType::String(None)), "VARCHAR(255)");
    assert_eq!(
        generator.generate_column_def(&ColumnType::String(Some(100))),
        "VARCHAR(100)"
    );

    // DateTime类型
    assert_eq!(generator.generate_column_def(&ColumnType::DateTime), "TIMESTAMP");

    // JSON类型 - PostgreSQL使用JSONB
    assert_eq!(generator.generate_column_def(&ColumnType::Json), "JSONB");
}

/// TEST-MDB-007: MySQL特定SQL生成测试
#[test]
fn test_mysql_sql_generation() {
    let generator = SqlGenerator::new(DatabaseType::MySql);

    // Boolean类型
    assert_eq!(generator.generate_column_def(&ColumnType::Boolean), "BOOLEAN");

    // String类型
    assert_eq!(generator.generate_column_def(&ColumnType::String(None)), "VARCHAR(255)");
    assert_eq!(
        generator.generate_column_def(&ColumnType::String(Some(100))),
        "VARCHAR(100)"
    );

    // DateTime类型
    assert_eq!(generator.generate_column_def(&ColumnType::DateTime), "DATETIME");

    // JSON类型 - MySQL使用JSON
    assert_eq!(generator.generate_column_def(&ColumnType::Json), "JSON");

    // BigInteger类型
    assert_eq!(generator.generate_column_def(&ColumnType::BigInteger), "BIGINT");

    // 注意：自增列需要特定的列定义，不是所有列类型都支持
    // Integer类型
    assert_eq!(generator.generate_column_def(&ColumnType::Integer), "INTEGER");
}

/// TEST-MDB-008: 健康检查查询测试 - 不同数据库
#[tokio::test]
async fn test_health_check_query_different_databases() {
    let config = common::get_test_config();
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");

    let mut session = pool.get_session("admin").await.expect("Failed to get session");
    let conn = session.connection().expect("Failed to get connection");

    // 执行健康检查
    let is_healthy = pool.check_connection_health(conn).await;

    // 无论数据库类型如何，健康检查应该返回有效结果
    assert!(is_healthy, "Connection should be healthy");
}

/// TEST-MDB-009: 不同数据库的迁移历史表创建测试
#[tokio::test]
async fn test_migration_table_creation() {
    // 跳过 SQLite 内存数据库测试（每个连接是独立的数据库）
    // 迁移表创建测试需要持久化数据库或真实数据库
    if common::get_test_config().url_sanitized() == "sqlite::memory:" {
        return;
    }

    let config = common::get_test_config();
    let pool = DbPool::with_config(config.clone())
        .await
        .expect("Failed to create pool");
    let session = pool.get_session("admin").await.expect("Failed to get session");

    // 创建迁移历史表（使用 execute_raw_ddl）
    let create_sql = match config.url_sanitized() {
        url if url.starts_with("sqlite") => {
            "CREATE TABLE IF NOT EXISTS dbnexus_migrations (
                version INTEGER PRIMARY KEY,
                description TEXT NOT NULL,
                applied_at TEXT NOT NULL DEFAULT (datetime('now')),
                file_path TEXT
            );"
        }
        url if url.starts_with("postgres") || url.starts_with("postgresql") => {
            "CREATE TABLE IF NOT EXISTS dbnexus_migrations (
                version INTEGER PRIMARY KEY,
                description TEXT NOT NULL,
                applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                file_path TEXT
            );"
        }
        url if url.starts_with("mysql") => {
            "CREATE TABLE IF NOT EXISTS dbnexus_migrations (
                version INT PRIMARY KEY,
                description TEXT NOT NULL,
                applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                file_path TEXT
            );"
        }
        _ => return, // 未知数据库类型，跳过
    };

    let result = session.execute_raw_ddl(create_sql).await;
    assert!(result.is_ok(), "Migration table should be created successfully");

    // 验证表存在（使用 execute_raw 执行查询）
    let verify_sql = match config.url_sanitized() {
        url if url.starts_with("sqlite") => {
            "SELECT name FROM sqlite_master WHERE type='table' AND name='dbnexus_migrations'"
        }
        _ => "SELECT 1 FROM dbnexus_migrations LIMIT 1",
    };

    // 使用 execute_raw 执行查询
    let verify_result = session.execute_raw(verify_sql).await;
    assert!(verify_result.is_ok(), "Migration table should exist");
}

/// TEST-MDB-010: 数据库URL格式验证测试
#[test]
fn test_database_url_format_validation() {
    // 有效格式
    assert!(is_valid_database_url("sqlite::memory:"));
    assert!(is_valid_database_url("sqlite:///tmp/test.db"));
    assert!(is_valid_database_url("postgres://localhost:5432/testdb"));
    assert!(is_valid_database_url("postgresql://user:pass@localhost/testdb"));
    assert!(is_valid_database_url("mysql://localhost:3306/testdb"));
    assert!(is_valid_database_url("mysql://user:pass@localhost/testdb"));

    // 无效格式
    assert!(!is_valid_database_url("invalid://localhost/test"));
    assert!(!is_valid_database_url(""));
    assert!(!is_valid_database_url("localhost/test"));
}

/// TEST-MDB-011: 跨数据库数据类型映射测试
#[test]
fn test_cross_database_type_mapping() {
    let sqlite = SqlGenerator::new(DatabaseType::Sqlite);
    let postgres = SqlGenerator::new(DatabaseType::Postgres);
    let mysql = SqlGenerator::new(DatabaseType::MySql);

    // 验证相同列类型在不同数据库中的差异
    let column_types = vec![
        ColumnType::Integer,
        ColumnType::BigInteger,
        ColumnType::Boolean,
        ColumnType::String(Some(100)),
        ColumnType::Text,
        ColumnType::DateTime,
        ColumnType::Json,
    ];

    for col_type in column_types {
        let sqlite_def = sqlite.generate_column_def(&col_type);
        let postgres_def = postgres.generate_column_def(&col_type);
        let mysql_def = mysql.generate_column_def(&col_type);

        // 每个数据库都应该生成有效的SQL定义
        assert!(
            !sqlite_def.is_empty(),
            "SQLite should generate definition for {:?}",
            col_type
        );
        assert!(
            !postgres_def.is_empty(),
            "PostgreSQL should generate definition for {:?}",
            col_type
        );
        assert!(
            !mysql_def.is_empty(),
            "MySQL should generate definition for {:?}",
            col_type
        );
    }
}

/// TEST-MDB-012: 多数据库环境配置测试
#[test]
fn test_multi_database_config() {
    // 测试不同数据库的配置解析
    let sqlite_config = DbConfigBuilder::new()
        .url("sqlite::memory:")
        .max_connections(10)
        .min_connections(1)
        .idle_timeout(300)
        .acquire_timeout(5000)
        .admin_role("admin")
        .migration_timeout(60)
        .build()
        .expect("Failed to build sqlite config");

    let postgres_config = DbConfigBuilder::new()
        .url("postgres://localhost/test")
        .max_connections(20)
        .min_connections(5)
        .idle_timeout(300)
        .acquire_timeout(5000)
        .admin_role("admin")
        .migration_timeout(60)
        .build()
        .expect("Failed to build postgres config");

    let mysql_config = DbConfigBuilder::new()
        .url("mysql://localhost/test")
        .max_connections(15)
        .min_connections(3)
        .idle_timeout(300)
        .acquire_timeout(5000)
        .admin_role("admin")
        .migration_timeout(60)
        .build()
        .expect("Failed to build mysql config");

    // 验证配置有效
    assert!(sqlite_config.url_sanitized().starts_with("sqlite"));
    assert!(postgres_config.url_sanitized().starts_with("postgres"));
    assert!(mysql_config.url_sanitized().starts_with("mysql"));
}

/// TEST-MDB-013: 连接池状态跨数据库测试
#[tokio::test]
async fn test_pool_status_across_databases() {
    let config = common::get_test_config();
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");

    let session = pool.get_session("admin").await.expect("Failed to get session");
    drop(session);

    let status = pool.status();

    // 验证状态结构
    assert!(status.total >= 1, "Pool should have at least 1 connection");
    // 注意：u32 类型不能为负数，这些检查没有意义但保留以表明意图
    // assert!(status.active >= 0, "Active connections should be >= 0");
    // assert!(status.idle >= 0, "Idle connections should be >= 0");
    assert_eq!(
        status.total,
        status.active + status.idle,
        "Total should equal active + idle"
    );
}

/// TEST-MDB-014: 数据库特定功能测试
#[tokio::test]
#[cfg(feature = "sqlite")]
async fn test_database_specific_features() {
    // 创建带有权限配置的测试
    let perm_content = r#"
roles:
  admin:
    tables:
      - name: "*"
        operations:
          - select
          - insert
          - update
          - delete
"#;
    let temp_dir = tempfile::tempdir().expect("Failed to create temp dir");
    let perm_file = temp_dir.path().join("test_permissions.yaml");
    std::fs::write(&perm_file, perm_content).expect("Failed to write permissions file");

    let config = DbConfigBuilder::new()
        .url("sqlite::memory:")
        .max_connections(5)
        .permissions_path(perm_file.to_string_lossy().as_ref())
        .build()
        .expect("Failed to build config");
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");
    let session = pool.get_session("admin").await.expect("Failed to get session");

    // 创建测试表（使用 execute_raw_ddl）
    session
        .execute_raw_ddl("CREATE TABLE IF NOT EXISTS feature_test (id INTEGER PRIMARY KEY, data TEXT)")
        .await
        .expect("Failed to create test table");

    // 插入测试数据
    session
        .execute_raw("INSERT INTO feature_test (id, data) VALUES (1, 'test')")
        .await
        .expect("Failed to insert data");

    // 查询验证
    let _result = session
        .execute_raw("SELECT * FROM feature_test")
        .await
        .expect("Failed to query data");

    // 清理（使用 execute_raw_ddl）
    session
        .execute_raw_ddl("DROP TABLE feature_test")
        .await
        .expect("Failed to drop test table");
}

/// TEST-MDB-015: 数据库连接参数处理测试
#[tokio::test]
async fn test_connection_parameter_handling() {
    let config = common::get_test_config();
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");

    // 验证池配置正确应用
    let pool_config = pool.config();
    assert!(pool_config.max_connections() >= 1);
    assert!(pool_config.min_connections() >= 1);
    assert!(pool_config.min_connections() <= pool_config.max_connections());
    assert!(pool_config.idle_timeout() >= 30);
    assert!(pool_config.acquire_timeout() >= 1000);
}

/// TEST-MDB-016: 事务跨数据库兼容性测试
#[tokio::test]
async fn test_transaction_compatibility() {
    let config = common::get_test_config();
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");

    // 使用单独的会话进行事务操作
    let session = pool.get_session("admin").await.expect("Failed to get session");

    // 创建测试表（使用 execute_raw_ddl）
    session
        .execute_raw_ddl("CREATE TABLE IF NOT EXISTS txn_test (id INTEGER PRIMARY KEY, value INTEGER)")
        .await
        .expect("Failed to create test table");

    // 提交更改并获取新会话
    drop(session);
    let mut session = pool.get_session("admin").await.expect("Failed to get session");

    // 测试事务开始
    session.begin_transaction().await.expect("Failed to begin transaction");

    // 插入数据
    let insert_result = session.execute_raw("INSERT INTO txn_test (value) VALUES (100)").await;

    // 如果插入失败，可能是连接问题，直接跳过此测试
    if insert_result.is_err() {
        // 清理并返回（使用 execute_raw_ddl）
        let _ = session.execute_raw_ddl("DROP TABLE IF EXISTS txn_test").await;
        return;
    }

    // 提交事务
    session.commit().await.expect("Failed to commit");

    // 验证数据已提交
    let result = session.execute_raw("SELECT value FROM txn_test WHERE id = 1").await;

    assert!(result.is_ok(), "Transaction should commit successfully");

    // 清理
    session
        .execute_raw_ddl("DROP TABLE txn_test")
        .await
        .expect("Failed to drop test table");
}

/// TEST-MDB-017: 并发操作跨数据库测试
#[tokio::test]
async fn test_concurrent_operations_cross_database() {
    let config = common::get_test_config();
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");
    let pool = std::sync::Arc::new(pool);

    // 创建测试表（使用 execute_raw_ddl）
    let setup_session = pool.get_session("admin").await.expect("Failed to get session");
    setup_session
        .execute_raw_ddl("CREATE TABLE IF NOT EXISTS concurrent_test (id INTEGER PRIMARY KEY, counter INTEGER)")
        .await
        .expect("Failed to create test table");
    drop(setup_session);

    let pool_clone = pool.clone();
    let mut handles = Vec::new();

    // 并发执行操作
    for i in 0..10 {
        let pool = pool_clone.clone();
        let handle = tokio::spawn(async move {
            // 验证输入安全性（防止 SQL 注入）
            if i > 10000 {
                return Err(dbnexus::DbError::Config("Invalid counter value".to_string()));
            }

            let session = pool.get_session("admin").await.expect("Failed to get session");
            session
                .execute_raw(&format!("INSERT INTO concurrent_test (counter) VALUES ({})", i))
                .await
        });
        handles.push(handle);
    }

    // 等待所有操作完成
    let results = futures::future::join_all(handles).await;

    // 验证所有操作都成功
    for (i, result) in results.into_iter().enumerate() {
        assert!(result.is_ok(), "Operation {} should succeed", i);
    }

    // 验证数据插入数量
    let verify_session = pool.get_session("admin").await.expect("Failed to get session");
    let _count_result = verify_session.execute_raw("SELECT COUNT(*) FROM concurrent_test").await;

    // 清理（使用 IF EXISTS 避免错误）
    let _ = verify_session.execute_raw("DROP TABLE IF EXISTS concurrent_test").await;
}

/// TEST-MDB-018: 数据库类型与配置兼容性测试
#[tokio::test]
async fn test_database_config_compatibility() {
    let config = common::get_test_config();
    let pool = DbPool::with_config(config).await.expect("Failed to create pool");

    // 验证配置正确应用
    let pool_config = pool.config();

    // 验证连接限制
    assert!(
        pool_config.max_connections() >= pool_config.min_connections(),
        "max_connections should be >= min_connections"
    );

    // 验证超时设置合理
    assert!(
        pool_config.idle_timeout() >= 30 && pool_config.idle_timeout() <= 3600,
        "idle_timeout should be between 30 and 3600 seconds"
    );

    assert!(
        pool_config.acquire_timeout() >= 1000 && pool_config.acquire_timeout() <= 60000,
        "acquire_timeout should be between 1000 and 60000 milliseconds"
    );
}

/// 辅助函数：检测数据库类型
fn detect_db_type(url: &str) -> DatabaseType {
    if url.starts_with("sqlite:") {
        DatabaseType::Sqlite
    } else if url.starts_with("postgres:") || url.starts_with("postgresql:") {
        DatabaseType::Postgres
    } else if url.starts_with("mysql:") {
        DatabaseType::MySql
    } else {
        DatabaseType::Sqlite
    }
}

/// 辅助函数：验证数据库URL格式
fn is_valid_database_url(url: &str) -> bool {
    if url.is_empty() {
        return false;
    }

    let prefixes = ["sqlite:", "postgres:", "postgresql:", "mysql:"];
    prefixes.iter().any(|prefix| url.starts_with(prefix))
}
