//! Integration tests for DuckDB migration support.
//!
//! These tests verify the full migration workflow for DuckDB, including
//! SQL generation for tables, enums, extensions, views, indexes, and
//! data-loss warnings.

use prax_migrate::{
    DuckDbSqlGenerator, EnumDiff, ExtensionDiff, FieldAlterDiff, FieldDiff, ForeignKeyDiff,
    IndexDiff, ModelAlterDiff, ModelDiff, SchemaDiff, ViewDiff,
};

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

fn make_bigint_pk(column_name: &str) -> FieldDiff {
    FieldDiff {
        name: column_name.to_string(),
        column_name: column_name.to_string(),
        sql_type: "BIGINT".to_string(),
        nullable: false,
        default: None,
        is_primary_key: true,
        is_auto_increment: true,
        is_unique: false,
    }
}

fn make_varchar_field(name: &str, nullable: bool) -> FieldDiff {
    FieldDiff {
        name: name.to_string(),
        column_name: name.to_string(),
        sql_type: "VARCHAR".to_string(),
        nullable,
        default: None,
        is_primary_key: false,
        is_auto_increment: false,
        is_unique: false,
    }
}

fn make_model(name: &str, table_name: &str, fields: Vec<FieldDiff>) -> ModelDiff {
    ModelDiff {
        name: name.to_string(),
        table_name: table_name.to_string(),
        fields,
        primary_key: vec!["id".to_string()],
        indexes: Vec::new(),
        unique_constraints: Vec::new(),
        foreign_keys: Vec::new(),
    }
}

#[test]
fn test_duckdb_empty_diff_produces_empty_migration() {
    let generator = DuckDbSqlGenerator;
    let diff = SchemaDiff::default();
    let result = generator.generate(&diff);
    assert!(result.is_empty());
    assert!(result.warnings.is_empty());
}

#[test]
fn test_duckdb_generate_create_table_sql() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    diff.create_models.push(make_model(
        "User",
        "users",
        vec![make_bigint_pk("id"), make_varchar_field("email", false)],
    ));
    diff.create_indexes
        .push(IndexDiff::new("idx_users_email", "users", vec!["email".to_string()]).unique());

    let migration = generator.generate(&diff);

    // up.sql should contain both CREATE TABLE and CREATE UNIQUE INDEX
    assert!(!migration.up.is_empty());
    assert!(migration.up.contains("CREATE TABLE \"users\""));
    assert!(migration.up.contains("GENERATED BY DEFAULT AS IDENTITY"));
    assert!(migration.up.contains("CREATE UNIQUE INDEX"));

    // down.sql should contain DROP INDEX and DROP TABLE
    assert!(migration.down.contains("DROP INDEX"));
    assert!(migration.down.contains("DROP TABLE"));

    // No warnings for pure creation
    assert!(migration.warnings.is_empty());
}

#[test]
fn test_duckdb_generate_drop_operations_with_warnings() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    diff.drop_models.push("legacy_table".to_string());

    diff.alter_models.push(ModelAlterDiff {
        name: "User".to_string(),
        table_name: "users".to_string(),
        add_fields: vec![],
        drop_fields: vec!["deprecated_field".to_string()],
        alter_fields: vec![FieldAlterDiff {
            name: "status".to_string(),
            column_name: "status".to_string(),
            old_type: Some("INTEGER".to_string()),
            new_type: Some("VARCHAR".to_string()),
            old_nullable: None,
            new_nullable: None,
            old_default: None,
            new_default: None,
        }],
        add_indexes: vec![],
        drop_indexes: vec![],
        add_foreign_keys: vec![],
        drop_foreign_keys: vec![],
    });

    let migration = generator.generate(&diff);

    // Three warnings: drop table, drop column, type change
    assert_eq!(migration.warnings.len(), 3);

    assert!(
        migration
            .warnings
            .iter()
            .any(|w| w.contains("legacy_table")),
        "Expected a drop-table warning for legacy_table"
    );
    assert!(
        migration
            .warnings
            .iter()
            .any(|w| w.contains("deprecated_field")),
        "Expected a drop-column warning for deprecated_field"
    );
    assert!(
        migration
            .warnings
            .iter()
            .any(|w| w.contains("reverse migration")),
        "Expected a type-change warning mentioning reverse migration"
    );
}

#[test]
fn test_duckdb_generate_list_type() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    diff.create_models.push(ModelDiff {
        name: "Product".to_string(),
        table_name: "products".to_string(),
        fields: vec![
            make_bigint_pk("id"),
            FieldDiff {
                name: "tags".to_string(),
                column_name: "tags".to_string(),
                sql_type: "VARCHAR[]".to_string(),
                nullable: false,
                default: Some("[]".to_string()),
                is_primary_key: false,
                is_auto_increment: false,
                is_unique: false,
            },
            FieldDiff {
                name: "prices".to_string(),
                column_name: "prices".to_string(),
                sql_type: "DECIMAL(10,2)[]".to_string(),
                nullable: true,
                default: None,
                is_primary_key: false,
                is_auto_increment: false,
                is_unique: false,
            },
        ],
        primary_key: vec!["id".to_string()],
        indexes: Vec::new(),
        unique_constraints: Vec::new(),
        foreign_keys: Vec::new(),
    });

    let migration = generator.generate(&diff);

    assert!(!migration.up.is_empty());
    let create_sql = &migration.up;

    // VARCHAR[] array type with default
    assert!(
        create_sql.contains("\"tags\" VARCHAR[] NOT NULL DEFAULT []"),
        "Expected tags column with VARCHAR[] NOT NULL DEFAULT [], got: {create_sql}"
    );

    // DECIMAL[] array type (nullable, so no NOT NULL)
    assert!(
        create_sql.contains("\"prices\" DECIMAL(10,2)[]"),
        "Expected prices column with DECIMAL(10,2)[], got: {create_sql}"
    );
}

#[test]
fn test_duckdb_generate_enum_with_table() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    diff.create_enums.push(EnumDiff {
        name: "status".to_string(),
        values: vec![
            "pending".to_string(),
            "active".to_string(),
            "archived".to_string(),
        ],
    });

    diff.create_models.push(ModelDiff {
        name: "Order".to_string(),
        table_name: "orders".to_string(),
        fields: vec![
            make_bigint_pk("id"),
            FieldDiff {
                name: "status".to_string(),
                column_name: "status".to_string(),
                sql_type: "status".to_string(), // enum type
                nullable: false,
                default: None,
                is_primary_key: false,
                is_auto_increment: false,
                is_unique: false,
            },
        ],
        primary_key: vec!["id".to_string()],
        indexes: Vec::new(),
        unique_constraints: Vec::new(),
        foreign_keys: Vec::new(),
    });

    let migration = generator.generate(&diff);

    // up: enum created before table
    assert!(
        migration.up.contains("CREATE TYPE"),
        "Expected CREATE TYPE in up.sql"
    );
    assert!(
        migration.up.contains("CREATE TABLE \"orders\""),
        "Expected CREATE TABLE orders in up.sql"
    );
    // Enum statement must appear before table statement
    let enum_pos = migration.up.find("CREATE TYPE").unwrap();
    let table_pos = migration.up.find("CREATE TABLE").unwrap();
    assert!(
        enum_pos < table_pos,
        "Enum must be created before the table that references it"
    );

    // down: table dropped before enum
    assert!(
        migration.down.contains("DROP TABLE"),
        "Expected DROP TABLE in down.sql"
    );
    assert!(
        migration.down.contains("DROP TYPE"),
        "Expected DROP TYPE in down.sql"
    );
    let drop_table_pos = migration.down.find("DROP TABLE").unwrap();
    let drop_type_pos = migration.down.find("DROP TYPE").unwrap();
    assert!(
        drop_table_pos < drop_type_pos,
        "Table must be dropped before the enum type"
    );
}

#[test]
fn test_duckdb_generate_extensions() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    diff.create_extensions.push(ExtensionDiff {
        name: "parquet".to_string(),
        schema: None,
        version: None,
    });
    diff.create_extensions.push(ExtensionDiff {
        name: "json".to_string(),
        schema: None,
        version: None,
    });

    let migration = generator.generate(&diff);

    assert!(!migration.up.is_empty());
    assert!(
        migration.up.contains("INSTALL 'parquet'"),
        "Expected INSTALL parquet"
    );
    assert!(
        migration.up.contains("LOAD 'parquet'"),
        "Expected LOAD parquet"
    );
    assert!(
        migration.up.contains("INSTALL 'json'"),
        "Expected INSTALL json"
    );
    assert!(migration.up.contains("LOAD 'json'"), "Expected LOAD json");
}

#[test]
fn test_duckdb_generate_view_with_materialized_warning() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    // Regular view — no warning expected
    diff.create_views.push(ViewDiff {
        name: "ActiveUsers".to_string(),
        view_name: "active_users".to_string(),
        sql_query: "SELECT * FROM users WHERE active = true".to_string(),
        is_materialized: false,
        refresh_interval: None,
        fields: vec![],
    });

    // Materialized view — should trigger a warning and fall back to regular VIEW
    diff.create_views.push(ViewDiff {
        name: "UserStats".to_string(),
        view_name: "user_stats".to_string(),
        sql_query: "SELECT COUNT(*) AS count FROM users".to_string(),
        is_materialized: true,
        refresh_interval: None,
        fields: vec![],
    });

    let migration = generator.generate(&diff);

    assert!(
        migration.up.contains("CREATE VIEW \"active_users\""),
        "Expected CREATE VIEW active_users"
    );
    assert!(
        migration.up.contains("CREATE VIEW \"user_stats\""),
        "Expected CREATE VIEW user_stats (materialized falls back)"
    );

    // Exactly one warning: for the materialized view
    assert_eq!(
        migration.warnings.len(),
        1,
        "Expected exactly 1 warning, got: {:?}",
        migration.warnings
    );
    assert!(
        migration.warnings[0].contains("materialized views"),
        "Warning should mention materialized views"
    );
    assert!(
        migration.warnings[0].contains("user_stats"),
        "Warning should name the view"
    );
}

#[test]
fn test_duckdb_foreign_key_unenforced_warning() {
    let generator = DuckDbSqlGenerator;
    let mut diff = SchemaDiff::default();

    diff.alter_models.push(ModelAlterDiff {
        name: "Post".to_string(),
        table_name: "posts".to_string(),
        add_fields: vec![],
        drop_fields: vec![],
        alter_fields: vec![],
        add_indexes: vec![],
        drop_indexes: vec![],
        add_foreign_keys: vec![ForeignKeyDiff {
            constraint_name: "fk_posts_user".to_string(),
            columns: vec!["user_id".to_string()],
            referenced_table: "users".to_string(),
            referenced_columns: vec!["id".to_string()],
            on_delete: Some("CASCADE".to_string()),
            on_update: None,
        }],
        drop_foreign_keys: vec![],
    });

    let migration = generator.generate(&diff);

    assert!(
        migration
            .warnings
            .iter()
            .any(|w| w.contains("Foreign keys") && w.contains("not enforced")),
        "Expected foreign key enforcement warning, got: {:?}",
        migration.warnings
    );
    assert!(
        migration.up.contains("FOREIGN KEY"),
        "Expected FOREIGN KEY in up.sql"
    );
}
