Order Item

/*
    This model provides a business-ready, denormalized view of order item data
    pre-joined for easy consumption, mirroring order item API.
*/

"
    )
}}

with order_items as (
    select * from {{ ref('order_item') }}
)

, prescription_fills as (
    select * from {{ ref('prescription_fill') }}
)

, doctor as (
    select * from {{ ref('doctor') }}
)

, vendor_order_items as (
    select * from {{ ref('vendor_order_item') }}
    where is_deleted = false
)

, order_item_revenue as (
    select * from {{ ref('order_item_revenue') }}
)

, payouts as (
    select * from {{ ref('payout') }}
)

, item as (
    select * from {{ ref('item') }}
)

-- Order item revenue filtered to refund charges and aggregated
-- by order item ID
, sponsored_refund_amount as (
    select
        order_item_id
        , coalesce(sum(abs(amount)), 0) as sponsored_refund_amount
    from order_item_revenue
    where
        kind = 'REFUND_CHARGE'
        and is_deleted = false
    group by order_item_id
)

-- Order item revenue records with associated payouts aggregated
-- by order item ID
, net_payout as (
    select
        order_item_id
        , sum(amount) as net_payout
    from order_item_revenue
    where
        payout_id is not null
        and is_deleted = false
    group by order_item_id
)

-- Stripe payouts details filtered to order items
-- w/ associated payouts
, stripe_payouts_detail as (
    select
        order_item_revenue.order_item_id
        , payouts.stripe_id
        , payouts.created::date as created
    from order_item_revenue
    inner join payouts
        on order_item_revenue.payout_id = payouts.id
)

-- Stripe payouts aggregated by order item ID
, stripe_payouts_agg as (
    select
        order_item_id
        , array_agg(
            object_construct(
                'stripe_id', stripe_id
                , 'created', created
            )
        ) as stripe_payouts
    from stripe_payouts_detail
    group by order_item_id
)

-- Order item revenue calculated by order item ID
, order_item_revenue_calc as (
    select
        id
        , subtotal
        - clinic_discounts
        + handling_fee
        + amortized_urgency_fee
        + amortized_shipping
        + client_purchasing_fee
        + loyalty_program_total_earnings
        - amortized_wholesale_tax
            as revenue
    from order_items
)

, profit_calc as (
    select
        order_items.id as order_item_id
        , order_items.status
        -- Total transferrable amount
        , order_item_revenue_calc.revenue
        - (
            order_items.amortized_processing_fee
            + order_items.amortized_service_fee
            + order_items.amortized_vc_connect_fee
        )
            as total_transferrable_amount
        -- Vendor cost
        , case
            when vendor_order_items.id is not null
                then (
                    coalesce((vendor_order_items.unit_cost * vendor_order_items.quantity), 0)
                    + coalesce(vendor_order_items.fees, 0)
                    + coalesce(vendor_order_items.shipping, 0)
                    + coalesce(vendor_order_items.tax, 0)
                    + coalesce(vendor_order_items.fees_sponsored_by_vetcove, 0)
                )
            else 0
        end as vendor_cost
        , order_items.clinic_refund_debit
        -- Order item has shipping vnedor but no vendor order item
        , case
            when
                order_items.has_shipping_vendor_id = true
                and vendor_order_items.order_item_id is null
                then true
            else false
        end as vendor_fulfilled_no_voi
        -- Profit calculated by order item ID (non canceled orders only)
        , case
            when order_items.status in ('CLINIC_CANCELED', 'CLIENT_CANCELED') then 0
            when vendor_fulfilled_no_voi = true then null
            else (
                total_transferrable_amount
                - vendor_cost
                - order_items.clinic_refund_debit
            )
        end as profit
    from order_items
    left join order_item_revenue_calc
        on order_items.id = order_item_revenue_calc.id
    left join vendor_order_items
        on order_items.id = vendor_order_items.order_item_id
)

select
    order_items.id as order_item_id
    , order_items.created
    , order_items.modified
    , order_items.is_deleted
    , order_items.order_id
    , order_items.item_id
    , order_items.quantity
    , order_items.unit_price
    , order_items.handling_fee
    , order_items.status
    , order_items.is_subscription
    , order_items.is_pickup
    , order_items.patient_id
    , case
        when order_items.status in ('CLINIC_CANCELED', 'CLIENT_CANCELED') then 0
        when order_items.has_shipping_vendor_id = false then 0
        when vendor_order_items.id is null then null
        else vendor_order_items.unit_cost * vendor_order_items.quantity
    end as vendor_subtotal
    , case
        when order_items.status in ('CLINIC_CANCELED', 'CLIENT_CANCELED') then 0
        when order_items.has_shipping_vendor_id = false then 0
        when vendor_order_items.id is null then null
        else vendor_order_items.fees
    end as vendor_fees
    , case
        when order_items.status in ('CLINIC_CANCELED', 'CLIENT_CANCELED') then 0
        when order_items.has_shipping_vendor_id = false then 0
        when vendor_order_items.id is null then null
        else (
            vendor_order_items.shipping
            + vendor_order_items.amortized_clinic_withheld_shipping
        )
    end as vendor_shipping
    , order_items.subtotal as client_price
    , case
        when item.denormed_etailer_price is not null
            then item.denormed_etailer_price * order_items.quantity
        else null
    end as current_chewy_price
    , case
        when order_items.status in ('CLINIC_CANCELED', 'CLIENT_CANCELED') then 0
        else order_item_revenue_calc.revenue
    end as revenue
    , profit_calc.profit
    , case
        when
            profit_calc.profit is null
            or order_items.status in ('CLINIC_CANCELED', 'CLIENT_CANCELED')
            or order_item_revenue_calc.revenue = 0
            then null
        else
            round(
                100 * profit_calc.profit / order_item_revenue_calc.revenue
                , 2
            )
    end as margin
    , order_items.ship_date
    , order_items.amortized_tax as vc_marketplace_facilitator_tax
    , order_items.clinic_discounts
    , order_items.rebate_discounts
    , order_items.vendor_service_fee as diet_fulfillment_fee
    , order_items.refunded_amount
    , net_payout.net_payout
    , coalesce(stripe_payouts_agg.stripe_payouts, parse_json('[]')) as stripe_payouts
    , order_items.amortized_urgency_fee as urgency_fee
    , doctor.id as authorizing_vet_id
    , concat(doctor.first_name, ' ', doctor.last_name) as authorizing_vet
    , order_items.delivered_date
    , case
        when
            prescription_fills.id is not null
            and prescription_fills.status = 'ACTIVE'
            then date(prescription_fills.created)
        else null
    end as dvm_authorization_date
    , prescription_fills.prescription_id
    , order_items.amortized_processing_fee as credit_card_processing_fee
    , order_items.order_processing_fee
    , coalesce(sponsored_refund_amount.sponsored_refund_amount, 0)
        as sponsored_refund_amount
    , order_items.vendor_name
    , order_items.loyalty_program_total_earnings as loyalty_savings
    , coalesce(vendor_order_items.fees, 0)
    + order_items.amortized_vc_connect_fee as pharmacy_fulfillment_fee
    , order_items.amortized_wholesale_tax as wholesale_tax
    , order_items.amortized_shipping as shipping
    , order_items.client_purchasing_fee as client_service_fee
    , greatest(
        coalesce(order_items.last_updated, '1900-01-01')
        , coalesce(doctor.last_updated, '1900-01-01')
        , coalesce(item.last_updated, '1900-01-01')
        , coalesce(vendor_order_items.last_updated, '1900-01-01')
        , coalesce(prescription_fills.last_updated, '1900-01-01')
    ) as last_updated
    , order_items.vetcove_corporate_id
from
    order_items
left join prescription_fills
    on order_items.prescription_fill_id = prescription_fills.id
left join doctor
    on prescription_fills.prescribed_by_doctor_id = doctor.id
left join vendor_order_items
    on order_items.id = vendor_order_items.order_item_id
left join sponsored_refund_amount
    on order_items.id = sponsored_refund_amount.order_item_id
left join net_payout
    on order_items.id = net_payout.order_item_id
left join stripe_payouts_agg
    on order_items.id = stripe_payouts_agg.order_item_id
left join profit_calc
    on order_items.id = profit_calc.order_item_id
left join item
    on order_items.item_id = item.id
left join order_item_revenue_calc
    on order_items.id = order_item_revenue_calc.id