-- 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;