-- Baltique OS Core 2.8.0
-- Patient & Staff Experience Finalization
-- Reference migration. Runtime installation is performed idempotently by /upgrade_2_8_0.php.

CREATE TABLE IF NOT EXISTS enterprise_experience_audit_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_uuid VARCHAR(80) NOT NULL,
  run_type VARCHAR(60) NOT NULL DEFAULT 'manual',
  source_root VARCHAR(500) NULL,
  files_scanned INT UNSIGNED NOT NULL DEFAULT 0,
  findings_total INT UNSIGNED NOT NULL DEFAULT 0,
  critical_count INT UNSIGNED NOT NULL DEFAULT 0,
  warning_count INT UNSIGNED NOT NULL DEFAULT 0,
  info_count INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'completed',
  details_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  UNIQUE KEY uq_enterprise_experience_run(run_uuid),
  KEY idx_exp_runs(created_at,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_experience_findings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  run_id BIGINT UNSIGNED NOT NULL,
  finding_key VARCHAR(190) NOT NULL,
  audience VARCHAR(40) NOT NULL DEFAULT 'staff',
  category VARCHAR(80) NOT NULL,
  severity VARCHAR(40) NOT NULL DEFAULT 'warning',
  title VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  file_path VARCHAR(500) NULL,
  line_number INT UNSIGNED NULL,
  route_path VARCHAR(255) NULL,
  remediation TEXT NULL,
  finding_status VARCHAR(40) NOT NULL DEFAULT 'open',
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  review_note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(id),
  KEY idx_exp_find_run(run_id,severity,finding_status),
  KEY idx_exp_find_key(finding_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_experience_preferences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  preference_key VARCHAR(120) NOT NULL,
  preference_value TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_exp_preference(user_id,preference_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS enterprise_experience_route_registry (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  route_path VARCHAR(255) NOT NULL,
  audience VARCHAR(40) NOT NULL DEFAULT 'staff',
  canonical_title_pl VARCHAR(255) NOT NULL,
  canonical_title_en VARCHAR(255) NOT NULL,
  canonical_entry TINYINT(1) NOT NULL DEFAULT 1,
  owner_role VARCHAR(80) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY(id),
  UNIQUE KEY uq_exp_route(route_path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings(setting_key,setting_value,created_at,updated_at) VALUES
('enterprise_patient_staff_experience_version','2.8.0',NOW(),NOW()),
('enterprise_patient_staff_experience_enabled','1',NOW(),NOW()),
('enterprise_experience_canonical_staff_workspace','/workspace.php',NOW(),NOW()),
('enterprise_experience_canonical_patient_home','/patient_home.php',NOW(),NOW()),
('enterprise_experience_final_visual_layer','enterprise_experience_280',NOW(),NOW()),
('enterprise_experience_auto_patient_message_enabled','0',NOW(),NOW()),
('enterprise_experience_auto_clinical_decision_enabled','0',NOW(),NOW()),
('core_version_280','1',NOW(),NOW())
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value),updated_at=NOW();
