-- EnglishSpeak
-- Final fix after partial book_levels removal
-- Expected current state:
-- - units.book_id exists and contains migrated data
-- - fk_units_book exists
-- - uq_units_book_unit_number exists
-- - units.book_level_id still exists
-- - book_levels still exists

-- 1. Temporarily remove the new foreign key
ALTER TABLE units
DROP FOREIGN KEY fk_units_book;

-- 2. Make book_id required
ALTER TABLE units
MODIFY COLUMN book_id BIGINT UNSIGNED NOT NULL;

-- 3. Restore the foreign key
ALTER TABLE units
ADD CONSTRAINT fk_units_book
FOREIGN KEY (book_id)
REFERENCES books(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

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

-- 5. Remove the obsolete table
DROP TABLE book_levels;
