//! Direct tests for mt_db::mutations functions.
//!
//! These tests exercise database mutation functions that lack integration
//! test coverage through route-level tests.

use crate::harness::TestHarness;
use mt_core::types::BanType;
use uuid::Uuid;

// Ban cleanup

#[tokio::test]
async fn cleanup_expired_bans_removes_expired() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let user1 = Uuid::new_v4();
    let user2 = Uuid::new_v4();
    let admin = Uuid::new_v4();

    for (id, name) in [(user1, "user1"), (user2, "user2"), (admin, "admin")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    // Create expired ban
    sqlx::query(
        "INSERT INTO community_bans (community_id, user_id, banned_by, ban_type, expires_at)
         VALUES ($1, $2, $3, 'ban', now() - interval '1 day')",
    )
    .bind(comm_id)
    .bind(user1)
    .bind(admin)
    .execute(&h.db)
    .await
    .unwrap();

    // Create active ban
    sqlx::query(
        "INSERT INTO community_bans (community_id, user_id, banned_by, ban_type, expires_at)
         VALUES ($1, $2, $3, 'ban', now() + interval '1 day')",
    )
    .bind(comm_id)
    .bind(user2)
    .bind(admin)
    .execute(&h.db)
    .await
    .unwrap();

    let cleaned = mt_db::mutations::cleanup_expired_bans(&h.db, comm_id)
        .await
        .unwrap();
    assert_eq!(cleaned, 1, "Should remove 1 expired ban");

    let remaining: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM community_bans WHERE community_id = $1")
            .bind(comm_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(remaining, 1, "Should keep 1 active ban");
}

#[tokio::test]
async fn cleanup_expired_bans_keeps_permanent() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let user = Uuid::new_v4();
    let admin = Uuid::new_v4();

    for (id, name) in [(user, "user"), (admin, "admin")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    // Create permanent ban (no expires_at)
    mt_db::mutations::create_community_ban(
        &h.db,
        comm_id,
        user,
        admin,
        BanType::Ban,
        Some("permanent"),
        None,
    )
    .await
    .unwrap();

    let cleaned = mt_db::mutations::cleanup_expired_bans(&h.db, comm_id)
        .await
        .unwrap();
    assert_eq!(cleaned, 0, "Should not remove permanent bans");
}

#[tokio::test]
async fn create_community_ban_upserts_on_conflict() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let user = Uuid::new_v4();
    let admin = Uuid::new_v4();

    for (id, name) in [(user, "user"), (admin, "admin")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    // First ban
    let id1 = mt_db::mutations::create_community_ban(
        &h.db,
        comm_id,
        user,
        admin,
        BanType::Ban,
        Some("first reason"),
        None,
    )
    .await
    .unwrap();

    // Second ban (same user+type) should upsert, not duplicate
    let id2 = mt_db::mutations::create_community_ban(
        &h.db,
        comm_id,
        user,
        admin,
        BanType::Ban,
        Some("updated reason"),
        None,
    )
    .await
    .unwrap();

    assert_eq!(id1, id2, "Upsert should return same ban ID");

    // Verify reason updated
    let reason: Option<String> =
        sqlx::query_scalar("SELECT reason FROM community_bans WHERE id = $1")
            .bind(id1)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(reason.as_deref(), Some("updated reason"));
}

// Category mutations

#[tokio::test]
async fn swap_category_order_atomic() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let cat_a = mt_db::mutations::create_category(&h.db, comm_id, "Alpha", "alpha", None, 0)
        .await
        .unwrap();
    let cat_b = mt_db::mutations::create_category(&h.db, comm_id, "Beta", "beta", None, 1)
        .await
        .unwrap();

    mt_db::mutations::swap_category_order(&h.db, cat_a, 0, cat_b, 1)
        .await
        .unwrap();

    let order_a: i32 = sqlx::query_scalar("SELECT sort_order FROM categories WHERE id = $1")
        .bind(cat_a)
        .fetch_one(&h.db)
        .await
        .unwrap();
    let order_b: i32 = sqlx::query_scalar("SELECT sort_order FROM categories WHERE id = $1")
        .bind(cat_b)
        .fetch_one(&h.db)
        .await
        .unwrap();

    assert_eq!(order_a, 1, "Alpha should now have order 1");
    assert_eq!(order_b, 0, "Beta should now have order 0");
}

#[tokio::test]
async fn get_category_id_by_slugs_found() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let found = mt_db::mutations::get_category_id_by_slugs(&h.db, "test", "general")
        .await
        .unwrap();
    assert_eq!(found, Some(cat_id));
}

#[tokio::test]
async fn get_category_id_by_slugs_not_found() {
    let h = TestHarness::new().await;
    let _comm_id = h.create_community("Test", "test").await;

    let found = mt_db::mutations::get_category_id_by_slugs(&h.db, "test", "nonexistent")
        .await
        .unwrap();
    assert_eq!(found, None);

    let found = mt_db::mutations::get_category_id_by_slugs(&h.db, "nosuchcommunity", "general")
        .await
        .unwrap();
    assert_eq!(found, None);
}

#[tokio::test]
async fn update_category_updates_fields() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = mt_db::mutations::create_category(
        &h.db,
        comm_id,
        "Old Name",
        "oldslug",
        Some("Old desc"),
        0,
    )
    .await
    .unwrap();

    let updated =
        mt_db::mutations::update_category(&h.db, cat_id, comm_id, "New Name", Some("New desc"))
            .await
            .unwrap();
    assert!(updated);

    let (name, desc): (String, Option<String>) =
        sqlx::query_as("SELECT name, description FROM categories WHERE id = $1")
            .bind(cat_id)
            .fetch_one(&h.db)
            .await
            .unwrap();

    assert_eq!(name, "New Name");
    assert_eq!(desc.as_deref(), Some("New desc"));
}

// Membership mutations

#[tokio::test]
async fn ensure_membership_idempotent() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let user = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
        .bind(user)
        .bind("testuser")
        .execute(&h.db)
        .await
        .unwrap();

    // First call creates membership
    mt_db::mutations::ensure_membership(&h.db, user, comm_id)
        .await
        .unwrap();

    // Second call should succeed (ON CONFLICT DO NOTHING)
    mt_db::mutations::ensure_membership(&h.db, user, comm_id)
        .await
        .unwrap();

    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM memberships WHERE user_id = $1 AND community_id = $2",
    )
    .bind(user)
    .bind(comm_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(count, 1, "Should have exactly one membership row");
}

#[tokio::test]
async fn ensure_membership_with_role_idempotent() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let user = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
        .bind(user)
        .bind("roleuser")
        .execute(&h.db)
        .await
        .unwrap();

    mt_db::mutations::ensure_membership_with_role(
        &h.db,
        user,
        comm_id,
        mt_core::types::CommunityRole::Moderator,
    )
    .await
    .unwrap();

    // Second call with same role should succeed
    mt_db::mutations::ensure_membership_with_role(
        &h.db,
        user,
        comm_id,
        mt_core::types::CommunityRole::Moderator,
    )
    .await
    .unwrap();

    // Verify role is preserved (DO NOTHING means first write wins)
    let role: String =
        sqlx::query_scalar("SELECT role FROM memberships WHERE user_id = $1 AND community_id = $2")
            .bind(user)
            .bind(comm_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(role, "moderator");
}

// Thread mutations

#[tokio::test]
async fn soft_delete_sets_deleted_at() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
        .bind(author)
        .bind("author")
        .execute(&h.db)
        .await
        .unwrap();

    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Delete Me")
        .await
        .unwrap();

    // Verify not deleted
    let deleted: bool =
        sqlx::query_scalar("SELECT deleted_at IS NOT NULL FROM threads WHERE id = $1")
            .bind(thread_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert!(!deleted, "Should not be deleted initially");

    mt_db::mutations::soft_delete_thread(&h.db, thread_id)
        .await
        .unwrap();

    let deleted: bool =
        sqlx::query_scalar("SELECT deleted_at IS NOT NULL FROM threads WHERE id = $1")
            .bind(thread_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert!(deleted, "Should be soft-deleted");
}

#[tokio::test]
async fn create_post_bumps_thread_activity() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
        .bind(author)
        .bind("author")
        .execute(&h.db)
        .await
        .unwrap();

    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Activity Test")
        .await
        .unwrap();

    // Set activity to a known old time
    sqlx::query("UPDATE threads SET last_activity_at = '2000-01-01'::timestamptz WHERE id = $1")
        .bind(thread_id)
        .execute(&h.db)
        .await
        .unwrap();

    // Create post should bump last_activity_at
    mt_db::mutations::create_post(&h.db, thread_id, author, "test", "<p>test</p>")
        .await
        .unwrap();

    let recent: bool = sqlx::query_scalar(
        "SELECT last_activity_at > '2020-01-01'::timestamptz FROM threads WHERE id = $1",
    )
    .bind(thread_id)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert!(recent, "last_activity_at should be updated to recent time");
}

/// Regression (fuzz-2026-07-06 TOCTOU): a reply insert is conditional on the
/// thread still being live and unlocked, in the same statement, so a lock or
/// soft-delete that races the handler's snapshot check can't let the reply
/// commit (nor let the post_count trigger increment a dead thread). Exercised at
/// the DB layer, where the guard lives.
#[tokio::test]
async fn reply_insert_blocked_on_locked_or_deleted_thread() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, 'toctouauthor')")
        .bind(author)
        .execute(&h.db)
        .await
        .unwrap();
    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "TOCTOU")
        .await
        .unwrap();

    // Locked → no insert.
    sqlx::query("UPDATE threads SET locked = true WHERE id = $1")
        .bind(thread_id)
        .execute(&h.db)
        .await
        .unwrap();
    assert!(
        matches!(
            mt_db::mutations::create_post(&h.db, thread_id, author, "r", "<p>r</p>").await,
            Err(sqlx::Error::RowNotFound)
        ),
        "a reply must not insert on a locked thread"
    );

    // Unlocked but soft-deleted → still no insert.
    sqlx::query("UPDATE threads SET locked = false, deleted_at = now() WHERE id = $1")
        .bind(thread_id)
        .execute(&h.db)
        .await
        .unwrap();
    assert!(
        matches!(
            mt_db::mutations::create_post(&h.db, thread_id, author, "r", "<p>r</p>").await,
            Err(sqlx::Error::RowNotFound)
        ),
        "a reply must not insert on a soft-deleted thread"
    );

    // Restored to live → the reply lands, proving the guard only blocks the bad states.
    sqlx::query("UPDATE threads SET deleted_at = NULL WHERE id = $1")
        .bind(thread_id)
        .execute(&h.db)
        .await
        .unwrap();
    assert!(
        mt_db::mutations::create_post(&h.db, thread_id, author, "r", "<p>r</p>")
            .await
            .is_ok(),
        "a live, unlocked thread must still accept the reply"
    );

    // The post_count trigger only counted the one successful insert.
    let post_count: i32 = sqlx::query_scalar("SELECT post_count FROM threads WHERE id = $1")
        .bind(thread_id)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(
        post_count, 1,
        "only the successful reply may bump post_count"
    );
}

// Endorsement mutations

#[tokio::test]
async fn toggle_endorsement_db_roundtrip() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    let endorser = Uuid::new_v4();
    for (id, name) in [(author, "author"), (endorser, "endorser")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
        .await
        .unwrap();
    let post_id =
        mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
            .await
            .unwrap();

    // First toggle: endorse
    let result = mt_db::mutations::toggle_endorsement(&h.db, post_id, endorser)
        .await
        .unwrap();
    assert!(result, "First toggle should endorse (return true)");

    // Second toggle: un-endorse
    let result = mt_db::mutations::toggle_endorsement(&h.db, post_id, endorser)
        .await
        .unwrap();
    assert!(!result, "Second toggle should un-endorse (return false)");

    // Third toggle: endorse again
    let result = mt_db::mutations::toggle_endorsement(&h.db, post_id, endorser)
        .await
        .unwrap();
    assert!(result, "Third toggle should endorse again (return true)");
}

// Flag mutations

#[tokio::test]
async fn create_post_external_ref_is_idempotent() {
    // The internal reply path dedups on external_ref: a retried/replayed call
    // returns the existing post and never inserts a duplicate (audit E1).
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
        .bind(author)
        .bind("author")
        .execute(&h.db)
        .await
        .unwrap();

    let (thread_id, _op) =
        mt_db::mutations::create_thread_with_op(&h.db, cat_id, author, "T", "op", "<p>op</p>")
            .await
            .unwrap();

    let count = || async {
        let n: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM posts WHERE thread_id = $1")
            .bind(thread_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
        n.0
    };
    let activity = || async {
        let a: (chrono::DateTime<chrono::Utc>,) =
            sqlx::query_as("SELECT last_activity_at FROM threads WHERE id = $1")
                .bind(thread_id)
                .fetch_one(&h.db)
                .await
                .unwrap();
        a.0
    };

    // First call: fresh reply, created = true.
    let (id1, created1) = mt_db::mutations::create_post_external_ref(
        &h.db,
        thread_id,
        author,
        "mnw:post:msg-1",
        "reply",
        "<p>reply</p>",
    )
    .await
    .unwrap();
    assert!(created1, "first call creates the reply");
    assert_eq!(count().await, 2, "OP + one reply");
    let count_for_trigger: (i32,) = sqlx::query_as("SELECT post_count FROM threads WHERE id = $1")
        .bind(thread_id)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(
        count_for_trigger.0, 2,
        "m029 trigger counted the reply once"
    );
    let activity_after_first = activity().await;

    // Replay with the same external_ref: same post id, created = false, no dup,
    // and last_activity_at is NOT re-bumped.
    let (id2, created2) = mt_db::mutations::create_post_external_ref(
        &h.db,
        thread_id,
        author,
        "mnw:post:msg-1",
        "reply",
        "<p>reply</p>",
    )
    .await
    .unwrap();
    assert_eq!(id1, id2, "replay returns the original post id");
    assert!(!created2, "replay does not create");
    assert_eq!(count().await, 2, "no duplicate reply inserted");
    assert_eq!(
        activity().await,
        activity_after_first,
        "replay does not re-bump activity"
    );

    // A distinct external_ref does insert a new reply.
    let (id3, created3) = mt_db::mutations::create_post_external_ref(
        &h.db,
        thread_id,
        author,
        "mnw:post:msg-2",
        "reply2",
        "<p>reply2</p>",
    )
    .await
    .unwrap();
    assert_ne!(id3, id1);
    assert!(created3);
    assert_eq!(count().await, 3, "distinct ref adds a reply");
}

#[tokio::test]
async fn insert_flag_idempotent_per_user() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    let flagger = Uuid::new_v4();
    for (id, name) in [(author, "author"), (flagger, "flagger")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
        .await
        .unwrap();
    let post_id =
        mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
            .await
            .unwrap();

    // First flag
    mt_db::mutations::insert_flag(&h.db, post_id, flagger, "spam", None)
        .await
        .unwrap();

    // Second flag from same user, ON CONFLICT DO NOTHING
    mt_db::mutations::insert_flag(&h.db, post_id, flagger, "off_topic", Some("detail"))
        .await
        .unwrap();

    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM post_flags WHERE post_id = $1 AND flagger_id = $2",
    )
    .bind(post_id)
    .bind(flagger)
    .fetch_one(&h.db)
    .await
    .unwrap();
    assert_eq!(count, 1, "Should have exactly 1 flag per user per post");
}

// Image mutations

#[tokio::test]
async fn remove_image_marks_removed() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let uploader = Uuid::new_v4();
    let moderator = Uuid::new_v4();
    for (id, name) in [(uploader, "uploader"), (moderator, "moderator")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    let image_id = mt_db::mutations::insert_image(
        &h.db,
        uploader,
        comm_id,
        "s3/key.jpg",
        "photo.jpg",
        "image/jpeg",
        12345,
    )
    .await
    .unwrap();

    // Verify not removed
    let removed: bool =
        sqlx::query_scalar("SELECT removed_at IS NOT NULL FROM images WHERE id = $1")
            .bind(image_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert!(!removed, "Should not be removed initially");

    mt_db::mutations::remove_image(&h.db, image_id, moderator)
        .await
        .unwrap();

    let (removed, removed_by): (bool, Option<Uuid>) =
        sqlx::query_as("SELECT removed_at IS NOT NULL, removed_by FROM images WHERE id = $1")
            .bind(image_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert!(removed, "Should be marked as removed");
    assert_eq!(
        removed_by,
        Some(moderator),
        "removed_by should match moderator"
    );
}

#[tokio::test]
async fn remove_image_idempotent() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let uploader = Uuid::new_v4();
    let mod1 = Uuid::new_v4();
    let mod2 = Uuid::new_v4();
    for (id, name) in [(uploader, "uploader"), (mod1, "mod1"), (mod2, "mod2")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    let image_id = mt_db::mutations::insert_image(
        &h.db,
        uploader,
        comm_id,
        "s3/key.jpg",
        "photo.jpg",
        "image/jpeg",
        100,
    )
    .await
    .unwrap();

    // First remove
    mt_db::mutations::remove_image(&h.db, image_id, mod1)
        .await
        .unwrap();

    // Second remove (different mod), should be no-op (WHERE removed_at IS NULL)
    mt_db::mutations::remove_image(&h.db, image_id, mod2)
        .await
        .unwrap();

    // Verify original remover preserved
    let removed_by: Option<Uuid> =
        sqlx::query_scalar("SELECT removed_by FROM images WHERE id = $1")
            .bind(image_id)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(removed_by, Some(mod1), "First remover should be preserved");
}

// Link preview mutations

#[tokio::test]
async fn insert_link_preview_dedup() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
        .bind(author)
        .bind("author")
        .execute(&h.db)
        .await
        .unwrap();

    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
        .await
        .unwrap();
    let post_id =
        mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
            .await
            .unwrap();

    mt_db::mutations::insert_link_preview(
        &h.db,
        post_id,
        "https://example.com",
        Some("Example"),
        Some("A site"),
    )
    .await
    .unwrap();

    // Duplicate, ON CONFLICT DO NOTHING
    mt_db::mutations::insert_link_preview(
        &h.db,
        post_id,
        "https://example.com",
        Some("Different Title"),
        None,
    )
    .await
    .unwrap();

    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM link_previews WHERE post_id = $1")
        .bind(post_id)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(
        count, 1,
        "Should have exactly 1 link preview per URL per post"
    );
}

// Mention mutations

#[tokio::test]
async fn insert_mentions_dedup() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;

    let author = Uuid::new_v4();
    let mentioned = Uuid::new_v4();
    for (id, name) in [(author, "author"), (mentioned, "mentioned")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    let thread_id = mt_db::mutations::create_thread(&h.db, cat_id, author, "Test")
        .await
        .unwrap();
    let post_id =
        mt_db::mutations::create_post(&h.db, thread_id, author, "content", "<p>content</p>")
            .await
            .unwrap();

    mt_db::mutations::insert_mentions(&h.db, post_id, &[mentioned])
        .await
        .unwrap();

    // Insert same mention again, ON CONFLICT DO NOTHING
    mt_db::mutations::insert_mentions(&h.db, post_id, &[mentioned])
        .await
        .unwrap();

    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM post_mentions WHERE post_id = $1")
        .bind(post_id)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(
        count, 1,
        "Should have exactly 1 mention row per user per post"
    );
}

// Upsert user

#[tokio::test]
async fn upsert_user_updates_on_conflict() {
    let h = TestHarness::new().await;
    let user_id = Uuid::new_v4();

    mt_db::mutations::upsert_user(&h.db, user_id, "oldname", Some("Old Display"))
        .await
        .unwrap();

    // Upsert with new values
    mt_db::mutations::upsert_user(&h.db, user_id, "newname", Some("New Display"))
        .await
        .unwrap();

    let (username, display_name): (String, Option<String>) =
        sqlx::query_as("SELECT username, display_name FROM users WHERE mnw_account_id = $1")
            .bind(user_id)
            .fetch_one(&h.db)
            .await
            .unwrap();

    assert_eq!(username, "newname");
    assert_eq!(display_name.as_deref(), Some("New Display"));
}

#[tokio::test]
async fn s3_purge_sweep_targets_only_removed_unpurged_images() {
    let h = TestHarness::new().await;
    let comm_id = h.create_community("Test", "test").await;

    let uploader = Uuid::new_v4();
    let moderator = Uuid::new_v4();
    for (id, name) in [(uploader, "purgeuploader"), (moderator, "purgemod")] {
        sqlx::query("INSERT INTO users (mnw_account_id, username) VALUES ($1, $2)")
            .bind(id)
            .bind(name)
            .execute(&h.db)
            .await
            .unwrap();
    }

    // A live image and a removed image.
    let live = mt_db::mutations::insert_image(
        &h.db,
        uploader,
        comm_id,
        "s3/live.jpg",
        "live.jpg",
        "image/jpeg",
        100,
    )
    .await
    .unwrap();
    let removed = mt_db::mutations::insert_image(
        &h.db,
        uploader,
        comm_id,
        "s3/removed.jpg",
        "removed.jpg",
        "image/jpeg",
        100,
    )
    .await
    .unwrap();
    mt_db::mutations::remove_image(&h.db, removed, moderator)
        .await
        .unwrap();

    // Only the removed-and-unpurged image is pending purge.
    let pending = mt_db::queries::list_images_pending_s3_purge(&h.db, 100)
        .await
        .unwrap();
    let ids: Vec<Uuid> = pending.iter().map(|p| p.id).collect();
    assert!(
        ids.contains(&removed),
        "removed image must be pending purge"
    );
    assert!(!ids.contains(&live), "live image must not be pending purge");
    assert_eq!(
        pending
            .iter()
            .find(|p| p.id == removed)
            .map(|p| p.s3_key.as_str()),
        Some("s3/removed.jpg"),
        "pending row must carry the S3 key for deletion"
    );

    // After marking purged, it drops out of the sweep, convergent.
    mt_db::mutations::mark_images_s3_purged(&h.db, &[removed])
        .await
        .unwrap();
    let pending = mt_db::queries::list_images_pending_s3_purge(&h.db, 100)
        .await
        .unwrap();
    assert!(
        !pending.iter().any(|p| p.id == removed),
        "purged image must not be revisited by the sweep"
    );

    // Marking an empty batch is a no-op (sweep's failed-batch path).
    mt_db::mutations::mark_images_s3_purged(&h.db, &[])
        .await
        .unwrap();
}

// Username-collision reconciliation (ultra-fuzz S2)

/// A login that renames into a username a *stale* mirror row still holds must
/// succeed, not 23505-lockout. The stale row yields the name (MNW is the source
/// of truth; usernames are reusable there) and keeps its data under a
/// collision-proof `mnw_<account-id>` placeholder.
#[tokio::test]
async fn upsert_user_reclaims_username_from_stale_row() {
    let h = TestHarness::new().await;
    let old = Uuid::new_v4();
    let new = Uuid::new_v4();

    // `old` currently owns "shared".
    mt_db::mutations::upsert_user(&h.db, old, "shared", None)
        .await
        .unwrap();

    // `new` logs in having taken the name "shared" upstream. This must not error.
    mt_db::mutations::upsert_user(&h.db, new, "shared", None)
        .await
        .expect("rename into a name a stale row holds must not lock the user out");

    // `new` now owns "shared"; `old` was vacated to its placeholder.
    let owner: Uuid =
        sqlx::query_scalar("SELECT mnw_account_id FROM users WHERE username = 'shared'")
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(owner, new, "the current login must own the reused username");

    let old_name: String =
        sqlx::query_scalar("SELECT username FROM users WHERE mnw_account_id = $1")
            .bind(old)
            .fetch_one(&h.db)
            .await
            .unwrap();
    assert_eq!(
        old_name,
        format!("mnw_{old}"),
        "stale row keeps its data under a placeholder name"
    );
}

/// Re-upserting the same account with its own unchanged username is a no-op rename
/// (the `mnw_account_id <> $2` guard skips self), not a self-collision.
#[tokio::test]
async fn upsert_user_same_account_same_name_is_stable() {
    let h = TestHarness::new().await;
    let acct = Uuid::new_v4();
    mt_db::mutations::upsert_user(&h.db, acct, "stable", Some("Display"))
        .await
        .unwrap();
    mt_db::mutations::upsert_user(&h.db, acct, "stable", Some("Display Two"))
        .await
        .unwrap();

    let name: String = sqlx::query_scalar("SELECT username FROM users WHERE mnw_account_id = $1")
        .bind(acct)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert_eq!(name, "stable");
}

/// `posts.is_active` (migration 034) is the canonical live-post predicate. These
/// pin the two properties that make it worth having over a hand-copied
/// `removed_at IS NULL AND deleted_at IS NULL` pair: it tracks BOTH columns, and
/// it cannot be set independently of them.
#[tokio::test]
async fn posts_is_active_tracks_both_soft_delete_columns() {
    let mut h = TestHarness::new().await;
    let author_id = h.login_as("isactiveauthor").await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;
    h.add_membership(author_id, comm_id, "member").await;

    let thread_id = h
        .create_thread_with_post(cat_id, author_id, "Active", "Content")
        .await;
    let post_id = mt_db::queries::list_posts_in_thread(&h.db, thread_id)
        .await
        .unwrap()[0]
        .id;

    let active = |id: Uuid, db: sqlx::PgPool| async move {
        sqlx::query_scalar::<_, bool>("SELECT is_active FROM posts WHERE id = $1")
            .bind(id)
            .fetch_one(&db)
            .await
            .unwrap()
    };

    assert!(
        active(post_id, h.db.clone()).await,
        "a fresh post is active"
    );

    // The mod column alone flips it.
    sqlx::query("UPDATE posts SET removed_at = now() WHERE id = $1")
        .bind(post_id)
        .execute(&h.db)
        .await
        .unwrap();
    assert!(
        !active(post_id, h.db.clone()).await,
        "removed_at deactivates"
    );

    sqlx::query("UPDATE posts SET removed_at = NULL WHERE id = $1")
        .bind(post_id)
        .execute(&h.db)
        .await
        .unwrap();
    assert!(
        active(post_id, h.db.clone()).await,
        "clearing it reactivates"
    );

    // The author column alone flips it too. This is the half that drifted at
    // five call sites, because it is dormant and nothing catches a missing check.
    sqlx::query("UPDATE posts SET deleted_at = now() WHERE id = $1")
        .bind(post_id)
        .execute(&h.db)
        .await
        .unwrap();
    assert!(
        !active(post_id, h.db.clone()).await,
        "deleted_at must deactivate too, not just removed_at"
    );
}

#[tokio::test]
async fn posts_is_active_cannot_be_written_directly() {
    let mut h = TestHarness::new().await;
    let author_id = h.login_as("nowriteauthor").await;
    let comm_id = h.create_community("Test", "test").await;
    let cat_id = h.create_category(comm_id, "General", "general").await;
    h.add_membership(author_id, comm_id, "member").await;

    let thread_id = h
        .create_thread_with_post(cat_id, author_id, "NoWrite", "Content")
        .await;
    let post_id = mt_db::queries::list_posts_in_thread(&h.db, thread_id)
        .await
        .unwrap()[0]
        .id;

    sqlx::query("UPDATE posts SET removed_at = now() WHERE id = $1")
        .bind(post_id)
        .execute(&h.db)
        .await
        .unwrap();

    // Postgres refuses the write, so the column can never disagree with the two
    // it is derived from. This is the property a shared SQL fragment could not
    // have given us.
    let err = sqlx::query("UPDATE posts SET is_active = true WHERE id = $1")
        .bind(post_id)
        .execute(&h.db)
        .await
        .expect_err("a generated column must reject a direct write");
    assert!(
        err.to_string().contains("can only be updated to DEFAULT"),
        "expected Postgres to reject the write to a generated column, got: {err}"
    );

    let still_inactive: bool = sqlx::query_scalar("SELECT is_active FROM posts WHERE id = $1")
        .bind(post_id)
        .fetch_one(&h.db)
        .await
        .unwrap();
    assert!(!still_inactive, "the removed post must stay inactive");
}