#!/usr/bin/env python3
"""汇总三个系统冰激淋+饮料月度数据，生成柱状图"""
import openpyxl, json
from collections import defaultdict
from datetime import datetime

fp = '/mnt/c/Users/kingw/Desktop/自助机项目成立至2026年5月31日完全统计表.xlsx'
wb = openpyxl.load_workbook(fp, data_only=True)

# Parse helper: get month columns from row 1 (header)
def parse_sheet(ws, cat, dev_col_idx, month_start_col):
    """Extract monthly data from a sheet. Returns list of {month: {total, count}}"""
    monthly = defaultdict(lambda: {'total': 0.0, 'count': 0})
    
    # Read header to get month labels
    headers = []
    for col in range(month_start_col, ws.max_column + 1):
        h = ws.cell(row=1, column=col).value
        if h and h != '总计' and h != '备注':
            headers.append((col, str(h)[:7]))  # e.g., "2025-01"
    
    # Read data rows
    for row_idx in range(2, ws.max_row + 1):
        for col, month_label in headers:
            val = ws.cell(row=row_idx, column=col).value
            if val and val != '-' and val != 0:
                try:
                    amt = float(val)
                except:
                    continue
                if amt > 0:
                    monthly[month_label]['total'] += amt
                    monthly[month_label]['count'] += 1
    
    return dict(monthly)

# Parse all sheets
all_data = {
    'ice_cream': defaultdict(lambda: {'total': 0.0, 'count': 0}),
    'beverage': defaultdict(lambda: {'total': 0.0, 'count': 0}),
}

# 2.0系统冰激淋: col A=地区, B=设备名, C-Q=monthly (2025-11 to 2026-05)
ws = wb['2.0系统冰激淋']
data = parse_sheet(ws, 'ice', 2, 3)
for m, v in data.items():
    all_data['ice_cream'][m]['total'] += v['total']
    all_data['ice_cream'][m]['count'] += v['count']

# 2.0系统 饮料机: col A=地区, B=设备名, C-K=monthly (2025-11 to 2026-05), L=总计, M=备注
ws = wb['2.0系统 饮料机']
data = parse_sheet(ws, 'bev', 2, 3)
for m, v in data.items():
    all_data['beverage'][m]['total'] += v['total']
    all_data['beverage'][m]['count'] += v['count']

# 1.0系统冰激淋: col A=地区, B=设备名, C-R=monthly (2025-02 to 2026-05), S=总计
ws = wb['1.0系统冰激淋']
data = parse_sheet(ws, 'ice', 2, 3)
for m, v in data.items():
    all_data['ice_cream'][m]['total'] += v['total']
    all_data['ice_cream'][m]['count'] += v['count']

# 1.0系统饮料数据: col A=设备名, B-F=monthly (2026-01 to 2026-05), G=总计
ws = wb['1.0系统饮料数据']
data = parse_sheet(ws, 'bev', 1, 2)
for m, v in data.items():
    all_data['beverage'][m]['total'] += v['total']
    all_data['beverage'][m]['count'] += v['count']

# 海逸冰激淋: col A=地区, B=编号, C=设备名, D-AA=monthly (2024-06 to 2026-05), AB=总计
ws = wb['海逸冰激淋']
data = parse_sheet(ws, 'ice', 3, 4)
for m, v in data.items():
    all_data['ice_cream'][m]['total'] += v['total']
    all_data['ice_cream'][m]['count'] += v['count']

# Compute combined
combined = defaultdict(lambda: {'total': 0.0, 'count': 0})
for m in set(list(all_data['ice_cream'].keys()) + list(all_data['beverage'].keys())):
    combined[m]['total'] = all_data['ice_cream'].get(m, {}).get('total', 0) + all_data['beverage'].get(m, {}).get('total', 0)
    combined[m]['count'] = all_data['ice_cream'].get(m, {}).get('count', 0) + all_data['beverage'].get(m, {}).get('count', 0)

# Filter to 2025-01 to 2026-05
target_months = []
for y in [2025, 2026]:
    end_m = 13 if y == 2025 else 6  # 2025 full year, 2026 up to May
    for m in range(1, end_m):
        target_months.append(f'{y}-{m:02d}')

def build_monthly_series(data_dict):
    rows = []
    for m in target_months:
        d = data_dict.get(m, {'total': 0.0, 'count': 0})
        total = round(d['total'], 2)
        count = d['count']
        avg = round(total / count, 2) if count > 0 else 0
        rows.append({'month': m, 'total': total, 'count': count, 'avg': avg})
    return rows

ice_series = build_monthly_series(all_data['ice_cream'])
bev_series = build_monthly_series(all_data['beverage'])
comb_series = build_monthly_series(combined)

# Save to JSON for charting
with open('/tmp/monthly_summary.json', 'w') as f:
    json.dump({
        'ice_cream': ice_series,
        'beverage': bev_series,
        'combined': comb_series
    }, f, ensure_ascii=False)

# Print summary
for label, series in [('🍦冰激淋', ice_series), ('🥤饮料', bev_series), ('📊合计', comb_series)]:
    print(f'\n{label}:')
    print(f'  {"月份":<10} {"销售额":>10} {"机器数":>6} {"单机月均":>10}')
    total_all = 0
    for r in series:
        print(f'  {r["month"]:<10} {r["total"]:>10,.0f} {r["count"]:>6} {r["avg"]:>10,.0f}')
        total_all += r['total']
    print(f'  {"累计":<10} {total_all:>10,.0f}')

print('\nData saved to /tmp/monthly_summary.json')