-- Migration script to add first_name and last_name columns to employees table

-- Add first_name column
ALTER TABLE employees ADD COLUMN first_name VARCHAR(100) AFTER department_id;

-- Add last_name column
ALTER TABLE employees ADD COLUMN last_name VARCHAR(100) AFTER first_name;

-- Populate first_name and last_name from name column
-- Split name on first space, everything before = first_name, everything after = last_name
UPDATE employees
SET 
  first_name = TRIM(SUBSTRING_INDEX(name, ' ', 1)),
  last_name = TRIM(SUBSTRING(name, LENGTH(SUBSTRING_INDEX(name, ' ', 1)) + 2));

-- Handle cases where there's no space (single name)
UPDATE employees
SET last_name = first_name
WHERE last_name = '' OR last_name IS NULL;

-- Make columns NOT NULL after population
ALTER TABLE employees MODIFY COLUMN first_name VARCHAR(100) NOT NULL;
ALTER TABLE employees MODIFY COLUMN last_name VARCHAR(100) NOT NULL;