-- dbt_project/dbt_project.yml equivalent
-- ==============================================================================
-- dbt Models for Olist E-commerce Data Warehouse
-- CLO3: dbt transforms (Silver → Gold)
-- ==============================================================================

-- ============================================================
-- STAGING MODELS (Silver Layer)
-- ============================================================

-- models/staging/stg_orders.sql
-- {{ config(materialized='view', tags=['staging']) }}
CREATE OR REPLACE VIEW stg_orders AS
SELECT
    order_id,
    customer_id,
    LOWER(TRIM(order_status)) AS order_status,
    order_purchase_timestamp::TIMESTAMP AS purchased_at,
    order_approved_at::TIMESTAMP AS approved_at,
    order_delivered_carrier_date::TIMESTAMP AS shipped_at,
    order_delivered_customer_date::TIMESTAMP AS delivered_at,
    order_estimated_delivery_date::TIMESTAMP AS estimated_delivery_at,
    -- Derived
    EXTRACT(EPOCH FROM (delivered_at - purchased_at)) / 86400 AS delivery_days,
    EXTRACT(EPOCH FROM (delivered_at - estimated_delivery_at)) / 86400 AS delivery_delay_days
FROM raw_orders  -- Bronze source
WHERE order_id IS NOT NULL
  AND order_purchase_timestamp IS NOT NULL;


-- models/staging/stg_order_items.sql
CREATE OR REPLACE VIEW stg_order_items AS
SELECT
    order_id,
    order_item_id,
    product_id,
    seller_id,
    shipping_limit_date::TIMESTAMP AS shipping_limit_at,
    GREATEST(price, 0) AS price,
    GREATEST(freight_value, 0) AS freight_value,
    price + freight_value AS total_value
FROM raw_order_items
WHERE order_id IS NOT NULL
  AND price >= 0;


-- models/staging/stg_customers.sql
CREATE OR REPLACE VIEW stg_customers AS
SELECT
    customer_id,
    customer_unique_id,
    customer_zip_code_prefix AS zip_code,
    INITCAP(TRIM(customer_city)) AS city,
    UPPER(TRIM(customer_state)) AS state
FROM raw_customers
WHERE customer_id IS NOT NULL;


-- models/staging/stg_payments.sql
CREATE OR REPLACE VIEW stg_payments AS
SELECT
    order_id,
    payment_sequential,
    LOWER(TRIM(payment_type)) AS payment_type,
    payment_installments,
    GREATEST(payment_value, 0) AS payment_value
FROM raw_payments
WHERE order_id IS NOT NULL;


-- models/staging/stg_reviews.sql
CREATE OR REPLACE VIEW stg_reviews AS
SELECT
    review_id,
    order_id,
    review_score,
    review_comment_title,
    review_comment_message,
    LENGTH(COALESCE(review_comment_message, '')) > 0 AS has_comment,
    LENGTH(COALESCE(review_comment_message, '')) AS comment_length,
    review_creation_date::TIMESTAMP AS created_at,
    review_answer_timestamp::TIMESTAMP AS answered_at
FROM raw_reviews
WHERE review_id IS NOT NULL;


-- ============================================================
-- INTERMEDIATE MODELS
-- ============================================================

-- models/intermediate/int_order_enriched.sql
-- {{ config(materialized='table', tags=['intermediate']) }}
CREATE OR REPLACE VIEW int_order_enriched AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_status,
    o.purchased_at,
    o.delivered_at,
    o.delivery_days,
    o.delivery_delay_days,
    o.delivery_delay_days > 0 AS is_late_delivery,
    -- Item aggregates
    COALESCE(i.item_count, 0) AS item_count,
    COALESCE(i.total_price, 0) AS total_price,
    COALESCE(i.total_freight, 0) AS total_freight,
    COALESCE(i.total_price, 0) + COALESCE(i.total_freight, 0) AS gmv,
    -- Payment aggregates
    COALESCE(p.total_payment, 0) AS total_payment,
    p.primary_payment_type,
    p.max_installments,
    -- Review
    r.review_score,
    r.has_comment AS review_has_text
FROM stg_orders o
LEFT JOIN (
    SELECT order_id,
           COUNT(*) AS item_count,
           SUM(price) AS total_price,
           SUM(freight_value) AS total_freight
    FROM stg_order_items
    GROUP BY order_id
) i ON o.order_id = i.order_id
LEFT JOIN (
    SELECT order_id,
           SUM(payment_value) AS total_payment,
           MODE() WITHIN GROUP (ORDER BY payment_type) AS primary_payment_type,
           MAX(payment_installments) AS max_installments
    FROM stg_payments
    GROUP BY order_id
) p ON o.order_id = p.order_id
LEFT JOIN (
    SELECT DISTINCT ON (order_id)
           order_id, review_score, has_comment
    FROM stg_reviews
    ORDER BY order_id, created_at DESC
) r ON o.order_id = r.order_id;


-- ============================================================
-- MART MODELS (Gold Layer)
-- ============================================================

-- models/marts/fact_orders.sql
-- {{ config(materialized='table', tags=['mart', 'fact']) }}
-- This creates the final fact_orders from int_order_enriched
-- See init-scripts/postgres/01_init_dw.sql for full DDL

-- models/marts/agg_daily_revenue.sql
-- {{ config(materialized='table', tags=['mart', 'aggregate']) }}
CREATE OR REPLACE VIEW v_agg_daily_revenue AS
SELECT
    DATE(purchased_at) AS date,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(total_price) AS total_revenue,
    SUM(total_freight) AS total_freight,
    SUM(gmv) AS gmv,
    AVG(gmv) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM int_order_enriched
WHERE order_status = 'delivered'
GROUP BY DATE(purchased_at)
ORDER BY date;


-- ============================================================
-- dbt TESTS (Data Quality)
-- ============================================================

-- tests/assert_orders_not_null.sql
-- SELECT * FROM stg_orders WHERE order_id IS NULL;
-- (Should return 0 rows)

-- tests/assert_positive_prices.sql
-- SELECT * FROM stg_order_items WHERE price < 0 OR freight_value < 0;

-- tests/assert_valid_status.sql
-- SELECT * FROM stg_orders 
-- WHERE order_status NOT IN ('delivered', 'shipped', 'canceled', 'processing', 
--                             'unavailable', 'invoiced', 'created', 'approved');

-- tests/assert_referential_integrity.sql
-- SELECT oi.order_id FROM stg_order_items oi
-- LEFT JOIN stg_orders o ON oi.order_id = o.order_id
-- WHERE o.order_id IS NULL;

-- tests/assert_unique_order_id.sql
-- SELECT order_id, COUNT(*) FROM stg_orders 
-- GROUP BY order_id HAVING COUNT(*) > 1;
