Loan Transactions Extract

/*
This CTE is responsible for extracting data from the line_items table, specifically 
for transactions of type 'PAYMENT'. It's designed to retrieve crucial information 
from these payment transactions and prepare it for further processing.
*/
WITH payment_split_json_records AS (
    select
        jsonb_array_elements(line_item_relationships) field,
        valid_at as effective_at,
        external_line_item_id as payment_external_line_item_id,
        account_id
    from
        data_direct.line_items
    where
        line_item_type = 'PAYMENT'
),
/*
This CTE transforms the data extracted in the previous CTE into a format suitable 
for analysis. It categorizes the transactions into 'PAYMENT_SPLIT', calculates 
the original amount, and determines attributes related to loans and other transaction details.
*/
splits_as_transactions AS (
    select
        'PAYMENT_SPLIT' as line_item_type,
        payment_external_line_item_id,
        (psjr.field ->> 'split_amount_cents') :: int as original_amount_cents,
        (psjr.field ->> 'line_item_client_id') as transaction_id,
        (psjr.field ->> 'paid_down_line_item_client_id') as paid_down_client_id,
        (psjr.field ->> 'paid_down_line_item_type') as paid_down_type,
        (
            psjr.field ->> 'paid_down_line_item_parent_client_id'
        ) as paid_down_parent_external_line_item_id -- we can use this to map to the loan
,
        effective_at,
        account_id
    from
        payment_split_json_records psjr
),
/*
This CTE retrieves all transactions of types 'LOAN' and 'PAYMENT_REVERSAL' from the 
line_items table. It's primarily focused on loan-related transactions.
*/
loan_transactions AS (
    select
        *
    from
        data_direct.line_items
    where
        line_item_type IN ('LOAN', 'PAYMENT_REVERSAL')
),
/*
The aggregate_transactions CTE combines the data from the previous CTEs, 
creating a unified view of various transaction attributes. 
*/
aggregate_transactions AS (
    select
        -- this is the Canopy 'Account' entity that every transaction belongs to
        account_id AS customer_account_id -- this is the original transaction line item id that can be used to retrieve it from the api
,
        transaction_id -- the type of the transaction as tracked Line Item entity
,
        line_item_type TRANSACTION_TYPE -- for items that can be linked back to the original MCA, this will be the MCA transaction id, other will be left blank, as it is not tied to the MCA but the Canopy Account entity that the MCA belongs to.
,
        CASE
            paid_down_type
            WHEN 'MANUAL_FEE' THEN NULL
            ELSE COALESCE(
                paid_down_parent_external_line_item_id,
                paid_down_client_id
            )
        END LOAN_ID -- when the transaction became effective
,
        effective_at :: date CREATED_AT -- For the TRANSACTION_TYPE LOAN, the original draw amount
        -- For the TRANSACTION_TYPE PAYMENT_SPLIT, the amount allocated to the paid down type
,
        original_amount_cents AMOUNT,
        paid_down_type AS TYPE -- The status of the line item in the Canopy system
,
        'SPLIT_VALID' STATUS
    FROM
        splits_as_transactions sat
    UNION
    SELECT
        account_id AS customer_account_id,
        external_line_item_id transaction_id,
        line_item_type TRANSACTION_TYPE,
        CASE
            line_item_type
            WHEN 'LOAN' THEN external_line_item_id
        END LOAN_ID,
        effective_at :: date as CREATED_AT,
        original_amount_cents AMOUNT,
        CASE
            line_item_type
            WHEN 'LOAN' THEN 'MCA'
        END AS TYPE,
        line_item_status STATUS
    FROM
        loan_transactions
)
SELECT
    *
FROM
    aggregate_transactions;