-- ============================================================================
-- Krow — immutable published versions
--
-- Phase 3. §3: "Immutable versions. Editing publishes a new version. Running
-- conversations pin the version they started with."
--
-- WHAT WAS WRONG
--
-- `agent_definitions` holds one row per (org, definition_id) and editing it
-- UPDATEs that row in place. The `version` column moves, but nothing keeps what
-- version 2 said — so "which agent answered this?" has no answer once somebody
-- saves, and the `agent_version` recorded on every run in `agent_runs` points at
-- a definition that no longer exists in that form.
--
-- That is tolerable for a curated set shipped with the deployment and wrong the
-- moment a tenant edits their own agent, which is exactly the line Phase 3 has
-- to cross.
--
-- WHY A SECOND TABLE RATHER THAN VERSIONING THE FIRST
--
-- The alternative is to widen the unique index to (org_id, definition_id,
-- version) and mark one row current. It is fewer tables and it makes every
-- existing read ambiguous: a query for "the Activity Agent" would silently
-- return however many rows exist, and the ones that forgot the version
-- predicate would appear to work until the second version was published.
--
-- So `agent_definitions` keeps meaning exactly what it means today — the
-- current, editable definition — and every read of it is unchanged. This table
-- is the history beside it, and it is APPEND-ONLY: no UPDATE path exists in the
-- repository, and the trigger below refuses one at the database.
--
-- WHAT PINS A RUN
--
-- A run records agent_version already. With this table that number becomes
-- resolvable: the loader can load the definition AS IT WAS, which is what makes
-- a resumed run — and, more importantly, an approved write — execute against
-- the agent the person was actually looking at. A confirmation approved against
-- version 3 must not be carried out by version 4's tool list.
--
-- WHAT IS DELIBERATELY ABSENT
--
--   a diff or patch format   Versions are whole snapshots. A patch chain has to
--                            be replayed to be read, and a corrupted link makes
--                            every later version unreadable. Markdown is small.
--   deletion                 There is no path to remove a version. A trajectory
--                            referencing one that had been deleted would be a
--                            record nobody can explain, which is the thing
--                            §6 exists to prevent.
-- ============================================================================

SET search_path = public;

CREATE TABLE definition_versions (
  id            uuid        PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Which kind of definition. Agents and skills version identically and are
  -- kept in one table for that reason: two tables with the same columns and the
  -- same rules is two places to fix the next rule.
  kind          text        NOT NULL,

  org_id        uuid        NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,

  -- The author-facing id, NOT a foreign key to agent_definitions. A version
  -- must outlive the definition it came from: deleting an agent must not
  -- destroy the record of what it said while it was answering.
  definition_id text        NOT NULL,

  version       integer     NOT NULL,

  -- The whole definition as it was. Snapshot, not patch — see the note above.
  markdown      text        NOT NULL,

  -- Denormalised for listing a history without parsing every blob.
  name          text        NOT NULL DEFAULT '',
  description   text        NOT NULL DEFAULT '',
  pages         text[]      NOT NULL DEFAULT '{}',

  -- Who published it and when. SET NULL so a departed author's versions stay
  -- readable — the organization still has to answer for what its agents did.
  published_by  uuid        REFERENCES users (id) ON DELETE SET NULL,
  published_at  timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT definition_versions_kind_check CHECK (kind IN ('agent', 'skill')),
  CONSTRAINT definition_versions_version_positive CHECK (version >= 1),
  CONSTRAINT definition_versions_markdown_not_blank CHECK (length(btrim(markdown)) > 0),
  -- One row per version per definition per tenant. This is what makes a version
  -- number mean something: publishing the same number twice is a bug, and it
  -- fails here rather than leaving two rows that disagree.
  CONSTRAINT definition_versions_unique UNIQUE (org_id, kind, definition_id, version)
);

-- "Show me this definition's history", newest first. Also the lookup that
-- resolves one specific version, which is the hot path.
CREATE INDEX definition_versions_lookup_idx
  ON definition_versions (org_id, kind, definition_id, version DESC);

-- Append-only, enforced here rather than trusted to the repository.
--
-- A published version is a record of what a person approved and what an agent
-- answered with. Code that edits one is code that rewrites history, and the
-- reason to put this in the database is that the repository is not the only
-- thing that can reach the table — a migration, a console session and a future
-- service all can.
CREATE FUNCTION definition_versions_immutable() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION
    'definition_versions is append-only: version % of % cannot be % (publish a new version instead)',
    OLD.version, OLD.definition_id, lower(TG_OP);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER definition_versions_no_update
  BEFORE UPDATE OR DELETE ON definition_versions
  FOR EACH ROW EXECUTE FUNCTION definition_versions_immutable();

COMMENT ON TABLE definition_versions IS
  'Every published version of an agent or skill, as a whole snapshot. Append-only, enforced by '
  'trigger. A run records agent_version; this is what makes that number resolvable back to the '
  'definition that actually answered. See §3.';