-- Baltique OS Core 0.71.0 — OR Staff Mobile Console / OR Live & Readiness Hotfixes

CREATE TABLE IF NOT EXISTS or_staff_mobile_notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_task_id BIGINT UNSIGNED NULL,
  live_date DATE NULL,
  responsible_role VARCHAR(80) NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'internal',
  recipient_email VARCHAR(190) NULL,
  recipient_phone VARCHAR(80) NULL,
  subject VARCHAR(255) NULL,
  body TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  queue_table VARCHAR(80) NULL,
  queue_id BIGINT UNSIGNED NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_staff_mobile_notif_task(staff_task_id, channel, status),
  KEY idx_or_staff_mobile_notif_day(live_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_staff_mobile_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  role_slug VARCHAR(80) NULL,
  live_date DATE NULL,
  device_label VARCHAR(190) NULL,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_staff_mobile_session_user(user_id, live_date),
  KEY idx_or_staff_mobile_session_role(role_slug, live_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
