-- 1. Rename username to nic in employee_login
ALTER TABLE employee_login CHANGE username nic VARCHAR(20) NOT NULL;

-- 2. Add new FK columns to child tables
ALTER TABLE applications ADD COLUMN employee_nic VARCHAR(20);
ALTER TABLE employee_details ADD COLUMN employee_nic VARCHAR(20);
ALTER TABLE reviews ADD COLUMN employee_nic VARCHAR(20);

-- 3. Migrate Data
UPDATE applications a JOIN employee_login e ON a.employee_id = e.id SET a.employee_nic = e.nic;
UPDATE employee_details ed JOIN employee_login e ON ed.user_id = e.id SET ed.employee_nic = e.nic;
UPDATE reviews r JOIN employee_login e ON r.employee_id = e.id SET r.employee_nic = e.nic;

-- 4. Drop old Foreign Keys
ALTER TABLE applications DROP FOREIGN KEY fk_app_employee;
ALTER TABLE employee_details DROP FOREIGN KEY fk_emp_det_user;
ALTER TABLE reviews DROP FOREIGN KEY fk_review_employee;

-- 5. Modify PK of employee_login
-- First drop the AUTO_INCREMENT property if exists (it's the PK)
ALTER TABLE employee_login MODIFY id INT NOT NULL;
ALTER TABLE employee_login DROP PRIMARY KEY;
ALTER TABLE employee_login DROP COLUMN id;
ALTER TABLE employee_login ADD PRIMARY KEY (nic);

-- 6. Re-establish ID/NIC columns in child tables
-- We want to replace employee_id with employee_nic basically.
-- I'll drop the old columns and rename the new ones to allow code to perhaps migrate easier?
-- Actually, the PHP code uses 'employee_id' or 'user_id'.
-- If I keep the column name 'employee_id' but make it VARCHAR, it might be confusing but less code change?
-- No, 'id' implies int. 'nic' implies string. It's better to be explicit.
-- I will DROP the old int columns and RENAME the new 'employee_nic' columns to 'employee_nic' (keeping it explicit) OR rename to 'user_id' (if I want to trick the code, but type mismatch will happen).
-- Let's stick to explicitly new column names 'employee_nic' for relations.
-- However, for `employee_details`, reference is `user_id`. I should probably rename `employee_nic` to `nic` or `user_nic`?
-- Let's standardise: `employee_nic` in applications/reviews, and `user_nic` in employee_details?
-- Or just `employee_id` but change type to VARCHAR? That preserves code variable names like `$row['employee_id']`.
-- But `$row['employee_id']` usually expects an int.
-- Let's go with cleaning up. explicit `employee_nic`.

-- Add Constraints
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;

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