-- Adds a separate "description" field to shifts, distinct from the short
-- `name` used as the compact label in schedule grid cells (e.g. "PM-HK").
-- The description is shown instead when the shift picker dropdown is open,
-- so staff can see a fuller explanation of what the code means without the
-- grid cells themselves getting cluttered.
--
-- Safe to run more than once -- it checks whether the column already exists
-- first, since some MySQL/MariaDB versions don't support
-- `ADD COLUMN IF NOT EXISTS`.

SET @col_exists := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'shifts'
      AND COLUMN_NAME = 'description'
);

SET @ddl := IF(
    @col_exists = 0,
    'ALTER TABLE `shifts` ADD COLUMN `description` VARCHAR(255) DEFAULT NULL AFTER `name`',
    'SELECT ("Column already exists, nothing to do")'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;