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

CREATE TABLE project_views (

    id         INTEGER PRIMARY KEY,

    project_id INTEGER 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   INTEGER NOT NULL,

    created_ms INTEGER NOT NULL,

    UNIQUE (project_id, name)

);

CREATE TABLE project_card_moves (

    id         INTEGER PRIMARY KEY,

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

    card_id    INTEGER NOT NULL,

    column_id  INTEGER,

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