-- pulls: squashed from 0500_pull_review.sql.

CREATE TABLE pull_auto_merges (
  issue_id TEXT PRIMARY KEY REFERENCES issues(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  method TEXT NOT NULL CHECK (method IN ('merge', 'squash', 'rebase')),
  message TEXT NOT NULL DEFAULT '',
  enabled_by TEXT NOT NULL REFERENCES accounts(id),
  created_at INTEGER NOT NULL,
  head_sha TEXT
);

CREATE INDEX pull_auto_merges_repo ON pull_auto_merges(repo_id);

CREATE TABLE merge_queue_entries (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  base_ref TEXT NOT NULL,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  method TEXT NOT NULL CHECK (method IN ('merge', 'squash')),
  state TEXT NOT NULL CHECK (state IN ('queued', 'testing', 'merged', 'failed', 'removed')),
  speculative_sha TEXT,
  base_sha TEXT,
  head_sha TEXT,
  error TEXT,
  enqueued_by TEXT NOT NULL REFERENCES accounts(id),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX merge_queue_active ON merge_queue_entries(repo_id, base_ref, state, created_at);

CREATE UNIQUE INDEX merge_queue_issue_active ON merge_queue_entries(issue_id) WHERE state IN ('queued', 'testing');

CREATE INDEX merge_queue_speculative ON merge_queue_entries(repo_id, speculative_sha);

-- Development sidebar: manual links (closing-keyword links are derived from the body at read time).
CREATE TABLE pull_issue_links (
  pull_issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  source TEXT NOT NULL CHECK (source IN ('manual', 'keyword')),
  created_by TEXT REFERENCES accounts(id),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (pull_issue_id, issue_id)
);

CREATE INDEX pull_issue_links_issue ON pull_issue_links(issue_id);