-- ============================================================
-- Learnillion — Migration: add categories
-- Run this on existing installs.
-- ============================================================

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;

-- Seed categories across different niches
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);

-- Add category_id to lessons
ALTER TABLE `lessons`
  ADD COLUMN `category_id` INT UNSIGNED NULL AFTER `name`,
  ADD CONSTRAINT `fk_lessons_category`
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
  ADD INDEX `idx_category` (`category_id`);