-- Baltique OS Core 0.70.0 — OR Event Automation & Staff Assignment

CREATE TABLE IF NOT EXISTS or_event_staff_roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_slug VARCHAR(80) NOT NULL,
  role_label VARCHAR(190) NOT NULL,
  role_group VARCHAR(80) NOT NULL DEFAULT 'or',
  default_sla_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  escalation_role VARCHAR(80) 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_event_staff_role(role_slug),
  KEY idx_or_event_staff_role_active(active, role_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_event_staff_duties (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  duty_date DATE NOT NULL,
  role_slug VARCHAR(80) NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  staff_label VARCHAR(190) NULL,
  staff_email VARCHAR(190) NULL,
  staff_phone VARCHAR(80) NULL,
  starts_at TIME NOT NULL DEFAULT '07:00:00',
  ends_at TIME NOT NULL DEFAULT '19:00:00',
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  note TEXT 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_event_duty_date_role(duty_date, role_slug, status),
  KEY idx_or_event_duty_user(user_id, duty_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_event_automation_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_code VARCHAR(120) NOT NULL,
  rule_label VARCHAR(190) NOT NULL,
  stage_from VARCHAR(40) NULL,
  stage_to VARCHAR(40) NOT NULL,
  event_type VARCHAR(80) NOT NULL DEFAULT 'stage_changed',
  responsible_role VARCHAR(80) NOT NULL,
  task_title VARCHAR(255) NOT NULL,
  task_body TEXT NULL,
  task_domain VARCHAR(80) NOT NULL DEFAULT 'or_flow',
  sla_minutes INT UNSIGNED NOT NULL DEFAULT 15,
  alert_severity VARCHAR(40) NOT NULL DEFAULT 'info',
  create_alert TINYINT(1) NOT NULL DEFAULT 0,
  requires_ack TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 100,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_event_rule_code(rule_code),
  KEY idx_or_event_rule_stage(stage_to, active, sort_order),
  KEY idx_or_event_rule_role(responsible_role, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_event_staff_tasks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  live_date DATE NOT NULL,
  live_case_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  booking_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  source_event_id BIGINT UNSIGNED NULL,
  automation_rule_id BIGINT UNSIGNED NULL,
  task_code VARCHAR(160) NULL,
  task_title VARCHAR(255) NOT NULL,
  task_body TEXT NULL,
  task_domain VARCHAR(80) NOT NULL DEFAULT 'or_flow',
  stage_from VARCHAR(40) NULL,
  stage_to VARCHAR(40) NULL,
  responsible_role VARCHAR(80) NOT NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  assigned_staff_label VARCHAR(190) NULL,
  assigned_staff_email VARCHAR(190) NULL,
  assigned_staff_phone VARCHAR(80) NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  due_at DATETIME NULL,
  acknowledged_at DATETIME NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  completed_by_user_id BIGINT UNSIGNED NULL,
  completion_note TEXT NULL,
  signature_data_url MEDIUMTEXT NULL,
  signature_sha256 VARCHAR(128) NULL,
  escalation_level INT UNSIGNED NOT NULL DEFAULT 0,
  alert_id BIGINT UNSIGNED NULL,
  mirror_table VARCHAR(80) NULL,
  mirror_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),
  UNIQUE KEY uq_or_event_task_rule_case(source_event_id, automation_rule_id),
  KEY idx_or_event_task_day_status(live_date, status, priority),
  KEY idx_or_event_task_role(responsible_role, status, due_at),
  KEY idx_or_event_task_case(operative_case_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_event_staff_alerts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_task_id BIGINT UNSIGNED NULL,
  live_date DATE NOT NULL,
  live_case_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  responsible_role VARCHAR(80) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  acknowledged_by_user_id BIGINT UNSIGNED NULL,
  acknowledged_at DATETIME NULL,
  resolved_by_user_id BIGINT UNSIGNED NULL,
  resolved_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_event_staff_alert_day(live_date, status, severity),
  KEY idx_or_event_staff_alert_task(staff_task_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_event_staff_task_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_task_id BIGINT UNSIGNED NULL,
  live_date DATE NULL,
  event_type VARCHAR(80) NOT NULL,
  event_body TEXT NULL,
  old_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_event_staff_task_evt(staff_task_id, created_at),
  KEY idx_or_event_staff_task_evt_day(live_date, event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
