vibesql-storage 0.2.0

// ============================================================================
// SQL Dump Tests
// ============================================================================

use vibesql_catalog::{ColumnSchema, TableSchema};
use vibesql_types::{DataType, SqlValue};

use crate::Database;

#[test]
fn test_save_sql_dump() {
    let mut db = Database::new();

    // Create test schema
    let schema = TableSchema::new(
        "test".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "name".to_string(),
                DataType::Varchar { max_length: Some(50) },
                false,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Insert test data
    let table = db.get_table_mut("test").unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(1),
            SqlValue::Varchar(arcstr::ArcStr::from("Alice")),
        ]))
        .unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(2),
            SqlValue::Varchar(arcstr::ArcStr::from("Bob")),
        ]))
        .unwrap();

    // Save SQL dump
    let path = "/tmp/test_db.sql";
    db.save_sql_dump(path).unwrap();

    // Verify file exists and contains expected content
    let content = std::fs::read_to_string(path).unwrap();
    assert!(content.contains("CREATE TABLE"));
    assert!(content.contains("INSERT INTO"));
    assert!(content.contains("Alice"));
    assert!(content.contains("Bob"));

    // Cleanup
    std::fs::remove_file(path).ok();
}

/// Issue #5619: a TableSchema carrying verbatim CREATE TABLE source text must
/// emit that text byte-for-byte in the SQL dump (instead of a normalized
/// reconstruction), so the original whitespace survives a save/reload round-trip
/// and sqlite_master.sql stays SQLite-compatible.
#[test]
fn test_sql_dump_emits_verbatim_create_source() {
    let mut db = Database::new();

    let mut schema = TableSchema::new(
        "test1".to_string(),
        vec![
            ColumnSchema::new("one".to_string(), DataType::Varchar { max_length: Some(10) }, true),
            ColumnSchema::new("two".to_string(), DataType::Varchar { max_length: None }, true),
        ],
    );
    let verbatim = "CREATE TABLE test1 (\n      one varchar(10),\n      two text\n    )";
    schema.set_sql_source(verbatim);
    db.create_table(schema).unwrap();

    let path = "/tmp/test_db_verbatim_5619.sql";
    db.save_sql_dump(path).unwrap();

    let content = std::fs::read_to_string(path).unwrap();
    assert!(
        content.contains(verbatim),
        "dump should contain the verbatim CREATE TABLE text, got:\n{}",
        content
    );

    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_value_to_literal() {
    use crate::persistence::save::sql_value_to_literal;

    assert_eq!(sql_value_to_literal(&SqlValue::Null), "NULL");
    assert_eq!(sql_value_to_literal(&SqlValue::Integer(42)), "42");
    assert_eq!(sql_value_to_literal(&SqlValue::Varchar(arcstr::ArcStr::from("test"))), "'test'");
    assert_eq!(
        sql_value_to_literal(&SqlValue::Varchar(arcstr::ArcStr::from("test's"))),
        "'test''s'"
    );
    assert_eq!(sql_value_to_literal(&SqlValue::Boolean(true)), "TRUE");
    assert_eq!(sql_value_to_literal(&SqlValue::Boolean(false)), "FALSE");
}

#[test]
fn test_sql_dump_with_nulls() {
    let mut db = Database::new();

    // Create test schema with nullable column
    let schema = TableSchema::new(
        "test_nulls".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "nullable_col".to_string(),
                DataType::Varchar { max_length: Some(50) },
                true,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Insert test data with NULL values
    let table = db.get_table_mut("test_nulls").unwrap();
    table.insert(crate::Row::new(vec![SqlValue::Integer(1), SqlValue::Null])).unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(2),
            SqlValue::Varchar(arcstr::ArcStr::from("text")),
        ]))
        .unwrap();
    table.insert(crate::Row::new(vec![SqlValue::Integer(3), SqlValue::Null])).unwrap();

    // Save SQL dump
    let path = "/tmp/test_nulls.sql";
    db.save_sql_dump(path).unwrap();

    // Verify SQL contains NULL literal
    let content = std::fs::read_to_string(path).unwrap();
    assert!(content.contains("NULL"), "SQL dump should contain NULL literal");
    assert!(content.contains("INSERT INTO test_nulls VALUES (1, NULL)"));
    assert!(content.contains("INSERT INTO test_nulls VALUES (2, 'text')"));
    assert!(content.contains("INSERT INTO test_nulls VALUES (3, NULL)"));

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_dump_with_quotes() {
    let mut db = Database::new();

    // Create test schema
    let schema = TableSchema::new(
        "test_quotes".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "text".to_string(),
                DataType::Varchar { max_length: Some(100) },
                false,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Insert test data with various quote types
    let table = db.get_table_mut("test_quotes").unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(1),
            SqlValue::Varchar(arcstr::ArcStr::from("it's")),
        ]))
        .unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(2),
            SqlValue::Varchar(arcstr::ArcStr::from("say \"hello\"")),
        ]))
        .unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(3),
            SqlValue::Varchar(arcstr::ArcStr::from("it's a \"test\"")),
        ]))
        .unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(4),
            SqlValue::Varchar(arcstr::ArcStr::from("")),
        ]))
        .unwrap();

    // Save SQL dump
    let path = "/tmp/test_quotes.sql";
    db.save_sql_dump(path).unwrap();

    // Verify SQL properly escapes quotes
    let content = std::fs::read_to_string(path).unwrap();
    assert!(content.contains("'it''s'"), "Single quotes should be escaped as ''");
    assert!(
        content.contains("'say \"hello\"'"),
        "Double quotes should be preserved in single-quoted strings"
    );
    assert!(content.contains("'it''s a \"test\"'"), "Mixed quotes should be handled correctly");
    assert!(content.contains("''"), "Empty string should be represented");

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_dump_empty_database() {
    let db = Database::new();

    // Save SQL dump of empty database
    let path = "/tmp/test_empty_db.sql";
    db.save_sql_dump(path).unwrap();

    // Verify file exists and contains valid SQL structure
    let content = std::fs::read_to_string(path).unwrap();
    assert!(content.contains("-- VibeSQL Database Dump"));
    assert!(content.contains("-- Schemas"));
    assert!(content.contains("-- Roles"));
    assert!(content.contains("-- Tables and Data"));
    assert!(content.contains("-- Indexes"));
    assert!(content.contains("-- End of dump"));

    // Verify no CREATE TABLE statements in empty database
    assert!(!content.contains("CREATE TABLE"));

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_dump_empty_table() {
    let mut db = Database::new();

    // Create test schema but insert no data
    let schema = TableSchema::new(
        "empty_table".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "name".to_string(),
                DataType::Varchar { max_length: Some(50) },
                false,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Save SQL dump
    let path = "/tmp/test_empty_table.sql";
    db.save_sql_dump(path).unwrap();

    // Verify SQL contains CREATE TABLE but no INSERT statements
    let content = std::fs::read_to_string(path).unwrap();
    assert!(content.contains("CREATE TABLE empty_table"));
    assert!(
        !content.contains("INSERT INTO empty_table"),
        "Empty table should have no INSERT statements"
    );

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_dump_with_indexes() {
    let mut db = Database::new();

    // Create test schema
    let schema = TableSchema::new(
        "test_indexes".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "name".to_string(),
                DataType::Varchar { max_length: Some(50) },
                false,
            ),
            ColumnSchema::new(
                "email".to_string(),
                DataType::Varchar { max_length: Some(100) },
                false,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Create indexes (use unqualified table name - will resolve to default schema)
    let idx1 = vibesql_ast::IndexColumn::Column {
        column_name: "name".to_string(),
        direction: vibesql_ast::OrderDirection::Asc,
        prefix_length: None,
    };
    db.create_index("idx_name".to_string(), "test_indexes".to_string(), false, vec![idx1]).unwrap();

    let idx2 = vibesql_ast::IndexColumn::Column {
        column_name: "email".to_string(),
        direction: vibesql_ast::OrderDirection::Asc,
        prefix_length: None,
    };
    db.create_index("idx_email_unique".to_string(), "test_indexes".to_string(), true, vec![idx2])
        .unwrap();

    // Save SQL dump
    let path = "/tmp/test_indexes.sql";
    db.save_sql_dump(path).unwrap();

    // Verify SQL contains CREATE INDEX statements
    let content = std::fs::read_to_string(path).unwrap();
    // Index names are normalized to lowercase
    assert!(content.contains("CREATE INDEX idx_name"), "Should contain CREATE INDEX idx_name");
    assert!(
        content.contains("CREATE UNIQUE INDEX idx_email_unique"),
        "Should contain CREATE UNIQUE INDEX idx_email_unique"
    );
    assert!(content.contains("ON test_indexes"), "Should reference test_indexes table");
    // ASC is the default sort order and is omitted from the emitted DDL; the
    // Debug form ("Asc") must never appear because it is not valid SQL.
    assert!(
        !content.contains("Asc"),
        "Debug-formatted sort direction must not leak into the SQL dump"
    );

    // Cleanup
    std::fs::remove_file(path).ok();
}

/// Regression test for #5561: indexes whose name, table name, or columns contain
/// special characters (spaces, single quotes, parens) must be emitted with quoted
/// identifiers so that the dump re-lexes correctly when the database is reloaded.
/// Previously the index DDL was emitted with raw identifiers, so a table named
/// `t1'x1` produced `CREATE INDEX i3 ON t1'x1 (b Asc, c Asc);`, which aborted the
/// reload with an unterminated-string-literal lexer error.
#[test]
fn test_sql_dump_index_quoted_identifiers_roundtrip() {
    use crate::persistence::load::read_sql_dump;

    let mut db = Database::new();
    // (parser is used below to prove the emitted DDL re-lexes)

    // Table name with an embedded single quote.
    let schema = TableSchema::new(
        "t1'x1".to_string(),
        vec![
            ColumnSchema::new("b".to_string(), DataType::Integer, false),
            ColumnSchema::new("c".to_string(), DataType::Integer, false),
        ],
    );
    db.create_table(schema).unwrap();

    // Index with a DESC column on the oddly-named table.
    let cols = vec![
        vibesql_ast::IndexColumn::Column {
            column_name: "b".to_string(),
            direction: vibesql_ast::OrderDirection::Desc,
            prefix_length: None,
        },
        vibesql_ast::IndexColumn::Column {
            column_name: "c".to_string(),
            direction: vibesql_ast::OrderDirection::Asc,
            prefix_length: None,
        },
    ];
    db.create_index("i3".to_string(), "t1'x1".to_string(), false, cols).unwrap();

    let path = "/tmp/test_index_quoted_roundtrip.sql";
    db.save_sql_dump(path).unwrap();

    let content = read_sql_dump(path).unwrap();
    // Table name must be double-quoted so the single quote does not start a string.
    assert!(
        content.contains("ON \"t1'x1\""),
        "Index DDL must quote the table name. Got:\n{content}"
    );
    // The Debug-formatted direction ("Asc") must not appear; DESC is explicit.
    assert!(!content.contains("Asc"), "Debug sort direction leaked:\n{content}");
    assert!(content.contains("DESC"), "DESC direction should be emitted:\n{content}");

    // The emitted CREATE INDEX statement must re-lex/parse cleanly. Before the
    // fix it produced a lexer error on the unquoted `t1'x1` table name.
    let create_index_stmt = content
        .lines()
        .find(|l| l.trim_start().starts_with("CREATE INDEX"))
        .unwrap_or_else(|| panic!("No CREATE INDEX line in dump:\n{content}"));
    vibesql_parser::Parser::parse_sql(create_index_stmt).unwrap_or_else(|e| {
        panic!("Emitted index DDL must re-parse: {e}\nStatement: {create_index_stmt}")
    });

    std::fs::remove_file(path).ok();
}

/// Regression test for #5579: the SQL dump must preserve the *original case* of
/// an index name, matching sqlite3 3.51.0 (whose `.dump` and `sqlite_master`
/// retain the spelling the user typed). The IndexManager keys indexes by a
/// lowercased storage key for case-insensitive lookup, but `metadata.index_name`
/// retains the original case, which is what the dump must emit. A mixed-case
/// name with embedded special characters also exercises the quoting path.
#[test]
fn test_sql_dump_preserves_index_name_case() {
    use crate::persistence::load::read_sql_dump;

    let mut db = Database::new();

    let schema = TableSchema::new(
        "t".to_string(),
        vec![
            ColumnSchema::new("b".to_string(), DataType::Integer, false),
            ColumnSchema::new("c".to_string(), DataType::Integer, false),
        ],
    );
    db.create_table(schema).unwrap();

    // Simple mixed-case name (no special chars): emitted unquoted, original case.
    let cols1 = vec![vibesql_ast::IndexColumn::Column {
        column_name: "b".to_string(),
        direction: vibesql_ast::OrderDirection::Asc,
        prefix_length: None,
    }];
    db.create_index("MyIdx".to_string(), "t".to_string(), false, cols1).unwrap();

    // Mixed-case name with spaces/parens: emitted quoted, original case preserved
    // (mirrors the repro in #5579: "x1 (b Asc, c Asc)").
    let cols2 = vec![
        vibesql_ast::IndexColumn::Column {
            column_name: "b".to_string(),
            direction: vibesql_ast::OrderDirection::Desc,
            prefix_length: None,
        },
        vibesql_ast::IndexColumn::Column {
            column_name: "c".to_string(),
            direction: vibesql_ast::OrderDirection::Asc,
            prefix_length: None,
        },
    ];
    db.create_index("x1 (b Asc, c Asc)".to_string(), "t".to_string(), false, cols2).unwrap();

    let path = "/tmp/test_index_name_case.sql";
    db.save_sql_dump(path).unwrap();

    let content = read_sql_dump(path).unwrap();

    // Original case retained, not lowercased.
    assert!(
        content.contains("CREATE INDEX MyIdx ON"),
        "Index name case must be preserved (expected `MyIdx`). Got:\n{content}"
    );
    assert!(!content.contains("myidx"), "Index name must not be lowercased. Got:\n{content}");
    assert!(
        content.contains("\"x1 (b Asc, c Asc)\""),
        "Quoted index name must preserve original case. Got:\n{content}"
    );
    assert!(
        !content.contains("x1 (b asc, c asc)"),
        "Quoted index name must not be lowercased. Got:\n{content}"
    );

    // Case-insensitive lookup is unaffected: a lowercased probe still finds the
    // index that was created with mixed case.
    assert!(db.index_exists("myidx"), "case-insensitive lookup must still work");

    // Both emitted CREATE INDEX statements must re-lex/parse cleanly (the dump
    // is text-only at this layer; a full execute round-trip lives in the
    // executor crate, which has the parser+executor wired together).
    for line in content.lines().filter(|l| l.trim_start().starts_with("CREATE INDEX")) {
        vibesql_parser::Parser::parse_sql(line)
            .unwrap_or_else(|e| panic!("Emitted index DDL must re-parse: {e}\nStatement: {line}"));
    }

    // Case-insensitive DROP removes the mixed-case index.
    db.drop_index("MYIDX").expect("case-insensitive DROP must drop the mixed-case index");
    assert!(!db.index_exists("MyIdx"), "index should be gone after case-insensitive drop");

    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_dump_all_data_types() {
    let mut db = Database::new();

    // Create test schema with all data types
    let schema = TableSchema::new(
        "all_types".to_string(),
        vec![
            // Integer types
            ColumnSchema::new("col_int".to_string(), DataType::Integer, true),
            ColumnSchema::new("col_smallint".to_string(), DataType::Smallint, true),
            ColumnSchema::new("col_bigint".to_string(), DataType::Bigint, true),
            // Float types
            ColumnSchema::new("col_float".to_string(), DataType::Float { precision: 24 }, true),
            ColumnSchema::new("col_real".to_string(), DataType::Real, true),
            ColumnSchema::new("col_double".to_string(), DataType::DoublePrecision, true),
            // String types
            ColumnSchema::new(
                "col_varchar".to_string(),
                DataType::Varchar { max_length: Some(100) },
                true,
            ),
            ColumnSchema::new("col_char".to_string(), DataType::Character { length: 10 }, true),
            // Boolean
            ColumnSchema::new("col_bool".to_string(), DataType::Boolean, true),
            // Numeric
            ColumnSchema::new(
                "col_numeric".to_string(),
                DataType::Numeric { precision: 10, scale: 2 },
                true,
            ),
            ColumnSchema::new(
                "col_decimal".to_string(),
                DataType::Decimal { precision: 5, scale: 0 },
                true,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Insert test data with various values
    let table = db.get_table_mut("all_types").unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(42),
            SqlValue::Smallint(100),
            SqlValue::Bigint(999999),
            SqlValue::Float(3.14),
            SqlValue::Real(2.718),
            SqlValue::Double(1.414),
            SqlValue::Varchar(arcstr::ArcStr::from("test")),
            SqlValue::Character(arcstr::ArcStr::from("fixed")),
            SqlValue::Boolean(true),
            SqlValue::Numeric(123.45),
            SqlValue::Numeric(999.0),
        ]))
        .unwrap();

    // Test special float values (NaN, Infinity)
    table
        .insert(crate::Row::new(vec![
            SqlValue::Null,
            SqlValue::Null,
            SqlValue::Null,
            SqlValue::Float(f32::NAN),
            SqlValue::Real(f64::INFINITY),
            SqlValue::Double(f64::NEG_INFINITY),
            SqlValue::Null,
            SqlValue::Null,
            SqlValue::Boolean(false),
            SqlValue::Null,
            SqlValue::Null,
        ]))
        .unwrap();

    // Save SQL dump
    let path = "/tmp/test_all_types.sql";
    db.save_sql_dump(path).unwrap();

    // Verify SQL contains all data type declarations
    let content = std::fs::read_to_string(path).unwrap();
    assert!(content.contains("INTEGER"));
    assert!(content.contains("SMALLINT"));
    assert!(content.contains("BIGINT"));
    assert!(content.contains("FLOAT(24)"));
    assert!(content.contains("REAL"));
    assert!(content.contains("DOUBLE PRECISION"));
    assert!(content.contains("VARCHAR(100)"));
    assert!(content.contains("CHAR(10)"));
    assert!(content.contains("BOOLEAN"));
    assert!(content.contains("NUMERIC(10, 2)"));
    assert!(content.contains("DECIMAL(5, 0)"));

    // Verify special float values
    assert!(content.contains("'NaN'"));
    assert!(content.contains("'Infinity'"));
    assert!(content.contains("'-Infinity'"));
    assert!(content.contains("TRUE"));
    assert!(content.contains("FALSE"));

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_sql_dump_large_dataset() {
    let mut db = Database::new();

    // Create test schema
    let schema = TableSchema::new(
        "large_table".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "value".to_string(),
                DataType::Varchar { max_length: Some(50) },
                false,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Insert 10,000 rows
    let table = db.get_table_mut("large_table").unwrap();
    for i in 0..10000 {
        table
            .insert(crate::Row::new(vec![
                SqlValue::Integer(i as i64),
                SqlValue::Varchar(arcstr::ArcStr::from(format!("value_{}", i))),
            ]))
            .unwrap();
    }

    // Save SQL dump and measure
    let path = "/tmp/test_large.sql";
    let start = std::time::Instant::now();
    db.save_sql_dump(path).unwrap();
    let duration = start.elapsed();

    // Verify export completed in reasonable time (should be fast)
    assert!(duration.as_secs() < 10, "Export took too long: {:?}", duration);

    // Verify file exists and has content
    let metadata = std::fs::metadata(path).unwrap();
    assert!(metadata.len() > 0, "File should have content");

    // Verify row count in file
    let content = std::fs::read_to_string(path).unwrap();
    let insert_count = content.matches("INSERT INTO large_table").count();
    assert_eq!(insert_count, 10000, "Should have 10,000 INSERT statements");

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_read_sql_dump() {
    use crate::persistence::load::read_sql_dump;

    let mut db = Database::new();

    // Create test schema
    let schema = TableSchema::new(
        "test_load".to_string(),
        vec![
            ColumnSchema::new("id".to_string(), DataType::Integer, false),
            ColumnSchema::new(
                "name".to_string(),
                DataType::Varchar { max_length: Some(50) },
                false,
            ),
        ],
    );

    db.create_table(schema).unwrap();

    // Insert test data
    let table = db.get_table_mut("test_load").unwrap();
    table
        .insert(crate::Row::new(vec![
            SqlValue::Integer(1),
            SqlValue::Varchar(arcstr::ArcStr::from("Test")),
        ]))
        .unwrap();

    // Save SQL dump
    let path = "/tmp/test_read_dump.sql";
    db.save_sql_dump(path).unwrap();

    // Read the dump back
    let content = read_sql_dump(path).unwrap();
    assert!(content.contains("CREATE TABLE"));
    assert!(content.contains("INSERT INTO"));
    assert!(content.contains("Test"));

    // Cleanup
    std::fs::remove_file(path).ok();
}

#[test]
fn test_read_sql_dump_file_not_found() {
    use crate::persistence::load::read_sql_dump;

    let result = read_sql_dump("/tmp/nonexistent_file_xyz123.sql");
    assert!(result.is_err());
    assert!(result.unwrap_err().to_string().contains("does not exist"));
}

// Issue #5618: the SQL dump must echo a table's *original* declared spelling
// (not the lowercase canonical catalog key) so a reload reproduces the same
// `sqlite_master.name`. A keyword-named identifier (e.g. `"create"`) must also
// be double-quoted so the reload re-lexes it as an identifier rather than the
// keyword token (which would otherwise come back uppercased as `CREATE`).
#[test]
fn test_sql_dump_preserves_quoted_keyword_table_name() {
    use vibesql_ast::TableIdentifier;

    let mut db = Database::new();
    let schema = TableSchema::new(
        "create".to_string(),
        vec![ColumnSchema::new("f1".to_string(), DataType::Integer, true)],
    );
    // Quoted reserved word as a table name (CREATE TABLE "create" (f1 int)).
    db.create_table_with_identifier(schema, TableIdentifier::new("create", true)).unwrap();

    let path = "/tmp/test_dump_quoted_keyword.sql";
    db.save_sql_dump(path).unwrap();
    let content = std::fs::read_to_string(path).unwrap();
    std::fs::remove_file(path).ok();

    // The keyword name must be quoted with its original (lowercase) spelling.
    assert!(
        content.contains("CREATE TABLE \"create\" ("),
        "dump should quote the keyword table name verbatim; got:\n{content}"
    );
    // It must NOT emit a bare `CREATE TABLE create (` which would re-lex as the
    // CREATE keyword and round-trip to the wrong case.
    assert!(
        !content.contains("CREATE TABLE create ("),
        "dump must not emit an unquoted keyword table name; got:\n{content}"
    );
}

#[test]
fn test_sql_dump_preserves_mixed_case_table_and_column_names() {
    use vibesql_ast::TableIdentifier;

    let mut db = Database::new();
    let schema = TableSchema::new(
        "MixedCase".to_string(),
        vec![
            ColumnSchema::new("ColA".to_string(), DataType::Integer, true),
            ColumnSchema::new("B".to_string(), DataType::Integer, true),
        ],
    );
    db.create_table_with_identifier(schema, TableIdentifier::new("MixedCase", true)).unwrap();

    let path = "/tmp/test_dump_mixed_case.sql";
    db.save_sql_dump(path).unwrap();
    let content = std::fs::read_to_string(path).unwrap();
    std::fs::remove_file(path).ok();

    // Original case must be preserved for both table and column names.
    assert!(
        content.contains("CREATE TABLE MixedCase ("),
        "dump should echo the original table case; got:\n{content}"
    );
    assert!(content.contains("ColA"), "dump should echo the original column case; got:\n{content}");
    // It must NOT have been folded to the lowercase canonical key.
    assert!(
        !content.contains("CREATE TABLE mixedcase ("),
        "dump must not fold the table name to lowercase; got:\n{content}"
    );
}