-- Workstream 6: discussions with categories, threaded replies, answers and upvotes.
CREATE TABLE discussion_categories (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  emoji TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  answerable INTEGER NOT NULL DEFAULT 0,
  UNIQUE (repo_id, name)
);

CREATE TABLE discussions (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  category_id TEXT NOT NULL REFERENCES discussion_categories(id),
  author_id TEXT NOT NULL REFERENCES accounts(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL DEFAULT '',
  answer_id TEXT,
  upvotes INTEGER NOT NULL DEFAULT 0,
  comments_count INTEGER NOT NULL DEFAULT 0,
  locked INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (repo_id, number)
);
CREATE INDEX discussions_repo ON discussions(repo_id, category_id, updated_at DESC);

CREATE TABLE discussion_comments (
  id TEXT PRIMARY KEY,
  discussion_id TEXT NOT NULL REFERENCES discussions(id) ON DELETE CASCADE,
  parent_id TEXT REFERENCES discussion_comments(id) ON DELETE CASCADE,
  author_id TEXT NOT NULL REFERENCES accounts(id),
  body TEXT NOT NULL,
  upvotes INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);
CREATE INDEX discussion_comments_discussion ON discussion_comments(discussion_id, created_at);

CREATE TABLE discussion_votes (
  subject_id TEXT NOT NULL,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (subject_id, user_id)
);