Iterations on project boards: a named span of days with a burn-up of cards done; closing one archives its Done column's cards under it, its page lists them (closes #343) 046e3352cdfd

-- Iterations on project boards (oxygenhub#343): a named span of days. A

-- board has at most one open at a time; closing it archives the cards in

-- its Done column under it, so the board shows the current iteration and

-- each past one keeps its cards and its burn-up.

CREATE TABLE project_iterations (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    start_ms   BIGINT NOT NULL,

    end_ms     BIGINT NOT NULL,

    closed_ms  BIGINT

);

CREATE INDEX project_iterations_by_project ON project_iterations (project_id, start_ms);

-- The iteration a card was archived under when that iteration closed.

ALTER TABLE project_cards ADD COLUMN iteration_id BIGINT REFERENCES project_iterations(id) ON DELETE SET NULL;

-- Iterations on project boards (oxygenhub#343): a named span of days. A

-- board has at most one open at a time; closing it archives the cards in

-- its Done column under it, so the board shows the current iteration and

-- each past one keeps its cards and its burn-up.

CREATE TABLE project_iterations (

    id         INTEGER PRIMARY KEY,

    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,

    name       TEXT NOT NULL,

    start_ms   INTEGER NOT NULL,

    end_ms     INTEGER NOT NULL,

    closed_ms  INTEGER

);

CREATE INDEX project_iterations_by_project ON project_iterations (project_id, start_ms);

-- The iteration a card was archived under when that iteration closed.

ALTER TABLE project_cards ADD COLUMN iteration_id INTEGER REFERENCES project_iterations(id) ON DELETE SET NULL;

- **Iterations on project boards (#343).** A board can run an iteration: a name and its first and last days. The board shows it above the columns, day N of M and the cards done so far, with a burn-up of cards done per day. Closing it archives the cards in the Done column (the one that takes finished work, or else the last) under it; its page, linked from "past iterations", lists them, restorable, with its burn-up. One iteration is open at a time. Migration 0100.

    /// 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>,

}

    // Back on the board, it no longer counts as done in a past iteration.

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

}

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

        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)

    /// A past iteration: the cards archived under it, and its burn-up (#343).

    #[serde(default)]

    iteration: Option<i64>,

        } else if col.cards.len() > COLUMN_SHOWN && view.all != Some(col.column.id) && !view.archived() && view.iteration.is_none() {

}

/// An iteration as the board shows it (#343).

pub struct IterationView {

    pub it: pdb::Iteration,

    /// Its first and last days, YYYY-MM-DD.

    pub first: String,

    pub last: String,

    pub days: i64,

    /// Its days so far (all of them once it has ended).

    pub so_far: usize,

    /// Cards done in it so far.

    pub done: usize,

    /// The burn-up as SVG polyline points in a [`BURN_W`] × [`BURN_H`] box,

    /// one point per day so far; `top` is the count at the top of the box.

    pub points: String,

    pub top: usize,

}

pub const BURN_W: i64 = 280;

pub const BURN_H: i64 = 64;

/// How many past iterations the board lists.

const PAST_SHOWN: usize = 12;

const DAY_MS: i64 = 86_400_000;

/// `YYYY-MM-DD` to the midnight (UTC) that starts it.

fn day_ms(s: &str) -> Option<i64> {

    let mut parts = s.trim().splitn(3, '-').map(|p| p.parse::<i32>().ok());

    let (y, m, d) = (parts.next()??, parts.next()??, parts.next()??);

    let month = time::Month::try_from(u8::try_from(m).ok()?).ok()?;

    let date = time::Date::from_calendar_date(y, month, u8::try_from(d).ok()?).ok()?;

    Some(date.midnight().assume_utc().unix_timestamp() * 1000)

}

/// The day (UTC) holding `ms`, as `YYYY-MM-DD`.

fn day_of(ms: i64) -> String {

    let t = time::OffsetDateTime::from_unix_timestamp(ms.div_euclid(1000)).unwrap_or(time::OffsetDateTime::UNIX_EPOCH);

    format!("{:04}-{:02}-{:02}", t.year(), u8::from(t.month()), t.day())

async fn iteration_view(app: &Shared, it: pdb::Iteration, done: Option<i64>, now: i64) -> sqlx::Result<IterationView> {

    let times = pdb::done_times(&app.db, &it, done).await?;

    let days = pdb::iteration_days(&it);

    let up = pdb::burn_up(&times, it.start_ms, it.end_ms, it.closed_ms.unwrap_or(now));

    let top = up.last().copied().unwrap_or(0).max(1);

    // From nothing at the start to each day's end.

    let points = std::iter::once((0, 0))

        .chain(up.iter().enumerate().map(|(d, &n)| (d as i64 + 1, n)))

        .map(|(d, n)| format!("{},{}", d * BURN_W / days, BURN_H - n as i64 * BURN_H / top as i64))

        .collect::<Vec<_>>()

        .join(" ");

    Ok(IterationView {

        first: day_of(it.start_ms),

        last: day_of(it.end_ms - 1),

        days,

        so_far: up.len(),

        done: times.len(),

        points,

        top,

        it,

    })

}

    /// The open iteration, the past ones (newest first), and the one the

    /// page shows the cards of (#343).

    current: Option<IterationView>,

    past: Vec<IterationView>,

    shown_iteration: Option<IterationView>,

    /// A new iteration's suggested first and last days: today and 13 days on.

    next_first: String,

    next_last: String,

    let now = db::now_ms();

    let iterations = pdb::iterations(&app.db, project.id).await.map_err(e)?;

    let shown = view.iteration.and_then(|id| iterations.iter().find(|i| i.id == id && i.closed_ms.is_some()).cloned());

    // A past iteration's page is the archive of what was done in it.

    let archived = view.archived() || shown.is_some();

    if let Some(it) = &shown {

        for col in columns.iter_mut() {

            col.cards.retain(|c| c.card.iteration_id == Some(it.id));

        }

    }

    let done = pdb::done_column(&columns.iter().map(|c| c.column.clone()).collect::<Vec<_>>());

    let (mut current, mut past) = (None, Vec::new());

    for it in iterations {

        if it.closed_ms.is_none() {

            current = Some(iteration_view(app, it, done, now).await.map_err(e)?);

        } else if past.len() < PAST_SHOWN {

            past.push(iteration_view(app, it, done, now).await.map_err(e)?);

        }

    }

    let shown_iteration = match shown {

        Some(it) => Some(iteration_view(app, it, done, now).await.map_err(e)?),

        None => None,

    };

        current,

        past,

        shown_iteration,

        next_first: day_of(now),

        next_last: day_of(now + 13 * DAY_MS),

    /// The iteration to close, and a new one's first and last days as

    /// YYYY-MM-DD (#343).

    #[serde(default)]

    iteration: Option<i64>,

    #[serde(default)]

    start: String,

    #[serde(default)]

    end: String,

            }

        }

        "start_iteration" => {

            let name: String = f.name.trim().chars().take(60).collect();

            match (day_ms(&f.start), day_ms(&f.end)) {

                _ if name.is_empty() => error = Some("Name the iteration.".into()),

                (Some(start), Some(last)) if last >= start && last - start < 366 * DAY_MS => {

                    match pdb::start_iteration(&app.db, project.id, &name, start, last + DAY_MS).await.map_err(e)? {

                        Some(_) => notice = Some(format!("{name} started.")),

                        None => error = Some("Close the open iteration before starting another.".into()),

                    }

                }

                (Some(_), Some(_)) => error = Some("An iteration ends on or after its first day, within a year.".into()),

                _ => error = Some("Give the first and last days as dates.".into()),

            }

        }

        "close_iteration" => {

            let Some(id) = f.iteration else { return Err(Error::bad(shell, "no iteration")) };

            match pdb::close_iteration(&app.db, project.id, id, pdb::done_column(&columns)).await.map_err(e)? {

                Some(n) => {

                    let detail = format!("iteration {id}: {n} done card(s) archived");

                    db::accounts::audit(&app.db, viewer.account(), "project.iteration_closed", &format!("{owner}/{number}"), &detail)

                        .await

                        .ok();

                    notice = Some(format!("Closed: {n} done card{} archived under it.", if n == 1 { "" } else { "s" }));

                }

                None => error = Some("That iteration is not open.".into()),

/// #343: a board's iteration shows its days and burn-up; one is open at a

/// time; closing it archives the Done column's cards under it, and its page

/// shows them; a card restored from it no longer counts as done in it.

async fn board_iterations_close_into_the_archive() {

    let h = hub().await;

    let app = &h.app;

    let andy = register(app, "andy").await;

    let csrf = csrf_of(&get(app, "/projects/andy", Some(&andy)).await.body);

    post(app, "/projects/andy", Some(&andy), &format!("_csrf={csrf}&title=Roadmap")).await;

    let column = |name: &'static str| async move {

        sqlx::query_scalar::<_, i64>("SELECT id FROM project_columns WHERE name = $1").bind(name).fetch_one(&app.db).await.unwrap()

    };

    let (backlog, done) = (column("Backlog").await, column("Done").await);

    let project: i64 = sqlx::query_scalar("SELECT id FROM projects").fetch_one(&app.db).await.unwrap();

    let day = 86_400_000i64;

    let now = db::now_ms();

    for (i, (note, col, age)) in [("done-a", done, 2), ("done-b", done, 0), ("todo-c", backlog, 5)].into_iter().enumerate() {

        sqlx::query(

            "INSERT INTO project_cards (project_id, column_id, position, kind, note, created_ms, moved_ms) VALUES ($1, $2, $3, 'note', $4, 0, $5)",

        )

        .bind(project)

        .bind(col)

        .bind(i as i64)

        .bind(note)

        .bind(now - age * day)

        .execute(&app.db)

        .await

        .unwrap();

    }

    let date = |ms: i64| {

        let t = time::OffsetDateTime::from_unix_timestamp(ms / 1000).unwrap();

        format!("{:04}-{:02}-{:02}", t.year(), u8::from(t.month()), t.day())

    };

    let board = "/projects/andy/1";

    let (who, token) = (&andy, &csrf);

    let act = |body: String| async move { post(app, board, Some(who), &format!("_csrf={token}&{body}")).await };

    // No iteration yet: the board offers to start one.

    assert!(get(app, board, Some(&andy)).await.body.contains("start an iteration"));

    let r = act(format!("action=start_iteration&name=Sprint+1&start={}&end=nonsense", date(now))).await;

    assert!(r.body.contains("Give the first and last days as dates."), "{}", r.body);

    let r = act(format!("action=start_iteration&name=Sprint+1&start={}&end={}", date(now), date(now - 2 * day))).await;

    assert!(r.body.contains("ends on or after its first day"), "{}", r.body);

    let r = act(format!("action=start_iteration&name=Sprint+1&start={}&end={}", date(now - 3 * day), date(now + 10 * day))).await;

    assert!(r.body.contains("Sprint 1 started."), "{}", r.body);

    let page = get(app, board, Some(&andy)).await.body;

    assert!(page.contains("day 4 of 14 · 2 cards done"), "{page}");

    // Four days so far of fourteen (20 units each): done-a reached Done on

    // the second, done-b today.

    assert!(page.contains("<polyline points=\"0,64 20,64 40,32 60,32 80,0\""), "{page}");

    assert!(page.contains("value=\"close_iteration\""));

    let r = act(format!("action=start_iteration&name=Sprint+2&start={}&end={}", date(now), date(now + day))).await;

    assert!(r.body.contains("Close the open iteration before starting another."), "{}", r.body);

    // Closing it archives what is done; the rest stays on the board.

    let it: i64 = sqlx::query_scalar("SELECT id FROM project_iterations").fetch_one(&app.db).await.unwrap();

    let r = act(format!("action=close_iteration&iteration={it}")).await;

    assert!(r.body.contains("Closed: 2 done cards archived under it."), "{}", r.body);

    let page = get(app, board, Some(&andy)).await.body;

    assert!(!page.contains("done-a") && !page.contains("done-b") && page.contains("todo-c"), "{page}");

    assert!(page.contains(&format!("?iteration={it}\">Sprint 1</a>")) && page.contains("start an iteration"), "{page}");

    let r = act(format!("action=close_iteration&iteration={it}")).await;

    assert!(r.body.contains("That iteration is not open."), "{}", r.body);

    // Its page: the cards done in it, restorable, with its burn-up.

    let past = get(app, &format!("{board}?iteration={it}"), Some(&andy)).await.body;

    assert!(past.contains("done-a") && past.contains("done-b") && !past.contains("todo-c"), "{past}");

    assert!(past.contains("2 cards done, archived when it closed") && past.contains("class=\"burnup\""), "{past}");

    let a: i64 = sqlx::query_scalar("SELECT id FROM project_cards WHERE note = 'done-a'").fetch_one(&app.db).await.unwrap();

    act(format!("action=restore&card={a}")).await;

    let past = get(app, &format!("{board}?iteration={it}"), Some(&andy)).await.body;

    assert!(!past.contains("done-a") && past.contains("1 card done"), "{past}");

    assert!(get(app, board, Some(&andy)).await.body.contains("done-a"), "back on the board");

}

/// The burn-up counts cards done by the end of each day so far (#343).

#[test]

fn a_burn_up_counts_each_days_done_cards() {

    use oxygenhub::db::projects::burn_up;

    let day = 86_400_000i64;

    let start = 10 * day;

    // Done before the start, on day one, twice on day three; the iteration

    // has five days and it is midway through the fourth.

    let times = [start - day, start + 1000, start + 2 * day + 5, start + 2 * day + 9];

    assert_eq!(burn_up(&times, start, start + 5 * day, start + 3 * day + day / 2), [2, 2, 4, 4]);

    // After its end: every day, no more.

    assert_eq!(burn_up(&times, start, start + 5 * day, start + 40 * day).len(), 5);

    // Before its start: nothing yet.

    assert!(burn_up(&times, start, start + 5 * day, start - day).is_empty());

}

#[tokio::test(flavor = "multi_thread")]

{% macro burnup(v) %}<svg class="burnup" viewBox="0 0 {{ crate::web::projects::BURN_W }} {{ crate::web::projects::BURN_H }}" preserveAspectRatio="none" role="img" aria-label="Burn-up: {{ v.done }} card{% if v.done != 1 %}s{% endif %} done over {{ v.so_far }} of {{ v.days }} days"><line x1="0" y1="{{ crate::web::projects::BURN_H }}" x2="{{ crate::web::projects::BURN_W }}" y2="{{ crate::web::projects::BURN_H }}"></line><polyline points="{{ v.points }}"></polyline></svg>{% endmacro %}

{% if let Some(v) = shown_iteration %}

<section class="iteration past" aria-label="Iteration {{ v.it.name }}">

  <div><h2>{{ v.it.name }}</h2><p class="small mute">{{ v.first }} to {{ v.last }} · {{ v.done }} card{% if v.done != 1 %}s{% endif %} done, archived when it closed{% if can_edit %}; restore one to put it back on the board{% endif %} · <a href="/projects/{{ project.owner }}/{{ project.number }}">back to the board</a></p></div>

  {% call burnup(v) %}

</section>

{% else if archived %}<p class="note board-archive-note">Archived cards: off the board, kept and searchable{% if can_edit %}; restore one to put it back at the end of its column{% endif %}. <a href="/projects/{{ project.owner }}/{{ project.number }}">Back to the board</a></p>

{% else %}

<section class="iteration" aria-label="Iteration">

  {% if let Some(v) = current %}

  <div><h2>{{ v.it.name }}</h2><p class="small mute">{{ v.first }} to {{ v.last }} · day {{ v.so_far }} of {{ v.days }} · {{ v.done }} card{% if v.done != 1 %}s{% endif %} done</p>

  {% if can_edit %}<form class="tiny" method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="iteration" value="{{ v.it.id }}"><button name="action" value="close_iteration" class="small" data-confirm="Close {{ v.it.name }}? The cards in the Done column are archived under it.">CLOSE ITERATION</button></form>{% endif %}</div>

  {% call burnup(v) %}

  {% else if can_edit %}

  <details class="iteration-new"><summary class="small">start an iteration</summary>

    <form method="post" action="/projects/{{ project.owner }}/{{ project.number }}"><input type="hidden" name="_csrf" value="{{ shell.csrf }}"><input type="hidden" name="action" value="start_iteration">

      <label for="it-name">Name</label><input type="text" id="it-name" name="name" maxlength="60" placeholder="Sprint 1" required>

      <div class="inline-fields"><label>First day <input type="date" name="start" value="{{ next_first }}" required></label><label>Last day <input type="date" name="end" value="{{ next_last }}" required></label></div>

      <p class="small mute">Closing it archives the cards in the Done column under it.</p>

      <button type="submit" class="small">START</button>

    </form>

  </details>

  {% endif %}

  {% if !past.is_empty() %}<details class="iteration-past"><summary class="small">past iterations ({{ past.len() }})</summary><ul>{% for v in past %}<li><a href="/projects/{{ project.owner }}/{{ project.number }}?iteration={{ v.it.id }}">{{ v.it.name }}</a> <span class="small mute">{{ v.first }} to {{ v.last }} · {{ v.done }} done</span></li>{% endfor %}</ul></details>{% endif %}

</section>

{% endif %}

<form class="board-filter" method="get" action="/projects/{{ project.owner }}/{{ project.number }}" role="search">{% if let Some(v) = shown_iteration %}<input type="hidden" name="iteration" value="{{ v.it.id }}">{% else if archived %}<input type="hidden" name="archived" value="1">{% endif %}

/* A board's iteration (#343): its name and days beside its burn-up. */

.iteration { display: flex; flex-wrap: wrap; gap: 10px 18px; align-items: center; margin: 0 0 10px; padding: 8px 10px; background: var(--panel); border: 1px solid var(--line); }

.iteration h2 { font-size: 14px; margin: 0 0 2px; }

.iteration p { margin: 0 0 4px; }

.iteration > div { min-width: 0; flex: 1 1 16em; }

.iteration .burnup { flex: 0 1 280px; width: 280px; max-width: 100%; height: 64px; }

.burnup line { stroke: var(--line); stroke-width: 1; }

.burnup polyline { fill: none; stroke: var(--acc); stroke-width: 2; vector-effect: non-scaling-stroke; stroke-linejoin: round; }

.iteration-new form { display: flex; flex-direction: column; gap: 4px; margin-top: 6px; max-width: 26em; }

.iteration-new .inline-fields { display: flex; flex-wrap: wrap; gap: 8px; }

.iteration-past ul { margin: 6px 0 0; padding-left: 18px; }