-- Resume migration

-- 1. Fix PK on employee_login
-- Drop the unique index 'username' that was on the old username/nic column
ALTER TABLE employee_login DROP INDEX username;
-- Add the actual Primary Key
ALTER TABLE employee_login ADD PRIMARY KEY (nic);

-- 2. Add Constraints to child tables
ALTER TABLE applications ADD CONSTRAINT fk_app_emp_nic FOREIGN KEY (employee_nic) REFERENCES employee_login(nic) ON DELETE CASCADE;
ALTER TABLE employee_details ADD CONSTRAINT fk_emp_det_nic FOREIGN KEY (employee_nic) REFERENCES employee_login(nic) ON DELETE CASCADE;
ALTER TABLE reviews ADD CONSTRAINT fk_review_emp_nic FOREIGN KEY (employee_nic) REFERENCES employee_login(nic) ON DELETE CASCADE;

-- 3. Drop old columns
ALTER TABLE applications DROP COLUMN employee_id;
ALTER TABLE employee_details DROP COLUMN user_id;
ALTER TABLE reviews DROP COLUMN employee_id;
