# Install dependencies
pip install duckdb dbt-duckdb pandas

# Verify installation
python -c "import duckdb; print('DuckDB version:', duckdb.__version__)"
python -c "import dbt_duckdb; print('dbt-duckdb OK')"

mkdir duckdb-dbt-project && cd duckdb-dbt-project
dbt init my_data_product
cd my_data_product

name: 'my_data_product'
version: '1.0.0'
config-version: 2

profile: 'my_data_product'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"
clean-targets:
  - "target"
  - "dbt_packages"

models:
  my_data_product:
    +materialized: view

my_data_product:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: "./data.db"
      schemas:
        - public
      settings:
        max_memory: '8GB'
        threads: 4

# seeds/orders.csv
order_id,customer_id,product_id,order_date,amount,status,shop_name,category
1001,C001,P001,2026-01-15,299.50,completed,StoreA,Electronics
1002,C002,P003,2026-01-15,89.00,completed,StoreB,Clothing
1003,C001,P005,2026-01-16,1299.00,completed,StoreA,Appliances
1004,C003,P002,2026-01-16,45.00,refunded,StoreB,Food
1005,C004,P007,2026-01-17,599.00,completed,StoreC,Electronics

# seeds/products.csv
product_id,product_name,category,shop_name,cost_price,supplier
P001,iPhone 15 Case,Electronics,StoreA,50,Shenzhen Supplier
P003,Cotton T-Shirt,Clothing,StoreB,15,Guangzhou Supplier
P005,Air Fryer,Appliances,StoreA,300,Ningbo Supplier
P002,Nut Gift Box,Food,StoreB,12,Hangzhou Supplier
P007,Bluetooth Earphones,Electronics,StoreC,200,Dongguan Supplier

{{ config(materialized='table') }}

SELECT 
    order_id,
    customer_id,
    product_id,
    CAST(order_date AS DATE) AS order_date,
    amount,
    LOWER(status) AS status,
    shop_name,
    category
FROM {{ source('seed', 'orders') }}
WHERE order_id IS NOT NULL

{{ config(materialized='table') }}

WITH orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
products AS (
    SELECT * FROM {{ source('seed', 'products') }}
)

SELECT 
    o.order_id,
    o.customer_id,
    o.order_date,
    o.amount,
    o.status,
    o.shop_name,
    o.category,
    p.product_name,
    p.cost_price,
    ROUND(o.amount - p.cost_price, 2) AS gross_profit,
    ROUND(100.0 * (o.amount - p.cost_price) / NULLIF(o.amount, 0), 1) AS profit_margin_pct
FROM orders o
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.status = 'completed'

{{ config(materialized='table') }}

SELECT 
    shop_name,
    category,
    DATE_TRUNC('month', order_date) AS sale_month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    SUM(gross_profit) AS total_profit,
    AVG(profit_margin_pct) AS avg_margin,
    COUNT(DISTINCT customer_id) AS unique_customers,
    -- Month-over-month comparison
    LAG(SUM(amount)) OVER (
        PARTITION BY shop_name, category 
        ORDER BY DATE_TRUNC('month', order_date)
    ) AS prev_month_revenue,
    ROUND(
        100.0 * (SUM(amount) - LAG(SUM(amount)) OVER (
            PARTITION BY shop_name, category 
            ORDER BY DATE_TRUNC('month', order_date)
        )) / NULLIF(LAG(SUM(amount)) OVER (
            PARTITION BY shop_name, category 
            ORDER BY DATE_TRUNC('month', order_date)
        ), 0),
        1
    ) AS mom_growth_pct
FROM {{ ref('fct_sales') }}
GROUP BY shop_name, category, DATE_TRUNC('month', order_date)
ORDER BY sale_month DESC, total_revenue DESC

version: 2

models:
  - name: fct_sales
    description: "Core sales fact table, includes only completed orders"
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: amount
        tests:
          - not_null
          - accepted_values:
              values: '>0'
      - name: gross_profit
        tests:
          - relationships:
              to: ref('stg_orders')
              field: order_id

# Compile and run all models
dbt run

# Run all tests
dbt test

# Generate documentation (can be deployed as a static website)
dbt docs generate
dbt docs serve

import duckdb
from datetime import datetime

# Connect to the DuckDB database output by dbt
con = duckdb.connect("data.db")

# Get the latest month's shop performance data
performance = con.execute("""
    SELECT * FROM dm_shop_performance 
    WHERE sale_month = (SELECT MAX(sale_month) FROM dm_shop_performance)
""").fetchdf()

# Get summary metrics
summary = con.execute("""
    SELECT 
        COUNT(DISTINCT shop_name) AS shop_count,
        COUNT(DISTINCT category) AS category_count,
        SUM(order_count) AS total_orders,
        ROUND(SUM(total_revenue), 2) AS total_revenue,
        ROUND(SUM(total_profit), 2) AS total_profit,
        ROUND(AVG(avg_margin), 1) AS avg_margin,
        ROUND(SUM(CASE WHEN mom_growth_pct > 0 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 1) AS growth_ratio
    FROM dm_shop_performance
    WHERE sale_month = (SELECT MAX(sale_month) FROM dm_shop_performance)
""").fetchone()

print("=" * 60)
print(f"📊 Monthly Sales Report — {datetime.now().strftime('%B %Y')}")
print("=" * 60)
print(f"🏪 Shop Count: {summary[0]}")
print(f"📦 Categories: {summary[1]}")
print(f"🛒 Total Orders: {summary[2]:,}")
print(f"💰 Total Revenue: ${summary[3]:,.0f}")
print(f"📈 Total Profit: ${summary[4]:,.0f}")
print(f"🎯 Avg Margin: {summary[5]}%")
print(f"🚀 Growth Shops: {summary[6]}%")
print("-" * 60)
print("\nShop Performance Rankings:")
for _, row in performance.head(10).iterrows():
    trend = "🔺" if row['mom_growth_pct'] > 0 else "🔻" if row['mom_growth_pct'] < 0 else "➡️"
    print(f"  {trend} {row['shop_name']} | {row['category']} | "
          f"Revenue ${row['total_revenue']:,.0f} | Profit ${row['total_profit']:,.0f} | "
          f"MoM {row['mom_growth_pct']:+.1f}%")