//! 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),
        Err(_) => {
            // Retry once on conflict (concurrent number assignment)
            try_create_issue(pool, repo_id, author_id, title, body_md, body_html).await
        }
    }
}

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)
}

/// 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)
        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)
        "#,
    )
    .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)
}

/// 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(|s| s.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)
}