-- Step 27: Daily operations visibility, missed-recording reminders, and paid overtime markers.
-- Run after step26_communications_reminders.sql.

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD COLUMN `paid_overtime` TINYINT NOT NULL DEFAULT 0',
    'SELECT ''schedule.paid_overtime already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND COLUMN_NAME = 'paid_overtime'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD COLUMN `paid_overtime_marked_by` INT UNSIGNED DEFAULT NULL',
    'SELECT ''schedule.paid_overtime_marked_by already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND COLUMN_NAME = 'paid_overtime_marked_by'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD COLUMN `paid_overtime_marked_at` DATETIME DEFAULT NULL',
    'SELECT ''schedule.paid_overtime_marked_at already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND COLUMN_NAME = 'paid_overtime_marked_at'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

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

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD KEY `idx_schedule_paid_overtime` (`paid_overtime`, `recording_date`)',
    'SELECT ''idx_schedule_paid_overtime already exists'' AS message')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND INDEX_NAME = 'idx_schedule_paid_overtime'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD COLUMN `paid_overtime` TINYINT NOT NULL DEFAULT 0',
    'SELECT ''daily_recording_instances.paid_overtime already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND COLUMN_NAME = 'paid_overtime'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD COLUMN `paid_overtime_marked_by` INT UNSIGNED DEFAULT NULL',
    'SELECT ''daily_recording_instances.paid_overtime_marked_by already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND COLUMN_NAME = 'paid_overtime_marked_by'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD COLUMN `paid_overtime_marked_at` DATETIME DEFAULT NULL',
    'SELECT ''daily_recording_instances.paid_overtime_marked_at already exists'' AS message')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND COLUMN_NAME = 'paid_overtime_marked_at'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

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

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD KEY `idx_daily_instances_paid_overtime` (`paid_overtime`, `recording_date`)',
    'SELECT ''idx_daily_instances_paid_overtime already exists'' AS message')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND INDEX_NAME = 'idx_daily_instances_paid_overtime'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

UPDATE schedule s
LEFT JOIN users u ON u.id = s.paid_overtime_marked_by
SET s.paid_overtime_marked_by = NULL
WHERE s.paid_overtime_marked_by IS NOT NULL
  AND u.id IS NULL;

UPDATE daily_recording_instances d
LEFT JOIN users u ON u.id = d.paid_overtime_marked_by
SET d.paid_overtime_marked_by = NULL
WHERE d.paid_overtime_marked_by IS NOT NULL
  AND u.id IS NULL;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `schedule` ADD CONSTRAINT `fk_schedule_paid_overtime_user` FOREIGN KEY (`paid_overtime_marked_by`) REFERENCES `users`(`id`) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT ''fk_schedule_paid_overtime_user already exists'' AS message')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'schedule'
    AND CONSTRAINT_NAME = 'fk_schedule_paid_overtime_user'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

SET @step27_sql = (
  SELECT IF(COUNT(*) = 0,
    'ALTER TABLE `daily_recording_instances` ADD CONSTRAINT `fk_daily_instances_paid_overtime_user` FOREIGN KEY (`paid_overtime_marked_by`) REFERENCES `users`(`id`) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT ''fk_daily_instances_paid_overtime_user already exists'' AS message')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'daily_recording_instances'
    AND CONSTRAINT_NAME = 'fk_daily_instances_paid_overtime_user'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
PREPARE step27_stmt FROM @step27_sql;
EXECUTE step27_stmt;
DEALLOCATE PREPARE step27_stmt;

INSERT IGNORE INTO system_settings (setting_key, setting_value, description, updated_at) VALUES
('missed_start_alert_minutes', '10', 'Minutes after scheduled start before sending missed-start internal alert.', NOW()),
('missed_end_alert_minutes', '10', 'Minutes after scheduled end before sending missed-completion internal alert.', NOW()),
('push_vapid_private_key', '', 'Private VAPID key for future server-side Web Push delivery. Keep this secret.', NOW()),
('push_vapid_subject', 'mailto:shahidmunir@crescentcollege.edu.pk', 'VAPID contact subject for browser push services.', NOW());

INSERT IGNORE INTO automation_rules (rule_key, rule_name, is_enabled, description, updated_at) VALUES
('missed_recording_start_alert', 'Missed Recording Start Alert', 1, 'Send internal alert if a recording is not started after the grace period.', NOW()),
('missed_recording_end_alert', 'Missed Recording End Alert', 1, 'Send internal alert if a started recording is not completed after scheduled end.', NOW()),
('recording_status_broadcast', 'Recording Status Broadcast', 1, 'Broadcast class start and completion updates to all cameramen.', NOW()),
('paid_overtime_marked', 'Paid Overtime Marked', 1, 'Log and notify when superadmin marks a completed recording for paid overtime.', NOW());