//! DB-layer contract tests for `db::items::bulk`, batch item operations.
//!
//! Audit Run 16 flagged `bulk` as a Testing cold spot. Every bulk mutation is
//! ownership-scoped via `project_id IN (SELECT id FROM projects WHERE user_id =
//! $n)`, an IDOR seal that was only exercised through the HTTP surface. These
//! pin that seal at the DB layer (a non-owner's bulk op touches zero rows), plus
//! the soft-delete idempotency, the tag ON CONFLICT dedup, `move_item`'s
//! sort-order swap, and `duplicate_item`'s savepoint slug-collision retry (the
//! Phase 3 TOCTOU fix): two concurrent duplicates of one source get distinct
//! slugs and both succeed.

use crate::harness::db::TestDb;
use makenotwork::db::items;
use makenotwork::db::{ItemId, PriceCents, ProjectId, TagId, UserId};

async fn seed_user(pool: &sqlx::PgPool, username: &str) -> UserId {
    let hash = makenotwork::auth::hash_password("password123").expect("hash");
    sqlx::query_scalar::<_, UserId>(
        "INSERT INTO users (username, email, password_hash, email_verified)
         VALUES ($1, $2, $3, true) RETURNING id",
    )
    .bind(username)
    .bind(format!("{username}@test.com"))
    .bind(&hash)
    .fetch_one(pool)
    .await
    .expect("seed user")
}

async fn seed_project(pool: &sqlx::PgPool, user: UserId, slug: &str) -> ProjectId {
    sqlx::query_scalar::<_, ProjectId>(
        "INSERT INTO projects (user_id, slug, title) VALUES ($1, $2, 'P') RETURNING id",
    )
    .bind(user)
    .bind(slug)
    .fetch_one(pool)
    .await
    .expect("seed project")
}

/// Seed an item with an explicit sort_order and a unique slug.
async fn seed_item(pool: &sqlx::PgPool, project: ProjectId, slug: &str, sort: i32) -> ItemId {
    // Explicit is_public = false: the column defaults to true, but these tests
    // assert publish/unpublish transitions, so items start unpublished.
    sqlx::query_scalar::<_, ItemId>(
        "INSERT INTO items (project_id, title, item_type, price_cents, slug, sort_order, is_public)
         VALUES ($1, $2, 'digital', 1000, $3, $4, false) RETURNING id",
    )
    .bind(project)
    .bind(format!("Item {slug}"))
    .bind(slug)
    .bind(sort)
    .fetch_one(pool)
    .await
    .expect("seed item")
}

async fn seed_tag(pool: &sqlx::PgPool, slug: &str) -> TagId {
    sqlx::query_scalar::<_, TagId>(
        "INSERT INTO tags (name, slug, path) VALUES ($1, $2, $2) RETURNING id",
    )
    .bind(slug)
    .bind(slug)
    .fetch_one(pool)
    .await
    .expect("seed tag")
}

async fn is_public(pool: &sqlx::PgPool, item: ItemId) -> bool {
    sqlx::query_scalar::<_, bool>("SELECT is_public FROM items WHERE id = $1")
        .bind(item)
        .fetch_one(pool)
        .await
        .unwrap()
}

async fn sort_order(pool: &sqlx::PgPool, item: ItemId) -> i32 {
    sqlx::query_scalar::<_, i32>("SELECT sort_order FROM items WHERE id = $1")
        .bind(item)
        .fetch_one(pool)
        .await
        .unwrap()
}

async fn price_of(pool: &sqlx::PgPool, item: ItemId) -> i32 {
    sqlx::query_scalar::<_, i32>("SELECT price_cents FROM items WHERE id = $1")
        .bind(item)
        .fetch_one(pool)
        .await
        .unwrap()
}

// ── ownership scoping (IDOR seal) ────────────────────────────────────────────

#[tokio::test]
async fn bulk_publish_affects_only_owned_items_and_clears_schedule() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_pub_owner").await;
    let attacker = seed_user(&db.pool, "blk_pub_attacker").await;
    let project = seed_project(&db.pool, owner, "blk-pub").await;
    let a = seed_item(&db.pool, project, "pa", 0).await;
    let b = seed_item(&db.pool, project, "pb", 1).await;
    // Give one a scheduled publish_at to prove bulk_publish clears it.
    sqlx::query("UPDATE items SET publish_at = NOW() + INTERVAL '1 day' WHERE id = $1")
        .bind(a)
        .execute(&db.pool)
        .await
        .unwrap();

    // A non-owner cannot publish the items, zero rows, nothing changes.
    let hijack = items::bulk_publish(&db.pool, &[a, b], project, attacker)
        .await
        .unwrap();
    assert_eq!(hijack, 0, "a non-owner bulk_publish must touch no rows");
    assert!(!is_public(&db.pool, a).await);

    // The owner publishes both and the schedule is cleared.
    let n = items::bulk_publish(&db.pool, &[a, b], project, owner)
        .await
        .unwrap();
    assert_eq!(n, 2);
    assert!(is_public(&db.pool, a).await && is_public(&db.pool, b).await);
    let sched: Option<chrono::DateTime<chrono::Utc>> =
        sqlx::query_scalar("SELECT publish_at FROM items WHERE id = $1")
            .bind(a)
            .fetch_one(&db.pool)
            .await
            .unwrap();
    assert!(
        sched.is_none(),
        "bulk_publish clears any scheduled publish_at"
    );
}

#[tokio::test]
async fn bulk_unpublish_is_owner_scoped() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_unpub_owner").await;
    let attacker = seed_user(&db.pool, "blk_unpub_attacker").await;
    let project = seed_project(&db.pool, owner, "blk-unpub").await;
    let item = seed_item(&db.pool, project, "ua", 0).await;
    items::bulk_publish(&db.pool, &[item], project, owner)
        .await
        .unwrap();

    assert_eq!(
        items::bulk_unpublish(&db.pool, &[item], project, attacker)
            .await
            .unwrap(),
        0
    );
    assert!(
        is_public(&db.pool, item).await,
        "a non-owner cannot unpublish"
    );
    assert_eq!(
        items::bulk_unpublish(&db.pool, &[item], project, owner)
            .await
            .unwrap(),
        1
    );
    assert!(!is_public(&db.pool, item).await);
}

#[tokio::test]
async fn bulk_delete_soft_deletes_once_and_is_owner_scoped() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_del_owner").await;
    let attacker = seed_user(&db.pool, "blk_del_attacker").await;
    let project = seed_project(&db.pool, owner, "blk-del").await;
    let item = seed_item(&db.pool, project, "da", 0).await;

    assert_eq!(
        items::bulk_delete(&db.pool, &[item], project, attacker)
            .await
            .unwrap(),
        0,
        "a non-owner cannot delete"
    );

    // First delete soft-deletes; the second is a no-op (deleted_at IS NULL guard).
    assert_eq!(
        items::bulk_delete(&db.pool, &[item], project, owner)
            .await
            .unwrap(),
        1
    );
    assert_eq!(
        items::bulk_delete(&db.pool, &[item], project, owner)
            .await
            .unwrap(),
        0,
        "re-deleting an already-deleted item is a no-op"
    );
    let (deleted, public): (bool, bool) =
        sqlx::query_as("SELECT deleted_at IS NOT NULL, is_public FROM items WHERE id = $1")
            .bind(item)
            .fetch_one(&db.pool)
            .await
            .unwrap();
    assert!(
        deleted && !public,
        "soft-delete stamps deleted_at and unpublishes"
    );
}

#[tokio::test]
async fn bulk_update_price_is_owner_scoped() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_price_owner").await;
    let attacker = seed_user(&db.pool, "blk_price_attacker").await;
    let project = seed_project(&db.pool, owner, "blk-price").await;
    let item = seed_item(&db.pool, project, "pra", 0).await;

    let new_price = PriceCents::new(4200).expect("valid price");
    assert_eq!(
        items::bulk_update_price(&db.pool, &[item], project, attacker, new_price)
            .await
            .unwrap(),
        0
    );
    assert_eq!(
        price_of(&db.pool, item).await,
        1000,
        "a non-owner cannot reprice"
    );

    assert_eq!(
        items::bulk_update_price(&db.pool, &[item], project, owner, new_price)
            .await
            .unwrap(),
        1
    );
    assert_eq!(price_of(&db.pool, item).await, 4200);
}

#[tokio::test]
async fn bulk_add_tag_dedups_and_is_owner_scoped() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_tag_owner").await;
    let attacker = seed_user(&db.pool, "blk_tag_attacker").await;
    let project = seed_project(&db.pool, owner, "blk-tag").await;
    let a = seed_item(&db.pool, project, "ta", 0).await;
    let b = seed_item(&db.pool, project, "tb", 1).await;
    let tag = seed_tag(&db.pool, "genre").await;

    assert_eq!(
        items::bulk_add_tag(&db.pool, &[a, b], project, attacker, tag)
            .await
            .unwrap(),
        0,
        "a non-owner cannot tag"
    );

    // First tag both; a repeat is a no-op via ON CONFLICT (item_id, tag_id).
    assert_eq!(
        items::bulk_add_tag(&db.pool, &[a, b], project, owner, tag)
            .await
            .unwrap(),
        2
    );
    assert_eq!(
        items::bulk_add_tag(&db.pool, &[a, b], project, owner, tag)
            .await
            .unwrap(),
        0,
        "re-tagging is idempotent"
    );
}

// ── move_item reorder ────────────────────────────────────────────────────────

#[tokio::test]
async fn move_item_swaps_sort_order_within_the_project() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_move_owner").await;
    let project = seed_project(&db.pool, owner, "blk-move").await;
    let first = seed_item(&db.pool, project, "m0", 0).await;
    let middle = seed_item(&db.pool, project, "m1", 1).await;
    let last = seed_item(&db.pool, project, "m2", 2).await;

    // Move the middle item up: it swaps with the first.
    items::move_item(&db.pool, project, owner, middle, "up")
        .await
        .unwrap();
    assert_eq!(sort_order(&db.pool, middle).await, 0);
    assert_eq!(sort_order(&db.pool, first).await, 1);
    assert_eq!(
        sort_order(&db.pool, last).await,
        2,
        "the untouched item keeps its slot"
    );

    // Moving the top item up again is a no-op (already first).
    items::move_item(&db.pool, project, owner, middle, "up")
        .await
        .unwrap();
    assert_eq!(sort_order(&db.pool, middle).await, 0);
}

// ── duplicate_item, metadata copy + slug-collision retry ────────────────────

#[tokio::test]
async fn duplicate_item_makes_a_draft_copy_with_metadata() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_dup_owner").await;
    let attacker = seed_user(&db.pool, "blk_dup_attacker").await;
    let project = seed_project(&db.pool, owner, "blk-dup").await;
    let source = seed_item(&db.pool, project, "orig", 0).await;
    let tag = seed_tag(&db.pool, "dup-tag").await;
    sqlx::query("INSERT INTO item_tags (item_id, tag_id) VALUES ($1, $2)")
        .bind(source)
        .bind(tag)
        .execute(&db.pool)
        .await
        .unwrap();
    sqlx::query("UPDATE items SET is_public = true WHERE id = $1")
        .bind(source)
        .execute(&db.pool)
        .await
        .unwrap();

    // A non-owner cannot duplicate (ownership verified via project subquery).
    assert!(
        items::duplicate_item(&db.pool, source, attacker)
            .await
            .is_err()
    );

    let copy = items::duplicate_item(&db.pool, source, owner)
        .await
        .unwrap();
    assert!(
        copy.title.starts_with("Copy of "),
        "the copy is titled 'Copy of ...'"
    );
    assert!(
        !copy.is_public,
        "the copy is a private draft regardless of the source"
    );
    let tag_count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM item_tags WHERE item_id = $1")
        .bind(copy.id)
        .fetch_one(&db.pool)
        .await
        .unwrap();
    assert_eq!(tag_count, 1, "tags are carried onto the copy");
}

/// The Phase 3 slug-TOCTOU fix: two duplicates of the same source race for the
/// base slug. The savepoint retry makes the unique index the arbiter, so both
/// succeed with distinct slugs rather than one 500-ing on a raw 23505.
#[tokio::test]
async fn concurrent_duplicates_get_distinct_slugs() {
    let db = TestDb::new().await;
    let owner = seed_user(&db.pool, "blk_dupr_owner").await;
    let project = seed_project(&db.pool, owner, "blk-dupr").await;
    let source = seed_item(&db.pool, project, "racey", 0).await;

    let p1 = db.pool.clone();
    let p2 = db.pool.clone();
    let (a, b) = tokio::join!(
        tokio::spawn(async move { items::duplicate_item(&p1, source, owner).await }),
        tokio::spawn(async move { items::duplicate_item(&p2, source, owner).await }),
    );
    let a = a.unwrap().expect("first duplicate succeeds");
    let b = b
        .unwrap()
        .expect("second duplicate succeeds under contention");

    let slug_a: String = sqlx::query_scalar("SELECT slug FROM items WHERE id = $1")
        .bind(a.id)
        .fetch_one(&db.pool)
        .await
        .unwrap();
    let slug_b: String = sqlx::query_scalar("SELECT slug FROM items WHERE id = $1")
        .bind(b.id)
        .fetch_one(&db.pool)
        .await
        .unwrap();
    assert_ne!(
        slug_a, slug_b,
        "concurrent duplicates must land on distinct slugs"
    );
    assert_ne!(a.id, b.id);
}