use super::{CommunityState, PgPool, Utc, Uuid};

/// Create a new community and return its ID.
#[tracing::instrument(skip_all)]
pub async fn create_community(
    pool: &PgPool,
    name: &str,
    slug: &str,
    description: Option<&str>,
) -> Result<Uuid, sqlx::Error> {
    sqlx::query_scalar!(
        "INSERT INTO communities (name, slug, description)
         VALUES ($1, $2, $3)
         RETURNING id",
        name,
        slug,
        description,
    )
    .fetch_one(pool)
    .await
}

/// Update a community's name and description.
#[tracing::instrument(skip_all)]
pub async fn update_community<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    name: &str,
    description: Option<&str>,
    auto_hide_threshold: Option<i32>,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE communities SET name = $2, description = $3, auto_hide_threshold = $4 WHERE id = $1",
        community_id,
        name,
        description,
        auto_hide_threshold,
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Suspend a community.
#[tracing::instrument(skip_all)]
pub async fn suspend_community<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    reason: Option<&str>,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE communities SET suspended_at = now(), suspension_reason = $2 WHERE id = $1",
        community_id,
        reason,
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Unsuspend a community.
#[tracing::instrument(skip_all)]
pub async fn unsuspend_community<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE communities SET suspended_at = NULL, suspension_reason = NULL WHERE id = $1",
        community_id,
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Set the community moderation state. See [`CommunityState`] for semantics.
#[tracing::instrument(skip_all)]
pub async fn set_community_state<'e, E: sqlx::PgExecutor<'e>>(
    executor: E,
    community_id: Uuid,
    state: CommunityState,
) -> Result<(), sqlx::Error> {
    sqlx::query!(
        "UPDATE communities SET state = $2 WHERE id = $1",
        community_id,
        state.as_str()
    )
    .execute(executor)
    .await?;
    Ok(())
}

/// Result of a clean-slate operation.
pub struct CleanSlateResult {
    /// Number of threads deleted (excluding the system reset thread that's
    /// then inserted). Useful for the success toast.
    pub deleted_thread_count: i64,
    /// ID of the system "Community reset" thread created in the first
    /// category. `None` if the community has no categories, clean-slate
    /// still deletes threads but has nowhere to post the notice.
    pub system_thread_id: Option<Uuid>,
}

/// Clean-slate a community: delete all threads (and the posts / footnotes /
/// endorsements / flags / read-positions that cascade from them) while
/// preserving the community row, categories, memberships, bans, mutes, and
/// tags. Posts a system thread "Community reset by &lt;actor&gt; on &lt;date&gt;"
/// in the first category by `sort_order`.
///
/// Authorization is the caller's responsibility (see `routes/admin.rs`); this
/// mutation only enforces atomicity.
#[tracing::instrument(skip_all)]
pub async fn clean_slate_community(
    conn: &mut sqlx::PgConnection,
    community_id: Uuid,
    actor_id: Uuid,
    actor_display: &str,
) -> Result<CleanSlateResult, sqlx::Error> {
    // Delete every thread whose category belongs to this community. Cascades
    // reap posts, footnotes, endorsements, flags, read-positions, link
    // previews, mentions, and tag joins.
    let deleted = sqlx::query_scalar!(
        r#"WITH d AS (
            DELETE FROM threads
             WHERE category_id IN (SELECT id FROM categories WHERE community_id = $1)
             RETURNING 1
         )
         SELECT COUNT(*) AS "count!" FROM d"#,
        community_id,
    )
    .fetch_one(&mut *conn)
    .await?;

    // Pick the first category by sort_order to host the reset notice. None
    // means the community has no categories, nothing to post into.
    let first_category = sqlx::query_scalar!(
        "SELECT id FROM categories
         WHERE community_id = $1
         ORDER BY sort_order
         LIMIT 1",
        community_id,
    )
    .fetch_optional(&mut *conn)
    .await?;

    let system_thread_id = if let Some(cat_id) = first_category {
        let now = Utc::now();
        let date = now.format("%Y-%m-%d").to_string();
        let title = format!("Community reset by {actor_display} on {date}");
        let body_md = format!(
            "This community was reset by **{actor_display}** on {date}. All previous threads have been cleared. Settings, categories, members, and bans are preserved.",
        );
        let body_html = format!(
            "<p>This community was reset by <strong>{}</strong> on {}. All previous threads have been cleared. Settings, categories, members, and bans are preserved.</p>",
            html_escape(actor_display),
            date,
        );

        let thread_id = sqlx::query_scalar!(
            "INSERT INTO threads (category_id, author_id, title, pinned, locked)
             VALUES ($1, $2, $3, TRUE, TRUE)
             RETURNING id",
            cat_id,
            actor_id,
            title,
        )
        .fetch_one(&mut *conn)
        .await?;

        sqlx::query!(
            "INSERT INTO posts (thread_id, author_id, body_markdown, body_html)
             VALUES ($1, $2, $3, $4)",
            thread_id,
            actor_id,
            body_md,
            body_html,
        )
        .execute(&mut *conn)
        .await?;

        Some(thread_id)
    } else {
        None
    };

    Ok(CleanSlateResult {
        deleted_thread_count: deleted,
        system_thread_id,
    })
}

/// Minimal HTML-escape for actor display names embedded in the reset notice.
/// We render the notice as a literal HTML string (skipping the markdown
/// pipeline) so we don't have to thread renderer config into the mutation.
fn html_escape(input: &str) -> String {
    input
        .replace('&', "&amp;")
        .replace('<', "&lt;")
        .replace('>', "&gt;")
        .replace('"', "&quot;")
        .replace('\'', "&#39;")
}