//! Issues, labels, milestones and their events (hub spec §8).

use super::{now_ms, Pool};

use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]

pub struct IssueRow {

    pub id: i64,

    pub repo_id: i64,

    pub number: i64,

    pub title: String,

    pub body: String,

    pub author_id: Option<i64>,

    pub author: String,

    pub state: String,

    pub reason: String,

    pub locked: bool,

    pub pinned: bool,

    pub milestone_id: Option<i64>,

    pub created_ms: i64,

    pub updated_ms: i64,

    pub closed_ms: Option<i64>,

}

#[derive(Clone, Debug, FromRow)]

pub struct IssueComment {

    pub id: i64,

    pub issue_id: i64,

    pub author_id: Option<i64>,

    pub author: String,

    pub body: String,

    pub created_ms: i64,

    pub edited_ms: Option<i64>,

}

#[derive(Clone, Debug, FromRow)]

pub struct IssueEvent {

    pub id: i64,

    pub at_ms: i64,

    pub actor: String,

    pub event: String,

    pub detail: String,

}

#[derive(Clone, Debug, FromRow, PartialEq, Eq)]

pub struct Label {

    pub id: i64,

    pub repo_id: i64,

    pub name: String,

    pub colour: String,

    pub description: String,

}

#[derive(Clone, Debug, FromRow)]

pub struct Milestone {

    pub id: i64,

    pub repo_id: i64,

    pub title: String,

    pub description: String,

    pub due_ms: Option<i64>,

    pub state: String,

}

pub struct NewIssue<'a> {

    pub repo_id: i64,

    pub title: &'a str,

    pub body: &'a str,

    pub author_id: Option<i64>,

    pub author: &'a str,

}

pub async fn create(pool: &Pool, i: NewIssue<'_>) -> sqlx::Result<IssueRow> {

    let number = super::proposals::next_number(pool, i.repo_id).await?;

    let now = now_ms();

    sqlx::query_as("INSERT INTO issues (repo_id, number, title, body, author_id, author, created_ms, updated_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *")

        .bind(i.repo_id)

        .bind(number)

        .bind(i.title)

        .bind(i.body)

        .bind(i.author_id)

        .bind(i.author)

        .bind(now)

        .bind(now)

        .fetch_one(pool)

        .await

}

pub async fn get(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Option<IssueRow>> {

    sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 AND number = $2").bind(repo_id).bind(number).fetch_optional(pool).await

}

pub async fn get_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<IssueRow>> {

    sqlx::query_as("SELECT * FROM issues WHERE id = $1").bind(id).fetch_optional(pool).await

}

pub async fn list(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<IssueRow>> {

    sqlx::query_as("SELECT * FROM issues WHERE repo_id = $1 ORDER BY pinned DESC, number DESC").bind(repo_id).fetch_all(pool).await

}

/// Numbers in use by issues and proposals, for cross references.

pub async fn numbers(pool: &Pool, repo_id: i64) -> sqlx::Result<(Vec<i64>, Vec<i64>)> {

    let issues: Vec<i64> = sqlx::query_scalar("SELECT number FROM issues WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;

    let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = $1").bind(repo_id).fetch_all(pool).await?;

    Ok((issues, proposals))

}

pub async fn assigned_to(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<IssueRow>> {

    sqlx::query_as("SELECT i.* FROM issues i JOIN issue_assignees a ON a.issue_id = i.id WHERE a.account_id = $1 AND i.state = 'open' ORDER BY i.updated_ms DESC")

        .bind(account_id)

        .fetch_all(pool)

        .await

}

pub async fn edit(pool: &Pool, id: i64, title: &str, body: &str) -> sqlx::Result<()> {

    sqlx::query("UPDATE issues SET title = $1, body = $2, updated_ms = $3 WHERE id = $4")

        .bind(title)

        .bind(body)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn set_state(pool: &Pool, id: i64, state: &str, reason: &str) -> sqlx::Result<()> {

    let now = now_ms();

    let closed: Option<i64> = if state == "closed" { Some(now) } else { None };

    sqlx::query("UPDATE issues SET state = $1, reason = $2, closed_ms = $3, updated_ms = $4 WHERE id = $5")

        .bind(state)

        .bind(reason)

        .bind(closed)

        .bind(now)

        .bind(id)

        .execute(pool)

        .await?;

    if state == "closed" {

        finished(pool, "issues", id).await?;

    }

    Ok(())

}

/// Move the cards of finished issue or proposal `id` of `table` (#325).

pub(super) async fn finished(pool: &Pool, table: &str, id: i64) -> sqlx::Result<()> {

    let at: Option<(i64, i64)> =

        sqlx::query_as(&format!("SELECT repo_id, number FROM {table} WHERE id = $1")).bind(id).fetch_optional(pool).await?;

    match at {

        Some((repo, number)) => super::projects::finished(pool, repo, number).await,

        None => Ok(()),

    }

}

pub async fn set_flags(pool: &Pool, id: i64, locked: Option<bool>, pinned: Option<bool>) -> sqlx::Result<()> {

    if let Some(l) = locked {

        sqlx::query("UPDATE issues SET locked = $1 WHERE id = $2").bind(l).bind(id).execute(pool).await?;

    }

    if let Some(p) = pinned {

        sqlx::query("UPDATE issues SET pinned = $1 WHERE id = $2").bind(p).bind(id).execute(pool).await?;

    }

    Ok(())

}

pub async fn set_milestone(pool: &Pool, id: i64, milestone_id: Option<i64>) -> sqlx::Result<()> {

    sqlx::query("UPDATE issues SET milestone_id = $1, updated_ms = $2 WHERE id = $3")

        .bind(milestone_id)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {

    sqlx::query("UPDATE issues SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;

    Ok(())

}

pub async fn comments(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueComment>> {

    sqlx::query_as("SELECT * FROM issue_comments WHERE issue_id = $1 ORDER BY created_ms, id").bind(issue_id).fetch_all(pool).await

}

pub async fn add_comment(pool: &Pool, issue_id: i64, author_id: Option<i64>, author: &str, body: &str) -> sqlx::Result<IssueComment> {

    let row = sqlx::query_as(

        "INSERT INTO issue_comments (issue_id, author_id, author, body, created_ms) VALUES ($1, $2, $3, $4, $5) RETURNING *",

    )

    .bind(issue_id)

    .bind(author_id)

    .bind(author)

    .bind(body)

    .bind(now_ms())

    .fetch_one(pool)

    .await?;

    touch(pool, issue_id).await?;

    Ok(row)

}

pub async fn edit_comment(pool: &Pool, id: i64, author_id: i64, body: &str) -> sqlx::Result<bool> {

    let n = sqlx::query("UPDATE issue_comments SET body = $1, edited_ms = $2 WHERE id = $3 AND author_id = $4")

        .bind(body)

        .bind(now_ms())

        .bind(id)

        .bind(author_id)

        .execute(pool)

        .await?;

    Ok(n.rows_affected() > 0)

}

pub async fn delete_comment(pool: &Pool, id: i64) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM issue_comments WHERE id = $1").bind(id).execute(pool).await?;

    Ok(())

}

pub async fn events(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<IssueEvent>> {

    sqlx::query_as("SELECT id, at_ms, actor, event, detail FROM issue_events WHERE issue_id = $1 ORDER BY at_ms, id")

        .bind(issue_id)

        .fetch_all(pool)

        .await

}

/// Whether change `hash` (its short form) already references it (#318):

/// a change is recorded once, whichever bookmarks it is pushed on.

pub async fn referenced_by(pool: &Pool, issue_id: i64, hash: &str) -> sqlx::Result<bool> {

    let n: i64 = sqlx::query_scalar(

        "SELECT COUNT(*) FROM issue_events WHERE issue_id = $1 AND event = 'referenced by change' AND (detail = $2 OR detail LIKE $3)",

    )

    .bind(issue_id)

    .bind(hash)

    .bind(format!("{hash} %"))

    .fetch_one(pool)

    .await?;

    Ok(n > 0)

}

pub async fn add_event(pool: &Pool, issue_id: i64, actor: &str, event: &str, detail: &str) -> sqlx::Result<()> {

    sqlx::query("INSERT INTO issue_events (issue_id, at_ms, actor, event, detail) VALUES ($1, $2, $3, $4, $5)")

        .bind(issue_id)

        .bind(now_ms())

        .bind(actor)

        .bind(event)

        .bind(detail)

        .execute(pool)

        .await?;

    Ok(())

}

// ------------------------------------------------------------------ labels

pub async fn labels(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Label>> {

    sqlx::query_as("SELECT * FROM labels WHERE repo_id = $1 ORDER BY name").bind(repo_id).fetch_all(pool).await

}

pub async fn label_by_name(pool: &Pool, repo_id: i64, name: &str) -> sqlx::Result<Option<Label>> {

    sqlx::query_as("SELECT * FROM labels WHERE repo_id = $1 AND name = $2").bind(repo_id).bind(name).fetch_optional(pool).await

}

pub async fn upsert_label(pool: &Pool, repo_id: i64, name: &str, colour: &str, description: &str) -> sqlx::Result<Label> {

    sqlx::query_as("INSERT INTO labels (repo_id, name, colour, description) VALUES ($1, $2, $3, $4) ON CONFLICT (repo_id, name) DO UPDATE SET colour = excluded.colour, description = excluded.description RETURNING *")

        .bind(repo_id)

        .bind(name)

        .bind(colour)

        .bind(description)

        .fetch_one(pool)

        .await

}

pub async fn delete_label(pool: &Pool, id: i64) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM labels WHERE id = $1").bind(id).execute(pool).await?;

    Ok(())

}

pub async fn issue_labels(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<Label>> {

    sqlx::query_as("SELECT l.* FROM labels l JOIN issue_labels il ON il.label_id = l.id WHERE il.issue_id = $1 ORDER BY l.name")

        .bind(issue_id)

        .fetch_all(pool)

        .await

}

pub async fn set_issue_label(pool: &Pool, issue_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {

    if on {

        sqlx::query("INSERT INTO issue_labels (issue_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")

            .bind(issue_id)

            .bind(label_id)

            .execute(pool)

            .await?;

    } else {

        sqlx::query("DELETE FROM issue_labels WHERE issue_id = $1 AND label_id = $2").bind(issue_id).bind(label_id).execute(pool).await?;

    }

    Ok(())

}

/// Labels of every issue of a repository, for the list page.

/// Every proposal's labels in a repository, for filtering the list.

pub async fn labels_by_proposal(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {

    let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(

        "SELECT pl.proposal_id, l.id, l.repo_id, l.name, l.colour, l.description FROM proposal_labels pl JOIN labels l ON l.id = pl.label_id JOIN proposals p ON p.id = pl.proposal_id WHERE p.repo_id = $1 ORDER BY l.name",

    )

    .bind(repo_id)

    .fetch_all(pool)

    .await?;

    Ok(rows

        .into_iter()

        .map(|(proposal_id, id, repo_id, name, colour, description)| (proposal_id, Label { id, repo_id, name, colour, description }))

        .collect())

}

pub async fn labels_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, Label)>> {

    let rows: Vec<(i64, i64, i64, String, String, String)> = sqlx::query_as(

        "SELECT il.issue_id, l.id, l.repo_id, l.name, l.colour, l.description FROM issue_labels il JOIN labels l ON l.id = il.label_id JOIN issues i ON i.id = il.issue_id WHERE i.repo_id = $1 ORDER BY l.name",

    )

    .bind(repo_id)

    .fetch_all(pool)

    .await?;

    Ok(rows

        .into_iter()

        .map(|(issue_id, id, repo_id, name, colour, description)| (issue_id, Label { id, repo_id, name, colour, description }))

        .collect())

}

pub async fn proposal_labels(pool: &Pool, proposal_id: i64) -> sqlx::Result<Vec<Label>> {

    sqlx::query_as("SELECT l.* FROM labels l JOIN proposal_labels pl ON pl.label_id = l.id WHERE pl.proposal_id = $1 ORDER BY l.name")

        .bind(proposal_id)

        .fetch_all(pool)

        .await

}

pub async fn set_proposal_label(pool: &Pool, proposal_id: i64, label_id: i64, on: bool) -> sqlx::Result<()> {

    if on {

        sqlx::query("INSERT INTO proposal_labels (proposal_id, label_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")

            .bind(proposal_id)

            .bind(label_id)

            .execute(pool)

            .await?;

    } else {

        sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = $1 AND label_id = $2")

            .bind(proposal_id)

            .bind(label_id)

            .execute(pool)

            .await?;

    }

    Ok(())

}

// --------------------------------------------------------------- assignees

pub async fn assignees(pool: &Pool, issue_id: i64) -> sqlx::Result<Vec<String>> {

    sqlx::query_scalar(

        "SELECT a.username FROM accounts a JOIN issue_assignees ia ON ia.account_id = a.id WHERE ia.issue_id = $1 ORDER BY a.username",

    )

    .bind(issue_id)

    .fetch_all(pool)

    .await

}

pub async fn set_assignee(pool: &Pool, issue_id: i64, account_id: i64, on: bool) -> sqlx::Result<()> {

    if on {

        sqlx::query("INSERT INTO issue_assignees (issue_id, account_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")

            .bind(issue_id)

            .bind(account_id)

            .execute(pool)

            .await?;

    } else {

        sqlx::query("DELETE FROM issue_assignees WHERE issue_id = $1 AND account_id = $2")

            .bind(issue_id)

            .bind(account_id)

            .execute(pool)

            .await?;

    }

    Ok(())

}

/// Assignees of every issue of a repository, for the list page.

pub async fn assignees_by_issue(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<(i64, String)>> {

    sqlx::query_as("SELECT ia.issue_id, a.username FROM issue_assignees ia JOIN accounts a ON a.id = ia.account_id JOIN issues i ON i.id = ia.issue_id WHERE i.repo_id = $1")

        .bind(repo_id)

        .fetch_all(pool)

        .await

}

// -------------------------------------------------------------- milestones

pub async fn milestones(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Milestone>> {

    sqlx::query_as("SELECT * FROM milestones WHERE repo_id = $1 ORDER BY state, due_ms, title").bind(repo_id).fetch_all(pool).await

}

pub async fn milestone(pool: &Pool, id: i64) -> sqlx::Result<Option<Milestone>> {

    sqlx::query_as("SELECT * FROM milestones WHERE id = $1").bind(id).fetch_optional(pool).await

}

pub async fn upsert_milestone(pool: &Pool, repo_id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<Milestone> {

    sqlx::query_as("INSERT INTO milestones (repo_id, title, description, due_ms) VALUES ($1, $2, $3, $4) ON CONFLICT (repo_id, title) DO UPDATE SET description = excluded.description, due_ms = excluded.due_ms RETURNING *")

        .bind(repo_id)

        .bind(title)

        .bind(description)

        .bind(due_ms)

        .fetch_one(pool)

        .await

}

/// Change a milestone's title, description and due date.

pub async fn update_milestone(pool: &Pool, id: i64, title: &str, description: &str, due_ms: Option<i64>) -> sqlx::Result<()> {

    sqlx::query("UPDATE milestones SET title = $1, description = $2, due_ms = $3 WHERE id = $4")

        .bind(title)

        .bind(description)

        .bind(due_ms)

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn set_milestone_state(pool: &Pool, id: i64, state: &str) -> sqlx::Result<()> {

    sqlx::query("UPDATE milestones SET state = $1 WHERE id = $2").bind(state).bind(id).execute(pool).await?;

    Ok(())

}

/// `(open, closed)` issue counts per milestone.

pub async fn milestone_counts(pool: &Pool, milestone_id: i64) -> sqlx::Result<(i64, i64)> {

    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 AND state = 'open'")

        .bind(milestone_id)

        .fetch_one(pool)

        .await?;

    let closed: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issues WHERE milestone_id = $1 AND state = 'closed'")

        .bind(milestone_id)

        .fetch_one(pool)

        .await?;

    Ok((open, closed))

}