-- issues: squashed from 0600_issues_planning.sql, 0601_projects.sql, 0602_discussions.sql.

-- 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)
);

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);

-- Workstream 6: projects (table/board views with custom fields).
CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  visibility TEXT NOT NULL DEFAULT 'private' CHECK (visibility IN ('public', 'private')),
  closed INTEGER NOT NULL DEFAULT 0,
  created_by TEXT NOT NULL REFERENCES accounts(id),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (owner_id, number)
);

-- kind: text | number | date | single_select; options is a JSON array of option names.
CREATE TABLE project_fields (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  kind TEXT NOT NULL CHECK (kind IN ('text', 'number', 'date', 'single_select')),
  options TEXT NOT NULL DEFAULT '[]',
  position INTEGER NOT NULL,
  UNIQUE (project_id, name)
);

CREATE TABLE project_items (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  issue_id TEXT REFERENCES issues(id) ON DELETE CASCADE,
  note TEXT,
  position REAL NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE (project_id, issue_id)
);

CREATE INDEX project_items_issue ON project_items(issue_id);

CREATE TABLE project_item_values (
  item_id TEXT NOT NULL REFERENCES project_items(id) ON DELETE CASCADE,
  field_id TEXT NOT NULL REFERENCES project_fields(id) ON DELETE CASCADE,
  value TEXT NOT NULL,
  PRIMARY KEY (item_id, field_id)
);

-- 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)
);