-- ====================================================================
-- Redact settings secrets from old audit-log rows (2026-09-25)
--   Until this date, PUT /api/settings wrote the whole submitted form into
--   audit_logs.new_values, including the SMTP password and the PayPal /
--   Stripe / Google / Facebook secrets in plain text. Anyone with the
--   Audit Logs permission could read them. The route now logs only
--   "(changed)" / "(cleared)" for secrets; this replaces the plain values
--   already stored with "(redacted)". Nothing else in those rows changes.
-- Works on MariaDB 10.4+ and MySQL 8. Plain statements, safe to run more
-- than once. Consider rotating any key that was ever saved in plain text
-- where others could read the audit log.
-- ====================================================================

SET NAMES utf8mb4;

UPDATE `audit_logs` SET `new_values` = JSON_REPLACE(`new_values`, '$.smtp_password', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`new_values`) AND JSON_TYPE(JSON_EXTRACT(`new_values`, '$.smtp_password')) = 'STRING';
UPDATE `audit_logs` SET `new_values` = JSON_REPLACE(`new_values`, '$.google_client_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`new_values`) AND JSON_TYPE(JSON_EXTRACT(`new_values`, '$.google_client_secret')) = 'STRING';
UPDATE `audit_logs` SET `new_values` = JSON_REPLACE(`new_values`, '$.facebook_app_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`new_values`) AND JSON_TYPE(JSON_EXTRACT(`new_values`, '$.facebook_app_secret')) = 'STRING';
UPDATE `audit_logs` SET `new_values` = JSON_REPLACE(`new_values`, '$.paypal_client_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`new_values`) AND JSON_TYPE(JSON_EXTRACT(`new_values`, '$.paypal_client_secret')) = 'STRING';
UPDATE `audit_logs` SET `new_values` = JSON_REPLACE(`new_values`, '$.stripe_secret_key', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`new_values`) AND JSON_TYPE(JSON_EXTRACT(`new_values`, '$.stripe_secret_key')) = 'STRING';
UPDATE `audit_logs` SET `new_values` = JSON_REPLACE(`new_values`, '$.stripe_webhook_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`new_values`) AND JSON_TYPE(JSON_EXTRACT(`new_values`, '$.stripe_webhook_secret')) = 'STRING';

UPDATE `audit_logs` SET `old_values` = JSON_REPLACE(`old_values`, '$.smtp_password', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`old_values`) AND JSON_TYPE(JSON_EXTRACT(`old_values`, '$.smtp_password')) = 'STRING';
UPDATE `audit_logs` SET `old_values` = JSON_REPLACE(`old_values`, '$.google_client_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`old_values`) AND JSON_TYPE(JSON_EXTRACT(`old_values`, '$.google_client_secret')) = 'STRING';
UPDATE `audit_logs` SET `old_values` = JSON_REPLACE(`old_values`, '$.facebook_app_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`old_values`) AND JSON_TYPE(JSON_EXTRACT(`old_values`, '$.facebook_app_secret')) = 'STRING';
UPDATE `audit_logs` SET `old_values` = JSON_REPLACE(`old_values`, '$.paypal_client_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`old_values`) AND JSON_TYPE(JSON_EXTRACT(`old_values`, '$.paypal_client_secret')) = 'STRING';
UPDATE `audit_logs` SET `old_values` = JSON_REPLACE(`old_values`, '$.stripe_secret_key', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`old_values`) AND JSON_TYPE(JSON_EXTRACT(`old_values`, '$.stripe_secret_key')) = 'STRING';
UPDATE `audit_logs` SET `old_values` = JSON_REPLACE(`old_values`, '$.stripe_webhook_secret', '(redacted)')
  WHERE `table_name` = 'site_settings' AND JSON_VALID(`old_values`) AND JSON_TYPE(JSON_EXTRACT(`old_values`, '$.stripe_webhook_secret')) = 'STRING';
