-- Update attendance table to support multiple shifts per day
ALTER TABLE attendance ADD COLUMN IF NOT EXISTS shift_id INT AFTER employee_id;
ALTER TABLE attendance ADD CONSTRAINT fk_attendance_shift FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE SET NULL;

-- Drop old unique key if it exists
ALTER TABLE attendance DROP INDEX one_per_day;

-- Create new unique key including shift_id
ALTER TABLE attendance ADD UNIQUE KEY `one_per_shift_day` (employee_id, date, shift_id);
