import duckdb
from pathlib import Path

# 连接 DuckDB 内存数据库
con = duckdb.connect(':memory:')

# ── 第一步：读取所有数据源 ──

# 1. CSV - 支付交易记录
con.execute("""
    COPY transactions FROM 'data/transactions_202605.csv'
    (FORMAT CSV, HEADER, DELIMITER ',');
""")

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

# 3. JSON - 广告费用
con.execute("""
    COPY ad_spend FROM 'data/ad_spend.json'
    (FORMAT JSON, AUTO_DETERMINE);
""")

# 4. Excel - 退款数据
con.execute("""
    COPY refunds FROM 'data/refunds.xlsx'
    (HEADER, SHEET 'Refunds');
""")

# 5. SQLite - 商品信息
con.execute("""
    ATTACH 'data/products.db' AS products_db (TYPE sqlite);
    CREATE TABLE products AS
        SELECT * FROM products_db.main.products;
""")

# ── 第二步：跨数据源联合查询 ──

pl_sql = """
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 ON t.product_id = p.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;
"""

result = con.execute(pl_sql).fetchdf()
print(result)

# 导出结果
result.to_csv('reports/pl_statement.csv', index=False)
result.to_parquet('reports/pl_statement.parquet')

con.close()

trend_sql = """
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())