import duckdb
import pandas as pd
from pathlib import Path

# 连接 DuckDB 内存数据库（零配置）
con = duckdb.connect(':memory:')

# 支付交易记录（CSV）—— 直接读取，无需建表
con.execute("""
    COPY transactions FROM 'data/transactions_202605.csv'
    (FORMAT CSV, HEADER, DELIMITER ',');
""")

# 物流费用（CSV）
con.execute("""
    COPY logistics FROM 'data/logistics_costs.csv'
    (FORMAT CSV, HEADER, DELIMITER ',');
""")

# 广告费用（API 导出的 JSON 格式）
con.execute("""
    COPY ad_spend FROM 'data/ad_spend.json'
    (FORMAT JSON, AUTO_DETERMINE);
""")

# 退款数据（Excel）—— DuckDB 直接读 xlsx！
con.execute("""
    COPY refunds FROM 'data/refunds.xlsx'
    (HEADER, SHEET 'Refunds');
""")

WITH 
    -- 收入：按月份聚合
    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
    ),
    
    -- 退款：按月汇总
    refund_amount AS (
        SELECT 
            DATE_TRUNC('month', refund_date) AS month,
            SUM(refund_amount) AS total_refund
        FROM refunds
        GROUP BY 1
    ),
    
    -- 商品成本（关联商品表获取成本价）
    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_total AS (
        SELECT 
            DATE_TRUNC('month', shipped_date) AS month,
            SUM(cost) AS total_logistics
        FROM logistics
        GROUP BY 1
    ),
    
    -- 广告成本
    ad_total AS (
        SELECT 
            DATE_TRUNC('month', start_date) AS month,
            SUM(spend) AS total_ad_spend
        FROM ad_spend
        GROUP BY 1
    )

-- 组装最终损益表
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;

# 执行查询，获取 Pandas DataFrame
result = con.execute(pl_sql).fetchdf()
print(result)

# 导出为 CSV 供 BI 工具接入
result.to_csv('reports/pl_statement.csv', index=False)

# 导出为 Parquet，供下次查询直接读取（加速 5 倍+）
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,
    -- 上月收入（环比）
    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,
    -- 去年同期收入（同比）
    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)")

# 交互式筛选——分析师自己改条件
filter_month = st.selectbox("选择月份", ['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} 交易分析")
st.line_chart(st.session_state.chart_data)