-- Step 35: Per-recording paid overtime minute adjustments.
-- Run after step34_overtime_holidays.sql.
-- Superadmin can add or subtract minutes for exact payable OT calculations.

SET @step35_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD COLUMN `overtime_adjustment_minutes` INT NOT NULL DEFAULT 0 AFTER `paid_overtime_remarks`',
    'SELECT ''schedule.overtime_adjustment_minutes already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND COLUMN_NAME = 'overtime_adjustment_minutes'
);
PREPARE step35_stmt FROM @step35_sql;
EXECUTE step35_stmt;
DEALLOCATE PREPARE step35_stmt;

SET @step35_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD COLUMN `overtime_adjustment_reason` VARCHAR(255) DEFAULT NULL AFTER `overtime_adjustment_minutes`',
    'SELECT ''schedule.overtime_adjustment_reason already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND COLUMN_NAME = 'overtime_adjustment_reason'
);
PREPARE step35_stmt FROM @step35_sql;
EXECUTE step35_stmt;
DEALLOCATE PREPARE step35_stmt;

SET @step35_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD COLUMN `overtime_adjustment_minutes` INT NOT NULL DEFAULT 0 AFTER `paid_overtime_remarks`',
    'SELECT ''daily_recording_instances.overtime_adjustment_minutes already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND COLUMN_NAME = 'overtime_adjustment_minutes'
);
PREPARE step35_stmt FROM @step35_sql;
EXECUTE step35_stmt;
DEALLOCATE PREPARE step35_stmt;

SET @step35_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD COLUMN `overtime_adjustment_reason` VARCHAR(255) DEFAULT NULL AFTER `overtime_adjustment_minutes`',
    'SELECT ''daily_recording_instances.overtime_adjustment_reason already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND COLUMN_NAME = 'overtime_adjustment_reason'
);
PREPARE step35_stmt FROM @step35_sql;
EXECUTE step35_stmt;
DEALLOCATE PREPARE step35_stmt;