-- table: authorship_remediation
CREATE TABLE authorship_remediation (
               id INTEGER PRIMARY KEY,
               sentence_id INTEGER NOT NULL REFERENCES sentences(id),
               original_author TEXT NOT NULL,
               remediation_author TEXT NOT NULL,
               finding TEXT NOT NULL,
               resolution TEXT NOT NULL,
               artifact_path TEXT NOT NULL,
               artifact_sha256 TEXT NOT NULL,
               created_at TEXT NOT NULL,
               UNIQUE(sentence_id, artifact_path)
           );

-- table: batches
CREATE TABLE batches (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL UNIQUE,            -- 'batch-001'
  cheatsheet_version TEXT,               -- mega cheatsheet doc version used
  guide_version TEXT,                    -- generation guide doc version used
  notes TEXT,
  created_at TEXT NOT NULL
);

-- table: lessons
CREATE TABLE lessons (
  id INTEGER PRIMARY KEY,
  batch_id INTEGER REFERENCES batches(id),
  category TEXT NOT NULL,                -- error-pattern|style|process
  lesson TEXT NOT NULL,
  guide_change TEXT,                     -- what was changed in the guide/cheatsheet
  created_at TEXT NOT NULL
);

-- table: lexeme_log
CREATE TABLE lexeme_log (id INTEGER PRIMARY KEY, headword TEXT, action TEXT, detail TEXT, created_at TEXT);

-- table: lexemes
CREATE TABLE lexemes (
  id INTEGER PRIMARY KEY,
  headword TEXT NOT NULL UNIQUE,      -- practical orthography (elder surface)
  phonemic TEXT,                      -- IPA (dictionary.yaml)
  pos TEXT NOT NULL,                  -- noun|adjective|verb-tr|verb-itr|locational|temporal|pronoun|quantifier|particle|suffix|clitic|compound
  conj_class TEXT,                    -- l | y | NA
  case_class TEXT,                    -- neutral | potent | both | seven-kin-neutral | NA
  gloss TEXT NOT NULL,
  senses TEXT,                        -- JSON [{sense, definition, status}]  status: attested|extended|transparent-compound|circumlocution
  attestation TEXT,                   -- JSON [{source, ref, quote}]
  allomorphy TEXT,                    -- case/inflection allomorph notes
  morphology TEXT,                    -- derivations, compounds, redup, suppletion
  collocations TEXT,                  -- attested collocations
  usage_notes TEXT,                   -- register, polysemy warnings, taboo substitutes
  antonyms_contrasts TEXT,
  corpus_freq INTEGER DEFAULT 0,
  status TEXT NOT NULL,               -- attested | transparent-compound | watchlist | phantom-retired
  verified_at TEXT,
  notes TEXT
);

-- table: process_events
CREATE TABLE process_events (
          id INTEGER PRIMARY KEY,
          batch_label TEXT NOT NULL,
          phase TEXT NOT NULL,
          event_type TEXT NOT NULL,
          actor TEXT NOT NULL,
          detail TEXT NOT NULL,
          command TEXT,
          output_excerpt TEXT,
          artifact_path TEXT,
          created_at TEXT NOT NULL
        );

-- table: reviews
CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  sentence_id INTEGER NOT NULL REFERENCES sentences(id),
  round INTEGER NOT NULL,
  lens TEXT NOT NULL,                    -- morphology|lexicon|syntax|fidelity|register
  verdict TEXT NOT NULL,                 -- pass|issue|fail
  issues TEXT,
  suggested_fix TEXT,
  reviewer TEXT NOT NULL,                -- claude-main | claude-review-agent
  created_at TEXT NOT NULL
);

-- table: revisions
CREATE TABLE revisions (
  id INTEGER PRIMARY KEY,
  sentence_id INTEGER NOT NULL REFERENCES sentences(id),
  old_kuku TEXT, new_kuku TEXT,
  old_english TEXT, new_english TEXT,
  old_analysis TEXT, new_analysis TEXT,
  reason TEXT NOT NULL,
  created_at TEXT NOT NULL
);

-- table: sentences
CREATE TABLE sentences (
  id INTEGER PRIMARY KEY,
  batch_id INTEGER NOT NULL REFERENCES batches(id),
  seq INTEGER NOT NULL,                  -- position within batch
  english TEXT NOT NULL,
  kuku TEXT NOT NULL,                    -- elder-practical surface orthography
  analysis TEXT NOT NULL,                -- Patz hyphenated morphemes + gloss
  frame TEXT NOT NULL,                   -- e.g. verbless_np_adj, transitive_past
  tier INTEGER,                          -- 1-6 difficulty ladder
  words_used TEXT,                       -- JSON array of dictionary bases
  loans_used TEXT,                       -- JSON array
  evidence TEXT,                         -- closest attested model(s): grammar §, elder/DB row
  status TEXT NOT NULL DEFAULT 'draft',  -- draft|under_review|accepted|revised|rejected
  confidence TEXT,                       -- high|medium
  rights_status TEXT NOT NULL DEFAULT 'project_approved_synthetic_pending_elder_verification',
  author TEXT NOT NULL DEFAULT 'claude', -- always claude; no scripts author sentences
  created_at TEXT NOT NULL,
  updated_at TEXT,
  UNIQUE(batch_id, seq)
);

-- index: idx_process_events_batch
CREATE INDEX idx_process_events_batch ON process_events(batch_label, created_at);

-- index: idx_process_events_type
CREATE INDEX idx_process_events_type ON process_events(event_type, created_at);
