-- Snippets: small, shareable collections of text files owned by one user. Each save writes a
-- revision holding the whole set of files as JSON, so history never depends on later edits.
CREATE TABLE snippets (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  visibility TEXT NOT NULL CHECK (visibility IN ('public','unlisted','private')),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX snippets_owner ON snippets (owner_id, updated_at);

CREATE TABLE snippet_files (
  snippet_id TEXT NOT NULL REFERENCES snippets(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  content TEXT NOT NULL,
  position INTEGER NOT NULL,
  PRIMARY KEY (snippet_id, name)
);

CREATE TABLE snippet_revisions (
  id TEXT PRIMARY KEY,
  snippet_id TEXT NOT NULL REFERENCES snippets(id) ON DELETE CASCADE,
  author_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TEXT NOT NULL,
  files_json TEXT NOT NULL
);
CREATE INDEX snippet_revisions_snippet ON snippet_revisions (snippet_id, created_at);