-- A user's named issue and merge request queries (#292). query is the
-- canonical text of the query; it is parsed again on every run, and @me
-- resolves to whoever runs it. A pinned query shows on the dashboard.
CREATE TABLE saved_queries (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name       TEXT NOT NULL,
    query      TEXT NOT NULL,
    pinned     INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
    UNIQUE (user_id, name)
);

-- A query reads each table by the repositories it may see, newest first.
CREATE INDEX issues_repo_created ON issues(repo_id, created_at);
CREATE INDEX merge_requests_repo_created ON merge_requests(repo_id, created_at);