Postgres, step 2b: flags compared as TRUE and FALSE, counted with CASE ea14c4ec5c3f

/// accounts are left out.

pub async fn assignable(pool: &Pool, repo_id: i64, owner: &str) -> sqlx::Result<Vec<String>> {

    sqlx::query_scalar(

           SELECT id FROM accounts WHERE username = $1 \

           UNION SELECT account_id FROM collaborators WHERE repo_id = $2 AND account_id IS NOT NULL \

           UNION SELECT tm.account_id FROM team_members tm JOIN collaborators c ON c.team_id = tm.team_id WHERE c.repo_id = $2 \

/// was no longer pending.

pub async fn approve_device(pool: &Pool, id: i64, account_id: i64, scopes: &str) -> sqlx::Result<bool> {

    let r = sqlx::query(

    )

    .bind(account_id)

    .bind(scopes)

}

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

    Ok(())

}

/// The account with this verified email (case-insensitive), if exactly one.

pub async fn account_by_verified_email(pool: &Pool, email: &str) -> sqlx::Result<Option<Account>> {

        .bind(email)

        .fetch_all(pool)

        .await?;

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

           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) \

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

           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 \

    let ids = serde_json::to_string(repo_ids).unwrap_or_else(|_| "[]".into());

    sqlx::query_as(

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

           AND repo_id IN (SELECT value FROM json_each($3)) ORDER BY at_ms",

    )

    .bind(account_id)

pub async fn add(pool: &Pool, n: New<'_>) -> sqlx::Result<()> {

    let New { account_id, repo_id, reason, kind, title, url, actor } = n;

    // A new event brings a thread that was set aside back to the inbox.

        .bind(account_id)

        .bind(thread_of(url))

        .execute(pool)

    let mut sql = String::from(

        "SELECT n.id, MAX(n.at_ms) AS at_ms, n.kind, n.title, n.url, n.actor, n.thread, n.repo_id, \

         (SELECT r.owner || '/' || r.name FROM repositories r WHERE r.id = n.repo_id) AS repo, \

         FROM notifications n WHERE n.account_id = $1 AND n.done = $2",

    );

    // Placeholders numbered as they are added: $1 and $2 above.

    }

    sql.push_str(" GROUP BY n.thread HAVING 1");

    if f.unread_only {

    }

    match f.reason.as_deref() {

        Some("participating") => sql.push_str(" AND SUM(n.reason = 'participating') > 0"),

pub async fn set_thread(pool: &Pool, account_id: i64, thread: &str, read: Option<bool>, done: Option<bool>) -> sqlx::Result<()> {

    if let Some(r) = read {

        if r {

                .bind(account_id)

                .bind(thread)

                .execute(pool)

                .await?;

        } else {

            // Unread: the newest notification of the thread only.

                .bind(account_id)

                .bind(thread)

                .execute(pool)

        }

    }

    if let Some(d) = done {

        sqlx::query(&format!("UPDATE notifications SET done = $1{read_too} WHERE account_id = $2 AND thread = $3"))

            .bind(d)

            .bind(account_id)

}

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

        .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) => {

                .bind(account_id)

                .bind(id)

                .execute(pool)

                .await?

        }

    };

    Ok(())

}

/// The newest release that is not a pre-release: the "latest" marker.

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

        .bind(repo_id)

        .fetch_optional(pool)

        .await

    }

    tracing::warn!("flow: runner {} drained after {n} errors in a row (last: {last})", r.name);

    let Some(me) = flow_account(&app.db).await else { return };

    crate::notify::deliver(

        app,

        crate::notify::Event {

/// 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) {

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

    }

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

    assert!(page.body.contains("#999") && !page.body.contains("issue/999"), "unknown numbers are not linked");

    let unread = db::notify::unread(&app.db, db::accounts::account_by_name(&app.db, "bob").await.unwrap().unwrap().id).await.unwrap();

    assert_eq!(unread, 1, "one notification, not one per reason");

    let inbox = get(app, "/notifications", Some(&bob)).await;

    assert!(inbox.body.contains("andy/demo#1: Crash on start"), "{}", inbox.body);

    assert!(get(app, "/andy/demo", Some(&bob)).await.body.contains("class=\"count\">1<"), "bell shows the count");