//! Cross-repository reads for the signed-in dashboard (hub spec §6):
//! the activity feed and the proposals waiting on a reviewer. Callers
//! pass the repository ids the viewer may see, so nothing here decides
//! access.

use super::Pool;
use sqlx::FromRow;

/// One line of the activity feed.
#[derive(Clone, Debug, FromRow)]
pub struct Activity {
    pub owner: String,
    pub name: String,
    pub at_ms: i64,
    pub actor: String,
    /// `push`, `release`, `proposal` or `issue`.
    pub kind: String,
    /// What happened: the event name, or the bookmark for a push.
    pub event: String,
    /// The proposal or issue title, the release tag.
    pub title: String,
    /// The proposal or issue number, 0 otherwise.
    pub number: i64,
}

impl Activity {
    /// Where the line links to.
    pub fn url(&self) -> String {
        let base = format!("/{}/{}", self.owner, self.name);
        match self.kind.as_str() {
            "proposal" => format!("{base}/proposal/{}", self.number),
            "issue" => format!("{base}/issue/{}", self.number),
            "release" => format!("{base}/releases/tag/{}", self.title),
            _ => format!("{base}/pushes"),
        }
    }
}

fn ids_json(ids: &[i64]) -> String {
    serde_json::to_string(ids).unwrap_or_else(|_| "[]".into())
}

/// The newest `limit` events across the repositories in `repo_ids`.
pub async fn feed(pool: &Pool, repo_ids: &[i64], limit: i64) -> sqlx::Result<Vec<Activity>> {
    feed_by(pool, repo_ids, None, limit).await
}

/// Like `feed`, only what `actor` did when one is given (profile pages).
pub async fn feed_by(pool: &Pool, repo_ids: &[i64], actor: Option<&str>, limit: i64) -> sqlx::Result<Vec<Activity>> {
    sqlx::query_as(
        "WITH seen(id) AS (SELECT value FROM json_each($1)), ev AS ( \
           SELECT repo_id, at_ms, pusher AS actor, 'push' AS kind, reference AS event, '' AS title, 0 AS number FROM pushes WHERE ok = 1 \
           UNION ALL SELECT repo_id, created_ms, author, 'release', 'published', tag, 0 FROM releases \
           UNION ALL SELECT p.repo_id, e.at_ms, e.actor, 'proposal', e.event, p.title, p.number FROM proposal_events e JOIN proposals p ON p.id = e.proposal_id \
           UNION ALL SELECT i.repo_id, e.at_ms, e.actor, 'issue', e.event, i.title, i.number FROM issue_events e JOIN issues i ON i.id = e.issue_id) \
         SELECT r.owner, r.name, ev.at_ms, ev.actor, ev.kind, ev.event, ev.title, ev.number \
         FROM ev JOIN repositories r ON r.id = ev.repo_id WHERE ev.repo_id IN (SELECT id FROM seen) \
           AND ($2 IS NULL OR ev.actor = $3 COLLATE NOCASE) \
         ORDER BY ev.at_ms DESC LIMIT $4",
    )
    .bind(ids_json(repo_ids))
    .bind(actor)
    .bind(actor)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// A proposal on the dashboard, with its repository's name.
#[derive(Clone, Debug, FromRow)]
pub struct ProposalLine {
    pub owner: String,
    pub name: String,
    pub number: i64,
    pub title: String,
    pub author: String,
    pub state: String,
    pub conflicted: bool,
    pub updated_ms: i64,
}

/// Open proposals in `repo_ids` by someone else that `account_id` has
/// not reviewed yet: the review queue.
pub async fn awaiting_review(pool: &Pool, account_id: i64, repo_ids: &[i64]) -> sqlx::Result<Vec<ProposalLine>> {
    sqlx::query_as(
        "SELECT r.owner, r.name, p.number, p.title, p.author, p.state, p.conflicted, p.updated_ms \
         FROM proposals p JOIN repositories r ON r.id = p.repo_id \
         WHERE p.repo_id IN (SELECT value FROM json_each($1)) AND p.state = 'open' \
           AND (p.author_id IS NULL OR p.author_id != $2) \
           AND NOT EXISTS (SELECT 1 FROM proposal_reviews v WHERE v.proposal_id = p.id AND v.author_id = $3) \
         ORDER BY p.updated_ms DESC",
    )
    .bind(ids_json(repo_ids))
    .bind(account_id)
    .bind(account_id)
    .fetch_all(pool)
    .await
}

/// `account_id`'s own open and draft proposals in repositories it can
/// still see.
pub async fn authored(pool: &Pool, account_id: i64, repo_ids: &[i64]) -> sqlx::Result<Vec<ProposalLine>> {
    sqlx::query_as(
        "SELECT r.owner, r.name, p.number, p.title, p.author, p.state, p.conflicted, p.updated_ms \
         FROM proposals p JOIN repositories r ON r.id = p.repo_id \
         WHERE p.author_id = $1 AND p.state IN ('open', 'draft') AND p.repo_id IN (SELECT value FROM json_each($2)) \
         ORDER BY p.updated_ms DESC",
    )
    .bind(account_id)
    .bind(ids_json(repo_ids))
    .fetch_all(pool)
    .await
}

/// An issue on the dashboard, with its repository's name.
#[derive(Clone, Debug, FromRow)]
pub struct IssueLine {
    pub owner: String,
    pub name: String,
    pub number: i64,
    pub title: String,
    pub updated_ms: i64,
}

/// Open issues assigned to `account_id`, in repositories it can see.
pub async fn assigned(pool: &Pool, account_id: i64, repo_ids: &[i64]) -> sqlx::Result<Vec<IssueLine>> {
    sqlx::query_as(
        "SELECT r.owner, r.name, i.number, i.title, i.updated_ms \
         FROM issues i JOIN issue_assignees a ON a.issue_id = i.id JOIN repositories r ON r.id = i.repo_id \
         WHERE a.account_id = $1 AND i.state = 'open' AND i.repo_id IN (SELECT value FROM json_each($2)) \
         ORDER BY i.updated_ms DESC",
    )
    .bind(account_id)
    .bind(ids_json(repo_ids))
    .fetch_all(pool)
    .await
}

/// One event of Your sky (docs/backgrounds.md §5): its repository, when,
/// its kind (0 a push, 1 a proposal event, 2 an issue event, 3 a release,
/// 4 a Flow run) and how many marks it makes: a push's changes, else one.
#[derive(Clone, Debug, FromRow)]
pub struct SkyEvent {
    pub repo_id: i64,
    pub at_ms: i64,
    pub kind: i64,
    pub n: i64,
}

/// The events since `since_ms` in `repo_ids`, newest first, at most `limit`.
pub async fn sky(pool: &Pool, repo_ids: &[i64], since_ms: i64, limit: i64) -> sqlx::Result<Vec<SkyEvent>> {
    sqlx::query_as(
        "WITH seen(id) AS (SELECT value FROM json_each($1)), ev AS ( \
           SELECT repo_id, at_ms, 0 AS kind, MAX(changes, 1) AS n FROM pushes WHERE ok = 1 \
           UNION ALL SELECT p.repo_id, e.at_ms, 1, 1 FROM proposal_events e JOIN proposals p ON p.id = e.proposal_id \
           UNION ALL SELECT i.repo_id, e.at_ms, 2, 1 FROM issue_events e JOIN issues i ON i.id = e.issue_id \
           UNION ALL SELECT repo_id, created_ms, 3, 1 FROM releases \
           UNION ALL SELECT repo_id, created_ms, 4, 1 FROM flow_runs) \
         SELECT repo_id, at_ms, kind, n FROM ev WHERE repo_id IN (SELECT id FROM seen) AND at_ms >= $2 \
         ORDER BY at_ms DESC LIMIT $3",
    )
    .bind(ids_json(repo_ids))
    .bind(since_ms)
    .bind(limit)
    .fetch_all(pool)
    .await
}

/// One account's pushes of the last year in the repositories a viewer may
/// see, for the profile's pulse (#217): `(repo_id, at_ms, changes, via)`.
/// Landings are left out (their changes were pushed once already), as are
/// failed pushes and pushes that brought nothing.
pub async fn pulse(pool: &Pool, account_id: i64, repo_ids: &[i64], since_ms: i64) -> sqlx::Result<Vec<(i64, i64, i64, String)>> {
    let ids = serde_json::to_string(repo_ids).unwrap_or_else(|_| "[]".into());
    sqlx::query_as(
        "SELECT repo_id, at_ms, changes, via FROM pushes \
         WHERE account_id = $1 AND ok = 1 AND changes > 0 AND at_ms >= $2 AND reason NOT LIKE 'landed%' \
           AND repo_id IN (SELECT value FROM json_each($3)) ORDER BY at_ms",
    )
    .bind(account_id)
    .bind(since_ms)
    .bind(ids)
    .fetch_all(pool)
    .await
}

/// Releases an account made in the last year in repositories a viewer may
/// see: `(repo_id, tag, created_ms)`, to label the pulse's peaks.
pub async fn releases_by(pool: &Pool, account_id: i64, repo_ids: &[i64], since_ms: i64) -> sqlx::Result<Vec<(i64, String, i64)>> {
    let ids = serde_json::to_string(repo_ids).unwrap_or_else(|_| "[]".into());
    sqlx::query_as(
        "SELECT repo_id, tag, created_ms FROM releases WHERE author_id = $1 AND created_ms >= $2 \
           AND repo_id IN (SELECT value FROM json_each($3)) ORDER BY created_ms",
    )
    .bind(account_id)
    .bind(since_ms)
    .bind(ids)
    .fetch_all(pool)
    .await
}