-- kb-versioning (spec 119): append-only version snapshots for KB articles.
-- Every explicit save ([Save & version]), publish, and restore writes an
-- immutable JSONB snapshot of the article's title + Tiptap content.

CREATE TABLE IF NOT EXISTS kb_article_versions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  article_id UUID NOT NULL REFERENCES kb_articles(id) ON DELETE CASCADE,
  tenant_id UUID NOT NULL,
  version_number INTEGER NOT NULL,
  title TEXT NOT NULL,
  content JSONB NOT NULL,
  label TEXT,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (article_id, version_number)
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS idx_kb_article_versions_article
  ON kb_article_versions(article_id, version_number DESC);
