-- ============================================================
-- Learnillion — Fresh install schema
-- For existing installs, run migration-002-categories.sql instead.
-- ============================================================

CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL UNIQUE,
  `slug` VARCHAR(120) NOT NULL UNIQUE,
  `sort_order` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_slug (`slug`),
  INDEX idx_sort (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `categories` (`name`, `slug`, `sort_order`) VALUES
  ('Performance Marketing', 'performance-marketing', 10),
  ('Content & SEO',         'content-seo',           20),
  ('Sales & Outreach',      'sales-outreach',        30),
  ('Product Management',    'product-management',    40),
  ('UX & Design',           'ux-design',             50),
  ('Startup Finance',       'startup-finance',       60),
  ('Leadership',            'leadership',            70),
  ('Productivity',          'productivity',          80),
  ('Data & Analytics',      'data-analytics',        90),
  ('Engineering',           'engineering',          100),
  ('Career Growth',         'career-growth',        110),
  ('Personal Finance',      'personal-finance',     120),
  ('Health & Wellbeing',    'health-wellbeing',     130),
  ('Communication',         'communication',        140),
  ('Negotiation',           'negotiation',          150);

CREATE TABLE IF NOT EXISTS `lessons` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `category_id` INT UNSIGNED NULL,
  `duration` VARCHAR(50) DEFAULT NULL,
  `eyebrow` VARCHAR(255) DEFAULT NULL,

  `lesson_dir`         VARCHAR(255) NOT NULL,
  `original_html_path` VARCHAR(255) NOT NULL,
  `audio_dir`          VARCHAR(255) NOT NULL,

  `sentences_json` LONGTEXT NOT NULL,
  `stage_html`     LONGTEXT NOT NULL,
  `text_html`      LONGTEXT NOT NULL,
  `inline_css`     LONGTEXT DEFAULT NULL,
  `inline_js`      LONGTEXT DEFAULT NULL,
  `head_links`     LONGTEXT DEFAULT NULL,

  `voice`  VARCHAR(50) DEFAULT 'Joanna',
  `engine` VARCHAR(20) DEFAULT 'neural',

  `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,

  CONSTRAINT `fk_lessons_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,

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