use dbnest_core::instance::SqliteInfo;
use dbnest_core::schema::apply::apply_sqlite_plan;
use dbnest_core::schema::load::{load_schema_dir, load_schema_json};
use dbnest_core::schema::model::Schema;
use dbnest_core::schema::plan::generators::{plan_postgres, plan_sqlite};
use dbnest_core::schema::validate::validate_schema;
use dbnest_core::{Backend, ConnectionInfo, Engine, Instance};
use serde_json::json;
use std::path::PathBuf;
use time::OffsetDateTime;

fn schema(value: serde_json::Value) -> Schema {
    serde_json::from_value(value).expect("schema should deserialize")
}

fn test_dir() -> PathBuf {
    let dir = std::env::temp_dir().join(format!("dbnest-test-{}", uuid::Uuid::new_v4().simple()));
    std::fs::create_dir_all(&dir).unwrap();
    dir
}

#[test]
fn nullable_defaults_to_true() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "email", "type": "string" }]
        }]
    }));

    assert!(schema.tables[0].columns[0].nullable);
}

#[test]
fn validation_rejects_invalid_identifier() {
    let schema = schema(json!({
        "tables": [{
            "name": "bad-name",
            "columns": [{ "name": "id", "type": "int64" }]
        }]
    }));

    let err = validate_schema(&schema).expect_err("invalid identifier should fail");
    assert!(err.to_string().contains("invalid table identifier"));
}

#[test]
fn validation_rejects_duplicate_global_index_names() {
    let schema = schema(json!({
        "tables": [
            {
                "name": "users",
                "columns": [{ "name": "email", "type": "string" }],
                "indexes": [{ "name": "idx_shared", "columns": ["email"] }]
            },
            {
                "name": "books",
                "columns": [{ "name": "title", "type": "string" }],
                "indexes": [{ "name": "idx_shared", "columns": ["title"] }]
            }
        ]
    }));

    let err = validate_schema(&schema).expect_err("duplicate index name should fail");
    assert!(err.to_string().contains("duplicate index name"));
}

#[test]
fn validation_rejects_duplicate_table_names() {
    let schema = schema(json!({
        "tables": [
            {
                "name": "users",
                "columns": [{ "name": "id", "type": "int64" }]
            },
            {
                "name": "users",
                "columns": [{ "name": "email", "type": "string" }]
            }
        ]
    }));

    let err = validate_schema(&schema).expect_err("duplicate table name should fail");
    assert!(err.to_string().contains("duplicate table name"));
}

#[test]
fn validation_rejects_duplicate_column_names() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [
                { "name": "email", "type": "string" },
                { "name": "email", "type": "string" }
            ]
        }]
    }));

    let err = validate_schema(&schema).expect_err("duplicate column name should fail");
    assert!(err.to_string().contains("duplicate column"));
}

#[test]
fn validation_rejects_index_referencing_unknown_column() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "email", "type": "string" }],
            "indexes": [{ "name": "idx_missing", "columns": ["missing"] }]
        }]
    }));

    let err = validate_schema(&schema).expect_err("unknown index column should fail");
    assert!(err.to_string().contains("references unknown column"));
}

#[test]
fn validation_rejects_duplicate_columns_inside_index() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "email", "type": "string" }],
            "indexes": [{ "name": "idx_email", "columns": ["email", "email"] }]
        }]
    }));

    let err = validate_schema(&schema).expect_err("duplicate index column should fail");
    assert!(err.to_string().contains("contains duplicate column"));
}

#[test]
fn directory_loader_is_deterministic() {
    let dir = test_dir();
    std::fs::create_dir(dir.join("z_table")).unwrap();
    std::fs::create_dir(dir.join("a_table")).unwrap();
    std::fs::write(
        dir.join("z_table").join("columns.json"),
        r#"[{"name":"id","type":"int64"}]"#,
    )
    .unwrap();
    std::fs::write(
        dir.join("a_table").join("columns.json"),
        r#"[{"name":"id","type":"int64"}]"#,
    )
    .unwrap();

    let schema = load_schema_dir(&dir).unwrap();
    let names = schema
        .tables
        .iter()
        .map(|t| t.name.as_str())
        .collect::<Vec<_>>();

    assert_eq!(names, ["a_table", "z_table"]);
    let _ = std::fs::remove_dir_all(dir);
}

#[test]
fn json_loader_reads_single_schema_file() {
    let dir = test_dir();
    let path = dir.join("schema.json");
    std::fs::write(
        &path,
        r#"{"tables":[{"name":"users","columns":[{"name":"id","type":"int64"}]}]}"#,
    )
    .unwrap();

    let schema = load_schema_json(&path).unwrap();

    assert_eq!(schema.tables.len(), 1);
    assert_eq!(schema.tables[0].name, "users");
    let _ = std::fs::remove_dir_all(dir);
}

#[test]
fn directory_loader_reads_optional_indexes_file() {
    let dir = test_dir();
    let table_dir = dir.join("users");
    std::fs::create_dir(&table_dir).unwrap();
    std::fs::write(
        table_dir.join("columns.json"),
        r#"[{"name":"email","type":"string"}]"#,
    )
    .unwrap();
    std::fs::write(
        table_dir.join("indexes.json"),
        r#"[{"name":"idx_users_email","columns":["email"],"unique":true}]"#,
    )
    .unwrap();

    let schema = load_schema_dir(&dir).unwrap();

    assert_eq!(schema.tables[0].indexes.len(), 1);
    assert_eq!(schema.tables[0].indexes[0].name, "idx_users_email");
    assert!(schema.tables[0].indexes[0].unique);
    let _ = std::fs::remove_dir_all(dir);
}

#[test]
fn directory_loader_allows_missing_indexes_file() {
    let dir = test_dir();
    let table_dir = dir.join("users");
    std::fs::create_dir(&table_dir).unwrap();
    std::fs::write(
        table_dir.join("columns.json"),
        r#"[{"name":"email","type":"string"}]"#,
    )
    .unwrap();

    let schema = load_schema_dir(&dir).unwrap();

    assert!(schema.tables[0].indexes.is_empty());
    let _ = std::fs::remove_dir_all(dir);
}

#[test]
fn validation_rejects_invalid_typed_defaults() {
    let int_schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "age", "type": "int64", "default": "old" }]
        }]
    }));
    let int_err = validate_schema(&int_schema).expect_err("invalid int default should fail");
    assert!(int_err.to_string().contains("must be an integer"));

    let bool_schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "active", "type": "bool", "default": "yes" }]
        }]
    }));
    let bool_err = validate_schema(&bool_schema).expect_err("invalid bool default should fail");
    assert!(bool_err.to_string().contains("must be true/false/1/0"));
}

#[test]
fn validation_rejects_empty_raw_sql_default() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "id", "type": "int64", "default": { "sql": " " } }]
        }]
    }));

    let err = validate_schema(&schema).expect_err("empty raw SQL default should fail");
    assert!(err.to_string().contains("raw SQL default"));
}

#[test]
fn sqlite_plan_uses_typed_defaults_and_bool_check() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [
                { "name": "email", "type": "string", "default": "hello@example.com" },
                { "name": "active", "type": "bool", "default": true },
                { "name": "created_at", "type": "timestamp", "default": "now" },
                { "name": "raw_value", "type": "int64", "default": { "sql": "1 + 1" } }
            ]
        }]
    }));

    validate_schema(&schema).unwrap();
    let plan = plan_sqlite(&schema);

    assert_eq!(
        plan.statements[0],
        "CREATE TABLE IF NOT EXISTS \"users\" (\n  \"email\" TEXT DEFAULT 'hello@example.com',\n  \"active\" INTEGER CHECK (\"active\" IN (0, 1)) DEFAULT 1,\n  \"created_at\" TEXT DEFAULT CURRENT_TIMESTAMP,\n  \"raw_value\" INTEGER DEFAULT 1 + 1\n);"
    );
}

#[test]
fn postgres_plan_uses_typed_defaults() {
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [
                { "name": "email", "type": "string", "default": "hello@example.com" },
                { "name": "active", "type": "bool", "default": false },
                { "name": "created_at", "type": "timestamp", "default": "now" },
                { "name": "id", "type": "uuid", "default": { "sql": "gen_random_uuid()" } }
            ]
        }]
    }));

    validate_schema(&schema).unwrap();
    let plan = plan_postgres(&schema);

    assert_eq!(
        plan.statements[0],
        "CREATE TABLE IF NOT EXISTS \"users\" (\n  \"email\" TEXT DEFAULT 'hello@example.com',\n  \"active\" BOOLEAN DEFAULT FALSE,\n  \"created_at\" TIMESTAMPTZ DEFAULT NOW(),\n  \"id\" UUID DEFAULT gen_random_uuid()\n);"
    );
}

#[test]
fn sqlite_apply_executes_plan() {
    let dir = test_dir();
    let db_path = dir.join("test.sqlite");
    let inst = Instance {
        id: "testid".into(),
        engine: Engine::Sqlite,
        backend: Backend::Embedded,
        created_at: OffsetDateTime::now_utc(),
        connection: ConnectionInfo {
            database_url: format!("sqlite:///{}", db_path.display()),
            host: None,
            port: None,
            database: None,
            user: None,
        },
        sqlite: Some(SqliteInfo {
            path: db_path.clone(),
            managed: false,
        }),
        container: None,
    };
    let schema = schema(json!({
        "tables": [{
            "name": "users",
            "columns": [{ "name": "id", "type": "int64", "primary_key": true }]
        }]
    }));
    let plan = plan_sqlite(&schema);

    apply_sqlite_plan(&inst, &plan).unwrap();

    let conn = rusqlite::Connection::open(db_path).unwrap();
    let count: i64 = conn
        .query_row(
            "SELECT count(*) FROM sqlite_master WHERE type = 'table' AND name = 'users'",
            [],
            |row| row.get(0),
        )
        .unwrap();

    assert_eq!(count, 1);
    let _ = std::fs::remove_dir_all(dir);
}
