"""
莱塔萝门店数据看板 - 后端数据生成器
输出: dashboard_data.json (所有日报/周报/月报/自定义数据预生成)
"""
import pandas as pd
import json
import os, sys
import numpy as np

from datetime import date, timedelta
sys.stdout.reconfigure(encoding='utf-8')

class NpEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, (np.integer,)): return int(obj)
        if isinstance(obj, (np.floating,)): return float(obj) if not np.isnan(obj) else 0
        if isinstance(obj, (np.bool_,)): return bool(obj)
        if isinstance(obj, (np.ndarray,)): return obj.tolist()
        return super().default(obj)

BASE_DIR = os.path.dirname(os.path.abspath(__file__))

def load_config():
    cfg_path = os.path.join(BASE_DIR, "config.json")
    with open(cfg_path, "r", encoding="utf-8-sig") as f:
        return json.load(f)

SALES_FILE = os.path.join(BASE_DIR, 'sales.parquet')
OUTPUT_FILE = os.path.join(BASE_DIR, 'dashboard_data.json')

def load():
    df = pd.read_parquet(SALES_FILE)
    try:
        cfg = load_config()
        exclude = cfg.get("exclude_stores", [])
        if exclude:
            df = df[~df["store_name"].isin(exclude)].copy()
    except Exception:
        pass
    # 保留全部数据（含负数退货/满减），只排除空门店名的统计行
    sales = df[df['store_name'].notna() & (df['store_name'].str.strip() != '')].copy()
    for col in ['date','week','month','hour','year']:
        if col == 'date':
            sales[col] = sales['sale_time'].dt.strftime('%Y-%m-%d')
        elif col == 'week':
            sales[col] = sales['sale_time'].dt.isocalendar().week.astype(float).fillna(0).astype(int)
        else:
            sales[col] = getattr(sales['sale_time'].dt, col).astype(float).fillna(0).astype(int)
    return sales

def fmt(records):
    out = []
    for r in records:
        item = {}
        for k, v in r.items():
            if isinstance(v, (np.integer,)): item[k] = int(v)
            elif isinstance(v, (np.floating,)): item[k] = float(v) if not np.isnan(v) else 0
            elif pd.isna(v) or v is None: item[k] = 0
            else: item[k] = v
        out.append(item)
    return out

def sum_stats(df):
    o = int(df['receipt_no'].nunique())
    i = int(df['quantity'].sum())
    t = float(df['subtotal'].sum())
    return {
        'total_sales': round(t,2), 'order_count': o, 'item_count': i,
        'avg_price': round(t/o,2) if o else 0,
        'attach_rate': round(i/o,1) if o else 0,
        'store_count': int(df['store_name'].nunique()),
    }

def store_rk(df, n=999):
    g = df.groupby('store_name').agg(
        销售额=('subtotal','sum'),单数=('receipt_no','nunique'),件数=('quantity','sum')
    ).sort_values('销售额',ascending=False).reset_index()
    g['销售额'] = g['销售额'].round(2)
    return fmt(g.head(n).to_dict('records'))

def item_rk(df, n=10):
    # group by product_name, take first product_code for each
    g = df.groupby('product_name').agg(
        款号=('product_code','first'),销量=('quantity','sum'),销售额=('subtotal','sum')
    ).sort_values('销量',ascending=False).reset_index()
    records = fmt(g.head(n).to_dict('records'))
    # rename 款号 back to product_code for iTab compatibility
    for r in records:
        if '款号' in r:
            r['product_code'] = r.pop('款号')
    return records


def category_rk(df, n=10):
    # join with inventory to get category
    inv_file = os.path.join(BASE_DIR, 'inventory.parquet')
    inv = pd.read_parquet(inv_file)
    cat_map = inv[['product_code','category']].dropna(subset=['category']).drop_duplicates('product_code')
    df2 = df.merge(cat_map, on='product_code', how='left')
    df2['category'] = df2['category'].fillna('其他')
    g = df2.groupby('category').agg(
        销量=('quantity','sum'),销售额=('subtotal','sum')
    ).sort_values('销售额',ascending=False).reset_index()
    return fmt(g.head(n).to_dict('records'))

def daily_trend(df):
    t = df.groupby('date').agg(
        销售额=('subtotal','sum'),单数=('receipt_no','nunique')
    ).reset_index().sort_values('date')
    t['销售额'] = t['销售额'].round(2)
    return fmt(t.to_dict('records'))

def hour_dist(df):
    hd = df.groupby('hour').size().to_dict()
    return {str(h): int(hd.get(h,0)) for h in range(9,23)}

def pay_brk(df):
    return {
        'cash': round(float(df['cash'].sum()),2),
        'wechat': round(float(df['wechat_alipay'].sum()),2),
        'other': round(float(df['other_pay'].sum()),2),
    }

def week_range(year, week):
    jan4 = date(year, 1, 4)
    start = jan4 + timedelta(weeks=week-1, days=-jan4.weekday())
    end = start + timedelta(days=6)
    return start.isoformat(), end.isoformat()

def generate():
    print("加载数据...")
    sales = load()

    all_dates = sorted([d for d in sales['date'].unique() if d and str(d)!='nan'])
    last_date = all_dates[-1]
    all_stores = sorted([s for s in sales['store_name'].unique() if s and str(s).strip()])

    lr = sales[sales['date']==last_date].iloc[0]
    ly, lw, lm = int(lr['year']), int(lr['week']), int(lr['month'])

    # ============ 总览 ============
    overview = {
        'total_sales': round(float(sales['subtotal'].sum()),2),
        'total_orders': int(sales['receipt_no'].nunique()),
        'total_items': int(sales['quantity'].sum()),
        'stores_count': len(all_stores),
        'avg_price': round(float(sales['subtotal'].sum())/int(sales['receipt_no'].nunique()),2),
        'attach_rate': round(int(sales['quantity'].sum())/int(sales['receipt_no'].nunique()),1),
        'date_range': [all_dates[0], all_dates[-1]],
    }

    # ============ 全部日报 ============
    print(f"日报 ({len(all_dates)}天)...")
    daily_reports = {}
    for d in all_dates:
        day = sales[sales['date']==d]
        daily_reports[d] = {
            'date': d,
            'summary': sum_stats(day),
            'store_ranking': store_rk(day),
            'item_top10': item_rk(day, 10),
            'hour_distribution': hour_dist(day),
        }

    # ============ 全部周报 ============
    weeks_data = {}
    for (y, w), grp in sales.groupby(['year','week']):
        if int(y) <= 2000: continue  # 跳过脏数据
        key = f"{int(y)}-{int(w)}"
        ws, we = week_range(int(y), int(w))
        weeks_data[key] = {
            'year': int(y), 'week': int(w),
            'date_range': [ws, we],
            'summary': sum_stats(grp),
            'daily_trend': daily_trend(grp),
            'store_ranking': store_rk(grp),
            'item_top15': item_rk(grp, 15),
                'category_ranking': category_rk(grp, 10),
        }
    print(f"周报 ({len(weeks_data)}周)...")

    # ============ 全部月报 ============
    months_data = {}
    for (y, m), grp in sales.groupby(['year','month']):
        if int(y) <= 2000: continue
        key = f"{int(y)}-{int(m)}"
        months_data[key] = {
            'year': int(y), 'month': int(m),
            'summary': sum_stats(grp),
            'daily_trend': daily_trend(grp),
            'store_ranking': store_rk(grp),
            'item_top15': item_rk(grp, 15),
            'category_ranking': category_rk(grp, 10),
        }
    print(f"月报 ({len(months_data)}月)...")

    # ============ 周/月选择器列表 ============
    weeks_list = []
    for key, w in weeks_data.items():
        weeks_list.append({
            'key': key, 'year': w['year'], 'week': w['week'],
            'date_range': w['date_range'],
            'sales': w['summary']['total_sales'],
        })
    weeks_list.sort(key=lambda x: (x['year'], x['week']))

    months_list = []
    for key, m in months_data.items():
        months_list.append({
            'key': key, 'year': m['year'], 'month': m['month'],
            'sales': m['summary']['total_sales'],
        })
    months_list.sort(key=lambda x: (x['year'], x['month']))

    # ============ 全部年报 ============
    years_data = {}
    for (y,), grp in sales.groupby(['year']):
        if int(y) <= 2000: continue
        key = str(int(y))
        years_data[key] = {
            'year': int(y),
            'summary': sum_stats(grp),
            'daily_trend': daily_trend(grp),
            'store_ranking': store_rk(grp),
            'item_top15': item_rk(grp, 15),
            'monthly_trend': [],  # 各月销售额
            'category_ranking': category_rk(grp, 10),
        }
        # 月度趋势
        for mkey, mdata in months_data.items():
            if mdata['year'] == int(y):
                years_data[key]['monthly_trend'].append({
                    'month': mdata['month'],
                    'sales': mdata['summary']['total_sales'],
                    'orders': mdata['summary']['order_count'],
                })
        years_data[key]['monthly_trend'].sort(key=lambda x: x['month'])
    print(f"年报 ({len(years_data)}年)...")

    # ============ 年份列表 ============
    years_list = []
    for key, ydata in years_data.items():
        years_list.append({
            'key': key, 'year': ydata['year'],
            'sales': ydata['summary']['total_sales'],
        })
    years_list.sort(key=lambda x: x['year'])
    default_year = years_list[-1]['key'] if years_list else None

    # ============ 库存统计 ============
    print("库存统计...")
    inv_file = os.path.join(BASE_DIR, 'inventory.parquet')
    inventory = pd.read_parquet(inv_file)
    try:
        cfg = load_config()
        exclude = cfg.get("exclude_stores", [])
        if exclude:
            inventory = inventory[~inventory["store_name"].isin(exclude)].copy()
    except Exception:
        pass
    inventory = inventory[inventory['stock_qty'] > 0].copy() if 'stock_qty' in inventory.columns else inventory
    # 排除门店名为空的库存（仓库/统计行）
    inventory = inventory[inventory['store_name'].notna() & (inventory['store_name'].str.strip() != '')]

    # 库存总览
    inv_overview = {
        'total_items': int(inventory['stock_qty'].sum()),
        'total_stores': int(inventory['store_name'].nunique()),
        'total_retail': round(float(inventory['retail_total'].sum()), 2),
        'total_cost': round(float(inventory['cost_total'].sum()), 2),
        'sku_count': len(inventory),
        'unique_products': int(inventory['product_code'].nunique()),
    }

    # 各门店库存排名
    inv_store_rk = []
    g = inventory.groupby('store_name').agg(
        库存件数=('stock_qty','sum'),
        零售总额=('retail_total','sum'),
        成本总额=('cost_total','sum'),
        SKU数=('product_code','nunique'),
    ).sort_values('库存件数', ascending=False).reset_index()
    g['零售总额'] = g['零售总额'].round(2)
    g['成本总额'] = g['成本总额'].round(2)
    inv_store_rk = fmt(g.to_dict('records'))

    # 各门店分季节库存
    inv_by_store_season = {}
    if 'season' in inventory.columns:
        for store in all_stores:
            sd = inventory[inventory['store_name'] == store]
            if len(sd) == 0: continue
            sg = sd.groupby('season').agg(
                库存件数=('stock_qty','sum'),
                零售总额=('retail_total','sum'),
                成本总额=('cost_total','sum'),
                SKU数=('product_code','nunique'),
            ).reset_index()
            sg['零售总额'] = sg['零售总额'].round(2)
            sg['成本总额'] = sg['成本总额'].round(2)
            inv_by_store_season[store] = fmt(sg.to_dict('records'))

    # 全部门店(所有季节合计) 按季节汇总
    inv_by_season = []
    if 'season' in inventory.columns:
        sg = inventory.groupby('season').agg(
            库存件数=('stock_qty','sum'),
            零售总额=('retail_total','sum'),
            成本总额=('cost_total','sum'),
            SKU数=('product_code','nunique'),
        ).sort_values('库存件数', ascending=False).reset_index()
        sg['零售总额'] = sg['零售总额'].round(2)
        sg['成本总额'] = sg['成本总额'].round(2)
        inv_by_season = fmt(sg.to_dict('records'))

    # 库存品类分布
    inv_by_category = []
    if 'category' in inventory.columns:
        cg = inventory.groupby('category').agg(
            库存件数=('stock_qty','sum'),
            零售总额=('retail_total','sum'),
            成本总额=('cost_total','sum'),
        ).sort_values('库存件数', ascending=False).reset_index().head(20)
        cg['零售总额'] = cg['零售总额'].round(2)
        cg['成本总额'] = cg['成本总额'].round(2)
        inv_by_category = fmt(cg.to_dict('records'))
    else:
        # 用 product_short 近似分类
        cg = inventory.groupby('product_short').agg(
            库存件数=('stock_qty','sum'),
            零售总额=('retail_total','sum'),
            成本总额=('cost_total','sum'),
        ).sort_values('库存件数', ascending=False).reset_index().head(20)
        cg['零售总额'] = cg['零售总额'].round(2)
        cg['成本总额'] = cg['成本总额'].round(2)
        inv_by_category = fmt(cg.to_dict('records'))

    # ============ 输出 ============
    data = {
        'overview': overview,
        'dates': all_dates,
        'all_stores': all_stores,
        'daily_reports': daily_reports,
        'default_daily': daily_reports[last_date],
        'weekly': weeks_data[f"{ly}-{lw}"],
        'year_data': years_data.get(default_year) if default_year else None,
        'years_data': years_data,
        'years': years_list,
        'default_year': default_year,
        'monthly': months_data[f"{ly}-{lm}"],
        'weeks_data': weeks_data,
        'months_data': months_data,
        'weeks': weeks_list,
        'months': months_list,

        # 库存统计
        'inventory': {
            'overview': inv_overview,
            'store_ranking': inv_store_rk,
            'by_store_season': inv_by_store_season,
            'by_season': inv_by_season,
            'by_category': inv_by_category,
        },
    }

    try:
        cfg = load_config()
        data['access_groups'] = cfg.get('access_groups', {})
    except Exception:
        data['access_groups'] = {}

    with open(OUTPUT_FILE, 'w', encoding='utf-8') as f:
        json.dump(data, f, ensure_ascii=False, indent=2, cls=NpEncoder)

    print(f"\n生成完成！")
    print(f"总销售额: \u00a5{overview['total_sales']:,.0f} ({overview['total_orders']}单)")
    print(f"日期: {all_dates[0]} ~ {all_dates[-1]}")
    print(f"门店: {len(all_stores)}家")

if __name__ == '__main__':
    generate()