-- Baltique OS Core 0.72.0 — OR Mobile Push & Escalation Center

CREATE TABLE IF NOT EXISTS or_mobile_push_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  role_slug VARCHAR(80) NULL,
  staff_email VARCHAR(190) NULL,
  staff_phone VARCHAR(80) NULL,
  device_label VARCHAR(190) NULL,
  endpoint_hash VARCHAR(128) NOT NULL,
  subscription_json JSON NOT NULL,
  permission_status VARCHAR(40) NOT NULL DEFAULT 'granted',
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_push_endpoint(endpoint_hash),
  KEY idx_or_push_user(user_id, active),
  KEY idx_or_push_role(role_slug, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_mobile_notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_task_id BIGINT UNSIGNED NULL,
  live_date DATE NULL,
  live_case_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  responsible_role VARCHAR(80) NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  notification_type VARCHAR(80) NOT NULL DEFAULT 'task_alert',
  channels_json JSON NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  ack_required TINYINT(1) NOT NULL DEFAULT 1,
  ack_by_user_id BIGINT UNSIGNED NULL,
  ack_at DATETIME NULL,
  push_status VARCHAR(40) NULL,
  email_queue_id BIGINT UNSIGNED NULL,
  sms_queue_id BIGINT UNSIGNED NULL,
  internal_queue_id BIGINT UNSIGNED NULL,
  last_error TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_mobile_notif_task(staff_task_id, status),
  KEY idx_or_mobile_notif_day(live_date, status, severity),
  KEY idx_or_mobile_notif_role(responsible_role, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_mobile_escalation_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_code VARCHAR(120) NOT NULL,
  role_slug VARCHAR(80) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  after_minutes INT UNSIGNED NOT NULL DEFAULT 10,
  escalation_level INT UNSIGNED NOT NULL DEFAULT 1,
  escalate_to_role VARCHAR(80) NOT NULL DEFAULT 'coordinator',
  channels_json JSON NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_mobile_policy(policy_code),
  KEY idx_or_mobile_policy_role(role_slug, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_mobile_escalation_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_task_id BIGINT UNSIGNED NOT NULL,
  policy_id BIGINT UNSIGNED NULL,
  escalation_level INT UNSIGNED NOT NULL DEFAULT 1,
  from_role VARCHAR(80) NULL,
  to_role VARCHAR(80) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  reason TEXT NULL,
  notification_id BIGINT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_mobile_escalation(staff_task_id, escalation_level, to_role),
  KEY idx_or_mobile_esc_status(status, severity, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_mobile_response_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_date DATE NOT NULL,
  total_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  open_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  acknowledged_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  completed_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  notification_count INT UNSIGNED NOT NULL DEFAULT 0,
  escalation_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_ack_minutes DECIMAL(10,2) NULL,
  avg_completion_minutes DECIMAL(10,2) NULL,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_mobile_response_report(report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
