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

/// A user's saved signature: `(markdown, html)`. Either field may be `NULL`.
pub type UserSignature = (Option<String>, Option<String>);

/// Look up a user's UUID by username.
#[tracing::instrument(skip_all)]
pub async fn get_user_by_username(
    pool: &PgPool,
    username: &str,
) -> Result<Option<Uuid>, sqlx::Error> {
    sqlx::query_scalar!(
        "SELECT mnw_account_id FROM users WHERE username = $1",
        username,
    )
    .fetch_optional(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct UserProfileRow {
    pub user_id: Uuid,
    pub username: String,
    pub display_name: Option<String>,
    pub avatar_url: Option<String>,
    pub role: CommunityRole,
    pub joined_at: DateTime<Utc>,
    pub post_count: i64,
    pub endorsement_count: i64,
}

/// Fetch a user's profile within a specific community.
/// Returns None if the user is not a member of the community.
#[tracing::instrument(skip_all)]
pub async fn get_user_profile_in_community(
    pool: &PgPool,
    community_slug: &str,
    username: &str,
) -> Result<Option<UserProfileRow>, sqlx::Error> {
    sqlx::query_as!(
        UserProfileRow,
        r#"SELECT u.mnw_account_id AS user_id,
                u.username,
                u.display_name,
                u.avatar_url,
                m.role AS "role: CommunityRole",
                m.joined_at AS "joined_at: chrono::DateTime<chrono::Utc>",
                (SELECT COUNT(*) FROM posts p
                 JOIN threads t ON t.id = p.thread_id
                 JOIN categories c ON c.id = t.category_id
                 WHERE p.author_id = u.mnw_account_id
                   AND c.community_id = co.id
                   AND p.removed_at IS NULL
                   AND p.deleted_at IS NULL
                   AND t.deleted_at IS NULL) AS "post_count!",
                (SELECT COUNT(*) FROM post_endorsements pe
                 JOIN posts p ON p.id = pe.post_id
                 JOIN threads t ON t.id = p.thread_id
                 JOIN categories c ON c.id = t.category_id
                 WHERE p.author_id = u.mnw_account_id
                   AND c.community_id = co.id
                   AND p.removed_at IS NULL
                   AND p.deleted_at IS NULL
                   AND t.deleted_at IS NULL) AS "endorsement_count!"
         FROM users u
         JOIN memberships m ON m.user_id = u.mnw_account_id
         JOIN communities co ON co.id = m.community_id
         WHERE co.slug = $1 AND u.username = $2"#,
        community_slug,
        username,
    )
    .fetch_optional(pool)
    .await
}

#[derive(sqlx::FromRow)]
pub struct UserActivityRow {
    pub thread_id: Uuid,
    pub thread_title: String,
    pub category_name: String,
    pub category_slug: String,
    pub post_created_at: DateTime<Utc>,
    pub is_thread_author: bool,
}

/// Fetch a user's recent activity (posts) within a community.
#[tracing::instrument(skip_all)]
pub async fn get_user_activity_in_community(
    pool: &PgPool,
    community_id: Uuid,
    user_id: Uuid,
    limit: i64,
) -> Result<Vec<UserActivityRow>, sqlx::Error> {
    sqlx::query_as!(
        UserActivityRow,
        r#"SELECT t.id AS thread_id,
                t.title AS thread_title,
                c.name AS category_name,
                c.slug AS category_slug,
                p.created_at AS "post_created_at: chrono::DateTime<chrono::Utc>",
                (t.author_id = $2) AS "is_thread_author!"
         FROM posts p
         JOIN threads t ON t.id = p.thread_id
         JOIN categories c ON c.id = t.category_id
         WHERE c.community_id = $1
           AND p.author_id = $2
           AND p.removed_at IS NULL
           AND p.deleted_at IS NULL
           AND t.deleted_at IS NULL
         ORDER BY p.created_at DESC
         LIMIT $3"#,
        community_id,
        user_id,
        limit,
    )
    .fetch_all(pool)
    .await
}

#[derive(sqlx::FromRow, serde::Serialize)]
pub struct UserMembershipSummary {
    pub community_name: String,
    pub community_slug: String,
    pub role: CommunityRole,
    pub joined_at: DateTime<Utc>,
    pub post_count: i64,
}

/// Fetch all community memberships for a user with post counts.
#[tracing::instrument(skip_all)]
pub async fn get_user_membership_summary(
    pool: &PgPool,
    user_id: Uuid,
) -> Result<Vec<UserMembershipSummary>, sqlx::Error> {
    sqlx::query_as!(
        UserMembershipSummary,
        r#"SELECT co.name AS community_name,
                co.slug AS community_slug,
                m.role AS "role: CommunityRole",
                m.joined_at AS "joined_at: chrono::DateTime<chrono::Utc>",
                (SELECT COUNT(*) FROM posts p
                 JOIN threads t ON t.id = p.thread_id
                 JOIN categories c ON c.id = t.category_id
                 WHERE p.author_id = $1
                   AND c.community_id = co.id
                   AND p.removed_at IS NULL
                   AND p.deleted_at IS NULL
                   AND t.deleted_at IS NULL) AS "post_count!"
         FROM memberships m
         JOIN communities co ON co.id = m.community_id
         WHERE m.user_id = $1
           AND co.suspended_at IS NULL
         ORDER BY co.name"#,
        user_id,
    )
    .fetch_all(pool)
    .await
}

/// Fetch a user's saved signature, or `None` if the user row is absent.
#[tracing::instrument(skip_all)]
pub async fn get_user_signature(
    pool: &PgPool,
    user_id: Uuid,
) -> Result<Option<UserSignature>, sqlx::Error> {
    let row = sqlx::query!(
        "SELECT signature_markdown, signature_html FROM users WHERE mnw_account_id = $1",
        user_id,
    )
    .fetch_optional(pool)
    .await?;
    Ok(row.map(|r| (r.signature_markdown, r.signature_html)))
}