-- ============================================================
-- EnglishSpeak
-- TIẾP TỤC PHỤC HỒI LEVEL SAU LỖI DUPLICATE level_number
-- Chạy file này trên database englishspeak hiện tại.
-- KHÔNG chạy lại file restore cũ.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. GỠ UNIQUE CŨ TRÊN levels.level_number
-- ============================================================

SET @old_unique_index := (
    SELECT INDEX_NAME
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'levels'
      AND COLUMN_NAME = 'level_number'
      AND NON_UNIQUE = 0
      AND INDEX_NAME <> 'PRIMARY'
    GROUP BY INDEX_NAME
    HAVING COUNT(*) = 1
    LIMIT 1
);

SET @sql := IF(
    @old_unique_index IS NOT NULL,
    CONCAT(
        'ALTER TABLE `levels` DROP INDEX `',
        REPLACE(@old_unique_index, '`', '``'),
        '`'
    ),
    'SELECT ''Không còn UNIQUE cũ trên level_number'' AS message'
);

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

-- ============================================================
-- 2. TẠO UNIQUE ĐÚNG THEO TỪNG BOOK
-- ============================================================

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

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

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


SET @has_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_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;

-- ============================================================
-- 3. THÊM UP 1 -> UP 6
-- ============================================================

INSERT INTO `levels`
(
    `book_id`,
    `level_number`,
    `name`,
    `slug`,
    `sort_order`,
    `status`,
    `created_at`,
    `updated_at`
)
SELECT
    b.`id`,
    n.`level_number`,
    CONCAT('Up ', n.`level_number`),
    CONCAT('up-', n.`level_number`),
    n.`level_number`,
    'active',
    NOW(),
    NOW()
FROM `books` b
CROSS JOIN (
    SELECT 1 AS level_number
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
) n
WHERE b.`slug` = 'everybody-up'
  AND NOT EXISTS (
      SELECT 1
      FROM `levels` l
      WHERE l.`book_id` = b.`id`
        AND l.`level_number` = n.`level_number`
  );

-- ============================================================
-- 4. THÊM units.level_id NẾU CHƯA CÓ
-- ============================================================

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;

-- ============================================================
-- 5. GÁN UNIT HIỆN TẠI VÀO LEVEL
-- ============================================================

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;


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;

-- ============================================================
-- 6. INDEX
-- ============================================================

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;

-- ============================================================
-- 7. 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;

-- ============================================================
-- 8. KIỂM TRA
-- ============================================================

SELECT
    b.`name` AS book_name,
    l.`id` AS level_id,
    l.`level_number`,
    l.`name` AS level_name,
    l.`slug`,
    l.`sort_order`,
    l.`status`
FROM `books` b
LEFT JOIN `levels` l
    ON l.`book_id` = b.`id`
ORDER BY b.`id`, 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.`id`, 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 = 0
-- ============================================================
