vespertide-query 0.2.0

use sea_query::{Alias, Index};

use vespertide_core::{ColumnType, ComplexColumnType, TableConstraint, TableDef};

use self::direct::build_direct_delete_column;
use self::sqlite_rebuild::build_delete_column_sqlite_temp_table;
use super::types::{BuiltQuery, DatabaseBackend};

mod direct;
mod sqlite_rebuild;

/// Build SQL to delete a column, optionally with DROP TYPE for enum columns (`PostgreSQL`).
///
/// For `SQLite`: Handles constraint removal before dropping the column:
/// - Unique/Index constraints: Dropped via DROP INDEX
/// - ForeignKey/PrimaryKey constraints: Uses temp table approach (recreate table without column)
///
/// `SQLite` doesn't cascade constraint drops when a column is dropped.
pub fn build_delete_column(
    backend: DatabaseBackend,
    table: &str,
    column: &str,
    column_type: Option<&ColumnType>,
    current_schema: &[TableDef],
    pending_constraints: &[TableConstraint],
) -> Vec<BuiltQuery> {
    let mut stmts = Vec::new();

    // SQLite: Check if we need special handling for constraints.
    if backend == DatabaseBackend::Sqlite
        && let Some(table_def) = current_schema.iter().find(|t| t.name == table)
        && let Some(sqlite_queries) = sqlite_constraint_handling(
            table,
            column,
            table_def,
            column_type,
            pending_constraints,
            &mut stmts,
        )
    {
        return sqlite_queries;
    }

    stmts.extend(build_direct_delete_column(table, column, column_type));
    stmts
}

fn sqlite_constraint_handling(
    table: &str,
    column: &str,
    table_def: &TableDef,
    column_type: Option<&ColumnType>,
    pending_constraints: &[TableConstraint],
    stmts: &mut Vec<BuiltQuery>,
) -> Option<Vec<BuiltQuery>> {
    // If the column has an enum type, SQLite embeds a CHECK constraint in CREATE TABLE.
    // ALTER TABLE DROP COLUMN fails if the column is referenced by any CHECK.
    // Must use temp table approach.
    if let Some(col_def) = table_def.columns.iter().find(|c| c.name == column)
        && let ColumnType::Complex(ComplexColumnType::Enum { .. }) = &col_def.r#type
    {
        return Some(build_delete_column_sqlite_temp_table(
            table,
            column,
            table_def,
            column_type,
            pending_constraints,
        ));
    }

    // Handle constraints referencing the deleted column.
    for constraint in &table_def.constraints {
        match constraint {
            // Check constraints may reference the column in their expression.
            // SQLite can't DROP COLUMN if a CHECK references it — use temp table.
            TableConstraint::Check { expr, .. } => {
                // Check if the expression references the column (e.g. "status" IN (...)).
                if expr.contains(&format!("\"{column}\"")) || expr.contains(column) {
                    return Some(build_delete_column_sqlite_temp_table(
                        table,
                        column,
                        table_def,
                        column_type,
                        pending_constraints,
                    ));
                }
            }
            // For column-based constraints, check if they reference the deleted column.
            _ if !constraint.columns().iter().any(|c| c == column) => {}
            // FK/PK require temp table approach - return immediately.
            TableConstraint::ForeignKey { .. } | TableConstraint::PrimaryKey { .. } => {
                return Some(build_delete_column_sqlite_temp_table(
                    table,
                    column,
                    table_def,
                    column_type,
                    pending_constraints,
                ));
            }
            // Unique/Index: drop the index first, then drop column below.
            TableConstraint::Unique { name, columns, .. } => {
                let index_name = vespertide_naming::build_unique_constraint_name(
                    table,
                    columns,
                    name.as_deref(),
                );
                let drop_idx = Index::drop()
                    .name(&index_name)
                    .table(Alias::new(table))
                    .to_owned();
                stmts.push(BuiltQuery::DropIndex(Box::new(drop_idx)));
            }
            TableConstraint::Index { name, columns } => {
                let index_name =
                    vespertide_naming::build_index_name(table, columns, name.as_deref());
                let drop_idx = Index::drop()
                    .name(&index_name)
                    .table(Alias::new(table))
                    .to_owned();
                stmts.push(BuiltQuery::DropIndex(Box::new(drop_idx)));
            }
            _ => {
                unreachable!("TableConstraint is #[non_exhaustive]; all variants are matched above")
            }
        }
    }

    None
}

#[cfg(test)]
mod tests {
    use super::*;
    use crate::sql::types::DatabaseBackend;
    use crate::test_support::col;
    use insta::{assert_snapshot, with_settings};
    use rstest::rstest;
    use vespertide_core::{ComplexColumnType, SimpleColumnType};

    #[rstest]
    #[case::delete_column_postgres(
            "delete_column_postgres",
            DatabaseBackend::Postgres,
            &["ALTER TABLE \"users\" DROP COLUMN \"email\""]
        )]
    #[case::delete_column_mysql(
            "delete_column_mysql",
            DatabaseBackend::MySql,
            &["ALTER TABLE `users` DROP COLUMN `email`"]
        )]
    #[case::delete_column_sqlite(
            "delete_column_sqlite",
            DatabaseBackend::Sqlite,
            &["ALTER TABLE \"users\" DROP COLUMN \"email\""]
        )]
    fn test_delete_column(
        #[case] title: &str,
        #[case] backend: DatabaseBackend,
        #[case] expected: &[&str],
    ) {
        let result = build_delete_column(backend, "users", "email", None, &[], &[]);
        let sql = result[0].build(backend);
        for exp in expected {
            assert!(
                sql.contains(exp),
                "Expected SQL to contain '{exp}', got: {sql}"
            );
        }

        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("delete_column_{}", title) }, {
            assert_snapshot!(sql);
        });
    }

    #[test]
    fn test_delete_enum_column_postgres() {
        use vespertide_core::EnumValues;

        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
            name: "status".into(),
            values: EnumValues::String(vec!["active".into(), "inactive".into()]),
        });
        let result = build_delete_column(
            DatabaseBackend::Postgres,
            "users",
            "status",
            Some(&enum_type),
            &[],
            &[],
        );

        // Should have 2 statements: ALTER TABLE and DROP TYPE
        assert_eq!(result.len(), 2);

        let alter_sql = result[0].build(DatabaseBackend::Postgres);
        assert!(alter_sql.contains("DROP COLUMN"));

        let drop_type_sql = result[1].build(DatabaseBackend::Postgres);
        assert!(drop_type_sql.contains("DROP TYPE \"users_status\""));

        // MySQL and SQLite should have empty DROP TYPE
        let drop_type_mysql = result[1].build(DatabaseBackend::MySql);
        assert!(drop_type_mysql.is_empty());
    }

    #[test]
    fn test_delete_non_enum_column_no_drop_type() {
        let text_type = ColumnType::Simple(SimpleColumnType::Text);
        let result = build_delete_column(
            DatabaseBackend::Postgres,
            "users",
            "name",
            Some(&text_type),
            &[],
            &[],
        );

        // Should only have 1 statement: ALTER TABLE
        assert_eq!(result.len(), 1);
    }

    #[test]
    fn test_delete_column_sqlite_drops_unique_constraint_first() {
        // SQLite should drop unique constraint index before dropping the column
        let schema = vec![TableDef {
            name: "gift".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("gift_code", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Unique {
                name: None,
                columns: vec!["gift_code".into()],
                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
                    keep: vespertide_core::KeepPolicy::First,
                },
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "gift",
            "gift_code",
            None,
            &schema,
            &[],
        );

        // Should have 2 statements: DROP INDEX then ALTER TABLE DROP COLUMN
        assert_eq!(result.len(), 2);

        let drop_index_sql = result[0].build(DatabaseBackend::Sqlite);
        assert!(
            drop_index_sql.contains("DROP INDEX"),
            "Expected DROP INDEX, got: {drop_index_sql}"
        );
        assert!(
            drop_index_sql.contains("uq_gift__gift_code"),
            "Expected index name uq_gift__gift_code, got: {drop_index_sql}"
        );

        let drop_column_sql = result[1].build(DatabaseBackend::Sqlite);
        assert!(
            drop_column_sql.contains("DROP COLUMN"),
            "Expected DROP COLUMN, got: {drop_column_sql}"
        );
    }

    #[test]
    fn test_delete_column_sqlite_drops_index_constraint_first() {
        // SQLite should drop index before dropping the column
        let schema = vec![TableDef {
            name: "users".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("email", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Index {
                name: None,
                columns: vec!["email".into()],
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "users",
            "email",
            None,
            &schema,
            &[],
        );

        // Should have 2 statements: DROP INDEX then ALTER TABLE DROP COLUMN
        assert_eq!(result.len(), 2);

        let drop_index_sql = result[0].build(DatabaseBackend::Sqlite);
        assert!(drop_index_sql.contains("DROP INDEX"));
        assert!(drop_index_sql.contains("ix_users__email"));

        let drop_column_sql = result[1].build(DatabaseBackend::Sqlite);
        assert!(drop_column_sql.contains("DROP COLUMN"));
    }

    #[test]
    fn test_delete_column_postgres_does_not_drop_constraints() {
        // PostgreSQL cascades constraint drops, so we shouldn't emit extra DROP INDEX
        let schema = vec![TableDef {
            name: "gift".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("gift_code", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Unique {
                name: None,
                columns: vec!["gift_code".into()],
                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
                    keep: vespertide_core::KeepPolicy::First,
                },
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Postgres,
            "gift",
            "gift_code",
            None,
            &schema,
            &[],
        );

        // Should have only 1 statement: ALTER TABLE DROP COLUMN
        assert_eq!(result.len(), 1);

        let drop_column_sql = result[0].build(DatabaseBackend::Postgres);
        assert!(drop_column_sql.contains("DROP COLUMN"));
    }

    #[test]
    fn test_delete_column_sqlite_with_named_unique_constraint() {
        // Test with a named unique constraint
        let schema = vec![TableDef {
            name: "gift".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("gift_code", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Unique {
                name: Some("gift_code".into()),
                columns: vec!["gift_code".into()],
                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
                    keep: vespertide_core::KeepPolicy::First,
                },
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "gift",
            "gift_code",
            None,
            &schema,
            &[],
        );

        assert_eq!(result.len(), 2);

        let drop_index_sql = result[0].build(DatabaseBackend::Sqlite);
        // Named constraint: uq_gift__gift_code (name is "gift_code")
        assert!(
            drop_index_sql.contains("uq_gift__gift_code"),
            "Expected uq_gift__gift_code, got: {drop_index_sql}"
        );
    }

    #[test]
    fn test_delete_column_sqlite_with_fk_uses_temp_table() {
        // SQLite should use temp table approach when deleting a column with FK constraint
        let schema = vec![TableDef {
            name: "gift".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("sender_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col("message", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::ForeignKey {
                name: None,
                columns: vec!["sender_id".into()],
                ref_table: "user".into(),
                ref_columns: vec!["id".into()],
                on_delete: None,
                on_update: None,
                orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "gift",
            "sender_id",
            None,
            &schema,
            &[],
        );

        // Should use temp table approach:
        // 1. CREATE TABLE gift_temp (without sender_id column)
        // 2. INSERT INTO gift_temp SELECT ... FROM gift
        // 3. DROP TABLE gift
        // 4. ALTER TABLE gift_temp RENAME TO gift
        assert!(
            result.len() >= 4,
            "Expected at least 4 statements for temp table approach, got: {}",
            result.len()
        );

        let all_sql: Vec<String> = result
            .iter()
            .map(|q| q.build(DatabaseBackend::Sqlite))
            .collect();
        let combined_sql = all_sql.join("\n");

        // Verify temp table creation
        assert!(
            combined_sql.contains("CREATE TABLE") && combined_sql.contains("gift_temp"),
            "Expected CREATE TABLE gift_temp, got: {combined_sql}"
        );

        // Verify the new table doesn't have sender_id column
        assert!(
            !combined_sql.contains("\"sender_id\"") || combined_sql.contains("DROP TABLE"),
            "New table should not contain sender_id column"
        );

        // Verify data copy (INSERT ... SELECT)
        assert!(
            combined_sql.contains("INSERT INTO"),
            "Expected INSERT INTO for data copy, got: {combined_sql}"
        );

        // Verify original table drop
        assert!(
            combined_sql.contains("DROP TABLE") && combined_sql.contains("\"gift\""),
            "Expected DROP TABLE gift, got: {combined_sql}"
        );

        // Verify rename
        assert!(
            combined_sql.contains("RENAME"),
            "Expected RENAME for temp table, got: {combined_sql}"
        );
    }

    #[test]
    fn test_delete_column_sqlite_with_fk_preserves_other_columns() {
        // When using temp table approach, other columns should be preserved
        let schema = vec![TableDef {
            name: "gift".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("sender_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col("receiver_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col("message", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![
                TableConstraint::ForeignKey {
                    name: None,
                    columns: vec!["sender_id".into()],
                    ref_table: "user".into(),
                    ref_columns: vec!["id".into()],
                    on_delete: None,
                    on_update: None,
                    orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
                },
                TableConstraint::Index {
                    name: None,
                    columns: vec!["receiver_id".into()],
                },
            ],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "gift",
            "sender_id",
            None,
            &schema,
            &[],
        );

        let all_sql: Vec<String> = result
            .iter()
            .map(|q| q.build(DatabaseBackend::Sqlite))
            .collect();
        let combined_sql = all_sql.join("\n");

        // Should preserve other columns
        assert!(combined_sql.contains("\"id\""), "Should preserve id column");
        assert!(
            combined_sql.contains("\"receiver_id\""),
            "Should preserve receiver_id column"
        );
        assert!(
            combined_sql.contains("\"message\""),
            "Should preserve message column"
        );

        // Should recreate index on receiver_id (not on sender_id)
        assert!(
            combined_sql.contains("CREATE INDEX") && combined_sql.contains("ix_gift__receiver_id"),
            "Should recreate index on receiver_id, got: {combined_sql}"
        );
    }

    #[test]
    fn test_delete_column_postgres_with_fk_does_not_use_temp_table() {
        // PostgreSQL should NOT use temp table - just drop column directly
        let schema = vec![TableDef {
            name: "gift".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("sender_id", ColumnType::Simple(SimpleColumnType::BigInt)),
            ],
            constraints: vec![TableConstraint::ForeignKey {
                name: None,
                columns: vec!["sender_id".into()],
                ref_table: "user".into(),
                ref_columns: vec!["id".into()],
                on_delete: None,
                on_update: None,
                orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Postgres,
            "gift",
            "sender_id",
            None,
            &schema,
            &[],
        );

        // Should have only 1 statement: ALTER TABLE DROP COLUMN
        assert_eq!(
            result.len(),
            1,
            "PostgreSQL should only have 1 statement, got: {}",
            result.len()
        );

        let sql = result[0].build(DatabaseBackend::Postgres);
        assert!(
            sql.contains("DROP COLUMN"),
            "Expected DROP COLUMN, got: {sql}"
        );
        assert!(
            !sql.contains("gift_temp"),
            "PostgreSQL should not use temp table"
        );
    }

    #[test]
    fn test_delete_column_sqlite_with_pk_uses_temp_table() {
        // SQLite should use temp table approach when deleting a column that's part of PK
        let schema = vec![TableDef {
            name: "order_items".into(),
            description: None,
            columns: vec![
                col("order_id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("product_id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("quantity", ColumnType::Simple(SimpleColumnType::Integer)),
            ],
            constraints: vec![TableConstraint::PrimaryKey {
                auto_increment: false,
                columns: vec!["order_id".into(), "product_id".into()],
                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
            }],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "order_items",
            "product_id",
            None,
            &schema,
            &[],
        );

        // Should use temp table approach
        assert!(
            result.len() >= 4,
            "Expected at least 4 statements for temp table approach, got: {}",
            result.len()
        );

        let all_sql: Vec<String> = result
            .iter()
            .map(|q| q.build(DatabaseBackend::Sqlite))
            .collect();
        let combined_sql = all_sql.join("\n");

        assert!(
            combined_sql.contains("order_items_temp"),
            "Should use temp table approach for PK column deletion"
        );
    }

    #[test]
    fn test_delete_column_sqlite_unique_on_different_column_not_dropped() {
        // When deleting a column in SQLite, UNIQUE constraints on OTHER columns should NOT be dropped
        // This tests line 46's condition: only drop constraints that reference the deleted column
        let schema = vec![TableDef {
            name: "users".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("email", ColumnType::Simple(SimpleColumnType::Text)),
                col("nickname", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![
                // UNIQUE on email (the column we're NOT deleting)
                TableConstraint::Unique {
                    name: None,
                    columns: vec!["email".into()],
                    strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
                        keep: vespertide_core::KeepPolicy::First,
                    },
                },
            ],
        }];

        // Delete nickname, which does NOT have the unique constraint
        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "users",
            "nickname",
            None,
            &schema,
            &[],
        );

        // Should only have 1 statement: ALTER TABLE DROP COLUMN (no DROP INDEX needed)
        assert_eq!(
            result.len(),
            1,
            "Should not drop UNIQUE on email when deleting nickname, got: {} statements",
            result.len()
        );

        let sql = result[0].build(DatabaseBackend::Sqlite);
        assert!(
            sql.contains("DROP COLUMN"),
            "Expected DROP COLUMN, got: {sql}"
        );
        assert!(
            !sql.contains("DROP INDEX"),
            "Should NOT drop the email UNIQUE constraint when deleting nickname"
        );
    }

    #[test]
    fn test_delete_column_sqlite_temp_table_filters_constraints_correctly() {
        // When using temp table approach, constraints referencing the deleted column should be excluded,
        // but constraints on OTHER columns should be preserved
        // This tests lines 122-124: filter constraints by column reference
        let schema = vec![TableDef {
            name: "orders".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col("status", ColumnType::Simple(SimpleColumnType::Text)),
                col(
                    "created_at",
                    ColumnType::Simple(SimpleColumnType::Timestamp),
                ),
            ],
            constraints: vec![
                // FK on user_id (column we're deleting) - should be excluded
                TableConstraint::ForeignKey {
                    name: None,
                    columns: vec!["user_id".into()],
                    ref_table: "users".into(),
                    ref_columns: vec!["id".into()],
                    on_delete: None,
                    on_update: None,
                    orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
                },
                // Index on created_at (different column) - should be preserved and recreated
                TableConstraint::Index {
                    name: None,
                    columns: vec!["created_at".into()],
                },
                // Another FK on a different column - should be preserved
                TableConstraint::ForeignKey {
                    name: None,
                    columns: vec!["status".into()],
                    ref_table: "statuses".into(),
                    ref_columns: vec!["code".into()],
                    on_delete: None,
                    on_update: None,
                    orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
                },
            ],
        }];

        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "orders",
            "user_id",
            None,
            &schema,
            &[],
        );

        let all_sql: Vec<String> = result
            .iter()
            .map(|q| q.build(DatabaseBackend::Sqlite))
            .collect();
        let combined_sql = all_sql.join("\n");

        // Should use temp table approach (FK triggers it)
        assert!(
            combined_sql.contains("orders_temp"),
            "Should use temp table approach for FK column deletion"
        );

        // Index on created_at should be recreated after rename
        assert!(
            combined_sql.contains("ix_orders__created_at"),
            "Index on created_at should be recreated, got: {combined_sql}"
        );

        // The FK on user_id should NOT appear (deleted column)
        // But the FK on status should be preserved
        assert!(
            combined_sql.contains("REFERENCES \"statuses\""),
            "FK on status should be preserved, got: {combined_sql}"
        );

        // Count FK references - should only be 1 (status FK, not user_id FK)
        let fk_patterns = combined_sql.matches("REFERENCES").count();
        assert_eq!(
            fk_patterns, 1,
            "Only the FK on status should exist (not the one on user_id), got: {combined_sql}"
        );
    }

    // ==================== Snapshot Tests ====================

    fn build_sql_snapshot(result: &[BuiltQuery], backend: DatabaseBackend) -> String {
        result
            .iter()
            .map(|q| q.build(backend))
            .collect::<Vec<_>>()
            .join(";\n")
    }

    #[rstest]
    #[case::postgres("postgres", DatabaseBackend::Postgres)]
    #[case::mysql("mysql", DatabaseBackend::MySql)]
    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
    fn test_delete_column_with_unique_constraint(
        #[case] title: &str,
        #[case] backend: DatabaseBackend,
    ) {
        let schema = vec![TableDef {
            name: "users".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("email", ColumnType::Simple(SimpleColumnType::Text)),
                col("name", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Unique {
                name: None,
                columns: vec!["email".into()],
                strategy: vespertide_core::UniqueConstraintStrategy::DeleteDuplicates {
                    keep: vespertide_core::KeepPolicy::First,
                },
            }],
        }];

        let result = build_delete_column(backend, "users", "email", None, &schema, &[]);
        let sql = build_sql_snapshot(&result, backend);

        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("delete_column_with_unique_{}", title) }, {
            assert_snapshot!(sql);
        });
    }

    #[rstest]
    #[case::postgres("postgres", DatabaseBackend::Postgres)]
    #[case::mysql("mysql", DatabaseBackend::MySql)]
    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
    fn test_delete_column_with_index_constraint(
        #[case] title: &str,
        #[case] backend: DatabaseBackend,
    ) {
        let schema = vec![TableDef {
            name: "posts".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col(
                    "created_at",
                    ColumnType::Simple(SimpleColumnType::Timestamp),
                ),
                col("title", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Index {
                name: None,
                columns: vec!["created_at".into()],
            }],
        }];

        let result = build_delete_column(backend, "posts", "created_at", None, &schema, &[]);
        let sql = build_sql_snapshot(&result, backend);

        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("delete_column_with_index_{}", title) }, {
            assert_snapshot!(sql);
        });
    }

    #[rstest]
    #[case::postgres("postgres", DatabaseBackend::Postgres)]
    #[case::mysql("mysql", DatabaseBackend::MySql)]
    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
    fn test_delete_column_with_fk_constraint(
        #[case] title: &str,
        #[case] backend: DatabaseBackend,
    ) {
        let schema = vec![TableDef {
            name: "orders".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col("total", ColumnType::Simple(SimpleColumnType::Integer)),
            ],
            constraints: vec![TableConstraint::ForeignKey {
                name: None,
                columns: vec!["user_id".into()],
                ref_table: "users".into(),
                ref_columns: vec!["id".into()],
                on_delete: None,
                on_update: None,
                orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
            }],
        }];

        let result = build_delete_column(backend, "orders", "user_id", None, &schema, &[]);
        let sql = build_sql_snapshot(&result, backend);

        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("delete_column_with_fk_{}", title) }, {
            assert_snapshot!(sql);
        });
    }

    #[rstest]
    #[case::postgres("postgres", DatabaseBackend::Postgres)]
    #[case::mysql("mysql", DatabaseBackend::MySql)]
    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
    fn test_delete_column_with_pk_constraint(
        #[case] title: &str,
        #[case] backend: DatabaseBackend,
    ) {
        let schema = vec![TableDef {
            name: "order_items".into(),
            description: None,
            columns: vec![
                col("order_id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("product_id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("quantity", ColumnType::Simple(SimpleColumnType::Integer)),
            ],
            constraints: vec![TableConstraint::PrimaryKey {
                auto_increment: false,
                columns: vec!["order_id".into(), "product_id".into()],
                strategy: vespertide_core::PrimaryKeyAdditionStrategy::default(),
            }],
        }];

        let result = build_delete_column(backend, "order_items", "product_id", None, &schema, &[]);
        let sql = build_sql_snapshot(&result, backend);

        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("delete_column_with_pk_{}", title) }, {
            assert_snapshot!(sql);
        });
    }

    #[rstest]
    #[case::postgres("postgres", DatabaseBackend::Postgres)]
    #[case::mysql("mysql", DatabaseBackend::MySql)]
    #[case::sqlite("sqlite", DatabaseBackend::Sqlite)]
    fn test_delete_column_with_fk_and_index_constraints(
        #[case] title: &str,
        #[case] backend: DatabaseBackend,
    ) {
        // Complex case: FK on the deleted column + Index on another column
        let schema = vec![TableDef {
            name: "orders".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col(
                    "created_at",
                    ColumnType::Simple(SimpleColumnType::Timestamp),
                ),
                col("total", ColumnType::Simple(SimpleColumnType::Integer)),
            ],
            constraints: vec![
                TableConstraint::ForeignKey {
                    name: None,
                    columns: vec!["user_id".into()],
                    ref_table: "users".into(),
                    ref_columns: vec!["id".into()],
                    on_delete: None,
                    on_update: None,
                    orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
                },
                TableConstraint::Index {
                    name: None,
                    columns: vec!["created_at".into()],
                },
            ],
        }];

        let result = build_delete_column(backend, "orders", "user_id", None, &schema, &[]);
        let sql = build_sql_snapshot(&result, backend);

        with_settings!({ snapshot_path => "../snapshots", snapshot_suffix => format!("delete_column_with_fk_and_index_{}", title) }, {
            assert_snapshot!(sql);
        });
    }

    #[test]
    fn test_delete_column_sqlite_temp_table_with_enum_column() {
        // SQLite temp table approach with enum column type
        // This tests lines 122-124: enum type drop in temp table function
        use vespertide_core::EnumValues;

        let enum_type = ColumnType::Complex(ComplexColumnType::Enum {
            name: "order_status".into(),
            values: EnumValues::String(vec![
                "pending".into(),
                "shipped".into(),
                "delivered".into(),
            ]),
        });

        let schema = vec![TableDef {
            name: "orders".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("user_id", ColumnType::Simple(SimpleColumnType::BigInt)),
                col("status", enum_type.clone()),
            ],
            constraints: vec![TableConstraint::ForeignKey {
                name: None,
                columns: vec!["user_id".into()],
                ref_table: "users".into(),
                ref_columns: vec!["id".into()],
                on_delete: None,
                on_update: None,
                orphan_strategy: vespertide_core::ForeignKeyOrphanStrategy::default(),
            }],
        }];

        // Delete the FK column (user_id) with an enum type - triggers temp table AND enum drop
        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "orders",
            "user_id",
            Some(&enum_type),
            &schema,
            &[],
        );

        // Should use temp table approach (FK triggers it) + DROP TYPE at end
        assert!(
            result.len() >= 4,
            "Expected at least 4 statements for temp table approach, got: {}",
            result.len()
        );

        let all_sql: Vec<String> = result
            .iter()
            .map(|q| q.build(DatabaseBackend::Sqlite))
            .collect();
        let combined_sql = all_sql.join("\n");

        // Verify temp table approach
        assert!(
            combined_sql.contains("orders_temp"),
            "Should use temp table approach"
        );

        // The DROP TYPE statement should be empty for SQLite (only applies to PostgreSQL)
        // but the code path should still be executed
        let last_stmt = result.last().unwrap();
        let last_sql = last_stmt.build(DatabaseBackend::Sqlite);
        // SQLite doesn't have DROP TYPE, so it should be empty string
        assert!(
            last_sql.is_empty() || !last_sql.contains("DROP TYPE"),
            "SQLite should not emit DROP TYPE"
        );

        // Verify it DOES emit DROP TYPE for PostgreSQL
        let pg_last_sql = last_stmt.build(DatabaseBackend::Postgres);
        assert!(
            pg_last_sql.contains("DROP TYPE"),
            "PostgreSQL should emit DROP TYPE, got: {pg_last_sql}"
        );
    }

    #[test]
    fn test_delete_column_sqlite_with_check_constraint_referencing_column() {
        // When a CHECK constraint references the column being deleted,
        // SQLite can't use ALTER TABLE DROP COLUMN — must use temp table approach.
        let schema = vec![TableDef {
            name: "orders".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("amount", ColumnType::Simple(SimpleColumnType::Integer)),
            ],
            constraints: vec![TableConstraint::Check {
                name: "check_positive".into(),
                expr: "amount > 0".into(),
                strategy: vespertide_core::CheckViolationStrategy::default(),
            }],
        }];

        // Delete amount column — CHECK references it, so temp table is needed
        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "orders",
            "amount",
            None,
            &schema,
            &[],
        );

        // Should use temp table approach (CREATE temp, INSERT, DROP, RENAME)
        assert!(
            result.len() >= 4,
            "Expected temp table approach (>=4 stmts), got: {} statements",
            result.len()
        );

        let sql = result[0].build(DatabaseBackend::Sqlite);
        assert!(
            sql.contains("orders_temp"),
            "Expected temp table creation, got: {sql}"
        );
        // The CHECK constraint referencing "amount" should NOT be in the temp table
        assert!(
            !sql.contains("check_positive"),
            "CHECK referencing deleted column should be removed, got: {sql}"
        );
    }

    /// Mutant target: `sqlite_rebuild.rs` line 46 —
    /// `!expr.contains("\"col\"") && !expr.contains(col)` — the
    /// CHECK-constraint filter for SQLite temp-table rebuild after the
    /// referenced column is dropped. Pins BOTH quoted and unquoted shapes.
    #[rstest]
    #[case::sqlite_unquoted(DatabaseBackend::Sqlite, "age > 0")]
    #[case::sqlite_quoted(DatabaseBackend::Sqlite, r#""age" > 0"#)]
    #[case::postgres_unquoted(DatabaseBackend::Postgres, "age > 0")]
    #[case::postgres_quoted(DatabaseBackend::Postgres, r#""age" > 0"#)]
    #[case::mysql_unquoted(DatabaseBackend::MySql, "age > 0")]
    #[case::mysql_quoted(DatabaseBackend::MySql, r#""age" > 0"#)]
    fn delete_column_removes_check_constraint_referencing_it(
        #[case] backend: DatabaseBackend,
        #[case] check_expr: &str,
    ) {
        let schema = vec![TableDef {
            name: "people".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("age", ColumnType::Simple(SimpleColumnType::Integer)),
            ],
            constraints: vec![TableConstraint::Check {
                name: "chk_age_positive".into(),
                expr: check_expr.into(),
                strategy: vespertide_core::CheckViolationStrategy::default(),
            }],
        }];

        let queries = build_delete_column(backend, "people", "age", None, &schema, &[]);
        let sql = queries
            .iter()
            .map(|q| q.build(backend))
            .collect::<Vec<_>>()
            .join("\n");

        if backend == DatabaseBackend::Sqlite {
            assert!(
                !sql.contains("chk_age_positive"),
                "SQLite rebuild must omit the CHECK '{check_expr}' that \
                 references the deleted column; got: {sql}"
            );
            assert!(
                !sql.contains(check_expr),
                "SQLite rebuild must not echo the CHECK expression \
                 '{check_expr}' (it references the deleted column); got: {sql}"
            );
            assert!(
                sql.contains("people_temp"),
                "expected SQLite temp-table rebuild for 'people'; got: {sql}"
            );
        } else {
            assert!(
                !queries.is_empty(),
                "{backend:?}: expected DROP COLUMN output, got empty"
            );
            assert!(
                sql.contains("DROP COLUMN"),
                "{backend:?}: expected DROP COLUMN, got: {sql}"
            );
        }
    }

    #[test]
    fn test_delete_column_sqlite_with_check_constraint_not_referencing_column() {
        // When a CHECK constraint does NOT reference the column being deleted,
        // simple DROP COLUMN should work.
        let schema = vec![TableDef {
            name: "orders".into(),
            description: None,
            columns: vec![
                col("id", ColumnType::Simple(SimpleColumnType::Integer)),
                col("amount", ColumnType::Simple(SimpleColumnType::Integer)),
                col("note", ColumnType::Simple(SimpleColumnType::Text)),
            ],
            constraints: vec![TableConstraint::Check {
                name: "check_positive".into(),
                expr: "amount > 0".into(),
                strategy: vespertide_core::CheckViolationStrategy::default(),
            }],
        }];

        // Delete "note" column — CHECK only references "amount", not "note"
        let result = build_delete_column(
            DatabaseBackend::Sqlite,
            "orders",
            "note",
            None,
            &schema,
            &[],
        );

        assert_eq!(
            result.len(),
            1,
            "Unrelated CHECK should be skipped, got: {} statements",
            result.len()
        );

        let sql = result[0].build(DatabaseBackend::Sqlite);
        assert!(
            sql.contains("DROP COLUMN"),
            "Expected DROP COLUMN, got: {sql}"
        );
    }
}