-- Step 22: Management final audit support.
-- Run after schema.sql, step15_enterprise_ops.sql, step17_open_ended_recordings.sql,
-- step18_branding_backup_datetime.sql, and step21_management_reporting.sql if present.

ALTER TABLE cameraman_attendance
  MODIFY status ENUM('Present','Late','Absent','Leave','Short Leave','Issue Reported') NOT NULL DEFAULT 'Present';

CREATE TABLE IF NOT EXISTS schedule_resource_changes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_type ENUM('schedule','daily_instance') NOT NULL,
  source_id INT UNSIGNED NOT NULL,
  old_cameraman_id INT UNSIGNED DEFAULT NULL,
  new_cameraman_id INT UNSIGNED DEFAULT NULL,
  old_camera_id INT UNSIGNED DEFAULT NULL,
  new_camera_id INT UNSIGNED DEFAULT NULL,
  old_room_id INT UNSIGNED DEFAULT NULL,
  new_room_id INT UNSIGNED DEFAULT NULL,
  reason TEXT DEFAULT NULL,
  changed_by INT UNSIGNED DEFAULT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_resource_changes_source (source_type, source_id),
  KEY idx_resource_changes_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_enabled', '0', 'Reserved for future SMTP delivery. Current delivery uses PHP mail plus fallback log.')
ON DUPLICATE KEY UPDATE description = 'Reserved for future SMTP delivery. Current delivery uses PHP mail plus fallback log.', updated_at = CURRENT_TIMESTAMP;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_host', '', 'SMTP host for future PHPMailer integration.')
ON DUPLICATE KEY UPDATE description = 'SMTP host for future PHPMailer integration.', updated_at = CURRENT_TIMESTAMP;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_port', '587', 'SMTP port for future PHPMailer integration.')
ON DUPLICATE KEY UPDATE description = 'SMTP port for future PHPMailer integration.', updated_at = CURRENT_TIMESTAMP;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_username', '', 'SMTP username for future PHPMailer integration.')
ON DUPLICATE KEY UPDATE description = 'SMTP username for future PHPMailer integration.', updated_at = CURRENT_TIMESTAMP;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_password', '', 'SMTP password placeholder. Store securely before enabling real SMTP.')
ON DUPLICATE KEY UPDATE description = 'SMTP password placeholder. Store securely before enabling real SMTP.', updated_at = CURRENT_TIMESTAMP;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_from_email', 'no-reply@crescentcollege.edu.pk', 'Sender email for future SMTP delivery.')
ON DUPLICATE KEY UPDATE description = 'Sender email for future SMTP delivery.', updated_at = CURRENT_TIMESTAMP;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES ('smtp_from_name', 'CCA Media Recording System', 'Sender name for future SMTP delivery.')
ON DUPLICATE KEY UPDATE description = 'Sender name for future SMTP delivery.', updated_at = CURRENT_TIMESTAMP;