-- Projects: boards of issues, proposals and notes across one owner's

-- repositories (hub spec §8.2).

CREATE TABLE projects (

    id          INTEGER PRIMARY KEY,

    owner       TEXT NOT NULL,

    number      INTEGER NOT NULL,

    title       TEXT NOT NULL,

    description TEXT NOT NULL DEFAULT '',

    public      INTEGER NOT NULL DEFAULT 1,

    closed      INTEGER NOT NULL DEFAULT 0,

    created_ms  INTEGER NOT NULL,

    updated_ms  INTEGER NOT NULL,

    UNIQUE (owner, number)

);

CREATE TABLE project_columns (

    id         INTEGER PRIMARY KEY,

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

    name       TEXT NOT NULL,

    position   INTEGER NOT NULL

);

CREATE INDEX project_columns_by_project ON project_columns (project_id, position);

CREATE TABLE project_cards (

    id         INTEGER PRIMARY KEY,

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

    column_id  INTEGER NOT NULL REFERENCES project_columns(id) ON DELETE CASCADE,

    position   INTEGER NOT NULL,

    kind       TEXT NOT NULL CHECK (kind IN ('issue', 'proposal', 'note')),

    repo_id    INTEGER REFERENCES repositories(id) ON DELETE CASCADE,

    number     INTEGER,

    note       TEXT NOT NULL DEFAULT '',

    added_by   TEXT NOT NULL DEFAULT '',

    created_ms INTEGER NOT NULL

);

CREATE INDEX project_cards_by_column ON project_cards (column_id, position);

CREATE UNIQUE INDEX project_cards_once ON project_cards (project_id, repo_id, number) WHERE kind <> 'note';