gitfor/platform

-- Workstream 6: milestones, issue types, sub-issues, pins, transfers, reactions, attachments.
CREATE TABLE milestones (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  title TEXT NOT NULL COLLATE NOCASE,
  description TEXT NOT NULL DEFAULT '',
  due_on INTEGER,
  state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (repo_id, title)
);

CREATE TABLE issue_types (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  color TEXT NOT NULL,
  description TEXT,
  UNIQUE (repo_id, name)
);

ALTER TABLE issues ADD COLUMN milestone_id TEXT REFERENCES milestones(id) ON DELETE SET NULL;
ALTER TABLE issues ADD COLUMN type_id TEXT REFERENCES issue_types(id) ON DELETE SET NULL;
ALTER TABLE issues ADD COLUMN parent_id TEXT REFERENCES issues(id) ON DELETE SET NULL;
CREATE INDEX issues_milestone ON issues(milestone_id, state);
CREATE INDEX issues_parent ON issues(parent_id, state);

CREATE TABLE pinned_issues (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, issue_id)
);

-- Old (repo, number) addresses of transferred issues so links keep working.
CREATE TABLE issue_redirects (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,