-- Pending review drafts — inline comments staged before a reviewer submits.
-- One pending_review row per (pr, author). Many pending_review_comments per review.
CREATE TABLE IF NOT EXISTS pending_reviews (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pr_id       UUID NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
  author_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (pr_id, author_id)
);

CREATE TABLE IF NOT EXISTS pending_review_comments (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  review_id   UUID NOT NULL REFERENCES pending_reviews(id) ON DELETE CASCADE,
  file_path   TEXT NOT NULL,
  line_number INTEGER NOT NULL,
  body        TEXT NOT NULL,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pending_reviews_pr    ON pending_reviews(pr_id);
CREATE INDEX IF NOT EXISTS idx_pending_reviews_author ON pending_reviews(pr_id, author_id);
CREATE INDEX IF NOT EXISTS idx_pending_review_comments_review ON pending_review_comments(review_id);