-- 09_audit_trail_customers.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: Audit trail trigger on customer changes.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.

-- Audit trail for customers table
CREATE TABLE IF NOT EXISTS bank.customers_audit (
  audit_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT,
  op CHAR(1), -- I/U/D
  changed_at TIMESTAMPTZ DEFAULT now(),
  old_row JSONB,
  new_row JSONB
);

CREATE OR REPLACE FUNCTION bank.trg_customers_audit() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO bank.customers_audit (customer_id, op, old_row) VALUES (OLD.customer_id, 'D', to_jsonb(OLD));
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO bank.customers_audit (customer_id, op, old_row, new_row) VALUES (OLD.customer_id, 'U', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSE
    INSERT INTO bank.customers_audit (customer_id, op, new_row) VALUES (NEW.customer_id, 'I', to_jsonb(NEW));
    RETURN NEW;
  END IF;
END; $$;

DROP TRIGGER IF EXISTS trg_customers_audit ON bank.customers;
CREATE TRIGGER trg_customers_audit AFTER INSERT OR UPDATE OR DELETE ON bank.customers
FOR EACH ROW EXECUTE FUNCTION bank.trg_customers_audit();
