//! The `repositories` and `pushes` tables.

use super::{now_ms, Pool};

use hub_api::Visibility;

use sqlx::FromRow;

#[derive(Clone, Debug, Default, FromRow)]

pub struct RepoRow {

    pub id: i64,

    pub owner: String,

    pub name: String,

    pub description: String,

    pub default_bookmark: String,

    pub visibility: String,

    pub created_ms: i64,

    pub updated_ms: i64,

    #[sqlx(default)]

    pub git_mirror: bool,

    #[sqlx(default)]

    pub git_mirror_state: String,

    /// The repository this one was forked from (migration 0009).

    #[sqlx(default)]

    pub forked_from: Option<i64>,

    /// Space-separated topics (migration 0010).

    #[sqlx(default)]

    pub topics: String,

    /// OxygenFlow: `on`, `off`, or empty to follow hub.toml (migration 0018).

    #[sqlx(default)]

    pub flow: String,

    /// Its jobs' priority: `low`, `normal`, `high`, or empty to follow its

    /// organisation's and the site's (oxygenhub#244, migration 0073).

    #[sqlx(default)]

    pub flow_priority: String,

    /// Diffusion: `off`, `green`, `any`, or empty to follow hub.toml

    /// (migration 0058, #148).

    #[sqlx(default)]

    pub diffusion: String,

    /// Diffusion auto-land: `auto`, `propose`, or empty for propose

    /// (migration 0061).

    #[sqlx(default)]

    pub diffusion_land: String,

    /// Backups (migration 0062): empty with its owner's, `off`, or its own

    /// `hourly` or `daily`.

    #[sqlx(default)]

    pub backup: String,

    /// Read-only, with why (migration 0040, #108).

    #[sqlx(default)]

    pub archived: bool,

    #[sqlx(default)]

    pub archived_reason: String,

    /// Agents' tokens may approve or reject its approval gates (#258).

    #[sqlx(default)]

    pub agents_decide_gates: bool,

    /// Newest run wins (#267): a superseded run already running finishes;

    /// only queued ones are cancelled. Off by default.

    #[sqlx(default)]

    pub flow_keep_running: bool,

    /// Where a push may skip its run with `[skip flow]` (#284, migration

    /// 0088): `any` bookmark, `others` than the default one, or `never`.

    #[sqlx(default)]

    pub flow_skip: String,

    /// A push run reuses a job's pass on the same state (#265, migration

    /// 0090). On by default.

    #[sqlx(default)]

    pub flow_reuse: bool,

    /// Pushes that add possible secrets are refused (#268, migration

    /// 0085). On by default.

    #[sqlx(default)]

    pub push_protection: bool,

    /// `repo`, or `drop` for a drop (#279, migration 0087): a small

    /// repository named `drops/ID` that no repository list shows.

    #[sqlx(default)]

    pub kind: String,

    /// A drop's title.

    #[sqlx(default)]

    pub title: String,

    /// Counts for the tabs; filled by `get` only.

    #[sqlx(default)]

    pub open_issues: i64,

    #[sqlx(default)]

    pub open_proposals: i64,

}

impl RepoRow {

    pub fn visibility(&self) -> Visibility {

        Visibility::parse(&self.visibility).unwrap_or(Visibility::Private)

    }

    pub fn topic_list(&self) -> Vec<&str> {

        self.topics.split_whitespace().collect()

    }

    pub fn slug(&self) -> String {

        format!("{}/{}", self.owner, self.name)

    }

    /// A drop (#279), not a repository.

    pub fn is_drop(&self) -> bool {

        self.kind == DROP

    }

}

/// `repositories.kind` of a drop (#279).

pub const DROP: &str = "drop";

#[derive(Clone, Debug, FromRow)]

pub struct PushRow {

    pub id: i64,

    pub at_ms: i64,

    pub reference: String,

    pub ok: bool,

    pub reason: String,

    pub old_state: String,

    pub new_state: String,

    pub heads: i64,

    pub changes: i64,

    pub pusher: String,

}

/// One reference's outcome in a push, as the protocol backend reports it.

#[derive(Clone, Debug)]

pub struct PushOutcome {

    pub reference: String,

    pub ok: bool,

    pub reason: String,

    pub old_state: String,

    pub new_state: String,

    pub heads: i64,

    pub changes: i64,

}

/// Every repository, drops left out: what every list, search and count

/// of repositories starts from (#279).

pub async fn list(pool: &Pool) -> sqlx::Result<Vec<RepoRow>> {

    sqlx::query_as("SELECT * FROM repositories WHERE kind = 'repo' ORDER BY updated_ms DESC, owner, name").fetch_all(pool).await

}

/// Every repository and every drop: for upkeep that covers all stored

/// repositories (gc, the history scan, `oxygenhub check`), never a list

/// people see unless it labels the drops.

pub async fn list_with_drops(pool: &Pool) -> sqlx::Result<Vec<RepoRow>> {

    sqlx::query_as("SELECT * FROM repositories ORDER BY updated_ms DESC, owner, name").fetch_all(pool).await

}

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

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

}

pub async fn list_by_owner(pool: &Pool, owner: &str) -> sqlx::Result<Vec<RepoRow>> {

    sqlx::query_as("SELECT * FROM repositories WHERE owner = $1 AND kind = 'repo' ORDER BY updated_ms DESC, name")

        .bind(owner)

        .fetch_all(pool)

        .await

}

// ------------------------------------------------------------------ drops

/// A drop's row (#279): a repository named `drops/ID`, its kind `drop`,

/// push protection on and Flow off. `visibility` is `public`, or

/// `private` for a secret drop.

pub async fn create_drop(

    pool: &Pool,

    owner: &str,

    id: &str,

    title: &str,

    description: &str,

    visibility: Visibility,

) -> sqlx::Result<RepoRow> {

    let now = now_ms();

    sqlx::query_as(

        "INSERT INTO repositories (owner, name, description, visibility, created_ms, updated_ms, kind, title, flow) \

         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'off') RETURNING *",

    )

    .bind(owner)

    .bind(format!("{}/{id}", crate::repos::names::DROPS))

    .bind(description)

    .bind(visibility.as_str())

    .bind(now)

    .bind(now)

    .bind(DROP)

    .bind(title)

    .fetch_one(pool)

    .await

}

/// A comment on a drop (#280).

#[derive(Clone, Debug, FromRow)]

pub struct DropComment {

    pub id: i64,

    pub repo_id: i64,

    pub author_id: Option<i64>,

    pub author: String,

    pub body: String,

    pub created_ms: i64,

}

pub async fn add_drop_comment(pool: &Pool, repo_id: i64, author_id: i64, author: &str, body: &str) -> sqlx::Result<i64> {

    sqlx::query_scalar("INSERT INTO drop_comments (repo_id, author_id, author, body, created_ms) VALUES ($1, $2, $3, $4, $5) RETURNING id")

        .bind(repo_id)

        .bind(author_id)

        .bind(author)

        .bind(body)

        .bind(now_ms())

        .fetch_one(pool)

        .await

}

/// A drop's comments, oldest first.

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

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

}

pub async fn drop_comment(pool: &Pool, repo_id: i64, id: i64) -> sqlx::Result<Option<DropComment>> {

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

}

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

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

    Ok(())

}

/// An owner's drops, newest activity first.

pub async fn drops_of(pool: &Pool, owner: &str) -> sqlx::Result<Vec<RepoRow>> {

    sqlx::query_as("SELECT * FROM repositories WHERE kind = 'drop' AND owner = $1 ORDER BY updated_ms DESC, id DESC")

        .bind(owner)

        .fetch_all(pool)

        .await

}

/// The drop with this id, whoever owns it: ids are random and made

/// unique across the hub.

pub async fn drop_by_id(pool: &Pool, id: &str) -> sqlx::Result<Option<RepoRow>> {

    sqlx::query_as("SELECT * FROM repositories WHERE kind = 'drop' AND name = $1")

        .bind(format!("{}/{id}", crate::repos::names::DROPS))

        .fetch_optional(pool)

        .await

}

/// A drop's title, description and visibility, and that it changed.

pub async fn set_drop(pool: &Pool, row_id: i64, title: &str, description: &str, visibility: Visibility) -> sqlx::Result<()> {

    sqlx::query("UPDATE repositories SET title = $1, description = $2, visibility = $3, updated_ms = $4 WHERE id = $5 AND kind = 'drop'")

        .bind(title)

        .bind(description)

        .bind(visibility.as_str())

        .bind(now_ms())

        .bind(row_id)

        .execute(pool)

        .await?;

    Ok(())

}

/// One repository, with its open issue and proposal counts for the tabs.

pub async fn get(pool: &Pool, owner: &str, name: &str) -> sqlx::Result<Option<RepoRow>> {

    sqlx::query_as(

        "SELECT r.*, \

           (SELECT COUNT(*) FROM issues i WHERE i.repo_id = r.id AND i.state = 'open') AS open_issues, \

           (SELECT COUNT(*) FROM proposals p WHERE p.repo_id = r.id AND p.state IN ('open', 'draft')) AS open_proposals \

         FROM repositories r WHERE r.owner = $1 AND r.name = $2",

    )

    .bind(owner)

    .bind(name)

    .fetch_optional(pool)

    .await

}

/// Create a repository row. One under an organisation of two or more

/// members requires one sealed approval to land a proposal (oxygenhub#64),

/// since someone other than the author can then give it; others require none.

pub async fn create(pool: &Pool, owner: &str, name: &str, description: &str, visibility: Visibility) -> sqlx::Result<RepoRow> {

    let now = now_ms();

    sqlx::query_as(

        "INSERT INTO repositories (owner, name, description, visibility, created_ms, updated_ms, required_approvals) \

         VALUES ($1, $2, $3, $4, $5, $6, (SELECT CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END FROM org_members m JOIN organisations o ON o.id = m.org_id WHERE o.name = $7)) RETURNING *",

    )

    .bind(owner)

    .bind(name)

    .bind(description)

    .bind(visibility.as_str())

    .bind(now)

    .bind(now)

    .bind(owner)

    .fetch_one(pool)

    .await

}

/// The repository an old name now points at, after a transfer.

pub async fn moved_to(pool: &Pool, owner: &str, name: &str) -> sqlx::Result<Option<RepoRow>> {

    let id: Option<i64> = sqlx::query_scalar("SELECT repo_id FROM repo_redirects WHERE old_owner = $1 AND old_name = $2")

        .bind(owner)

        .bind(name)

        .fetch_optional(pool)

        .await?;

    match id {

        Some(id) => get_by_id(pool, id).await,

        None => Ok(None),

    }

}

/// Move a repository to another owner, keeping its name: the row, and a

/// redirect from the old name. A redirect that pointed at the new name

/// goes, so names never loop.

pub async fn transfer(pool: &Pool, id: i64, old_owner: &str, name: &str, new_owner: &str) -> sqlx::Result<()> {

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

        .bind(new_owner)

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    sqlx::query("DELETE FROM repo_redirects WHERE old_owner = $1 AND old_name = $2").bind(new_owner).bind(name).execute(pool).await?;

    sqlx::query("INSERT INTO repo_redirects (old_owner, old_name, repo_id, moved_ms) VALUES ($1, $2, $3, $4) ON CONFLICT (old_owner, old_name) DO UPDATE SET repo_id = excluded.repo_id, moved_ms = excluded.moved_ms")

        .bind(old_owner)

        .bind(name)

        .bind(id)

        .bind(now_ms())

        .execute(pool)

        .await?;

    Ok(())

}

/// Archive (read-only) or bring back a repository.

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

    sqlx::query("UPDATE repositories SET archived = $1, archived_reason = $2, updated_ms = $3 WHERE id = $4")

        .bind(archived)

        .bind(if archived { reason } else { "" })

        .bind(now_ms())

        .bind(id)

        .execute(pool)

        .await?;

    Ok(())

}

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

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

    Ok(())

}

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

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

    Ok(())

}

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

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

    Ok(())

}

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

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

    Ok(())

}

/// Record a push's per-reference results and bump the repository's activity.

pub async fn record_push(pool: &Pool, id: i64, pusher: Option<(i64, &str)>, results: &[PushOutcome]) -> sqlx::Result<()> {

    record_push_via(pool, id, pusher, "", results).await

}

/// [`record_push`], naming the agent token the push came through (#217).

pub async fn record_push_via(pool: &Pool, id: i64, pusher: Option<(i64, &str)>, via: &str, results: &[PushOutcome]) -> sqlx::Result<()> {

    let now = now_ms();

    let mut tx = super::begin_write(pool).await?;

    for r in results {

        sqlx::query("INSERT INTO pushes (repo_id, at_ms, reference, ok, reason, old_state, new_state, heads, changes, account_id, pusher, via) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)")

            .bind(id)

            .bind(now)

            .bind(&r.reference)

            .bind(r.ok)

            .bind(&r.reason)

            .bind(&r.old_state)

            .bind(&r.new_state)

            .bind(r.heads)

            .bind(r.changes)

            .bind(pusher.map(|p| p.0).filter(|id| *id > 0))

            .bind(pusher.map(|p| p.1).unwrap_or(""))

            .bind(via)

            .execute(&mut *tx)

            .await?;

    }

    if results.iter().any(|r| r.ok) {

        sqlx::query("UPDATE repositories SET updated_ms = $1 WHERE id = $2").bind(now).bind(id).execute(&mut *tx).await?;

    }

    tx.commit().await

}

pub async fn pushes(pool: &Pool, id: i64, limit: i64) -> sqlx::Result<Vec<PushRow>> {

    sqlx::query_as("SELECT id, at_ms, reference, ok, reason, old_state, new_state, heads, changes, pusher FROM pushes WHERE repo_id = $1 ORDER BY at_ms DESC, id DESC LIMIT $2")

        .bind(id)

        .bind(limit)

        .fetch_all(pool)

        .await

}

/// Topics from user input: comma or space separated, lower-cased,

/// letters, digits and dashes, at most 35 characters each and 20 in all.

/// Returns the stored form or what is wrong.

pub fn parse_topics(input: &str) -> Result<String, String> {

    let mut out: Vec<String> = Vec::new();

    for t in input.split([',', ' ', '\n', '\t']).map(|t| t.trim().to_ascii_lowercase()).filter(|t| !t.is_empty()) {

        if t.len() > 35 || !t.chars().all(|c| c.is_ascii_alphanumeric() || c == '-') || t.starts_with('-') {

            return Err(format!("\"{t}\" is not a topic: use letters, digits and dashes, up to 35 characters"));

        }

        if !out.contains(&t) {

            out.push(t);

        }

    }

    if out.len() > 20 {

        return Err("At most 20 topics.".into());

    }

    Ok(out.join(" "))

}

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

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

    Ok(())

}

/// Star or unstar; returns whether it is now starred.

pub async fn toggle_star(pool: &Pool, account_id: i64, repo_id: i64) -> sqlx::Result<bool> {

    let gone = sqlx::query("DELETE FROM stars WHERE account_id = $1 AND repo_id = $2").bind(account_id).bind(repo_id).execute(pool).await?;

    if gone.rows_affected() > 0 {

        return Ok(false);

    }

    sqlx::query("INSERT INTO stars (account_id, repo_id, at_ms) VALUES ($1, $2, $3)")

        .bind(account_id)

        .bind(repo_id)

        .bind(now_ms())

        .execute(pool)

        .await?;

    Ok(true)

}

pub async fn starred(pool: &Pool, account_id: i64, repo_id: i64) -> sqlx::Result<bool> {

    let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM stars WHERE account_id = $1 AND repo_id = $2")

        .bind(account_id)

        .bind(repo_id)

        .fetch_one(pool)

        .await?;

    Ok(n > 0)

}

/// Stars and watchers of one repository.

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

    let stars: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM stars WHERE repo_id = $1").bind(repo_id).fetch_one(pool).await?;

    let watchers = super::notify::watchers(pool, repo_id).await?.len() as i64;

    Ok((stars, watchers))

}

/// Star counts of every starred repository.

pub async fn star_counts(pool: &Pool) -> sqlx::Result<std::collections::HashMap<i64, i64>> {

    let rows: Vec<(i64, i64)> = sqlx::query_as("SELECT repo_id, COUNT(*) FROM stars GROUP BY repo_id").fetch_all(pool).await?;

    Ok(rows.into_iter().collect())

}

/// Record that `id` is a fork of `from`.

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

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

    Ok(())

}

/// The forks of `id`, newest activity first.

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

    sqlx::query_as("SELECT * FROM repositories WHERE forked_from = $1 AND kind = 'repo' ORDER BY updated_ms DESC")

        .bind(id)

        .fetch_all(pool)

        .await

}

/// `on`, `off`, or `` (follow hub.toml's `[flow] repos`).

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

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

    Ok(())

}

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

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

    Ok(())

}

/// Whether agents' tokens may decide the repository's approval gates (#258).

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

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

    Ok(())

}

/// Whether a new run leaves the older runs it supersedes that are already

/// running to finish (#267), cancelling only the queued ones.

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

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

    Ok(())

}

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

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

    Ok(())

}

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

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

    Ok(())

}

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

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

    Ok(())

}

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

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

    Ok(())

}

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