name: 'northwind_simple'   # changed from 'northwind'
version: '1.0.0'

profile: 'northwind_clickhouse'  # same profile — same DB connection

models:
  northwind_simple:
    staging:
      +materialized: view
    marts:
      +materialized: table

-- Thin wrapper: rename PascalCase columns to snake_case
SELECT
    OrderID     AS order_id,
    CustomerID  AS customer_id,
    toDate(OrderDate) AS order_date,
    Freight     AS freight
FROM {{ source('northwind_oltp', 'Orders') }}

SELECT
    CustomerID   AS customer_id,
    CompanyName  AS company_name,
    Country      AS country
FROM {{ source('northwind_oltp', 'Customers') }}

-- One row per customer, surrogate key
SELECT
    row_number() OVER (ORDER BY customer_id) AS customer_key,
    customer_id,
    company_name,
    country
FROM {{ ref('stg_customers') }}

-- One row per ORDER (not per line item — much simpler)
SELECT
    row_number() OVER (ORDER BY o.order_date, o.order_id) AS order_key,
    o.order_date,
    coalesce(dc.customer_key, 0) AS customer_key,
    toDecimal64(o.freight, 2) AS freight
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('dim_customer') }} dc
    ON o.customer_id = dc.customer_id
WHERE o.order_date IS NOT NULL

-- Revenue summary per customer — ready for dashboards
SELECT
    dc.company_name,
    dc.country,
    count(*)                        AS total_orders,
    round(sum(f.freight), 2)        AS total_freight,
    round(avg(f.freight), 2)        AS avg_freight
FROM {{ ref('fact_order_summary') }} f
LEFT JOIN {{ ref('dim_customer') }} dc
    ON f.customer_key = dc.customer_key
GROUP BY dc.company_name, dc.country
ORDER BY total_freight DESC