-- Baltique OS Core 0.34.0 — Data Retention / Patient Anonymization / Test Cleanup

CREATE TABLE IF NOT EXISTS data_retention_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  action_type VARCHAR(80) NOT NULL,
  patient_count INT UNSIGNED NOT NULL DEFAULT 0,
  patient_hash VARCHAR(128) NULL,
  reason TEXT NULL,
  delete_files TINYINT(1) NOT NULL DEFAULT 0,
  summary_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_data_retention_logs_action (action_type, created_at),
  KEY idx_data_retention_logs_user (created_by_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS data_retention_reports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  report_type VARCHAR(80) NOT NULL DEFAULT 'compliance',
  report_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_data_retention_reports_type (report_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- patients gets anonymized_at / anonymized_by_user_id / anonymization_reason in upgrade_0_34_0.php when missing.
