Triage Composer

/* Generated by contextIsKey · DataHub context layer; review required; not executed.
Incident question: Why is AsterVale Living's accounts-receivable balance different from the customer-facing order and settlement records?
*/
WITH customer_scope AS (
    -- Mapped DataHub context: identity customers with regional policy.
    SELECT c.customer_id, c.account_id, c.customer_region, p.credit_days,
           CASE WHEN p.region_code IS NULL THEN 1 ELSE 0 END AS policy_join_missing
    FROM identity.customers AS c
    LEFT JOIN policy.regional_ar_rules AS p ON p.region_code = c.customer_region
), order_events AS (
    SELECT o.order_id AS event_id, o.order_id, o.customer_id, o.ordered_at AS event_at,
           o.order_total AS amount, 0 AS source_join_missing
    FROM commerce.orders AS o
), invoice_events AS (
    SELECT a.invoice_id AS event_id, a.order_id, a.customer_id, a.posted_at AS event_at,
           a.amount, 0 AS source_join_missing
    FROM finance.ar_transactions AS a
), order_comparison AS (
    SELECT o.order_id, o.customer_id, o.event_at AS ordered_at, o.amount AS order_total,
           a.amount AS invoice_amount
    FROM order_events AS o
    LEFT JOIN invoice_events AS a ON a.order_id = o.order_id
), payment_events AS (
    SELECT s.settlement_id AS event_id, a.customer_id, s.settled_at AS event_at,
           -s.settled_amount AS amount,
           CASE WHEN a.invoice_id IS NULL THEN 1 ELSE 0 END AS source_join_missing
    FROM payments.settlements AS s
    LEFT JOIN finance.ar_transactions AS a ON a.invoice_id = s.invoice_id
), return_refund_events AS (
    SELECT r.return_id AS event_id, o.customer_id, r.returned_at AS event_at,
           -r.refund_amount AS amount,
           CASE WHEN o.order_id IS NULL THEN 1 ELSE 0 END AS source_join_missing
    FROM commerce.returns_refunds AS r
    LEFT JOIN commerce.orders AS o ON o.order_id = r.order_id
), fulfillment_events AS (
    SELECT s.shipment_id AS event_id, o.customer_id, s.delivered_at AS event_at,
           CAST(0 AS decimal(18, 2)) AS amount,
           CASE WHEN o.order_id IS NULL THEN 1 ELSE 0 END AS source_join_missing
    FROM fulfillment.shipments AS s
    LEFT JOIN commerce.orders AS o ON o.order_id = s.order_id
), normalized_events AS (
    SELECT event_id, customer_id, event_at, amount, 'ORDER' AS event_type, 0 AS affects_ar,
           source_join_missing
    FROM order_events
    UNION ALL
    SELECT event_id, customer_id, event_at, amount, 'INVOICE' AS event_type, 1 AS affects_ar,
           source_join_missing
    FROM invoice_events
    UNION ALL
    SELECT event_id, customer_id, event_at, amount, 'PAYMENT' AS event_type, 1 AS affects_ar,
           source_join_missing
    FROM payment_events
    UNION ALL
    SELECT event_id, customer_id, event_at, amount, 'REFUND' AS event_type, 1 AS affects_ar,
           source_join_missing
    FROM return_refund_events
    UNION ALL
    SELECT event_id, customer_id, event_at, amount, 'FULFILLMENT' AS event_type, 0 AS affects_ar,
           source_join_missing
    FROM fulfillment_events
), running_balance AS (
    SELECT event_id, customer_id, event_at, amount, event_type, affects_ar,
           source_join_missing,
           SUM(CASE WHEN affects_ar = 1 THEN amount ELSE CAST(0 AS decimal(18, 2)) END)
               OVER (PARTITION BY customer_id,
                                    CASE WHEN customer_id IS NULL THEN event_id END
                     ORDER BY event_at, event_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
    FROM normalized_events
), order_invoice_mismatches AS (
    SELECT order_id, customer_id
    FROM order_comparison
    WHERE invoice_amount IS NULL OR order_total <> invoice_amount
), final_results AS (
    SELECT r.event_id, r.customer_id, r.event_at, r.amount, r.event_type, r.affects_ar,
           r.running_balance, c.account_id, c.customer_region, c.credit_days,
           CASE
               WHEN r.source_join_missing = 1 OR c.customer_id IS NULL
                    OR c.policy_join_missing = 1 THEN 'MISSING_JOIN'
               WHEN m.order_id IS NOT NULL THEN 'ORDER_INVOICE_MISMATCH'
               WHEN r.affects_ar = 1 AND (r.running_balance < 0 OR r.amount IS NULL)
                   THEN 'AR_BALANCE_EXCEPTION'
               ELSE NULL
           END AS issue_label
    FROM running_balance AS r
    LEFT JOIN order_invoice_mismatches AS m
        ON m.customer_id = r.customer_id AND m.order_id = r.event_id
    LEFT JOIN customer_scope AS c ON c.customer_id = r.customer_id
)
SELECT f.*
FROM final_results AS f
ORDER BY f.customer_id, f.event_at, f.event_id;