import duckdb
import pandas as pd

con = duckdb.connect()

# read_csv_auto infers column types automatically—no schema needed
analysis_sql = """
SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM read_csv_auto('orders.csv')
GROUP BY month, product_category
ORDER BY month DESC, total_revenue DESC
"""

result = con.sql(analysis_sql).df()
print(result.head(10))

import matplotlib.pyplot as plt
import matplotlib
matplotlib.use('Agg')  # headless mode for server deployment

fig, axes = plt.subplots(2, 2, figsize=(14, 10))
fig.suptitle('Monthly Sales Analysis Report', fontsize=16, fontweight='bold')

# 1. Revenue trend by category
pivot_data = result.pivot(index='month', columns='product_category',
                           values='total_revenue')
pivot_data.plot(kind='line', ax=axes[0, 0], marker='o')
axes[0, 0].set_title('Revenue Trend by Category')
axes[0, 0].tick_params(axis='x', rotation=45)

# 2. Pie chart for latest month
latest_month = result['month'].max()
monthly_data = result[result['month'] == latest_month]
monthly_data.plot.pie(y='total_revenue', ax=axes[0, 1],
                       labels=monthly_data['product_category'],
                       autopct='%1.1f%%')
axes[0, 1].set_title(f'{latest_month} Revenue Share')

# 3. Order value distribution
axes[1, 0].hist(result['avg_order_value'], bins=20,
                color='steelblue', alpha=0.7)
axes[1, 0].set_title('Average Order Value Distribution')
axes[1, 0].set_xlabel('Amount (CNY)')

# 4. Customer count by category
pivot_customers = result.pivot(index='month',
                                columns='product_category',
                                values='unique_customers')
pivot_customers.plot.bar(stacked=True, ax=axes[1, 1])
axes[1, 1].set_title('Monthly Customers by Category')
axes[1, 1].tick_params(axis='x', rotation=45)

plt.tight_layout()
plt.savefig('report_charts.png', dpi=150, bbox_inches='tight')
plt.close()
print('Charts generated')

from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, \
    Image, Table, TableStyle
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import cm
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

# Register Chinese font
try:
    pdfmetrics.registerFont(TTFont('Chinese',
        '/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc'))
    font_name = 'Chinese'
except:
    font_name = 'Helvetica'

doc = SimpleDocTemplate('sales_report.pdf', pagesize=A4,
                        rightMargin=2*cm, leftMargin=2*cm,
                        topMargin=2*cm, bottomMargin=2*cm)

styles = getSampleStyleSheet()
title_style = ParagraphStyle('Title', parent=styles['Title'],
                              fontName=font_name, fontSize=18,
                              spaceAfter=20)
heading_style = ParagraphStyle('Heading', parent=styles['Heading1'],
                                fontName=font_name, fontSize=14,
                                spaceAfter=10)
body_style = ParagraphStyle('Body', parent=styles['Normal'],
                             fontName=font_name, fontSize=10,
                             leading=16)

story = []

# Title
story.append(Paragraph('Monthly Sales Analysis Report', title_style))
story.append(Paragraph(
    f'Generated: {pd.Timestamp.now().strftime("%Y-%m-%d")}', body_style))
story.append(Spacer(1, 0.5*cm))

# Executive summary
total_revenue = result['total_revenue'].sum()
total_orders = result['order_count'].sum()
avg_value = result['avg_order_value'].mean()
story.append(Paragraph('I. Executive Summary', heading_style))
summary = (f'Analysis covers {len(result)} records across '
           f'{result["product_category"].nunique()} categories. '
           f'Total revenue: {total_revenue:,.0f} CNY, '
           f'{total_orders:,} orders, average order value: '
           f'{avg_value:.2f} CNY.')
story.append(Paragraph(summary, body_style))
story.append(Spacer(1, 0.5*cm))

# Charts
story.append(Paragraph('II. Data Visualization', heading_style))
story.append(Image('report_charts.png', width=450, height=320))
story.append(Spacer(1, 0.5*cm))

# Top 10 table
story.append(Paragraph('III. Top 10 Categories', heading_style))
top10 = result.nlargest(10, 'total_revenue')
table_data = [['Month', 'Category', 'Orders', 'Revenue', 'Avg Value']]
for _, row in top10.iterrows():
    table_data.append([str(row['month'])[:10], row['product_category'],
                       str(int(row['order_count'])),
                       f"{row['total_revenue']:,.0f}",
                       f"{row['avg_order_value']:.2f}"])

table = Table(table_data, colWidths=[2.5*cm, 3*cm, 2*cm, 3*cm, 3*cm])
table.setStyle(TableStyle([
    ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#2C3E50')),
    ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
    ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
    ('FONTNAME', (0, 0), (-1, 0), font_name),
    ('FONTSIZE', (0, 0), (-1, 0), 10),
    ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
    ('GRID', (0, 0), (-1, -1), 1, colors.grey),
    ('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#ECF0F1')),
]))
story.append(table)

doc.build(story)
print('PDF report generated: sales_report.pdf')

import yaml
from datetime import datetime

def run_report(client_config_path='clients/default.yaml'):
    with open(client_config_path) as f:
        config = yaml.safe_load(f)

    con = duckdb.connect()
    sql = config['sql_template'].format(
        date_range=config.get('date_range', 'last_30_days')
    )
    result = con.sql(sql).df()

    output_name = (f"reports/{config['name']}_"
                   f"{datetime.now().strftime('%Y%m%d')}.pdf")
    render_pdf(result, config, output_path=output_name)
    print(f"✅ {config['name']} report generated")

# clients/ecommerce_a.yaml:
# name: Ecommerce Client A
# sql_template: "SELECT * FROM read_csv_auto('orders_a.csv') WHERE ..."
# date_range: last_30_days

# One configuration change, batch-generate 10+ reports per day