ALTER TABLE employees
  ADD COLUMN IF NOT EXISTS last_login_at DATETIME NULL,
  ADD COLUMN IF NOT EXISTS last_seen_at DATETIME NULL,
  ADD COLUMN IF NOT EXISTS last_logout_at DATETIME NULL,
  ADD COLUMN IF NOT EXISTS is_online TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS current_session_id VARCHAR(128) NULL;

CREATE TABLE IF NOT EXISTS staff_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT NOT NULL,
  session_id VARCHAR(128) NOT NULL,
  connected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NULL,
  disconnected_at DATETIME NULL,
  ip_address VARCHAR(45) NULL,
  user_agent TEXT NULL,
  logout_reason VARCHAR(60) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_staff_sessions_session_id (session_id),
  KEY idx_staff_sessions_employee_id (employee_id),
  KEY idx_staff_sessions_last_seen_at (last_seen_at),
  KEY idx_staff_sessions_disconnected_at (disconnected_at)
);

CREATE TABLE IF NOT EXISTS patient_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  action ENUM('created', 'updated', 'deleted') NOT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_patient_events_created_at (created_at),
  INDEX idx_patient_events_patient_id (patient_id)
);

CREATE TABLE IF NOT EXISTS realtime_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  topic VARCHAR(80) NOT NULL,
  record_id INT NOT NULL,
  action ENUM('created', 'updated', 'deleted') NOT NULL,
  metadata LONGTEXT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_realtime_events_topic_id (topic, id),
  INDEX idx_realtime_events_created_at (created_at),
  INDEX idx_realtime_events_record_id (record_id)
);