-- ============================================================
-- Learnillion — Migration: add skill paths
-- ============================================================

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

-- Seed skill paths (curated learning tracks across niches)
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);

-- Add skill_path_id column to lessons
ALTER TABLE `lessons`
  ADD COLUMN `skill_path_id` INT UNSIGNED NULL AFTER `category_id`,
  ADD CONSTRAINT `fk_lessons_skill_path`
    FOREIGN KEY (`skill_path_id`) REFERENCES `skill_paths`(`id`) ON DELETE SET NULL,
  ADD INDEX `idx_skill_path` (`skill_path_id`);