-- ============================================================
-- EnglishSpeak
-- PHỤC HỒI TẦNG LEVEL
-- Kiến trúc chuẩn:
-- books -> levels -> units -> lessons -> activities
--
-- Ngày tạo: 2026-08-03
--
-- AN TOÀN:
-- 1. Không xóa units.book_id.
-- 2. Không xóa dữ liệu hiện tại.
-- 3. Tạo bảng sao lưu trước khi thay đổi.
-- 4. Có thể chạy lại; các thao tác được kiểm tra trước.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 0. KIỂM TRA DATABASE
-- ============================================================

SELECT DATABASE() AS current_database;

-- ============================================================
-- 1. SAO LƯU NHANH DỮ LIỆU QUAN TRỌNG
-- ============================================================

CREATE TABLE IF NOT EXISTS `backup_20260803_levels`
LIKE `levels`;

INSERT IGNORE INTO `backup_20260803_levels`
SELECT * FROM `levels`;

CREATE TABLE IF NOT EXISTS `backup_20260803_units`
LIKE `units`;

INSERT IGNORE INTO `backup_20260803_units`
SELECT * FROM `units`;

-- ============================================================
-- 2. BỔ SUNG BOOK_ID VÀ SLUG CHO LEVELS
-- ============================================================

SET @has_levels_book_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'levels'
      AND COLUMN_NAME = 'book_id'
);

SET @sql := IF(
    @has_levels_book_id = 0,
    'ALTER TABLE `levels`
       ADD COLUMN `book_id` BIGINT UNSIGNED NULL AFTER `id`',
    'SELECT ''levels.book_id đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET @has_levels_slug := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'levels'
      AND COLUMN_NAME = 'slug'
);

SET @sql := IF(
    @has_levels_slug = 0,
    'ALTER TABLE `levels`
       ADD COLUMN `slug` VARCHAR(100) NULL AFTER `name`',
    'SELECT ''levels.slug đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 3. GÁN CÁC LEVEL HIỆN TẠI VÀO ĐÚNG BOOK
-- ============================================================

-- Global Success: Lớp 1 -> Lớp 5
UPDATE `levels`
SET
    `book_id` = (
        SELECT `id`
        FROM `books`
        WHERE `slug` = 'global-success'
        LIMIT 1
    ),
    `slug` = CONCAT('grade-', `level_number`)
WHERE `name` IN ('Lớp 1', 'Lớp 2', 'Lớp 3', 'Lớp 4', 'Lớp 5');

-- Everybody Up: Starter
UPDATE `levels`
SET
    `book_id` = (
        SELECT `id`
        FROM `books`
        WHERE `slug` = 'everybody-up'
        LIMIT 1
    ),
    `slug` = 'starter',
    `level_number` = 0,
    `sort_order` = 0
WHERE `name` = 'Starter';

-- ============================================================
-- 4. BỔ SUNG CÁC LEVEL CÒN THIẾU CỦA EVERYBODY UP
-- ============================================================

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    1,
    'Up 1',
    'up-1',
    1,
    'active',
    NOW(),
    NOW()
FROM `books` b
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`slug` = 'up-1'
  );

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    2,
    'Up 2',
    'up-2',
    2,
    'active',
    NOW(),
    NOW()
FROM `books` b
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`slug` = 'up-2'
  );

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    3,
    'Up 3',
    'up-3',
    3,
    'active',
    NOW(),
    NOW()
FROM `books` b
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`slug` = 'up-3'
  );

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    4,
    'Up 4',
    'up-4',
    4,
    'active',
    NOW(),
    NOW()
FROM `books` b
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`slug` = 'up-4'
  );

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    5,
    'Up 5',
    'up-5',
    5,
    'active',
    NOW(),
    NOW()
FROM `books` b
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`slug` = 'up-5'
  );

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    6,
    'Up 6',
    'up-6',
    6,
    'active',
    NOW(),
    NOW()
FROM `books` b
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`slug` = 'up-6'
  );

-- ============================================================
-- 5. THÊM LEVEL_ID VÀO UNITS
-- ============================================================

SET @has_units_level_id := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'units'
      AND COLUMN_NAME = 'level_id'
);

SET @sql := IF(
    @has_units_level_id = 0,
    'ALTER TABLE `units`
       ADD COLUMN `level_id` BIGINT UNSIGNED NULL AFTER `book_id`',
    'SELECT ''units.level_id đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 6. GÁN CÁC UNIT HIỆN TẠI VÀO LEVEL ĐÚNG
-- ============================================================

-- Dữ liệu Global Success hiện tại là nội dung Lớp 1.
UPDATE `units` u
INNER JOIN `books` b
    ON b.`id` = u.`book_id`
INNER JOIN `levels` l
    ON l.`book_id` = b.`id`
   AND l.`slug` = 'grade-1'
SET u.`level_id` = l.`id`
WHERE b.`slug` = 'global-success'
  AND u.`level_id` IS NULL;

-- Dữ liệu Everybody Up hiện tại là nội dung Starter.
UPDATE `units` u
INNER JOIN `books` b
    ON b.`id` = u.`book_id`
INNER JOIN `levels` l
    ON l.`book_id` = b.`id`
   AND l.`slug` = 'starter'
SET u.`level_id` = l.`id`
WHERE b.`slug` = 'everybody-up'
  AND u.`level_id` IS NULL;

-- ============================================================
-- 7. TẠO INDEX
-- ============================================================

SET @has_levels_book_index := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'levels'
      AND INDEX_NAME = 'idx_levels_book'
);

SET @sql := IF(
    @has_levels_book_index = 0,
    'ALTER TABLE `levels`
       ADD INDEX `idx_levels_book` (`book_id`)',
    'SELECT ''idx_levels_book đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET @has_levels_book_slug_unique := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'levels'
      AND INDEX_NAME = 'uq_levels_book_slug'
);

SET @sql := IF(
    @has_levels_book_slug_unique = 0,
    'ALTER TABLE `levels`
       ADD UNIQUE INDEX `uq_levels_book_slug` (`book_id`, `slug`)',
    'SELECT ''uq_levels_book_slug đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET @has_units_level_index := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'units'
      AND INDEX_NAME = 'idx_units_level'
);

SET @sql := IF(
    @has_units_level_index = 0,
    'ALTER TABLE `units`
       ADD INDEX `idx_units_level` (`level_id`)',
    'SELECT ''idx_units_level đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================
-- 8. TẠO FOREIGN KEY
-- ============================================================

SET @has_fk_levels_book := (
    SELECT COUNT(*)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'levels'
      AND CONSTRAINT_NAME = 'fk_levels_book'
);

SET @sql := IF(
    @has_fk_levels_book = 0,
    'ALTER TABLE `levels`
       ADD CONSTRAINT `fk_levels_book`
       FOREIGN KEY (`book_id`)
       REFERENCES `books` (`id`)
       ON DELETE CASCADE
       ON UPDATE CASCADE',
    'SELECT ''fk_levels_book đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


SET @has_fk_units_level := (
    SELECT COUNT(*)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'units'
      AND CONSTRAINT_NAME = 'fk_units_level'
);

SET @sql := IF(
    @has_fk_units_level = 0,
    'ALTER TABLE `units`
       ADD CONSTRAINT `fk_units_level`
       FOREIGN KEY (`level_id`)
       REFERENCES `levels` (`id`)
       ON DELETE RESTRICT
       ON UPDATE CASCADE',
    'SELECT ''fk_units_level đã tồn tại'' AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- 9. KIỂM TRA KẾT QUẢ
-- ============================================================

SELECT
    b.`id` AS book_id,
    b.`name` AS book_name,
    l.`id` AS level_id,
    l.`name` AS level_name,
    l.`slug` AS level_slug,
    l.`sort_order`,
    l.`status`
FROM `books` b
LEFT JOIN `levels` l
    ON l.`book_id` = b.`id`
ORDER BY b.`sort_order`, l.`sort_order`, l.`id`;


SELECT
    u.`id` AS unit_id,
    b.`name` AS book_name,
    l.`name` AS level_name,
    u.`unit_number`,
    u.`title`
FROM `units` u
INNER JOIN `books` b
    ON b.`id` = u.`book_id`
LEFT JOIN `levels` l
    ON l.`id` = u.`level_id`
ORDER BY b.`sort_order`, l.`sort_order`, u.`sort_order`, u.`id`;


SELECT
    COUNT(*) AS units_without_level
FROM `units`
WHERE `level_id` IS NULL;

-- ============================================================
-- KẾT QUẢ MONG ĐỢI
-- ============================================================
-- Global Success:
--   Lớp 1
--   Lớp 2
--   Lớp 3
--   Lớp 4
--   Lớp 5
--
-- Everybody Up:
--   Starter
--   Up 1
--   Up 2
--   Up 3
--   Up 4
--   Up 5
--   Up 6
--
-- units_without_level phải bằng 0.
--
-- CHƯA XÓA units.book_id.
-- Chỉ xóa cột cũ sau khi toàn bộ code PHP đã chuyển sang level_id
-- và đã kiểm tra chạy ổn.
-- ============================================================
