// Auto-generated by test-generator. Do not edit.
// Category: catalog

#[allow(unused_imports)]
use scythe_codegen as _codegen;
#[allow(unused_imports)]
use syn as _syn;

#[test]
fn test_alter_add_column() {
    // From: testing_data/catalog/alter_table/01_add_column.json
    // "ALTER TABLE ADD COLUMN adds a new column to an existing table"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
        "ALTER TABLE users ADD COLUMN email text NOT NULL",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 3, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "name", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(!table_users.columns[1].nullable, "column nullable for name");
    assert_eq!(table_users.columns[2].name, "email", "column name");
    assert_eq!(
        table_users.columns[2].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_users.columns[2].nullable,
        "column nullable for email"
    );
}

#[test]
fn test_alter_add_constraint() {
    // From: testing_data/catalog/alter_table/07_add_constraint.json
    // "ALTER TABLE ADD CONSTRAINT adds a foreign key constraint to an existing table"
    let schema_sql = &[
        "CREATE TABLE departments (id serial PRIMARY KEY, name text NOT NULL)",
        "CREATE TABLE employees (id serial PRIMARY KEY, name text NOT NULL, department_id integer NOT NULL)",
        "ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: departments
    let table_departments = catalog
        .get_table("departments")
        .expect("table departments should exist");
    assert_eq!(
        table_departments.columns.len(),
        2,
        "column count for table departments"
    );
    assert_eq!(table_departments.columns[0].name, "id", "column name");
    assert_eq!(
        table_departments.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_departments.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_departments.columns[1].name, "name", "column name");
    assert_eq!(
        table_departments.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_departments.columns[1].nullable,
        "column nullable for name"
    );

    // Assert table: employees
    let table_employees = catalog
        .get_table("employees")
        .expect("table employees should exist");
    assert_eq!(
        table_employees.columns.len(),
        3,
        "column count for table employees"
    );
    assert_eq!(table_employees.columns[0].name, "id", "column name");
    assert_eq!(
        table_employees.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_employees.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_employees.columns[1].name, "name", "column name");
    assert_eq!(
        table_employees.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_employees.columns[1].nullable,
        "column nullable for name"
    );
    assert_eq!(
        table_employees.columns[2].name, "department_id",
        "column name"
    );
    assert_eq!(
        table_employees.columns[2].sql_type, "integer",
        "column sql_type for department_id"
    );
    assert!(
        !table_employees.columns[2].nullable,
        "column nullable for department_id"
    );
}

#[test]
fn test_alter_column_type() {
    // From: testing_data/catalog/alter_table/04_alter_type.json
    // "ALTER TABLE ALTER COLUMN SET DATA TYPE changes a column type"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, age smallint NOT NULL)",
        "ALTER TABLE users ALTER COLUMN age SET DATA TYPE integer",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 2, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "age", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "integer",
        "column sql_type for age"
    );
    assert!(!table_users.columns[1].nullable, "column nullable for age");
}

#[test]
fn test_alter_drop_column() {
    // From: testing_data/catalog/alter_table/02_drop_column.json
    // "ALTER TABLE DROP COLUMN removes a column from an existing table"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL, nickname text, email text NOT NULL)",
        "ALTER TABLE users DROP COLUMN nickname",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 3, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "name", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(!table_users.columns[1].nullable, "column nullable for name");
    assert_eq!(table_users.columns[2].name, "email", "column name");
    assert_eq!(
        table_users.columns[2].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_users.columns[2].nullable,
        "column nullable for email"
    );
}

#[test]
fn test_alter_drop_not_null() {
    // From: testing_data/catalog/alter_table/06_drop_not_null.json
    // "ALTER TABLE ALTER COLUMN DROP NOT NULL makes a required column nullable"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, email text NOT NULL)",
        "ALTER TABLE users ALTER COLUMN email DROP NOT NULL",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 2, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "email", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for email"
    );
    assert!(table_users.columns[1].nullable, "column nullable for email");
}

#[test]
fn test_alter_rename_column() {
    // From: testing_data/catalog/alter_table/03_rename_column.json
    // "ALTER TABLE RENAME COLUMN changes a column name"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
        "ALTER TABLE users RENAME COLUMN name TO full_name",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 2, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "full_name", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for full_name"
    );
    assert!(
        !table_users.columns[1].nullable,
        "column nullable for full_name"
    );
}

#[test]
fn test_alter_rename_table() {
    // From: testing_data/catalog/alter_table/08_rename_table.json
    // "ALTER TABLE RENAME TO changes the table name"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL)",
        "ALTER TABLE users RENAME TO people",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: people
    let table_people = catalog
        .get_table("people")
        .expect("table people should exist");
    assert_eq!(
        table_people.columns.len(),
        2,
        "column count for table people"
    );
    assert_eq!(table_people.columns[0].name, "id", "column name");
    assert_eq!(
        table_people.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_people.columns[0].nullable, "column nullable for id");
    assert_eq!(table_people.columns[1].name, "name", "column name");
    assert_eq!(
        table_people.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_people.columns[1].nullable,
        "column nullable for name"
    );
}

#[test]
fn test_alter_set_not_null() {
    // From: testing_data/catalog/alter_table/05_set_not_null.json
    // "ALTER TABLE ALTER COLUMN SET NOT NULL makes a nullable column required"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, email text)",
        "ALTER TABLE users ALTER COLUMN email SET NOT NULL",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 2, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "email", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_users.columns[1].nullable,
        "column nullable for email"
    );
}

#[test]
fn test_basic_composite() {
    // From: testing_data/catalog/create_composite/01_basic_composite.json
    // "CREATE TYPE AS composite with text fields for an address"
    let schema_sql = &["CREATE TYPE address AS (street text, city text, zip text)"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert composite: address
    let comp_address = catalog
        .get_composite("address")
        .expect("composite address should exist");
    assert_eq!(
        comp_address.fields.len(),
        3,
        "field count for composite address"
    );
    assert_eq!(
        comp_address.fields[0].name, "street",
        "composite field name"
    );
    assert_eq!(
        comp_address.fields[0].sql_type, "text",
        "composite field sql_type for street"
    );
    assert_eq!(comp_address.fields[1].name, "city", "composite field name");
    assert_eq!(
        comp_address.fields[1].sql_type, "text",
        "composite field sql_type for city"
    );
    assert_eq!(comp_address.fields[2].name, "zip", "composite field name");
    assert_eq!(
        comp_address.fields[2].sql_type, "text",
        "composite field sql_type for zip"
    );
}

#[test]
fn test_composite_with_types() {
    // From: testing_data/catalog/create_composite/02_composite_with_types.json
    // "Composite type with various field types including arrays"
    let schema_sql = &[
        "CREATE TYPE inventory_item AS (name text, supplier_id integer, price numeric(10,2), tags text[], in_stock boolean)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert composite: inventory_item
    let comp_inventory_item = catalog
        .get_composite("inventory_item")
        .expect("composite inventory_item should exist");
    assert_eq!(
        comp_inventory_item.fields.len(),
        5,
        "field count for composite inventory_item"
    );
    assert_eq!(
        comp_inventory_item.fields[0].name, "name",
        "composite field name"
    );
    assert_eq!(
        comp_inventory_item.fields[0].sql_type, "text",
        "composite field sql_type for name"
    );
    assert_eq!(
        comp_inventory_item.fields[1].name, "supplier_id",
        "composite field name"
    );
    assert_eq!(
        comp_inventory_item.fields[1].sql_type, "integer",
        "composite field sql_type for supplier_id"
    );
    assert_eq!(
        comp_inventory_item.fields[2].name, "price",
        "composite field name"
    );
    assert_eq!(
        comp_inventory_item.fields[2].sql_type, "numeric(10,2)",
        "composite field sql_type for price"
    );
    assert_eq!(
        comp_inventory_item.fields[3].name, "tags",
        "composite field name"
    );
    assert_eq!(
        comp_inventory_item.fields[3].sql_type, "text[]",
        "composite field sql_type for tags"
    );
    assert_eq!(
        comp_inventory_item.fields[4].name, "in_stock",
        "composite field name"
    );
    assert_eq!(
        comp_inventory_item.fields[4].sql_type, "boolean",
        "composite field sql_type for in_stock"
    );
}

#[test]
fn test_basic_domain() {
    // From: testing_data/catalog/create_domain/01_basic_domain.json
    // "CREATE DOMAIN with CHECK constraint for positive integers"
    let schema_sql = &["CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0)"];

    let _catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
}

#[test]
fn test_domain_not_null() {
    // From: testing_data/catalog/create_domain/02_domain_not_null.json
    // "CREATE DOMAIN with NOT NULL constraint"
    let schema_sql = &["CREATE DOMAIN non_empty_text AS text NOT NULL"];

    let _catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();
}

#[test]
fn test_alter_enum_add_value() {
    // From: testing_data/catalog/create_enum/02_alter_enum_add_value.json
    // "ALTER TYPE ADD VALUE appends a new value to an existing enum"
    let schema_sql = &[
        "CREATE TYPE status AS ENUM ('active', 'inactive')",
        "ALTER TYPE status ADD VALUE 'banned'",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert enum: status
    let enum_status = catalog
        .get_enum("status")
        .expect("enum status should exist");
    assert_eq!(
        enum_status.values,
        vec!["active", "inactive", "banned"],
        "enum values for status"
    );
}

#[test]
fn test_alter_enum_rename_value() {
    // From: testing_data/catalog/create_enum/03_alter_enum_rename_value.json
    // "ALTER TYPE RENAME VALUE changes an existing enum value name"
    let schema_sql = &[
        "CREATE TYPE status AS ENUM ('active', 'inactive', 'banned')",
        "ALTER TYPE status RENAME VALUE 'banned' TO 'suspended'",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert enum: status
    let enum_status = catalog
        .get_enum("status")
        .expect("enum status should exist");
    assert_eq!(
        enum_status.values,
        vec!["active", "inactive", "suspended"],
        "enum values for status"
    );
}

#[test]
fn test_basic_enum() {
    // From: testing_data/catalog/create_enum/01_basic_enum.json
    // "CREATE TYPE AS ENUM with three values"
    let schema_sql = &["CREATE TYPE status AS ENUM ('active', 'inactive', 'banned')"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert enum: status
    let enum_status = catalog
        .get_enum("status")
        .expect("enum status should exist");
    assert_eq!(
        enum_status.values,
        vec!["active", "inactive", "banned"],
        "enum values for status"
    );
}

#[test]
fn test_multiple_enums() {
    // From: testing_data/catalog/create_enum/04_multiple_enums.json
    // "Multiple enum types defined together"
    let schema_sql = &[
        "CREATE TYPE status AS ENUM ('active', 'inactive')",
        "CREATE TYPE role AS ENUM ('admin', 'editor', 'viewer')",
        "CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'critical')",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert enum: status
    let enum_status = catalog
        .get_enum("status")
        .expect("enum status should exist");
    assert_eq!(
        enum_status.values,
        vec!["active", "inactive"],
        "enum values for status"
    );

    // Assert enum: priority
    let enum_priority = catalog
        .get_enum("priority")
        .expect("enum priority should exist");
    assert_eq!(
        enum_priority.values,
        vec!["low", "medium", "high", "critical"],
        "enum values for priority"
    );

    // Assert enum: role
    let enum_role = catalog.get_enum("role").expect("enum role should exist");
    assert_eq!(
        enum_role.values,
        vec!["admin", "editor", "viewer"],
        "enum values for role"
    );
}

#[test]
fn test_all_string_types() {
    // From: testing_data/catalog/create_table/03_all_string_types.json
    // "Table with all PostgreSQL string types: text, varchar, char, bytea"
    let schema_sql = &["CREATE TABLE string_types (a text, b varchar(255), c char(10), d bytea)"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: string_types
    let table_string_types = catalog
        .get_table("string_types")
        .expect("table string_types should exist");
    assert_eq!(
        table_string_types.columns.len(),
        4,
        "column count for table string_types"
    );
    assert_eq!(table_string_types.columns[0].name, "a", "column name");
    assert_eq!(
        table_string_types.columns[0].sql_type, "text",
        "column sql_type for a"
    );
    assert!(
        table_string_types.columns[0].nullable,
        "column nullable for a"
    );
    assert_eq!(table_string_types.columns[1].name, "b", "column name");
    assert_eq!(
        table_string_types.columns[1].sql_type, "varchar(255)",
        "column sql_type for b"
    );
    assert!(
        table_string_types.columns[1].nullable,
        "column nullable for b"
    );
    assert_eq!(table_string_types.columns[2].name, "c", "column name");
    assert_eq!(
        table_string_types.columns[2].sql_type, "char(10)",
        "column sql_type for c"
    );
    assert!(
        table_string_types.columns[2].nullable,
        "column nullable for c"
    );
    assert_eq!(table_string_types.columns[3].name, "d", "column name");
    assert_eq!(
        table_string_types.columns[3].sql_type, "bytea",
        "column sql_type for d"
    );
    assert!(
        table_string_types.columns[3].nullable,
        "column nullable for d"
    );
}

#[test]
fn test_array_columns() {
    // From: testing_data/catalog/create_table/08_array_columns.json
    // "Table with array column types: int[], text[], boolean[]"
    let schema_sql =
        &["CREATE TABLE arrays_example (id serial, int_list int[], tags text[], flags boolean[])"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: arrays_example
    let table_arrays_example = catalog
        .get_table("arrays_example")
        .expect("table arrays_example should exist");
    assert_eq!(
        table_arrays_example.columns.len(),
        4,
        "column count for table arrays_example"
    );
    assert_eq!(table_arrays_example.columns[0].name, "id", "column name");
    assert_eq!(
        table_arrays_example.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_arrays_example.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(
        table_arrays_example.columns[1].name, "int_list",
        "column name"
    );
    assert_eq!(
        table_arrays_example.columns[1].sql_type, "int[]",
        "column sql_type for int_list"
    );
    assert!(
        table_arrays_example.columns[1].nullable,
        "column nullable for int_list"
    );
    assert_eq!(table_arrays_example.columns[2].name, "tags", "column name");
    assert_eq!(
        table_arrays_example.columns[2].sql_type, "text[]",
        "column sql_type for tags"
    );
    assert!(
        table_arrays_example.columns[2].nullable,
        "column nullable for tags"
    );
    assert_eq!(table_arrays_example.columns[3].name, "flags", "column name");
    assert_eq!(
        table_arrays_example.columns[3].sql_type, "boolean[]",
        "column sql_type for flags"
    );
    assert!(
        table_arrays_example.columns[3].nullable,
        "column nullable for flags"
    );
}

#[test]
fn test_basic_table() {
    // From: testing_data/catalog/create_table/01_basic_table.json
    // "Simple table with id, name, and created_at columns"
    let schema_sql = &[
        "CREATE TABLE users (id integer NOT NULL, name text NOT NULL, created_at timestamp NOT NULL)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 3, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "name", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(!table_users.columns[1].nullable, "column nullable for name");
    assert_eq!(table_users.columns[2].name, "created_at", "column name");
    assert_eq!(
        table_users.columns[2].sql_type, "timestamp",
        "column sql_type for created_at"
    );
    assert!(
        !table_users.columns[2].nullable,
        "column nullable for created_at"
    );
}

#[test]
fn test_boolean_and_uuid() {
    // From: testing_data/catalog/create_table/05_boolean_and_uuid.json
    // "Table with boolean and uuid column types"
    let schema_sql =
        &["CREATE TABLE flags (id uuid NOT NULL, is_active boolean NOT NULL, is_deleted boolean)"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: flags
    let table_flags = catalog
        .get_table("flags")
        .expect("table flags should exist");
    assert_eq!(table_flags.columns.len(), 3, "column count for table flags");
    assert_eq!(table_flags.columns[0].name, "id", "column name");
    assert_eq!(
        table_flags.columns[0].sql_type, "uuid",
        "column sql_type for id"
    );
    assert!(!table_flags.columns[0].nullable, "column nullable for id");
    assert_eq!(table_flags.columns[1].name, "is_active", "column name");
    assert_eq!(
        table_flags.columns[1].sql_type, "boolean",
        "column sql_type for is_active"
    );
    assert!(
        !table_flags.columns[1].nullable,
        "column nullable for is_active"
    );
    assert_eq!(table_flags.columns[2].name, "is_deleted", "column name");
    assert_eq!(
        table_flags.columns[2].sql_type, "boolean",
        "column sql_type for is_deleted"
    );
    assert!(
        table_flags.columns[2].nullable,
        "column nullable for is_deleted"
    );
}

#[test]
fn test_catalog_all_numeric_types() {
    // From: testing_data/catalog/create_table/02_all_numeric_types.json
    // "Table with all PostgreSQL numeric types: smallint, integer, bigint, real, double precision, numeric, serial, bigserial"
    let schema_sql = &[
        "CREATE TABLE numeric_types (a smallint, b integer, c bigint, d real, e double precision, f numeric(10,2), g serial, h bigserial)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: numeric_types
    let table_numeric_types = catalog
        .get_table("numeric_types")
        .expect("table numeric_types should exist");
    assert_eq!(
        table_numeric_types.columns.len(),
        8,
        "column count for table numeric_types"
    );
    assert_eq!(table_numeric_types.columns[0].name, "a", "column name");
    assert_eq!(
        table_numeric_types.columns[0].sql_type, "smallint",
        "column sql_type for a"
    );
    assert!(
        table_numeric_types.columns[0].nullable,
        "column nullable for a"
    );
    assert_eq!(table_numeric_types.columns[1].name, "b", "column name");
    assert_eq!(
        table_numeric_types.columns[1].sql_type, "integer",
        "column sql_type for b"
    );
    assert!(
        table_numeric_types.columns[1].nullable,
        "column nullable for b"
    );
    assert_eq!(table_numeric_types.columns[2].name, "c", "column name");
    assert_eq!(
        table_numeric_types.columns[2].sql_type, "bigint",
        "column sql_type for c"
    );
    assert!(
        table_numeric_types.columns[2].nullable,
        "column nullable for c"
    );
    assert_eq!(table_numeric_types.columns[3].name, "d", "column name");
    assert_eq!(
        table_numeric_types.columns[3].sql_type, "real",
        "column sql_type for d"
    );
    assert!(
        table_numeric_types.columns[3].nullable,
        "column nullable for d"
    );
    assert_eq!(table_numeric_types.columns[4].name, "e", "column name");
    assert_eq!(
        table_numeric_types.columns[4].sql_type, "double precision",
        "column sql_type for e"
    );
    assert!(
        table_numeric_types.columns[4].nullable,
        "column nullable for e"
    );
    assert_eq!(table_numeric_types.columns[5].name, "f", "column name");
    assert_eq!(
        table_numeric_types.columns[5].sql_type, "numeric(10,2)",
        "column sql_type for f"
    );
    assert!(
        table_numeric_types.columns[5].nullable,
        "column nullable for f"
    );
    assert_eq!(table_numeric_types.columns[6].name, "g", "column name");
    assert_eq!(
        table_numeric_types.columns[6].sql_type, "integer",
        "column sql_type for g"
    );
    assert!(
        !table_numeric_types.columns[6].nullable,
        "column nullable for g"
    );
    assert_eq!(table_numeric_types.columns[7].name, "h", "column name");
    assert_eq!(
        table_numeric_types.columns[7].sql_type, "bigint",
        "column sql_type for h"
    );
    assert!(
        !table_numeric_types.columns[7].nullable,
        "column nullable for h"
    );
}

#[test]
fn test_catalog_network_types() {
    // From: testing_data/catalog/create_table/07_network_types.json
    // "Table with PostgreSQL network types: inet, cidr, macaddr"
    let schema_sql = &[
        "CREATE TABLE network_info (id serial, ip_address inet NOT NULL, subnet cidr, mac macaddr)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: network_info
    let table_network_info = catalog
        .get_table("network_info")
        .expect("table network_info should exist");
    assert_eq!(
        table_network_info.columns.len(),
        4,
        "column count for table network_info"
    );
    assert_eq!(table_network_info.columns[0].name, "id", "column name");
    assert_eq!(
        table_network_info.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_network_info.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(
        table_network_info.columns[1].name, "ip_address",
        "column name"
    );
    assert_eq!(
        table_network_info.columns[1].sql_type, "inet",
        "column sql_type for ip_address"
    );
    assert!(
        !table_network_info.columns[1].nullable,
        "column nullable for ip_address"
    );
    assert_eq!(table_network_info.columns[2].name, "subnet", "column name");
    assert_eq!(
        table_network_info.columns[2].sql_type, "cidr",
        "column sql_type for subnet"
    );
    assert!(
        table_network_info.columns[2].nullable,
        "column nullable for subnet"
    );
    assert_eq!(table_network_info.columns[3].name, "mac", "column name");
    assert_eq!(
        table_network_info.columns[3].sql_type, "macaddr",
        "column sql_type for mac"
    );
    assert!(
        table_network_info.columns[3].nullable,
        "column nullable for mac"
    );
}

#[test]
fn test_constraints_check() {
    // From: testing_data/catalog/create_table/14_constraints_check.json
    // "CHECK constraints on columns"
    let schema_sql = &[
        "CREATE TABLE products (id serial PRIMARY KEY, name text NOT NULL, price numeric(10,2) NOT NULL CHECK (price > 0), quantity integer NOT NULL CHECK (quantity >= 0))",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: products
    let table_products = catalog
        .get_table("products")
        .expect("table products should exist");
    assert_eq!(
        table_products.columns.len(),
        4,
        "column count for table products"
    );
    assert_eq!(table_products.columns[0].name, "id", "column name");
    assert_eq!(
        table_products.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_products.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_products.columns[1].name, "name", "column name");
    assert_eq!(
        table_products.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_products.columns[1].nullable,
        "column nullable for name"
    );
    assert_eq!(table_products.columns[2].name, "price", "column name");
    assert_eq!(
        table_products.columns[2].sql_type, "numeric(10,2)",
        "column sql_type for price"
    );
    assert!(
        !table_products.columns[2].nullable,
        "column nullable for price"
    );
    assert_eq!(table_products.columns[3].name, "quantity", "column name");
    assert_eq!(
        table_products.columns[3].sql_type, "integer",
        "column sql_type for quantity"
    );
    assert!(
        !table_products.columns[3].nullable,
        "column nullable for quantity"
    );
}

#[test]
fn test_constraints_default() {
    // From: testing_data/catalog/create_table/13_constraints_default.json
    // "Columns with DEFAULT values"
    let schema_sql = &[
        "CREATE TABLE posts (id serial PRIMARY KEY, title text NOT NULL, status text NOT NULL DEFAULT 'draft', view_count integer NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now())",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: posts
    let table_posts = catalog
        .get_table("posts")
        .expect("table posts should exist");
    assert_eq!(table_posts.columns.len(), 5, "column count for table posts");
    assert_eq!(table_posts.columns[0].name, "id", "column name");
    assert_eq!(
        table_posts.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_posts.columns[0].nullable, "column nullable for id");
    assert_eq!(table_posts.columns[1].name, "title", "column name");
    assert_eq!(
        table_posts.columns[1].sql_type, "text",
        "column sql_type for title"
    );
    assert!(
        !table_posts.columns[1].nullable,
        "column nullable for title"
    );
    assert_eq!(table_posts.columns[2].name, "status", "column name");
    assert_eq!(
        table_posts.columns[2].sql_type, "text",
        "column sql_type for status"
    );
    assert!(
        !table_posts.columns[2].nullable,
        "column nullable for status"
    );
    assert_eq!(table_posts.columns[3].name, "view_count", "column name");
    assert_eq!(
        table_posts.columns[3].sql_type, "integer",
        "column sql_type for view_count"
    );
    assert!(
        !table_posts.columns[3].nullable,
        "column nullable for view_count"
    );
    assert_eq!(table_posts.columns[4].name, "created_at", "column name");
    assert_eq!(
        table_posts.columns[4].sql_type, "timestamptz",
        "column sql_type for created_at"
    );
    assert!(
        !table_posts.columns[4].nullable,
        "column nullable for created_at"
    );
}

#[test]
fn test_constraints_foreign_key() {
    // From: testing_data/catalog/create_table/11_constraints_foreign_key.json
    // "Foreign key constraint referencing another table"
    let schema_sql = &[
        "CREATE TABLE authors (id serial PRIMARY KEY, name text NOT NULL)",
        "CREATE TABLE books (id serial PRIMARY KEY, title text NOT NULL, author_id integer NOT NULL REFERENCES authors (id))",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: authors
    let table_authors = catalog
        .get_table("authors")
        .expect("table authors should exist");
    assert_eq!(
        table_authors.columns.len(),
        2,
        "column count for table authors"
    );
    assert_eq!(table_authors.columns[0].name, "id", "column name");
    assert_eq!(
        table_authors.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_authors.columns[0].nullable, "column nullable for id");
    assert_eq!(table_authors.columns[1].name, "name", "column name");
    assert_eq!(
        table_authors.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_authors.columns[1].nullable,
        "column nullable for name"
    );

    // Assert table: books
    let table_books = catalog
        .get_table("books")
        .expect("table books should exist");
    assert_eq!(table_books.columns.len(), 3, "column count for table books");
    assert_eq!(table_books.columns[0].name, "id", "column name");
    assert_eq!(
        table_books.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_books.columns[0].nullable, "column nullable for id");
    assert_eq!(table_books.columns[1].name, "title", "column name");
    assert_eq!(
        table_books.columns[1].sql_type, "text",
        "column sql_type for title"
    );
    assert!(
        !table_books.columns[1].nullable,
        "column nullable for title"
    );
    assert_eq!(table_books.columns[2].name, "author_id", "column name");
    assert_eq!(
        table_books.columns[2].sql_type, "integer",
        "column sql_type for author_id"
    );
    assert!(
        !table_books.columns[2].nullable,
        "column nullable for author_id"
    );
}

#[test]
fn test_constraints_not_null() {
    // From: testing_data/catalog/create_table/09_constraints_not_null.json
    // "Mix of NOT NULL and nullable columns to verify nullable flag"
    let schema_sql = &[
        "CREATE TABLE profiles (id integer NOT NULL, username text NOT NULL, bio text, email text NOT NULL, avatar_url text)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: profiles
    let table_profiles = catalog
        .get_table("profiles")
        .expect("table profiles should exist");
    assert_eq!(
        table_profiles.columns.len(),
        5,
        "column count for table profiles"
    );
    assert_eq!(table_profiles.columns[0].name, "id", "column name");
    assert_eq!(
        table_profiles.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_profiles.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_profiles.columns[1].name, "username", "column name");
    assert_eq!(
        table_profiles.columns[1].sql_type, "text",
        "column sql_type for username"
    );
    assert!(
        !table_profiles.columns[1].nullable,
        "column nullable for username"
    );
    assert_eq!(table_profiles.columns[2].name, "bio", "column name");
    assert_eq!(
        table_profiles.columns[2].sql_type, "text",
        "column sql_type for bio"
    );
    assert!(
        table_profiles.columns[2].nullable,
        "column nullable for bio"
    );
    assert_eq!(table_profiles.columns[3].name, "email", "column name");
    assert_eq!(
        table_profiles.columns[3].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_profiles.columns[3].nullable,
        "column nullable for email"
    );
    assert_eq!(table_profiles.columns[4].name, "avatar_url", "column name");
    assert_eq!(
        table_profiles.columns[4].sql_type, "text",
        "column sql_type for avatar_url"
    );
    assert!(
        table_profiles.columns[4].nullable,
        "column nullable for avatar_url"
    );
}

#[test]
fn test_constraints_primary_key() {
    // From: testing_data/catalog/create_table/10_constraints_primary_key.json
    // "Single column and composite primary key constraints (PK implies NOT NULL)"
    let schema_sql = &[
        "CREATE TABLE single_pk (id integer PRIMARY KEY, name text)",
        "CREATE TABLE composite_pk (tenant_id integer, user_id integer, data text, PRIMARY KEY (tenant_id, user_id))",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: single_pk
    let table_single_pk = catalog
        .get_table("single_pk")
        .expect("table single_pk should exist");
    assert_eq!(
        table_single_pk.columns.len(),
        2,
        "column count for table single_pk"
    );
    assert_eq!(table_single_pk.columns[0].name, "id", "column name");
    assert_eq!(
        table_single_pk.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_single_pk.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_single_pk.columns[1].name, "name", "column name");
    assert_eq!(
        table_single_pk.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        table_single_pk.columns[1].nullable,
        "column nullable for name"
    );

    // Assert table: composite_pk
    let table_composite_pk = catalog
        .get_table("composite_pk")
        .expect("table composite_pk should exist");
    assert_eq!(
        table_composite_pk.columns.len(),
        3,
        "column count for table composite_pk"
    );
    assert_eq!(
        table_composite_pk.columns[0].name, "tenant_id",
        "column name"
    );
    assert_eq!(
        table_composite_pk.columns[0].sql_type, "integer",
        "column sql_type for tenant_id"
    );
    assert!(
        !table_composite_pk.columns[0].nullable,
        "column nullable for tenant_id"
    );
    assert_eq!(table_composite_pk.columns[1].name, "user_id", "column name");
    assert_eq!(
        table_composite_pk.columns[1].sql_type, "integer",
        "column sql_type for user_id"
    );
    assert!(
        !table_composite_pk.columns[1].nullable,
        "column nullable for user_id"
    );
    assert_eq!(table_composite_pk.columns[2].name, "data", "column name");
    assert_eq!(
        table_composite_pk.columns[2].sql_type, "text",
        "column sql_type for data"
    );
    assert!(
        table_composite_pk.columns[2].nullable,
        "column nullable for data"
    );
}

#[test]
fn test_constraints_unique() {
    // From: testing_data/catalog/create_table/12_constraints_unique.json
    // "Single column UNIQUE and composite UNIQUE constraints"
    let schema_sql = &[
        "CREATE TABLE accounts (id serial PRIMARY KEY, email text NOT NULL UNIQUE, first_name text NOT NULL, last_name text NOT NULL, UNIQUE (first_name, last_name))",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: accounts
    let table_accounts = catalog
        .get_table("accounts")
        .expect("table accounts should exist");
    assert_eq!(
        table_accounts.columns.len(),
        4,
        "column count for table accounts"
    );
    assert_eq!(table_accounts.columns[0].name, "id", "column name");
    assert_eq!(
        table_accounts.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_accounts.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_accounts.columns[1].name, "email", "column name");
    assert_eq!(
        table_accounts.columns[1].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_accounts.columns[1].nullable,
        "column nullable for email"
    );
    assert_eq!(table_accounts.columns[2].name, "first_name", "column name");
    assert_eq!(
        table_accounts.columns[2].sql_type, "text",
        "column sql_type for first_name"
    );
    assert!(
        !table_accounts.columns[2].nullable,
        "column nullable for first_name"
    );
    assert_eq!(table_accounts.columns[3].name, "last_name", "column name");
    assert_eq!(
        table_accounts.columns[3].sql_type, "text",
        "column sql_type for last_name"
    );
    assert!(
        !table_accounts.columns[3].nullable,
        "column nullable for last_name"
    );
}

#[test]
fn test_generated_columns() {
    // From: testing_data/catalog/create_table/16_generated_columns.json
    // "GENERATED ALWAYS AS stored columns"
    let schema_sql = &[
        "CREATE TABLE rectangles (id serial PRIMARY KEY, width real NOT NULL, height real NOT NULL, area real GENERATED ALWAYS AS (width * height) STORED)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: rectangles
    let table_rectangles = catalog
        .get_table("rectangles")
        .expect("table rectangles should exist");
    assert_eq!(
        table_rectangles.columns.len(),
        4,
        "column count for table rectangles"
    );
    assert_eq!(table_rectangles.columns[0].name, "id", "column name");
    assert_eq!(
        table_rectangles.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_rectangles.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_rectangles.columns[1].name, "width", "column name");
    assert_eq!(
        table_rectangles.columns[1].sql_type, "real",
        "column sql_type for width"
    );
    assert!(
        !table_rectangles.columns[1].nullable,
        "column nullable for width"
    );
    assert_eq!(table_rectangles.columns[2].name, "height", "column name");
    assert_eq!(
        table_rectangles.columns[2].sql_type, "real",
        "column sql_type for height"
    );
    assert!(
        !table_rectangles.columns[2].nullable,
        "column nullable for height"
    );
    assert_eq!(table_rectangles.columns[3].name, "area", "column name");
    assert_eq!(
        table_rectangles.columns[3].sql_type, "real",
        "column sql_type for area"
    );
    assert!(
        table_rectangles.columns[3].nullable,
        "column nullable for area"
    );
}

#[test]
fn test_json_types() {
    // From: testing_data/catalog/create_table/06_json_types.json
    // "Table with json and jsonb column types"
    let schema_sql = &["CREATE TABLE documents (id serial, metadata json, payload jsonb NOT NULL)"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: documents
    let table_documents = catalog
        .get_table("documents")
        .expect("table documents should exist");
    assert_eq!(
        table_documents.columns.len(),
        3,
        "column count for table documents"
    );
    assert_eq!(table_documents.columns[0].name, "id", "column name");
    assert_eq!(
        table_documents.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_documents.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_documents.columns[1].name, "metadata", "column name");
    assert_eq!(
        table_documents.columns[1].sql_type, "json",
        "column sql_type for metadata"
    );
    assert!(
        table_documents.columns[1].nullable,
        "column nullable for metadata"
    );
    assert_eq!(table_documents.columns[2].name, "payload", "column name");
    assert_eq!(
        table_documents.columns[2].sql_type, "jsonb",
        "column sql_type for payload"
    );
    assert!(
        !table_documents.columns[2].nullable,
        "column nullable for payload"
    );
}

#[test]
fn test_multiple_tables() {
    // From: testing_data/catalog/create_table/17_multiple_tables.json
    // "Multiple tables with foreign key relationships between them"
    let schema_sql = &[
        "CREATE TABLE departments (id serial PRIMARY KEY, name text NOT NULL)",
        "CREATE TABLE employees (id serial PRIMARY KEY, name text NOT NULL, department_id integer NOT NULL REFERENCES departments (id))",
        "CREATE TABLE projects (id serial PRIMARY KEY, title text NOT NULL, lead_id integer REFERENCES employees (id), department_id integer NOT NULL REFERENCES departments (id))",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: departments
    let table_departments = catalog
        .get_table("departments")
        .expect("table departments should exist");
    assert_eq!(
        table_departments.columns.len(),
        2,
        "column count for table departments"
    );
    assert_eq!(table_departments.columns[0].name, "id", "column name");
    assert_eq!(
        table_departments.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_departments.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_departments.columns[1].name, "name", "column name");
    assert_eq!(
        table_departments.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_departments.columns[1].nullable,
        "column nullable for name"
    );

    // Assert table: projects
    let table_projects = catalog
        .get_table("projects")
        .expect("table projects should exist");
    assert_eq!(
        table_projects.columns.len(),
        4,
        "column count for table projects"
    );
    assert_eq!(table_projects.columns[0].name, "id", "column name");
    assert_eq!(
        table_projects.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_projects.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_projects.columns[1].name, "title", "column name");
    assert_eq!(
        table_projects.columns[1].sql_type, "text",
        "column sql_type for title"
    );
    assert!(
        !table_projects.columns[1].nullable,
        "column nullable for title"
    );
    assert_eq!(table_projects.columns[2].name, "lead_id", "column name");
    assert_eq!(
        table_projects.columns[2].sql_type, "integer",
        "column sql_type for lead_id"
    );
    assert!(
        table_projects.columns[2].nullable,
        "column nullable for lead_id"
    );
    assert_eq!(
        table_projects.columns[3].name, "department_id",
        "column name"
    );
    assert_eq!(
        table_projects.columns[3].sql_type, "integer",
        "column sql_type for department_id"
    );
    assert!(
        !table_projects.columns[3].nullable,
        "column nullable for department_id"
    );

    // Assert table: employees
    let table_employees = catalog
        .get_table("employees")
        .expect("table employees should exist");
    assert_eq!(
        table_employees.columns.len(),
        3,
        "column count for table employees"
    );
    assert_eq!(table_employees.columns[0].name, "id", "column name");
    assert_eq!(
        table_employees.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_employees.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_employees.columns[1].name, "name", "column name");
    assert_eq!(
        table_employees.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_employees.columns[1].nullable,
        "column nullable for name"
    );
    assert_eq!(
        table_employees.columns[2].name, "department_id",
        "column name"
    );
    assert_eq!(
        table_employees.columns[2].sql_type, "integer",
        "column sql_type for department_id"
    );
    assert!(
        !table_employees.columns[2].nullable,
        "column nullable for department_id"
    );
}

#[test]
fn test_reserved_words() {
    // From: testing_data/catalog/create_table/18_reserved_words.json
    // "Table and column names that are SQL reserved words using quoted identifiers"
    let schema_sql = &[
        "CREATE TABLE \"order\" (\"id\" serial PRIMARY KEY, \"select\" text NOT NULL, \"from\" text, \"group\" integer NOT NULL)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: order
    let table_order = catalog
        .get_table("order")
        .expect("table order should exist");
    assert_eq!(table_order.columns.len(), 4, "column count for table order");
    assert_eq!(table_order.columns[0].name, "id", "column name");
    assert_eq!(
        table_order.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_order.columns[0].nullable, "column nullable for id");
    assert_eq!(table_order.columns[1].name, "select", "column name");
    assert_eq!(
        table_order.columns[1].sql_type, "text",
        "column sql_type for select"
    );
    assert!(
        !table_order.columns[1].nullable,
        "column nullable for select"
    );
    assert_eq!(table_order.columns[2].name, "from", "column name");
    assert_eq!(
        table_order.columns[2].sql_type, "text",
        "column sql_type for from"
    );
    assert!(table_order.columns[2].nullable, "column nullable for from");
    assert_eq!(table_order.columns[3].name, "group", "column name");
    assert_eq!(
        table_order.columns[3].sql_type, "integer",
        "column sql_type for group"
    );
    assert!(
        !table_order.columns[3].nullable,
        "column nullable for group"
    );
}

#[test]
fn test_serial_types() {
    // From: testing_data/catalog/create_table/15_serial_types.json
    // "serial, bigserial, smallserial columns with implicit NOT NULL and underlying integer types"
    let schema_sql = &["CREATE TABLE serials (a smallserial, b serial, c bigserial)"];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: serials
    let table_serials = catalog
        .get_table("serials")
        .expect("table serials should exist");
    assert_eq!(
        table_serials.columns.len(),
        3,
        "column count for table serials"
    );
    assert_eq!(table_serials.columns[0].name, "a", "column name");
    assert_eq!(
        table_serials.columns[0].sql_type, "smallint",
        "column sql_type for a"
    );
    assert!(!table_serials.columns[0].nullable, "column nullable for a");
    assert_eq!(table_serials.columns[1].name, "b", "column name");
    assert_eq!(
        table_serials.columns[1].sql_type, "integer",
        "column sql_type for b"
    );
    assert!(!table_serials.columns[1].nullable, "column nullable for b");
    assert_eq!(table_serials.columns[2].name, "c", "column name");
    assert_eq!(
        table_serials.columns[2].sql_type, "bigint",
        "column sql_type for c"
    );
    assert!(!table_serials.columns[2].nullable, "column nullable for c");
}

#[test]
fn test_temporal_types() {
    // From: testing_data/catalog/create_table/04_temporal_types.json
    // "Table with all PostgreSQL temporal types: date, time, timetz, timestamp, timestamptz, interval"
    let schema_sql = &[
        "CREATE TABLE temporal_types (a date, b time, c timetz, d timestamp, e timestamptz, f interval)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: temporal_types
    let table_temporal_types = catalog
        .get_table("temporal_types")
        .expect("table temporal_types should exist");
    assert_eq!(
        table_temporal_types.columns.len(),
        6,
        "column count for table temporal_types"
    );
    assert_eq!(table_temporal_types.columns[0].name, "a", "column name");
    assert_eq!(
        table_temporal_types.columns[0].sql_type, "date",
        "column sql_type for a"
    );
    assert!(
        table_temporal_types.columns[0].nullable,
        "column nullable for a"
    );
    assert_eq!(table_temporal_types.columns[1].name, "b", "column name");
    assert_eq!(
        table_temporal_types.columns[1].sql_type, "time",
        "column sql_type for b"
    );
    assert!(
        table_temporal_types.columns[1].nullable,
        "column nullable for b"
    );
    assert_eq!(table_temporal_types.columns[2].name, "c", "column name");
    assert_eq!(
        table_temporal_types.columns[2].sql_type, "timetz",
        "column sql_type for c"
    );
    assert!(
        table_temporal_types.columns[2].nullable,
        "column nullable for c"
    );
    assert_eq!(table_temporal_types.columns[3].name, "d", "column name");
    assert_eq!(
        table_temporal_types.columns[3].sql_type, "timestamp",
        "column sql_type for d"
    );
    assert!(
        table_temporal_types.columns[3].nullable,
        "column nullable for d"
    );
    assert_eq!(table_temporal_types.columns[4].name, "e", "column name");
    assert_eq!(
        table_temporal_types.columns[4].sql_type, "timestamptz",
        "column sql_type for e"
    );
    assert!(
        table_temporal_types.columns[4].nullable,
        "column nullable for e"
    );
    assert_eq!(table_temporal_types.columns[5].name, "f", "column name");
    assert_eq!(
        table_temporal_types.columns[5].sql_type, "interval",
        "column sql_type for f"
    );
    assert!(
        table_temporal_types.columns[5].nullable,
        "column nullable for f"
    );
}

#[test]
fn test_basic_view() {
    // From: testing_data/catalog/create_view/01_basic_view.json
    // "CREATE VIEW with a SELECT from an existing table"
    let schema_sql = &[
        "CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL, is_active boolean NOT NULL DEFAULT true)",
        "CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE is_active = true",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: users
    let table_users = catalog
        .get_table("users")
        .expect("table users should exist");
    assert_eq!(table_users.columns.len(), 4, "column count for table users");
    assert_eq!(table_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_users.columns[0].nullable, "column nullable for id");
    assert_eq!(table_users.columns[1].name, "name", "column name");
    assert_eq!(
        table_users.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(!table_users.columns[1].nullable, "column nullable for name");
    assert_eq!(table_users.columns[2].name, "email", "column name");
    assert_eq!(
        table_users.columns[2].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_users.columns[2].nullable,
        "column nullable for email"
    );
    assert_eq!(table_users.columns[3].name, "is_active", "column name");
    assert_eq!(
        table_users.columns[3].sql_type, "boolean",
        "column sql_type for is_active"
    );
    assert!(
        !table_users.columns[3].nullable,
        "column nullable for is_active"
    );
}

#[test]
fn test_materialized_view() {
    // From: testing_data/catalog/create_view/02_materialized_view.json
    // "CREATE MATERIALIZED VIEW with aggregation query"
    let schema_sql = &[
        "CREATE TABLE orders (id serial PRIMARY KEY, customer_id integer NOT NULL, total numeric(10,2) NOT NULL, created_at date NOT NULL)",
        "CREATE MATERIALIZED VIEW order_summary AS SELECT customer_id, count(*) AS order_count, sum(total) AS total_spent FROM orders GROUP BY customer_id",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: orders
    let table_orders = catalog
        .get_table("orders")
        .expect("table orders should exist");
    assert_eq!(
        table_orders.columns.len(),
        4,
        "column count for table orders"
    );
    assert_eq!(table_orders.columns[0].name, "id", "column name");
    assert_eq!(
        table_orders.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(!table_orders.columns[0].nullable, "column nullable for id");
    assert_eq!(table_orders.columns[1].name, "customer_id", "column name");
    assert_eq!(
        table_orders.columns[1].sql_type, "integer",
        "column sql_type for customer_id"
    );
    assert!(
        !table_orders.columns[1].nullable,
        "column nullable for customer_id"
    );
    assert_eq!(table_orders.columns[2].name, "total", "column name");
    assert_eq!(
        table_orders.columns[2].sql_type, "numeric(10,2)",
        "column sql_type for total"
    );
    assert!(
        !table_orders.columns[2].nullable,
        "column nullable for total"
    );
    assert_eq!(table_orders.columns[3].name, "created_at", "column name");
    assert_eq!(
        table_orders.columns[3].sql_type, "date",
        "column sql_type for created_at"
    );
    assert!(
        !table_orders.columns[3].nullable,
        "column nullable for created_at"
    );
}

#[test]
fn test_cross_schema_reference() {
    // From: testing_data/catalog/multi_schema/02_cross_schema_reference.json
    // "Tables in different schemas with foreign key references across schemas"
    let schema_sql = &[
        "CREATE SCHEMA core",
        "CREATE SCHEMA billing",
        "CREATE TABLE core.users (id serial PRIMARY KEY, name text NOT NULL)",
        "CREATE TABLE billing.invoices (id serial PRIMARY KEY, user_id integer NOT NULL REFERENCES core.users (id), amount numeric(10,2) NOT NULL)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: core.users
    let table_core_users = catalog
        .get_table("core.users")
        .expect("table core.users should exist");
    assert_eq!(
        table_core_users.columns.len(),
        2,
        "column count for table core.users"
    );
    assert_eq!(table_core_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_core_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_core_users.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_core_users.columns[1].name, "name", "column name");
    assert_eq!(
        table_core_users.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_core_users.columns[1].nullable,
        "column nullable for name"
    );

    // Assert table: billing.invoices
    let table_billing_invoices = catalog
        .get_table("billing.invoices")
        .expect("table billing.invoices should exist");
    assert_eq!(
        table_billing_invoices.columns.len(),
        3,
        "column count for table billing.invoices"
    );
    assert_eq!(table_billing_invoices.columns[0].name, "id", "column name");
    assert_eq!(
        table_billing_invoices.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_billing_invoices.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(
        table_billing_invoices.columns[1].name, "user_id",
        "column name"
    );
    assert_eq!(
        table_billing_invoices.columns[1].sql_type, "integer",
        "column sql_type for user_id"
    );
    assert!(
        !table_billing_invoices.columns[1].nullable,
        "column nullable for user_id"
    );
    assert_eq!(
        table_billing_invoices.columns[2].name, "amount",
        "column name"
    );
    assert_eq!(
        table_billing_invoices.columns[2].sql_type, "numeric(10,2)",
        "column sql_type for amount"
    );
    assert!(
        !table_billing_invoices.columns[2].nullable,
        "column nullable for amount"
    );
}

#[test]
fn test_schema_qualified() {
    // From: testing_data/catalog/multi_schema/01_schema_qualified.json
    // "CREATE SCHEMA and CREATE TABLE with schema-qualified names"
    let schema_sql = &[
        "CREATE SCHEMA app",
        "CREATE TABLE app.users (id serial PRIMARY KEY, name text NOT NULL, email text NOT NULL)",
    ];

    let catalog = scythe_core::catalog::Catalog::from_ddl(schema_sql).unwrap();

    // Assert table: app.users
    let table_app_users = catalog
        .get_table("app.users")
        .expect("table app.users should exist");
    assert_eq!(
        table_app_users.columns.len(),
        3,
        "column count for table app.users"
    );
    assert_eq!(table_app_users.columns[0].name, "id", "column name");
    assert_eq!(
        table_app_users.columns[0].sql_type, "integer",
        "column sql_type for id"
    );
    assert!(
        !table_app_users.columns[0].nullable,
        "column nullable for id"
    );
    assert_eq!(table_app_users.columns[1].name, "name", "column name");
    assert_eq!(
        table_app_users.columns[1].sql_type, "text",
        "column sql_type for name"
    );
    assert!(
        !table_app_users.columns[1].nullable,
        "column nullable for name"
    );
    assert_eq!(table_app_users.columns[2].name, "email", "column name");
    assert_eq!(
        table_app_users.columns[2].sql_type, "text",
        "column sql_type for email"
    );
    assert!(
        !table_app_users.columns[2].nullable,
        "column nullable for email"
    );
}
