// This Source Code Form is subject to the terms of the Mozilla Public
// License, v. 2.0. If a copy of the MPL was not distributed with this
// file, You can obtain one at https://mozilla.org/MPL/2.0/.

//! Issues and pull requests (shared `issues` table), comments, and issue labels.

use model::{Comment, Issue, IssueState, Label, PullRequest};
use sqlx::any::AnyRow;
use sqlx::{AssertSqlSafe, Row};

use crate::{Store, StoreError, new_id, now_ms};

/// The columns of `issues` in a fixed order.
const ISSUE_COLUMNS: &str = "id, repo_id, number, author_id, title, body, state, is_pull, \
     assignee_id, created_at, updated_at, closed_at, locked_at";

/// Open/closed counts for a repo's issues or PRs.
#[derive(Debug, Clone, Copy, Default, PartialEq, Eq)]
pub struct StateCounts {
    /// Number of open items.
    pub open: i64,
    /// Number of closed items.
    pub closed: i64,
}

impl Store {
    /// Create an issue or PR, allocating the next per-repo number atomically.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn create_issue(
        &self,
        repo_id: &str,
        author_id: &str,
        title: &str,
        body: &str,
        is_pull: bool,
    ) -> Result<Issue, StoreError> {
        let mut tx = self.pool.begin().await?;
        // Allocate the shared number: read then bump inside the transaction.
        let row = sqlx::query("SELECT next_iid FROM repos WHERE id = $1")
            .bind(repo_id)
            .fetch_one(&mut *tx)
            .await?;
        let number: i64 = row.try_get("next_iid")?;
        sqlx::query("UPDATE repos SET next_iid = $1 WHERE id = $2")
            .bind(number + 1)
            .bind(repo_id)
            .execute(&mut *tx)
            .await?;

        let now = now_ms();
        let issue = Issue {
            id: new_id(),
            repo_id: repo_id.to_string(),
            number,
            author_id: author_id.to_string(),
            title: title.to_string(),
            body: body.to_string(),
            state: IssueState::Open,
            is_pull,
            assignee_id: None,
            created_at: now,
            updated_at: now,
            closed_at: None,
            locked_at: None,
        };
        let sql = "INSERT INTO issues \
             (id, repo_id, number, author_id, title, body, state, is_pull, assignee_id, \
              created_at, updated_at, closed_at) \
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)";
        sqlx::query(sql)
            .bind(&issue.id)
            .bind(&issue.repo_id)
            .bind(issue.number)
            .bind(&issue.author_id)
            .bind(&issue.title)
            .bind(&issue.body)
            .bind(issue.state.as_str())
            .bind(issue.is_pull)
            .bind(&issue.assignee_id)
            .bind(issue.created_at)
            .bind(issue.updated_at)
            .bind(issue.closed_at)
            .execute(&mut *tx)
            .await?;
        tx.commit().await?;
        Ok(issue)
    }

    /// Fetch an issue/PR by repo and number, filtered by kind.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn issue_by_number(
        &self,
        repo_id: &str,
        number: i64,
        is_pull: bool,
    ) -> Result<Option<Issue>, StoreError> {
        let sql = format!(
            "SELECT {ISSUE_COLUMNS} FROM issues WHERE repo_id = $1 AND number = $2 AND is_pull = $3"
        );
        let row = sqlx::query(AssertSqlSafe(sql))
            .bind(repo_id)
            .bind(number)
            .bind(is_pull)
            .fetch_optional(&self.pool)
            .await?;
        Ok(row.as_ref().map(|r| self.map_issue(r)).transpose()?)
    }

    /// Fetch an issue or PR by repo and number, of either kind (for dependency
    /// references, which cross issues and PRs).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn issue_by_number_any(
        &self,
        repo_id: &str,
        number: i64,
    ) -> Result<Option<Issue>, StoreError> {
        let sql = format!("SELECT {ISSUE_COLUMNS} FROM issues WHERE repo_id = $1 AND number = $2");
        let row = sqlx::query(AssertSqlSafe(sql))
            .bind(repo_id)
            .bind(number)
            .fetch_optional(&self.pool)
            .await?;
        Ok(row.as_ref().map(|r| self.map_issue(r)).transpose()?)
    }

    /// Fetch an issue/PR by id.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn issue_by_id(&self, id: &str) -> Result<Option<Issue>, StoreError> {
        let sql = format!("SELECT {ISSUE_COLUMNS} FROM issues WHERE id = $1");
        let row = sqlx::query(AssertSqlSafe(sql))
            .bind(id)
            .fetch_optional(&self.pool)
            .await?;
        Ok(row.as_ref().map(|r| self.map_issue(r)).transpose()?)
    }

    /// List a repo's issues or PRs, optionally filtered by state, newest first,
    /// bounded to `limit` rows starting at `offset`.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn list_issues(
        &self,
        repo_id: &str,
        is_pull: bool,
        state: Option<IssueState>,
        limit: i64,
        offset: i64,
    ) -> Result<Vec<Issue>, StoreError> {
        // Placeholders after the fixed ones depend on whether a state filter is set.
        let (state_clause, limit_ph, offset_ph) = if state.is_some() {
            (" AND state = $3", "$4", "$5")
        } else {
            ("", "$3", "$4")
        };
        let sql = format!(
            "SELECT {ISSUE_COLUMNS} FROM issues WHERE repo_id = $1 AND is_pull = $2{state_clause} \
             ORDER BY number DESC LIMIT {limit_ph} OFFSET {offset_ph}"
        );
        let mut query = sqlx::query(AssertSqlSafe(sql)).bind(repo_id).bind(is_pull);
        if let Some(s) = state {
            query = query.bind(s.as_str());
        }
        let rows = query.bind(limit).bind(offset).fetch_all(&self.pool).await?;
        rows.iter()
            .map(|r| self.map_issue(r))
            .collect::<Result<_, _>>()
            .map_err(Into::into)
    }

    /// Open/closed counts for a repo's issues or PRs.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn issue_counts(
        &self,
        repo_id: &str,
        is_pull: bool,
    ) -> Result<StateCounts, StoreError> {
        let sql = "SELECT state, COUNT(*) AS n FROM issues \
             WHERE repo_id = $1 AND is_pull = $2 GROUP BY state";
        let rows = sqlx::query(sql)
            .bind(repo_id)
            .bind(is_pull)
            .fetch_all(&self.pool)
            .await?;
        let mut counts = StateCounts::default();
        for row in &rows {
            let state: String = row.try_get("state")?;
            let n: i64 = row.try_get("n")?;
            if state == "closed" {
                counts.closed = n;
            } else {
                counts.open = n;
            }
        }
        Ok(counts)
    }

    /// Set an issue/PR's open/closed state (stamping/clearing `closed_at`).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn set_issue_state(&self, id: &str, state: IssueState) -> Result<bool, StoreError> {
        let now = now_ms();
        let closed_at = matches!(state, IssueState::Closed).then_some(now);
        let updated = sqlx::query(
            "UPDATE issues SET state = $1, closed_at = $2, updated_at = $3 WHERE id = $4",
        )
        .bind(state.as_str())
        .bind(closed_at)
        .bind(now)
        .bind(id)
        .execute(&self.pool)
        .await?
        .rows_affected();
        Ok(updated > 0)
    }

    /// Lock or unlock an issue/PR conversation.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn set_issue_locked(&self, id: &str, locked: bool) -> Result<bool, StoreError> {
        let now = now_ms();
        let locked_at = locked.then_some(now);
        let updated =
            sqlx::query("UPDATE issues SET locked_at = $1, updated_at = $2 WHERE id = $3")
                .bind(locked_at)
                .bind(now)
                .bind(id)
                .execute(&self.pool)
                .await?
                .rows_affected();
        Ok(updated > 0)
    }

    /// Set (or clear) an issue/PR's assignee.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn set_issue_assignee(
        &self,
        id: &str,
        assignee_id: Option<&str>,
    ) -> Result<bool, StoreError> {
        let updated =
            sqlx::query("UPDATE issues SET assignee_id = $1, updated_at = $2 WHERE id = $3")
                .bind(assignee_id)
                .bind(now_ms())
                .bind(id)
                .execute(&self.pool)
                .await?
                .rows_affected();
        Ok(updated > 0)
    }

    /// Edit an issue/PR's title and body.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn update_issue(
        &self,
        id: &str,
        title: &str,
        body: &str,
    ) -> Result<bool, StoreError> {
        let updated =
            sqlx::query("UPDATE issues SET title = $1, body = $2, updated_at = $3 WHERE id = $4")
                .bind(title)
                .bind(body)
                .bind(now_ms())
                .bind(id)
                .execute(&self.pool)
                .await?
                .rows_affected();
        Ok(updated > 0)
    }

    // ---- Comments ----

    /// Add a comment to an issue/PR.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn add_comment(
        &self,
        issue_id: &str,
        author_id: &str,
        body: &str,
    ) -> Result<Comment, StoreError> {
        let now = now_ms();
        let comment = Comment {
            id: new_id(),
            issue_id: issue_id.to_string(),
            author_id: author_id.to_string(),
            body: body.to_string(),
            created_at: now,
            updated_at: now,
        };
        sqlx::query(
            "INSERT INTO comments (id, issue_id, author_id, body, created_at, updated_at) \
             VALUES ($1, $2, $3, $4, $5, $6)",
        )
        .bind(&comment.id)
        .bind(&comment.issue_id)
        .bind(&comment.author_id)
        .bind(&comment.body)
        .bind(comment.created_at)
        .bind(comment.updated_at)
        .execute(&self.pool)
        .await?;
        // Touch the issue so its "updated" reflects the new activity.
        let _ = sqlx::query("UPDATE issues SET updated_at = $1 WHERE id = $2")
            .bind(now)
            .bind(issue_id)
            .execute(&self.pool)
            .await;
        Ok(comment)
    }

    /// List an issue/PR's comments oldest-first.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn list_comments(&self, issue_id: &str) -> Result<Vec<Comment>, StoreError> {
        let rows = sqlx::query(
            "SELECT id, issue_id, author_id, body, created_at, updated_at FROM comments \
             WHERE issue_id = $1 ORDER BY created_at ASC",
        )
        .bind(issue_id)
        .fetch_all(&self.pool)
        .await?;
        rows.iter()
            .map(|r| {
                Ok(Comment {
                    id: r.try_get("id")?,
                    issue_id: r.try_get("issue_id")?,
                    author_id: r.try_get("author_id")?,
                    body: r.try_get("body")?,
                    created_at: r.try_get("created_at")?,
                    updated_at: r.try_get("updated_at")?,
                })
            })
            .collect::<Result<_, sqlx::Error>>()
            .map_err(Into::into)
    }

    /// Fetch a single comment by id (for edit authorization).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn comment_by_id(&self, id: &str) -> Result<Option<Comment>, StoreError> {
        let row = sqlx::query(
            "SELECT id, issue_id, author_id, body, created_at, updated_at FROM comments \
             WHERE id = $1",
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;
        row.map(|r| {
            Ok(Comment {
                id: r.try_get("id")?,
                issue_id: r.try_get("issue_id")?,
                author_id: r.try_get("author_id")?,
                body: r.try_get("body")?,
                created_at: r.try_get("created_at")?,
                updated_at: r.try_get("updated_at")?,
            })
        })
        .transpose()
        .map_err(|e: sqlx::Error| e.into())
    }

    /// Edit a comment's body.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn update_comment(&self, id: &str, body: &str) -> Result<bool, StoreError> {
        let updated = sqlx::query("UPDATE comments SET body = $1, updated_at = $2 WHERE id = $3")
            .bind(body)
            .bind(now_ms())
            .bind(id)
            .execute(&self.pool)
            .await?
            .rows_affected();
        Ok(updated > 0)
    }

    // ---- Issue labels ----

    /// The labels applied to an issue/PR.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn issue_labels(&self, issue_id: &str) -> Result<Vec<Label>, StoreError> {
        let sql = "SELECT l.id, l.repo_id, l.name, l.color, l.description, l.created_at \
             FROM issue_labels il JOIN labels l ON l.id = il.label_id \
             WHERE il.issue_id = $1 ORDER BY l.name_lower ASC";
        let rows = sqlx::query(sql)
            .bind(issue_id)
            .fetch_all(&self.pool)
            .await?;
        rows.iter()
            .map(|r| {
                Ok(Label {
                    id: r.try_get("id")?,
                    repo_id: r.try_get("repo_id")?,
                    name: r.try_get("name")?,
                    color: r.try_get("color")?,
                    description: r.try_get("description")?,
                    created_at: r.try_get("created_at")?,
                })
            })
            .collect::<Result<_, sqlx::Error>>()
            .map_err(Into::into)
    }

    /// Add a label to an issue/PR (idempotent).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn add_issue_label(&self, issue_id: &str, label_id: &str) -> Result<(), StoreError> {
        sqlx::query(
            "INSERT INTO issue_labels (issue_id, label_id) VALUES ($1, $2) \
             ON CONFLICT (issue_id, label_id) DO NOTHING",
        )
        .bind(issue_id)
        .bind(label_id)
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Remove a label from an issue/PR.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn remove_issue_label(
        &self,
        issue_id: &str,
        label_id: &str,
    ) -> Result<(), StoreError> {
        sqlx::query("DELETE FROM issue_labels WHERE issue_id = $1 AND label_id = $2")
            .bind(issue_id)
            .bind(label_id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    // ---- Pull requests ----

    /// Open a pull request: allocate the shared number, insert the `issues` row
    /// (`is_pull = true`), and record its base/head refs, all in one transaction.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn create_pull(
        &self,
        repo_id: &str,
        author_id: &str,
        title: &str,
        body: &str,
        base_ref: &str,
        head_ref: &str,
    ) -> Result<Issue, StoreError> {
        let mut tx = self.pool.begin().await?;
        let row = sqlx::query("SELECT next_iid FROM repos WHERE id = $1")
            .bind(repo_id)
            .fetch_one(&mut *tx)
            .await?;
        let number: i64 = row.try_get("next_iid")?;
        sqlx::query("UPDATE repos SET next_iid = $1 WHERE id = $2")
            .bind(number + 1)
            .bind(repo_id)
            .execute(&mut *tx)
            .await?;

        let now = now_ms();
        let issue = Issue {
            id: new_id(),
            repo_id: repo_id.to_string(),
            number,
            author_id: author_id.to_string(),
            title: title.to_string(),
            body: body.to_string(),
            state: IssueState::Open,
            is_pull: true,
            assignee_id: None,
            created_at: now,
            updated_at: now,
            closed_at: None,
            locked_at: None,
        };
        sqlx::query(
            "INSERT INTO issues \
             (id, repo_id, number, author_id, title, body, state, is_pull, assignee_id, \
              created_at, updated_at, closed_at) \
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)",
        )
        .bind(&issue.id)
        .bind(&issue.repo_id)
        .bind(issue.number)
        .bind(&issue.author_id)
        .bind(&issue.title)
        .bind(&issue.body)
        .bind(issue.state.as_str())
        .bind(issue.is_pull)
        .bind(&issue.assignee_id)
        .bind(issue.created_at)
        .bind(issue.updated_at)
        .bind(issue.closed_at)
        .execute(&mut *tx)
        .await?;
        sqlx::query("INSERT INTO pull_requests (issue_id, base_ref, head_ref) VALUES ($1, $2, $3)")
            .bind(&issue.id)
            .bind(base_ref)
            .bind(head_ref)
            .execute(&mut *tx)
            .await?;
        tx.commit().await?;
        Ok(issue)
    }

    /// Fetch the pull-request row for an issue id, if it is a PR.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn pull_by_issue(&self, issue_id: &str) -> Result<Option<PullRequest>, StoreError> {
        let row = sqlx::query(
            "SELECT issue_id, base_ref, head_ref, merged_at, merged_by, merge_sha, merge_strategy \
             FROM pull_requests WHERE issue_id = $1",
        )
        .bind(issue_id)
        .fetch_optional(&self.pool)
        .await?;
        row.map(|r| {
            Ok(PullRequest {
                issue_id: r.try_get("issue_id")?,
                base_ref: r.try_get("base_ref")?,
                head_ref: r.try_get("head_ref")?,
                merged_at: r.try_get("merged_at")?,
                merged_by: r.try_get("merged_by")?,
                merge_sha: r.try_get("merge_sha")?,
                merge_strategy: r.try_get("merge_strategy")?,
            })
        })
        .transpose()
        .map_err(|e: sqlx::Error| e.into())
    }

    /// Record a merge: stamp the pull-request row and close the issue, atomically.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn mark_pull_merged(
        &self,
        issue_id: &str,
        merged_by: &str,
        merge_sha: &str,
        strategy: &str,
    ) -> Result<(), StoreError> {
        let now = now_ms();
        let mut tx = self.pool.begin().await?;
        sqlx::query(
            "UPDATE pull_requests \
             SET merged_at = $1, merged_by = $2, merge_sha = $3, merge_strategy = $4 \
             WHERE issue_id = $5",
        )
        .bind(now)
        .bind(merged_by)
        .bind(merge_sha)
        .bind(strategy)
        .bind(issue_id)
        .execute(&mut *tx)
        .await?;
        sqlx::query(
            "UPDATE issues SET state = 'closed', closed_at = $1, updated_at = $1 WHERE id = $2",
        )
        .bind(now)
        .bind(issue_id)
        .execute(&mut *tx)
        .await?;
        tx.commit().await?;
        Ok(())
    }

    // ---- Dependencies ----

    /// Record that `issue_id` is blocked by `depends_on_id` (idempotent).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn add_dependency(
        &self,
        issue_id: &str,
        depends_on_id: &str,
    ) -> Result<(), StoreError> {
        sqlx::query(
            "INSERT INTO issue_dependencies (issue_id, depends_on_id, created_at) \
             VALUES ($1, $2, $3) ON CONFLICT (issue_id, depends_on_id) DO NOTHING",
        )
        .bind(issue_id)
        .bind(depends_on_id)
        .bind(now_ms())
        .execute(&self.pool)
        .await?;
        Ok(())
    }

    /// Remove a dependency edge.
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn remove_dependency(
        &self,
        issue_id: &str,
        depends_on_id: &str,
    ) -> Result<(), StoreError> {
        sqlx::query("DELETE FROM issue_dependencies WHERE issue_id = $1 AND depends_on_id = $2")
            .bind(issue_id)
            .bind(depends_on_id)
            .execute(&self.pool)
            .await?;
        Ok(())
    }

    /// The issues/PRs that `issue_id` is blocked by (its dependencies).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn dependencies_of(&self, issue_id: &str) -> Result<Vec<Issue>, StoreError> {
        self.related_issues(
            "SELECT i.id, i.repo_id, i.number, i.author_id, i.title, i.body, i.state, \
                    i.is_pull, i.assignee_id, i.created_at, i.updated_at, i.closed_at, i.locked_at \
             FROM issue_dependencies d JOIN issues i ON i.id = d.depends_on_id \
             WHERE d.issue_id = $1 ORDER BY i.number ASC",
            issue_id,
        )
        .await
    }

    /// The issues/PRs blocked by `issue_id` (its dependents).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn dependents_of(&self, issue_id: &str) -> Result<Vec<Issue>, StoreError> {
        self.related_issues(
            "SELECT i.id, i.repo_id, i.number, i.author_id, i.title, i.body, i.state, \
                    i.is_pull, i.assignee_id, i.created_at, i.updated_at, i.closed_at, i.locked_at \
             FROM issue_dependencies d JOIN issues i ON i.id = d.issue_id \
             WHERE d.depends_on_id = $1 ORDER BY i.number ASC",
            issue_id,
        )
        .await
    }

    /// Count `issue_id`'s dependencies that are still open (i.e. blocking it).
    ///
    /// # Errors
    ///
    /// Returns [`StoreError::Query`] on a database failure.
    pub async fn open_dependency_count(&self, issue_id: &str) -> Result<i64, StoreError> {
        let row = sqlx::query(
            "SELECT COUNT(*) AS n FROM issue_dependencies d JOIN issues i ON i.id = d.depends_on_id \
             WHERE d.issue_id = $1 AND i.state = 'open'",
        )
        .bind(issue_id)
        .fetch_one(&self.pool)
        .await?;
        Ok(row.try_get("n")?)
    }

    /// Run a dependency join query bound to one issue id and map the rows.
    async fn related_issues(&self, sql: &str, issue_id: &str) -> Result<Vec<Issue>, StoreError> {
        let rows = sqlx::query(AssertSqlSafe(sql.to_string()))
            .bind(issue_id)
            .fetch_all(&self.pool)
            .await?;
        rows.iter()
            .map(|r| self.map_issue(r))
            .collect::<Result<_, _>>()
            .map_err(Into::into)
    }

    /// Map an `issues` row (selected via [`ISSUE_COLUMNS`]) to an [`Issue`].
    fn map_issue(&self, row: &AnyRow) -> Result<Issue, sqlx::Error> {
        Ok(Issue {
            id: row.try_get("id")?,
            repo_id: row.try_get("repo_id")?,
            number: row.try_get("number")?,
            author_id: row.try_get("author_id")?,
            title: row.try_get("title")?,
            body: row.try_get("body")?,
            state: IssueState::from_token(&row.try_get::<String, _>("state")?),
            is_pull: self.get_bool(row, "is_pull")?,
            assignee_id: row.try_get("assignee_id")?,
            created_at: row.try_get("created_at")?,
            updated_at: row.try_get("updated_at")?,
            closed_at: row.try_get("closed_at")?,
            locked_at: row.try_get("locked_at")?,
        })
    }
}