Recharge Models

select
    id::varchar as affiliate_id --pk
  , NULLIF(TRIM(name), '') as affiliate_name
  , LOWER(TRIM(first_name)) as first_name
  , LOWER(TRIM(last_name)) as last_name
  , LOWER(TRIM(email)) as email
  , LOWER(TRIM(ref_code)) as ref_code
  , LOWER(TRIM(status)) as status
  , group_id::varchar as group_id
  , tags as tags_json
  -- contact / social
  , NULLIF(TRIM(website), '') as website
  , LOWER(TRIM(instagram)) as instagram
  , LOWER(TRIM(state)) as state
  , LOWER(TRIM(country)) as country
  , LOWER(TRIM(payment_method)) as payment_method
  -- GoAffPro-reported lifetime totals (pass-through; recompute from orders where you need auditable numbers)
  , number_of_orders as goaffpro_number_of_orders
  , total_referral_earnings as goaffpro_total_referral_earnings
  , total_amount_paid as goaffpro_total_amount_paid
  , total_amount_pending as goaffpro_total_amount_pending
  , subtotal_revenue as goaffpro_subtotal_revenue
  -- EXAMPLE ONLY: account-specific custom fields — rename/replace or drop
  , NULLIF(TRIM(extra_1), '') as extra_1
  , NULLIF(TRIM(extra_2), '') as extra_2
  , NULLIF(TRIM(extra_3), '') as extra_3
  -- timestamps arrive as strings — cast, leave in UTC. NULLIF guards empty strings.
  , NULLIF(TRIM(approved_at), '')::timestamptz as approved_at
  , NULLIF(TRIM(blocked_at), '')::timestamptz as blocked_at
  , NULLIF(TRIM(last_login), '')::timestamptz as last_login_at
  , __updated_at as __updated_at -- EXAMPLE ONLY: your sync tool's last-synced timestamp
from {{sources.goaffpro.affiliates}}

select
    id as subscription_id -- PK
  , customer_id
  , address_id
  , TRIM(status) as status
  , TRIM(product_title) as product_title
  , TRIM(variant_title) as variant_title
  , NULLIF(TRIM(price), '')::numeric as price
  , quantity
  , order_interval_frequency
  , TRIM(order_interval_unit) as order_interval_unit
  , charge_interval_frequency
  , next_charge_scheduled_at
  , TRIM(cancellation_reason) as cancellation_reason
  , TRIM(cancellation_reason_comments) as cancellation_reason_comments
  , cancelled_at
  , created_at
  , updated_at
from {{sources.recharge.subscription}}

select
    id as charge_id -- PK
  , customer_id
  , address_id
  , TRIM(status) as status
  , TRIM(type) as type
  , total_price
  , subtotal_price
  , total_line_items_price
  , total_discounts
  , total_tax
  , total_refunds
  , orders_count
  , TRIM(payment_processor) as payment_processor
  , TRIM(error_type) as error_type
  , TRIM(external_order_id_ecommerce) as external_order_id
  , scheduled_at
  , processed_at
  , created_at
  , updated_at
from {{sources.recharge.charge}}

select
    id as order_id -- PK
  , customer_id
  , charge_id
  , address_id
  , TRIM(status) as status
  , TRIM(type) as type
  , NULLIF(TRIM(total_price), '')::numeric as total_price
  , is_prepaid
  , TRIM(external_order_id_ecommerce) as external_order_id
  , TRIM(external_order_number_ecommerce) as external_order_number
  , scheduled_at
  , processed_at
  , shipped_date
  , created_at
  , updated_at
from {{sources.recharge.orders}}

select
    order_id as order_id -- PK (composite w/ line_item_index)
  , index as line_item_index -- PK (composite w/ order_id)
  , purchase_item_id
  , TRIM(purchase_item_type) as purchase_item_type
  , TRIM(title) as title
  , TRIM(product_title) as product_title
  , TRIM(variant_title) as variant_title
  , quantity
  , NULLIF(TRIM(unit_price), '')::numeric as unit_price
  , NULLIF(TRIM(total_price), '')::numeric as total_price
  , TRIM(external_product_id_ecommerce) as external_product_id
  , TRIM(external_variant_id_ecommerce) as external_variant_id
from {{sources.recharge.order_line_item}}

select
    c.customer_id as customer_id -- PK
  , c.email
  , c.first_name
  , c.last_name
  , c.first_charge_processed_at
  , MIN(s.created_at) as first_subscription_created_at
  , MAX(s.created_at) as latest_subscription_created_at
  , COUNT(s.subscription_id) as total_subscriptions
  , COUNT(s.subscription_id) FILTER (WHERE s.status = 'ACTIVE') as n_active_subscriptions
  , COUNT(s.subscription_id) FILTER (WHERE s.status = 'CANCELLED') as n_cancelled_subscriptions
from {{models.recharge_customers_base}} c
left join {{models.recharge_subscriptions_base}} s on c.customer_id = s.customer_id
group by
    c.customer_id
  , c.email
  , c.first_name
  , c.last_name
  , c.first_charge_processed_at

select
    s.customer_id
  , sc.subcription_id
  , s.subscription_id as subscription_id -- PK
  , s.customer_id
  , c.email
  , c.first_name
  , c.last_name
  , s.status
  , s.product_title
  , s.variant_title
  , s.price
  , s.quantity
  , s.order_interval_frequency
  , s.order_interval_unit
  , s.charge_interval_frequency
  , s.next_charge_scheduled_at
  , s.cancellation_reason
  , s.cancellation_reason_comments
  , s.cancelled_at
  , s.created_at
  , s.updated_at
  , f.first_subscription_created_at
  , f.n_total_subscriptions
  , f.n_active_subscriptions
from {{models.recharge_subscriptions_base}} s
left join {{models.recharge_customers_base}} c on s.customer_id = c.customer_id
left join {{models.recharge_customer_subscription_facts}} f on s.customer_id = f.customer_id
left join {{models.recharge_subscriptions_base}} sc on sc.customer_id = s.customer_id