-- Step 31: Cameraman duty timings for overtime calculation.
-- Run after step30_cameraman_overtime_rates.sql.

SET @step31_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD COLUMN `duty_start_time` TIME DEFAULT NULL AFTER `overtime_hourly_rate`',
    'SELECT ''cameramen.duty_start_time already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND COLUMN_NAME = 'duty_start_time'
);
PREPARE step31_stmt FROM @step31_sql;
EXECUTE step31_stmt;
DEALLOCATE PREPARE step31_stmt;

SET @step31_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD COLUMN `duty_end_time` TIME DEFAULT NULL AFTER `duty_start_time`',
    'SELECT ''cameramen.duty_end_time already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND COLUMN_NAME = 'duty_end_time'
);
PREPARE step31_stmt FROM @step31_sql;
EXECUTE step31_stmt;
DEALLOCATE PREPARE step31_stmt;

SET @step31_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD KEY `idx_cameramen_duty_time` (`duty_start_time`, `duty_end_time`)',
    'SELECT ''idx_cameramen_duty_time already exists'' AS message')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND INDEX_NAME = 'idx_cameramen_duty_time'
);
PREPARE step31_stmt FROM @step31_sql;
EXECUTE step31_stmt;
DEALLOCATE PREPARE step31_stmt;