-- Step 30: Cameraman overtime hourly rates.
-- Run after step29_equipment_assets.sql.

SET @step30_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD COLUMN `overtime_hourly_rate` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `is_supervisor`',
    'SELECT ''cameramen.overtime_hourly_rate already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND COLUMN_NAME = 'overtime_hourly_rate'
);
PREPARE step30_stmt FROM @step30_sql;
EXECUTE step30_stmt;
DEALLOCATE PREPARE step30_stmt;

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