use crate::db::DB_POOL;

use crate::user::User;

use sqlx::PgPool;

use sqlx::types::Uuid;

use supp_macro::local_db_sqlx_test;

use tokio::sync::OnceCell;

static USER: OnceCell<User> = OnceCell::const_new();

#[local_db_sqlx_test]

async fn test_create_and_list_scripts(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let bytecode = vec![0x00, 0x61, 0x73, 0x6d];

    let id = user.create_script(bytecode.clone(), None).await?;

    assert!(!id.is_nil());

    let scripts = user.list_scripts().await?;

    assert_eq!(scripts.len(), 1);

    assert_eq!(scripts[0].id, id);

    assert_eq!(scripts[0].size, bytecode.len() as i32);

    assert!(scripts[0].name.is_none());

}

#[local_db_sqlx_test]

async fn test_create_script_with_name(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let bytecode = vec![0x00, 0x61, 0x73, 0x6d];

    let name = "test_script".to_string();

    user.create_script(bytecode, Some(name.clone())).await?;

    let scripts = user.list_scripts().await?;

    assert_eq!(scripts.len(), 1);

    assert_eq!(scripts[0].name, Some(name));

}

#[local_db_sqlx_test]

async fn test_get_script(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let bytecode = vec![0x00, 0x61, 0x73, 0x6d, 0x01, 0x00, 0x00, 0x00];

    let name = "fetch_test".to_string();

    let id = user

        .create_script(bytecode.clone(), Some(name.clone()))

        .await?;

    let script = user.get_script(id).await?;

    assert_eq!(script.id, id);

    assert_eq!(script.bytecode, bytecode);

    assert_eq!(script.name, Some(name));

}

#[local_db_sqlx_test]

async fn test_update_script_bytecode(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let original = vec![0x00, 0x61, 0x73, 0x6d];

    let updated = vec![0x00, 0x61, 0x73, 0x6d, 0x01, 0x00, 0x00, 0x00];

    let id = user.create_script(original, None).await?;

    user.update_script_bytecode(id, updated.clone()).await?;

    let script = user.get_script(id).await?;

    assert_eq!(script.bytecode, updated);

}

#[local_db_sqlx_test]

async fn test_delete_script(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let bytecode = vec![0x00, 0x61, 0x73, 0x6d];

    let id = user

        .create_script(bytecode, Some("to_delete".to_string()))

        .await?;

    assert_eq!(user.list_scripts().await?.len(), 1);

    user.delete_script(id).await?;

    assert!(user.list_scripts().await?.is_empty());

}

#[local_db_sqlx_test]

async fn test_set_script_enabled(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let bytecode = vec![0x00, 0x61, 0x73, 0x6d];

    let id = user.create_script(bytecode, None).await?;

    assert!(user.get_script(id).await?.enabled.is_none());

    user.set_script_enabled(id, false).await?;

    assert_eq!(

        user.get_script(id).await?.enabled,

        Some("false".to_string())

    );

    user.set_script_enabled(id, true).await?;

    assert!(user.get_script(id).await?.enabled.is_none());

}

#[local_db_sqlx_test]

async fn test_update_script_name(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let bytecode = vec![0x00, 0x61, 0x73, 0x6d];

    let id = user

        .create_script(bytecode, Some("original_name".to_string()))

        .await?;

    assert_eq!(

        user.get_script(id).await?.name,

        Some("original_name".to_string())

    );

    user.update_script_name(id, Some("new_name".to_string()))

        .await?;

    assert_eq!(

        user.get_script(id).await?.name,

        Some("new_name".to_string())

    );

    user.update_script_name(id, None).await?;

    assert!(user.get_script(id).await?.name.is_none());

}

#[local_db_sqlx_test]

async fn test_multiple_scripts(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id1 = user

        .create_script(vec![0x01], Some("script1".to_string()))

        .await?;

    let id2 = user

        .create_script(vec![0x02, 0x03], Some("script2".to_string()))

        .await?;

    let id3 = user.create_script(vec![0x04, 0x05, 0x06], None).await?;

    let ids: Vec<Uuid> = user.list_scripts().await?.iter().map(|s| s.id).collect();

    assert_eq!(ids.len(), 3);

    assert!(ids.contains(&id1) && ids.contains(&id2) && ids.contains(&id3));

    user.delete_script(id2).await?;

    let ids: Vec<Uuid> = user.list_scripts().await?.iter().map(|s| s.id).collect();

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

    assert!(ids.contains(&id1) && !ids.contains(&id2) && ids.contains(&id3));

}

#[local_db_sqlx_test]

async fn test_template_crud_round_trip(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let source = "(set-draft-note \"Groceries\")".to_string();

    let id = user

        .create_template(source.clone(), Some("groceries".to_string()))

        .await?;

    let detail = user.get_template(id).await?;

    assert_eq!(detail.source, source);

    assert_eq!(detail.name, Some("groceries".to_string()));

    let templates = user.list_templates().await?;

    assert_eq!(templates.len(), 1);

    assert_eq!(templates[0].id, id);

    user.update_template_source(id, "(set-draft-note \"Rent\")".to_string())

        .await?;

    assert_eq!(

        user.get_template(id).await?.source,

        "(set-draft-note \"Rent\")"

    );

    user.update_template_name(id, Some("rent".to_string()))

        .await?;

    assert_eq!(user.get_template(id).await?.name, Some("rent".to_string()));

    user.delete_template(id).await?;

    assert!(user.list_templates().await?.is_empty());

}

/// Scripts and templates are isolated by kind: a template never shows up in the

/// script listing and vice versa.

#[local_db_sqlx_test]

async fn test_kind_isolation(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let script_id = user.create_script(vec![0x00, 0x61], None).await?;

    let template_id = user

        .create_template("(set-draft-note \"x\")".to_string(), None)

        .await?;

    let script_ids: Vec<Uuid> = user.list_scripts().await?.iter().map(|s| s.id).collect();

    assert_eq!(script_ids, vec![script_id]);

    let template_ids: Vec<Uuid> = user.list_templates().await?.iter().map(|t| t.id).collect();

    assert_eq!(template_ids, vec![template_id]);

    assert!(user.get_script(template_id).await.is_err());

    assert!(user.get_template(script_id).await.is_err());

}

/// Two artifacts sharing the same canonical `name`/`enabled` tag values both

/// link correctly — the canonical-id-propagation fix. Before the fix, the

/// second create linked a dangling fresh uuid and the FK insert failed.

#[local_db_sqlx_test]

async fn test_shared_canonical_tag_values(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id1 = user

        .create_script(vec![0x01], Some("shared".to_string()))

        .await?;

    let id2 = user

        .create_script(vec![0x02], Some("shared".to_string()))

        .await?;

    user.set_script_enabled(id1, false).await?;

    user.set_script_enabled(id2, false).await?;

    let scripts = user.list_scripts().await?;

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

    for s in &scripts {

        assert_eq!(s.name, Some("shared".to_string()));

        assert_eq!(s.enabled, Some("false".to_string()));

    }

}

/// Deleting one of two artifacts sharing the canonical `kind` tag succeeds

/// (artifact-lifecycle delete — parent gone at commit, deferred guard passes).

#[local_db_sqlx_test]

async fn test_lifecycle_delete_with_shared_kind(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id1 = user.create_script(vec![0x01], None).await?;

    let id2 = user.create_script(vec![0x02], None).await?;

    user.delete_script(id1).await?;

    let remaining: Vec<Uuid> = user.list_scripts().await?.iter().map(|s| s.id).collect();

    assert_eq!(remaining, vec![id2]);

}

/// Resolve the canonical id of the `kind` tag linked to an artifact.

/// The auto-run selector is fail-closed: a source-only template is never

/// returned, while an automation is. This is the load-bearing execution guard.

#[local_db_sqlx_test]

async fn test_template_never_in_autorun(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let script_id = user

        .create_script(vec![0x00, 0x61, 0x73, 0x6d], None)

        .await?;

    user.create_template("(set-draft-note \"x\")".to_string(), None)

        .await?;

    let enabled: Vec<Uuid> = sqlx::query_file!("sql/select/artifacts/enabled.sql")

        .fetch_all(&pool)

        .await?

        .into_iter()

        .map(|r| r.id)

        .collect();

    assert_eq!(enabled, vec![script_id]);

}

/// The DB rejects a second `kind` tag on one artifact (one-kind invariant).

#[local_db_sqlx_test]

async fn test_db_rejects_second_kind_tag(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id = user.create_script(vec![0x01], None).await?;

    // Upsert a kind=template tag row, then try to also link it.

    let template_tag: Uuid = sqlx::query_scalar(

        "INSERT INTO tags (id, tag_name, tag_value) VALUES ($1, 'kind', 'template') \

         ON CONFLICT (tag_name, tag_value) DO UPDATE SET tag_value = excluded.tag_value \

         RETURNING id",

    )

    .bind(Uuid::new_v4())

    .fetch_one(&pool)

    .await?;

    let result = sqlx::query("INSERT INTO artifact_tags (artifact_id, tag_id) VALUES ($1, $2)")

        .bind(id)

        .bind(template_tag)

        .execute(&pool)

        .await;

    assert!(result.is_err(), "second kind tag must be rejected");

}

/// The DB rejects tagging a source-only artifact as automation (kind<->shape):

/// an automation must carry bytecode.

#[local_db_sqlx_test]

async fn test_db_rejects_kind_shape_mismatch(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id = user

        .create_template("(set-draft-note \"x\")".to_string(), None)

        .await?;

    let template_kind = kind_tag_id(&pool, id).await;

    // Detach the correct kind=template, then attempt kind=automation.

    sqlx::query("DELETE FROM artifact_tags WHERE artifact_id = $1 AND tag_id = $2")

        .bind(id)

        .bind(template_kind)

        .execute(&pool)

        .await

        .ok();

    let automation_tag: Uuid = sqlx::query_scalar(

        "INSERT INTO tags (id, tag_name, tag_value) VALUES ($1, 'kind', 'automation') \

         ON CONFLICT (tag_name, tag_value) DO UPDATE SET tag_value = excluded.tag_value \

         RETURNING id",

    )

    .bind(Uuid::new_v4())

    .fetch_one(&pool)

    .await?;

    let result = sqlx::query("INSERT INTO artifact_tags (artifact_id, tag_id) VALUES ($1, $2)")

        .bind(id)

        .bind(automation_tag)

        .execute(&pool)

        .await;

    assert!(

        result.is_err(),

        "tagging a source-only artifact as automation must be rejected"

    );

}

/// The DB rejects a generic unlink of a `kind` tag while the artifact survives

/// (the deferred DELETE guard distinguishes lifecycle delete from unlink).

#[local_db_sqlx_test]

async fn test_db_rejects_generic_kind_unlink(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id = user.create_script(vec![0x01], None).await?;

    let kind_tag = kind_tag_id(&pool, id).await;

    let result = sqlx::query("DELETE FROM artifact_tags WHERE artifact_id = $1 AND tag_id = $2")

        .bind(id)

        .bind(kind_tag)

        .execute(&pool)

        .await;

    assert!(

        result.is_err(),

        "unlinking a kind tag from a surviving artifact must be rejected"

    );

}

/// The DB rejects deleting a `kind` tag row that is still referenced

/// (the shared-canonical DELETE vector via `delete_tag`).

#[local_db_sqlx_test]

async fn test_db_rejects_referenced_kind_tag_delete(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id = user.create_script(vec![0x01], None).await?;

    let kind_tag = kind_tag_id(&pool, id).await;

    let result = sqlx::query("DELETE FROM tags WHERE id = $1")

        .bind(kind_tag)

        .execute(&pool)

        .await;

    assert!(

        result.is_err(),

        "deleting a referenced kind tag must be rejected"

    );

}

/// The DB rejects renaming a linked `kind` tag into a value inconsistent with

/// the artifact's shape (the canonical-rename vector via `update_tag`).

#[local_db_sqlx_test]

async fn test_db_rejects_kind_tag_rename(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id = user.create_script(vec![0x01], None).await?;

    let kind_tag = kind_tag_id(&pool, id).await;

    // Flip automation -> template on an artifact that carries bytecode.

    let result = sqlx::query("UPDATE tags SET tag_value = 'template' WHERE id = $1")

        .bind(kind_tag)

        .execute(&pool)

        .await;

    assert!(

        result.is_err(),

        "flipping a linked kind tag to an inconsistent value must be rejected"

    );

}

/// Renaming a linked `kind` tag to a NON-kind name (so the artifact would end

/// up with zero kind tags) is rejected by the exactly-one-kind invariant. This

/// is the `update_tag`-orphan vector: a generic tag rename must not strip the

/// discriminator off a live artifact.

#[local_db_sqlx_test]

async fn test_db_rejects_kind_tag_rename_to_non_kind(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let id = user.create_script(vec![0x01], None).await?;

    let result = user

        .update_tag(

            kind_tag_id(&pool, id).await,

            "category".to_string(),

            "misc".to_string(),

            None,

        )

        .await;

    assert!(

        result.is_err(),

        "renaming a linked kind tag to a non-kind must be rejected (would orphan)"

    );

}

/// A raw `INSERT INTO artifacts` with no kind tag cannot commit — the deferred

/// artifacts trigger requires exactly one kind tag at commit.

#[local_db_sqlx_test]

async fn test_db_rejects_artifact_without_kind(pool: PgPool) -> Result<(), anyhow::Error> {

    user().await;

    let result = sqlx::query("INSERT INTO artifacts (id, bytecode, source) VALUES ($1, $2, NULL)")

        .bind(Uuid::new_v4())

        .bind(vec![0x01u8])

        .execute(&pool)

        .await;

    assert!(

        result.is_err(),

        "an artifact with no kind tag must not commit"

    );

}

/// Moving a `kind` link off one artifact onto another (raw UPDATE of

/// `artifact_tags.artifact_id`) is rejected: the old owner would be left

/// kindless. Validates the old-owner re-check on UPDATE.

#[local_db_sqlx_test]

async fn test_db_rejects_kind_link_move(pool: PgPool) -> Result<(), anyhow::Error> {

    let user = user().await;

    let donor = user.create_script(vec![0x01], None).await?;

    let recipient = user.create_script(vec![0x02], None).await?;

    let donor_kind_link = kind_tag_id(&pool, donor).await;

    // Repoint the donor's kind link to the recipient — donor is left kindless,

    // recipient ends up with two kind tags. Either way: rejected.

    let result = sqlx::query(

        "UPDATE artifact_tags SET artifact_id = $1 \

         WHERE artifact_id = $2 AND tag_id = $3",

    )

    .bind(recipient)

    .bind(donor)

    .bind(donor_kind_link)

    .execute(&pool)

    .await;

    assert!(

        result.is_err(),

        "moving a kind link off its artifact must be rejected"

    );

}