import duckdb
import pandas as pd

# Connect in-memory (use .duckdb file in production)
con = duckdb.connect(":memory:")

# Generate simulated order data using CTEs
con.execute("""
CREATE TABLE orders AS
WITH dates AS (
 SELECT DATE '2024-01-01' + n AS order_date
 FROM unnest(generate_series(0, 364)) AS t(n)
),
categories AS (
 SELECT unnest(['Electronics', 'Clothing', 'Home & Kitchen', 'Books', 'Sports']) AS cat
),
products AS (
 SELECT 
 unnest(generate_series(1, 500)) AS product_id,
 unnest(categories) AS category,
 FLOOR(RANDOM() * 500 + 10)::DOUBLE AS price,
 FLOOR(RANDOM() * 5 + 1) AS rating
 FROM categories
 LIMIT 500
),
orders_detail AS (
 SELECT 
 p.product_id,
 p.category,
 p.price * (1 + (RANDOM() - 0.5) * 0.2) AS final_price,
 d.order_date,
 FLOOR(RANDOM() * 5 + 1)::INTEGER AS quantity,
 FLOOR(RANDOM() * 1000000) AS order_id
 FROM dates d
 CROSS JOIN products p
 WHERE RANDOM() < 0.15
)
SELECT * FROM orders_detail
""")

count = con.execute("SELECT COUNT(*) FROM orders").fetchone()[0]
print(f"📦 Orders generated: {count:,}")
con.execute("CREATE INDEX idx_date ON orders(order_date)").fetchall()

daily_sales = con.execute("""
WITH daily AS (
 SELECT 
 order_date,
 SUM(final_price * quantity) AS revenue,
 COUNT(DISTINCT order_id) AS orders,
 AVG(final_price * quantity) AS avg_order_value
 FROM orders
 GROUP BY order_date
)
SELECT 
 order_date,
 revenue,
 orders,
 ROUND(avg_order_value, 2) AS avg_order_value,
 LAG(revenue, 7) OVER (ORDER BY order_date) AS revenue_last_week,
 ROUND(
 (revenue - LAG(revenue, 7) OVER (ORDER BY order_date)) 
 / NULLIF(LAG(revenue, 7) OVER (ORDER BY order_date), 0) * 100, 2
 ) AS WoW_change_pct
FROM daily
ORDER BY order_date
""").df()

print(daily_sales.tail(7))
# Shows last 7 days of sales trend with automatic WoW comparison

category_pareto = con.execute("""
WITH cat_rev AS (
 SELECT 
 category,
 SUM(final_price * quantity) AS total_revenue,
 ROUND(
 SUM(final_price * quantity) * 100.0 
 / SUM(SUM(final_price * quantity)) OVER (), 2
 ) AS revenue_pct
 FROM orders
 GROUP BY category
),
ranked AS (
 SELECT *,
 ROW_NUMBER() OVER (ORDER BY total_revenue DESC) AS rn,
 SUM(total_revenue) OVER (ORDER BY total_revenue DESC 
 ROWS UNBOUNDED PRECEDING) AS cumulative_revenue
 FROM cat_rev
)
SELECT 
 category,
 total_revenue,
 revenue_pct,
 rn,
 ROUND(cumulative_revenue / SUM(total_revenue) OVER () * 100, 1) AS cumulative_pct
FROM ranked
ORDER BY total_revenue DESC
""").df()

print(category_pareto.to_string(index=False))

ltv = con.execute("""
WITH user_metrics AS (
 SELECT 
 FLOOR(RANDOM() * 5000) + 1 AS user_id,
 COUNT(DISTINCT order_id) AS total_orders,
 SUM(final_price * quantity) AS lifetime_value,
 MIN(order_date) AS first_order_date,
 MAX(order_date) AS last_order_date,
 DATEDAY(MAX(order_date)) - DATEDAY(MIN(order_date)) AS active_days
 FROM orders
 GROUP BY user_id
 HAVING total_orders >= 1
)
SELECT 
 CASE 
 WHEN lifetime_value >= PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY lifetime_value) THEN 'Platinum'
 WHEN lifetime_value >= PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY lifetime_value) THEN 'Gold'
 WHEN lifetime_value >= PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lifetime_value) THEN 'Silver'
 ELSE 'Bronze'
 END AS tier,
 COUNT(*) AS user_count,
 ROUND(AVG(lifetime_value), 2) AS avg_ltv,
 ROUND(SUM(lifetime_value), 2) AS total_revenue,
 ROUND(AVG(total_orders), 2) AS avg_orders
FROM user_metrics
GROUP BY 1
ORDER BY avg_ltv DESC
""").df()

print(ltv.to_string(index=False))

class DuckDBSalesEngine:
 """E-commerce Sales Analytics Engine - initialize once, reuse infinitely"""
 
 def __init__(self, data_path: str = ":memory:"):
 self.con = duckdb.connect(data_path)
 
 def load_csv(self, table_name: str, file_path: str):
 """Auto-detect schema and import CSV"""
 self.con.execute(
 f"CREATE TABLE {table_name} AS SELECT * FROM read_csv_auto('{file_path}')"
 )
 
 def daily_trend(self, days: int = 30) -> pd.DataFrame:
 return self.con.execute("""
 WITH daily AS (
 SELECT order_date,
 SUM(final_price * quantity) AS revenue,
 COUNT(DISTINCT order_id) AS orders
 FROM orders
 GROUP BY order_date
 )
 SELECT order_date, revenue, orders,
 LAG(revenue, 7) OVER (ORDER BY order_date) AS prev_week_rev,
 ROUND((revenue - LAG(revenue,7) OVER (ORDER BY order_date)) 
 / NULLIF(LAG(revenue,7) OVER (ORDER BY order_date),0) * 100, 2) AS wow_pct
 FROM daily
 WHERE order_date >= (SELECT MAX(order_date) - INTERVAL '{days}' DAY FROM daily)
 ORDER BY order_date
 """.format(days=days)).df()
 
 def category_pareto(self) -> pd.DataFrame:
 return self.con.execute("""
 WITH cat_rev AS (
 SELECT category,
 SUM(final_price * quantity) AS total_revenue
 FROM orders GROUP BY category
 ),
 ranked AS (
 SELECT *, 
 SUM(total_revenue) OVER (ORDER BY total_revenue DESC 
 ROWS UNBOUNDED PRECEDING) AS cum_rev
 FROM cat_rev
 )
 SELECT category, total_revenue, 
 ROUND(total_revenue/SUM(total_revenue) OVER()*100, 2) AS pct,
 ROUND(cum_rev/SUM(total_revenue) OVER()*100, 1) AS cum_pct
 FROM ranked ORDER BY total_revenue DESC
 """).df()
 
 def export_report(self, output_path: str = "sales_report.parquet"):
 """Export analysis results to Excel"""
 trend = self.daily_trend()
 pareto = self.category_pareto()
 with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
 trend.to_excel(writer, sheet_name='Daily Trend', index=False)
 pareto.to_excel(writer, sheet_name='Category Pareto', index=False)
 print(f"✅ Report exported: {output_path}")

engine = DuckDBSalesEngine()
# engine.load_csv('orders', '/path/to/your/orders.csv')
trend = engine.daily_trend(30)
pareto = engine.category_pareto()
engine.export_report("weekly_report.xlsx")

# streamlit_app.py
import streamlit as st
import duckdb
import pandas as pd

st.set_page_config(page_title="📊 E-commerce Analytics", layout="wide")
st.title("🦆 DuckDB E-commerce Sales Engine")

# Sidebar: Data source selection
st.sidebar.header("📁 Data Source")
data_source = st.sidebar.selectbox("Choose source", ["Sample Data", "Upload CSV"])

if data_source == "Sample Data":
 con = duckdb.connect(":memory:")
 # ... same sample data generation as above
else:
 uploaded = st.sidebar.file_uploader("Upload CSV", type=["csv"])
 if uploaded:
 df = pd.read_csv(uploaded)
 con = duckdb.connect(":memory:")
 con.register("orders", df)

# Main dashboard: Three KPI cards
col1, col2, col3 = st.columns(3)
with col1:
 st.metric("📈 Today's Revenue", "$12,450", "+8.3%")
with col2:
 st.metric("🛒 Today's Orders", "342", "+5.1%")
with col3:
 st.metric("💰 Avg Order Value", "$36.41", "-1.2%")

# Sales trend chart
st.subheader("📅 Last 30 Days Sales Trend")
trend_df = con.execute("""
 SELECT order_date, 
 SUM(final_price * quantity) AS revenue
 FROM orders
 GROUP BY order_date
 ORDER BY order_date
""").df()
st.line_chart(trend_df.set_index('order_date')['revenue'])

# Category Pareto chart
st.subheader("🏷️ Category Revenue Contribution (Pareto)")
pareto_df = con.execute("""
 SELECT category, 
 SUM(final_price * quantity) AS revenue,
 ROUND(SUM(final_price * quantity)*100.0/SUM(SUM(final_price * quantity)) OVER (), 1) AS pct
 FROM orders GROUP BY category
""").df()
pareto_df = pareto_df.sort_values('revenue', ascending=False)
pareto_df['cum_pct'] = pareto_df['pct'].cumsum()
st.bar_chart(pareto_df.set_index('category')[['revenue', 'pct']])

# Export button
if st.button("📥 Export Report"):
 with pd.ExcelWriter("sales_report.xlsx", engine='openpyxl') as writer:
 con.execute("SELECT * FROM orders").df().to_excel(writer, sheet_name='Raw Data', index=False)
 st.success("✅ Report downloaded!")

pip install streamlit duckdb pandas openpyxl
streamlit run streamlit_app.py