-- EnglishSpeak - Content Library Architecture Fix
-- Update Everybody Up book_level naming and add Starter level

-- 1. Add Starter level (level_number = 0) for Everybody Up Starter
INSERT IGNORE INTO levels (level_number, name, sort_order, status) VALUES
(0, 'Starter', 0, 'active');

-- 2. Fix Everybody Up book_level titles
--    "Everybody Up – Lớp 1" → "Up 1"
--    "Everybody Up – Lớp 2" → "Up 2"
--    etc.
UPDATE book_levels SET title = 'Up 1' WHERE id = 6;
UPDATE book_levels SET title = 'Up 2' WHERE id = 7;
UPDATE book_levels SET title = 'Up 3' WHERE id = 8;
UPDATE book_levels SET title = 'Up 4' WHERE id = 9;
UPDATE book_levels SET title = 'Up 5' WHERE id = 10;

-- 3. Fix Global Success book_level titles
--    "Global Success – Lớp 1" → "Global Success Lớp 1"
--    (task says "Global Success Lớp 1" naming)
UPDATE book_levels SET title = 'Global Success Lớp 1' WHERE id = 1;
UPDATE book_levels SET title = 'Global Success Lớp 2' WHERE id = 2;
UPDATE book_levels SET title = 'Global Success Lớp 3' WHERE id = 3;
UPDATE book_levels SET title = 'Global Success Lớp 4' WHERE id = 4;
UPDATE book_levels SET title = 'Global Success Lớp 5' WHERE id = 5;

-- 4. Add Everybody Up Starter book_level
INSERT INTO book_levels (book_id, level_id, title, sort_order, status)
SELECT b.id, l.id, 'Everybody Up Starter', 0, 'active'
FROM books b, levels l
WHERE b.slug = 'everybody-up' AND l.level_number = 0
ON DUPLICATE KEY UPDATE title = 'Everybody Up Starter';

-- 5. Seed sample units for each book_level
-- Global Success Lớp 1
INSERT IGNORE INTO units (book_level_id, unit_number, title, sort_order) VALUES
(1, 'Unit 1', 'In the school playground', 1),
(1, 'Unit 2', 'In the dining room', 2),
(1, 'Unit 3', 'At the street market', 3),
(1, 'Unit 4', 'In the bedroom', 4),
(1, 'Unit 5', 'At the fish and chip shop', 5),
(1, 'Unit 6', 'In the classroom', 6),
(1, 'Unit 7', 'In the garden', 7),
(1, 'Unit 8', 'In the park', 8);

-- Everybody Up Starter
INSERT IGNORE INTO units (book_level_id, unit_number, title, sort_order)
SELECT bl.id, 'Unit 1', 'School Supplies', 1
FROM book_levels bl WHERE bl.title = 'Everybody Up Starter'
ON DUPLICATE KEY UPDATE title = 'School Supplies';

INSERT IGNORE INTO units (book_level_id, unit_number, title, sort_order)
SELECT bl.id, 'Unit 2', 'Art Class', 2
FROM book_levels bl WHERE bl.title = 'Everybody Up Starter'
ON DUPLICATE KEY UPDATE title = 'Art Class';

-- Seed sample lessons for the first unit of Global Success Lớp 1
INSERT IGNORE INTO lessons (unit_id, lesson_number, title, lesson_type, sort_order) 
SELECT u.id, 'Lesson 1', 'Listen and repeat.', 'vocabulary', 1
FROM units u WHERE u.title = 'In the school playground' AND u.unit_number = 'Unit 1'
ON DUPLICATE KEY UPDATE title = 'Listen and repeat.';

INSERT IGNORE INTO lessons (unit_id, lesson_number, title, lesson_type, sort_order)
SELECT u.id, 'Lesson 2', 'Point and say.', 'speaking', 2
FROM units u WHERE u.title = 'In the school playground' AND u.unit_number = 'Unit 1'
ON DUPLICATE KEY UPDATE title = 'Point and say.';

INSERT IGNORE INTO lessons (unit_id, lesson_number, title, lesson_type, sort_order)
SELECT u.id, 'Lesson 3', 'Listen and chant.', 'listening', 3
FROM units u WHERE u.title = 'In the school playground' AND u.unit_number = 'Unit 1'
ON DUPLICATE KEY UPDATE title = 'Listen and chant.';

-- Seed sample activities for lesson 1
INSERT IGNORE INTO activities (lesson_id, title, activity_type, sort_order, xp_reward)
SELECT l.id, 'Word List', 'vocabulary', 1, 10
FROM lessons l 
JOIN units u ON l.unit_id = u.id
WHERE u.title = 'In the school playground' AND l.lesson_number = 'Lesson 1'
ON DUPLICATE KEY UPDATE title = 'Word List';

INSERT IGNORE INTO activities (lesson_id, title, activity_type, sort_order, xp_reward)
SELECT l.id, 'Flashcards', 'flashcards', 2, 15
FROM lessons l
JOIN units u ON l.unit_id = u.id
WHERE u.title = 'In the school playground' AND l.lesson_number = 'Lesson 1'
ON DUPLICATE KEY UPDATE title = 'Flashcards';

INSERT IGNORE INTO activities (lesson_id, title, activity_type, sort_order, xp_reward)
SELECT l.id, 'Listen & Repeat', 'pronunciation', 3, 20
FROM lessons l
JOIN units u ON l.unit_id = u.id
WHERE u.title = 'In the school playground' AND l.lesson_number = 'Lesson 1'
ON DUPLICATE KEY UPDATE title = 'Listen & Repeat';