-- +brant Up

-- Inline comments on a proposal, anchored to a block of a document.
--
-- The anchoring model was held back until the review UI existed (see the note
-- this migration removes from schema.sql), because a schema is the expensive
-- thing to get wrong here. What the built UI settled: comments live on
-- proposals, any block of a proposed document can carry one, and the anchor is
-- content-first.
--
-- The stored tuple is (doc_id, heading_path, block_index, block_hash). Line
-- numbers are deliberately absent: prose reflows, so a one-word edit moves
-- every line below it, which is the same property that made a line-oriented
-- differ useless for this corpus.
--
-- WHAT IS NOT STORED: whether a comment still fits. A comment is not outdated
-- in general — it is outdated *at a revision*, and a proposal branch moves
-- under it as the agent revises. Anchor state is derived at read time by
-- core.ResolveAnchor against the revision being displayed. A column here would
-- be a cache of a function of a moving input, and would be wrong every time the
-- agent pushed.
CREATE TABLE comment (
	id            SERIAL PRIMARY KEY,
	proposal_id   INTEGER NOT NULL REFERENCES proposal(id) ON DELETE CASCADE,
	-- A reply. One level only: a thread is a root plus its replies, which is
	-- what a review conversation with one human and one agent actually is.
	-- Enforced in db/comment.go, since a CHECK cannot look at another row.
	parent_id     INTEGER REFERENCES comment(id) ON DELETE CASCADE,

	-- The anchor. NULL on a reply, which inherits its root's rather than
	-- carrying a copy that could drift from it.
	doc_id        TEXT,                   -- archive addressing key: "SPEC-0007", or the path
	doc_path      TEXT,                   -- path as at comment time; display, and which diff it belongs to
	heading_path  TEXT[],                 -- enclosing headings, outermost first
	block_index   INTEGER,                -- position within heading_path, NOT within the document
	block_hash    TEXT,                   -- prosediff block hash when the comment was written
	side          TEXT,                   -- new | old ("old" only for a block the proposal deletes)

	body          TEXT NOT NULL,
	author        TEXT NOT NULL,          -- owner username, or "claude-code/spec-writer"
	author_kind   TEXT NOT NULL,          -- human | agent
	agent_session TEXT,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	-- Set when the owner resolves the thread. Agents may not resolve — an agent
	-- marking its own critique resolved would defeat the auto-merge gate below
	-- — which is enforced in the service layer, where the caller's identity is
	-- known; this column only records that it happened.
	resolved      TIMESTAMPTZ,

	CONSTRAINT ck_comment_body CHECK (length(btrim(body)) > 0),
	CONSTRAINT ck_comment_author CHECK (length(author) > 0),
	CONSTRAINT ck_comment_author_kind CHECK (author_kind IN ('human', 'agent')),
	-- Provenance, on the same rule as proposal: the identity strings, not the
	-- credential, are what identify who said what, so an agent comment without
	-- a session is not a comment with a missing field — it is an unattributable
	-- one. NOT NULL alone would accept the empty string and lose that.
	CONSTRAINT ck_comment_provenance CHECK (
		(author_kind = 'agent') = (agent_session IS NOT NULL AND length(agent_session) > 0)
	),
	-- A root carries the whole anchor and a reply carries none of it. Written
	-- as one predicate over every anchor column so a half-populated anchor —
	-- the shape a partial write would leave — cannot be stored at all.
	CONSTRAINT ck_comment_anchor CHECK (
		(parent_id IS NULL) = (doc_id IS NOT NULL)
		AND (doc_id IS NULL) = (doc_path IS NULL)
		AND (doc_id IS NULL) = (heading_path IS NULL)
		AND (doc_id IS NULL) = (block_index IS NULL)
		AND (doc_id IS NULL) = (block_hash IS NULL)
		AND (doc_id IS NULL) = (side IS NULL)
	),
	CONSTRAINT ck_comment_side CHECK (side IS NULL OR side IN ('new', 'old')),
	CONSTRAINT ck_comment_block_index CHECK (block_index IS NULL OR block_index >= 0),
	-- Resolution is a property of the thread, not of one message in it.
	CONSTRAINT ck_comment_resolved CHECK (resolved IS NULL OR parent_id IS NULL)
);

-- The review page reads a proposal's whole thread set in one go.
CREATE INDEX ix_comment_proposal ON comment (proposal_id, created);
-- Replies, by thread.
CREATE INDEX ix_comment_parent ON comment (parent_id) WHERE parent_id IS NOT NULL;
-- The auto-merge gate asks one question — "does this proposal have an
-- unresolved thread?" — on every policy merge, so it gets its own partial
-- index rather than scanning a proposal's comments to answer it.
CREATE INDEX ix_comment_unresolved ON comment (proposal_id)
	WHERE parent_id IS NULL AND resolved IS NULL;

-- +brant Down
DROP TABLE comment;