pip install duckdb pandas

#!/usr/bin/env python3
"""
DuckDB Automated Daily Report System v1.0
Usage: Schedule with cron for daily execution
"""

import duckdb
import pandas as pd
import json
import smtplib
import os
import sys
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime, timedelta
from pathlib import Path

# ============================================================
# CONFIGURATION — Edit these values only
# ============================================================

DB_PATH = "daily_report.duckdb"          # DuckDB database file
DATA_DIR = "data"                         # CSV data directory

SMTP_CONFIG = {
    "host": "smtp.gmail.com",
    "port": 465,
    "user": "[email protected]",
    "password": "your_app_password",     # Use an app-specific password
}

RECIPIENTS = ["[email protected]"]

# ============================================================
# Step 1: Data Loading — Incremental Append to DuckDB
# ============================================================

def load_data(con: duckdb.DuckDBPyConnection):
    """Scan data/ directory for CSV files, incrementally append to DuckDB"""
    con.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            order_id       VARCHAR PRIMARY KEY,
            order_date     DATE,
            store          VARCHAR,
            category       VARCHAR,
            product        VARCHAR,
            quantity       INTEGER,
            unit_price     DOUBLE,
            total_amount   DOUBLE,
            cost           DOUBLE,
            channel        VARCHAR
        )
    """)

    data_dir = Path(DATA_DIR)
    if not data_dir.exists():
        data_dir.mkdir()
        print(f"[INFO] Created data directory: {DATA_DIR}")
        return 0

    csv_files = list(data_dir.glob("*.csv"))
    if not csv_files:
        print("[INFO] No new CSV files found, using existing data")
        return 0

    loaded = 0
    for f in csv_files:
        try:
            con.execute(f"""
                INSERT OR IGNORE INTO orders
                SELECT * FROM read_csv_auto('{f}')
            """)
            loaded += 1
            backup_dir = data_dir / "processed"
            backup_dir.mkdir(exist_ok=True)
            f.rename(backup_dir / f.name)
            print(f"[OK] Loaded: {f.name}")
        except Exception as e:
            print(f"[WARN] Error processing {f.name}: {e}")

    print(f"[INFO] Loaded {loaded} new file(s)")
    return loaded

# ============================================================
# Step 2: Core Analysis — Single SQL Computes All KPIs
# ============================================================

def analyze(con: duckdb.DuckDBPyConnection) -> dict:
    """Run multi-dimensional analysis, return JSON-serializable KPIs"""
    
    base = con.execute("""
        SELECT
            count(*) AS total_orders,
            sum(total_amount) AS total_revenue,
            sum(cost) AS total_cost,
            sum(total_amount - cost) AS total_profit,
            round(avg(total_amount), 2) AS avg_order_value,
            round(
                (sum(total_amount - cost) / NULLIF(sum(total_amount), 0)) * 100,
                2
            ) AS profit_margin_pct
        FROM orders
        WHERE order_date = CURRENT_DATE - INTERVAL '1 day'
    """).fetchdf().iloc[0].to_dict()

    # Week-over-week comparison
    wow = con.execute("""
        SELECT
            round(
                (sum(CASE WHEN order_date = CURRENT_DATE - INTERVAL '1 day'
                     THEN total_amount ELSE 0 END)
                 -
                 sum(CASE WHEN order_date = CURRENT_DATE - INTERVAL '8 days'
                     THEN total_amount ELSE 0 END)
                ) / NULLIF(sum(CASE WHEN order_date = CURRENT_DATE - INTERVAL '8 days'
                     THEN total_amount ELSE 0 END), 0) * 100,
                2
            ) AS revenue_wow_pct,
            round(
                (count(CASE WHEN order_date = CURRENT_DATE - INTERVAL '1 day'
                     THEN 1 END)
                 -
                 count(CASE WHEN order_date = CURRENT_DATE - INTERVAL '8 days'
                     THEN 1 END)
                ) / NULLIF(count(CASE WHEN order_date = CURRENT_DATE - INTERVAL '8 days'
                     THEN 1 END), 0) * 100,
                2
            ) AS orders_wow_pct
        FROM orders
        WHERE order_date IN (
            CURRENT_DATE - INTERVAL '1 day',
            CURRENT_DATE - INTERVAL '8 days'
        )
    """).fetchdf().iloc[0].to_dict()

    trend = con.execute("""
        SELECT
            order_date,
            count(*) AS orders,
            round(sum(total_amount), 2) AS revenue
        FROM orders
        WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
          AND order_date < CURRENT_DATE
        GROUP BY order_date
        ORDER BY order_date
    """).fetchdf().to_dict(orient="records")

    category_rank = con.execute("""
        SELECT
            category,
            count(*) AS orders,
            round(sum(total_amount), 2) AS revenue,
            round(sum(total_amount - cost), 2) AS profit,
            round(
                (sum(total_amount - cost) / NULLIF(sum(total_amount), 0)) * 100,
                2
            ) AS margin_pct
        FROM orders
        WHERE order_date = CURRENT_DATE - INTERVAL '1 day'
        GROUP BY category
        ORDER BY revenue DESC
    """).fetchdf().to_dict(orient="records")

    top_products = con.execute("""
        SELECT
            product,
            count(*) AS orders,
            round(sum(total_amount), 2) AS revenue,
            round(sum(quantity), 0) AS total_qty
        FROM orders
        WHERE order_date = CURRENT_DATE - INTERVAL '1 day'
        GROUP BY product
        ORDER BY revenue DESC
        LIMIT 10
    """).fetchdf().to_dict(orient="records")

    channel_dist = con.execute("""
        SELECT
            channel,
            count(*) AS orders,
            round(sum(total_amount), 2) AS revenue,
            round(
                sum(total_amount) / NULLIF(sum(sum(total_amount)) OVER (), 0) * 100,
                2
            ) AS pct
        FROM orders
        WHERE order_date = CURRENT_DATE - INTERVAL '1 day'
        GROUP BY channel
        ORDER BY revenue DESC
    """).fetchdf().to_dict(orient="records")

    return {
        "date": (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d"),
        "base": base,
        "wow": wow,
        "trend": trend,
        "category_rank": category_rank,
        "top_products": top_products,
        "channel_dist": channel_dist,
    }

# ============================================================
# Step 3: HTML Report Generation with Charts
# ============================================================

def generate_html(kpi: dict) -> str:
    """Generate a dark-themed HTML report with Chart.js visualizations"""
    b = kpi["base"]
    w = kpi["wow"]

    return f"""<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Daily Report - {kpi["date"]}</title>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<style>
* {{ margin: 0; padding: 0; box-sizing: border-box; }}
body {{ font-family: -apple-system, 'Segoe UI', Roboto, sans-serif; background: #0f172a; color: #e2e8f0; padding: 20px; }}
.container {{ max-width: 1200px; margin: 0 auto; }}
h1 {{ font-size: 1.5rem; color: #f8fafc; margin-bottom: 8px; }}
.date {{ color: #94a3b8; font-size: 0.9rem; margin-bottom: 24px; }}
.kpi-grid {{ display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 16px; margin-bottom: 30px; }}
.kpi-card {{ background: #1e293b; border-radius: 12px; padding: 20px; border: 1px solid #334155; }}
.kpi-card .label {{ color: #94a3b8; font-size: 0.85rem; margin-bottom: 4px; }}
.kpi-card .value {{ font-size: 1.8rem; font-weight: 700; color: #f8fafc; }}
.kpi-card .change {{ font-size: 0.85rem; margin-top: 4px; }}
.up {{ color: #22c55e; }} .down {{ color: #ef4444; }}
.section {{ margin-bottom: 30px; }}
h2 {{ font-size: 1.2rem; color: #f1f5f9; margin-bottom: 16px; border-left: 3px solid #3b82f6; padding-left: 12px; }}
.chart-container {{ background: #1e293b; border-radius: 12px; padding: 20px; border: 1px solid #334155; }}
table {{ width: 100%; border-collapse: collapse; }}
th {{ text-align: left; padding: 12px 8px; color: #94a3b8; font-weight: 500; font-size: 0.85rem; border-bottom: 1px solid #334155; }}
td {{ padding: 10px 8px; border-bottom: 1px solid #1e293b; }}
tr:hover td {{ background: #1e293b; }}
.text-right {{ text-align: right; }}
</style>
</head>
<body>
<div class="container">
<h1>📊 Daily Business Report</h1>
<p class="date">{kpi["date"]} | Auto-generated</p>

<div class="kpi-grid">
<div class="kpi-card">
<div class="label">Total Revenue</div>
<div class="value">${b["total_revenue"]:,.0f}</div>
<div class="change {'up' if w.get('revenue_wow_pct', 0) >= 0 else 'down'}">
WoW: {w.get('revenue_wow_pct', 0):+.2f}%
</div>
</div>
<div class="kpi-card">
<div class="label">Total Profit</div>
<div class="value">${b["total_profit"]:,.0f}</div>
<div class="change">Margin: {b.get('profit_margin_pct', 0):.1f}%</div>
</div>
<div class="kpi-card">
<div class="label">Orders</div>
<div class="value">{b["total_orders"]:,.0f}</div>
<div class="change {'up' if w.get('orders_wow_pct', 0) >= 0 else 'down'}">
WoW: {w.get('orders_wow_pct', 0):+.2f}%
</div>
</div>
<div class="kpi-card">
<div class="label">Avg Order Value</div>
<div class="value">${b.get('avg_order_value', 0):,.2f}</div>
</div>
</div>

<div class="section">
<h2>📈 7-Day Trend</h2>
<div class="chart-container">
<canvas id="trendChart" height="100"></canvas>
</div>
</div>

<div class="section" style="display: grid; grid-template-columns: 1fr 1fr; gap: 20px;">
<div>
<h2>📂 Category Ranking</h2>
<div class="chart-container">
<table>
<tr><th>Category</th><th class="text-right">Orders</th><th class="text-right">Revenue</th><th class="text-right">Margin</th></tr>
{''.join(f'<tr><td>{r["category"]}</td><td class="text-right">{r["orders"]}</td><td class="text-right">${r["revenue"]:,.0f}</td><td class="text-right">{r["margin_pct"]}%</td></tr>' for r in kpi["category_rank"])}
</table>
</div>
</div>
<div>
<h2>🏆 Top 10 Products</h2>
<div class="chart-container">
<table>
<tr><th>Product</th><th class="text-right">Sold</th><th class="text-right">Revenue</th></tr>
{''.join(f'<tr><td>{r["product"]}</td><td class="text-right">{r["total_qty"]:.0f}</td><td class="text-right">${r["revenue"]:,.0f}</td></tr>' for r in kpi["top_products"])}
</table>
</div>
</div>
</div>

<div class="section">
<h2>📡 Channel Distribution</h2>
<div class="chart-container">
<canvas id="channelChart" height="80"></canvas>
</div>
</div>
</div>

<script>
new Chart(document.getElementById('trendChart'), {{
type: 'line',
data: {{
labels: {json.dumps([d['order_date'] for d in kpi['trend']])},
datasets: [{{
label: 'Revenue ($)',
data: {json.dumps([d['revenue'] for d in kpi['trend']])},
borderColor: '#3b82f6',
backgroundColor: 'rgba(59,130,246,0.1)',
fill: true,
tension: 0.3,
}}, {{
label: 'Orders',
data: {json.dumps([d['orders'] for d in kpi['trend']])},
borderColor: '#22c55e',
backgroundColor: 'rgba(34,197,94,0.1)',
fill: true,
tension: 0.3,
yAxisID: 'y1',
}}],
}},
options: {{
responsive: true,
plugins: {{ legend: {{ labels: {{ color: '#94a3b8' }} }} }},
scales: {{
x: {{ ticks: {{ color: '#94a3b8' }} }},
y: {{ ticks: {{ color: '#94a3b8' }} }},
y1: {{ position: 'right', ticks: {{ color: '#94a3b8' }} }},
}},
}},
}});

new Chart(document.getElementById('channelChart'), {{
type: 'doughnut',
data: {{
labels: {json.dumps([d['channel'] for d in kpi['channel_dist']])},
datasets: [{{
data: {json.dumps([d['revenue'] for d in kpi['channel_dist']])},
backgroundColor: ['#3b82f6', '#22c55e', '#f59e0b', '#ef4444', '#8b5cf6'],
}}],
}},
options: {{
plugins: {{ legend: {{ labels: {{ color: '#94a3b8' }} }} }},
}},
}});
</script>
</body>
</html>"""

# ============================================================
# Step 4: Email Delivery
# ============================================================

def send_email(html_content: str, report_date: str, recipients: list):
    """Send HTML email via SMTP"""
    msg = MIMEMultipart("alternative")
    msg["Subject"] = f"📊 Daily Business Report - {report_date}"
    msg["From"] = SMTP_CONFIG["user"]
    msg["To"] = ", ".join(recipients)
    msg.attach(MIMEText(html_content, "html", "utf-8"))

    with smtplib.SMTP_SSL(SMTP_CONFIG["host"], SMTP_CONFIG["port"]) as server:
        server.login(SMTP_CONFIG["user"], SMTP_CONFIG["password"])
        server.sendmail(SMTP_CONFIG["user"], recipients, msg.as_string())

    print(f"[OK] Email sent to {len(recipients)} recipient(s)")

# ============================================================
# Main Flow
# ============================================================

def main():
    print("=" * 50)
    print(f"DuckDB Daily Report | {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
    print("=" * 50)

    con = duckdb.connect(DB_PATH)

    try:
        load_data(con)

        print("[INFO] Running multi-dimensional analysis...")
        kpi = analyze(con)

        if kpi["base"]["total_orders"] == 0:
            print("[WARN] No orders from yesterday, skipping report generation")
            return

        print("[INFO] Generating HTML report...")
        html = generate_html(kpi)

        send_email(html, kpi["date"], RECIPIENTS)

        b = kpi["base"]
        print(f"\n📊 {kpi['date']} Summary:")
        print(f"   Revenue: ${b['total_revenue']:,.0f} | Profit: ${b['total_profit']:,.0f}")
        print(f"   Orders: {b['total_orders']} | AOV: ${b.get('avg_order_value', 0):,.2f}")
        print(f"   Margin: {b.get('profit_margin_pct', 0):.1f}%")

    finally:
        con.close()

    print("\n✅ Report generation complete")

if __name__ == "__main__":
    main()

#!/usr/bin/env python3
"""Generate mock order data for testing"""
import csv
import random
from datetime import datetime, timedelta

random.seed(42)
stores = ["Downtown", "Mall", "University", "Airport"]
categories = ["Beverages", "Mains", "Snacks", "Desserts", "Combos"]
products = {
    "Beverages": ["Signature Milk Tea", "Americano", "Fresh Juice", "Lemon Tea"],
    "Mains": ["Beef Noodles", "BBQ Rice", "Sandwich", "Pasta"],
    "Snacks": ["French Fries", "Chicken Wings", "Spring Rolls", "Onion Rings"],
    "Desserts": ["Tiramisu", "Mango Pancake", "Pudding", "Ice Cream"],
    "Combos": ["Lunch A", "Lunch B", "Afternoon Tea", "Family Meal"],
}
channels = ["Dine-in", "Delivery", "App", "Group Buy"]

with open("data/orders_2026-05-18.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["order_id", "order_date", "store", "category", "product",
                 "quantity", "unit_price", "total_amount", "cost", "channel"])
    for i in range(200):
        cat = random.choice(categories)
        prod = random.choice(products[cat])
        qty = random.randint(1, 5)
        price = round(random.uniform(15, 68), 2)
        cost = round(price * random.uniform(0.4, 0.7), 2)
        w.writerow([
            f"ORD{20260518}{i:04d}",
            "2026-05-18",
            random.choice(stores),
            cat,
            prod,
            qty,
            price,
            round(qty * price, 2),
            round(qty * cost, 2),
            random.choice(channels),
        ])

print("✅ Generated data/orders_2026-05-18.csv (200 mock orders)")

# Generate yesterday's report every morning at 9:00 AM
0 9 * * * cd /opt/daily-report && /usr/bin/python3 daily_report.py >> report.log 2>&1

# Optional: afternoon alert if order volume drops below threshold
0 18 * * * cd /opt/daily-report && /usr/bin/python3 daily_report.py --alert-only >> alert.log 2>&1