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

/// Check if a user is tracking a specific thread.
#[tracing::instrument(skip_all)]
pub async fn is_thread_tracked(
    pool: &PgPool,
    user_id: Uuid,
    thread_id: Uuid,
) -> Result<bool, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT EXISTS(SELECT 1 FROM tracked_threads WHERE user_id = $1 AND thread_id = $2) AS "exists!""#,
        user_id,
        thread_id,
    )
    .fetch_one(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct TrackedThreadRow {
    pub thread_id: Uuid,
    pub thread_title: String,
    pub community_name: String,
    pub community_slug: String,
    pub category_slug: String,
    pub unread_count: i64,
    pub has_mention: bool,
    pub tracked_at: DateTime<Utc>,
}

/// List a user's tracked threads with unread post counts, newest activity first.
///
/// Paginated (`limit`/`offset`) so a user tracking hundreds of threads doesn't
/// render (and recompute) an unbounded page. The last-read cutoff is resolved
/// with a single LEFT JOIN rather than a per-row nested subquery, and the unread
/// count excludes mod-removed posts (`p.removed_at IS NULL`) so the badge can't
/// be inflated by removed content and can use the `idx_posts_not_removed` index.
#[tracing::instrument(skip_all)]
pub async fn list_tracked_threads(
    pool: &PgPool,
    user_id: Uuid,
    limit: i64,
    offset: i64,
) -> Result<Vec<TrackedThreadRow>, sqlx::Error> {
    sqlx::query_as!(
        TrackedThreadRow,
        r#"SELECT tt.thread_id,
                t.title AS thread_title,
                co.name AS community_name,
                co.slug AS community_slug,
                cat.slug AS category_slug,
                (SELECT COUNT(*) FROM posts p
                 WHERE p.thread_id = tt.thread_id
                   AND p.removed_at IS NULL
                   AND (lrp.created_at IS NULL OR p.created_at > lrp.created_at)
                ) AS "unread_count!",
                EXISTS (
                    SELECT 1 FROM post_mentions pm
                    JOIN posts p ON p.id = pm.post_id
                    WHERE pm.mentioned_user_id = tt.user_id
                      AND p.thread_id = tt.thread_id
                ) AS "has_mention!",
                tt.tracked_at AS "tracked_at: chrono::DateTime<chrono::Utc>"
         FROM tracked_threads tt
         JOIN threads t ON t.id = tt.thread_id
         JOIN categories cat ON cat.id = t.category_id
         JOIN communities co ON co.id = cat.community_id
         LEFT JOIN posts lrp ON lrp.id = tt.last_read_post_id
         WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL
         ORDER BY t.last_activity_at DESC
         LIMIT $2 OFFSET $3"#,
        user_id,
        limit,
        offset,
    )
    .fetch_all(pool)
    .await
}

/// Count a user's visible tracked threads (for pagination). Mirrors the
/// visibility filters of `list_tracked_threads`.
#[tracing::instrument(skip_all)]
pub async fn count_tracked_threads(pool: &PgPool, user_id: Uuid) -> Result<i64, sqlx::Error> {
    sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!"
         FROM tracked_threads tt
         JOIN threads t ON t.id = tt.thread_id
         JOIN categories cat ON cat.id = t.category_id
         JOIN communities co ON co.id = cat.community_id
         WHERE tt.user_id = $1 AND t.deleted_at IS NULL AND co.suspended_at IS NULL"#,
        user_id,
    )
    .fetch_one(pool)
    .await
}