-- Baltique OS Core 0.52.0 — Patient Service Quality Board / Polygon Editor Hotfix

CREATE TABLE IF NOT EXISTS patient_service_quality_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_month CHAR(7) NOT NULL,
  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,
  escalation_count INT UNSIGNED NOT NULL DEFAULT 0,
  avg_first_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),
  UNIQUE KEY uq_psq_month (period_month),
  KEY idx_psq_period (period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_service_quality_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period_month CHAR(7) NOT NULL,
  recommendation_type VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'info',
  title VARCHAR(255) NOT NULL,
  body TEXT NULL,
  action_hint 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_psq_rec_month (period_month, status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hospital_floorplan_backgrounds (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  site_id BIGINT UNSIGNED NOT NULL,
  floor_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(190) NOT NULL,
  original_name VARCHAR(255) NULL,
  stored_name VARCHAR(255) NULL,
  storage_path TEXT NULL,
  mime_type VARCHAR(120) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_floor_bg_floor (floor_id, status),
  KEY idx_floor_bg_site (site_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
