CREATE TABLE IF NOT EXISTS failed_jobs (
  build_id INT,
  job_name TEXT,
  error_category TEXT,   -- from failedJobDetails: test-failure, build-error, crash, etc.
  error_snippet TEXT,
  known_issue_url TEXT,  -- NULL if unmatched
  known_issue_title TEXT,
  is_pr_correlated BOOLEAN DEFAULT FALSE,
  recovery_status TEXT DEFAULT 'not-checked',  -- effectively-passed, real-failure, no-results
  notes TEXT,
  PRIMARY KEY (build_id, job_name)
);

-- Unmatched failures (Build Analysis red = these exist)
SELECT job_name, error_category, error_snippet FROM failed_jobs
WHERE known_issue_url IS NULL;

-- Are ALL failures accounted for?
SELECT COUNT(*) as total,
       SUM(CASE WHEN known_issue_url IS NOT NULL THEN 1 ELSE 0 END) as matched
FROM failed_jobs;

-- Which crash/canceled jobs need recovery verification?
SELECT job_name, build_id FROM failed_jobs
WHERE error_category IN ('crash', 'unclassified') AND recovery_status = 'not-checked';

-- PR-correlated failures (fix before retrying)
SELECT job_name, error_snippet FROM failed_jobs WHERE is_pr_correlated = TRUE;

CREATE TABLE IF NOT EXISTS pr_comments (
  pr_number INT,
  repo TEXT DEFAULT 'dotnet/efcore',
  comment_id INT PRIMARY KEY,
  author TEXT,
  created_at TEXT,
  body TEXT,
  is_triage BOOLEAN DEFAULT FALSE  -- set TRUE if comment diagnoses a failure
);

-- What has already been diagnosed? (avoid re-investigating)
SELECT author, created_at, substr(body, 1, 200) FROM pr_comments
WHERE is_triage = TRUE ORDER BY created_at;

-- Cross-PR: same failure discussed in multiple PRs?
SELECT pr_number, author, substr(body, 1, 150) FROM pr_comments
WHERE body LIKE '%BlazorWasm%' ORDER BY created_at;

-- Who was asked to investigate what?
SELECT author, substr(body, 1, 200) FROM pr_comments
WHERE body LIKE '%PTAL%' OR body LIKE '%could you%look%';