sqlx-firebirdsql 0.1.0

use std::str::FromStr;

use sqlx_core::connection::{ConnectOptions, Connection};
use sqlx_core::executor::Executor;
use sqlx_core::row::Row;
use sqlx_core::sql_str::AssertSqlSafe;

use serial_test::serial;
use sqlx_firebirdsql::{FirebirdConnectOptions, FirebirdConnection, FirebirdRow};

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

fn connect_options() -> FirebirdConnectOptions {
    let user = std::env::var("ISC_USER").unwrap_or_else(|_| "sysdba".to_string());
    let password = std::env::var("ISC_PASSWORD").unwrap_or_else(|_| "masterkey".to_string());
    FirebirdConnectOptions::new()
        .host("localhost")
        .port(3050)
        .username(&user)
        .password(&password)
        .database("/tmp/sqlx-firebird-test.fdb")
}

fn connect_url() -> String {
    let user = std::env::var("ISC_USER").unwrap_or_else(|_| "sysdba".to_string());
    let password = std::env::var("ISC_PASSWORD").unwrap_or_else(|_| "masterkey".to_string());
    format!(
        "firebird://{}:{}@localhost:3050//tmp/sqlx-firebird-test.fdb",
        user, password
    )
}

async fn setup() -> FirebirdConnection {
    let url = connect_url();

    // Try to create the database; ignore error if it already exists
    let _ = firebirust::ConnectionAsync::create_database_url(&url).await;

    let opts = connect_options();
    opts.connect().await.expect("failed to connect to Firebird")
}

/// Drop a table if it exists, using EXECUTE BLOCK.
async fn drop_table_if_exists(conn: &mut FirebirdConnection, table: &str) {
    let sql = format!(
        "EXECUTE BLOCK AS BEGIN \
            IF (EXISTS(SELECT 1 FROM RDB$RELATIONS WHERE TRIM(RDB$RELATION_NAME) = '{}')) THEN \
                EXECUTE STATEMENT 'DROP TABLE {}'; \
         END",
        table, table
    );
    let _ = Executor::execute(&mut *conn, AssertSqlSafe(sql)).await;
}

// ---------------------------------------------------------------------------
// Connection tests
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_connect() {
    let mut conn = setup().await;
    conn.ping().await.expect("ping failed");
    conn.close().await.expect("close failed");
}

#[tokio::test]
#[serial]
async fn test_connect_from_url() {
    let url = connect_url();
    let opts = FirebirdConnectOptions::from_str(&url).expect("failed to parse URL");
    let mut conn = opts.connect().await.expect("failed to connect");
    conn.ping().await.expect("ping failed");
    conn.close().await.expect("close failed");
}

// ---------------------------------------------------------------------------
// Basic query tests
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_select_from_rdb_database() {
    let mut conn = setup().await;

    let row: FirebirdRow = Executor::fetch_one(
        &mut conn,
        "SELECT 1 AS VAL FROM RDB$DATABASE",
    )
    .await
    .expect("fetch_one failed");

    let val: i32 = row.get("VAL");
    assert_eq!(val, 1);
}

#[tokio::test]
#[serial]
async fn test_select_expression() {
    let mut conn = setup().await;

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT 2 + 3 AS RESULT FROM RDB$DATABASE")
            .await
            .expect("fetch_one failed");

    let result: i32 = row.get("RESULT");
    assert_eq!(result, 5);
}

#[tokio::test]
#[serial]
async fn test_fetch_optional_none() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_EMPTY").await;
    Executor::execute(&mut conn, "CREATE TABLE TEST_EMPTY (ID INTEGER NOT NULL PRIMARY KEY)")
        .await
        .expect("CREATE TABLE failed");

    let row = Executor::fetch_optional(&mut conn, "SELECT * FROM TEST_EMPTY")
        .await
        .expect("fetch_optional failed");
    assert!(row.is_none());

    drop_table_if_exists(&mut conn, "TEST_EMPTY").await;
}

// ---------------------------------------------------------------------------
// Integer type tests
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_integer_types() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_INTEGERS").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_INTEGERS (
            ID INTEGER NOT NULL PRIMARY KEY,
            V_SMALL SMALLINT,
            V_INT INTEGER,
            V_BIG BIGINT
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_INTEGERS (ID, V_SMALL, V_INT, V_BIG) VALUES (1, 123, 456789, 9876543210)",
    )
    .await
    .expect("INSERT failed");

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_INTEGERS WHERE ID = 1")
            .await
            .expect("fetch_one failed");

    let v_small: i16 = row.get("V_SMALL");
    let v_int: i32 = row.get("V_INT");
    let v_big: i64 = row.get("V_BIG");

    assert_eq!(v_small, 123);
    assert_eq!(v_int, 456789);
    assert_eq!(v_big, 9876543210);

    drop_table_if_exists(&mut conn, "TEST_INTEGERS").await;
}

// ---------------------------------------------------------------------------
// Float type tests
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_float_types() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_FLOATS").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_FLOATS (
            ID INTEGER NOT NULL PRIMARY KEY,
            V_FLOAT FLOAT,
            V_DOUBLE DOUBLE PRECISION
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_FLOATS (ID, V_FLOAT, V_DOUBLE) VALUES (1, 3.14, 2.718281828459045)",
    )
    .await
    .expect("INSERT failed");

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_FLOATS WHERE ID = 1")
            .await
            .expect("fetch_one failed");

    let v_float: f32 = row.get("V_FLOAT");
    let v_double: f64 = row.get("V_DOUBLE");

    assert!((v_float - 3.14).abs() < 0.01);
    assert!((v_double - 2.718281828459045).abs() < 1e-10);

    drop_table_if_exists(&mut conn, "TEST_FLOATS").await;
}

// ---------------------------------------------------------------------------
// Boolean type test
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_boolean_type() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_BOOL").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_BOOL (
            ID INTEGER NOT NULL PRIMARY KEY,
            V_BOOL BOOLEAN
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_BOOL (ID, V_BOOL) VALUES (1, TRUE)",
    )
    .await
    .expect("INSERT failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_BOOL (ID, V_BOOL) VALUES (2, FALSE)",
    )
    .await
    .expect("INSERT failed");

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_BOOL WHERE ID = 1")
            .await
            .expect("fetch_one failed");
    let v: bool = row.get("V_BOOL");
    assert!(v);

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_BOOL WHERE ID = 2")
            .await
            .expect("fetch_one failed");
    let v: bool = row.get("V_BOOL");
    assert!(!v);

    drop_table_if_exists(&mut conn, "TEST_BOOL").await;
}

// ---------------------------------------------------------------------------
// String type tests
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_string_types() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_STRINGS").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_STRINGS (
            ID INTEGER NOT NULL PRIMARY KEY,
            V_VARCHAR VARCHAR(200),
            V_CHAR CHAR(10)
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_STRINGS (ID, V_VARCHAR, V_CHAR) VALUES (1, 'hello world', 'abc')",
    )
    .await
    .expect("INSERT failed");

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_STRINGS WHERE ID = 1")
            .await
            .expect("fetch_one failed");

    let v_varchar: String = row.get("V_VARCHAR");
    let v_char: String = row.get("V_CHAR");

    assert_eq!(v_varchar, "hello world");
    // CHAR is padded with spaces in Firebird
    assert_eq!(v_char.trim(), "abc");

    drop_table_if_exists(&mut conn, "TEST_STRINGS").await;
}

// ---------------------------------------------------------------------------
// BLOB type test
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_blob_type() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_BLOB").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_BLOB (
            ID INTEGER NOT NULL PRIMARY KEY,
            V_BLOB BLOB SUB_TYPE 0
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_BLOB (ID, V_BLOB) VALUES (1, x'DEADBEEF')",
    )
    .await
    .expect("INSERT failed");

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_BLOB WHERE ID = 1")
            .await
            .expect("fetch_one failed");

    let v_blob: Vec<u8> = row.get("V_BLOB");
    assert_eq!(v_blob, vec![0xDE, 0xAD, 0xBE, 0xEF]);

    drop_table_if_exists(&mut conn, "TEST_BLOB").await;
}

// ---------------------------------------------------------------------------
// NULL handling tests
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_null_values() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_NULLS").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_NULLS (
            ID INTEGER NOT NULL PRIMARY KEY,
            V_INT INTEGER,
            V_VARCHAR VARCHAR(100)
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(
        &mut conn,
        "INSERT INTO TEST_NULLS (ID, V_INT, V_VARCHAR) VALUES (1, NULL, NULL)",
    )
    .await
    .expect("INSERT failed");

    let row: FirebirdRow =
        Executor::fetch_one(&mut conn, "SELECT * FROM TEST_NULLS WHERE ID = 1")
            .await
            .expect("fetch_one failed");

    let v_int: Option<i32> = row.get("V_INT");
    let v_varchar: Option<String> = row.get("V_VARCHAR");

    assert!(v_int.is_none());
    assert!(v_varchar.is_none());

    drop_table_if_exists(&mut conn, "TEST_NULLS").await;
}

// ---------------------------------------------------------------------------
// Multiple rows
// ---------------------------------------------------------------------------

#[tokio::test]
#[serial]
async fn test_fetch_all() {
    let mut conn = setup().await;

    drop_table_if_exists(&mut conn, "TEST_MULTI").await;
    Executor::execute(
        &mut conn,
        "CREATE TABLE TEST_MULTI (
            ID INTEGER NOT NULL PRIMARY KEY,
            V VARCHAR(50)
        )",
    )
    .await
    .expect("CREATE TABLE failed");

    Executor::execute(&mut conn, "INSERT INTO TEST_MULTI (ID, V) VALUES (1, 'alpha')")
        .await
        .expect("INSERT failed");
    Executor::execute(&mut conn, "INSERT INTO TEST_MULTI (ID, V) VALUES (2, 'beta')")
        .await
        .expect("INSERT failed");
    Executor::execute(&mut conn, "INSERT INTO TEST_MULTI (ID, V) VALUES (3, 'gamma')")
        .await
        .expect("INSERT failed");

    let rows: Vec<FirebirdRow> =
        Executor::fetch_all(&mut conn, "SELECT * FROM TEST_MULTI ORDER BY ID")
            .await
            .expect("fetch_all failed");

    assert_eq!(rows.len(), 3);
    assert_eq!(rows[0].get::<String, _>("V"), "alpha");
    assert_eq!(rows[1].get::<String, _>("V"), "beta");
    assert_eq!(rows[2].get::<String, _>("V"), "gamma");

    drop_table_if_exists(&mut conn, "TEST_MULTI").await;
}

// ---------------------------------------------------------------------------
// Date / Time / Timestamp tests (chrono)
// ---------------------------------------------------------------------------

#[cfg(feature = "chrono")]
mod chrono_tests {
    use super::*;
    use chrono::{NaiveDate, NaiveDateTime, NaiveTime};

    #[tokio::test]
    async fn test_date_type() {
        let mut conn = setup().await;

        drop_table_if_exists(&mut conn, "TEST_DATE").await;
        Executor::execute(
            &mut conn,
            "CREATE TABLE TEST_DATE (
                ID INTEGER NOT NULL PRIMARY KEY,
                V_DATE DATE
            )",
        )
        .await
        .expect("CREATE TABLE failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_DATE (ID, V_DATE) VALUES (1, '2024-03-15')",
        )
        .await
        .expect("INSERT failed");

        let row: FirebirdRow =
            Executor::fetch_one(&mut conn, "SELECT * FROM TEST_DATE WHERE ID = 1")
                .await
                .expect("fetch_one failed");

        let v_date: NaiveDate = row.get("V_DATE");
        assert_eq!(v_date, NaiveDate::from_ymd_opt(2024, 3, 15).unwrap());

        drop_table_if_exists(&mut conn, "TEST_DATE").await;
    }

    #[tokio::test]
    async fn test_time_type() {
        let mut conn = setup().await;

        drop_table_if_exists(&mut conn, "TEST_TIME").await;
        Executor::execute(
            &mut conn,
            "CREATE TABLE TEST_TIME (
                ID INTEGER NOT NULL PRIMARY KEY,
                V_TIME TIME
            )",
        )
        .await
        .expect("CREATE TABLE failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_TIME (ID, V_TIME) VALUES (1, '14:30:59')",
        )
        .await
        .expect("INSERT failed");

        let row: FirebirdRow =
            Executor::fetch_one(&mut conn, "SELECT * FROM TEST_TIME WHERE ID = 1")
                .await
                .expect("fetch_one failed");

        let v_time: NaiveTime = row.get("V_TIME");
        assert_eq!(v_time, NaiveTime::from_hms_opt(14, 30, 59).unwrap());

        drop_table_if_exists(&mut conn, "TEST_TIME").await;
    }

    #[tokio::test]
    async fn test_timestamp_type() {
        let mut conn = setup().await;

        drop_table_if_exists(&mut conn, "TEST_TIMESTAMP").await;
        Executor::execute(
            &mut conn,
            "CREATE TABLE TEST_TIMESTAMP (
                ID INTEGER NOT NULL PRIMARY KEY,
                V_TS TIMESTAMP
            )",
        )
        .await
        .expect("CREATE TABLE failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_TIMESTAMP (ID, V_TS) VALUES (1, '2024-03-15 14:30:59')",
        )
        .await
        .expect("INSERT failed");

        let row: FirebirdRow =
            Executor::fetch_one(&mut conn, "SELECT * FROM TEST_TIMESTAMP WHERE ID = 1")
                .await
                .expect("fetch_one failed");

        let v_ts: NaiveDateTime = row.get("V_TS");
        let expected = NaiveDate::from_ymd_opt(2024, 3, 15)
            .unwrap()
            .and_hms_opt(14, 30, 59)
            .unwrap();
        assert_eq!(v_ts, expected);

        drop_table_if_exists(&mut conn, "TEST_TIMESTAMP").await;
    }
}

// ---------------------------------------------------------------------------
// Decimal type test (rust_decimal)
// ---------------------------------------------------------------------------

#[cfg(feature = "rust_decimal")]
mod decimal_tests {
    use super::*;
    use rust_decimal::Decimal;

    #[tokio::test]
    async fn test_decimal_type() {
        let mut conn = setup().await;

        drop_table_if_exists(&mut conn, "TEST_DECIMAL").await;
        Executor::execute(
            &mut conn,
            "CREATE TABLE TEST_DECIMAL (
                ID INTEGER NOT NULL PRIMARY KEY,
                V_DEC DECIMAL(18, 3)
            )",
        )
        .await
        .expect("CREATE TABLE failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_DECIMAL (ID, V_DEC) VALUES (1, 12345.678)",
        )
        .await
        .expect("INSERT failed");

        let row: FirebirdRow =
            Executor::fetch_one(&mut conn, "SELECT * FROM TEST_DECIMAL WHERE ID = 1")
                .await
                .expect("fetch_one failed");

        let v_dec: Decimal = row.get("V_DEC");
        assert_eq!(v_dec, Decimal::new(12345678, 3));

        drop_table_if_exists(&mut conn, "TEST_DECIMAL").await;
    }

    #[tokio::test]
    async fn test_decimal_negative() {
        let mut conn = setup().await;

        drop_table_if_exists(&mut conn, "TEST_DEC_NEG").await;
        Executor::execute(
            &mut conn,
            "CREATE TABLE TEST_DEC_NEG (
                ID INTEGER NOT NULL PRIMARY KEY,
                V_DEC DECIMAL(16, 3)
            )",
        )
        .await
        .expect("CREATE TABLE failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_DEC_NEG (ID, V_DEC) VALUES (1, -0.123)",
        )
        .await
        .expect("INSERT failed");

        let row: FirebirdRow =
            Executor::fetch_one(&mut conn, "SELECT * FROM TEST_DEC_NEG WHERE ID = 1")
                .await
                .expect("fetch_one failed");

        let v_dec: Decimal = row.get("V_DEC");
        assert_eq!(v_dec, Decimal::new(-123, 3));

        drop_table_if_exists(&mut conn, "TEST_DEC_NEG").await;
    }
}

// ---------------------------------------------------------------------------
// Comprehensive type roundtrip (modeled after firebirust test)
// ---------------------------------------------------------------------------

#[cfg(all(feature = "chrono", feature = "rust_decimal"))]
mod comprehensive_tests {
    use super::*;
    use chrono::{NaiveDate, NaiveDateTime, NaiveTime};
    use rust_decimal::Decimal;

    #[tokio::test]
    async fn test_comprehensive_types() {
        let mut conn = setup().await;

        drop_table_if_exists(&mut conn, "TEST_ALL_TYPES").await;
        Executor::execute(
            &mut conn,
            "CREATE TABLE TEST_ALL_TYPES (
                A INTEGER NOT NULL PRIMARY KEY,
                B VARCHAR(30) NOT NULL,
                C CHAR(1024),
                D DECIMAL(16,3) DEFAULT -0.123,
                E DATE DEFAULT '1967-08-11',
                F TIMESTAMP DEFAULT '1967-08-11 23:45:01',
                G TIME DEFAULT '23:45:01',
                H BLOB SUB_TYPE 1,
                I DOUBLE PRECISION DEFAULT 0.0,
                J FLOAT DEFAULT 0.0
            )",
        )
        .await
        .expect("CREATE TABLE failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_ALL_TYPES (A, B, C, H) VALUES (1, 'a', 'b', 'This is a pen')",
        )
        .await
        .expect("INSERT 1 failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_ALL_TYPES (A, B, C, E, G, I, J) VALUES (2, 'A', 'B', '1999-01-25', '00:00:01', 0.1, 0.1)",
        )
        .await
        .expect("INSERT 2 failed");

        Executor::execute(
            &mut conn,
            "INSERT INTO TEST_ALL_TYPES (A, B, C, E, G, I, J) VALUES (3, 'X', 'Y', '2001-07-05', '00:01:02', 0.2, 0.2)",
        )
        .await
        .expect("INSERT 3 failed");

        let rows: Vec<FirebirdRow> =
            Executor::fetch_all(&mut conn, "SELECT * FROM TEST_ALL_TYPES ORDER BY A")
                .await
                .expect("fetch_all failed");

        assert_eq!(rows.len(), 3);

        // Row 1
        assert_eq!(rows[0].get::<i32, _>("A"), 1);
        assert_eq!(rows[0].get::<String, _>("B"), "a");
        assert_eq!(rows[0].get::<String, _>("C").trim(), "b");
        assert_eq!(rows[0].get::<Decimal, _>("D"), Decimal::new(-123, 3));
        assert_eq!(
            rows[0].get::<NaiveDate, _>("E"),
            NaiveDate::from_ymd_opt(1967, 8, 11).unwrap()
        );
        assert_eq!(
            rows[0].get::<NaiveDateTime, _>("F"),
            NaiveDate::from_ymd_opt(1967, 8, 11)
                .unwrap()
                .and_hms_opt(23, 45, 1)
                .unwrap()
        );
        assert_eq!(
            rows[0].get::<NaiveTime, _>("G"),
            NaiveTime::from_hms_opt(23, 45, 1).unwrap()
        );
        assert_eq!(
            rows[0].get::<Vec<u8>, _>("H"),
            b"This is a pen".to_vec()
        );
        assert!((rows[0].get::<f64, _>("I") - 0.0).abs() < f64::EPSILON);
        assert!((rows[0].get::<f32, _>("J") - 0.0).abs() < f32::EPSILON);

        // Row 2
        assert_eq!(rows[1].get::<i32, _>("A"), 2);
        assert_eq!(rows[1].get::<String, _>("B"), "A");
        assert_eq!(
            rows[1].get::<NaiveDate, _>("E"),
            NaiveDate::from_ymd_opt(1999, 1, 25).unwrap()
        );
        assert_eq!(
            rows[1].get::<NaiveTime, _>("G"),
            NaiveTime::from_hms_opt(0, 0, 1).unwrap()
        );
        assert!((rows[1].get::<f64, _>("I") - 0.1).abs() < 1e-10);
        assert!((rows[1].get::<f32, _>("J") - 0.1).abs() < 0.01);

        // Row 3
        assert_eq!(rows[2].get::<i32, _>("A"), 3);
        assert_eq!(rows[2].get::<String, _>("B"), "X");
        assert_eq!(
            rows[2].get::<NaiveDate, _>("E"),
            NaiveDate::from_ymd_opt(2001, 7, 5).unwrap()
        );

        drop_table_if_exists(&mut conn, "TEST_ALL_TYPES").await;
    }
}