// Schéma SQLite d'Immbot AI — idempotent (CREATE TABLE IF NOT EXISTS).
// Conçu pour rester portable vers PostgreSQL (types simples, pas de trigger exotique).

export const SCHEMA = `
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- ============================= Utilisateurs et sécurité =============================
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL DEFAULT '',
  email TEXT,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'student' CHECK (role IN ('student','instructor','admin')),
  must_change_password INTEGER NOT NULL DEFAULT 0,
  is_initial_admin INTEGER NOT NULL DEFAULT 0,
  auth_provider TEXT NOT NULL DEFAULT 'local',
  external_id TEXT,
  disabled INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  last_login_at TEXT
);

CREATE TABLE IF NOT EXISTS sessions (
  token_hash TEXT PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  expires_at TEXT NOT NULL,
  ip TEXT, user_agent TEXT
);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);

CREATE TABLE IF NOT EXISTS auth_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER, username TEXT, event TEXT NOT NULL, ip TEXT, detail TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_auth_events_time ON auth_events(created_at);

-- ============================= Cours et contenu =============================
CREATE TABLE IF NOT EXISTS courses (
  code TEXT PRIMARY KEY,              -- 'IMM1003'
  full_code TEXT NOT NULL,            -- 'IMM1003-20'
  title TEXT NOT NULL,
  session_label TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  color TEXT NOT NULL DEFAULT '#003E7E',
  active INTEGER NOT NULL DEFAULT 1,
  source_path TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS enrollments (
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_code TEXT NOT NULL REFERENCES courses(code) ON DELETE CASCADE,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (user_id, course_code)
);

CREATE TABLE IF NOT EXISTS documents (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_code TEXT REFERENCES courses(code),
  space TEXT NOT NULL,                -- official-imm1003 | official-imm1033 | instructor-private | student-temporary-upload | student-persistent-files
  path TEXT NOT NULL UNIQUE,
  filename TEXT NOT NULL,
  doc_type TEXT NOT NULL,             -- slides | plan | exercise | solution | aide-memoire | glossary | markdown | upload | exam
  title TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT '',
  week INTEGER,
  checksum TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'ok',  -- ok | error | pending
  error TEXT,
  visible_to_students INTEGER NOT NULL DEFAULT 1,
  ingested_at TEXT,
  chunk_count INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_documents_course ON documents(course_code, space);

CREATE TABLE IF NOT EXISTS chunks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  document_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  course_code TEXT,
  space TEXT NOT NULL,
  seq INTEGER NOT NULL,
  ref_type TEXT NOT NULL,             -- slide | section | exercise | glossary | page | sheet
  ref_number INTEGER,
  ref_label TEXT NOT NULL DEFAULT '', -- 'Séance 4 — Diapositive 18'
  section_title TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL DEFAULT '',
  content TEXT NOT NULL,              -- texte indexable (détexifié)
  display_content TEXT NOT NULL DEFAULT '', -- version affichable (markdown + $math$)
  box_types TEXT NOT NULL DEFAULT '', -- 'definition,important,example,formula'
  week INTEGER,
  owner_user_id INTEGER,              -- pour les espaces étudiants
  conversation_id INTEGER,            -- pour student-temporary-upload
  embedding BLOB
);
CREATE INDEX IF NOT EXISTS idx_chunks_doc ON chunks(document_id);
CREATE INDEX IF NOT EXISTS idx_chunks_space ON chunks(space, course_code);

CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(
  title, content,
  tokenize = 'unicode61 remove_diacritics 2'
);

CREATE TABLE IF NOT EXISTS ingestion_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  started_at TEXT NOT NULL DEFAULT (datetime('now')),
  finished_at TEXT,
  triggered_by TEXT NOT NULL DEFAULT 'script',
  files_scanned INTEGER NOT NULL DEFAULT 0,
  files_ingested INTEGER NOT NULL DEFAULT 0,
  files_skipped INTEGER NOT NULL DEFAULT 0,
  chunks_created INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'running',
  report TEXT NOT NULL DEFAULT ''
);

-- ============================= Conversations =============================
CREATE TABLE IF NOT EXISTS conversations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_code TEXT REFERENCES courses(code),
  title TEXT NOT NULL DEFAULT 'Nouvelle conversation',
  folder TEXT NOT NULL DEFAULT '',
  pinned INTEGER NOT NULL DEFAULT 0,
  archived INTEGER NOT NULL DEFAULT 0,
  mode TEXT NOT NULL DEFAULT 'ask',
  knowledge_mode TEXT NOT NULL DEFAULT 'course-only',
  model TEXT NOT NULL DEFAULT '',
  parent_conversation_id INTEGER,
  branched_from_message_id INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_conversations_user ON conversations(user_id, archived, updated_at);

CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('user','assistant','system')),
  content TEXT NOT NULL,
  citations TEXT NOT NULL DEFAULT '[]',   -- JSON résolu côté serveur
  attachments TEXT NOT NULL DEFAULT '[]', -- JSON [{id, filename, mime}]
  model TEXT NOT NULL DEFAULT '',
  mode TEXT NOT NULL DEFAULT '',
  knowledge_mode TEXT NOT NULL DEFAULT '',
  tokens_in INTEGER NOT NULL DEFAULT 0,
  tokens_out INTEGER NOT NULL DEFAULT 0,
  cost REAL NOT NULL DEFAULT 0,
  feedback INTEGER NOT NULL DEFAULT 0,
  flagged INTEGER NOT NULL DEFAULT 0,
  saved INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_messages_conv ON messages(conversation_id, id);

CREATE TABLE IF NOT EXISTS uploads (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  conversation_id INTEGER,
  filename TEXT NOT NULL,
  mime TEXT NOT NULL,
  size INTEGER NOT NULL,
  path TEXT NOT NULL,
  extracted_text TEXT NOT NULL DEFAULT '',
  persistent INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS report_flags (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL,
  reason TEXT NOT NULL DEFAULT '',
  resolved INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================= Modèles et usage =============================
CREATE TABLE IF NOT EXISTS model_overrides (
  model_id TEXT PRIMARY KEY,
  enabled INTEGER NOT NULL DEFAULT 1,
  note TEXT NOT NULL DEFAULT '',
  favorite INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS usage_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  model TEXT NOT NULL,
  kind TEXT NOT NULL DEFAULT 'chat',  -- chat | quiz-gen | flashcard-gen | exam-gen | summary | title
  tokens_in INTEGER NOT NULL DEFAULT 0,
  tokens_out INTEGER NOT NULL DEFAULT 0,
  cost REAL NOT NULL DEFAULT 0,
  latency_ms INTEGER NOT NULL DEFAULT 0,
  ok INTEGER NOT NULL DEFAULT 1,
  error TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_usage_user_time ON usage_log(user_id, created_at);

-- ============================= Paramètres, prompts, annonces =============================
CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS prompt_versions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,                 -- ex. 'base-system', 'course-imm1003', 'tutor-mode'
  content TEXT NOT NULL,
  version INTEGER NOT NULL,
  active INTEGER NOT NULL DEFAULT 1,
  created_by TEXT NOT NULL DEFAULT 'seed',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_prompt_name ON prompt_versions(name, active);

CREATE TABLE IF NOT EXISTS announcements (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  course_code TEXT,
  pinned INTEGER NOT NULL DEFAULT 0,
  active INTEGER NOT NULL DEFAULT 1,
  created_by INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================= Concepts et maîtrise =============================
CREATE TABLE IF NOT EXISTS concepts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_code TEXT NOT NULL REFERENCES courses(code),
  slug TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  week INTEGER,
  importance INTEGER NOT NULL DEFAULT 2,  -- 1 faible, 2 moyenne, 3 haute
  axis TEXT NOT NULL DEFAULT 'connaissances', -- connaissances|calcul|interpretation|jugement|communication
  UNIQUE (course_code, slug)
);

CREATE TABLE IF NOT EXISTS concept_links (
  from_id INTEGER NOT NULL REFERENCES concepts(id) ON DELETE CASCADE,
  to_id INTEGER NOT NULL REFERENCES concepts(id) ON DELETE CASCADE,
  type TEXT NOT NULL DEFAULT 'relation', -- prerequis | approfondissement | relation | application
  PRIMARY KEY (from_id, to_id, type)
);

CREATE TABLE IF NOT EXISTS mastery (
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  concept_id INTEGER NOT NULL REFERENCES concepts(id) ON DELETE CASCADE,
  score REAL NOT NULL DEFAULT 0,
  observations INTEGER NOT NULL DEFAULT 0,
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (user_id, concept_id)
);

CREATE TABLE IF NOT EXISTS mastery_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  concept_id INTEGER NOT NULL,
  kind TEXT NOT NULL,                -- flashcard | quiz | exam | correction
  correct INTEGER NOT NULL,
  difficulty INTEGER NOT NULL DEFAULT 3,
  autonomy REAL NOT NULL DEFAULT 1,  -- 1 sans aide, <1 avec indices
  confidence INTEGER,                -- 1-5 déclaré, null si non demandé
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_mastery_events ON mastery_events(user_id, concept_id, created_at);

-- ============================= Flashcards (SM-2) =============================
CREATE TABLE IF NOT EXISTS flashcards (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_code TEXT NOT NULL REFERENCES courses(code),
  concept_id INTEGER REFERENCES concepts(id),
  type TEXT NOT NULL DEFAULT 'qa',   -- qa | definition | formula | error | comparison | calc
  front TEXT NOT NULL,
  back TEXT NOT NULL,
  source_chunk_id INTEGER,
  created_by TEXT NOT NULL DEFAULT 'seed', -- seed | ai | instructor | user
  owner_user_id INTEGER,             -- null = partagée (officielle)
  validated INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_flashcards_course ON flashcards(course_code, concept_id);

CREATE TABLE IF NOT EXISTS card_states (
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  card_id INTEGER NOT NULL REFERENCES flashcards(id) ON DELETE CASCADE,
  ef REAL NOT NULL DEFAULT 2.5,
  interval_days REAL NOT NULL DEFAULT 0,
  reps INTEGER NOT NULL DEFAULT 0,
  lapses INTEGER NOT NULL DEFAULT 0,
  due_at TEXT NOT NULL DEFAULT (datetime('now')),
  suspended INTEGER NOT NULL DEFAULT 0,
  favorite INTEGER NOT NULL DEFAULT 0,
  last_reviewed_at TEXT,
  PRIMARY KEY (user_id, card_id)
);
CREATE INDEX IF NOT EXISTS idx_card_states_due ON card_states(user_id, suspended, due_at);

CREATE TABLE IF NOT EXISTS review_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  card_id INTEGER NOT NULL,
  q INTEGER NOT NULL,                -- qualité 2/3/4/5
  interval_before REAL NOT NULL DEFAULT 0,
  reviewed_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================= Quiz =============================
CREATE TABLE IF NOT EXISTS quiz_questions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_code TEXT NOT NULL REFERENCES courses(code),
  concept_id INTEGER REFERENCES concepts(id),
  type TEXT NOT NULL DEFAULT 'mcq',  -- mcq | short | calc | error-detect | case | order | match
  difficulty INTEGER NOT NULL DEFAULT 3, -- 1-5
  question TEXT NOT NULL,
  options TEXT NOT NULL DEFAULT '[]', -- JSON
  answer TEXT NOT NULL,
  explanation TEXT NOT NULL DEFAULT '',
  source_chunk_id INTEGER,
  created_by TEXT NOT NULL DEFAULT 'seed',
  validated INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_quiz_course ON quiz_questions(course_code, concept_id, difficulty);

CREATE TABLE IF NOT EXISTS quiz_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_code TEXT NOT NULL,
  focus_concept_id INTEGER,
  started_at TEXT NOT NULL DEFAULT (datetime('now')),
  finished_at TEXT,
  n_correct INTEGER NOT NULL DEFAULT 0,
  n_total INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS quiz_answers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id INTEGER NOT NULL REFERENCES quiz_sessions(id) ON DELETE CASCADE,
  question_id INTEGER NOT NULL,
  user_answer TEXT NOT NULL DEFAULT '',
  correct INTEGER NOT NULL,
  confidence INTEGER,
  hints_used INTEGER NOT NULL DEFAULT 0,
  answered_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================= Examens blancs =============================
CREATE TABLE IF NOT EXISTS mock_exams (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_code TEXT NOT NULL REFERENCES courses(code),
  kind TEXT NOT NULL DEFAULT 'intra', -- intra | final | thematic | cumulative
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  duration_minutes INTEGER NOT NULL DEFAULT 120,
  question_ids TEXT NOT NULL DEFAULT '[]', -- JSON [questionId]
  config TEXT NOT NULL DEFAULT '{}',
  created_by TEXT NOT NULL DEFAULT 'seed',
  official INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS exam_attempts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  exam_id INTEGER NOT NULL REFERENCES mock_exams(id) ON DELETE CASCADE,
  mode TEXT NOT NULL DEFAULT 'practice', -- timed | practice
  started_at TEXT NOT NULL DEFAULT (datetime('now')),
  finished_at TEXT,
  answers TEXT NOT NULL DEFAULT '{}',    -- JSON {questionId: {answer, correct, confidence}}
  score REAL NOT NULL DEFAULT 0,
  total REAL NOT NULL DEFAULT 0,
  analysis TEXT NOT NULL DEFAULT '{}'    -- JSON par compétence/concept
);

-- ============================= Apprentissage : plans, erreurs, résumés, activité =============================
CREATE TABLE IF NOT EXISTS study_plans (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_code TEXT NOT NULL,
  exam_date TEXT NOT NULL,
  config TEXT NOT NULL DEFAULT '{}',
  plan TEXT NOT NULL DEFAULT '[]',       -- JSON [{date, items:[{kind, conceptId, label, done}]}]
  active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS error_notebook (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  course_code TEXT NOT NULL,
  concept_id INTEGER,
  question TEXT NOT NULL,
  given_answer TEXT NOT NULL DEFAULT '',
  correction TEXT NOT NULL DEFAULT '',
  explanation TEXT NOT NULL DEFAULT '',
  source TEXT NOT NULL DEFAULT 'quiz',   -- quiz | exam | chat
  status TEXT NOT NULL DEFAULT 'a-revoir', -- comprise | a-revoir | maitrisee
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_errors_user ON error_notebook(user_id, status);

CREATE TABLE IF NOT EXISTS summaries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  course_code TEXT NOT NULL,
  scope TEXT NOT NULL DEFAULT 'week',    -- week | concept | document | exam-prep
  ref TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  citations TEXT NOT NULL DEFAULT '[]',
  owner_user_id INTEGER,                 -- null = partagé
  created_by TEXT NOT NULL DEFAULT 'ai',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS saved_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  kind TEXT NOT NULL,                    -- answer | summary | quiz | plan | note
  course_code TEXT,
  title TEXT NOT NULL,
  content TEXT NOT NULL DEFAULT '',
  meta TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS activity_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  kind TEXT NOT NULL,                    -- chat | flashcards | quiz | exam | plan | summary
  course_code TEXT,
  duration_s INTEGER NOT NULL DEFAULT 0,
  meta TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_activity_user_time ON activity_log(user_id, created_at);

CREATE TABLE IF NOT EXISTS weekly_goals (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  week_start TEXT NOT NULL,              -- lundi ISO
  target TEXT NOT NULL DEFAULT '{}',     -- JSON {cards: 40, quiz: 3, minutes: 120}
  UNIQUE (user_id, week_start)
);
`;