-- EnglishSpeak
-- Migration: Remove book_levels completely
-- Backup database before running.

START TRANSACTION;

-- 1. Add new column
ALTER TABLE units
ADD COLUMN book_id BIGINT UNSIGNED NULL AFTER id;

-- 2. Copy data
UPDATE units u
JOIN book_levels bl
  ON u.book_level_id = bl.id
SET u.book_id = bl.book_id;

-- 3. Add new index
ALTER TABLE units
ADD INDEX idx_units_book (book_id);

-- 4. Add new FK
ALTER TABLE units
ADD CONSTRAINT fk_units_book
FOREIGN KEY (book_id)
REFERENCES books(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

-- 5. Remove old FK (confirmed name)
ALTER TABLE units
DROP FOREIGN KEY fk_units_book_level;

-- 6. Remove old index
ALTER TABLE units
DROP INDEX idx_units_book_level;

-- 7. Remove old column
ALTER TABLE units
DROP COLUMN book_level_id;

-- 8. Drop obsolete table
DROP TABLE book_levels;

COMMIT;
