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

#[derive(sqlx::FromRow)]
pub struct PostWithAuthor {
    pub id: Uuid,
    pub author_id: Uuid,
    pub author_name: String,
    pub author_username: String,
    pub body_html: String,
    pub created_at: DateTime<Utc>,
    pub edited_at: Option<DateTime<Utc>>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub removed_at: Option<DateTime<Utc>>,
    /// Author's current Fan+ status (denormalised on users; refreshed at
    /// OAuth callback / `POST /auth/refresh`). Used for the + badge and
    /// for signature visibility, signatures only render for current
    /// Fan+ subscribers.
    pub author_is_fan_plus: bool,
    /// Author's saved signature HTML (rendered at save time). Render only
    /// when `author_is_fan_plus` is true.
    pub author_signature_html: Option<String>,
}

#[derive(sqlx::FromRow)]
pub struct PostForEdit {
    pub id: Uuid,
    pub author_id: Uuid,
    pub body_markdown: String,
    pub created_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
    pub thread_id: Uuid,
    pub thread_title: String,
    pub community_name: String,
    pub community_slug: String,
    pub community_id: Uuid,
    pub category_name: String,
    pub category_slug: String,
}

#[tracing::instrument(skip_all)]
pub async fn list_posts_in_thread(
    pool: &PgPool,
    thread_id: Uuid,
) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
    sqlx::query_as!(
        PostWithAuthor,
        r#"SELECT p.id, p.author_id,
                COALESCE(u.display_name, u.username) AS "author_name!",
                u.username AS author_username,
                p.body_html,
                p.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
                p.edited_at AS "edited_at: chrono::DateTime<chrono::Utc>",
                p.deleted_at AS "deleted_at: chrono::DateTime<chrono::Utc>",
                p.removed_at AS "removed_at: chrono::DateTime<chrono::Utc>",
                u.is_fan_plus AS author_is_fan_plus,
                u.signature_html AS author_signature_html
         FROM posts p
         JOIN users u ON u.mnw_account_id = p.author_id
         WHERE p.thread_id = $1
         ORDER BY p.created_at"#,
        thread_id,
    )
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn list_posts_in_thread_paginated(
    pool: &PgPool,
    thread_id: Uuid,
    limit: i64,
    offset: i64,
) -> Result<Vec<PostWithAuthor>, sqlx::Error> {
    sqlx::query_as!(
        PostWithAuthor,
        r#"SELECT p.id, p.author_id,
                COALESCE(u.display_name, u.username) AS "author_name!",
                u.username AS author_username,
                p.body_html,
                p.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
                p.edited_at AS "edited_at: chrono::DateTime<chrono::Utc>",
                p.deleted_at AS "deleted_at: chrono::DateTime<chrono::Utc>",
                p.removed_at AS "removed_at: chrono::DateTime<chrono::Utc>",
                u.is_fan_plus AS author_is_fan_plus,
                u.signature_html AS author_signature_html
         FROM posts p
         JOIN users u ON u.mnw_account_id = p.author_id
         WHERE p.thread_id = $1
         ORDER BY p.created_at
         LIMIT $2 OFFSET $3"#,
        thread_id,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn count_posts_in_thread(pool: &PgPool, thread_id: Uuid) -> Result<i64, sqlx::Error> {
    // Intentionally counts ALL posts including mod-removed/soft-deleted: this
    // drives the thread-view pagination, and `list_posts_in_thread_paginated`
    // returns those rows so they render as tombstones. The count must match the
    // list it paginates, or the last page of tombstones would be cut off. For an
    // active-only count (e.g. the server-facing stats), use `get_thread_stats`.
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM posts WHERE thread_id = $1"#,
        thread_id,
    )
    .fetch_one(pool)
    .await
}

/// Count posts + footnotes by a user in the last N seconds (for per-user rate limiting).
#[tracing::instrument(skip_all)]
pub async fn count_recent_posts_by_user(
    pool: &PgPool,
    user_id: Uuid,
    seconds: i64,
) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT (SELECT COUNT(*) FROM posts WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2))
              + (SELECT COUNT(*) FROM post_footnotes WHERE author_id = $1 AND created_at > NOW() - make_interval(secs => $2)) AS "count!""#,
        user_id,
        seconds as f64,
    )
    .fetch_one(pool)
    .await
}

#[tracing::instrument(skip_all)]
pub async fn get_post_for_edit(
    pool: &PgPool,
    post_id: Uuid,
) -> Result<Option<super::Unscoped<PostForEdit>>, sqlx::Error> {
    sqlx::query_as!(
        PostForEdit,
        r#"SELECT p.id, p.author_id, p.body_markdown,
                p.created_at AS "created_at: chrono::DateTime<chrono::Utc>",
                p.deleted_at AS "deleted_at: chrono::DateTime<chrono::Utc>",
                p.thread_id, t.title AS thread_title,
                co.name AS community_name, co.slug AS community_slug,
                co.id AS community_id,
                c.name AS category_name, c.slug AS category_slug
         FROM posts p
         JOIN threads t ON t.id = p.thread_id
         JOIN categories c ON c.id = t.category_id
         JOIN communities co ON co.id = c.community_id
         WHERE p.id = $1"#,
        post_id,
    )
    .fetch_optional(pool)
    .await
    .map(|opt| {
        opt.map(|row| {
            let community_id = row.community_id;
            super::Unscoped::new(row, community_id)
        })
    })
}

/// Fetch a post's author_id and body_markdown for quote verification, scoped to
/// the quoting post's community.
///
/// The `community_id` filter is not optional: a `[quote:UUID:HASH]` marker only
/// verifies against a post *in the same community*, so a member of community A
/// can never use quote verification as an oracle against a post in community B
/// (C1 scope class, N3). There is deliberately no unscoped `by-id` variant of
/// this loader, the scoping is structural, which is why it needs no
/// `clippy.toml` disallowed-methods entry (cf. `get_category_in_community`).
#[tracing::instrument(skip_all)]
pub async fn get_post_body_markdown_in_community(
    pool: &PgPool,
    post_id: Uuid,
    community_id: Uuid,
) -> Result<Option<(Uuid, String)>, sqlx::Error> {
    // Only live posts can be quoted: a mod-removed post (or one in a soft-deleted
    // thread) must fail quote verification so its text cannot be re-surfaced into
    // a live post by quoting it back.
    sqlx::query!(
        "SELECT p.author_id, p.body_markdown
         FROM posts p
         JOIN threads t ON t.id = p.thread_id
         JOIN categories c ON c.id = t.category_id
         WHERE p.id = $1 AND c.community_id = $2
           AND p.removed_at IS NULL AND t.deleted_at IS NULL",
        post_id,
        community_id,
    )
    .fetch_optional(pool)
    .await
    .map(|opt| opt.map(|r| (r.author_id, r.body_markdown)))
}

/// Whether a post is mod-removed (`removed_at IS NOT NULL`).
#[tracing::instrument(skip_all)]
pub async fn is_post_removed(pool: &PgPool, post_id: Uuid) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT (removed_at IS NOT NULL) AS "removed!" FROM posts WHERE id = $1"#,
        post_id,
    )
    .fetch_one(pool)
    .await
}