use super::{PgPool, Uuid};

/// Atomically create an externally-referenced thread and its opening post in
/// one transaction. Returns `(thread_id, op_post_id)`.
///
/// The atomicity matters most here: the MNW→MT internal path retries, and a
/// committed-but-post-less thread would make the retry collide with the UNIQUE
/// `external_ref` index and 500 while leaking an empty thread.
#[tracing::instrument(skip_all)]
pub async fn create_thread_with_op_external_ref(
    pool: &PgPool,
    category_id: Uuid,
    author_id: Uuid,
    title: &str,
    external_ref: &str,
    body_markdown: &str,
    body_html: &str,
) -> Result<(Uuid, Uuid), sqlx::Error> {
    let mut tx = pool.begin().await?;

    let thread_id = sqlx::query_scalar!(
        "INSERT INTO threads (category_id, author_id, title, external_ref)
         VALUES ($1, $2, $3, $4)
         RETURNING id",
        category_id,
        author_id,
        title,
        external_ref,
    )
    .fetch_one(&mut *tx)
    .await?;

    let post_id = sqlx::query_scalar!(
        "INSERT INTO posts (thread_id, author_id, body_markdown, body_html)
         VALUES ($1, $2, $3, $4)
         RETURNING id",
        thread_id,
        author_id,
        body_markdown,
        body_html,
    )
    .fetch_one(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok((thread_id, post_id))
}

/// Low-level: insert a bare thread row (no opening post). Returns the thread ID.
///
/// Handlers must NOT pair this with a separate `create_post` for the OP, that
/// non-atomic sequence can leak a post-less thread. Use [`create_thread_with_op`]
/// for the real "start a thread" operation. This primitive exists for tests and
/// data construction that build posts explicitly.
#[tracing::instrument(skip_all)]
pub async fn create_thread(
    pool: &PgPool,
    category_id: Uuid,
    author_id: Uuid,
    title: &str,
) -> Result<Uuid, sqlx::Error> {
    sqlx::query_scalar!(
        "INSERT INTO threads (category_id, author_id, title)
         VALUES ($1, $2, $3)
         RETURNING id",
        category_id,
        author_id,
        title,
    )
    .fetch_one(pool)
    .await
}

/// Atomically create a thread and its opening post in one transaction.
///
/// Returns `(thread_id, op_post_id)`. Creating the thread and OP separately
/// could leave a titled, post-less thread if the second insert failed; this
/// makes that state unreachable. `threads.last_activity_at` defaults to now().
#[tracing::instrument(skip_all)]
pub async fn create_thread_with_op(
    pool: &PgPool,
    category_id: Uuid,
    author_id: Uuid,
    title: &str,
    body_markdown: &str,
    body_html: &str,
) -> Result<(Uuid, Uuid), sqlx::Error> {
    let mut tx = pool.begin().await?;
    let ids = create_thread_with_op_tx(
        &mut tx,
        category_id,
        author_id,
        title,
        body_markdown,
        body_html,
    )
    .await?;
    tx.commit().await?;
    Ok(ids)
}

/// Create a thread and its opening post on the caller's transaction.
///
/// Lets a handler fold the thread, its OP, its mentions, and its tags into one
/// atomic unit so a mid-sequence failure can't leave a thread whose @mentions or
/// tags silently vanished (ultra-fuzz M-St1). The pool-taking
/// [`create_thread_with_op`] wraps this in its own transaction.
#[tracing::instrument(skip_all)]
pub async fn create_thread_with_op_tx(
    conn: &mut sqlx::PgConnection,
    category_id: Uuid,
    author_id: Uuid,
    title: &str,
    body_markdown: &str,
    body_html: &str,
) -> Result<(Uuid, Uuid), sqlx::Error> {
    let thread_id = sqlx::query_scalar!(
        "INSERT INTO threads (category_id, author_id, title)
         VALUES ($1, $2, $3)
         RETURNING id",
        category_id,
        author_id,
        title,
    )
    .fetch_one(&mut *conn)
    .await?;

    let post_id = sqlx::query_scalar!(
        "INSERT INTO posts (thread_id, author_id, body_markdown, body_html)
         VALUES ($1, $2, $3, $4)
         RETURNING id",
        thread_id,
        author_id,
        body_markdown,
        body_html,
    )
    .fetch_one(&mut *conn)
    .await?;

    Ok((thread_id, post_id))
}

/// Atomically auto-hide a post if pending flag count meets the threshold.
/// Combines count check and removal in a single query to avoid race conditions.
/// Returns true if the post was actually removed.
/// Sets removed_by to NULL (system action), the mod log records the event.
#[tracing::instrument(skip_all)]
pub async fn auto_hide_if_threshold_met<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    post_id: Uuid,
    threshold: i32,
) -> Result<bool, sqlx::Error> {
    let result = sqlx::query!(
        "UPDATE posts SET removed_by = NULL, removed_at = now()
         WHERE id = $1 AND removed_at IS NULL
         AND (SELECT COUNT(*) FROM post_flags WHERE post_id = $1 AND resolved_at IS NULL) >= $2",
        post_id,
        threshold as i64,
    )
    .execute(executor)
    .await?;
    Ok(result.rows_affected() > 0)
}

/// Update a thread's title.
#[tracing::instrument(skip_all)]
pub async fn update_thread_title(
    pool: &PgPool,
    thread_id: Uuid,
    title: &str,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE threads SET title = $2 WHERE id = $1",
        thread_id,
        title
    )
    .execute(pool)
    .await?;
    Ok(())
}

/// Soft-delete a thread: set deleted_at (hides from listings).
#[tracing::instrument(skip_all)]
pub async fn soft_delete_thread<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    thread_id: Uuid,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE threads SET deleted_at = now() WHERE id = $1",
        thread_id
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Set or unset the pinned flag on a thread.
#[tracing::instrument(skip_all)]
pub async fn set_thread_pinned<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    thread_id: Uuid,
    pinned: bool,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE threads SET pinned = $2 WHERE id = $1",
        thread_id,
        pinned
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Set or unset the locked flag on a thread.
#[tracing::instrument(skip_all)]
pub async fn set_thread_locked<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    thread_id: Uuid,
    locked: bool,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE threads SET locked = $2 WHERE id = $1",
        thread_id,
        locked
    )
    .execute(executor)
    .await?;
    Ok(())
}