//! Issue tracker queries: issues, comments, labels.

use sqlx::PgPool;

use super::models::{DbIssue, DbIssueComment, DbIssueCommentWithAuthor, DbIssueWithMeta};
use super::{GitRepoId, IssueId, IssueStatus, UserId};
use crate::error::Result;

// ── Issues ──

/// Create a new issue, assigning the next sequential number for the repo.
/// Retries once on unique constraint violation (concurrent insert race).
#[tracing::instrument(skip_all)]
pub async fn create_issue(
    pool: &PgPool,
    repo_id: GitRepoId,
    author_id: UserId,
    title: &str,
    body_md: &str,
    body_html: &str,
) -> Result<DbIssue> {
    let result = try_create_issue(pool, repo_id, author_id, title, body_md, body_html).await;
    match result {
        Ok(issue) => Ok(issue),
        // Retry once ONLY on a unique violation: concurrent `MAX(number)+1`
        // assignment can collide on the `(repo_id, number)` index. Any other
        // error (FK violation, timeout, pool exhaustion) is not helped by an
        // immediate retry and should surface (audit Run 13 Conc).
        Err(e) if crate::helpers::is_unique_violation(&e) => {
            try_create_issue(pool, repo_id, author_id, title, body_md, body_html).await
        }
        Err(e) => Err(e),
    }
}

async fn try_create_issue(
    pool: &PgPool,
    repo_id: GitRepoId,
    author_id: UserId,
    title: &str,
    body_md: &str,
    body_html: &str,
) -> Result<DbIssue> {
    let issue = sqlx::query_as::<_, DbIssue>(
        r"
        INSERT INTO issues (repo_id, number, author_user_id, title, body_markdown, body_html)
        VALUES ($1, (SELECT COALESCE(MAX(number), 0) + 1 FROM issues WHERE repo_id = $1), $2, $3, $4, $5)
        RETURNING *
        ",
    )
    .bind(repo_id)
    .bind(author_id)
    .bind(title)
    .bind(body_md)
    .bind(body_html)
    .fetch_one(pool)
    .await?;

    Ok(issue)
}

/// Get a single issue by its primary key.
#[tracing::instrument(skip_all)]
pub async fn get_issue_by_id(pool: &PgPool, issue_id: IssueId) -> Result<Option<DbIssue>> {
    let issue = sqlx::query_as::<_, DbIssue>("SELECT * FROM issues WHERE id = $1")
        .bind(issue_id)
        .fetch_optional(pool)
        .await?;
    Ok(issue)
}

/// Get a single issue by its repo-scoped number.
#[tracing::instrument(skip_all)]
pub async fn get_issue_by_number(
    pool: &PgPool,
    repo_id: GitRepoId,
    number: i32,
) -> Result<Option<DbIssue>> {
    let issue =
        sqlx::query_as::<_, DbIssue>("SELECT * FROM issues WHERE repo_id = $1 AND number = $2")
            .bind(repo_id)
            .bind(number)
            .fetch_optional(pool)
            .await?;

    Ok(issue)
}

/// Fetch multiple issues by number in a single query, keyed by number. Used by
/// the git push-refs handler so a push touching many commits doesn't run one
/// `get_issue_by_number` per commit-reference (N+1).
#[tracing::instrument(skip_all)]
pub async fn get_issues_by_numbers(
    pool: &PgPool,
    repo_id: GitRepoId,
    numbers: &[i32],
) -> Result<std::collections::HashMap<i32, DbIssue>> {
    if numbers.is_empty() {
        return Ok(std::collections::HashMap::new());
    }
    let issues = sqlx::query_as::<_, DbIssue>(
        "SELECT * FROM issues WHERE repo_id = $1 AND number = ANY($2)",
    )
    .bind(repo_id)
    .bind(numbers)
    .fetch_all(pool)
    .await?;
    Ok(issues.into_iter().map(|i| (i.number, i)).collect())
}

/// List issues with author username and comment count. Returns (issues, total_count).
#[tracing::instrument(skip_all)]
pub async fn list_issues(
    pool: &PgPool,
    repo_id: GitRepoId,
    status: Option<IssueStatus>,
    search: Option<&str>,
    page: i64,
    per_page: i64,
) -> Result<(Vec<DbIssueWithMeta>, i64)> {
    let offset = (page - 1) * per_page;
    let status_str = status.map(|s| s.to_string());
    let search_pattern = search.map(|s| {
        format!(
            "%{}%",
            s.replace('\\', "\\\\")
                .replace('%', "\\%")
                .replace('_', "\\_")
        )
    });

    let issues = sqlx::query_as::<_, DbIssueWithMeta>(
        r"
        SELECT
            i.id, i.repo_id, i.number, i.author_user_id, i.title,
            i.status, i.created_at, i.updated_at,
            u.username AS author_username,
            (SELECT COUNT(*) FROM issue_comments WHERE issue_id = i.id) AS comment_count
        FROM issues i
        JOIN users u ON u.id = i.author_user_id
        WHERE i.repo_id = $1
          AND ($2::TEXT IS NULL OR i.status = $2)
          AND ($3::TEXT IS NULL OR i.title ILIKE $3 OR i.body_markdown ILIKE $3)
        ORDER BY i.created_at DESC
        LIMIT $4 OFFSET $5
        ",
    )
    .bind(repo_id)
    .bind(&status_str)
    .bind(&search_pattern)
    .bind(per_page)
    .bind(offset)
    .fetch_all(pool)
    .await?;

    let total: i64 = sqlx::query_scalar(
        r"
        SELECT COUNT(*)
        FROM issues
        WHERE repo_id = $1
          AND ($2::TEXT IS NULL OR status = $2)
          AND ($3::TEXT IS NULL OR title ILIKE $3 OR body_markdown ILIKE $3)
        ",
    )
    .bind(repo_id)
    .bind(&status_str)
    .bind(&search_pattern)
    .fetch_one(pool)
    .await?;

    Ok((issues, total))
}

/// Update an issue's title and body.
#[tracing::instrument(skip_all)]
pub async fn update_issue(
    pool: &PgPool,
    issue_id: IssueId,
    title: &str,
    body_md: &str,
    body_html: &str,
) -> Result<()> {
    sqlx::query(
        "UPDATE issues SET title = $2, body_markdown = $3, body_html = $4, updated_at = NOW() WHERE id = $1",
    )
    .bind(issue_id)
    .bind(title)
    .bind(body_md)
    .bind(body_html)
    .execute(pool)
    .await?;

    Ok(())
}

/// Update an issue's status (open/closed).
#[tracing::instrument(skip_all)]
pub async fn update_issue_status(
    pool: &PgPool,
    issue_id: IssueId,
    status: IssueStatus,
) -> Result<()> {
    sqlx::query("UPDATE issues SET status = $2, updated_at = NOW() WHERE id = $1")
        .bind(issue_id)
        .bind(status)
        .execute(pool)
        .await?;

    Ok(())
}

/// Get (open_count, closed_count) for a repo.
#[tracing::instrument(skip_all)]
pub async fn get_issue_counts(pool: &PgPool, repo_id: GitRepoId) -> Result<(i64, i64)> {
    let open: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE repo_id = $1 AND status = 'open'")
            .bind(repo_id)
            .fetch_one(pool)
            .await?;

    let closed: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE repo_id = $1 AND status = 'closed'")
            .bind(repo_id)
            .fetch_one(pool)
            .await?;

    Ok((open, closed))
}

// ── Comments ──

/// Add a comment to an issue.
#[tracing::instrument(skip_all)]
pub async fn create_comment(
    pool: &PgPool,
    issue_id: IssueId,
    author_id: UserId,
    body_md: &str,
    body_html: &str,
) -> Result<DbIssueComment> {
    let comment = sqlx::query_as::<_, DbIssueComment>(
        r"
        INSERT INTO issue_comments (issue_id, author_user_id, body_markdown, body_html)
        VALUES ($1, $2, $3, $4)
        RETURNING *
        ",
    )
    .bind(issue_id)
    .bind(author_id)
    .bind(body_md)
    .bind(body_html)
    .fetch_one(pool)
    .await?;

    // Touch the issue's updated_at
    sqlx::query("UPDATE issues SET updated_at = NOW() WHERE id = $1")
        .bind(issue_id)
        .execute(pool)
        .await?;

    Ok(comment)
}

/// Bulk-insert issue comments in a single statement, then touch each affected
/// issue's `updated_at` once. The git-push processor would otherwise issue one
/// INSERT (and one updated_at UPDATE) per referenced commit, ~100-150 sequential
/// writes per push (ultra-fuzz Run 6 S6). All comments share one author.
#[tracing::instrument(skip_all)]
pub async fn create_comments(
    pool: &PgPool,
    author_id: UserId,
    comments: &[(IssueId, String, String)],
) -> Result<()> {
    if comments.is_empty() {
        return Ok(());
    }
    let issue_ids: Vec<sqlx::types::Uuid> =
        comments.iter().map(|(id, _, _)| *id.as_uuid()).collect();
    let bodies_md: Vec<&str> = comments.iter().map(|(_, md, _)| md.as_str()).collect();
    let bodies_html: Vec<&str> = comments.iter().map(|(_, _, html)| html.as_str()).collect();

    sqlx::query(
        r"
        INSERT INTO issue_comments (issue_id, author_user_id, body_markdown, body_html)
        SELECT u.issue_id, $1, u.body_md, u.body_html
        FROM UNNEST($2::uuid[], $3::text[], $4::text[]) AS u(issue_id, body_md, body_html)
        ",
    )
    .bind(author_id)
    .bind(&issue_ids)
    .bind(&bodies_md)
    .bind(&bodies_html)
    .execute(pool)
    .await?;

    // Touch updated_at once per distinct affected issue.
    let mut distinct = issue_ids;
    distinct.sort_unstable();
    distinct.dedup();
    sqlx::query("UPDATE issues SET updated_at = NOW() WHERE id = ANY($1)")
        .bind(&distinct)
        .execute(pool)
        .await?;

    Ok(())
}

/// Bulk status update for the git-push processor. Each entry is `(issue, final
/// status)`; the caller resolves repeated references to one final state so each
/// issue appears at most once.
#[tracing::instrument(skip_all)]
pub async fn update_issue_statuses(
    pool: &PgPool,
    updates: &[(IssueId, IssueStatus)],
) -> Result<()> {
    if updates.is_empty() {
        return Ok(());
    }
    let ids: Vec<sqlx::types::Uuid> = updates.iter().map(|(id, _)| *id.as_uuid()).collect();
    let statuses: Vec<String> = updates.iter().map(|(_, s)| s.to_string()).collect();

    sqlx::query(
        r"
        UPDATE issues AS i SET status = u.status, updated_at = NOW()
        FROM UNNEST($1::uuid[], $2::text[]) AS u(id, status)
        WHERE i.id = u.id
        ",
    )
    .bind(&ids)
    .bind(&statuses)
    .execute(pool)
    .await?;

    Ok(())
}

/// List all comments on an issue with author usernames.
#[tracing::instrument(skip_all)]
pub async fn list_comments(
    pool: &PgPool,
    issue_id: IssueId,
) -> Result<Vec<DbIssueCommentWithAuthor>> {
    let comments = sqlx::query_as::<_, DbIssueCommentWithAuthor>(
        r"
        SELECT c.id, c.issue_id, c.author_user_id, c.body_markdown, c.body_html, c.created_at,
               u.username AS author_username
        FROM issue_comments c
        JOIN users u ON u.id = c.author_user_id
        WHERE c.issue_id = $1
        ORDER BY c.created_at ASC
        ",
    )
    .bind(issue_id)
    .fetch_all(pool)
    .await?;

    Ok(comments)
}

// ── Issue participants (for email notifications) ──

/// Get all distinct participant user IDs for an issue (author + all comment authors).
#[tracing::instrument(skip_all)]
pub async fn get_issue_participants(pool: &PgPool, issue_id: IssueId) -> Result<Vec<UserId>> {
    let ids = sqlx::query_scalar::<_, UserId>(
        r"
        SELECT DISTINCT author_user_id
        FROM (
            SELECT author_user_id FROM issues WHERE id = $1
            UNION
            SELECT author_user_id FROM issue_comments WHERE issue_id = $1
        ) AS participants
        ",
    )
    .bind(issue_id)
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// Record the Multithreaded forum thread that mirrors this issue. Best-effort:
/// log and proceed on DB error so the issue itself isn't lost.
#[tracing::instrument(skip_all)]
pub async fn set_mt_thread_id(
    pool: &PgPool,
    issue_id: IssueId,
    mt_thread_id: uuid::Uuid,
) -> Result<()> {
    sqlx::query("UPDATE issues SET mt_thread_id = $2 WHERE id = $1")
        .bind(issue_id)
        .bind(mt_thread_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// Fetch the MT thread linked to an issue, if any.
#[tracing::instrument(skip_all)]
pub async fn get_mt_thread_id(pool: &PgPool, issue_id: IssueId) -> Result<Option<uuid::Uuid>> {
    let row: Option<(Option<uuid::Uuid>,)> =
        sqlx::query_as("SELECT mt_thread_id FROM issues WHERE id = $1")
            .bind(issue_id)
            .fetch_optional(pool)
            .await?;
    Ok(row.and_then(|r| r.0))
}

// ── Issue message ID mapping (for email threading) ──

/// Store a mapping from an email Message-ID to an issue.
#[tracing::instrument(skip_all)]
pub async fn insert_issue_message_id(
    pool: &PgPool,
    message_id: &str,
    issue_id: IssueId,
) -> Result<()> {
    sqlx::query(
        "INSERT INTO issue_message_ids (message_id, issue_id) VALUES ($1, $2) ON CONFLICT DO NOTHING",
    )
    .bind(message_id)
    .bind(issue_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Look up an issue ID by any of the given email Message-IDs.
#[tracing::instrument(skip_all)]
pub async fn get_issue_id_by_any_message_id(
    pool: &PgPool,
    message_ids: &[&str],
) -> Result<Option<IssueId>> {
    if message_ids.is_empty() {
        return Ok(None);
    }

    let ids: Vec<String> = message_ids
        .iter()
        .map(std::string::ToString::to_string)
        .collect();
    let issue_id = sqlx::query_scalar::<_, IssueId>(
        "SELECT issue_id FROM issue_message_ids WHERE message_id = ANY($1) LIMIT 1",
    )
    .bind(&ids)
    .fetch_optional(pool)
    .await?;

    Ok(issue_id)
}