-- 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,
  PRIMARY KEY (repo_id, number)
);

-- subject_type: issue | comment | review_comment | discussion | discussion_comment
CREATE TABLE reactions (
  subject_type TEXT NOT NULL,
  subject_id TEXT NOT NULL,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (subject_type, subject_id, user_id, content)
);
CREATE INDEX reactions_subject ON reactions(subject_type, subject_id);

CREATE TABLE attachments (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  uploader_id TEXT NOT NULL REFERENCES accounts(id),
  name TEXT NOT NULL,
  content_type TEXT NOT NULL,
  size INTEGER NOT NULL,
  r2_key TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
CREATE INDEX attachments_owner ON attachments(owner_id);
CREATE INDEX attachments_uploader ON attachments(uploader_id);