Purchase Order, Receipt and Invoice Reconciliation

CREATE TABLE mart.purchase_item_reconciliation AS
WITH classified AS (
  SELECT
    comparison.*,
    CASE
      WHEN match_requirement = 'CONSIGNMENT'
        THEN 'OUTSIDE_INVOICE_SCOPE'
      WHEN deleted_event_count > 0
        THEN 'CANCELLED_OR_DELETED'
      WHEN invoice_receipt_event_count = 0
        THEN 'MISSING_INVOICE'
      WHEN match_requirement = 'THREE_WAY'
        AND goods_receipt_event_count = 0
        THEN 'MISSING_RECEIPT'
      WHEN reversal_event_count > 0
        THEN 'REVERSAL_REVIEW'
      WHEN invoice_arrived_before_receipt
        AND cleared_after_receipt
        AND purchase_to_invoice_difference <= value_tolerance
        AND receipt_to_invoice_difference <= value_tolerance
        THEN 'TIMING_ITEM_RESOLVED'
      WHEN goods_receipt_event_count > 1
        AND invoice_receipt_event_count > 1
        AND purchase_to_invoice_difference <= value_tolerance
        AND receipt_to_invoice_difference <= value_tolerance
        THEN 'MANY_TO_MANY'
      WHEN goods_receipt_event_count > 1
        AND invoice_receipt_event_count = 1
        AND purchase_to_invoice_difference <= value_tolerance
        AND receipt_to_invoice_difference <= value_tolerance
        THEN 'ONE_TO_MANY_RECEIPTS'
      WHEN goods_receipt_event_count <= 1
        AND invoice_receipt_event_count > 1
        AND purchase_to_invoice_difference <= value_tolerance
        AND receipt_to_invoice_difference <= value_tolerance
        THEN 'ONE_TO_MANY_INVOICES'
      WHEN match_requirement = 'THREE_WAY'
        AND purchase_to_invoice_difference <= 0.01
        AND receipt_to_invoice_difference <= 0.01
        THEN 'EXACT_THREE_WAY'
      WHEN match_requirement = 'TWO_WAY'
        AND purchase_to_invoice_difference <= 0.01
        THEN 'EXACT_TWO_WAY'
      WHEN purchase_to_invoice_difference <= value_tolerance
        AND receipt_to_invoice_difference <= value_tolerance
        THEN 'WITHIN_VALUE_TOLERANCE'
      WHEN recorded_invoice_value < purchase_item_value
        AND invoice_cleared_at IS NULL
        THEN 'PARTIAL_OR_OPEN'
      ELSE 'VALUE_DIFFERENCE'
    END AS match_class
  FROM staging.document_comparison AS comparison
)
SELECT
  classified.*,
  rule.rule_id,
  rule.priority AS rule_priority,
  rule.condition_summary,
  rule.responsible_team,
  rule.decision,
  CASE
    WHEN match_class IN (
      'OUTSIDE_INVOICE_SCOPE',
      'TIMING_ITEM_RESOLVED',
      'ONE_TO_MANY_RECEIPTS',
      'ONE_TO_MANY_INVOICES',
      'MANY_TO_MANY',
      'EXACT_THREE_WAY',
      'EXACT_TWO_WAY',
      'WITHIN_VALUE_TOLERANCE'
    ) THEN 'SUPPORTED'
    ELSE 'UNRESOLVED'
  END AS final_group,
  CASE
    WHEN match_class = 'OUTSIDE_INVOICE_SCOPE' THEN 'OUTSIDE_SCOPE'
    WHEN match_class = 'TIMING_ITEM_RESOLVED' THEN 'CLEARED_WITH_TIMING_NOTE'
    WHEN match_class IN (
      'ONE_TO_MANY_RECEIPTS',
      'ONE_TO_MANY_INVOICES',
      'MANY_TO_MANY'
    ) THEN 'CLEARED_AFTER_GROUPED_REVIEW'
    WHEN match_class IN (
      'EXACT_THREE_WAY',
      'EXACT_TWO_WAY'
    ) THEN 'CLEARED_BY_RULE'
    WHEN match_class = 'WITHIN_VALUE_TOLERANCE' THEN 'CLEARED_WITH_TOLERANCE'
    ELSE 'PAYMENT_HOLD'
  END AS payment_state,
  CASE
    WHEN match_class IN (
      'OUTSIDE_INVOICE_SCOPE',
      'TIMING_ITEM_RESOLVED',
      'ONE_TO_MANY_RECEIPTS',
      'ONE_TO_MANY_INVOICES',
      'MANY_TO_MANY',
      'EXACT_THREE_WAY',
      'EXACT_TWO_WAY',
      'WITHIN_VALUE_TOLERANCE'
    ) THEN 0
    ELSE GREATEST(
      0,
      DATE_DIFF(
        'day',
        CAST(last_event_at AS DATE),
        (
          SELECT MAX(CAST(event_timestamp AS DATE))
          FROM staging.events
          WHERE event_timestamp >= TIMESTAMPTZ '2018-01-01 00:00:00+00'
            AND event_timestamp < TIMESTAMPTZ '2020-01-01 00:00:00+00'
        )
      )
    )