-- Baltique OS Core 0.54.0 — Revenue & Patient Experience Bridge / Copilot Action Fix / OVH Mail Cron v2

CREATE TABLE IF NOT EXISTS revenue_px_procedure_costs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  procedure_key VARCHAR(190) NOT NULL,
  procedure_name VARCHAR(255) NOT NULL,
  estimated_direct_cost_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  estimated_staff_cost_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  estimated_room_cost_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  note TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  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_rpx_proc_key (procedure_key),
  KEY idx_rpx_proc_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS revenue_px_bridge_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  revenue_gross_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  refund_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
  margin_estimate_cents BIGINT NOT NULL DEFAULT 0,
  patient_count INT UNSIGNED NOT NULL DEFAULT 0,
  high_risk_patient_count INT UNSIGNED NOT NULL DEFAULT 0,
  complaint_count INT UNSIGNED NOT NULL DEFAULT 0,
  delayed_case_count INT UNSIGNED NOT NULL DEFAULT 0,
  nps_avg DECIMAL(6,2) NULL,
  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_rpx_report_month (report_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS revenue_px_bridge_recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_month CHAR(7) NOT NULL,
  recommendation_type VARCHAR(100) 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_rpx_rec_month (report_month,status,severity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
