import duckdb
import pandas as pd
from pathlib import Path

# Connect to DuckDB in-memory database (zero configuration)
con = duckdb.connect(':memory:')

# Payment transactions (CSV) — read directly, no table creation needed
con.execute("""
 COPY transactions FROM 'data/transactions_202605.csv'
 (FORMAT CSV, HEADER, DELIMITER ',');
""")

# Logistics costs (CSV)
con.execute("""
 COPY logistics FROM 'data/logistics_costs.csv'
 (FORMAT CSV, HEADER, DELIMITER ',');
""")

# Ad spend (JSON from API export)
con.execute("""
 COPY ad_spend FROM 'data/ad_spend.json'
 (FORMAT JSON, AUTO_DETERMINE);
""")

# Refunds (Excel) — DuckDB reads xlsx natively!
con.execute("""
 COPY refunds FROM 'data/refunds.xlsx'
 (HEADER, SHEET 'Refunds');
""")

WITH 
 -- Revenue: aggregated by month
 revenue AS (
 SELECT 
 DATE_TRUNC('month', order_date) AS month,
 SUM(amount) AS gross_revenue,
 COUNT(DISTINCT order_id) AS order_count
 FROM transactions
 WHERE status = 'completed'
 GROUP BY 1
 ),
 
 -- Refunds: aggregated by month
 refund_amount AS (
 SELECT 
 DATE_TRUNC('month', refund_date) AS month,
 SUM(refund_amount) AS total_refund
 FROM refunds
 GROUP BY 1
 ),
 
 -- Product cost (join with product table for cost price)
 product_cost AS (
 SELECT 
 DATE_TRUNC('month', t.order_date) AS month,
 SUM(t.quantity * p.cost_price) AS total_cost
 FROM transactions t
 JOIN products p USING (product_id)
 WHERE t.status = 'completed'
 GROUP BY 1
 ),
 
 -- Logistics cost
 logistics_total AS (
 SELECT 
 DATE_TRUNC('month', shipped_date) AS month,
 SUM(cost) AS total_logistics
 FROM logistics
 GROUP BY 1
 ),
 
 -- Ad spend
 ad_total AS (
 SELECT 
 DATE_TRUNC('month', start_date) AS month,
 SUM(spend) AS total_ad_spend
 FROM ad_spend
 GROUP BY 1
 )

-- Assemble final P&L statement
SELECT 
 r.month,
 ROUND(r.gross_revenue, 2) AS gross_revenue,
 COALESCE(ref.total_refund, 0) AS refunds,
 ROUND(r.gross_revenue - COALESCE(ref.total_refund, 0), 2) AS net_revenue,
 ROUND(pc.total_cost, 2) AS product_cost,
 ROUND(lt.total_logistics, 2) AS logistics_cost,
 ROUND(at.total_ad_spend, 2) AS ad_spend,
 ROUND(r.gross_revenue - COALESCE(ref.total_refund, 0) 
 - pc.total_cost - lt.total_logistics - at.total_ad_spend, 2) AS net_profit,
 ROUND(
 (r.gross_revenue - COALESCE(ref.total_refund, 0) 
 - pc.total_cost - lt.total_logistics - at.total_ad_spend) 
 / NULLIF(r.gross_revenue, 0) * 100, 2
 ) AS profit_margin_pct
FROM revenue r
LEFT JOIN refund_amount ref USING (month)
LEFT JOIN product_cost pc USING (month)
LEFT JOIN logistics_total lt USING (month)
LEFT JOIN ad_total at USING (month)
ORDER BY r.month;

# Execute query, get Pandas DataFrame
result = con.execute(pl_sql).fetchdf()
print(result)

# Export as CSV for BI tool integration
result.to_csv('reports/pl_statement.csv', index=False)

# Export as Parquet for 5x+ faster reads next time
result.to_parquet('reports/pl_statement.parquet')

con.close()

WITH monthly_pl AS (
 SELECT 
 DATE_TRUNC('month', order_date) AS month,
 SUM(amount) AS revenue
 FROM transactions
 WHERE status = 'completed'
 GROUP BY 1
 ORDER BY 1
)
SELECT 
 month,
 revenue,
 -- Previous month revenue (MoM)
 LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
 ROUND(
 (revenue - LAG(revenue) OVER (ORDER BY month)) 
 / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
 ) AS mom_growth_pct,
 -- Same month last year (YoY)
 LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
 ROUND(
 (revenue - LAG(revenue, 12) OVER (ORDER BY month)) 
 / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 2
 ) AS yoy_growth_pct
FROM monthly_pl;

import streamlit as st
import duckdb

con = duckdb.connect(':memory:')
con.execute("COPY transactions FROM 'data/transactions_202605.csv' (FORMAT CSV, HEADER)")
con.execute("COPY logistics FROM 'data/logistics_costs.csv' (FORMAT CSV, HEADER)")

# Interactive filtering — analysts change conditions themselves
filter_month = st.selectbox("Select Month", ['2026-05', '2026-04', '2026-03'])

query = f"""
 SELECT category, SUM(amount) as total
 FROM transactions
 WHERE DATE_TRUNC('month', order_date) = '{filter_month}-01'
 GROUP BY category
 ORDER BY total DESC
"""

st.dataframe(con.execute(query).fetchdf())
st.title(f"{filter_month} Transaction Analysis")
st.line_chart(st.session_state.chart_data)