import duckdb
import os

con = duckdb.connect('orders.duckdb')

# One SQL statement: CSV -> Parquet with ZSTD compression
con.execute("""
 COPY (SELECT * FROM read_csv_auto('orders_2026.csv'))
 TO 'orders_2026.parquet'
 (FORMAT PARQUET, COMPRESSION ZSTD)
""")

csv_size = os.path.getsize('orders_2026.csv')
parquet_size = os.path.getsize('orders_2026.parquet')
print(f"Original CSV: {csv_size / 1024 / 1024:.2f} MB")
print(f"Converted Parquet: {parquet_size / 1024 / 1024:.2f} MB")
print(f"Compression ratio: {parquet_size / csv_size * 100:.1f}%")

import duckdb
import time

con = duckdb.connect('orders.duckdb')

# Compare CSV vs Parquet query speed
start = time.time()
csv_result = con.execute("""
 SELECT customer_id, product_id, amount, order_date, status
 FROM read_csv_auto('orders_2026.csv')
 WHERE order_date >= '2026-07-01'
""").fetchdf()
csv_time = time.time() - start
print(f"CSV query time: {csv_time:.3f} seconds")

start = time.time()
parquet_result = con.execute("""
 SELECT customer_id, product_id, amount, order_date, status
 FROM 'orders_2026.parquet'
 WHERE order_date >= '2026-07-01'
""").fetchdf()
parquet_time = time.time() - start
print(f"Parquet query time: {parquet_time:.3f} seconds")
print(f"Speedup: {csv_time / parquet_time:.1f}x")

import duckdb

con = duckdb.connect('sales_analytics.duckdb')

# Write with year/month partitions (Hive-style)
con.execute("""
 COPY (
 SELECT *,
 EXTRACT(YEAR FROM order_date) AS year,
 EXTRACT(MONTH FROM order_date) AS month
 FROM read_csv_auto('sales_full_2024_2026.csv')
 )
 TO 'sales_partitioned'
 (FORMAT PARQUET, PARTITION_BY (year, month))
""")

# Query July 2026 data, automatically skipping 30+ other partitions
result = con.execute("""
 SELECT * FROM 'sales_partitioned'
 WHERE year = 2026 AND month = 7
""").fetchdf()

print(f"July 2026 rows: {len(result)}")

import duckdb
import pandas as pd
from datetime import datetime, timedelta
import os

class SalesReportGenerator:
 def __init__(self, parquet_path='sales_partitioned'):
 self.con = duckdb.connect(':memory:') # In-memory DB, faster for concurrent queries
 self.parquet_path = parquet_path
 
 def generate_daily_report(self, date=None):
 """Generate daily report"""
 if date is None:
 date = datetime.now().date()
 
 date_str = date.strftime('%Y-%m-%d')
 
 # Read only today's partition data
 daily_sales = self.con.execute(f"""
 SELECT 
 product_category,
 COUNT(*) AS order_count,
 SUM(sales_amount) AS total_sales,
 AVG(sales_amount) AS avg_order_value
 FROM '{self.parquet_path}'
 WHERE year = {date.year} 
 AND month = {date.month}
 AND day = {date.day}
 GROUP BY product_category
 ORDER BY total_sales DESC
 """).fetchdf()
 
 # Week-over-week comparison (same day last week)
 last_week = date - timedelta(days=7)
 weekly_compare = self.con.execute(f"""
 SELECT 
 product_category,
 SUM(CASE WHEN year = {date.year} AND month = {date.month} AND day = {date.day} 
 THEN sales_amount ELSE 0 END) AS current,
 SUM(CASE WHEN year = {last_week.year} AND month = {last_week.month} AND day = {last_week.day} 
 THEN sales_amount ELSE 0 END) AS last_week
 FROM '{self.parquet_path}'
 WHERE (year = {date.year} AND month = {date.month} AND day = {date.day})
 OR (year = {last_week.year} AND month = {last_week.month} AND day = {last_week.day})
 GROUP BY product_category
 """).fetchdf()
 
 weekly_compare['change_pct'] = (
 (weekly_compare['current'] - weekly_compare['last_week']) 
 / weekly_compare['last_week'] * 100
 ).round(2)
 
 return daily_sales, weekly_compare
 
 def export_to_excel(self, daily_sales, weekly_compare, output_path):
 """Export to Excel with formatting"""
 with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
 daily_sales.to_excel(writer, sheet_name='Daily Sales', index=False)
 weekly_compare.to_excel(writer, sheet_name='Weekly Compare', index=False)
 
 print(f"Report saved: {output_path}")
 return output_path

# Usage
if __name__ == '__main__':
 reporter = SalesReportGenerator()
 daily, compare = reporter.generate_daily_report()
 output = reporter.export_to_excel(
 daily, compare, 
 f'sales_report_{datetime.now().date()}.xlsx'
 )
 print(f"Report generated: {output}")