-- Lets shifts be restricted to specific departments. A shift with NO rows
-- here is treated as unrestricted (visible/assignable in every department's
-- schedule) -- this matters for backward compatibility: existing shifts
-- keep working everywhere until an admin deliberately scopes them down via
-- shift.php, rather than every shift suddenly disappearing from every
-- department's picker the moment this migration runs.
--
-- Many-to-many rather than a single department per shift, since real shift
-- codes mix both patterns -- some are department-specific (AM-HK, PM-FB,
-- AM-FD), others are naturally cross-department (OFF, MOD, W).
--
-- Safe to run more than once.

CREATE TABLE IF NOT EXISTS `shift_departments` (
    `shift_id` INT(11) NOT NULL,
    `department_id` INT(11) NOT NULL,
    PRIMARY KEY (`shift_id`, `department_id`),
    KEY `department_id` (`department_id`),
    CONSTRAINT `shift_departments_shift_fk` FOREIGN KEY (`shift_id`) REFERENCES `shifts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `shift_departments_department_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;