//! 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, "moderator")
        .await
        .unwrap();

    // Second call with same role should succeed
    mt_db::mutations::ensure_membership_with_role(&h.db, user, comm_id, "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>", true)
        .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");
}

// ============================================================================
// 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>", true,
    )
    .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 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>", true,
    )
    .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>", true,
    )
    .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>", true,
    )
    .await
    .unwrap();

    // Insert mentions
    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"));
}