-- +brant Up
CREATE TABLE space (
	id            SERIAL PRIMARY KEY,
	owner         TEXT NOT NULL,
	name          TEXT NOT NULL,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	CONSTRAINT uq_space_owner_name UNIQUE (owner, name)
);

CREATE TABLE document_id (
	doc_id        TEXT PRIMARY KEY,
	space_id      INTEGER NOT NULL REFERENCES space(id) ON DELETE CASCADE,
	path          TEXT NOT NULL,
	updated_rev   TEXT NOT NULL
);

CREATE TABLE proposal (
	id            SERIAL PRIMARY KEY,
	space_id      INTEGER NOT NULL REFERENCES space(id) ON DELETE CASCADE,
	title         TEXT NOT NULL,
	rationale     TEXT,
	base_rev      TEXT NOT NULL,
	branch        TEXT NOT NULL,
	state         TEXT NOT NULL,
	approval      TEXT,
	merged_rev    TEXT,
	agent         TEXT NOT NULL,
	agent_session TEXT NOT NULL,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	resolved      TIMESTAMPTZ,

	CONSTRAINT ck_proposal_state CHECK (state IN ('open', 'merged', 'rejected')),
	CONSTRAINT ck_proposal_approval CHECK (approval IS NULL OR approval IN ('human', 'policy')),
	CONSTRAINT ck_proposal_merged CHECK ((state = 'merged') = (approval IS NOT NULL)),
	CONSTRAINT ck_proposal_merged_rev CHECK ((state = 'merged') = (merged_rev IS NOT NULL)),
	CONSTRAINT ck_proposal_resolved CHECK ((state = 'open') = (resolved IS NULL)),
	CONSTRAINT ck_proposal_provenance CHECK (length(agent) > 0 AND length(agent_session) > 0)
);
CREATE INDEX ix_proposal_state_created ON proposal (state, created DESC);

CREATE TABLE agent_token (
	id            SERIAL PRIMARY KEY,
	name          TEXT NOT NULL,
	token_hash    BYTEA NOT NULL UNIQUE,
	created       TIMESTAMPTZ NOT NULL DEFAULT now(),
	revoked       TIMESTAMPTZ
);

CREATE TABLE index_stamp (
	space_id      INTEGER PRIMARY KEY REFERENCES space(id) ON DELETE CASCADE,
	rev           TEXT NOT NULL,
	indexed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE digest_mark (
	owner         TEXT PRIMARY KEY,
	seen_at       TIMESTAMPTZ NOT NULL
);

-- +brant Down
DROP TABLE digest_mark;
DROP TABLE index_stamp;
DROP TABLE agent_token;
DROP TABLE proposal;
DROP TABLE document_id;
DROP TABLE space;