-- Baltique OS Core 0.73.0 — OR Team Performance Analytics

CREATE TABLE IF NOT EXISTS or_team_performance_role_metrics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  metric_date DATE NOT NULL,
  role_slug VARCHAR(80) NOT NULL,
  total_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  completed_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  open_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  escalated_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  notification_count INT UNSIGNED NOT NULL DEFAULT 0,
  acknowledged_notifications INT UNSIGNED NOT NULL DEFAULT 0,
  avg_ack_minutes DECIMAL(10,2) NULL,
  avg_start_minutes DECIMAL(10,2) NULL,
  avg_completion_minutes DECIMAL(10,2) NULL,
  sla_compliance_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  performance_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(40) NOT NULL DEFAULT 'ok',
  metrics_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_team_perf_role_date (metric_date, role_slug),
  KEY idx_or_team_perf_role_score (metric_date, performance_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_team_performance_person_metrics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  metric_date DATE NOT NULL,
  role_slug VARCHAR(80) NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  staff_label VARCHAR(190) NULL,
  staff_email VARCHAR(190) NULL,
  total_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  completed_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  open_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  escalated_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  avg_ack_minutes DECIMAL(10,2) NULL,
  avg_completion_minutes DECIMAL(10,2) NULL,
  performance_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  metrics_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_team_perf_person_date (metric_date, role_slug, assigned_user_id, staff_label),
  KEY idx_or_team_perf_person_score (metric_date, performance_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_team_performance_alert_metrics (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  metric_date DATE NOT NULL,
  responsible_role VARCHAR(80) NULL,
  channel VARCHAR(40) NOT NULL DEFAULT 'internal',
  notification_count INT UNSIGNED NOT NULL DEFAULT 0,
  acknowledged_count INT UNSIGNED NOT NULL DEFAULT 0,
  escalation_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_ack_minutes DECIMAL(10,2) NULL,
  ack_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
  effectiveness_score DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  metrics_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_or_team_alert_metric (metric_date, responsible_role, channel),
  KEY idx_or_team_alert_score (metric_date, effectiveness_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_team_performance_bottlenecks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  metric_date DATE NOT NULL,
  bottleneck_type VARCHAR(80) NOT NULL,
  role_slug VARCHAR(80) NULL,
  staff_label VARCHAR(190) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  metric_value DECIMAL(12,2) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_or_team_bottleneck_date (metric_date, status, severity),
  KEY idx_or_team_bottleneck_role (role_slug, metric_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_team_performance_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  metric_date DATE NOT NULL,
  recommendation_type VARCHAR(80) NOT NULL,
  role_slug VARCHAR(80) NULL,
  staff_label VARCHAR(190) NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  proposed_action_json JSON 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_team_rec_date (metric_date, status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS or_team_performance_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_date DATE NOT NULL,
  total_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  completed_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  overdue_tasks INT UNSIGNED NOT NULL DEFAULT 0,
  escalation_count INT UNSIGNED NOT NULL DEFAULT 0,
  notification_count INT UNSIGNED NOT NULL DEFAULT 0,
  acknowledged_notifications INT UNSIGNED NOT NULL DEFAULT 0,
  avg_ack_minutes DECIMAL(10,2) NULL,
  avg_completion_minutes DECIMAL(10,2) NULL,
  team_score DECIMAL(10,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),
  UNIQUE KEY uq_or_team_report_date (report_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
