import os
os.makedirs('/tmp/duckdb_weekly', exist_ok=True)

with open('/tmp/duckdb_weekly/sales.csv', 'w') as f:
 import random
 products = ['iPhone', 'iPad', 'MacBook', 'AirPods']
 regions = ['East China', 'North China', 'South China', 'Southwest']
 f.write('date,product,region,revenue\n')
 for day in range(1, 32):
 for _ in range(50):
 f.write(f'2024-01-{day:02d},{random.choice(products)},{random.choice(regions)},{random.randint(5000, 50000)}\n')
print("Data generated: 1600 records ready")

import duckdb
from datetime import datetime, timedelta

conn = duckdb.connect()
conn.execute("CREATE TABLE sales AS SELECT * FROM read_csv('/tmp/duckdb_weekly/sales.csv')")

end_date = datetime(2024, 1, 31)
start_date = end_date - timedelta(days=6)

weekly_report = conn.execute("""
 SELECT 
 DATE(date) AS report_date,
 product,
 region,
 SUM(revenue) AS total_revenue,
 COUNT(*) AS order_count,
 AVG(revenue) AS avg_order_value
 FROM sales
 WHERE date BETWEEN ? AND ?
 GROUP BY 1, 2, 3
 ORDER BY total_revenue DESC
""", str(start_date.date()), str(end_date.date())).fetchdf()

print(weekly_report.head(10))

import matplotlib.pyplot as plt

product_summary = weekly_report.groupby('product')['total_revenue'].sum().sort_values(ascending=False)

plt.figure(figsize=(10, 6))
bars = plt.bar(product_summary.index, product_summary.values, color='steelblue')
plt.title('Weekly Revenue by Product', fontsize=16)
plt.ylabel('Revenue (CNY)', fontsize=12)
plt.xlabel('Product', fontsize=12)
plt.tight_layout()
plt.savefig('/tmp/duckdb_weekly/chart.png', dpi=150)
print("Chart saved")

# Heatmap: Region × Product
pivot_table = weekly_report.pivot_table(
 values='total_revenue', 
 index='region', 
 columns='product', 
 aggfunc='sum'
)
plt.figure(figsize=(12, 6))
plt.imshow(pivot_table, cmap='YlOrRd', aspect='auto')
plt.colorbar(label='Revenue')
plt.xticks(range(len(pivot_table.columns)), pivot_table.columns)
plt.yticks(range(len(pivot_table.index)), pivot_table.index)
plt.title('Revenue Heatmap: Region × Product')
plt.tight_layout()
plt.savefig('/tmp/duckdb_weekly/heatmap.png', dpi=150)

import telebot

BOT_TOKEN = 'your_bot_token_here'
CHAT_ID = 'your_chat_id_here'

bot = telebot.TeleBot(BOT_TOKEN)

message = f"""📊 Weekly Report Generated (2024-01-25 to 2024-01-31)

🏆 Top 3 Products by Revenue:
{weekly_report.head(3).to_string(index=False)}

💰 Total Revenue: {weekly_report['total_revenue'].sum():,.0f} CNY
📦 Total Orders: {weekly_report['order_count'].sum():,}
📈 Avg Order Value: {weekly_report['avg_order_value'].mean():,.0f} CNY
"""

bot.send_photo(
 CHAT_ID, 
 open('/tmp/duckdb_weekly/chart.png', 'rb'), 
 caption=message
)
print("Report sent to Telegram")

crontab -e

# Run every Monday at 8:00 AM
0 8 * * 1 cd /root && python3 /path/to/weekly_report.py >> /tmp/duckdb_weekly/cron.log 2>&1

from apscheduler.schedulers.background import BackgroundScheduler
import time

def run_weekly():
 print(f"[{datetime.now()}] Generating weekly report...")
 # Call the generation logic above
 print("Weekly report generated")

scheduler = BackgroundScheduler()
scheduler.add_job(
 run_weekly, 
 'cron', 
 day_of_week='mon', 
 hour=8, 
 minute=0
)
scheduler.start()

while True:
 time.sleep(60)

# First conversion: CSV → Parquet
conn.execute("""
 CREATE TABLE sales AS 
 SELECT * FROM read_csv('/data/raw/sales_2024/*.csv')
""")
conn.execute("COPY sales TO '/data/parquet/sales.parquet' (FORMAT PARQUET)")

# Subsequent queries read Parquet directly—5-10x faster
conn.execute("""
 CREATE TABLE sales_cache AS 
 SELECT * FROM read_parquet('/data/parquet/sales.parquet')
""")

# Mount remote data sources
conn.execute("ATTACH 'sales.db' AS sales_db (TYPE SQLite)")
conn.execute("ATTACH 'finance.duckdb' AS finance_db")

# Cross-source JOIN (zero data movement)
conn.execute("""
 SELECT 
 s.date,
 s.product,
 s.revenue,
 f.cost
 FROM sales_db.sales s
 JOIN finance_db.costs f ON s.order_id = f.order_id
""")

import streamlit as st
import duckdb

st.set_page_config(page_title="Weekly Dashboard", page_icon="📊")
st.title("📊 Real-time Weekly Dashboard")

@st.cache_data(ttl=3600)
def load_data():
 return duckdb.query("SELECT * FROM read_csv('sales.csv')").df()

df = load_data()
st.dataframe(df)

col1, col2, col3 = st.columns(3)
with col1:
 st.metric("Total Revenue", f"{df['revenue'].sum():,.0f} CNY")
with col2:
 st.metric("Total Orders", f"{df.shape[0]:,}")
with col3:
 st.metric("Avg Order Value", f"{df['revenue'].mean():,.0f} CNY")

st.line_chart(df.set_index('date')['revenue'])

# Clone the project
git clone https://github.com/duckdblab/weekly-report-system.git
cd weekly-report-system

# Install dependencies
pip install duckdb pandas matplotlib pyTelegramBotAPI apscheduler

# Generate test data
python generate_test_data.py

# Run the report manually
python weekly_report.py

# Or start the scheduler
python scheduler.py