projects.rs 91.8%

591 of 644 lines run, 88 of 188 functions · run #544 on main · run missed
//! Projects, their columns and cards (hub spec §8.2).

use super::{now_ms, Pool};
use sqlx::FromRow;

#[derive(Clone, Debug, FromRow)]
pub struct Project {
    pub id: i64,
    pub owner: String,
    pub number: i64,
    pub title: String,
    pub description: String,
    pub public: bool,
    pub closed: bool,
    pub created_ms: i64,
    pub updated_ms: i64,
}

#[derive(Clone, Debug, FromRow)]
pub struct Column {
    pub id: i64,
    pub project_id: i64,
    pub name: String,
    pub position: i64,
    /// Its cards are archived this many days after they joined it (#293).
    pub archive_after_days: Option<i64>,
    /// Cards whose issue closes or whose proposal lands or closes move here
    /// (#325); one column of a board at most.
    pub takes_finished: bool,
    /// Work in progress past this many cards turns its header amber (#361).
    #[sqlx(default)]
    pub wip_limit: Option<i64>,
}

#[derive(Clone, Debug, FromRow)]
pub struct Card {
    pub id: i64,
    pub project_id: i64,
    pub column_id: i64,
    pub position: i64,
    pub kind: String,
    pub repo_id: Option<i64>,
    pub number: Option<i64>,
    pub note: String,
    pub added_by: String,
    pub created_ms: i64,
    /// Off the board since then (#293): kept, and restorable.
    pub archived_ms: Option<i64>,
    /// When it joined its column.
    pub moved_ms: i64,
    /// The iteration it was archived under when that closed (#343).
    pub iteration_id: Option<i64>,
}

/// A named span of a board's days (#343). A board has one open at most;
/// closing it archives its Done column's cards under it.
#[derive(Clone, Debug, FromRow)]
pub struct Iteration {
    pub id: i64,
    pub project_id: i64,
    pub name: String,
    pub start_ms: i64,
    /// The end, exclusive: the midnight after its last day.
    pub end_ms: i64,
    pub closed_ms: Option<i64>,
}

/// A day, for the archive's ages.
const DAY_MS: i64 = 86_400_000;

pub const DEFAULT_COLUMNS: [&str; 3] = ["Backlog", "Doing", "Done"];

pub async fn create(pool: &Pool, owner: &str, title: &str, description: &str, public: bool) -> sqlx::Result<Project> {
    let mut tx = super::begin_write(pool).await?;
    let number: i64 =
        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(owner).fetch_one(&mut *tx).await?;
    let now = now_ms();
    let p: Project = sqlx::query_as(
        "INSERT INTO projects (owner, number, title, description, public, created_ms, updated_ms) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING *",
    )
    .bind(owner)
    .bind(number)
    .bind(title)
    .bind(description)
    .bind(public)
    .bind(now)
    .bind(now)
    .fetch_one(&mut *tx)
    .await?;
    // A board here now: an old address of a transferred one gives way (#125).
    sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(owner).bind(number).execute(&mut *tx).await?;
    for (i, name) in DEFAULT_COLUMNS.iter().enumerate() {
        sqlx::query("INSERT INTO project_columns (project_id, name, position, takes_finished) VALUES ($1, $2, $3, $4)")
            .bind(p.id)
            .bind(name)
            .bind(i as i64)
            .bind(*name == "Done")
            .execute(&mut *tx)
            .await?;
    }
    tx.commit().await?;
    Ok(p)
}

pub async fn of_owner(pool: &Pool, owner: &str) -> sqlx::Result<Vec<Project>> {
    sqlx::query_as("SELECT * FROM projects WHERE owner = $1 ORDER BY closed, updated_ms DESC").bind(owner).fetch_all(pool).await
}

/// Move project `id` to owner `to` (#125): it takes the next number there,
/// with its columns, cards and privacy, and its old address redirects to it.
pub async fn transfer(pool: &Pool, id: i64, to: &str) -> sqlx::Result<Project> {
    let mut tx = super::begin_write(pool).await?;
    let old: Project = sqlx::query_as("SELECT * FROM projects WHERE id = $1").bind(id).fetch_one(&mut *tx).await?;
    let number: i64 =
        sqlx::query_scalar("SELECT COALESCE(MAX(number), 0) + 1 FROM projects WHERE owner = $1").bind(to).fetch_one(&mut *tx).await?;
    sqlx::query("INSERT INTO project_redirects (owner, number, project_id) VALUES ($1, $2, $3) ON CONFLICT (owner, number) DO UPDATE SET project_id = excluded.project_id")
        .bind(&old.owner)
        .bind(old.number)
        .bind(id)
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM project_redirects WHERE owner = $1 AND number = $2").bind(to).bind(number).execute(&mut *tx).await?;
    let moved: Project = sqlx::query_as("UPDATE projects SET owner = $1, number = $2, updated_ms = $3 WHERE id = $4 RETURNING *")
        .bind(to)
        .bind(number)
        .bind(now_ms())
        .bind(id)
        .fetch_one(&mut *tx)
        .await?;
    tx.commit().await?;
    Ok(moved)
}

/// The project an old address (`owner`, `number`) now redirects to, after
/// a transfer (#125).
pub async fn redirected(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {
    sqlx::query_as("SELECT p.* FROM project_redirects r JOIN projects p ON p.id = r.project_id WHERE r.owner = $1 AND r.number = $2")
        .bind(owner)
        .bind(number)
        .fetch_optional(pool)
        .await
}

pub async fn get(pool: &Pool, owner: &str, number: i64) -> sqlx::Result<Option<Project>> {
    sqlx::query_as("SELECT * FROM projects WHERE owner = $1 AND number = $2").bind(owner).bind(number).fetch_optional(pool).await
}

pub async fn set(pool: &Pool, id: i64, title: &str, description: &str, public: bool, closed: bool) -> sqlx::Result<()> {
    sqlx::query("UPDATE projects SET title = $1, description = $2, public = $3, closed = $4, updated_ms = $5 WHERE id = $6")
        .bind(title)
        .bind(description)
        .bind(public)
        .bind(closed)
        .bind(now_ms())
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn delete(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM projects WHERE id = $1").bind(id).execute(pool).await?;
    Ok(())
}

async fn touch(pool: &Pool, id: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(id).execute(pool).await?;
    Ok(())
}

pub async fn columns(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Column>> {
    sqlx::query_as("SELECT * FROM project_columns WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
}

pub async fn cards(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Card>> {
    sqlx::query_as("SELECT * FROM project_cards WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
}

pub async fn add_column(pool: &Pool, project_id: i64, name: &str) -> sqlx::Result<()> {
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_columns WHERE project_id = $1")
        .bind(project_id)
        .fetch_one(pool)
        .await?;
    sqlx::query("INSERT INTO project_columns (project_id, name, position) VALUES ($1, $2, $3)")
        .bind(project_id)
        .bind(name)
        .bind(pos)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

pub async fn rename_column(pool: &Pool, project_id: i64, column_id: i64, name: &str) -> sqlx::Result<()> {
    sqlx::query("UPDATE project_columns SET name = $1 WHERE id = $2 AND project_id = $3")
        .bind(name)
        .bind(column_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Swap a column with its neighbour (`-1` left, `1` right).
pub async fn shift_column(pool: &Pool, project_id: i64, column_id: i64, by: i64) -> sqlx::Result<()> {
    let cols = columns(pool, project_id).await?;
    let Some(i) = cols.iter().position(|c| c.id == column_id) else { return Ok(()) };
    let j = i as i64 + by;
    if j < 0 || j as usize >= cols.len() {
        return Ok(());
    }
    let mut ids: Vec<i64> = cols.iter().map(|c| c.id).collect();
    ids.swap(i, j as usize);
    for (pos, id) in ids.iter().enumerate() {
        sqlx::query("UPDATE project_columns SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
    }
    touch(pool, project_id).await
}

/// Move a column to `position` (0 the leftmost; past the end the rightmost).
pub async fn place_column(pool: &Pool, project_id: i64, column_id: i64, position: i64) -> sqlx::Result<()> {
    let cols = columns(pool, project_id).await?;
    let Some(i) = cols.iter().position(|c| c.id == column_id) else { return Ok(()) };
    let mut ids: Vec<i64> = cols.iter().map(|c| c.id).collect();
    let id = ids.remove(i);
    ids.insert((position.max(0) as usize).min(ids.len()), id);
    for (pos, id) in ids.iter().enumerate() {
        sqlx::query("UPDATE project_columns SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
    }
    touch(pool, project_id).await
}

/// Remove a column; its cards go to the first remaining column. The last column stays.
pub async fn remove_column(pool: &Pool, project_id: i64, column_id: i64) -> sqlx::Result<bool> {
    let cols = columns(pool, project_id).await?;
    let Some(to) = cols.iter().find(|c| c.id != column_id) else { return Ok(false) };
    if !cols.iter().any(|c| c.id == column_id) {
        return Ok(false);
    }
    let mut tx = super::begin_write(pool).await?;
    let base: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(to.id)
        .fetch_one(&mut *tx)
        .await?;
    log_moves(&mut *tx, Some(to.id), now_ms(), "column_id = $1 AND archived_ms IS NULL", &[column_id]).await?;
    sqlx::query("UPDATE project_cards SET column_id = $1, position = position + $2 WHERE column_id = $3")
        .bind(to.id)
        .bind(base)
        .bind(column_id)
        .execute(&mut *tx)
        .await?;
    sqlx::query("DELETE FROM project_columns WHERE id = $1").bind(column_id).execute(&mut *tx).await?;
    tx.commit().await?;
    touch(pool, project_id).await?;
    Ok(true)
}

/// Record, for every card `select` picks (`SELECT project_id, id FROM
/// project_cards WHERE …`, bound from `$1` on), that it moved to `column`
/// (`None`: off the board) at `at` (#357). Called before the update that
/// moves them, with the same conditions.
async fn log_moves<'e, E: sqlx::Executor<'e, Database = super::Db>>(
    ex: E,
    column: Option<i64>,
    at: i64,
    select: &str,
    binds: &[i64],
) -> sqlx::Result<()> {
    let sql = format!(
        "INSERT INTO project_card_moves (project_id, card_id, column_id, at_ms) SELECT project_id, id, {}, {at} FROM project_cards WHERE {select}",
        match column {
            Some(c) => c.to_string(),
            None => "NULL".to_string(),
        }
    );
    let mut q = sqlx::query(&sql);
    for b in binds {
        q = q.bind(*b);
    }
    q.execute(ex).await?;
    Ok(())
}

/// A board's card moves since `since`, oldest first: (card, column or
/// `None` off the board, when).
pub async fn moves(pool: &Pool, project_id: i64, since: i64) -> sqlx::Result<Vec<(i64, Option<i64>, i64)>> {
    sqlx::query_as("SELECT card_id, column_id, at_ms FROM project_card_moves WHERE project_id = $1 AND at_ms >= $2 ORDER BY at_ms, id")
        .bind(project_id)
        .bind(since)
        .fetch_all(pool)
        .await
}

pub struct NewCard<'a> {
    pub column_id: i64,
    pub kind: &'a str,
    pub repo_id: Option<i64>,
    pub number: Option<i64>,
    pub note: &'a str,
    pub added_by: &'a str,
}

/// Add a card at the bottom of a column; `Ok(false)` when that issue or
/// proposal is already on the project.
pub async fn add_card(pool: &Pool, project_id: i64, c: NewCard<'_>) -> sqlx::Result<bool> {
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(c.column_id)
        .fetch_one(pool)
        .await?;
    let now = now_ms();
    let id: Option<i64> = sqlx::query_scalar(
        "INSERT INTO project_cards (project_id, column_id, position, kind, repo_id, number, note, added_by, created_ms, moved_ms) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $9) ON CONFLICT DO NOTHING RETURNING id",
    )
    .bind(project_id)
    .bind(c.column_id)
    .bind(pos)
    .bind(c.kind)
    .bind(c.repo_id)
    .bind(c.number)
    .bind(c.note)
    .bind(c.added_by)
    .bind(now)
    .fetch_optional(pool)
    .await?;
    if let Some(id) = id {
        log_moves(pool, Some(c.column_id), now, "id = $1", &[id]).await?;
    }
    touch(pool, project_id).await?;
    Ok(id.is_some())
}

/// Move a card to the bottom of another column of the same project.
pub async fn move_card(pool: &Pool, project_id: i64, card_id: i64, column_id: i64) -> sqlx::Result<()> {
    let ok: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_columns WHERE id = $1 AND project_id = $2")
        .bind(column_id)
        .bind(project_id)
        .fetch_one(pool)
        .await?;
    if ok == 0 {
        return Ok(());
    }
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(column_id)
        .fetch_one(pool)
        .await?;
    let now = now_ms();
    log_moves(
        pool,
        Some(column_id),
        now,
        "id = $1 AND project_id = $2 AND column_id <> $3 AND archived_ms IS NULL",
        &[card_id, project_id, column_id],
    )
    .await?;
    sqlx::query("UPDATE project_cards SET moved_ms = CASE WHEN column_id = $1 THEN moved_ms ELSE $3 END, column_id = $1, position = $2 WHERE id = $4 AND project_id = $5")
        .bind(column_id)
        .bind(pos)
        .bind(now)
        .bind(card_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Move `card_ids`, in that order, to the end of `column_id`, all or none
/// (#289).
pub async fn move_cards(pool: &Pool, project_id: i64, card_ids: &[i64], column_id: i64) -> sqlx::Result<()> {
    let mut tx = pool.begin().await?;
    let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = $1")
        .bind(column_id)
        .fetch_one(&mut *tx)
        .await?;
    let now = now_ms();
    for (i, id) in card_ids.iter().enumerate() {
        log_moves(
            &mut *tx,
            Some(column_id),
            now,
            "id = $1 AND project_id = $2 AND column_id <> $3 AND archived_ms IS NULL",
            &[*id, project_id, column_id],
        )
        .await?;
        sqlx::query("UPDATE project_cards SET column_id = $1, position = $2, moved_ms = $3 WHERE id = $4 AND project_id = $5")
            .bind(column_id)
            .bind(pos + i as i64)
            .bind(now)
            .bind(id)
            .bind(project_id)
            .execute(&mut *tx)
            .await?;
    }
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await
}

/// Take `card_ids` off the board, kept (#293); how many were on it.
pub async fn archive_cards(pool: &Pool, project_id: i64, card_ids: &[i64]) -> sqlx::Result<u64> {
    let mut tx = pool.begin().await?;
    let now = now_ms();
    let mut n = 0;
    for id in card_ids {
        log_moves(&mut *tx, None, now, "id = $1 AND project_id = $2 AND archived_ms IS NULL", &[*id, project_id]).await?;
        n += sqlx::query("UPDATE project_cards SET archived_ms = $1 WHERE id = $2 AND project_id = $3 AND archived_ms IS NULL")
            .bind(now)
            .bind(id)
            .bind(project_id)
            .execute(&mut *tx)
            .await?
            .rows_affected();
    }
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(n)
}

/// Put an archived card back on the board, at the end of its column, as
/// if it had just joined it (so its column does not archive it again at
/// once); `false` when it was not archived.
pub async fn restore_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<bool> {
    let mut tx = pool.begin().await?;
    let pos: i64 = sqlx::query_scalar(
        "SELECT COALESCE(MAX(position), -1) + 1 FROM project_cards WHERE column_id = (SELECT column_id FROM project_cards WHERE id = $1)",
    )
    .bind(card_id)
    .fetch_one(&mut *tx)
    .await?;
    let now = now_ms();
    sqlx::query(&format!(
        "INSERT INTO project_card_moves (project_id, card_id, column_id, at_ms) SELECT project_id, id, column_id, {now} FROM project_cards \
         WHERE id = $1 AND project_id = $2 AND archived_ms IS NOT NULL"
    ))
    .bind(card_id)
    .bind(project_id)
    .execute(&mut *tx)
    .await?;
    // Back on the board, it no longer counts as done in a past iteration.
    let r = sqlx::query(
        "UPDATE project_cards SET archived_ms = NULL, iteration_id = NULL, moved_ms = $1, position = $2 \
         WHERE id = $3 AND project_id = $4 AND archived_ms IS NOT NULL",
    )
    .bind(now)
    .bind(pos)
    .bind(card_id)
    .bind(project_id)
    .execute(&mut *tx)
    .await?;
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(r.rows_affected() == 1)
}

/// Whether `card` joined its column more than `days` ago, at `now`.
pub fn older_than(card: &Card, days: i64, now: i64) -> bool {
    card.moved_ms < now - days * DAY_MS
}

/// A board's iterations, newest first (#343).
pub async fn iterations(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<Iteration>> {
    sqlx::query_as("SELECT * FROM project_iterations WHERE project_id = $1 ORDER BY start_ms DESC, id DESC")
        .bind(project_id)
        .fetch_all(pool)
        .await
}

/// Start an iteration of `[start_ms, end_ms)`; `None` while another is open.
pub async fn start_iteration(pool: &Pool, project_id: i64, name: &str, start_ms: i64, end_ms: i64) -> sqlx::Result<Option<Iteration>> {
    let mut tx = super::begin_write(pool).await?;
    let open: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_iterations WHERE project_id = $1 AND closed_ms IS NULL")
        .bind(project_id)
        .fetch_one(&mut *tx)
        .await?;
    if open > 0 {
        return Ok(None);
    }
    let it: Iteration =
        sqlx::query_as("INSERT INTO project_iterations (project_id, name, start_ms, end_ms) VALUES ($1, $2, $3, $4) RETURNING *")
            .bind(project_id)
            .bind(name)
            .bind(start_ms)
            .bind(end_ms)
            .fetch_one(&mut *tx)
            .await?;
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now_ms()).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(Some(it))
}

/// The column whose cards are done: the one that takes finished work, or
/// else the last.
pub fn done_column(columns: &[Column]) -> Option<i64> {
    columns.iter().find(|c| c.takes_finished).or(columns.last()).map(|c| c.id)
}

/// Close an open iteration: the cards on the board in `done` (its Done
/// column) are archived under it. How many, or `None` when it was not open.
pub async fn close_iteration(pool: &Pool, project_id: i64, iteration_id: i64, done: Option<i64>) -> sqlx::Result<Option<u64>> {
    let mut tx = super::begin_write(pool).await?;
    let now = now_ms();
    let closed = sqlx::query("UPDATE project_iterations SET closed_ms = $1 WHERE id = $2 AND project_id = $3 AND closed_ms IS NULL")
        .bind(now)
        .bind(iteration_id)
        .bind(project_id)
        .execute(&mut *tx)
        .await?;
    if closed.rows_affected() == 0 {
        return Ok(None);
    }
    let mut n = 0;
    if let Some(done) = done {
        log_moves(&mut *tx, None, now, "project_id = $1 AND column_id = $2 AND archived_ms IS NULL", &[project_id, done]).await?;
        n = sqlx::query(
            "UPDATE project_cards SET archived_ms = $1, iteration_id = $2 WHERE project_id = $3 AND column_id = $4 AND archived_ms IS NULL",
        )
        .bind(now)
        .bind(iteration_id)
        .bind(project_id)
        .bind(done)
        .execute(&mut *tx)
        .await?
        .rows_affected();
    }
    sqlx::query("UPDATE projects SET updated_ms = $1 WHERE id = $2").bind(now).bind(project_id).execute(&mut *tx).await?;
    tx.commit().await?;
    Ok(Some(n))
}

/// When each card done in `it` reached the Done column `done`: those
/// archived under it, and while it is open those in `done` now or archived
/// from there during it.
pub async fn done_times(pool: &Pool, it: &Iteration, done: Option<i64>) -> sqlx::Result<Vec<i64>> {
    sqlx::query_scalar(
        "SELECT moved_ms FROM project_cards WHERE project_id = $1 AND (iteration_id = $2 \
         OR ($3 AND iteration_id IS NULL AND column_id = $4 AND (archived_ms IS NULL OR archived_ms >= $5))) ORDER BY moved_ms",
    )
    .bind(it.project_id)
    .bind(it.id)
    .bind(it.closed_ms.is_none())
    .bind(done.unwrap_or(-1))
    .bind(it.start_ms)
    .fetch_all(pool)
    .await
}

/// The burn-up of an iteration: cards done by the end of each of its days
/// so far, from `times` (when each reached Done; earlier than the start
/// counts on the first day). One entry per day up to `now` or its end.
pub fn burn_up(times: &[i64], start_ms: i64, end_ms: i64, now: i64) -> Vec<usize> {
    let days = ((end_ms - start_ms + DAY_MS - 1) / DAY_MS).max(1);
    let upto = ((now.min(end_ms - 1) - start_ms) / DAY_MS + 1).clamp(0, days);
    (0..upto).map(|d| times.iter().filter(|&&t| t < start_ms + (d + 1) * DAY_MS).count()).collect()
}

/// An iteration's length in days.
pub fn iteration_days(it: &Iteration) -> i64 {
    ((it.end_ms - it.start_ms + DAY_MS - 1) / DAY_MS).max(1)
}

/// Archive a column's cards this many days after they join it; `None`
/// finished cards after [`FINISHED_KEPT_DAYS`] (the default); [`NEVER`]
/// none.
pub async fn set_archive_after(pool: &Pool, project_id: i64, column_id: i64, days: Option<i64>) -> sqlx::Result<()> {
    sqlx::query("UPDATE project_columns SET archive_after_days = $1 WHERE id = $2 AND project_id = $3")
        .bind(days)
        .bind(column_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Days after which a column with no setting of its own archives its
/// finished cards (#293): a board shows the last fortnight's done work.
pub const FINISHED_KEPT_DAYS: i64 = 14;

/// The finished cards due for archiving: in a column with no setting of
/// its own, joined before `$1`, and their issue closed or proposal closed
/// or landed.
const FINISHED_DUE: &str = "archived_ms IS NULL AND moved_ms < $1 \
     AND EXISTS (SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id AND c.archive_after_days IS NULL) \
     AND ((kind = 'issue' AND EXISTS (SELECT 1 FROM issues i WHERE i.repo_id = project_cards.repo_id \
           AND i.number = project_cards.number AND i.state = 'closed')) \
       OR (kind = 'proposal' AND EXISTS (SELECT 1 FROM proposals p WHERE p.repo_id = project_cards.repo_id \
           AND p.number = project_cards.number AND p.state IN ('closed', 'landed'))))";

/// A column's `archive_after_days` that means never.
pub const NEVER: i64 = -1;

/// Archive, on every board, the cards their columns keep no longer (#293);
/// how many. A column that archives after N days: every card that joined it
/// longer ago. A column with no setting: finished cards (a closed issue, a
/// closed or landed proposal) after [`FINISHED_KEPT_DAYS`], so a Done column
/// stops growing while open work and notes stay. [`NEVER`]: none.
pub async fn archive_due(pool: &Pool, now: i64) -> sqlx::Result<u64> {
    log_moves(
        pool,
        None,
        now,
        "archived_ms IS NULL AND EXISTS (SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id \
         AND c.archive_after_days >= 0 AND project_cards.moved_ms < $1 - c.archive_after_days * $2)",
        &[now, DAY_MS],
    )
    .await?;
    let set = sqlx::query(
        "UPDATE project_cards SET archived_ms = $1 WHERE archived_ms IS NULL AND EXISTS \
         (SELECT 1 FROM project_columns c WHERE c.id = project_cards.column_id AND c.archive_after_days >= 0 \
          AND project_cards.moved_ms < $1 - c.archive_after_days * $2)",
    )
    .bind(now)
    .bind(DAY_MS)
    .execute(pool)
    .await?;
    log_moves(pool, None, now, FINISHED_DUE, &[now - FINISHED_KEPT_DAYS * DAY_MS]).await?;
    let finished = sqlx::query(&format!("UPDATE project_cards SET archived_ms = $2 WHERE {FINISHED_DUE}"))
        .bind(now - FINISHED_KEPT_DAYS * DAY_MS)
        .bind(now)
        .execute(pool)
        .await?;
    Ok(set.rows_affected() + finished.rows_affected())
}

/// Swap a card with its neighbour in its column (`-1` up, `1` down).
pub async fn shift_card(pool: &Pool, project_id: i64, card_id: i64, by: i64) -> sqlx::Result<()> {
    let Some(card): Option<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE id = $1 AND project_id = $2")
        .bind(card_id)
        .bind(project_id)
        .fetch_optional(pool)
        .await?
    else {
        return Ok(());
    };
    let col: Vec<Card> = sqlx::query_as("SELECT * FROM project_cards WHERE column_id = $1 ORDER BY position, id")
        .bind(card.column_id)
        .fetch_all(pool)
        .await?;
    let Some(i) = col.iter().position(|c| c.id == card_id) else { return Ok(()) };
    let j = i as i64 + by;
    if j < 0 || j as usize >= col.len() {
        return Ok(());
    }
    let mut ids: Vec<i64> = col.iter().map(|c| c.id).collect();
    ids.swap(i, j as usize);
    for (pos, id) in ids.iter().enumerate() {
        sqlx::query("UPDATE project_cards SET position = $1 WHERE id = $2").bind(pos as i64).bind(id).execute(pool).await?;
    }
    touch(pool, project_id).await
}

pub async fn remove_card(pool: &Pool, project_id: i64, card_id: i64) -> sqlx::Result<()> {
    log_moves(pool, None, now_ms(), "id = $1 AND project_id = $2 AND archived_ms IS NULL", &[card_id, project_id]).await?;
    sqlx::query("DELETE FROM project_cards WHERE id = $1 AND project_id = $2").bind(card_id).bind(project_id).execute(pool).await?;
    touch(pool, project_id).await
}

/// The projects (and column names) an issue or proposal is on.
/// Make `column_id` the board's column for finished work, or none (#325).
pub async fn set_takes_finished(pool: &Pool, project_id: i64, column_id: Option<i64>) -> sqlx::Result<()> {
    let mut tx = super::begin_write(pool).await?;
    sqlx::query("UPDATE project_columns SET takes_finished = FALSE WHERE project_id = $1").bind(project_id).execute(&mut *tx).await?;
    if let Some(c) = column_id {
        sqlx::query("UPDATE project_columns SET takes_finished = TRUE WHERE id = $1 AND project_id = $2")
            .bind(c)
            .bind(project_id)
            .execute(&mut *tx)
            .await?;
    }
    tx.commit().await?;
    touch(pool, project_id).await
}

/// Issue or proposal `number` of `repo_id` is finished (closed, landed):
/// its cards move to their boards' column for finished work, where a board
/// has one (#325). Archived cards stay archived.
pub async fn finished(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<()> {
    let cards: Vec<(i64, i64, i64)> = sqlx::query_as(
        "SELECT c.id, c.project_id, c.column_id FROM project_cards c
         WHERE c.repo_id = $1 AND c.number = $2 AND c.kind <> 'note' AND c.archived_ms IS NULL",
    )
    .bind(repo_id)
    .bind(number)
    .fetch_all(pool)
    .await?;
    for (card, project, column) in cards {
        let to: Option<i64> =
            sqlx::query_scalar("SELECT id FROM project_columns WHERE project_id = $1 AND takes_finished ORDER BY position LIMIT 1")
                .bind(project)
                .fetch_optional(pool)
                .await?;
        if let Some(to) = to.filter(|to| *to != column) {
            move_card(pool, project, card, to).await?;
        }
    }
    Ok(())
}

pub async fn placements(pool: &Pool, repo_id: i64, number: i64) -> sqlx::Result<Vec<(Project, String)>> {
    let rows: Vec<(i64, String, Option<i64>)> = sqlx::query_as(
        "SELECT c.project_id, col.name, c.archived_ms FROM project_cards c JOIN project_columns col ON col.id = c.column_id WHERE c.repo_id = $1 AND c.number = $2 AND c.kind <> 'note'",
    )
    .bind(repo_id)
    .bind(number)
    .fetch_all(pool)
    .await?;
    let mut out = Vec::new();
    for (pid, col, archived) in rows {
        let col = if archived.is_some() { format!("{col}, archived") } else { col };
        if let Some(p) = sqlx::query_as::<_, Project>("SELECT * FROM projects WHERE id = $1").bind(pid).fetch_optional(pool).await? {
            out.push((p, col));
        }
    }
    Ok(out)
}

/// A board's saved view (#361): a layout with its filter, lanes and sort.
#[derive(Clone, Debug, FromRow)]
pub struct View {
    pub id: i64,
    pub project_id: i64,
    pub name: String,
    /// `board` or `table`.
    pub layout: String,
    pub q: String,
    /// Swimlanes (board) or groups (table): `repo`, `assignee`, `label`,
    /// `milestone`, or empty.
    pub lane: String,
    /// The table's order: `title`, `reference`, `state`, `column`, `age`,
    /// a `-` before it for the reverse; empty is the board's order.
    pub sort: String,
    pub position: i64,
    pub created_ms: i64,
}

pub async fn views(pool: &Pool, project_id: i64) -> sqlx::Result<Vec<View>> {
    sqlx::query_as("SELECT * FROM project_views WHERE project_id = $1 ORDER BY position, id").bind(project_id).fetch_all(pool).await
}

/// Save a view under `name`, replacing the one of that name.
pub async fn save_view(pool: &Pool, project_id: i64, name: &str, layout: &str, q: &str, lane: &str, sort: &str) -> sqlx::Result<View> {
    let mut tx = super::begin_write(pool).await?;
    let existing: Option<i64> = sqlx::query_scalar("SELECT id FROM project_views WHERE project_id = $1 AND name = $2")
        .bind(project_id)
        .bind(name)
        .fetch_optional(&mut *tx)
        .await?;
    let v: View = match existing {
        Some(id) => {
            sqlx::query_as("UPDATE project_views SET layout = $1, q = $2, lane = $3, sort = $4 WHERE id = $5 RETURNING *")
                .bind(layout)
                .bind(q)
                .bind(lane)
                .bind(sort)
                .bind(id)
                .fetch_one(&mut *tx)
                .await?
        }
        None => {
            let pos: i64 = sqlx::query_scalar("SELECT COALESCE(MAX(position), -1) + 1 FROM project_views WHERE project_id = $1")
                .bind(project_id)
                .fetch_one(&mut *tx)
                .await?;
            sqlx::query_as(
                "INSERT INTO project_views (project_id, name, layout, q, lane, sort, position, created_ms) \
                 VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *",
            )
            .bind(project_id)
            .bind(name)
            .bind(layout)
            .bind(q)
            .bind(lane)
            .bind(sort)
            .bind(pos)
            .bind(now_ms())
            .fetch_one(&mut *tx)
            .await?
        }
    };
    tx.commit().await?;
    touch(pool, project_id).await?;
    Ok(v)
}

pub async fn delete_view(pool: &Pool, project_id: i64, view_id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("DELETE FROM project_views WHERE id = $1 AND project_id = $2").bind(view_id).bind(project_id).execute(pool).await?;
    touch(pool, project_id).await?;
    Ok(r.rows_affected() == 1)
}

/// A column's work-in-progress limit (#361); `None` for none.
pub async fn set_wip_limit(pool: &Pool, project_id: i64, column_id: i64, limit: Option<i64>) -> sqlx::Result<()> {
    sqlx::query("UPDATE project_columns SET wip_limit = $1 WHERE id = $2 AND project_id = $3")
        .bind(limit)
        .bind(column_id)
        .bind(project_id)
        .execute(pool)
        .await?;
    touch(pool, project_id).await
}

/// Link a board to a repository (#360): its issues and proposals join
/// the board as they open.
pub async fn link_repo(pool: &Pool, project_id: i64, repo_id: i64) -> sqlx::Result<()> {
    sqlx::query("INSERT INTO project_repos (project_id, repo_id) VALUES ($1, $2) ON CONFLICT DO NOTHING")
        .bind(project_id)
        .bind(repo_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// The boards a repository is linked to.
pub async fn boards_of_repo(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<i64>> {
    sqlx::query_scalar("SELECT project_id FROM project_repos WHERE repo_id = $1 ORDER BY project_id").bind(repo_id).fetch_all(pool).await
}

/// Issue or proposal `number` of `repo_id` opened (#360): a card for it at
/// the bottom of the first column of each board the repository is linked
/// to; an open board only.
pub async fn opened(pool: &Pool, repo_id: i64, kind: &str, number: i64, by: &str) -> sqlx::Result<()> {
    for project_id in boards_of_repo(pool, repo_id).await? {
        let closed: Option<bool> =
            sqlx::query_scalar("SELECT closed FROM projects WHERE id = $1").bind(project_id).fetch_optional(pool).await?;
        if closed != Some(false) {
            continue;
        }
        if let Some(first) = columns(pool, project_id).await?.first() {
            add_card(
                pool,
                project_id,
                NewCard { column_id: first.id, kind, repo_id: Some(repo_id), number: Some(number), note: "", added_by: by },
            )
            .await?;
        }
    }
    Ok(())
}
`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet
Coverage / crates/hub/src/db/projects.rs 91.8%

Keyboard
