BEGIN;

SET ROLE work_role;

CREATE TABLE IF NOT EXISTS work.graph_result_candidates (
  result_candidate_id TEXT PRIMARY KEY,
  graph_id TEXT NOT NULL,
  graph_revision BIGINT NOT NULL,
  node_id TEXT NOT NULL,
  author_run_id TEXT NOT NULL,
  artifact_refs TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  evidence_summary TEXT NOT NULL,
  summary TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (result_candidate_id <> ''), CHECK (graph_id <> ''), CHECK (graph_revision > 0),
  CHECK (node_id <> ''), CHECK (author_run_id <> ''), CHECK (evidence_summary <> ''), CHECK (summary <> ''),
  FOREIGN KEY (graph_id, graph_revision) REFERENCES work.graphs(graph_id, revision)
);

CREATE TABLE IF NOT EXISTS work.graph_conflicts (
  conflict_id TEXT PRIMARY KEY,
  graph_id TEXT NOT NULL,
  graph_revision BIGINT NOT NULL,
  node_id TEXT NOT NULL,
  candidate_ids TEXT[] NOT NULL,
  resolved BOOLEAN NOT NULL DEFAULT FALSE,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (conflict_id <> ''), CHECK (graph_id <> ''), CHECK (graph_revision > 0), CHECK (node_id <> ''),
  CHECK (cardinality(candidate_ids) >= 2),
  FOREIGN KEY (graph_id, graph_revision) REFERENCES work.graphs(graph_id, revision)
);

CREATE TABLE IF NOT EXISTS work.graph_synthesis_candidates (
  synthesis_id TEXT PRIMARY KEY,
  graph_id TEXT NOT NULL,
  graph_revision BIGINT NOT NULL,
  node_id TEXT NOT NULL,
  candidate_ids TEXT[] NOT NULL,
  selected_candidate_id TEXT NOT NULL,
  dissent_candidate_ids TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  verifier_run_id TEXT NOT NULL,
  verifier_identity TEXT NOT NULL,
  evidence_refs TEXT[] NOT NULL,
  summary TEXT NOT NULL,
  state TEXT NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (synthesis_id <> ''), CHECK (graph_id <> ''), CHECK (graph_revision > 0), CHECK (node_id <> ''),
  CHECK (cardinality(candidate_ids) >= 2), CHECK (selected_candidate_id <> ''), CHECK (verifier_run_id <> ''),
  CHECK (verifier_identity <> ''), CHECK (cardinality(evidence_refs) >= 1), CHECK (summary <> ''), CHECK (state = 'VERIFIED'),
  FOREIGN KEY (graph_id, graph_revision) REFERENCES work.graphs(graph_id, revision)
);

CREATE INDEX IF NOT EXISTS graph_result_candidates_node_idx ON work.graph_result_candidates(graph_id, graph_revision, node_id);
CREATE INDEX IF NOT EXISTS graph_conflicts_node_idx ON work.graph_conflicts(graph_id, graph_revision, node_id);
CREATE INDEX IF NOT EXISTS graph_synthesis_node_idx ON work.graph_synthesis_candidates(graph_id, graph_revision, node_id);

RESET ROLE;
COMMIT;