-- 1. Create employee_shifts junction table
CREATE TABLE IF NOT EXISTS employee_shifts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    shift_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE CASCADE,
    UNIQUE KEY `unique_emp_shift` (employee_id, shift_id)
);

-- 2. Migrate existing data from employees table if shift_id is present
INSERT IGNORE INTO employee_shifts (employee_id, shift_id)
SELECT id, shift_id FROM employees WHERE shift_id IS NOT NULL;

-- 3. We keep shift_id in employees table for backward compatibility but it will be redundant
-- or we can drop it later once everything is verified.
