USE media_recording_app;

CREATE TABLE IF NOT EXISTS system_settings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(100) NOT NULL,
  setting_value TEXT NULL,
  description TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_system_settings_key (setting_key),
  KEY idx_system_settings_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  recipient_type ENUM('admin','viewer','cameraman') NOT NULL,
  recipient_id INT UNSIGNED DEFAULT NULL,
  title VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  notification_type ENUM('info','warning','danger','success') NOT NULL DEFAULT 'info',
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notifications_recipient (recipient_type, recipient_id, is_read),
  KEY idx_notifications_created_at (created_at),
  KEY idx_notifications_type (notification_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_timeline (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_type ENUM('admin','viewer','cameraman','system') NOT NULL DEFAULT 'system',
  actor_id INT UNSIGNED DEFAULT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id INT UNSIGNED DEFAULT NULL,
  action VARCHAR(150) NOT NULL,
  details TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_timeline_actor (actor_type, actor_id),
  KEY idx_timeline_entity (entity_type, entity_id),
  KEY idx_timeline_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_rules (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_key VARCHAR(100) NOT NULL,
  rule_name VARCHAR(150) NOT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_automation_rules_key (rule_key),
  KEY idx_automation_rules_enabled (is_enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO system_settings (setting_key, setting_value, description)
VALUES
  ('institute_name', 'Media Recording Automation System', 'Name shown across operational screens.'),
  ('default_timezone', 'Asia/Karachi', 'Default timezone for scheduling and reports.'),
  ('enable_notifications', '1', 'Enable system notifications and operational alerts.'),
  ('allow_cameraman_unscheduled_request', '1', 'Allow cameramen to create unscheduled recording requests.'),
  ('default_schedule_status', 'Scheduled', 'Default status used when creating schedules.'),
  ('working_day_start_time', '09:00', 'Default working day start time.'),
  ('working_day_end_time', '17:00', 'Default working day end time.'),
  ('show_sunday_recordings', '0', 'Show Sunday recordings in operational planning views.'),
  ('auto_release_resources_on_cancel', '1', 'Automatically release resources when a schedule is cancelled.'),
  ('auto_release_resources_on_complete', '1', 'Automatically release resources when a schedule is completed.')
ON DUPLICATE KEY UPDATE
  description = VALUES(description),
  updated_at = CURRENT_TIMESTAMP;

INSERT INTO automation_rules (rule_key, rule_name, is_enabled, description)
VALUES
  ('schedule_conflict_detected', 'Schedule Conflict Detected', 1, 'Notify admins when overlapping schedule resources are detected.'),
  ('class_cancelled', 'Class Cancelled', 1, 'Notify admins when a class recording is cancelled.'),
  ('issue_reported', 'Issue Reported', 1, 'Notify admins when an operational issue is reported.'),
  ('camera_maintenance', 'Camera Maintenance', 1, 'Notify admins when a camera is moved to maintenance.'),
  ('teacher_leave_added', 'Teacher Leave Added', 1, 'Notify admins when teacher leave is added.'),
  ('unscheduled_recording_created', 'Unscheduled Recording Created', 1, 'Notify admins when an unscheduled recording is created.'),
  ('cameraman_not_checked_in', 'Cameraman Not Checked In', 1, 'Notify admins when a cameraman has not checked in for today.'),
  ('schedule_started', 'Schedule Started', 1, 'Notify admins when a recording starts.'),
  ('schedule_completed', 'Schedule Completed', 1, 'Notify admins when a recording completes.')
ON DUPLICATE KEY UPDATE
  rule_name = VALUES(rule_name),
  description = VALUES(description),
  updated_at = CURRENT_TIMESTAMP;