use psql_scip::{
    core::{
        ddl::walk_ddl,
        schema::{IndexTarget, QualifiedName, SchemaModel},
    },
    parse::split_and_parse,
};

fn fixture_sql(name: &str) -> String {
    let base =
        std::env::var("CARGO_MANIFEST_DIR").expect("CARGO_MANIFEST_DIR must be set by cargo test");
    let path = std::path::PathBuf::from(base)
        .join("tests/fixtures")
        .join(name);
    std::fs::read_to_string(&path).unwrap_or_else(|_| panic!("fixture {name} must be readable"))
}

fn model_from_fixture(name: &str) -> SchemaModel {
    let sql = fixture_sql(name);
    let (stmts, _) = split_and_parse(&sql).expect("fixture must parse");
    let mut model = SchemaModel::default();
    walk_ddl(&stmts, &mut model);
    model
}

fn users_key() -> QualifiedName {
    QualifiedName::new(Some("public"), "users")
}

fn orders_key() -> QualifiedName {
    QualifiedName::new(Some("public"), "orders")
}

fn products_key() -> QualifiedName {
    QualifiedName::new(Some("public"), "products")
}

#[test]
fn schema_simple_has_two_tables() {
    let model = model_from_fixture("schema_simple.sql");
    assert_eq!(model.tables.len(), 2, "expected two tables");
}

#[test]
fn users_table_has_three_columns() {
    let model = model_from_fixture("schema_simple.sql");
    let table = model
        .tables
        .get(&users_key())
        .expect("public.users must exist");
    assert_eq!(table.columns.len(), 3, "users must have three columns");
}

#[test]
fn users_id_is_not_nullable() {
    let model = model_from_fixture("schema_simple.sql");
    let table = model
        .tables
        .get(&users_key())
        .expect("public.users must exist");
    let col = table.columns.get("id").expect("id column must exist");
    assert!(!col.nullable, "id must be NOT NULL");
}

#[test]
fn users_created_at_is_nullable() {
    let model = model_from_fixture("schema_simple.sql");
    let table = model
        .tables
        .get(&users_key())
        .expect("public.users must exist");
    let col = table
        .columns
        .get("created_at")
        .expect("created_at column must exist");
    assert!(col.nullable, "created_at must be nullable");
}

#[test]
fn users_table_comment_attached() {
    let model = model_from_fixture("schema_simple.sql");
    let table = model
        .tables
        .get(&users_key())
        .expect("public.users must exist");
    assert_eq!(
        table.doc.as_deref(),
        Some("registered application users"),
        "table doc comment must match"
    );
}

#[test]
fn users_email_comment_attached() {
    let model = model_from_fixture("schema_simple.sql");
    let table = model
        .tables
        .get(&users_key())
        .expect("public.users must exist");
    let col = table.columns.get("email").expect("email column must exist");
    assert_eq!(
        col.doc.as_deref(),
        Some("unique login email"),
        "email column doc must match"
    );
}

#[test]
fn schema_simple_has_one_view() {
    let model = model_from_fixture("schema_simple.sql");
    assert_eq!(model.views.len(), 1, "expected one view");
}

#[test]
fn schema_simple_has_one_function() {
    let model = model_from_fixture("schema_simple.sql");
    assert_eq!(model.functions.len(), 1, "expected one function");
}

#[test]
fn function_comment_attached() {
    let model = model_from_fixture("schema_simple.sql");
    let key = QualifiedName::new(Some("public"), "get_user");
    let func = model
        .functions
        .get(&key)
        .expect("public.get_user must exist");
    assert_eq!(
        func.doc.as_deref(),
        Some("fetch a single user by id"),
        "function doc must match"
    );
}

#[test]
fn schema_simple_has_one_type() {
    let model = model_from_fixture("schema_simple.sql");
    assert_eq!(model.types.len(), 1, "expected one type");
}

#[test]
fn alter_table_add_column_appears() {
    let model = model_from_fixture("schema_alter_table.sql");
    let table = model
        .tables
        .get(&products_key())
        .expect("public.products must exist");
    assert!(
        table.columns.contains_key("price"),
        "price column must be present after ADD COLUMN"
    );
}

#[test]
fn alter_table_drop_column_removed() {
    let model = model_from_fixture("schema_alter_table.sql");
    let table = model
        .tables
        .get(&products_key())
        .expect("public.products must exist");
    assert!(
        !table.columns.contains_key("name"),
        "name column must be absent after DROP COLUMN"
    );
}

#[test]
fn definitions_have_nonzero_ranges() {
    let model = model_from_fixture("schema_simple.sql");
    for (key, table) in &model.tables {
        assert!(
            u32::from(table.range.start()) < u32::from(table.range.end()),
            "table {key:?} must have a nonzero range"
        );
    }
}

#[test]
fn index_gin_on_tsvector_captures_method_and_target() {
    let model = model_from_fixture("ref_index_gin_tsvector.sql");
    let key = QualifiedName::new(Some("public"), "docs_search");
    let idx = model.indexes.get(&key).expect("docs_search must exist");
    assert_eq!(idx.method, "gin");
    assert_eq!(idx.targets, vec![IndexTarget::Column("search_vec".into())]);
    assert!(!idx.unique);
    assert_eq!(idx.predicate, None);
}

#[test]
fn index_partial_unique_captures_predicate() {
    let model = model_from_fixture("ref_index_partial_unique.sql");
    let key = QualifiedName::new(Some("public"), "users_email_active");
    let idx = model
        .indexes
        .get(&key)
        .expect("users_email_active must exist");
    assert_eq!(idx.method, "btree");
    assert_eq!(idx.targets, vec![IndexTarget::Column("email".into())]);
    assert!(idx.unique);
    assert_eq!(idx.predicate.as_deref(), Some("deleted_at IS NULL"));
}

#[test]
fn index_expression_captures_deparsed_expr() {
    let model = model_from_fixture("ref_index_expression.sql");
    let key = QualifiedName::new(Some("public"), "users_lower_email");
    let idx = model
        .indexes
        .get(&key)
        .expect("users_lower_email must exist");
    assert_eq!(idx.method, "btree");
    assert_eq!(idx.targets, vec![IndexTarget::Expr("lower(email)".into())]);
    assert!(!idx.unique);
    assert_eq!(idx.predicate, None);
}

#[test]
fn enum_labels_round_trip_through_walker() {
    let model = model_from_fixture("ref_enum_labels.sql");
    let key = QualifiedName::new(Some("public"), "account_status");
    let td = model.types.get(&key).expect("account_status must exist");
    assert_eq!(
        td.labels,
        vec![
            "OPEN".to_owned(),
            "PENDING".to_owned(),
            "FLAGGED".to_owned(),
            "SUSPENDED".to_owned(),
        ],
        "labels must round-trip case and order from source SQL",
    );
}

#[test]
fn column_defaults_round_trip_through_walker() {
    let model = model_from_fixture("ref_column_defaults.sql");
    let key = QualifiedName::new(Some("public"), "tasks");
    let table = model.tables.get(&key).expect("public.tasks must exist");
    assert_eq!(
        table.columns["id"].default_expr.as_deref(),
        Some("gen_random_uuid()"),
    );
    assert_eq!(
        table.columns["created_at"].default_expr.as_deref(),
        Some("now()"),
    );
    assert_eq!(
        table.columns["status"].default_expr.as_deref(),
        Some("'pending'"),
    );
    assert_eq!(table.columns["retries"].default_expr.as_deref(), Some("0"),);
    assert_eq!(table.columns["note"].default_expr, None);
}

#[test]
fn orders_total_is_nullable() {
    let model = model_from_fixture("schema_simple.sql");
    let table = model
        .tables
        .get(&orders_key())
        .expect("public.orders must exist");
    let col = table.columns.get("total").expect("total column must exist");
    assert!(
        col.nullable,
        "orders.total has no NOT NULL, must be nullable"
    );
}
