Milestone 0.4: issues and notifications 18e381ea88c2

-- Issues, labels, milestones, notifications, watching and search

-- (hub spec §8, §10, §11).

CREATE TABLE issues (

    id           INTEGER PRIMARY KEY,

    repo_id      INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    number       INTEGER NOT NULL,

    title        TEXT NOT NULL,

    body         TEXT NOT NULL DEFAULT '',

    author_id    INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author       TEXT NOT NULL DEFAULT '',

    state        TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),

    reason       TEXT NOT NULL DEFAULT '' CHECK (reason IN ('', 'completed', 'not_planned')),

    locked       INTEGER NOT NULL DEFAULT 0,

    pinned       INTEGER NOT NULL DEFAULT 0,

    milestone_id INTEGER REFERENCES milestones(id) ON DELETE SET NULL,

    created_ms   INTEGER NOT NULL,

    updated_ms   INTEGER NOT NULL,

    closed_ms    INTEGER,

    UNIQUE (repo_id, number)

);

CREATE TABLE issue_comments (

    id         INTEGER PRIMARY KEY,

    issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    author_id  INTEGER REFERENCES accounts(id) ON DELETE SET NULL,

    author     TEXT NOT NULL DEFAULT '',

    body       TEXT NOT NULL,

    created_ms INTEGER NOT NULL,

    edited_ms  INTEGER

);

CREATE INDEX issue_comments_by_issue ON issue_comments (issue_id, created_ms);

CREATE TABLE issue_events (

    id       INTEGER PRIMARY KEY,

    issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    at_ms    INTEGER NOT NULL,

    actor    TEXT NOT NULL DEFAULT '',

    event    TEXT NOT NULL,

    detail   TEXT NOT NULL DEFAULT ''

);

CREATE TABLE labels (

    id          INTEGER PRIMARY KEY,

    repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    name        TEXT NOT NULL COLLATE NOCASE,

    colour      TEXT NOT NULL DEFAULT '#0072ff',

    description TEXT NOT NULL DEFAULT '',

    UNIQUE (repo_id, name)

);

CREATE TABLE issue_labels (

    issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,

    PRIMARY KEY (issue_id, label_id)

);

CREATE TABLE issue_assignees (

    issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    PRIMARY KEY (issue_id, account_id)

);

CREATE TABLE milestones (

    id          INTEGER PRIMARY KEY,

    repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    title       TEXT NOT NULL,

    description TEXT NOT NULL DEFAULT '',

    due_ms      INTEGER,

    state       TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),

    UNIQUE (repo_id, title)

);

-- Proposals get labels too (hub spec §7.1).

CREATE TABLE proposal_labels (

    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,

    label_id    INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,

    PRIMARY KEY (proposal_id, label_id)

);

-- The inbox (hub spec §10).

CREATE TABLE notifications (

    id         INTEGER PRIMARY KEY,

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    at_ms      INTEGER NOT NULL,

    kind       TEXT NOT NULL,

    title      TEXT NOT NULL,

    url        TEXT NOT NULL,

    actor      TEXT NOT NULL DEFAULT '',

    read       INTEGER NOT NULL DEFAULT 0

);

CREATE INDEX notifications_by_account ON notifications (account_id, read, at_ms DESC);

-- Watching a repository subscribes to all its events; participating in a

-- thread subscribes to it.

CREATE TABLE watches (

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,

    PRIMARY KEY (account_id, repo_id)

);

CREATE TABLE subscriptions (

    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

    kind       TEXT NOT NULL CHECK (kind IN ('issue', 'proposal')),

    target_id  INTEGER NOT NULL,

    PRIMARY KEY (account_id, kind, target_id)

);

-- Per-account notification preferences.

ALTER TABLE accounts ADD COLUMN email_notifications INTEGER NOT NULL DEFAULT 0;

ALTER TABLE accounts ADD COLUMN unsubscribe_key TEXT NOT NULL DEFAULT '';

-- Full-text search over issues, proposals and their comments (§11).

CREATE VIRTUAL TABLE search USING fts5(kind UNINDEXED, repo_id UNINDEXED, target_id UNINDEXED, number UNINDEXED, title, body, tokenize = 'porter unicode61');

//! 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 (?, ?, ?, ?, ?, ?, ?, ?) 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 = ? AND number = ?").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 = ?").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 = ? 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 = ?").bind(repo_id).fetch_all(pool).await?;

    let proposals: Vec<i64> = sqlx::query_scalar("SELECT number FROM proposals WHERE repo_id = ?").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 = ? 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 = ?, body = ?, updated_ms = ? WHERE id = ?")

        .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 = ?, reason = ?, closed_ms = ?, updated_ms = ? WHERE id = ?")

        .bind(state)

        .bind(reason)

        .bind(closed)

        .bind(now)

        .bind(id)

        .execute(pool)

        .await?;

    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 = ? WHERE id = ?").bind(l).bind(id).execute(pool).await?;

    }

    if let Some(p) = pinned {

        sqlx::query("UPDATE issues SET pinned = ? WHERE id = ?").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 = ?, updated_ms = ? WHERE id = ?")

        .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 = ? WHERE id = ?").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 = ? 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 (?, ?, ?, ?, ?) 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 = ?, edited_ms = ? WHERE id = ? AND author_id = ?")

        .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 = ?").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 = ? ORDER BY at_ms, id")

        .bind(issue_id)

        .fetch_all(pool)

        .await

}

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 (?, ?, ?, ?, ?)")

        .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 = ? 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 = ? AND name = ?").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 (?, ?, ?, ?) 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 = ?").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 = ? 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 OR IGNORE INTO issue_labels (issue_id, label_id) VALUES (?, ?)")

            .bind(issue_id)

            .bind(label_id)

            .execute(pool)

            .await?;

    } else {

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

    }

    Ok(())

}

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

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 = ? 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 = ? 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 OR IGNORE INTO proposal_labels (proposal_id, label_id) VALUES (?, ?)")

            .bind(proposal_id)

            .bind(label_id)

            .execute(pool)

            .await?;

    } else {

        sqlx::query("DELETE FROM proposal_labels WHERE proposal_id = ? AND label_id = ?")

            .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 = ? 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 OR IGNORE INTO issue_assignees (issue_id, account_id) VALUES (?, ?)")

            .bind(issue_id)

            .bind(account_id)

            .execute(pool)

            .await?;

    } else {

        sqlx::query("DELETE FROM issue_assignees WHERE issue_id = ? AND account_id = ?")

            .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 = ?")

        .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 = ? 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 = ?").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 (?, ?, ?, ?) 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

}

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

    sqlx::query("UPDATE milestones SET state = ? WHERE id = ?").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 = ? AND state = 'open'")

        .bind(milestone_id)

        .fetch_one(pool)

        .await?;

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

        .bind(milestone_id)

        .fetch_one(pool)

        .await?;

    Ok((open, closed))

}

//! Notifications, watching, subscriptions and the search index (hub spec

//! §10, §11).

use super::{now_ms, Pool};

use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]

pub struct Notification {

    pub id: i64,

    pub account_id: i64,

    pub at_ms: i64,

    pub kind: String,

    pub title: String,

    pub url: String,

    pub actor: String,

    pub read: bool,

}

pub async fn add(pool: &Pool, account_id: i64, kind: &str, title: &str, url: &str, actor: &str) -> sqlx::Result<()> {

    sqlx::query("INSERT INTO notifications (account_id, at_ms, kind, title, url, actor) VALUES (?, ?, ?, ?, ?, ?)")

        .bind(account_id)

        .bind(now_ms())

        .bind(kind)

        .bind(title)

        .bind(url)

        .bind(actor)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn inbox(pool: &Pool, account_id: i64, limit: i64) -> sqlx::Result<Vec<Notification>> {

    sqlx::query_as("SELECT * FROM notifications WHERE account_id = ? ORDER BY read, at_ms DESC, id DESC LIMIT ?")

        .bind(account_id)

        .bind(limit)

        .fetch_all(pool)

        .await

}

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

    sqlx::query_scalar("SELECT COUNT(*) FROM notifications WHERE account_id = ? AND read = 0").bind(account_id).fetch_one(pool).await

}

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

    match id {

        Some(id) => {

            sqlx::query("UPDATE notifications SET read = 1 WHERE account_id = ? AND id = ?").bind(account_id).bind(id).execute(pool).await?

        }

        None => sqlx::query("UPDATE notifications SET read = 1 WHERE account_id = ?").bind(account_id).execute(pool).await?,

    };

    Ok(())

}

// ---------------------------------------------------------------- watching

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

    let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM watches WHERE account_id = ? AND repo_id = ?")

        .bind(account_id)

        .bind(repo_id)

        .fetch_one(pool)

        .await?;

    Ok(n > 0)

}

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

    if on {

        sqlx::query("INSERT OR IGNORE INTO watches (account_id, repo_id) VALUES (?, ?)")

            .bind(account_id)

            .bind(repo_id)

            .execute(pool)

            .await?;

    } else {

        sqlx::query("DELETE FROM watches WHERE account_id = ? AND repo_id = ?").bind(account_id).bind(repo_id).execute(pool).await?;

    }

    Ok(())

}

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

    sqlx::query_scalar("SELECT account_id FROM watches WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await

}

pub async fn subscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {

    sqlx::query("INSERT OR IGNORE INTO subscriptions (account_id, kind, target_id) VALUES (?, ?, ?)")

        .bind(account_id)

        .bind(kind)

        .bind(target_id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn unsubscribe(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM subscriptions WHERE account_id = ? AND kind = ? AND target_id = ?")

        .bind(account_id)

        .bind(kind)

        .bind(target_id)

        .execute(pool)

        .await?;

    Ok(())

}

pub async fn subscribed(pool: &Pool, account_id: i64, kind: &str, target_id: i64) -> sqlx::Result<bool> {

    let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM subscriptions WHERE account_id = ? AND kind = ? AND target_id = ?")

        .bind(account_id)

        .bind(kind)

        .bind(target_id)

        .fetch_one(pool)

        .await?;

    Ok(n > 0)

}

pub async fn subscribers(pool: &Pool, kind: &str, target_id: i64) -> sqlx::Result<Vec<i64>> {

    sqlx::query_scalar("SELECT account_id FROM subscriptions WHERE kind = ? AND target_id = ?")

        .bind(kind)

        .bind(target_id)

        .fetch_all(pool)

        .await

}

// ------------------------------------------------------------------ search

#[derive(Clone, Debug, FromRow)]

pub struct Hit {

    pub kind: String,

    pub repo_id: i64,

    pub target_id: i64,

    pub number: i64,

    pub title: String,

    pub snippet: String,

}

/// Replace the indexed text of one thread (title plus body plus comments).

pub async fn index(pool: &Pool, kind: &str, repo_id: i64, target_id: i64, number: i64, title: &str, body: &str) -> sqlx::Result<()> {

    sqlx::query("DELETE FROM search WHERE kind = ? AND target_id = ?").bind(kind).bind(target_id).execute(pool).await?;

    sqlx::query("INSERT INTO search (kind, repo_id, target_id, number, title, body) VALUES (?, ?, ?, ?, ?, ?)")

        .bind(kind)

        .bind(repo_id)

        .bind(target_id)

        .bind(number)

        .bind(title)

        .bind(body)

        .execute(pool)

        .await?;

    Ok(())

}

/// Full-text hits, best first. The query is quoted so punctuation cannot

/// reach the FTS5 parser.

pub async fn search(pool: &Pool, query: &str, repo_id: Option<i64>, limit: i64) -> sqlx::Result<Vec<Hit>> {

    let terms: Vec<String> = query.split_whitespace().map(|t| format!("\"{}\"", t.replace('"', ""))).collect();

    if terms.is_empty() {

        return Ok(vec![]);

    }

    let q = terms.join(" ");

    let sql = "SELECT kind, repo_id, target_id, number, title, snippet(search, 5, '<mark>', '</mark>', '…', 12) AS snippet FROM search WHERE search MATCH ? AND (? IS NULL OR repo_id = ?) ORDER BY rank LIMIT ?";

    sqlx::query_as(sql).bind(q).bind(repo_id).bind(repo_id).bind(limit).fetch_all(pool).await

}

//! Notifications (hub spec §10): who to tell about an event, the inbox

//! row, and email when the account asked for it and SMTP is configured.

use crate::db::{self, accounts::Account, notify as ndb};

use crate::Shared;

use std::collections::BTreeSet;

/// An event worth telling people about.

pub struct Event<'a> {

    pub kind: &'a str,

    pub title: String,

    pub url: String,

    pub actor: &'a Account,

    pub repo_id: i64,

    /// `issue` or `proposal` and its id, for thread subscribers.

    pub thread: Option<(&'a str, i64)>,

    /// Bodies to scan for `@mentions`.

    pub mention_text: &'a str,

    /// Accounts to tell whatever their subscriptions (author, assignees).

    pub direct: Vec<i64>,

}

/// `@username` tokens in a text, unique.

pub fn mentions(text: &str) -> Vec<String> {

    let re = regex::Regex::new(r"(?:^|[^\w/])@([A-Za-z0-9][A-Za-z0-9._-]{0,63})").unwrap();

    let mut out = BTreeSet::new();

    for c in re.captures_iter(text) {

        out.insert(c[1].trim_end_matches('.').to_string());

    }

    out.into_iter().collect()

}

/// Deliver an event: subscribers of the thread, watchers of the

/// repository, mentioned accounts and direct targets, never the actor.

pub async fn deliver(app: &Shared, ev: Event<'_>) {

    let pool = &app.db;

    let mut targets: BTreeSet<i64> = BTreeSet::new();

    if let Some((kind, id)) = ev.thread {

        if let Ok(subs) = ndb::subscribers(pool, kind, id).await {

            targets.extend(subs);

        }

        // Acting on a thread subscribes the actor to it.

        ndb::subscribe(pool, ev.actor.id, kind, id).await.ok();

    }

    if let Ok(w) = ndb::watchers(pool, ev.repo_id).await {

        targets.extend(w);

    }

    for name in mentions(ev.mention_text) {

        if let Ok(Some(a)) = db::accounts::account_by_name(pool, &name).await {

            targets.insert(a.id);

        }

    }

    targets.extend(ev.direct.iter().copied());

    targets.remove(&ev.actor.id);

    for id in targets {

        ndb::add(pool, id, ev.kind, &ev.title, &ev.url, &ev.actor.username).await.ok();

        if let Ok(Some(a)) = db::accounts::account_by_id(pool, id).await {

            if a.email_notifications && !a.email.is_empty() {

                email(app, &a, &ev.title, &ev.url, &ev.actor.username).await;

            }

        }

    }

}

async fn email(app: &Shared, to: &Account, title: &str, url: &str, actor: &str) {

    let cfg = &app.config.email;

    if cfg.smtp.is_empty() || cfg.from.is_empty() {

        return;

    }

    use lettre::message::header::ContentType;

    use lettre::{AsyncSmtpTransport, AsyncTransport, Message, Tokio1Executor};

    let unsubscribe = format!("{}/settings/notifications?unsubscribe={}", app.config.base_url(), to.unsubscribe_key);

    let body = format!("{actor}: {title}\n\n{}{url}\n\nStop these emails: {unsubscribe}\n", app.config.base_url());

    let Ok(message) = Message::builder()

        .from(cfg.from.parse().unwrap_or_else(|_| "hub@localhost".parse().unwrap()))

        .to(match format!("{} <{}>", to.name(), to.email).parse() {

            Ok(m) => m,

            Err(_) => return,

        })

        .subject(format!("[{}] {title}", app.config.site.name))

        .header(ContentType::TEXT_PLAIN)

        .body(body)

    else {

        return;

    };

    let transport = match AsyncSmtpTransport::<Tokio1Executor>::from_url(&cfg.smtp) {

        Ok(t) => t.build(),

        Err(e) => {

            tracing::warn!("smtp url: {e}");

            return;

        }

    };

    if let Err(e) = transport.send(message).await {

        tracing::warn!("email to {}: {e}", to.email);

    }

}

#[cfg(test)]

mod tests {

    use super::*;

    #[test]

    fn finds_mentions() {

        assert_eq!(mentions("cc @andy and @bob. not an email [email protected], nor /@path"), vec!["andy", "bob"]);

        assert!(mentions("nothing here").is_empty());

    }

}

//! Cross references in Markdown (hub spec §8): `#12`, `owner/name#12`,

//! change hashes and `@user` become links before rendering. Text inside

//! code spans and fences is left alone.

/// Where a repository's numbers live: issues and proposals.

pub struct Numbers<'a> {

    pub repo_url: &'a str,

    pub issues: &'a [i64],

    pub proposals: &'a [i64],

}

impl Numbers<'_> {

    fn url_for(&self, n: i64) -> Option<String> {

        if self.proposals.contains(&n) {

            Some(format!("{}/proposal/{n}", self.repo_url))

        } else if self.issues.contains(&n) {

            Some(format!("{}/issue/{n}", self.repo_url))

        } else {

            None

        }

    }

}

/// Link references in `text`. Cross-repository references link to the

/// other repository's issue page, which resolves to a proposal itself.

pub fn link(text: &str, numbers: &Numbers<'_>) -> String {

    let mut out = String::with_capacity(text.len() + 64);

    let mut in_fence = false;

    for line in text.split_inclusive('\n') {

        if line.trim_start().starts_with("```") {

            in_fence = !in_fence;

            out.push_str(line);

            continue;

        }

        if in_fence {

            out.push_str(line);

            continue;

        }

        // Split on inline code spans; only even segments are prose.

        for (i, seg) in line.split('`').enumerate() {

            if i > 0 {

                out.push('`');

            }

            if i % 2 == 0 {

                out.push_str(&link_prose(seg, numbers));

            } else {

                out.push_str(seg);

            }

        }

    }

    out

}

fn link_prose(seg: &str, numbers: &Numbers<'_>) -> String {

    let re = regex::Regex::new(r"(?P<pre>^|[^\w/\[])(?:(?P<slug>[A-Za-z0-9][A-Za-z0-9._-]*/[A-Za-z0-9][A-Za-z0-9._-]*)#(?P<xn>\d+)|#(?P<n>\d+)|@(?P<user>[A-Za-z0-9][A-Za-z0-9._-]{0,63})|(?P<hash>\b[0-9a-f]{12,64}\b))").unwrap();

    re.replace_all(seg, |c: &regex::Captures| {

        let pre = &c["pre"];

        if let Some(slug) = c.name("slug") {

            let n = &c["xn"];

            return format!("{pre}[{}#{n}](/{}/issue/{n})", slug.as_str(), slug.as_str());

        }

        if let Some(n) = c.name("n") {

            let num: i64 = n.as_str().parse().unwrap_or(0);

            return match numbers.url_for(num) {

                Some(url) => format!("{pre}[#{num}]({url})"),

                None => c[0].to_string(),

            };

        }

        if let Some(u) = c.name("user") {

            let name = u.as_str().trim_end_matches('.');

            return format!("{pre}[@{name}](/{name})");

        }

        if let Some(h) = c.name("hash") {

            let hex = h.as_str();

            return format!("{pre}[{}]({}/change/{hex})", &hex[..12.min(hex.len())], numbers.repo_url);

        }

        c[0].to_string()

    })

    .into_owned()

}

/// Issue numbers a change message closes: `closes #12`, `fixes #3`,

/// `resolved #7`.

pub fn closes(message: &str) -> Vec<i64> {

    let re = regex::Regex::new(r"(?i)\b(?:close[sd]?|fix(?:e[sd])?|resolve[sd]?)\s+#(\d+)").unwrap();

    let mut out: Vec<i64> = re.captures_iter(message).filter_map(|c| c[1].parse().ok()).collect();

    out.sort();

    out.dedup();

    out

}

#[cfg(test)]

mod tests {

    use super::*;

    #[test]

    fn links_references_outside_code() {

        let n = Numbers { repo_url: "/a/b", issues: &[3], proposals: &[7] };

        let out = link("see #3 and #7 and #9, `#3` too, by @andy; a/b#3 and other/repo#5; hash deadbeefcafe1234\n```\n#3 @x\n```\n", &n);

        assert!(out.contains("[#3](/a/b/issue/3)"), "{out}");

        assert!(out.contains("[#7](/a/b/proposal/7)"), "{out}");

        assert!(out.contains(" #9,"), "unknown numbers stay: {out}");

        assert!(out.contains("`#3`"), "code spans untouched: {out}");

        assert!(out.contains("[@andy](/andy)"), "{out}");

        assert!(out.contains("[other/repo#5](/other/repo/issue/5)"), "{out}");

        assert!(out.contains("[deadbeefcafe](/a/b/change/deadbeefcafe1234)"), "{out}");

        assert!(out.contains("```\n#3 @x\n```"), "fences untouched: {out}");

    }

    #[test]

    fn closing_keywords() {

        assert_eq!(closes("Fixes #12 and closes #3, resolved #12"), vec![3, 12]);

        assert!(closes("mentions #4 only").is_empty());

    }

}

//! The inbox, notification preferences and search (hub spec §10, §11).

use super::settings::{csrf, require};

use super::{filters, Error, Page, Shell};

use crate::auth::Viewer;

use crate::db::{self, accounts, notify as ndb};

use crate::Shared;

use askama::Template;

use axum::extract::{Form, Query, State};

use axum::response::{IntoResponse, Redirect, Response};

use hub_api::Visibility;

use serde::Deserialize;

#[derive(Template)]

#[template(path = "notifications.html")]

pub struct InboxPage {

    shell: Shell,

    rows: Vec<ndb::Notification>,

    unread: i64,

    email_on: bool,

    notice: Option<String>,

}

async fn inbox_page(app: &Shared, shell: Shell, me: &accounts::Account, notice: Option<String>) -> Result<Page<InboxPage>, Error> {

    let rows = ndb::inbox(&app.db, me.id, 200).await.map_err(|e| Error::from_db(shell.clone(), e))?;

    let unread = ndb::unread(&app.db, me.id).await.map_err(|e| Error::from_db(shell.clone(), e))?;

    let fresh = accounts::account_by_id(&app.db, me.id).await.ok().flatten();

    Ok(Page(InboxPage { shell, rows, unread, email_on: fresh.map(|a| a.email_notifications).unwrap_or(false), notice }))

}

pub async fn inbox(State(app): State<Shared>, viewer: Viewer) -> Result<Page<InboxPage>, Error> {

    let shell = Shell::new(&app, "/notifications", &viewer);

    let me = require(&viewer, &shell)?;

    inbox_page(&app, shell, &me.account, None).await

}

#[derive(Deserialize)]

pub struct ReadForm {

    #[serde(rename = "_csrf")]

    csrf_token: String,

    #[serde(default)]

    id: Option<i64>,

}

pub async fn mark_read(State(app): State<Shared>, viewer: Viewer, Form(f): Form<ReadForm>) -> Result<Page<InboxPage>, Error> {

    let shell = Shell::new(&app, "/notifications", &viewer);

    let me = require(&viewer, &shell)?;

    csrf(&viewer, &f.csrf_token, &shell)?;

    ndb::mark_read(&app.db, me.account.id, f.id).await.map_err(|e| Error::from_db(shell.clone(), e))?;

    inbox_page(&app, shell, &me.account, None).await

}

#[derive(Deserialize)]

pub struct PrefsForm {

    #[serde(rename = "_csrf")]

    csrf_token: String,

    #[serde(default)]

    email: Option<String>,

}

pub async fn prefs(State(app): State<Shared>, viewer: Viewer, Form(f): Form<PrefsForm>) -> Result<Page<InboxPage>, Error> {

    let shell = Shell::new(&app, "/notifications", &viewer);

    let me = require(&viewer, &shell)?;

    csrf(&viewer, &f.csrf_token, &shell)?;

    let key = if me.account.unsubscribe_key.is_empty() { crate::auth::random_hex(16) } else { me.account.unsubscribe_key.clone() };

    sqlx::query("UPDATE accounts SET email_notifications = ?, unsubscribe_key = ? WHERE id = ?")

        .bind(f.email.is_some())

        .bind(&key)

        .bind(me.account.id)

        .execute(&app.db)

        .await

        .map_err(|e| Error::from_db(shell.clone(), e))?;

    inbox_page(

        &app,

        shell,

        &me.account,

        Some(if f.email.is_some() { "Email notifications are on.".into() } else { "Email notifications are off.".into() }),

    )

    .await

}

#[derive(Deserialize)]

pub struct UnsubQuery {

    unsubscribe: Option<String>,

}

/// The one-click link in every email: no session needed, the key is the proof.

pub async fn unsubscribe(State(app): State<Shared>, Query(q): Query<UnsubQuery>) -> Response {

    if let Some(key) = q.unsubscribe.filter(|k| k.len() >= 16) {

        sqlx::query("UPDATE accounts SET email_notifications = 0 WHERE unsubscribe_key = ?").bind(key).execute(&app.db).await.ok();

        return (axum::http::StatusCode::OK, "Email notifications are off for that account.").into_response();

    }

    Redirect::to("/notifications").into_response()

}

// ------------------------------------------------------------------ search

pub struct HitView {

    pub kind: String,

    pub slug: String,

    pub number: i64,

    pub title: String,

    pub snippet: String,

    pub url: String,

}

#[derive(Template)]

#[template(path = "search.html")]

pub struct SearchPage {

    shell: Shell,

    q: String,

    repos: Vec<db::repos::RepoRow>,

    accounts: Vec<String>,

    hits: Vec<HitView>,

}

#[derive(Deserialize)]

pub struct SearchQuery {

    q: Option<String>,

}

pub async fn search(State(app): State<Shared>, viewer: Viewer, Query(q): Query<SearchQuery>) -> Result<Page<SearchPage>, Error> {

    let shell = Shell::new(&app, "/search", &viewer);

    let query = q.q.unwrap_or_default().trim().to_string();

    if query.is_empty() {

        return Ok(Page(SearchPage { shell, q: query, repos: vec![], accounts: vec![], hits: vec![] }));

    }

    let e = |e| Error::from_db(shell.clone(), e);

    // What the viewer may see.

    let all = db::repos::list(&app.db).await.map_err(e)?;

    let mut visible = Vec::new();

    for r in all {

        let role = crate::auth::roles::role_for(&app.db, viewer.account(), &r).await.map_err(e)?;

        if crate::auth::roles::allows(role, crate::auth::Access::Read) {

            visible.push(r);

        }

    }

    let lower = query.to_lowercase();

    let repos: Vec<db::repos::RepoRow> = visible

        .iter()

        .filter(|r| {

            r.name.to_lowercase().contains(&lower)

                || r.description.to_lowercase().contains(&lower)

                || r.owner.to_lowercase().contains(&lower)

        })

        .cloned()

        .collect();

    let accounts: Vec<String> = accounts::accounts(&app.db)

        .await

        .map_err(e)?

        .into_iter()

        .filter(|a| a.username.to_lowercase().contains(&lower) || a.display_name.to_lowercase().contains(&lower))

        .map(|a| a.username)

        .collect();

    let mut hits = Vec::new();

    for h in ndb::search(&app.db, &query, None, 100).await.map_err(e)? {

        let Some(r) = visible.iter().find(|r| r.id == h.repo_id) else { continue };

        let url = format!("/{}/{}/{}", r.slug(), if h.kind == "proposal" { "proposal" } else { "issue" }, h.number);

        hits.push(HitView {

            kind: h.kind,

            slug: r.slug(),

            number: h.number,

            title: h.title,

            snippet: crate::render::escape(&h.snippet).replace("&lt;mark&gt;", "<mark>").replace("&lt;/mark&gt;", "</mark>"),

            url,

        });

    }

    let _ = Visibility::Public;

    Ok(Page(SearchPage { shell, q: query, repos, accounts, hits }))

}

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

use super::settings::{csrf, require};

use super::{filters, lookup, lookup_role, Error, Page, RepoShell, Shell};

use crate::auth::{Access, Role, Viewer};

use crate::db::{self, accounts, issues as idb, notify as ndb};

use crate::notify::{self, Event};

use crate::render::{self, refs::Numbers};

use crate::repos::view;

use crate::Shared;

use askama::Template;

use axum::extract::{Form, Path, Query, State};

use axum::response::{IntoResponse, Redirect, Response};

use serde::Deserialize;

use std::collections::HashMap;

/// Markdown with cross references linked, for a repository.

pub async fn markdown(app: &Shared, repo_id: i64, repo_url: &str, text: &str) -> String {

    let (issues, proposals) = idb::numbers(&app.db, repo_id).await.unwrap_or_default();

    let linked = render::refs::link(text, &Numbers { repo_url, issues: &issues, proposals: &proposals });

    render::markdown::render(&linked, &format!("{repo_url}/file/main/"), &format!("{repo_url}/file/main/"))

}

/// Re-index a thread for search: title, body and every comment.

pub async fn reindex(app: &Shared, repo_id: i64, issue: &idb::IssueRow) {

    let comments = idb::comments(&app.db, issue.id).await.unwrap_or_default();

    let text = std::iter::once(issue.body.clone()).chain(comments.into_iter().map(|c| c.body)).collect::<Vec<_>>().join("\n\n");

    ndb::index(&app.db, "issue", repo_id, issue.id, issue.number, &issue.title, &text).await.ok();

}

// ------------------------------------------------------------------ list

pub struct IssueLine {

    pub issue: idb::IssueRow,

    pub labels: Vec<idb::Label>,

    pub assignees: Vec<String>,

    pub milestone: Option<String>,

    pub comments: i64,

}

#[derive(Template)]

#[template(path = "issues.html")]

pub struct IssuesPage {

    shell: Shell,

    repo: RepoShell,

    rows: Vec<IssueLine>,

    state: String,

    q: String,

    counts: (usize, usize),

}

#[derive(Deserialize)]

pub struct ListQuery {

    state: Option<String>,

    q: Option<String>,

}

/// `label:bug assignee:andy milestone:v1 state:closed words…`

struct Filter {

    state: String,

    labels: Vec<String>,

    assignees: Vec<String>,

    milestone: Option<String>,

    words: Vec<String>,

}

fn parse_filter(state: Option<String>, q: &str) -> Filter {

    let mut f = Filter { state: state.unwrap_or_else(|| "open".into()), labels: vec![], assignees: vec![], milestone: None, words: vec![] };

    for tok in q.split_whitespace() {

        match tok.split_once(':') {

            Some(("label", v)) => f.labels.push(v.to_lowercase()),

            Some(("assignee", v)) => f.assignees.push(v.to_lowercase()),

            Some(("milestone", v)) => f.milestone = Some(v.to_lowercase()),

            Some(("state", v)) => f.state = v.to_string(),

            _ => f.words.push(tok.to_lowercase()),

        }

    }

    f

}

pub async fn list(

    State(app): State<Shared>,

    viewer: Viewer,

    Path((owner, name)): Path<(String, String)>,

    Query(q): Query<ListQuery>,

) -> Result<Page<IssuesPage>, Error> {

    let shell = Shell::new(&app, &format!("/{owner}/{name}/issues"), &viewer);

    let (_, row, role) = lookup(&app, &shell, &viewer, &owner, &name).await?;

    let e = |e| Error::from_db(shell.clone(), e);

    let all = idb::list(&app.db, row.id).await.map_err(e)?;

    let counts = (all.iter().filter(|i| i.state == "open").count(), all.iter().filter(|i| i.state == "closed").count());

    let qtext = q.q.clone().unwrap_or_default();

    let f = parse_filter(q.state, &qtext);

    let mut labels_by: HashMap<i64, Vec<idb::Label>> = HashMap::new();

    for (id, l) in idb::labels_by_issue(&app.db, row.id).await.map_err(e)? {

        labels_by.entry(id).or_default().push(l);

    }

    let mut assignees_by: HashMap<i64, Vec<String>> = HashMap::new();

    for (id, u) in idb::assignees_by_issue(&app.db, row.id).await.map_err(e)? {

        assignees_by.entry(id).or_default().push(u);

    }

    let milestones: HashMap<i64, String> =

        idb::milestones(&app.db, row.id).await.map_err(e)?.into_iter().map(|m| (m.id, m.title)).collect();

    let mut rows = Vec::new();

    for i in all {

        let labels = labels_by.remove(&i.id).unwrap_or_default();

        let assignees = assignees_by.remove(&i.id).unwrap_or_default();

        let milestone = i.milestone_id.and_then(|m| milestones.get(&m).cloned());

        let keep = (f.state == "all" || i.state == f.state)

            && f.labels.iter().all(|l| labels.iter().any(|x| x.name.to_lowercase() == *l))

            && f.assignees.iter().all(|a| assignees.iter().any(|x| x.to_lowercase() == *a))

            && f.milestone.as_ref().is_none_or(|m| milestone.as_ref().is_some_and(|x| x.to_lowercase() == *m))

            && f.words.iter().all(|w| i.title.to_lowercase().contains(w) || i.body.to_lowercase().contains(w));

        if keep {

            let comments: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM issue_comments WHERE issue_id = ?")

                .bind(i.id)

                .fetch_one(&app.db)

                .await

                .map_err(e)?;

            rows.push(IssueLine { issue: i, labels, assignees, milestone, comments });

        }

    }

    Ok(Page(IssuesPage { shell, repo: RepoShell::new(&app, &row, "issues").with_role(role), rows, state: f.state, q: qtext, counts }))

}

// ------------------------------------------------------------------- new

#[derive(Template)]

#[template(path = "issue_new.html")]

pub struct NewIssuePage {

    shell: Shell,

    repo: RepoShell,

    templates: Vec<(String, String)>,

    template: String,

    error: Option<String>,

}

#[derive(Deserialize)]

pub struct NewQuery {

    template: Option<String>,

}

/// `.oxyhub/ISSUE_TEMPLATE/*.md` in the default bookmark's state.

async fn issue_templates(app: &Shared, repo: &crate::repos::RepoName, default: &str) -> Vec<(String, String)> {

    let default = default.to_string();

    app.repos

        .read(repo, move |r, cache| {

            let Ok(target) = view::resolve(r, cache, &default) else { return Ok(vec![]) };

            let loaded = cache.load(r, &target.heads)?;

            let mut out = Vec::new();

            for (path, f) in &loaded.snapshot.files {

                if let Some(rest) = path.strip_prefix(".oxyhub/ISSUE_TEMPLATE/") {

                    if let (Some(name), oxy_core::state::FileContent::Text { bytes, .. }) = (rest.strip_suffix(".md"), &f.content) {

                        out.push((name.to_string(), String::from_utf8_lossy(bytes).into_owned()));

                    }

                }

            }

            Ok(out)

        })

        .await

        .unwrap_or_default()

}

pub async fn new_form(

    State(app): State<Shared>,

    viewer: Viewer,

    Path((owner, name)): Path<(String, String)>,

    Query(q): Query<NewQuery>,

) -> Result<Page<NewIssuePage>, Error> {

    let shell = Shell::new(&app, &format!("/{owner}/{name}/issues/new"), &viewer);

    require(&viewer, &shell)?;

    let (repo, row, role) = lookup(&app, &shell, &viewer, &owner, &name).await?;

    let templates = issue_templates(&app, &repo, &row.default_bookmark).await;

    let template = match q.template.as_deref() {

        Some("blank") => " ".into(),

        Some(t) => templates.iter().find(|(n, _)| n == t).map(|(_, b)| b.clone()).unwrap_or_default(),

        None if templates.is_empty() => " ".into(),

        None => String::new(),

    };

    Ok(Page(NewIssuePage { shell, repo: RepoShell::new(&app, &row, "issues").with_role(role), templates, template, error: None }))

}

#[derive(Deserialize)]

pub struct NewIssueForm {

    #[serde(rename = "_csrf")]

    csrf_token: String,

    title: String,

    #[serde(default)]

    body: String,

}

pub async fn create(

    State(app): State<Shared>,

    viewer: Viewer,

    Path((owner, name)): Path<(String, String)>,

    Form(f): Form<NewIssueForm>,

) -> Result<Response, Error> {

    let shell = Shell::new(&app, &format!("/{owner}/{name}/issues/new"), &viewer);

    let me = require(&viewer, &shell)?;

    csrf(&viewer, &f.csrf_token, &shell)?;

    let (_, row, role) = lookup(&app, &shell, &viewer, &owner, &name).await?;

    if f.title.trim().is_empty() {

        return Ok(Page(NewIssuePage {

            shell,

            repo: RepoShell::new(&app, &row, "issues").with_role(role),

            templates: vec![],

            template: f.body,

            error: Some("Give the issue a title.".into()),

        })

        .into_response());

    }

    let issue = idb::create(

        &app.db,

        idb::NewIssue {

            repo_id: row.id,

            title: f.title.trim(),

            body: f.body.trim(),

            author_id: Some(me.account.id),

            author: &me.account.username,

        },

    )

    .await

    .map_err(|e| Error::from_db(shell.clone(), e))?;

    idb::add_event(&app.db, issue.id, &me.account.username, "opened", "").await.ok();

    reindex(&app, row.id, &issue).await;

    let url = format!("/{owner}/{name}/issue/{}", issue.number);

    notify::deliver(

        &app,

        Event {

            kind: "issue.opened",

            title: format!("{owner}/{name}#{}: {}", issue.number, issue.title),

            url: url.clone(),

            actor: &me.account,

            repo_id: row.id,

            thread: Some(("issue", issue.id)),

            mention_text: &f.body,

            direct: vec![],

        },

    )

    .await;

    Ok(Redirect::to(&url).into_response())

}

// ---------------------------------------------------------------- detail

pub enum Item {

    Comment(idb::IssueComment, String),

    Event(idb::IssueEvent),

}

#[derive(Template)]

#[template(path = "issue.html")]

pub struct IssuePage {

    shell: Shell,