//! 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 SQL with each `SELECT value FROM json_each($n)` (the ids bound as a

/// JSON array) in this engine's words: Postgres has `json_array_elements_text`.

fn each(sql: &'static str) -> std::borrow::Cow<'static, str> {

    if cfg!(feature = "postgres") {

        let mut out = sql.to_string();

        for n in 1..=4 {

            out = out.replace(

                &format!("SELECT value FROM json_each(${n})"),

                &format!("SELECT CAST(value AS BIGINT) FROM json_array_elements_text(CAST(${n} AS json)) AS t(value)"),

            );

        }

        out.into()

    } else {

        sql.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(

        &each("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 = TRUE \

           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 lower(ev.actor) = lower($3)) \

         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(&each(

        "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(&each(

        "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(&each(

        "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(

        &each("WITH seen(id) AS (SELECT value FROM json_each($1)), ev AS ( \

           SELECT repo_id, at_ms, CAST(0 AS BIGINT) AS kind, CASE WHEN changes > 1 THEN changes ELSE 1 END AS n FROM pushes WHERE ok = TRUE \

           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(&each(

        "SELECT repo_id, at_ms, changes, via FROM pushes \

         WHERE account_id = $1 AND ok = TRUE 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(&each(

        "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

}

/// The newest Flow runs of `repo_ids`, created before `before_ms`, at most

/// `limit` (oxygenhub-android#26: the app's Flow monitor).

pub async fn recent_runs(pool: &Pool, repo_ids: &[i64], before_ms: i64, limit: i64) -> sqlx::Result<Vec<super::flow::Run>> {

    sqlx::query_as(&each(

        "WITH seen(id) AS (SELECT value FROM json_each($1)) \

         SELECT * FROM flow_runs WHERE repo_id IN (SELECT id FROM seen) AND created_ms < $2 \

         ORDER BY created_ms DESC, id DESC LIMIT $3",

    ))

    .bind(ids_json(repo_ids))

    .bind(before_ms)

    .bind(limit)

    .fetch_all(pool)

    .await

}