-- Baltique OS Core 0.51.0 — SLA Command Center

CREATE TABLE IF NOT EXISTS sla_command_duty_roster (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_slug VARCHAR(80) NOT NULL DEFAULT 'front_coordination',
  user_id BIGINT UNSIGNED NULL,
  display_name VARCHAR(190) NOT NULL,
  contact_email VARCHAR(190) NULL,
  contact_phone VARCHAR(80) NULL,
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  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_sla_duty_role_time (role_slug, starts_at, ends_at, active),
  KEY idx_sla_duty_user (user_id, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sla_command_escalations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  triage_id BIGINT UNSIGNED NULL,
  inbound_task_id BIGINT UNSIGNED NULL,
  thread_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  patient_id BIGINT UNSIGNED NULL,
  category VARCHAR(80) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'normal',
  escalation_level INT UNSIGNED NOT NULL DEFAULT 1,
  assigned_role VARCHAR(80) NOT NULL DEFAULT 'front_coordination',
  assigned_user_id BIGINT UNSIGNED NULL,
  assigned_name VARCHAR(190) NULL,
  sla_due_at DATETIME NULL,
  overdue_minutes INT NOT NULL DEFAULT 0,
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  acknowledged_at DATETIME NULL,
  resolved_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_sla_esc_status (status, severity, escalation_level, sla_due_at),
  KEY idx_sla_esc_case (operative_case_id, status),
  KEY idx_sla_esc_triage (triage_id, status),
  KEY idx_sla_esc_task (inbound_task_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sla_command_quality_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  total_items INT UNSIGNED NOT NULL DEFAULT 0,
  within_sla INT UNSIGNED NOT NULL DEFAULT 0,
  breached_sla INT UNSIGNED NOT NULL DEFAULT 0,
  critical_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_response_minutes DECIMAL(10,2) NULL,
  sla_compliance_rate DECIMAL(6,2) NOT NULL DEFAULT 0.00,
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sla_report_period (period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sla_command_action_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  action_type VARCHAR(80) NOT NULL,
  triage_id BIGINT UNSIGNED NULL,
  inbound_task_id BIGINT UNSIGNED NULL,
  escalation_id BIGINT UNSIGNED NULL,
  old_status VARCHAR(80) NULL,
  new_status VARCHAR(80) NULL,
  note TEXT NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sla_action_created (action_type, created_at),
  KEY idx_sla_action_esc (escalation_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
