use super::{PgPool, Uuid};

/// Insert a reply and bump the thread's last_activity_at atomically.
///
/// Opening posts are created with the thread via [`create_thread_with_op`];
/// this is for replies only. The display reply count derives from the
/// denormalized `threads.post_count`, which the m029 trigger maintains on every
/// post INSERT/DELETE/removed_at change, no counter to update by hand here.
#[tracing::instrument(skip_all)]
pub async fn create_post(
    pool: &PgPool,
    thread_id: Uuid,
    author_id: Uuid,
    body_markdown: &str,
    body_html: &str,
) -> Result<Uuid, sqlx::Error> {
    let mut tx = pool.begin().await?;
    let post_id = create_post_tx(&mut tx, thread_id, author_id, body_markdown, body_html)
        .await?
        // The pool-taking wrapper is for callers that have already established the
        // thread is live (tests, simple internal paths); a locked/deleted thread
        // here is a "row not inserted" error rather than a silent no-op.
        .ok_or(sqlx::Error::RowNotFound)?;
    tx.commit().await?;
    Ok(post_id)
}

/// Insert a reply and bump `last_activity_at` on the caller's transaction.
///
/// Lets a handler fold the reply *and its mentions* into one atomic unit so a
/// mid-sequence failure can't leave a committed reply whose @mentions silently
/// vanished (ultra-fuzz M-St1). The pool-taking [`create_post`] wraps this in
/// its own transaction for the simple, mention-free callers (and tests).
#[tracing::instrument(skip_all)]
pub async fn create_post_tx(
    conn: &mut sqlx::PgConnection,
    thread_id: Uuid,
    author_id: Uuid,
    body_markdown: &str,
    body_html: &str,
) -> Result<Option<Uuid>, sqlx::Error> {
    // Insert only while the thread is still live and unlocked, in the same
    // statement that reads that state, closing the TOCTOU between a handler's
    // snapshot-time `locked`/`deleted_at` check and this insert. Without the
    // guard a concurrent lock or soft-delete in the gap would let the reply
    // commit (and the m029 post_count trigger increment a deleted thread). None
    // = the thread was locked/deleted meanwhile; the caller surfaces it. This
    // mirrors the conditional-write pattern `mod_remove_post_cascade` uses.
    let post_id = sqlx::query_scalar!(
        "INSERT INTO posts (thread_id, author_id, body_markdown, body_html)
         SELECT $1, $2, $3, $4
         WHERE EXISTS (
             SELECT 1 FROM threads
             WHERE id = $1 AND deleted_at IS NULL AND locked = false
         )
         RETURNING id",
        thread_id,
        author_id,
        body_markdown,
        body_html,
    )
    .fetch_optional(&mut *conn)
    .await?;

    // Only bump activity if the reply actually landed.
    if post_id.is_some() {
        sqlx::query!(
            "UPDATE threads SET last_activity_at = now() WHERE id = $1",
            thread_id
        )
        .execute(&mut *conn)
        .await?;
    }

    Ok(post_id)
}

/// Idempotent reply insert keyed on `external_ref`, for the internal API.
///
/// Mirrors [`create_thread_with_op_external_ref`]: a retried or replayed
/// server→server reply carrying the same `external_ref` inserts at most once.
/// `ON CONFLICT` on the partial unique index (m030) makes the repeat a no-op,
/// so the m029 post_count trigger fires exactly once and `last_activity_at` is
/// bumped only on the fresh insert. Returns `(post_id, created)`, `created` is
/// false when an existing post was returned. User-facing replies use the plain
/// [`create_post`] (no ref); this path is internal-only.
#[tracing::instrument(skip_all)]
pub async fn create_post_external_ref(
    pool: &PgPool,
    thread_id: Uuid,
    author_id: Uuid,
    external_ref: &str,
    body_markdown: &str,
    body_html: &str,
) -> Result<(Uuid, bool), sqlx::Error> {
    let mut tx = pool.begin().await?;

    let inserted = sqlx::query_scalar!(
        "INSERT INTO posts (thread_id, author_id, body_markdown, body_html, external_ref)
         VALUES ($1, $2, $3, $4, $5)
         ON CONFLICT (external_ref) WHERE external_ref IS NOT NULL DO NOTHING
         RETURNING id",
        thread_id,
        author_id,
        body_markdown,
        body_html,
        external_ref,
    )
    .fetch_optional(&mut *tx)
    .await?;

    let result = match inserted {
        Some(id) => {
            // Fresh reply: bump thread activity (the trigger already counted it).
            sqlx::query!(
                "UPDATE threads SET last_activity_at = now() WHERE id = $1",
                thread_id
            )
            .execute(&mut *tx)
            .await?;
            (id, true)
        }
        None => {
            // Replay: the reply already exists. Return its id, don't re-bump.
            let existing =
                sqlx::query_scalar!("SELECT id FROM posts WHERE external_ref = $1", external_ref,)
                    .fetch_one(&mut *tx)
                    .await?;
            (existing, false)
        }
    };

    tx.commit().await?;
    Ok(result)
}

/// Test-only: mod-remove a single post without the OP-cascade.
///
/// Production code must use [`mod_remove_post_cascade`], which also soft-deletes
/// the thread when the post is its opening post, removing an OP without the
/// cascade leaves a headless, still-repliable thread. This non-cascade variant
/// exists solely so the integration suite can stage a removed post directly; it
/// is gated behind the `test-support` feature so handler code cannot reach it.
#[cfg(feature = "test-support")]
#[tracing::instrument(skip_all)]
pub async fn mod_remove_post(
    pool: &PgPool,
    post_id: Uuid,
    removed_by_id: Uuid,
) -> Result<bool, sqlx::Error> {
    let result = sqlx::query!(
        "UPDATE posts SET removed_by = $2, removed_at = now()
         WHERE id = $1 AND removed_at IS NULL",
        post_id,
        removed_by_id,
    )
    .execute(pool)
    .await?;
    Ok(result.rows_affected() > 0)
}

/// Outcome of [`mod_remove_post_cascade`].
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct PostRemoval {
    /// The post was removed by this call (false if it was already removed).
    pub post_removed: bool,
    /// The post was the thread's opening post, so the whole thread was
    /// soft-deleted as well.
    pub thread_removed: bool,
}

/// Mod-remove a post and, if it is the thread's opening post, soft-delete the
/// whole thread in the same transaction.
///
/// The opening post is the earliest post in the thread (by `created_at`, `id`
/// as tiebreak), counted regardless of removal state so the identity is stable.
/// Removing an OP without this cascade would leave a headless, still-repliable
/// thread; cascading deletes it so listings, the thread view, and the reply
/// path all treat it as gone. Returns false flags when the post was already
/// removed or was not the OP, callers can log a thread-deletion accordingly.
#[tracing::instrument(skip_all)]
pub async fn mod_remove_post_cascade(
    conn: &mut sqlx::PgConnection,
    post_id: Uuid,
    removed_by_id: Uuid,
) -> Result<PostRemoval, sqlx::Error> {
    let post_removed = sqlx::query!(
        "UPDATE posts SET removed_by = $2, removed_at = now()
         WHERE id = $1 AND removed_at IS NULL",
        post_id,
        removed_by_id,
    )
    .execute(&mut *conn)
    .await?
    .rows_affected()
        > 0;

    let mut thread_removed = false;
    if post_removed {
        // Soft-delete the thread only when this post is its opening post.
        thread_removed = sqlx::query!(
            "UPDATE threads SET deleted_at = now()
             WHERE deleted_at IS NULL
               AND id = (SELECT thread_id FROM posts WHERE id = $1)
               AND $1 = (
                   SELECT id FROM posts
                   WHERE thread_id = (SELECT thread_id FROM posts WHERE id = $1)
                   ORDER BY created_at ASC, id ASC
                   LIMIT 1
               )",
            post_id,
        )
        .execute(&mut *conn)
        .await?
        .rows_affected()
            > 0;
    }

    Ok(PostRemoval {
        post_removed,
        thread_removed,
    })
}