-- Ported from Polar custom_5302: Product Revenue Exclusions
-- Source: catalog/polar_dimensions.json
--
-- Tags any order that should NOT count toward Product Revenue (the metric
-- Brian uses for the bottom-of-funnel revenue figure). Exclusion rules
-- (mirror Polar's custom_5302 exactly):
--
-- ITAM (store = into-the-am.myshopify.com):
--   - customer_email = 'photoshoots@intotheam.com', OR
--   - any line_item.product_title contains 'Green Package Protection', OR
--   - any line_item.product_title contains 'Monthly Tee Club Subscription', OR
--   - any line_item.sku = 'AM12835'
--
-- iHR (store = iheartraves.myshopify.com):
--   - customer_email = 'photoshoots@iheartraves.com', OR
--   - any line_item.product_title contains 'Green Package Protection'
--
-- The line-item checks are pre-computed in stg_shopify__sales as
-- `has_excluded_line_item` (LOGICAL_OR — any line matches; used for line-revenue
-- exclusion) and `all_lines_excluded` (LOGICAL_AND — every line matches; used
-- here at the order level so we don't drop legitimate orders that just happened
-- to attach a Green Package Protection upsell).

{{ config(materialized='view') }}

WITH base AS (
    SELECT *
    FROM {{ ref('stg_shopify__sales') }}
)

SELECT
    *,
    CASE
        -- ITAM exclusions
        WHEN brand = 'ITAM' AND LOWER(COALESCE(customer_email, '')) = 'photoshoots@intotheam.com'
            THEN 'ITAM Exclusions'
        WHEN brand = 'ITAM' AND all_lines_excluded
            THEN 'ITAM Exclusions'
        -- iHR exclusions
        WHEN brand = 'iHR' AND LOWER(COALESCE(customer_email, '')) = 'photoshoots@iheartraves.com'
            THEN 'iHR Exclusions'
        WHEN brand = 'iHR' AND all_lines_excluded
            THEN 'iHR Exclusions'
        ELSE NULL
    END AS product_revenue_exclusion
FROM base
