-- Step 28: Senior cameraman supervisor access.
-- Run after step27_daily_ops_overtime.sql.

SET @step28_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `cameramen` ADD COLUMN `is_supervisor` TINYINT NOT NULL DEFAULT 0 AFTER `status`',
    'SELECT ''cameramen.is_supervisor already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'cameramen'
    AND COLUMN_NAME = 'is_supervisor'
);
PREPARE step28_stmt FROM @step28_sql;
EXECUTE step28_stmt;
DEALLOCATE PREPARE step28_stmt;

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