Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

103 KB
Raw Blame
use super::*;
use crate::data_grid_sql::{DataGridColumnInfo, DataGridTableMeta};
use crate::models::connection::DatabaseType;
use serde_json::json;

fn column(name: &str, source_index: usize) -> DataGridExtractColumn {
    DataGridExtractColumn {
        display_name: name.to_string(),
        source_name: Some(name.to_string()),
        source_index,
        data_type: None,
        description: None,
    }
}

pub(super) fn request(extractor: DataGridExtractorId) -> DataGridExtractRequest {
    DataGridExtractRequest {
        version: DATA_GRID_EXTRACTOR_CONTRACT_VERSION,
        extractor,
        database_type: Some(DatabaseType::Postgres),
        identifier_quote: None,
        table_meta: None,
        source_table_name: None,
        columns: vec![column("id", 0), column("name", 1)],
        selected_column_indexes: vec![0, 1],
        rows: vec![vec![json!(1), json!("Ada")], vec![json!(2), json!("Grace, Hopper")]],
        selection_kind: DataGridSelectionKind::Cells,
        options: DataGridExtractorOptions::default(),
    }
}

fn temporal_insert_request() -> DataGridExtractRequest {
    let mut native = request(DataGridExtractorId::SqlInserts);
    native.database_type = Some(DatabaseType::Oracle);
    native.columns = vec![column("CREATED_AT", 0), column("NOTE", 1), column("AMOUNT", 2)];
    native.selected_column_indexes = vec![0, 1, 2];
    native.rows = vec![vec![json!("2022-08-25T09:58:43Z"), json!("it's text"), json!(42)]];
    native.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("APP".into()),
        table_name: "EVENTS".into(),
        primary_keys: vec![],
        columns: Some(
            [("CREATED_AT", "DATE"), ("NOTE", "VARCHAR2"), ("AMOUNT", "NUMBER")]
                .into_iter()
                .map(|(name, data_type)| DataGridColumnInfo {
                    name: name.into(),
                    data_type: data_type.into(),
                    is_nullable: true,
                    is_primary_key: false,
                    column_default: None,
                    extra: None,
                })
                .collect(),
        ),
    });
    native
}

#[test]
fn oracle_query_insert_uses_real_result_types_without_table_metadata() {
    for database_type in [DatabaseType::Oracle, DatabaseType::OceanbaseOracle] {
        let mut value = serde_json::to_value(request(DataGridExtractorId::SqlInserts)).unwrap();
        value["databaseType"] = serde_json::to_value(database_type).unwrap();
        value["columns"] = json!([
            {"displayName":"D","sourceIndex":0,"dataType":"DATE"},
            {"displayName":"T","sourceIndex":1,"dataType":"TIMESTAMP"},
            {"displayName":"Z","sourceIndex":2,"dataType":"TIMESTAMP WITH TIME ZONE"},
            {"displayName":"TEXT","sourceIndex":3,"dataType":"VARCHAR2"}
        ]);
        value["selectedColumnIndexes"] = json!([2, 0, 1, 3]);
        value["rows"] = json!([
            [
                "2026-10-10 13:14:15",
                "2026-10-10 13:14:15.123456",
                "2026-10-10 13:14:15.123456 -5:30",
                "2026-10-10 13:14:15"
            ],
            [null, null, null, null]
        ]);
        let request = serde_json::from_value(value).unwrap();
        let sql = extract_data_grid_selection(request).unwrap().text;
        assert!(
            sql.contains("TO_TIMESTAMP_TZ('2026-10-10 13:14:15.123456 -05:30', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')")
        );
        assert!(sql.contains("TO_DATE('2026-10-10 13:14:15', 'YYYY-MM-DD HH24:MI:SS')"));
        assert!(sql.contains("TO_TIMESTAMP('2026-10-10 13:14:15.123456', 'YYYY-MM-DD HH24:MI:SS.FF')"));
        assert!(sql.contains(", '2026-10-10 13:14:15')"));
        assert!(sql.contains("VALUES (NULL, NULL, NULL, NULL)"));
    }
}

#[test]
fn query_insert_result_types_preserve_metadata_priority_and_unknown_strings() {
    let mut native = temporal_insert_request();
    let mut value = serde_json::to_value(&native).unwrap();
    value["columns"][1]["dataType"] = json!("DATE");
    native = serde_json::from_value(value).unwrap();
    let sql = extract_data_grid_selection(native).unwrap().text;
    assert!(sql.contains("'it''s text'"));
    let mut value = serde_json::to_value(request(DataGridExtractorId::SqlInserts)).unwrap();
    value["databaseType"] = serde_json::to_value(DatabaseType::OceanbaseOracle).unwrap();
    value["columns"][1]["dataType"] = json!("UNKNOWN");
    value["rows"] = json!([[1, "2026-10-10 13:14:15"]]);
    assert!(extract_data_grid_selection(serde_json::from_value(value).unwrap())
        .unwrap()
        .text
        .contains("'2026-10-10 13:14:15'"));
}

#[test]
fn sql_insert_temporal_format_contract_and_output() {
    let native = temporal_insert_request();
    assert_eq!(
        extract_data_grid_selection(native.clone()).unwrap().text,
        "INSERT INTO \"APP\".\"EVENTS\" (\"CREATED_AT\", \"NOTE\", \"AMOUNT\") VALUES (TO_DATE('2022-08-25 09:58:43', 'YYYY-MM-DD HH24:MI:SS'), 'it''s text', 42);"
    );
    let mut payload = serde_json::to_value(&native).unwrap();
    payload["options"]["sql"]["quoteIdentifiers"] = json!(false);
    payload["options"]["sql"]["temporalFormat"] = json!("string");
    let portable: DataGridExtractRequest = serde_json::from_value(payload).unwrap();
    assert_eq!(
        extract_data_grid_selection(portable).unwrap().text,
        "INSERT INTO APP.EVENTS (CREATED_AT, NOTE, AMOUNT) VALUES ('2022-08-25 09:58:43', 'it''s text', 42);"
    );
}

#[test]
fn sql_insert_portable_temporals_preserve_wall_time_and_escape_fallback_text() {
    for (data_type, value, native_literal, portable_literal) in [
        ("DATE", "2022-08-25", "DATE '2022-08-25'", "'2022-08-25 00:00:00'"),
        (
            "TIMESTAMP(6)",
            "2022-08-25 09:58:43.123456",
            "TO_TIMESTAMP('2022-08-25 09:58:43.123456', 'YYYY-MM-DD HH24:MI:SS.FF')",
            "'2022-08-25 09:58:43'",
        ),
        (
            "TIMESTAMP WITH TIME ZONE",
            "2022-08-25T09:58:43.123456+08:00",
            "TO_TIMESTAMP_TZ('2022-08-25 09:58:43.123456 +08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')",
            "'2022-08-25 09:58:43'",
        ),
        (
            "TIMESTAMP WITH LOCAL TIME ZONE",
            "2022-08-25T09:58:43Z",
            "TO_TIMESTAMP_TZ('2022-08-25 09:58:43 +00:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')",
            "'2022-08-25 09:58:43'",
        ),
        ("DATE", "not a date's value", "'not a date''s value'", "'not a date''s value'"),
        ("VARCHAR2", "2022-08-25T09:58:43Z", "'2022-08-25T09:58:43Z'", "'2022-08-25T09:58:43Z'"),
    ] {
        for database_type in [DatabaseType::Oracle, DatabaseType::OceanbaseOracle] {
            let mut request = temporal_insert_request();
            request.database_type = Some(database_type);
            request.rows[0][0] = json!(value);
            request.table_meta.as_mut().unwrap().columns.as_mut().unwrap()[0].data_type = data_type.into();
            let native = extract_data_grid_selection(request.clone()).unwrap().text;
            request.options.sql.temporal_format = DataGridTemporalFormat::String;
            let portable = extract_data_grid_selection(request).unwrap().text;
            assert!(native.contains(&format!("VALUES ({native_literal}, 'it''s text', 42)")), "{native}");
            assert!(portable.contains(&format!("VALUES ({portable_literal}, 'it''s text', 42)")), "{portable}");
        }
    }
    for value in [Value::Null, json!(42), json!(true), json!("")] {
        let mut request = temporal_insert_request();
        request.rows[0][0] = value;
        let native = extract_data_grid_selection(request.clone()).unwrap().text;
        request.options.sql.temporal_format = DataGridTemporalFormat::String;
        assert_eq!(extract_data_grid_selection(request).unwrap().text, native);
    }
}

#[test]
fn sql_insert_quote_opt_out_keeps_required_delimiters_and_namespaces() {
    for (database_type, quote, name, expected) in [
        (DatabaseType::Oracle, None, "EVENTS", "EVENTS"),
        (DatabaseType::Oracle, None, "Events", "\"Events\""),
        (DatabaseType::Oracle, None, "ORDER", "\"ORDER\""),
        (DatabaseType::Oracle, None, "A\"B", "\"A\"\"B\""),
        (DatabaseType::Oracle, None, "EVENT LOG", "\"EVENT LOG\""),
        (DatabaseType::Mysql, None, "events", "events"),
        (DatabaseType::Mysql, None, "order", "`order`"),
        (DatabaseType::Mysql, None, "a`b", "`a``b`"),
        (DatabaseType::Postgres, None, "events", "events"),
        (DatabaseType::Postgres, None, "Events", "\"Events\""),
        (DatabaseType::SqlServer, None, "events", "events"),
        (DatabaseType::SqlServer, None, "a]b", "[a]]b]"),
        (DatabaseType::Dameng, None, "EVENTS", "EVENTS"),
        (DatabaseType::Dameng, None, "ABSOLUTE", "\"ABSOLUTE\""),
        (DatabaseType::Kingbase, Some("`"), "events", "events"),
        (DatabaseType::Kingbase, Some("`"), "order", "`order`"),
        (DatabaseType::Jdbc, Some("\""), "events", "events"),
        (DatabaseType::Jdbc, Some("\""), "Events", "\"Events\""),
        (DatabaseType::Spanner, Some("\""), "order", "\"order\""),
    ] {
        for include_database_name in [true, false] {
            let mut request = request(DataGridExtractorId::SqlInserts);
            request.database_type = Some(database_type);
            request.identifier_quote = quote.map(str::to_string);
            request.columns = vec![column(name, 0)];
            request.selected_column_indexes = vec![0];
            request.rows = vec![vec![json!(1)]];
            request.table_meta = Some(DataGridTableMeta {
                catalog: None,
                database: None,
                schema: Some("APP".into()),
                table_name: name.into(),
                primary_keys: vec![],
                columns: None,
            });
            request.options.sql.quote_identifiers = false;
            request.options.sql.include_database_name = include_database_name;
            let text = extract_data_grid_selection(request).unwrap().text;
            assert!(text.contains(&format!("{expected} ({expected}) VALUES (1);")), "{database_type:?}: {text}");
            if !include_database_name {
                assert_eq!(text, format!("INSERT INTO {expected} ({expected}) VALUES (1);"));
            }
        }
    }
}

#[test]
fn sql_insert_portable_options_keep_insert_modes_and_type_fallbacks() {
    for insert_mode in
        [crate::data_grid_sql::DataGridCopyInsertMode::Merged, crate::data_grid_sql::DataGridCopyInsertMode::RowByRow]
    {
        let mut request = temporal_insert_request();
        request.database_type = Some(DatabaseType::Mysql);
        request.rows.push(vec![Value::Null, json!("O'Reilly"), json!(0)]);
        request.options.sql.quote_identifiers = false;
        request.options.sql.temporal_format = DataGridTemporalFormat::String;
        request.options.sql.insert_mode = insert_mode;
        request.options.sql.include_database_name = false;
        let result = extract_data_grid_selection(request).unwrap();
        assert!(result.text.contains("('2022-08-25 09:58:43', 'it''s text', 42)"));
        assert!(result.text.contains("(NULL, 'O''Reilly', 0)"));
        assert_eq!(
            result.text.matches("INSERT INTO EVENTS").count(),
            if insert_mode == crate::data_grid_sql::DataGridCopyInsertMode::Merged { 1 } else { 2 }
        );
    }
    let mut request = temporal_insert_request();
    request.options.sql.temporal_format = DataGridTemporalFormat::String;
    request.table_meta = None;
    let text = extract_data_grid_selection(request).unwrap().text;
    assert!(text.contains("'2022-08-25T09:58:43Z'"));
}

#[test]
fn sql_insert_string_format_preserves_binary_timestamps_and_numeric_epochs() {
    for (database_type, data_type, value) in [
        (DatabaseType::SqlServer, "timestamp", "0x00000000000007D3"),
        (DatabaseType::Iotdb, "TIMESTAMP", "1747308643123456789"),
    ] {
        let mut request = temporal_insert_request();
        request.database_type = Some(database_type);
        request.table_meta.as_mut().unwrap().columns.as_mut().unwrap()[0].data_type = data_type.into();
        request.rows[0][0] = json!(value);
        let native = extract_data_grid_selection(request.clone()).unwrap().text;
        request.options.sql.temporal_format = DataGridTemporalFormat::String;
        assert_eq!(extract_data_grid_selection(request).unwrap().text, native);
    }
}

#[test]
fn sql_insert_policy_contract_rejects_unknown_modes_and_invalid_boolean_types() {
    for invalid in [json!({"sql": {"temporalFormat": "custom"}}), json!({"sql": {"quoteIdentifiers": "false"}})] {
        assert!(serde_json::from_value::<DataGridExtractorOptions>(invalid).is_err());
    }
    let options: DataGridExtractorOptions =
        serde_json::from_value(json!({"sql": {"temporalFormat": "string", "quoteIdentifiers": false}})).unwrap();
    let roundtrip: DataGridExtractorOptions = serde_json::from_value(serde_json::to_value(options).unwrap()).unwrap();
    assert_eq!(roundtrip.sql.temporal_format, DataGridTemporalFormat::String);
    assert!(!roundtrip.sql.quote_identifiers);
}

#[test]
fn insert_policies_do_not_change_other_extractors_or_column_errors() {
    for extractor in [
        DataGridExtractorId::Csv,
        DataGridExtractorId::Tsv,
        DataGridExtractorId::Json,
        DataGridExtractorId::JsonLines,
        DataGridExtractorId::Markdown,
        DataGridExtractorId::Html,
        DataGridExtractorId::Xml,
        DataGridExtractorId::SqlUpdates,
        DataGridExtractorId::SqlInList,
        DataGridExtractorId::WhereClause,
    ] {
        let mut request = temporal_insert_request();
        request.extractor = extractor;
        request.table_meta.as_mut().unwrap().primary_keys = vec!["AMOUNT".into()];
        let native = extract_data_grid_selection(request.clone()).unwrap().text;
        request.options.sql.quote_identifiers = false;
        request.options.sql.temporal_format = DataGridTemporalFormat::String;
        assert_eq!(extract_data_grid_selection(request).unwrap().text, native, "{extractor:?}");
    }
    let mut request = temporal_insert_request();
    request.options.sql.quote_identifiers = false;
    request.options.sql.temporal_format = DataGridTemporalFormat::String;
    for info in request.table_meta.as_mut().unwrap().columns.as_mut().unwrap() {
        info.extra = Some("VIRTUAL GENERATED".into());
    }
    assert_eq!(extract_data_grid_selection(request).unwrap_err().code, DataGridExtractErrorCode::NoWritableColumns);
}

#[test]
fn partially_deserialized_options_use_the_canonical_defaults() {
    let options = serde_json::from_value::<DataGridExtractorOptions>(json!({
        "dsv": { "columnSeparator": ";" },
        "sql": { "skipGeneratedColumns": false },
        "json": {}
    }))
    .expect("deserialize partial extractor options");

    assert_eq!(options.dsv.column_separator, ";");
    assert_eq!(options.dsv.row_separator, "\n");
    assert_eq!(options.dsv.null_text, "NULL");
    assert_eq!(options.dsv.quote, '"');
    assert_eq!(options.dsv.quote_policy, DataGridQuotePolicy::Minimal);
    assert!(!options.dsv.include_column_header);
    assert!(!options.dsv.include_row_header);
    assert!(options.sql.skip_computed_columns);
    assert!(!options.sql.skip_generated_columns);
    assert_eq!(options.sql.insert_mode, crate::data_grid_sql::DataGridCopyInsertMode::Merged);
    assert_eq!(options.sql.primary_key_mode, crate::data_grid_sql::DataGridPrimaryKeyMode::Include);
    assert!(options.sql.include_database_name);
    assert!(options.sql.quote_identifiers);
    assert_eq!(options.sql.temporal_format, DataGridTemporalFormat::Native);
    assert!(options.json.pretty);
    assert!(!options.json.camel_case_field_names);
    assert!(!options.json.values_only);
}

#[test]
fn insert_database_name_option_handles_engine_namespaces() {
    use crate::data_grid_sql::DataGridCopyInsertMode;
    use crate::sql_dialect::uses_single_row_insert_statements;

    for (database_type, catalog, schema, qualified, unqualified) in [
        (DatabaseType::Mysql, None, "a`b", "`a``b`.`users`", "`users`"),
        (DatabaseType::Goldendb, None, "app", "`app`.`users`", "`users`"),
        (DatabaseType::Doris, Some("internal"), "app", "`app`.`users`", "`users`"),
        (DatabaseType::StarRocks, None, "app", "`app`.`users`", "`users`"),
        (DatabaseType::Sqlite, None, "main", "\"main\".\"users\"", "\"users\""),
        (DatabaseType::Sqlite, None, "attached", "\"attached\".\"users\"", "\"users\""),
        (DatabaseType::DuckDb, None, "main", "\"main\".\"users\"", "\"users\""),
        (DatabaseType::Oracle, None, "APP", "\"APP\".\"users\"", "\"users\""),
        (DatabaseType::Dameng, None, "APP", "\"APP\".\"users\"", "\"users\""),
        (DatabaseType::Kingbase, None, "audit", "\"audit\".\"users\"", "\"users\""),
        (DatabaseType::Gaussdb, None, "audit", "\"audit\".\"users\"", "\"users\""),
        (DatabaseType::OpenGauss, None, "audit", "\"audit\".\"users\"", "\"users\""),
        (DatabaseType::Db2, None, "APP", "\"APP\".\"users\"", "\"users\""),
        (DatabaseType::StarRocks, Some("iceberg"), "app", "`iceberg`.`app`.`users`", "`users`"),
        (DatabaseType::Postgres, None, "audit", "\"audit\".\"users\"", "\"users\""),
        (DatabaseType::SqlServer, Some("app"), "audit", "[app].[audit].[users]", "[users]"),
        (DatabaseType::Doris, Some("iceberg"), "app", "`iceberg`.`app`.`users`", "`users`"),
    ] {
        for include_database_name in [true, false] {
            for insert_mode in [DataGridCopyInsertMode::Merged, DataGridCopyInsertMode::RowByRow] {
                let mut request = request(DataGridExtractorId::SqlInserts);
                request.database_type = Some(database_type);
                request.table_meta = Some(DataGridTableMeta {
                    catalog: catalog.map(str::to_owned),
                    database: Some("app".to_owned()),
                    schema: Some(schema.to_owned()),
                    table_name: "users".to_owned(),
                    primary_keys: vec![],
                    columns: None,
                });
                request.options.sql.include_database_name = include_database_name;
                request.options.sql.insert_mode = insert_mode;
                // Exercise the same camelCase contract sent by the frontend.
                let request = serde_json::from_value(serde_json::to_value(request).unwrap()).unwrap();
                let result = extract_data_grid_selection(request).unwrap();
                let table = if include_database_name { qualified } else { unqualified };
                let statements: Vec<_> = result.text.split(";\n").collect();
                let expected_statement_count = if insert_mode == DataGridCopyInsertMode::RowByRow
                    || uses_single_row_insert_statements(database_type)
                {
                    2
                } else {
                    1
                };
                assert_eq!(statements.len(), expected_statement_count);
                for statement in statements {
                    assert!(statement.starts_with(&format!("INSERT INTO {table} (")), "{statement}");
                }
            }
        }
    }
}

#[test]
fn insert_database_name_option_uses_database_when_schema_is_absent() {
    use crate::data_grid_sql::DataGridCopyInsertMode;

    for database_type in [DatabaseType::Mysql, DatabaseType::Goldendb, DatabaseType::Doris, DatabaseType::StarRocks] {
        for (database, schema, qualified) in [
            (Some("a`b"), None, "`a``b`.`users`"),
            (Some("app"), Some(""), "`app`.`users`"),
            (Some("app"), Some("  "), "`app`.`users`"),
            (Some("app"), Some("other"), "`other`.`users`"),
            (None, Some("other"), "`other`.`users`"),
            (None, None, "`users`"),
            (Some("  "), None, "`users`"),
        ] {
            for include_database_name in [true, false] {
                for insert_mode in [DataGridCopyInsertMode::Merged, DataGridCopyInsertMode::RowByRow] {
                    let mut request = request(DataGridExtractorId::SqlInserts);
                    request.database_type = Some(database_type);
                    request.table_meta = Some(DataGridTableMeta {
                        catalog: None,
                        database: database.map(str::to_owned),
                        schema: schema.map(str::to_owned),
                        table_name: "users".to_owned(),
                        primary_keys: vec![],
                        columns: None,
                    });
                    request.options.sql.include_database_name = include_database_name;
                    request.options.sql.insert_mode = insert_mode;
                    let request = serde_json::from_value(serde_json::to_value(request).unwrap()).unwrap();
                    let result = extract_data_grid_selection(request).unwrap();
                    let table = if include_database_name { qualified } else { "`users`" };
                    for statement in result.text.split(";\n") {
                        assert!(statement.starts_with(&format!("INSERT INTO {table} (")), "{statement}");
                    }
                }
            }
        }
    }
}

#[test]
fn postgres_insert_can_omit_schema_namespace() {
    for schema in [Some("dbx_dst_ccfebf6b"), None] {
        for include_database_name in [true, false] {
            let mut request = request(DataGridExtractorId::SqlInserts);
            request.database_type = Some(DatabaseType::Postgres);
            request.table_meta = Some(DataGridTableMeta {
                catalog: None,
                database: Some("app".to_owned()),
                schema: schema.map(str::to_owned),
                table_name: "users".to_owned(),
                primary_keys: vec![],
                columns: None,
            });
            request.options.sql.include_database_name = include_database_name;
            let result = extract_data_grid_selection(request).unwrap();
            let table =
                if include_database_name && schema.is_some() { r#""dbx_dst_ccfebf6b"."users""# } else { r#""users""# };
            assert!(result.text.starts_with(&format!("INSERT INTO {table} (")), "{}", result.text);
        }
    }
}

#[test]
fn extracts_csv_with_minimal_standard_quoting() {
    let result = extract_data_grid_selection(request(DataGridExtractorId::Csv)).expect("CSV extraction");
    assert_eq!(result.text, "1,Ada\n2,\"Grace, Hopper\"");
}

#[test]
fn csv_supports_bottom_description_headers_case_and_quote_modes() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns[0].description = Some("identifier".into());
    request.options.dsv.header_position = DataGridHeaderPosition::Bottom;
    request.options.dsv.header_format = DataGridHeaderFormat::Both;
    request.options.dsv.header_case = DataGridHeaderCase::Upper;
    let result = extract_data_grid_selection(request).expect("CSV extraction");
    assert_eq!(result.text, "1,Ada\n2,\"Grace, Hopper\"\nID (IDENTIFIER),NAME");
}

#[test]
fn csv_applies_formula_line_feed_array_and_typed_quote_options() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns = vec![column("amount", 0), column("text", 1), column("items", 2)];
    request.columns[0].data_type = Some("INTEGER".into());
    request.columns[1].data_type = Some("VARCHAR".into());
    request.columns[2].data_type = Some("JSON_ARRAY".into());
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows =
        vec![vec![json!(-1), json!("=SUM(A1)\r\nnext"), json!(["a", "b"])], vec![json!(2), json!("001"), json!(["c"])]];
    request.options.dsv.escape_formulas = true;
    request.options.dsv.line_feed_escape_string = "\\n".into();
    request.options.dsv.array_format = DataGridArrayFormat::Braces;
    request.options.dsv.quote_policy = DataGridQuotePolicy::Strings;
    request.options.dsv.quote_numeric_strings = true;

    let result = extract_data_grid_selection(request).expect("CSV extraction");
    assert_eq!(result.text, "-1,\"'=SUM(A1)\\nnext\",\"{\"\"a\"\",\"\"b\"\"}\"\n2,\"001\",\"{\"\"c\"\"}\"");
}

#[test]
fn csv_type_quote_policies_still_quote_values_that_contain_delimiters() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns =
        vec![column("comma_amount", 0), column("newline_amount", 1), column("quote_amount", 2), column("name", 3)];
    for column in &mut request.columns[..3] {
        column.data_type = Some("DECIMAL".into());
    }
    request.columns[3].data_type = Some("VARCHAR".into());
    request.selected_column_indexes = vec![0, 1, 2, 3];
    request.rows = vec![vec![json!("1,234"), json!("1\n234"), json!("1\"234"), json!("Ada")]];
    request.options.dsv.quote_policy = DataGridQuotePolicy::AllButNumbers;

    let result = extract_data_grid_selection(request).expect("CSV extraction");
    assert_eq!(result.text, "\"1,234\",\"1\n234\",\"1\"\"234\",\"Ada\"");
    assert_eq!(parse_csv_record(&result.text), vec!["1,234", "1\n234", "1\"234", "Ada"]);
}

#[test]
fn one_row_keeps_numeric_types_for_typed_quote_policies_and_quotes_string_columns() {
    let mut request = request(DataGridExtractorId::OneRow);
    request.columns = vec![column("amount", 0), column("name", 1), column("code", 2)];
    request.columns[0].data_type = Some("INTEGER".into());
    request.columns[1].data_type = Some("VARCHAR".into());
    request.columns[2].data_type = Some("VARCHAR".into());
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(42), json!("Ada"), json!("001")]];
    request.options.dsv.quote_policy = DataGridQuotePolicy::Strings;
    request.options.dsv.quote_numeric_strings = true;

    let result = extract_data_grid_selection(request).expect("one-row extraction");
    assert_eq!(result.text, "42,\"Ada\",\"001\"");
    assert_eq!(parse_csv_record(&result.text), vec!["42", "Ada", "001"]);
}

#[test]
fn clickhouse_uint_columns_are_treated_as_numeric() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns.truncate(1);
    request.selected_column_indexes = vec![0];
    request.columns[0].data_type = Some("UInt8".into());
    request.rows = vec![vec![json!(255)]];
    request.options.dsv.quote_policy = DataGridQuotePolicy::AllButNumbers;
    assert_eq!(extract_data_grid_selection(request).unwrap().text, "255");
}

#[test]
fn csv_strings_policy_preserves_formula_tabs_arrays_and_large_numeric_text() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns = vec![column("formula", 0), column("items", 1), column("id", 2)];
    request.columns[0].data_type = Some("VARCHAR".into());
    request.columns[1].data_type = Some("JSON_ARRAY".into());
    request.columns[2].data_type = Some("BIGINT".into());
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!("\t=1+1"), json!(["a", "b\"c"]), json!("9007199254740993")]];
    request.options.dsv.quote_policy = DataGridQuotePolicy::Strings;
    request.options.dsv.quote_numeric_strings = true;
    request.options.dsv.escape_formulas = true;

    let result = extract_data_grid_selection(request).expect("CSV extraction");
    assert_eq!(parse_csv_record(&result.text), vec!["'\t=1+1", r#"["a","b\"c"]"#, "9007199254740993"]);
}

#[test]
fn typed_quote_policy_recognizes_decimal_aliases_as_numbers() {
    for data_type in ["DEC", "FIXED", "SMALLMONEY"] {
        let mut request = request(DataGridExtractorId::Csv);
        request.columns = vec![column("amount", 0)];
        request.columns[0].data_type = Some(data_type.into());
        request.selected_column_indexes = vec![0];
        request.rows = vec![vec![json!("1")]];
        request.options.dsv.quote_policy = DataGridQuotePolicy::Strings;

        assert_eq!(extract_data_grid_selection(request).unwrap().text, "1", "{data_type}");
    }
}

fn parse_csv_record(record: &str) -> Vec<String> {
    let mut fields = Vec::new();
    let mut field = String::new();
    let mut chars = record.chars().peekable();
    let mut quoted = false;
    while let Some(character) = chars.next() {
        match character {
            '"' if quoted && chars.peek() == Some(&'"') => {
                chars.next();
                field.push('"');
            }
            '"' => quoted = !quoted,
            ',' if !quoted => fields.push(std::mem::take(&mut field)),
            _ => field.push(character),
        }
    }
    fields.push(field);
    fields
}

#[test]
fn csv_formula_protection_covers_fullwidth_prefixes_but_not_typed_negative_numbers() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns = vec![
        column("amount", 0),
        column("formula", 1),
        column("untyped_negative", 2),
        column("numeric_string_negative", 3),
    ];
    request.columns[0].data_type = Some("DECIMAL".into());
    request.columns[1].data_type = Some("TEXT".into());
    request.columns[2].data_type = None;
    request.columns[3].data_type = Some("TEXT".into());
    request.selected_column_indexes = vec![0, 1, 2, 3];
    request.rows = vec![vec![json!(-123), json!("=1+1"), json!(-45.67), json!("-89.10")]];
    request.options.dsv.escape_formulas = true;

    let result = extract_data_grid_selection(request).expect("CSV extraction");
    assert_eq!(result.text, "-123,'=1+1,-45.67,-89.10");
}

#[test]
fn tsv_honors_header_null_and_advanced_options_like_csv() {
    // #13305: TSV shares the CSV/DSV option set; only the separator is pinned
    // and quoting stays off, so embedded double quotes stay verbatim.
    let mut request = request(DataGridExtractorId::TsvWithHeaders);
    request.columns = vec![column("=header", 0), column("value", 1)];
    request.selected_column_indexes = vec![0, 1];
    request.rows = vec![vec![json!("=SUM(A1)"), json!("line1\nline2")]];
    request.options.dsv.header_position = DataGridHeaderPosition::Bottom;
    request.options.dsv.escape_formulas = true;
    request.options.dsv.line_feed_escape_string = "\\n".into();

    let result = extract_data_grid_selection(request).expect("TSV extraction");
    assert_eq!(result.text, "'=SUM(A1)\tline1\\nline2\n'=header\tvalue");
}

#[test]
fn formula_escape_does_not_misclassify_interval_as_numeric() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns = vec![column("duration", 0)];
    request.columns[0].data_type = Some("INTERVAL".into());
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!("-1 day")]];
    request.options.dsv.escape_formulas = true;

    let result = extract_data_grid_selection(request).expect("CSV extraction");
    assert_eq!(result.text, "'-1 day");
}

#[test]
fn extracts_raw_values_without_escaping_quotes() {
    let mut request = request(DataGridExtractorId::Raw);
    request.columns = vec![column("payload", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!(r#"{"msg":"success"}"#)]];

    let result = extract_data_grid_selection(request).expect("raw extraction");

    assert_eq!(result.text, r#"{"msg":"success"}"#);
    assert_eq!(result.mime_type, "text/plain");
}

#[test]
fn name_and_description_two_rows_header_stacks_description_above_name() {
    let mut request = request(DataGridExtractorId::CsvWithHeaders);
    request.columns = vec![column("amount", 0), column("name", 1)];
    request.columns[0].description = Some("Amount in EUR".into());
    request.selected_column_indexes = vec![0, 1];
    request.rows = vec![vec![json!(1), json!("a")]];
    request.options.dsv.header_format = DataGridHeaderFormat::NameAndDescriptionTwoRows;
    request.options.dsv.header_position = DataGridHeaderPosition::Top;

    let result = extract_data_grid_selection(request).expect("CSV extraction");
    // A missing description leaves that cell empty on the description row.
    assert_eq!(result.text, "Amount in EUR,\namount,name\n1,a");
}

#[test]
fn name_and_description_two_rows_header_applies_case_and_bottom_position() {
    let mut request = request(DataGridExtractorId::TsvWithHeaders);
    request.columns = vec![column("amount", 0)];
    request.columns[0].description = Some("Amount".into());
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!(1)]];
    request.options.dsv.header_format = DataGridHeaderFormat::NameAndDescriptionTwoRows;
    request.options.dsv.header_position = DataGridHeaderPosition::Bottom;
    request.options.dsv.header_case = DataGridHeaderCase::Upper;

    let result = extract_data_grid_selection(request).expect("TSV extraction");
    assert_eq!(result.text, "1\nAMOUNT\nAMOUNT");
}

#[test]
fn tsv_default_still_writes_the_legacy_single_name_header() {
    let mut request = request(DataGridExtractorId::TsvWithHeaders);
    request.columns = vec![column("amount", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!(1)]];

    let result = extract_data_grid_selection(request).expect("TSV extraction");
    assert_eq!(result.text, "amount\n1");
}

#[test]
fn tsv_honors_the_null_text_option() {
    let mut request = request(DataGridExtractorId::Tsv);
    request.columns = vec![column("amount", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![Value::Null]];
    request.options.dsv.null_text = "NULL".into();

    let result = extract_data_grid_selection(request).expect("TSV extraction");
    assert_eq!(result.text, "NULL");
}

#[test]
fn plain_tsv_ignores_persisted_dsv_options_unlike_tsv_with_headers() {
    // The plain Tsv id is the implicit default of smart copy/cut; a user who
    // saved non-legacy options while configuring CSV must not get header rows
    // or formula escaping in plain Ctrl+C output (#13305 review).
    let mut request = request(DataGridExtractorId::Tsv);
    request.columns = vec![column("amount", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![Value::Number(serde_json::Number::from(1))]];
    request.options.dsv.header_position = DataGridHeaderPosition::Top;
    request.options.dsv.header_format = DataGridHeaderFormat::NameAndDescriptionTwoRows;
    request.options.dsv.escape_formulas = true;

    let result = extract_data_grid_selection(request).expect("TSV extraction");
    // Legacy plain Tsv carries no header row and applies no formula escaping,
    // even though persisted options ask for a two-row top header.
    assert_eq!(result.text, "1");
}

#[test]
fn tsv_and_pipe_emit_embedded_quotes_verbatim_while_csv_escapes_them() {
    // Reproduces #10087: a value that is itself wrapped in double quotes (e.g.
    // MySQL `SELECT CONCAT('"', id, '"')`) must copy out unchanged from the grid's
    // default multi-cell (TSV) copy, instead of being wrapped and doubled.
    let quoted = r#""9932b4d2ad1c4ff88b5bfe1690ee1bb8""#;

    for (extractor, separator) in [(DataGridExtractorId::Tsv, "\t"), (DataGridExtractorId::PipeSeparated, "|")] {
        let mut request = request(extractor);
        request.rows = vec![vec![json!(1), json!(quoted)]];
        let result = extract_data_grid_selection(request).expect("plain delimited extraction");
        assert_eq!(result.text, format!("1{separator}{quoted}"), "extractor {extractor:?} must not escape quotes");
    }

    // CSV is an RFC4180 format, so the same value must still be quoted and doubled.
    let mut csv = request(DataGridExtractorId::Csv);
    csv.rows = vec![vec![json!(1), json!(quoted)]];
    let csv_result = extract_data_grid_selection(csv).expect("CSV extraction");
    assert_eq!(csv_result.text, format!("1,\"{}\"", quoted.replace('"', "\"\"")));
}

#[test]
fn tsv_still_quotes_values_that_would_break_the_row_shape() {
    // Relaxing the quote-character trigger must not stop quoting on the actual
    // separator / line breaks, which would otherwise corrupt the TSV structure.
    let mut request = request(DataGridExtractorId::Tsv);
    request.rows = vec![vec![json!(1), json!("a\tb\nc")]];
    let result = extract_data_grid_selection(request).expect("TSV extraction");
    assert_eq!(result.text, "1\t\"a\tb\nc\"");
}

#[test]
fn raw_exports_null_as_an_empty_value() {
    let mut request = request(DataGridExtractorId::Raw);
    request.columns = vec![column("name", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![Value::Null]];

    let result = extract_data_grid_selection(request).expect("raw extraction");

    assert_eq!(result.text, "");
}

#[test]
fn raw_preserves_multiline_view_definition_with_tabs_and_quotes() {
    let lines = [
        "CREATE view v_Copy_Tab",
        "as",
        "outer apply (select string_agg(z.CurrencyName+':'+convert(varchar,round(z.Balance,2)), '/') YuE",
        "\tfrom currency_detail z where z.customer_id=a.id) e",
        "-- keep \"quoted\" identifiers and embedded\r\nnewlines",
        "",
    ];
    let mut request = request(DataGridExtractorId::Raw);
    request.columns = vec![column("Text", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = lines.iter().map(|line| vec![json!(line)]).collect();
    // Original text ignores the tabular format's quoting and header settings.
    request.options.dsv.quote_policy = DataGridQuotePolicy::Always;
    request.options.dsv.include_row_header = true;
    request.options.dsv.include_column_header = true;

    let result = extract_data_grid_selection(request).expect("raw view definition extraction");
    assert_eq!(result.text, lines.join("\n"));
    assert_eq!(result.row_count, lines.len());
    assert_eq!(result.column_count, 1);
}

#[test]
fn raw_rejects_multiple_selected_columns() {
    let error =
        extract_data_grid_selection(request(DataGridExtractorId::Raw)).expect_err("raw must reject multiple columns");

    assert_eq!(error.code, DataGridExtractErrorCode::InvalidRawSelection);
}

#[test]
fn csv_distinguishes_null_from_null_string() {
    let mut request = request(DataGridExtractorId::Csv);
    request.rows = vec![vec![json!(1), Value::Null], vec![json!(2), json!("NULL")]];
    let result = extract_data_grid_selection(request).expect("CSV extraction");
    // NULL emits the bare sentinel; the literal string "NULL" is force-quoted.
    assert_eq!(result.text, "1,NULL\n2,\"NULL\"");
}

#[test]
fn csv_keeps_null_unquoted_under_always_quote() {
    let mut request = request(DataGridExtractorId::Csv);
    request.rows = vec![vec![json!(1), Value::Null], vec![json!(2), json!("NULL")]];
    request.options.dsv.quote_policy = DataGridQuotePolicy::Always;
    let result = extract_data_grid_selection(request).expect("CSV extraction");
    // NULL stays bare even under Always; the string is quoted, so they differ.
    assert_eq!(result.text, "\"1\",NULL\n\"2\",\"NULL\"");
}

#[test]
fn csv_null_and_sentinel_collapse_under_never_quote() {
    let mut request = request(DataGridExtractorId::Csv);
    request.rows = vec![vec![json!(1), Value::Null], vec![json!(2), json!("NULL")]];
    request.options.dsv.quote_policy = DataGridQuotePolicy::Never;
    let result = extract_data_grid_selection(request).expect("CSV extraction");
    // Under Never quoting, NULL and the string "NULL" are inherently indistinguishable.
    assert_eq!(result.text, "1,NULL\n2,NULL");
}

#[test]
fn csv_empty_null_text_distinguishes_empty_string() {
    let mut request = request(DataGridExtractorId::Csv);
    request.rows = vec![vec![json!(1), Value::Null], vec![json!(2), json!("")]];
    request.options.dsv.null_text = String::new();
    let result = extract_data_grid_selection(request).expect("CSV extraction");
    // NULL -> bare empty; the empty string is force-quoted.
    assert_eq!(result.text, "1,\n2,\"\"");
}

#[test]
fn one_row_distinguishes_null_from_null_string() {
    let mut request = request(DataGridExtractorId::OneRow);
    request.rows = vec![vec![json!(1), Value::Null], vec![json!(2), json!("NULL")]];
    let result = extract_data_grid_selection(request).expect("one-row extraction");
    assert_eq!(result.text, "1,NULL,2,\"NULL\"");
}

#[test]
fn extracts_one_row_as_a_single_csv_record() {
    let result = extract_data_grid_selection(request(DataGridExtractorId::OneRow)).expect("One-row extraction");
    assert_eq!(result.text, "1,Ada,2,\"Grace, Hopper\"");
}

#[test]
fn extracts_all_remaining_text_table_formats() {
    let cases = [
        (DataGridExtractorId::Tsv, "1\tAda\n2\tGrace, Hopper"),
        (DataGridExtractorId::TsvWithHeaders, "id\tname\n1\tAda\n2\tGrace, Hopper"),
        (DataGridExtractorId::CsvWithHeaders, "id,name\n1,Ada\n2,\"Grace, Hopper\""),
        (DataGridExtractorId::PipeSeparated, "1|Ada\n2|Grace, Hopper"),
        (DataGridExtractorId::Markdown, "| id | name |\n| --- | --- |\n| 1 | Ada |\n| 2 | Grace, Hopper |"),
        (
            DataGridExtractorId::Pretty,
            "+----+---------------+\n| id | name          |\n+----+---------------+\n| 1  | Ada           |\n| 2  | Grace, Hopper |\n+----+---------------+\n",
        ),
    ];

    for (extractor, expected) in cases {
        let result = extract_data_grid_selection(request(extractor)).expect("text table extraction");
        assert_eq!(result.text, expected, "unexpected output for {extractor:?}");
        assert_eq!(result.row_count, 2);
        assert_eq!(result.column_count, 2);
    }
}

#[test]
fn pretty_output_uses_terminal_width_for_cjk_and_emoji() {
    let mut request = request(DataGridExtractorId::Pretty);
    request.columns = vec![column("标签", 0), column("name", 1)];
    request.rows = vec![vec![json!("中文"), json!("🙂")]];

    let result = extract_data_grid_selection(request).expect("Unicode pretty extraction");

    assert_eq!(
        result.text,
        "+------+------+
| 标签 | name |
+------+------+
| 中文 | 🙂   |
+------+------+
"
    );
}

#[test]
fn one_row_honors_standard_csv_quote_configuration() {
    let mut request = request(DataGridExtractorId::OneRow);
    request.options.dsv.quote_policy = DataGridQuotePolicy::Always;
    request.options.dsv.quote = '\'';

    let result = extract_data_grid_selection(request).expect("configured One-row extraction");

    assert_eq!(result.text, "'1','Ada','2','Grace, Hopper'");
}

#[test]
fn extracts_multi_column_sql_in_as_row_value_tuples() {
    let result = extract_data_grid_selection(request(DataGridExtractorId::SqlInList)).expect("SQL IN extraction");
    assert_eq!(result.text, "((1, 'Ada'), (2, 'Grace, Hopper'))");
}

#[test]
fn sql_in_list_deduplicates_single_selected_values() {
    let mut request = request(DataGridExtractorId::SqlInList);
    request.selected_column_indexes = vec![0];
    request.rows = vec![
        vec![json!(1), json!("first")],
        vec![json!(1), json!("unselected value differs")],
        vec![json!(2), json!("last")],
    ];

    let result = extract_data_grid_selection(request).expect("SQL IN extraction");

    assert_eq!(result.text, "(1, 2)");
}

#[test]
fn sql_in_list_deduplicates_multi_column_tuples_in_first_seen_order() {
    let mut request = request(DataGridExtractorId::SqlInList);
    request.rows = vec![
        vec![json!(2), json!("Grace")],
        vec![json!(1), json!("Ada")],
        vec![json!(2), json!("Grace")],
        vec![json!(1), json!("Lovelace")],
        vec![json!(1), json!("Ada")],
    ];

    let result = extract_data_grid_selection(request).expect("SQL IN extraction");

    assert_eq!(result.text, "((2, 'Grace'), (1, 'Ada'), (1, 'Lovelace'))");
}

#[test]
fn sql_in_list_deduplicates_only_identical_rendered_literals() {
    let mut request = request(DataGridExtractorId::SqlInList);
    request.selected_column_indexes = vec![0];
    request.rows = vec![
        vec![json!(1)],
        vec![json!("1")],
        vec![Value::Null],
        vec![Value::Null],
        vec![json!("O'Reilly")],
        vec![json!("O'Reilly")],
    ];

    let result = extract_data_grid_selection(request).expect("SQL IN extraction");

    assert_eq!(result.text, "(1, '1', NULL, 'O''Reilly')");
}

#[test]
fn sql_in_list_honors_kingbase_bit_literals() {
    let mut request = request(DataGridExtractorId::SqlInList);
    request.database_type = Some(DatabaseType::Kingbase);
    request.identifier_quote = Some("`".to_string());
    request.columns = vec![column("flag", 0)];
    request.selected_column_indexes = vec![0];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "flags".to_string(),
        primary_keys: vec![],
        columns: Some(vec![DataGridColumnInfo {
            name: "flag".to_string(),
            data_type: "pg_catalog.bit(1)".to_string(),
            is_nullable: true,
            is_primary_key: false,
            column_default: None,
            extra: None,
        }]),
    });
    request.rows = vec![vec![json!("0")], vec![json!("1")]];

    let result = extract_data_grid_selection(request).expect("Kingbase SQL IN extraction");
    assert_eq!(result.text, "(b'0', b'1')");
}

#[test]
fn preserves_duplicate_json_columns_without_overwriting_values() {
    let mut request = request(DataGridExtractorId::Json);
    request.columns[1].display_name = "id".to_string();
    let result = extract_data_grid_selection(request).expect("JSON extraction");
    assert!(result.text.contains("\"id_2\""));
    assert_eq!(result.warnings.len(), 1);
}

#[test]
fn avoids_collisions_between_generated_and_existing_json_column_suffixes() {
    let mut request = request(DataGridExtractorId::Json);
    request.columns = vec![column("id", 0), column("id", 1), column("id_2", 2)];
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(1), json!(2), json!(3)]];

    let result = extract_data_grid_selection(request).expect("JSON extraction");
    let rows = serde_json::from_str::<Value>(&result.text).expect("valid JSON output");

    assert_eq!(rows, json!([{"id": 1, "id_3": 2, "id_2": 3}]));
    assert_eq!(result.warnings.len(), 1);
}

#[test]
fn builds_updates_with_hidden_primary_keys_and_selected_columns() {
    let mut request = request(DataGridExtractorId::SqlUpdates);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.selected_column_indexes = vec![1];
    let result = extract_data_grid_selection(request).expect("SQL Updates extraction");
    assert_eq!(
        result.text,
        "UPDATE \"public\".\"users\" SET \"name\" = 'Ada' WHERE \"id\" = 1;\nUPDATE \"public\".\"users\" SET \"name\" = 'Grace, Hopper' WHERE \"id\" = 2;"
    );
}

#[test]
fn sql_updates_keep_legacy_case_insensitive_primary_key_resolution() {
    let mut request = request(DataGridExtractorId::SqlUpdates);
    request.columns = vec![column("ID", 0), column("id", 1), column("name", 2)];
    request.selected_column_indexes = vec![1, 2];
    request.rows = vec![vec![json!(1), json!(99), json!("Ada")]];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "quoted_ids".to_string(),
        primary_keys: vec!["ID".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "ID".to_string(),
                data_type: "integer".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "integer".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });

    let result = extract_data_grid_selection(request).expect("SQL UPDATE extraction");

    assert_eq!(result.text, "UPDATE \"quoted_ids\" SET \"name\" = 'Ada' WHERE \"ID\" = 1;");
}

#[test]
fn sql_copy_honors_kingbase_mysql_compat_connection_identifier_quote() {
    let table_meta = DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("audit-schema".to_string()),
        table_name: "events".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "event_type".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    };

    let mut insert = request(DataGridExtractorId::SqlInserts);
    insert.database_type = Some(DatabaseType::Kingbase);
    insert.identifier_quote = Some("`".to_string());
    insert.table_meta = Some(table_meta.clone());
    insert.columns = vec![column("id", 0), column("event_type", 1)];
    insert.rows = vec![vec![json!(1), json!("login")]];
    let insert_result = extract_data_grid_selection(insert).expect("Kingbase SQL INSERT extraction");
    assert_eq!(insert_result.text, "INSERT INTO `audit-schema`.`events` (`id`, `event_type`) VALUES (1, 'login');");

    let mut updates = request(DataGridExtractorId::SqlUpdates);
    updates.database_type = Some(DatabaseType::Kingbase);
    updates.identifier_quote = Some("`".to_string());
    updates.table_meta = Some(table_meta);
    updates.columns = vec![column("id", 0), column("event_type", 1)];
    updates.rows = vec![vec![json!(1), json!("logout")]];
    let updates_result = extract_data_grid_selection(updates).expect("Kingbase SQL UPDATE extraction");
    assert_eq!(updates_result.text, "UPDATE `audit-schema`.`events` SET `event_type` = 'logout' WHERE `id` = 1;");
}

#[test]
fn sql_update_computed_column_option_matches_the_frontend_capability() {
    let mut request = request(DataGridExtractorId::SqlUpdates);
    request.columns[1].display_name = "search_text".to_string();
    request.columns[1].source_name = Some("search_text".to_string());
    request.selected_column_indexes = vec![1];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "search_text".to_string(),
                data_type: "text".to_string(),
                is_nullable: true,
                is_primary_key: false,
                column_default: None,
                extra: Some("GENERATED ALWAYS AS".to_string()),
            },
        ]),
    });

    let skipped = extract_data_grid_selection(request.clone()).expect_err("computed UPDATE is skipped by default");
    assert_eq!(skipped.code, DataGridExtractErrorCode::NoWritableColumns);

    request.options.sql.skip_computed_columns = false;
    let included = extract_data_grid_selection(request).expect("explicit computed UPDATE extraction");
    assert!(included.text.contains("SET \"search_text\" ="));
}

#[test]
fn rejects_null_primary_keys_fail_fast() {
    let mut request = request(DataGridExtractorId::SqlUpdates);
    request.rows[0][0] = Value::Null;
    request.selected_column_indexes = vec![1];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });
    let error = extract_data_grid_selection(request).expect_err("NULL primary key must fail");
    assert_eq!(error.code, DataGridExtractErrorCode::NullPrimaryKey);
}

#[test]
fn extracts_custom_dsv_with_headers_nulls_and_standard_escaping() {
    let mut request = request(DataGridExtractorId::Dsv);
    request.rows[0][0] = Value::Null;
    request.rows[0][1] = json!("Ada;Lovelace");
    request.options.dsv = DataGridDsvOptions {
        column_separator: ";".to_string(),
        include_column_header: true,
        ..DataGridDsvOptions::default()
    };
    let result = extract_data_grid_selection(request).expect("custom DSV extraction");
    assert_eq!(result.text, "id;name\nNULL;\"Ada;Lovelace\"\n2;Grace, Hopper");
}

#[test]
fn custom_dsv_honors_row_headers_and_quote_policy() {
    let mut request = request(DataGridExtractorId::Dsv);
    request.options.dsv = DataGridDsvOptions {
        column_separator: ";".to_string(),
        include_row_header: true,
        quote_policy: DataGridQuotePolicy::Always,
        ..DataGridDsvOptions::default()
    };

    let result = extract_data_grid_selection(request).expect("configured DSV extraction");

    assert_eq!(result.text, "\"1\";\"1\";\"Ada\"\n\"2\";\"2\";\"Grace, Hopper\"");
}

#[test]
fn extracts_json_lines_as_one_object_per_record() {
    let result = extract_data_grid_selection(request(DataGridExtractorId::JsonLines)).expect("JSON Lines extraction");
    assert_eq!(result.text.lines().count(), 2);
    let first_row =
        serde_json::from_str::<Value>(result.text.lines().next().unwrap_or_default()).expect("first JSON Lines record");
    assert_eq!(first_row, json!({"id": 1, "name": "Ada"}));
}

#[test]
fn extracts_compact_json_when_pretty_printing_is_disabled() {
    let mut request = request(DataGridExtractorId::Json);
    request.options.json.pretty = false;

    let result = extract_data_grid_selection(request).expect("compact JSON extraction");

    assert_eq!(result.text, "[{\"id\":1,\"name\":\"Ada\"},{\"id\":2,\"name\":\"Grace, Hopper\"}]");
}

#[test]
fn converts_snake_case_json_field_names_only_when_enabled() {
    let mut request = request(DataGridExtractorId::Json);
    request.columns = vec![
        column("ID", 0),
        column("CODE", 1),
        column("NAME", 2),
        column("CREATE_TIME", 3),
        column("user_id", 4),
        column("ORDER_ITEM_ID", 5),
        column("alreadyCamel", 6),
        column("_display__name_", 7),
        column("naïve_value", 8),
    ];
    request.selected_column_indexes = vec![0, 1, 2, 3, 4, 5, 6, 7, 8];
    request.rows = vec![vec![
        json!(1),
        json!("A01"),
        json!("Ada"),
        json!("2026-08-16T08:52:23Z"),
        json!(7),
        json!(9),
        json!("kept"),
        json!("Ada"),
        json!(true),
    ]];

    let unchanged = extract_data_grid_selection(request.clone()).expect("default JSON extraction");
    assert_eq!(
        serde_json::from_str::<Value>(&unchanged.text).expect("valid default JSON"),
        json!([{
            "ID": 1,
            "CODE": "A01",
            "NAME": "Ada",
            "CREATE_TIME": "2026-08-16T08:52:23Z",
            "user_id": 7,
            "ORDER_ITEM_ID": 9,
            "alreadyCamel": "kept",
            "_display__name_": "Ada",
            "naïve_value": true
        }])
    );

    request.options.json.camel_case_field_names = true;
    let converted = extract_data_grid_selection(request).expect("camel-case JSON extraction");
    assert_eq!(
        serde_json::from_str::<Value>(&converted.text).expect("valid converted JSON"),
        json!([{
            "id": 1,
            "code": "A01",
            "name": "Ada",
            "createTime": "2026-08-16T08:52:23Z",
            "userId": 7,
            "orderItemId": 9,
            "alreadyCamel": "kept",
            "displayName": "Ada",
            "naïveValue": true
        }])
    );
}

#[test]
fn converts_json_lines_names_and_suffixes_post_conversion_collisions() {
    let mut request = request(DataGridExtractorId::JsonLines);
    request.columns = vec![column("user_id", 0), column("userId", 1), column("___", 2)];
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(1), json!(2), json!(3)], vec![json!(4), json!(5), json!(6)]];
    request.options.json.camel_case_field_names = true;

    let result = extract_data_grid_selection(request).expect("camel-case JSON Lines extraction");
    let rows = result
        .text
        .lines()
        .map(|line| serde_json::from_str::<Value>(line).expect("valid JSON Lines record"))
        .collect::<Vec<_>>();

    assert_eq!(
        rows,
        vec![json!({"userId": 1, "userId_2": 2, "___": 3}), json!({"userId": 4, "userId_2": 5, "___": 6})]
    );
    assert_eq!(result.warnings.len(), 1);
    assert_eq!(result.warnings[0].code, DataGridExtractWarningCode::DuplicateJsonColumnNames);
}

#[test]
fn suffixes_collisions_after_normalizing_uppercase_json_names() {
    let mut request = request(DataGridExtractorId::Json);
    request.columns = vec![column("ID", 0), column("id", 1)];
    request.selected_column_indexes = vec![0, 1];
    request.rows = vec![vec![json!(1), json!(2)]];
    request.options.json.camel_case_field_names = true;

    let result = extract_data_grid_selection(request).expect("normalized JSON extraction");

    assert_eq!(serde_json::from_str::<Value>(&result.text).expect("valid JSON"), json!([{"id": 1, "id_2": 2}]));
    assert_eq!(result.warnings.len(), 1);
    assert_eq!(result.warnings[0].code, DataGridExtractWarningCode::DuplicateJsonColumnNames);
}

#[test]
fn emits_bare_values_for_single_column_json_array_when_values_only() {
    let mut request = request(DataGridExtractorId::Json);
    request.columns = vec![column("attendance_date", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!("2026-09-04")], vec![json!("2026-09-27")], vec![json!(null)]];
    request.options.json.values_only = true;

    let result = extract_data_grid_selection(request.clone()).expect("values-only JSON extraction");
    assert_eq!(
        serde_json::from_str::<Value>(&result.text).expect("valid JSON"),
        json!(["2026-09-04", "2026-09-27", null])
    );

    request.options.json.pretty = false;
    let compact = extract_data_grid_selection(request).expect("compact values-only JSON extraction");
    assert_eq!(compact.text, r#"["2026-09-04","2026-09-27",null]"#);
}

#[test]
fn keeps_object_json_arrays_when_values_only_selection_spans_multiple_columns() {
    let mut request = request(DataGridExtractorId::Json);
    request.columns = vec![column("attendance_date", 0), column("state", 1)];
    request.selected_column_indexes = vec![0, 1];
    request.rows = vec![vec![json!("2026-09-04"), json!("done")]];
    request.options.json.values_only = true;

    let result = extract_data_grid_selection(request).expect("multi-column JSON extraction");
    assert_eq!(
        serde_json::from_str::<Value>(&result.text).expect("valid JSON"),
        json!([{"attendance_date": "2026-09-04", "state": "done"}])
    );
}

#[test]
fn builds_null_safe_where_clause_predicates() {
    let mut request = request(DataGridExtractorId::WhereClause);
    request.selected_column_indexes = vec![1];
    request.rows = vec![vec![json!(1), Value::Null]];
    let result = extract_data_grid_selection(request).expect("WHERE extraction");
    assert_eq!(result.text, "\"name\" IS NULL");
}

#[test]
fn builds_select_for_one_explicit_cell() {
    let mut request = request(DataGridExtractorId::SqlSelect);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });
    request.selected_column_indexes = vec![1];
    request.rows = vec![vec![json!(7), json!("Ada")]];

    let result = extract_data_grid_selection(request).expect("SELECT extraction");

    assert_eq!(result.text, "SELECT * FROM \"public\".\"users\" WHERE \"name\" = 'Ada';");
}

#[test]
fn select_row_uses_complete_identity_including_hidden_columns() {
    let mut request = request(DataGridExtractorId::SqlSelect);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["tenant_id".to_string(), "id".to_string()],
        columns: None,
    });
    request.columns = vec![column("name", 0), column("id", 1), column("tenant_id", 2)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!("Ada"), json!(7), json!(9)]];
    request.selection_kind = DataGridSelectionKind::Rows;

    let result = extract_data_grid_selection(request).expect("SELECT extraction");

    assert_eq!(result.text, "SELECT * FROM \"users\" WHERE \"tenant_id\" = 9 AND \"id\" = 7;");
}

#[test]
fn select_row_falls_back_to_all_columns_without_usable_identity() {
    for primary_keys in [Vec::new(), vec!["id".to_string()]] {
        let mut request = request(DataGridExtractorId::SqlSelect);
        request.table_meta = Some(DataGridTableMeta {
            catalog: None,
            database: None,
            schema: None,
            table_name: "users".to_string(),
            primary_keys,
            columns: None,
        });
        request.rows = vec![vec![Value::Null, json!("Ada")]];
        request.selection_kind = DataGridSelectionKind::Rows;

        let result = extract_data_grid_selection(request).expect("SELECT extraction");

        assert_eq!(result.text, "SELECT * FROM \"users\" WHERE \"id\" IS NULL AND \"name\" = 'Ada';");
    }
}

#[test]
fn select_cells_joins_multiple_columns_and_rows() {
    // A genuine multi-cell selection (same-row columns AND'd, multi-row
    // selections OR'd) is allowed for SELECT and reuses the WHERE predicate
    // builder, matching "Copy as WHERE clause" for the same selection.
    let mut request = request(DataGridExtractorId::SqlSelect);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });

    let result = extract_data_grid_selection(request).expect("multi-cell SELECT extraction");

    assert_eq!(
        result.text,
        "SELECT * FROM \"users\" WHERE (\"id\" = 1 AND \"name\" = 'Ada') OR (\"id\" = 2 AND \"name\" = 'Grace, Hopper');"
    );
}

#[test]
fn select_rejects_ambiguous_selection_and_missing_target() {
    let error = extract_data_grid_selection(request(DataGridExtractorId::SqlSelect))
        .expect_err("cells without a table must fail");
    assert_eq!(error.code, DataGridExtractErrorCode::MissingTableMetadata);

    let mut request = request(DataGridExtractorId::SqlSelect);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: Vec::new(),
        columns: None,
    });
    request.selection_kind = DataGridSelectionKind::Columns;
    let error = extract_data_grid_selection(request.clone()).expect_err("column selection must fail");
    assert_eq!(error.code, DataGridExtractErrorCode::InvalidSelectSelection);

    request.selection_kind = DataGridSelectionKind::Rows;
    let error = extract_data_grid_selection(request).expect_err("multiple selected rows must fail");
    assert_eq!(error.code, DataGridExtractErrorCode::InvalidSelectSelection);
}

#[test]
fn where_clause_applies_mysql_json_cast() {
    // WHERE must reuse the UPDATE predicate builder so MySQL JSON columns get
    // CAST(... AS JSON) instead of a raw string literal.
    let mut request = request(DataGridExtractorId::WhereClause);
    request.database_type = Some(DatabaseType::Mysql);
    request.selected_column_indexes = vec![1];
    request.rows = vec![vec![json!(1), json!("{\"k\":1}")]];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "t".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "json".to_string(),
                is_nullable: true,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    let mut select_request = request.clone();
    select_request.extractor = DataGridExtractorId::SqlSelect;
    let result = extract_data_grid_selection(request).expect("WHERE extraction");
    assert!(
        result.text.contains("CAST(") && result.text.contains(" AS JSON)"),
        "expected MySQL JSON CAST predicate, got: {}",
        result.text
    );

    let select_result = extract_data_grid_selection(select_request).expect("SELECT extraction");
    assert!(
        select_result.text.starts_with("SELECT * FROM `t` WHERE ")
            && select_result.text.contains("CAST(")
            && select_result.text.contains(" AS JSON)"),
        "expected MySQL JSON CAST predicate in SELECT, got: {}",
        select_result.text
    );
}

#[test]
fn select_cells_falls_back_to_display_name_like_where_clause() {
    // SELECT built from a cell selection reuses write_where_clause, so a
    // missing source_name must fall back to display_name exactly like WHERE
    // does, instead of erroring — the two must stay in lockstep for the
    // identical selection.
    let mut request = request(DataGridExtractorId::SqlSelect);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });
    request.columns = vec![
        DataGridExtractColumn {
            display_name: "id".to_string(),
            source_name: None,
            source_index: 0,
            data_type: None,
            description: None,
        },
        column("name", 1),
    ];
    let mut where_request = request.clone();
    where_request.extractor = DataGridExtractorId::WhereClause;

    let result = extract_data_grid_selection(request).expect("SELECT extraction with missing source_name");
    assert_eq!(
        result.text,
        "SELECT * FROM \"users\" WHERE (\"id\" = 1 AND \"name\" = 'Ada') OR (\"id\" = 2 AND \"name\" = 'Grace, Hopper');"
    );

    let where_result = extract_data_grid_selection(where_request).expect("WHERE extraction with missing source_name");
    assert_eq!(where_result.text, "(\"id\" = 1 AND \"name\" = 'Ada') OR (\"id\" = 2 AND \"name\" = 'Grace, Hopper')");
}

#[test]
fn where_clause_rejects_unsupported_nosql_databases() {
    for database_type in [DatabaseType::MongoDb, DatabaseType::Neo4j, DatabaseType::Tdengine] {
        let mut request = request(DataGridExtractorId::WhereClause);
        request.database_type = Some(database_type);
        let error = extract_data_grid_selection(request).expect_err("WHERE must reject NoSQL");
        assert_eq!(error.code, DataGridExtractErrorCode::UnsupportedDatabase, "{database_type:?}");
    }
}

#[test]
fn build_data_grid_copy_update_statements_returns_empty_for_mongodb() {
    use crate::data_grid_sql::{build_data_grid_copy_update_statements, DataGridCopyUpdateStatementOptions};
    let options = DataGridCopyUpdateStatementOptions {
        database_type: Some(DatabaseType::MongoDb),
        identifier_quote: None,
        table_meta: DataGridTableMeta {
            catalog: None,
            database: None,
            schema: None,
            table_name: "t".to_string(),
            primary_keys: vec!["id".to_string()],
            columns: None,
        },
        columns: vec!["id".to_string()],
        source_columns: Some(vec![Some("id".to_string())]),
        rows: vec![vec![json!(1)]],
        include_database_name: false,
    };
    assert!(build_data_grid_copy_update_statements(options).is_empty());
}

#[test]
fn format_grid_sql_literal_uses_numeric_bool_for_sqlserver() {
    use crate::data_grid_sql::format_grid_sql_literal;
    let column = DataGridColumnInfo {
        name: "active".to_string(),
        data_type: "tinyint".to_string(),
        is_nullable: true,
        is_primary_key: false,
        column_default: None,
        extra: None,
    };
    // SQL Server has no TRUE/FALSE literals; emit 1/0 even for non-BIT columns.
    assert_eq!(format_grid_sql_literal(&json!(true), Some(DatabaseType::SqlServer), Some(&column)), "1");
    assert_eq!(format_grid_sql_literal(&json!(false), Some(DatabaseType::SqlServer), Some(&column)), "0");
    // Other dialects still emit TRUE/FALSE for non-bit columns.
    assert_eq!(format_grid_sql_literal(&json!(true), Some(DatabaseType::Postgres), Some(&column)), "TRUE");
}

#[test]
fn escapes_html_and_preserves_xml_null_semantics() {
    let mut html_request = request(DataGridExtractorId::Html);
    html_request.rows = vec![vec![json!(1), json!("<Ada & Grace>")]];
    let html = extract_data_grid_selection(html_request).expect("HTML extraction");
    assert!(html.text.contains("&lt;Ada &amp; Grace&gt;"));

    let mut xml_request = request(DataGridExtractorId::Xml);
    xml_request.rows = vec![vec![json!(1), Value::Null]];
    let xml = extract_data_grid_selection(xml_request).expect("XML extraction");
    assert!(xml.text.contains("name=\"name\" null=\"true\""));
}

#[test]
fn sql_insert_skips_generated_and_computed_columns() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.columns.push(column("search_text", 2));
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(7), json!("Ada"), json!("generated")]];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("auto_increment".to_string()),
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "search_text".to_string(),
                data_type: "text".to_string(),
                is_nullable: true,
                is_primary_key: false,
                column_default: None,
                extra: Some("GENERATED ALWAYS AS".to_string()),
            },
        ]),
    });
    let mut include_computed_request = request.clone();
    include_computed_request.options.sql.skip_computed_columns = false;
    let mut include_generated_request = request.clone();
    include_generated_request.options.sql.skip_generated_columns = false;

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");
    assert!(result.text.contains("name"));
    assert!(!result.text.contains("search_text"));
    assert!(result.text.contains("(\"id\", \"name\")"));
    assert_eq!(result.omitted_columns, vec!["search_text"]);

    let included =
        extract_data_grid_selection(include_computed_request).expect("SQL INSERT extraction with computed columns");
    assert!(included.text.contains("search_text"));
    assert!(included.omitted_columns.is_empty());

    let included =
        extract_data_grid_selection(include_generated_request).expect("SQL INSERT extraction with generated columns");
    assert!(included.text.contains("(\"id\", \"name\")"));
    assert_eq!(included.omitted_columns, vec!["search_text"]);
}

#[test]
fn sql_insert_keeps_autoincrement_primary_key_by_default() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("auto_increment".to_string()),
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert!(result.text.contains("INSERT INTO \"users\" (\"id\", \"name\")"));
    assert!(result.text.contains("(1, 'Ada')"));
    assert!(result.omitted_columns.is_empty());
}

#[test]
fn sql_insert_honors_primary_key_exclusion_and_row_by_row_mode() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "bigint".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("auto_increment".to_string()),
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeGenerated;
    request.options.sql.insert_mode = crate::data_grid_sql::DataGridCopyInsertMode::RowByRow;

    let result = extract_data_grid_selection(request).expect("row-by-row SQL INSERT extraction");

    assert_eq!(
        result.text,
        "INSERT INTO \"public\".\"users\" (\"name\") VALUES ('Ada');\nINSERT INTO \"public\".\"users\" (\"name\") VALUES ('Grace, Hopper');"
    );
    assert_eq!(result.omitted_columns, vec!["id"]);
}

#[test]
fn sql_insert_primary_key_exclusion_omits_postgres_serial_key() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.database_type = Some(DatabaseType::Postgres);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "integer".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("serial".to_string()),
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.rows = vec![vec![json!(1), json!("Ada")]];
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeGenerated;

    let result = extract_data_grid_selection(request).expect("PostgreSQL SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO \"public\".\"users\" (\"name\") VALUES ('Ada');");
    assert_eq!(result.omitted_columns, vec!["id"]);
}

#[test]
fn sql_insert_generated_primary_key_exclusion_keeps_manual_composite_key_members() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "daily_stats".to_string(),
        primary_keys: vec!["id".to_string(), "stat_date".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "bigint".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("auto_increment".to_string()),
            },
            DataGridColumnInfo {
                name: "stat_date".to_string(),
                data_type: "date".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.columns = vec![column("id", 0), column("stat_date", 1), column("name", 2)];
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(1), json!("2026-08-18"), json!("Ada")]];
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeGenerated;

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO \"daily_stats\" (\"stat_date\", \"name\") VALUES ('2026-08-18', 'Ada');");
    assert_eq!(result.omitted_columns, vec!["id"]);
}

#[test]
fn sql_insert_all_primary_key_mode_excludes_manual_composite_key_members() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "daily_stats".to_string(),
        primary_keys: vec!["id".to_string(), "stat_date".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "bigint".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("auto_increment".to_string()),
            },
            DataGridColumnInfo {
                name: "stat_date".to_string(),
                data_type: "date".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.columns = vec![column("id", 0), column("stat_date", 1), column("name", 2)];
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(1), json!("2026-08-18"), json!("Ada")]];
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeAll;

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO \"daily_stats\" (\"name\") VALUES ('Ada');");
    assert_eq!(result.omitted_columns, vec!["id", "stat_date"]);
}

#[test]
fn sql_insert_primary_key_exclusion_matches_unique_case_insensitive_mysql_primary_key_names() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.database_type = Some(DatabaseType::Mysql);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["ID".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.columns = vec![column("id", 0), column("name", 1)];
    request.selected_column_indexes = vec![0, 1];
    request.rows = vec![vec![json!(1), json!("Ada")]];
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeAll;

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO `users` (`name`) VALUES ('Ada');");
    assert_eq!(result.omitted_columns, vec!["id"]);
}

#[test]
fn sql_insert_preserves_case_insensitive_mysql_identity_primary_key_columns() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.database_type = Some(DatabaseType::Mysql);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["ID".to_string()],
        columns: Some(vec![DataGridColumnInfo {
            name: "id".to_string(),
            data_type: "int".to_string(),
            is_nullable: false,
            is_primary_key: false,
            column_default: None,
            extra: Some("auto_increment".to_string()),
        }]),
    });
    request.columns = vec![column("id", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!(1)]];

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO `users` (`id`) VALUES (1);");
    assert!(result.omitted_columns.is_empty());
}

#[test]
fn sql_insert_primary_key_exclusion_preserves_case_distinct_postgres_columns() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "quoted_ids".to_string(),
        primary_keys: vec!["ID".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "ID".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    request.columns = vec![column("ID", 0), column("id", 1), column("name", 2)];
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!("key"), json!("lowercase"), json!("Ada")]];
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeAll;

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO \"public\".\"quoted_ids\" (\"id\", \"name\") VALUES ('lowercase', 'Ada');");
    assert_eq!(result.omitted_columns, vec!["ID"]);
}

#[test]
fn sql_insert_primary_key_exclusion_uses_exact_postgres_names_with_incomplete_metadata() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "quoted_ids".to_string(),
        primary_keys: vec!["ID".to_string()],
        columns: Some(vec![DataGridColumnInfo {
            name: "ID".to_string(),
            data_type: "integer".to_string(),
            is_nullable: false,
            is_primary_key: true,
            column_default: None,
            extra: Some("identity".to_string()),
        }]),
    });
    request.columns = vec![column("id", 0), column("name", 1)];
    request.selected_column_indexes = vec![0, 1];
    request.rows = vec![vec![json!("lowercase"), json!("Ada")]];
    request.options.sql.primary_key_mode = crate::data_grid_sql::DataGridPrimaryKeyMode::ExcludeGenerated;

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO \"quoted_ids\" (\"id\", \"name\") VALUES ('lowercase', 'Ada');");
    assert!(result.omitted_columns.is_empty());
}

#[test]
fn sql_insert_uses_exact_column_metadata_for_case_distinct_identity_columns() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "quoted_ids".to_string(),
        primary_keys: vec!["ID".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "text".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "ID".to_string(),
                data_type: "integer".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: Some("identity".to_string()),
            },
        ]),
    });
    request.columns = vec![column("id", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!("lowercase")]];

    let result = extract_data_grid_selection(request).expect("SQL INSERT extraction");

    assert_eq!(result.text, "INSERT INTO \"quoted_ids\" (\"id\") VALUES ('lowercase');");
    assert!(result.omitted_columns.is_empty());
}

#[test]
fn rejects_invalid_dsv_configuration() {
    let mut request = request(DataGridExtractorId::Dsv);
    request.options.dsv.column_separator.clear();
    let error = extract_data_grid_selection(request).expect_err("empty separator must fail");
    assert_eq!(error.code, DataGridExtractErrorCode::InvalidDsvConfiguration);
}

#[test]
fn rejects_ambiguous_or_oversized_dsv_configuration() {
    let mut overlapping = request(DataGridExtractorId::Dsv);
    overlapping.options.dsv.column_separator = "|".to_string();
    overlapping.options.dsv.row_separator = "||".to_string();
    assert_eq!(
        extract_data_grid_selection(overlapping).expect_err("overlapping separators must fail").code,
        DataGridExtractErrorCode::InvalidDsvConfiguration
    );

    let mut conflicting_quote = request(DataGridExtractorId::Csv);
    conflicting_quote.options.dsv.quote = ',';
    assert_eq!(
        extract_data_grid_selection(conflicting_quote).expect_err("separator quote must fail").code,
        DataGridExtractErrorCode::InvalidDsvConfiguration
    );

    let mut oversized = request(DataGridExtractorId::Dsv);
    oversized.options.dsv.column_separator = "123456789".to_string();
    assert_eq!(
        extract_data_grid_selection(oversized).expect_err("oversized separator must fail").code,
        DataGridExtractErrorCode::InvalidDsvConfiguration
    );
}

#[test]
fn extractor_contract_uses_the_frontend_camel_case_wire_shape() {
    let request = request(DataGridExtractorId::CsvWithHeaders);
    let value = serde_json::to_value(&request).expect("serialize extractor request");

    assert_eq!(value["extractor"], "csv-with-headers");
    assert_eq!(value["databaseType"], "postgres");
    assert_eq!(value["selectedColumnIndexes"], json!([0, 1]));
    assert_eq!(value["selectionKind"], "cells");
    assert_eq!(value["options"]["dsv"]["includeColumnHeader"], false);
    assert_eq!(value["options"]["json"]["camelCaseFieldNames"], false);
    assert!(value.get("selected_column_indexes").is_none());

    let decoded = serde_json::from_value::<DataGridExtractRequest>(value).expect("deserialize extractor request");
    assert_eq!(decoded.extractor, DataGridExtractorId::CsvWithHeaders);
    assert_eq!(decoded.selected_column_indexes, vec![0, 1]);
}

#[test]
fn extractor_contract_requires_an_explicit_version() {
    let mut value = serde_json::to_value(request(DataGridExtractorId::Csv)).expect("serialize extractor request");
    value.as_object_mut().expect("request object").remove("version");

    let error = serde_json::from_value::<DataGridExtractRequest>(value).expect_err("missing version must fail");

    assert!(error.to_string().contains("version"));
}

#[test]
fn extractor_contract_rejects_unknown_or_misspelled_fields() {
    let mut value = serde_json::to_value(request(DataGridExtractorId::Csv)).expect("serialize extractor request");
    value["options"]["sql"]["skipComputedColumn"] = json!(false);

    let error = serde_json::from_value::<DataGridExtractRequest>(value).expect_err("unknown option must fail");

    assert!(error.to_string().contains("skipComputedColumn"));
}

#[test]
fn rejects_invalid_contract_and_column_mappings_fail_fast() {
    let mut unsupported = request(DataGridExtractorId::Csv);
    unsupported.version = 2;
    assert_eq!(
        extract_data_grid_selection(unsupported).expect_err("unsupported contract version").code,
        DataGridExtractErrorCode::UnsupportedVersion
    );

    let mut invalid_column = request(DataGridExtractorId::Csv);
    invalid_column.selected_column_indexes = vec![99];
    assert_eq!(
        extract_data_grid_selection(invalid_column).expect_err("invalid selected column").code,
        DataGridExtractErrorCode::InvalidColumnIndex
    );

    let mut duplicate_column = request(DataGridExtractorId::Csv);
    duplicate_column.selected_column_indexes = vec![0, 0];
    assert_eq!(
        extract_data_grid_selection(duplicate_column).expect_err("duplicate selected column").code,
        DataGridExtractErrorCode::InvalidColumnIndex
    );

    let mut invalid_mapping = request(DataGridExtractorId::Csv);
    invalid_mapping.columns[0].source_index = 99;
    assert_eq!(
        extract_data_grid_selection(invalid_mapping).expect_err("invalid source mapping").code,
        DataGridExtractErrorCode::InvalidColumnMapping
    );
}

#[test]
fn sql_updates_require_table_primary_key_and_writable_columns() {
    let missing_table = request(DataGridExtractorId::SqlUpdates);
    assert_eq!(
        extract_data_grid_selection(missing_table).expect_err("missing table metadata").code,
        DataGridExtractErrorCode::MissingTableMetadata
    );

    let mut missing_primary_key = request(DataGridExtractorId::SqlUpdates);
    missing_primary_key.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: Vec::new(),
        columns: None,
    });
    assert_eq!(
        extract_data_grid_selection(missing_primary_key).expect_err("missing primary key").code,
        DataGridExtractErrorCode::MissingPrimaryKey
    );

    let mut primary_key_only = request(DataGridExtractorId::SqlUpdates);
    primary_key_only.selected_column_indexes = vec![0];
    primary_key_only.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });
    assert_eq!(
        extract_data_grid_selection(primary_key_only).expect_err("primary-key-only update").code,
        DataGridExtractErrorCode::NoWritableColumns
    );
}

#[test]
fn sql_copy_reports_selected_opaque_aggregate_states_before_generic_builder_errors() {
    let opaque_column = DataGridColumnInfo {
        name: "v2".to_string(),
        data_type: "agg_state<sum(int)>".to_string(),
        is_nullable: true,
        is_primary_key: false,
        column_default: None,
        extra: None,
    };
    for extractor in
        [DataGridExtractorId::SqlInserts, DataGridExtractorId::SqlDeletesInserts, DataGridExtractorId::SqlUpdates]
    {
        let mut request = request(extractor);
        request.columns = vec![column("v2", 0)];
        request.selected_column_indexes = vec![0];
        request.rows = vec![vec![json!("0x00ff")]];
        request.table_meta = Some(DataGridTableMeta {
            catalog: None,
            database: None,
            schema: None,
            table_name: "states".to_string(),
            primary_keys: Vec::new(),
            columns: Some(vec![opaque_column.clone()]),
        });

        let error = extract_data_grid_selection(request).expect_err("opaque SQL copy must fail explicitly");
        assert_eq!(error.code, DataGridExtractErrorCode::NoWritableColumns);
        assert_eq!(
            error.message,
            "Doris aggregate-state columns are opaque and cannot be copied as automatic SQL; use an explicit Doris state function instead."
        );
    }
}

#[test]
fn rejects_requests_that_exceed_the_column_budget() {
    let mut request = request(DataGridExtractorId::Csv);
    request.columns =
        (0..=DATA_GRID_EXTRACTOR_MAX_COLUMNS).map(|index| column(&format!("column_{index}"), index)).collect();

    let error = extract_data_grid_selection(request).expect_err("oversized column list must fail");

    assert_eq!(error.code, DataGridExtractErrorCode::InputTooLarge);
}

#[test]
fn rejects_requests_that_exceed_the_selected_index_budget() {
    // columns list is small, but selected_column_indexes is oversized —
    // the budget must catch it before Vec::with_capacity allocates.
    let mut request = request(DataGridExtractorId::Csv);
    request.selected_column_indexes = (0..=DATA_GRID_EXTRACTOR_MAX_COLUMNS).collect();

    let error = extract_data_grid_selection(request).expect_err("oversized index list must fail");

    assert_eq!(error.code, DataGridExtractErrorCode::InputTooLarge);
}

#[test]
fn bounded_output_stops_before_allocating_past_the_limit() {
    let mut output = BoundedOutput::new(4);
    assert_eq!(output.write(b"1234").expect("bounded write"), 4);
    assert!(output.write(b"5").is_err());
    assert!(output.exceeded_limit());
    assert_eq!(output.into_bytes(), b"1234");
}

#[test]
fn rejects_requests_that_exceed_the_row_budget_before_mapping_values() {
    let mut request = request(DataGridExtractorId::Csv);
    request.rows = vec![Vec::new(); DATA_GRID_EXTRACTOR_MAX_ROWS + 1];

    let error = extract_data_grid_selection(request).expect_err("oversized request must fail");

    assert_eq!(error.code, DataGridExtractErrorCode::InputTooLarge);
}

#[test]
fn rejects_estimated_oversized_sql_before_building_the_statement() {
    let mut request = request(DataGridExtractorId::SqlInserts);
    request.columns = vec![column("payload", 0)];
    request.selected_column_indexes = vec![0];
    request.rows = vec![vec![json!("x".repeat(DATA_GRID_EXTRACTOR_MAX_OUTPUT_BYTES / 6 + 1))]];

    let error = extract_data_grid_selection(request).expect_err("oversized SQL must fail before allocation");

    assert_eq!(error.code, DataGridExtractErrorCode::OutputTooLarge);
}

#[test]
fn builds_deletes_inserts_merged_mode() {
    let mut request = request(DataGridExtractorId::SqlDeletesInserts);
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    let result = extract_data_grid_selection(request).expect("SQL DELETEs/INSERTs extraction");
    assert_eq!(
        result.text,
        "DELETE FROM \"public\".\"users\" WHERE \"id\" = 1;\nDELETE FROM \"public\".\"users\" WHERE \"id\" = 2;\nINSERT INTO \"public\".\"users\" (\"id\", \"name\") VALUES\n(1, 'Ada'),\n(2, 'Grace, Hopper');"
    );
}

#[test]
fn builds_deletes_inserts_row_by_row_mode() {
    let mut request = request(DataGridExtractorId::SqlDeletesInserts);
    request.options.sql.insert_mode = crate::data_grid_sql::DataGridCopyInsertMode::RowByRow;
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: Some("public".to_string()),
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    let result = extract_data_grid_selection(request).expect("SQL DELETEs/INSERTs row-by-row extraction");
    assert_eq!(
        result.text,
        "DELETE FROM \"public\".\"users\" WHERE \"id\" = 1;\nINSERT INTO \"public\".\"users\" (\"id\", \"name\") VALUES (1, 'Ada');\nDELETE FROM \"public\".\"users\" WHERE \"id\" = 2;\nINSERT INTO \"public\".\"users\" (\"id\", \"name\") VALUES (2, 'Grace, Hopper');"
    );
}

#[test]
fn builds_deletes_inserts_with_composite_primary_key() {
    let mut request = request(DataGridExtractorId::SqlDeletesInserts);
    request.columns = vec![column("tenant_id", 0), column("user_id", 1), column("role", 2)];
    request.selected_column_indexes = vec![0, 1, 2];
    request.rows = vec![vec![json!(10), json!(1), json!("admin")], vec![json!(10), json!(2), json!("member")]];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "members".to_string(),
        primary_keys: vec!["tenant_id".to_string(), "user_id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "tenant_id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "user_id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "role".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    let result = extract_data_grid_selection(request).expect("composite key SQL DELETEs/INSERTs");
    assert_eq!(
        result.text,
        "DELETE FROM \"members\" WHERE \"tenant_id\" = 10 AND \"user_id\" = 1;\nDELETE FROM \"members\" WHERE \"tenant_id\" = 10 AND \"user_id\" = 2;\nINSERT INTO \"members\" (\"tenant_id\", \"user_id\", \"role\") VALUES\n(10, 1, 'admin'),\n(10, 2, 'member');"
    );
}

#[test]
fn builds_deletes_inserts_with_hidden_primary_keys() {
    let mut request = request(DataGridExtractorId::SqlDeletesInserts);
    request.columns = vec![column("id", 0), column("name", 1)];
    // User only selected the "name" column (col 1), not "id" (col 0)
    request.selected_column_indexes = vec![1];
    request.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: Some(vec![
            DataGridColumnInfo {
                name: "id".to_string(),
                data_type: "int".to_string(),
                is_nullable: false,
                is_primary_key: true,
                column_default: None,
                extra: None,
            },
            DataGridColumnInfo {
                name: "name".to_string(),
                data_type: "varchar".to_string(),
                is_nullable: false,
                is_primary_key: false,
                column_default: None,
                extra: None,
            },
        ]),
    });
    let result = extract_data_grid_selection(request).expect("hidden PK SQL DELETEs/INSERTs");
    assert_eq!(
        result.text,
        "DELETE FROM \"users\" WHERE \"id\" = 1;\nDELETE FROM \"users\" WHERE \"id\" = 2;\nINSERT INTO \"users\" (\"name\") VALUES\n('Ada'),\n('Grace, Hopper');"
    );
}

#[test]
fn deletes_inserts_requires_table_meta_and_primary_key() {
    let missing_table = request(DataGridExtractorId::SqlDeletesInserts);
    assert_eq!(
        extract_data_grid_selection(missing_table).expect_err("missing table metadata").code,
        DataGridExtractErrorCode::MissingTableMetadata
    );

    let mut missing_pk = request(DataGridExtractorId::SqlDeletesInserts);
    missing_pk.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: Vec::new(),
        columns: None,
    });
    assert_eq!(
        extract_data_grid_selection(missing_pk).expect_err("missing primary key").code,
        DataGridExtractErrorCode::MissingPrimaryKey
    );

    let mut null_pk = request(DataGridExtractorId::SqlDeletesInserts);
    null_pk.rows = vec![vec![serde_json::Value::Null, json!("Ada")]];
    null_pk.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });
    assert_eq!(
        extract_data_grid_selection(null_pk).expect_err("null primary key").code,
        DataGridExtractErrorCode::NullPrimaryKey
    );

    let mut nosql = request(DataGridExtractorId::SqlDeletesInserts);
    nosql.database_type = Some(DatabaseType::MongoDb);
    nosql.table_meta = Some(DataGridTableMeta {
        catalog: None,
        database: None,
        schema: None,
        table_name: "users".to_string(),
        primary_keys: vec!["id".to_string()],
        columns: None,
    });
    assert_eq!(
        extract_data_grid_selection(nosql).expect_err("nosql unsupported").code,
        DataGridExtractErrorCode::UnsupportedDatabase
    );
}