10 Audit

-- 10_audit: Flag raw records before accepting them
-- Read-only reference query. Setup must be loaded first.
SELECT r.batch_row,r.source_order_id,
 CASE WHEN r.customer_id IS NULL THEN 'missing_customer'
      WHEN c.customer_id IS NULL THEN 'unknown_customer' ELSE 'ok' END AS customer_check,
 CASE WHEN r.amount IS NULL THEN 'missing_amount'
      WHEN r.amount<0 THEN 'negative_amount' ELSE 'ok' END AS amount_check,
 CASE WHEN lower(trim(r.status_text)) IN ('completed','pending','cancelled') THEN 'ok'
      ELSE 'unknown_status' END AS status_check,
 CASE WHEN r.order_date_text IS NOT NULL AND pg_input_is_valid(r.order_date_text,'date')
      THEN 'ok' ELSE 'invalid_date' END AS date_check
FROM aabha_sql.raw_orders AS r LEFT JOIN aabha_sql.customers AS c
 ON c.customer_id=r.customer_id ORDER BY r.batch_row;