CREATE TABLE question (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  stem_md       TEXT NOT NULL,         -- en markdown
  type          TEXT NOT NULL,         -- 'single','multi','open','tf'
  explanation_md TEXT,                 -- feedback opcional
  points        REAL DEFAULT 1.0,
  lang          TEXT DEFAULT 'es',
  source_ref    TEXT,                  -- libro, módulo, etc.
  created_at    TEXT DEFAULT CURRENT_TIMESTAMP,
  updated_at    TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE option (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  question_id   INTEGER NOT NULL REFERENCES question(id) ON DELETE CASCADE,
  text_md       TEXT NOT NULL,
  is_correct    INTEGER NOT NULL DEFAULT 0,
  ord           INTEGER NOT NULL       -- orden canónico (antes de mezclar)
);

CREATE TABLE tag (
  id   INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE question_tag (
  question_id INTEGER NOT NULL REFERENCES question(id) ON DELETE CASCADE,
  tag_id      INTEGER NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
  PRIMARY KEY (question_id, tag_id)
);

-- Versión / histórico
CREATE TABLE question_rev (
  rev_id       INTEGER PRIMARY KEY AUTOINCREMENT,
  question_id  INTEGER NOT NULL,
  rev_ts       TEXT DEFAULT CURRENT_TIMESTAMP,
  user_name    TEXT,
  diff_text    TEXT      -- unified diff o JSON patch
);

• Nota: El “orden canónico” (ord) permite exportar siempre el mismo layout base; el barajado ocurre al generar exámenes.