WITH valid_barcodes_per_item AS (
    SELECT 
        barcode.orderitemid as order_item_id,
        COUNT(CASE WHEN valid = true THEN 1 END) AS valid_count
    FROM {{ source('ticketing_service', 'barcode') }} as barcode
    GROUP BY orderitemid
),

country_default_fee AS (
    SELECT
        countryfee.countryid AS country_id,
        countryfee.percentage AS country_default_percentage,
        countryfee.type AS country_booking_fee_type
    FROM {{ source('ticketing_service', 'countryfee') }} AS countryfee
),

custom_fee_info AS (
    SELECT
        ticket_tier_fee.id AS fee_settings_id,
        ticket_tier_fee.percentage AS ticket_tier_fee_percentage,
        event.countryid AS country_id,
        country_default_fee.country_default_percentage
    FROM {{ source('ticketing_service', 'tickettierfee') }} AS ticket_tier_fee
    LEFT JOIN {{ source('ticketing_service', 'tickettier') }} AS ticket_tier
        ON ticket_tier_fee.id = ticket_tier.feesettingsid
    LEFT JOIN {{ source('ticketing_service', 'event') }} AS event
        ON ticket_tier.eventid = event.id
    LEFT JOIN country_default_fee 
        ON country_default_fee.country_id = event.countryid
        AND country_default_fee.country_booking_fee_type = ticket_tier_fee.type
),

currency_info AS (
    SELECT
        currency.id AS currency_id,
        currency.minfee AS min_fee
    FROM {{ source('ticketing_service', 'currency') }} AS currency
)

SELECT
    event.legacyid AS event_id,
    ticket_tier.title AS ticket_tier_title,
    order_item.tickettierid AS ticket_tier_id,
    (order_item.retailamount - order_item.feeamount) AS ticket_revenue,
    order_item.retailamount AS retail_amount,
    order_table.completedat AS completed_at,
    COALESCE(valid_barcodes_per_item.valid_count, 0) AS valid_barcode_count,
    CASE 
        WHEN custom_fee_info.ticket_tier_fee_percentage != custom_fee_info.country_default_percentage THEN 
            CASE 
                WHEN (order_item.retailamount - ((order_item.retailamount) * (1 - (custom_fee_info.country_default_percentage / 100)))) < currency_info.min_fee 
                THEN currency_info.min_fee - order_item.feeamount
                ELSE (order_item.retailamount - ((order_item.retailamount) * (1 - (custom_fee_info.country_default_percentage / 100)))) - order_item.feeamount
            END
        ELSE 0
    END AS discount_due_to_custom_fee
FROM
    {{ source('ticketing_service', 'orderitem') }} AS order_item
JOIN
    {{ source('ticketing_service', 'tickettier') }} AS ticket_tier
    ON order_item.tickettierid = ticket_tier.id
JOIN
    {{ source('ticketing_service', 'event') }} AS event
    ON ticket_tier.eventid = event.id
JOIN
    {{ source('ticketing_service', 'order') }} AS order_table
    ON order_item.orderid = order_table.id
LEFT JOIN
    valid_barcodes_per_item
    ON order_item.id = valid_barcodes_per_item.order_item_id
LEFT JOIN
    custom_fee_info
    ON ticket_tier.feesettingsid = custom_fee_info.fee_settings_id
LEFT JOIN
    currency_info
    ON custom_fee_info.country_id = currency_info.currency_id
WHERE
    order_table.status = 'captured' 
    AND order_item.refundstatus IS NULL

I would like to make this incremental, inside the order table there is a column called completedat and I think we should use this so that we only update elements when that is updated?