-- EnglishSpeak
-- Fix migration after partial removal of book_levels
-- Current state expected:
-- - units.book_id already exists
-- - data already copied from book_levels
-- - fk_units_book already exists
-- - fk_units_book_level already removed
-- - idx_units_book_level already removed
-- - uq_unit_number still exists on (book_level_id, unit_number)

START TRANSACTION;

-- 1. Remove old unique index that still depends on book_level_id
ALTER TABLE units
DROP INDEX uq_unit_number;

-- 2. Create the new unique rule for Book -> Unit
ALTER TABLE units
ADD CONSTRAINT uq_units_book_unit_number
UNIQUE (book_id, unit_number);

-- 3. Make book_id required after data migration
ALTER TABLE units
MODIFY COLUMN book_id BIGINT UNSIGNED NOT NULL;

-- 4. Remove obsolete column
ALTER TABLE units
DROP COLUMN book_level_id;

-- 5. Remove obsolete table
DROP TABLE book_levels;

COMMIT;
