-- Board views and history (oxygenhub#361, #357): a column's work-in-progress

-- limit; a board's saved views, each a layout with its filter, lanes and

-- sort; and every card's moves (added, moved, archived, restored, removed),

-- from which cumulative flow, cycle time and forecasts are drawn. A move to

-- no column (NULL) takes the card off the board. Cards already on boards

-- start their history where they are now.

ALTER TABLE project_columns ADD COLUMN wip_limit BIGINT;

CREATE TABLE project_views (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

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

    name       TEXT NOT NULL,

    layout     TEXT NOT NULL DEFAULT 'board',

    q          TEXT NOT NULL DEFAULT '',

    lane       TEXT NOT NULL DEFAULT '',

    sort       TEXT NOT NULL DEFAULT '',

    position   BIGINT NOT NULL,

    created_ms BIGINT NOT NULL,

    UNIQUE (project_id, name)

);

CREATE TABLE project_card_moves (

    id         BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

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

    card_id    BIGINT NOT NULL,

    column_id  BIGINT,

    at_ms      BIGINT NOT NULL

);

CREATE INDEX project_card_moves_by_project ON project_card_moves (project_id, at_ms);

INSERT INTO project_card_moves (project_id, card_id, column_id, at_ms)

    SELECT project_id, id, column_id, moved_ms FROM project_cards;

INSERT INTO project_card_moves (project_id, card_id, column_id, at_ms)

    SELECT project_id, id, NULL, archived_ms FROM project_cards WHERE archived_ms IS NOT NULL;