-- ============================================================
-- 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 `skill_paths` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(120) NOT NULL UNIQUE,
  `slug` VARCHAR(140) NOT NULL UNIQUE,
  `icon` VARCHAR(20) DEFAULT NULL,
  `short_description` VARCHAR(280) DEFAULT NULL,
  `full_description` TEXT DEFAULT NULL,
  `category_id` INT UNSIGNED NULL,
  `sort_order` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_skill_paths_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
  INDEX idx_slug (`slug`),
  INDEX idx_sort (`sort_order`),
  INDEX idx_sp_category (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `skill_paths` (`name`, `slug`, `sort_order`) VALUES
  ('Growth Marketing Fundamentals', 'growth-marketing-fundamentals', 10),
  ('Google Ads Mastery',            'google-ads-mastery',            20),
  ('SEO from Scratch',              'seo-from-scratch',              30),
  ('Cold Outreach that Converts',   'cold-outreach',                 40),
  ('Product Discovery',             'product-discovery',             50),
  ('Shipping Faster',               'shipping-faster',               60),
  ('Design Systems 101',            'design-systems-101',            70),
  ('Fundraising Playbook',          'fundraising-playbook',          80),
  ('First 90 Days as a Manager',    'first-90-days-manager',         90),
  ('Deep Work',                     'deep-work',                    100),
  ('SQL for Non-Engineers',         'sql-for-non-engineers',        110),
  ('System Design Basics',          'system-design-basics',         120),
  ('Negotiation Essentials',        'negotiation-essentials',       130),
  ('Money Habits',                  'money-habits',                 140);

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,
  `skill_path_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,
  CONSTRAINT `fk_lessons_skill_path` FOREIGN KEY (`skill_path_id`) REFERENCES `skill_paths`(`id`) ON DELETE SET NULL,

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

CREATE TABLE IF NOT EXISTS `lesson_resources` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `lesson_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `file_url` VARCHAR(500) NOT NULL,
  `file_type` VARCHAR(20) DEFAULT NULL,
  `file_size` INT UNSIGNED DEFAULT NULL,
  `sort_order` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_lesson_resources_lesson` FOREIGN KEY (`lesson_id`) REFERENCES `lessons`(`id`) ON DELETE CASCADE,
  INDEX idx_lesson (`lesson_id`),
  INDEX idx_sort (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;