-- Baltique OS Core 0.53.0 — Patient Experience Cockpit / OS hotfixes

CREATE TABLE IF NOT EXISTS patient_experience_feedback (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  patient_id BIGINT UNSIGNED NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  appointment_id BIGINT UNSIGNED NULL,
  feedback_date DATE NOT NULL,
  source VARCHAR(80) NOT NULL DEFAULT 'manual',
  nps_score TINYINT NULL,
  satisfaction_score TINYINT NULL,
  complaint_flag TINYINT(1) NOT NULL DEFAULT 0,
  complaint_category VARCHAR(120) NULL,
  comment 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_px_feedback_date (feedback_date),
  KEY idx_px_feedback_patient (patient_id, feedback_date),
  KEY idx_px_feedback_case (operative_case_id, feedback_date),
  KEY idx_px_feedback_complaint (complaint_flag, feedback_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_experience_risk_scores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  score_month CHAR(7) NOT NULL,
  patient_id BIGINT UNSIGNED NOT NULL,
  operative_case_id BIGINT UNSIGNED NULL,
  risk_score INT NOT NULL DEFAULT 0,
  risk_level VARCHAR(40) NOT NULL DEFAULT 'low',
  factors_json JSON NULL,
  recommendation TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_px_risk_month_patient_case (score_month, patient_id, operative_case_id),
  KEY idx_px_risk_level (score_month, risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_experience_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  nps_avg DECIMAL(6,2) NULL,
  satisfaction_avg DECIMAL(6,2) NULL,
  complaint_count INT UNSIGNED NOT NULL DEFAULT 0,
  contact_count INT UNSIGNED NOT NULL DEFAULT 0,
  escalation_count INT UNSIGNED NOT NULL DEFAULT 0,
  delayed_case_count INT UNSIGNED NOT NULL DEFAULT 0,
  risk_high_count INT UNSIGNED NOT NULL DEFAULT 0,
  report_json JSON 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_px_report_month (report_month),
  KEY idx_px_report_period (period_from, period_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS patient_experience_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_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,
  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_px_rec_month (report_month, status, severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS paycore_price_catalog (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_type VARCHAR(80) NOT NULL DEFAULT 'procedure',
  source_code VARCHAR(120) NULL,
  name VARCHAR(255) NOT NULL,
  category VARCHAR(120) NULL,
  vat_mode VARCHAR(40) NOT NULL DEFAULT 'vat',
  vat_rate DECIMAL(6,2) NOT NULL DEFAULT 23.00,
  amount_net_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  amount_gross_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'PLN',
  active TINYINT(1) NOT NULL DEFAULT 1,
  note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  KEY idx_pc_price_type(item_type, active),
  KEY idx_pc_price_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
