-- Per-job-type document header / section title colour (hex, e.g. #ffff00).
ALTER TABLE `ptw_job_type`
  ADD COLUMN `job_colour_code` VARCHAR(7) NULL DEFAULT NULL
  COMMENT 'Hex colour for PTW document header and section titles';

UPDATE `ptw_job_type`
SET `job_colour_code` = '#ffff00'
WHERE UPPER(TRIM(`job_type_name`)) = 'GENERAL'
  AND (`job_colour_code` IS NULL OR TRIM(`job_colour_code`) = '');

UPDATE `ptw_job_type`
SET `job_colour_code` = '#ff0e0e'
WHERE UPPER(REPLACE(TRIM(`job_type_name`), ' ', '')) IN ('HOTWORK', 'HOT WORK')
  AND (`job_colour_code` IS NULL OR TRIM(`job_colour_code`) = '');
