-- 023_audit_trigger_all_columns.sql
--
-- Audit trigger covering ALL meaningful columns on customers table.
-- Sensitive fields (encrypted content, keys, message_html) are logged as
-- [REDACTED] — records THAT the change happened and WHO made it,
-- but never stores the actual content in the audit log.

CREATE OR REPLACE FUNCTION audit_customers_changes()
RETURNS TRIGGER AS $$
DECLARE
  col_name TEXT;
  old_val  TEXT;
  new_val  TEXT;
  source   TEXT;
  app_src  TEXT;
  is_sensitive BOOLEAN;
BEGIN
  app_src := NULLIF(current_setting('app.source', true), '');
  IF app_src IS NOT NULL THEN
    source := 'auto::' || app_src;
  ELSE
    source := 'manual::' || current_user;
  END IF;

  FOR col_name IN
    SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'customers'
      AND column_name NOT IN (
        'encryption_iv', 'encryption_tag', 'message_iv', 'message_tag',
        'sender_encryption_iv', 'sender_encryption_tag',
        'sender_message_iv', 'sender_message_tag'
      )
  LOOP
    EXECUTE format('SELECT ($1).%I::text', col_name) INTO old_val USING OLD;
    EXECUTE format('SELECT ($1).%I::text', col_name) INTO new_val USING NEW;
    IF old_val IS DISTINCT FROM new_val THEN
      is_sensitive := col_name IN (
        'encrypted_image', 'encrypted_message',
        'sender_encrypted_image', 'sender_encrypted_message',
        'message_html',
        'decryption_key', 'sender_decryption_key',
        'decryption_key_enc', 'decryption_key_enc_iv', 'decryption_key_enc_tag',
        'sender_decryption_key_enc', 'sender_decryption_key_enc_iv', 'sender_decryption_key_enc_tag',
        'kms_encrypted_data_key',
        'recipient_key_hmac', 'sender_key_hmac',
        'recipient_key_issued_at', 'sender_key_issued_at'
      );
      IF is_sensitive THEN
        INSERT INTO audit_log (table_name, record_id, customer_id, operation, field, old_value, new_value, changed_by)
        VALUES ('customers', NEW.order_id, NEW.customer_id, TG_OP, col_name,
                CASE WHEN old_val IS NULL THEN null ELSE '[REDACTED]' END,
                CASE WHEN new_val IS NULL THEN null ELSE '[REDACTED]' END,
                source);
      ELSE
        INSERT INTO audit_log (table_name, record_id, customer_id, operation, field, old_value, new_value, changed_by)
        VALUES ('customers', NEW.order_id, NEW.customer_id, TG_OP, col_name, old_val, new_val, source);
      END IF;
    END IF;
  END LOOP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_audit_customers ON customers;
CREATE TRIGGER trg_audit_customers
  AFTER UPDATE ON customers
  FOR EACH ROW
  EXECUTE FUNCTION audit_customers_changes();
