-- ============================================================
-- Learnillion — Lesson database schema
-- MySQL 5.7+ / MariaDB 10.3+
-- ============================================================

CREATE TABLE IF NOT EXISTS `lessons` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `duration` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. "12 min"',
  `eyebrow` VARCHAR(255) DEFAULT NULL COMMENT 'Category line above title',

  -- Filesystem locations (relative to site root)
  `lesson_dir`        VARCHAR(255) NOT NULL COMMENT 'lessons/<slug>',
  `original_html_path` VARCHAR(255) NOT NULL COMMENT 'lessons/<slug>/original.html',
  `audio_dir`         VARCHAR(255) NOT NULL COMMENT 'lessons/<slug>/audio',

  -- Extracted content stored as JSON so lesson.php doesn't need to reparse HTML
  -- sentences: array of { id, text, scene, audio_url, marks: [{time_ms,start,end,value}] }
  `sentences_json`  LONGTEXT NOT NULL,

  -- Raw HTML fragments from the uploaded file
  `stage_html`      LONGTEXT NOT NULL COMMENT 'Inner HTML of .stage-inner (all scenes)',
  `text_html`       LONGTEXT NOT NULL COMMENT 'Inner HTML of .text-content',
  `inline_css`      LONGTEXT DEFAULT NULL COMMENT 'Styles extracted from <style> tags',
  `inline_js`       LONGTEXT DEFAULT NULL COMMENT 'Scene animation JS extracted from <script>',
  `head_links`      LONGTEXT DEFAULT NULL COMMENT 'Google Fonts link tags, etc.',

  -- TTS settings used
  `voice`           VARCHAR(50) DEFAULT 'Joanna',
  `engine`          VARCHAR(20) DEFAULT 'neural',

  -- Status
  `status`          ENUM('draft','generating','ready','failed') DEFAULT 'draft',
  `error_message`   TEXT DEFAULT NULL,

  `created_at`      DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_slug (`slug`),
  INDEX idx_status (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;