Fact Chonks

with orders as (
    select * from {{ ref('stg_ecommerce__orders') }}
),

order_items as (
    select * from {{ ref('stg_ecommerce__order_items') }}
),

order_items_agg as (
    select
        order_id,
        count(*) as item_count,
        sum(quantity) as total_quantity,
        sum(unit_price * quantity) as gross_amount
    from order_items
    group by 1
),

final as (
    select
        -- keys
        orders.order_id,
        orders.customer_id,
        
        -- dimensions
        orders.order_date,
        orders.order_status,
        
        -- measures
        order_items_agg.item_count,
        order_items_agg.total_quantity,
        order_items_agg.gross_amount,
        orders.discount_amount,
        orders.order_total as net_amount,
        
        -- flags
        orders.discount_amount > 0 as has_discount,
        orders.order_status = 'completed' as is_completed,
        
        -- timestamps
        orders.created_at,
        orders.updated_at

    from orders
    left join order_items_agg using (order_id)
)

select * from final