version: 2

models:
  - name: INT_SHOPIFY__EMAIL_ORDER_FACTS_DBT
    description: >
      Row-level facts joining email-attributed Shopify orders with currency
      conversion and shop metadata. Only orders with a matching email UUID are
      included (INNER JOIN on EMAIL_IDS). One row per (SOURCE_SCHEMA, ORDER_ID) —
      single-touch attribution is enforced via QUALIFY on
      source(fansifter_shopify, shopify_order_url_tag).
      Source data is read directly from the dbt-fansifter-shopify marts.
    columns:
      - name: EMAIL_ID
        description: Email UUID (campaign or automated).
      - name: ORDER_ID
        description: Shopify order ID.
      - name: SOURCE_SCHEMA
        description: Schema identifier for the source Shopify store.
      - name: SHOP_ID
        description: Shopify shop ID from STG_SHOPIFY__SHOP_DBT.
      - name: SHOP_NAME
        description: Display name of the Shopify store.
      - name: CURRENCY
        description: ISO currency code of the order.
      - name: COUNTRY_CODE
        description: Shipping address country code from the source order.
      - name: TOTAL_NET_SALES
        description: >
          Net sales after refunds in original currency.
      - name: TOTAL_REFUNDS
        description: Sum of refunded line item subtotals (pre-tax) for this order.
      - name: TOTAL_DISCOUNTS
        description: Total discounts applied to the order in original currency.
      - name: TOTAL_TAXES
        description: Total taxes on the order in original currency.
      - name: TOTAL_DELIVERY
        description: Total shipping / delivery cost in original currency.
      - name: TOTAL_PRICE
        description: Order grand total in the original currency.
      - name: ITEMS_SOLD
        description: Number of sold items for the order. Coalesced to 0 when no order lines found.
      - name: TOTAL_NET_SALES_USD
        description: >
          TOTAL_NET_SALES converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 (assumes USD) when no rate is found.
      - name: TOTAL_REFUNDS_USD
        description: >
          TOTAL_REFUNDS converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 when no rate is found.
      - name: TOTAL_DISCOUNTS_USD
        description: >
          TOTAL_DISCOUNTS converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 when no rate is found.
      - name: TOTAL_TAXES_USD
        description: >
          TOTAL_TAXES converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 when no rate is found.
      - name: TOTAL_DELIVERY_USD
        description: >
          TOTAL_DELIVERY converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 when no rate is found.
      - name: TOTAL_PRICE_USD
        description: >
          TOTAL_PRICE converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 (assumes USD) when no rate is found.
      - name: _FIVETRAN_SYNCED
        description: Fivetran sync timestamp from the source order row.
      - name: IS_FIRST_TIME_BUYER
        description: >
          1 if this is the first order ever placed by this customer email
          (globally across all shops, ordered by PROCESSED_AT). 0 otherwise.

  - name: INT_SHOPIFY__EMAIL_PRODUCT_FACTS_DBT
    description: >
      Row-level product facts joining email-attributed Shopify orders with
      order line product details and currency conversion. One row per
      (EMAIL_ID, ORDER_LINE_ID) pair. Only orders with a matching email UUID
      are included (INNER JOIN on EMAIL_IDS). Source data is read directly
      from the dbt-fansifter-shopify marts.
    columns:
      - name: EMAIL_ID
        description: Email UUID (campaign or automated).
      - name: ORDER_ID
        description: Shopify order ID.
      - name: ORDER_LINE_ID
        description: Shopify order line ID.
      - name: PRODUCT_ID
        description: Shopify product ID.
      - name: PRODUCT_TITLE
        description: Product title from the order line.
      - name: PRODUCT_TYPE
        description: Product type from the product table. NULL when not set.
      - name: QUANTITY
        description: Quantity sold on this line item.
      - name: NET_LINE_AMOUNT
        description: Net sales for the line in original currency — PRICE * QUANTITY - TOTAL_DISCOUNT.
      - name: NET_LINE_AMOUNT_USD
        description: >
          Net sales converted to USD using DIM_CURRENCY.CONVERSION_RATE.
          Falls back to 1.0 when no rate is found.
      - name: CURRENCY
        description: ISO currency code of the order.
      - name: _FIVETRAN_SYNCED
        description: Fivetran sync timestamp from the source order row.