-- Step 26: In-app communications, recording reminders, push subscriptions, and helper assignments.
-- Run after step25_superadmin_operations.sql.

CREATE TABLE IF NOT EXISTS app_messages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sender_type ENUM('superadmin','admin','viewer','cameraman','system') NOT NULL DEFAULT 'system',
  sender_id INT UNSIGNED DEFAULT NULL,
  recipient_type ENUM('superadmin','admin','viewer','cameraman','all_admins','all_cameramen') NOT NULL,
  recipient_id INT UNSIGNED DEFAULT NULL,
  subject VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  priority ENUM('normal','urgent') NOT NULL DEFAULT 'normal',
  related_schedule_id INT UNSIGNED DEFAULT NULL,
  related_instance_id INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_app_messages_sender (sender_type, sender_id, created_at),
  KEY idx_app_messages_recipient (recipient_type, recipient_id, created_at),
  KEY idx_app_messages_schedule (related_schedule_id),
  KEY idx_app_messages_instance (related_instance_id),
  CONSTRAINT fk_app_messages_schedule
    FOREIGN KEY (related_schedule_id) REFERENCES schedule(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_app_messages_instance
    FOREIGN KEY (related_instance_id) REFERENCES daily_recording_instances(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_message_reads (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  message_id INT UNSIGNED NOT NULL,
  reader_type ENUM('superadmin','admin','viewer','cameraman') NOT NULL,
  reader_id INT UNSIGNED NOT NULL,
  read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_app_message_read (message_id, reader_type, reader_id),
  KEY idx_app_message_reads_reader (reader_type, reader_id),
  CONSTRAINT fk_app_message_reads_message
    FOREIGN KEY (message_id) REFERENCES app_messages(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recording_reminders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_type ENUM('schedule','daily_instance') NOT NULL,
  source_id INT UNSIGNED NOT NULL,
  recipient_type ENUM('superadmin','admin','viewer','cameraman','all_admins','all_cameramen') NOT NULL,
  recipient_id INT UNSIGNED DEFAULT NULL,
  title VARCHAR(200) NOT NULL,
  message TEXT NOT NULL,
  reminder_at DATETIME NOT NULL,
  channel ENUM('in_app','email','push','all') NOT NULL DEFAULT 'in_app',
  status ENUM('Pending','Sent','Dismissed') NOT NULL DEFAULT 'Pending',
  sent_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_recording_reminder (source_type, source_id, recipient_type, recipient_id, reminder_at, channel),
  KEY idx_recording_reminders_due (status, reminder_at),
  KEY idx_recording_reminders_recipient (recipient_type, recipient_id, status, reminder_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_subscriptions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_type ENUM('superadmin','admin','viewer','cameraman') NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  endpoint_hash CHAR(64) NOT NULL,
  endpoint TEXT NOT NULL,
  p256dh VARCHAR(255) DEFAULT NULL,
  auth_token VARCHAR(255) DEFAULT NULL,
  user_agent VARCHAR(255) DEFAULT NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_push_subscriptions_endpoint (endpoint_hash),
  KEY idx_push_subscriptions_user (user_type, user_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recording_helpers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_type ENUM('schedule','daily_instance') NOT NULL,
  source_id INT UNSIGNED NOT NULL,
  helper_cameraman_id INT UNSIGNED NOT NULL,
  assigned_by INT UNSIGNED DEFAULT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  remarks TEXT DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_recording_helper (source_type, source_id, helper_cameraman_id),
  KEY idx_recording_helpers_helper (helper_cameraman_id, assigned_at),
  KEY idx_recording_helpers_source (source_type, source_id),
  CONSTRAINT fk_recording_helpers_cameraman
    FOREIGN KEY (helper_cameraman_id) REFERENCES cameramen(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_recording_helpers_user
    FOREIGN KEY (assigned_by) REFERENCES users(id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO system_settings (setting_key, setting_value, description, updated_at) VALUES
('enable_push_notifications', '0', 'Allow browser push subscription storage and operational push delivery when a sender is configured.', NOW()),
('push_vapid_public_key', '', 'Public VAPID key for future web push delivery.', NOW()),
('recording_reminder_before_minutes', '15', 'Minutes before recording start to create reminder notifications.', NOW()),
('recording_completion_reminder_minutes', '10', 'Minutes after scheduled end time to remind cameramen to complete recordings.', NOW());

INSERT IGNORE INTO automation_rules (rule_key, rule_name, is_enabled, description, updated_at) VALUES
('recording_reminder_before_start', 'Recording Reminder Before Start', 1, 'Create reminder before a recording starts.', NOW()),
('recording_reminder_complete_after_end', 'Recording Completion Reminder', 1, 'Create reminder after scheduled end time if completion is still pending.', NOW()),
('helper_assignment_created', 'Helper Assignment Created', 1, 'Notify cameramen when superadmin assigns them as helper.', NOW()),
('app_message_sent', 'App Message Sent', 1, 'Log and notify users when an in-app message is sent.', NOW());