-- Baltique OS Core 0.74.0 — OR Process Mining & Bottleneck AI / Web Push registration hotfix
CREATE TABLE IF NOT EXISTS or_process_mining_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  total_cases INT UNSIGNED NOT NULL DEFAULT 0,
  total_events INT UNSIGNED NOT NULL DEFAULT 0,
  bottleneck_count INT UNSIGNED NOT NULL DEFAULT 0,
  top_bottleneck VARCHAR(255) NULL,
  avg_case_duration_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),
  KEY idx_or_pm_report_period(period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_process_mining_stage_metrics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_id BIGINT UNSIGNED NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  stage_key VARCHAR(80) NOT NULL,
  stage_label VARCHAR(190) NULL,
  case_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_duration_minutes DECIMAL(10,2) NULL,
  p90_duration_minutes DECIMAL(10,2) NULL,
  max_duration_minutes DECIMAL(10,2) NULL,
  queue_count INT UNSIGNED NOT NULL DEFAULT 0,
  delay_count INT UNSIGNED NOT NULL DEFAULT 0,
  bottleneck_score DECIMAL(10,2) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_pm_stage(period_from, period_to, stage_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_process_mining_transition_metrics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_id BIGINT UNSIGNED NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  transition_key VARCHAR(160) NOT NULL,
  from_stage VARCHAR(80) NULL,
  to_stage VARCHAR(80) NULL,
  transition_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_minutes DECIMAL(10,2) NULL,
  p90_minutes DECIMAL(10,2) NULL,
  max_minutes DECIMAL(10,2) NULL,
  bottleneck_score DECIMAL(10,2) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_pm_transition(period_from, period_to, transition_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_process_mining_role_metrics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_id BIGINT UNSIGNED NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  role_slug VARCHAR(80) NOT NULL,
  total_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  escalation_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_task_minutes DECIMAL(10,2) NULL,
  bottleneck_score DECIMAL(10,2) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_pm_role(period_from, period_to, role_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_process_mining_bottlenecks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_id BIGINT UNSIGNED NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  bottleneck_type VARCHAR(80) NOT NULL,
  target_key VARCHAR(160) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  metric_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_or_pm_bottleneck(period_from, period_to, severity, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS or_process_mining_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_id BIGINT UNSIGNED NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  recommendation_type VARCHAR(80) NOT NULL,
  target_key VARCHAR(160) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  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,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_pm_rec(period_from, period_to, status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
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,
  endpoint_hash VARCHAR(128) NOT NULL,
  endpoint TEXT NOT NULL,
  p256dh VARCHAR(255) NULL,
  auth VARCHAR(255) NULL,
  user_agent TEXT NULL,
  device_label VARCHAR(190) NULL,
  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_mobile_push_endpoint(endpoint_hash),
  KEY idx_or_mobile_push_user(user_id, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
