#!/usr/bin/env python3
"""
每日数据再生脚本 — 从原始Excel文件重新生成工作台JSON
用法: python3 regenerate_jsons.py <crm_file> <erp_file> <output_dir>
"""
import sys, os, json, zipfile, xml.etree.ElementTree as ET
from collections import defaultdict
from datetime import datetime, timedelta

def main():
    crm_file = sys.argv[1] if len(sys.argv) > 1 else None
    erp_file = sys.argv[2] if len(sys.argv) > 2 else None
    out_dir = sys.argv[3] if len(sys.argv) > 3 else os.path.dirname(os.path.abspath(__file__))

    if not crm_file or not os.path.exists(crm_file):
        print(f"CRM file not found: {crm_file}")
        sys.exit(1)
    if not erp_file or not os.path.exists(erp_file):
        print(f"ERP file not found: {erp_file}")
        sys.exit(1)

    print(f"CRM: {os.path.basename(crm_file)}")
    print(f"ERP: {os.path.basename(erp_file)}")
    print(f"Output: {out_dir}")

    # ====== Shared parse helpers ======
    def parse_xlsx(path):
        zf = zipfile.ZipFile(path)
        ns = {'s': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
        ss_xml = zf.read('xl/sharedStrings.xml')
        ss_tree = ET.fromstring(ss_xml)
        strings = [''.join((t.text or '') for t in si.findall('.//s:t', ns)) for si in ss_tree.findall('.//s:si', ns)]
        
        # Auto-detect sheet: try sheet2 first (newer exports), then sheet1
        sheet_names = [f for f in zf.namelist() if f.startswith('xl/worksheets/sheet') and f.endswith('.xml')]
        sheet_names.sort()
        sheet_file = 'xl/worksheets/sheet2.xml' if 'xl/worksheets/sheet2.xml' in sheet_names else sheet_names[0]
        
        sheet_xml = zf.read(sheet_file)
        sheet_tree = ET.fromstring(sheet_xml)
        
        def cv(c):
            v = c.find('s:v', ns); t = c.get('t', ''); val = v.text if v is not None else ''
            if t == 's' and val.isdigit(): idx = int(val); val = strings[idx] if idx < len(strings) else val
            return val
        def cl(ref): return ''.join(ch for ch in ref if ch.isalpha())
        
        rows = sheet_tree.findall('.//s:row', ns)
        data = []
        for row in rows[1:]:
            cells = {cl(c.get('r')): cv(c) for c in row.findall('s:c', ns)}
            data.append(cells)
        zf.close()
        return data

    # ====== 1. Daily Activities ======
    print("Processing CRM activities...")
    crm_data = parse_xlsx(crm_file)
    daily_acts = defaultdict(lambda: {'total': 0, 'by_dept': defaultdict(int), 'by_type': defaultdict(int), 'details': []})
    
    for cells in crm_data:
        # Detect format: old=M列日期, new=H列日期
        date = (cells.get('H') or cells.get('M') or '').strip()
        dept = (cells.get('A') or '').strip()
        # New format: E=名称, F=描述, I=创建者. Old: F=名称, E=描述, K/N=人
        act_name = (cells.get('E') or cells.get('F') or '').strip()
        # Check if old format (has 跟进部门 in A-like columns)
        is_old = bool(cells.get('K'))  # old format has K column
        if is_old:
            client = cells.get('B', '').strip()
            desc = (cells.get('E', '') or '').strip()[:80]
            sales = (cells.get('K', '') or cells.get('N', '')).strip()
        else:
            client = cells.get('A', '').strip()
            desc = (cells.get('F', '') or '').strip()[:80]
            sales = (cells.get('I', '') or '').strip()
        
        if not date: continue
        d = daily_acts[date]
        d['total'] += 1
        d['by_dept'][dept] += 1
        
        if '拜访' in act_name or '拜访' in desc: typ = '拜访'
        elif '电话' in act_name or '线上' in act_name: typ = '线上跟进'
        elif '测试' in act_name: typ = '测试跟进'
        elif '采购' in act_name or '订单' in act_name: typ = '订单跟进'
        elif '回款' in act_name: typ = '回款跟进'
        else: typ = '其他跟进'
        d['by_type'][typ] += 1
        
        if len(d['details']) < 50:
            d['details'].append({'dept': dept, 'client': client[:30], 'sales': sales, 'name': act_name, 'type': typ, 'desc': desc})

    sorted_dates = sorted(daily_acts.keys(), reverse=True)[:30]
    acts_json = {
        'total_activities': sum(d['total'] for d in daily_acts.values()),
        'days': sorted_dates,
        'daily': {d: daily_acts[d] for d in sorted_dates}
    }
    with open(os.path.join(out_dir, 'daily_activities.json'), 'w', encoding='utf-8') as f:
        json.dump(acts_json, f, ensure_ascii=False)
    print(f"  → daily_activities.json: {len(sorted_dates)} days, {acts_json['total_activities']} activities")

    # ====== 2. Daily Tests & Sales Orders ======
    print("Processing ERP orders...")
    erp_data = parse_xlsx(erp_file)
    daily_tests = defaultdict(lambda: {'count': 0, 'amount': 0, 'details': []})
    daily_sales = defaultdict(lambda: {'count': 0, 'amount': 0, 'details': []})
    
    for cells in erp_data:
        # Skip summary rows
        doc_no = cells.get('A', '').strip()
        if '共' in doc_no and '个' in doc_no: continue
        
        # New format: D/F=客户, B=销售员, C=金额, G=日期, I=单据类型, U=产品名, AD=赠品
        # Old format: A=客户, B=日期, C=销售员, D=金额, F=类型, I=产品名, N=赠品
        is_new = bool(cells.get('D')) and cells.get('D','').strip() and cells.get('I','').strip()
        
        if is_new:
            client = cells.get('D', '').strip() or cells.get('F', '').strip()
            sales = cells.get('B', '').strip()
            date_str = cells.get('G', '').strip()
            order_type = cells.get('I', '').strip()
            product = cells.get('U', '').strip()
            spec = cells.get('W', '').strip()  # 规格型号
            unit = cells.get('X', '').strip()  # 销售单位
            qty = float(cells.get('Z', '0') or 0) or float(cells.get('Y', '0') or 0)  # 管装数 or 管装量
            price = float(cells.get('AE', '0') or 0) or float(cells.get('AF', '0') or 0)  # 含税单价
            is_gift = cells.get('AD', '') == '是'
            try: amt = float(cells.get('C', 0) or 0)
            except: amt = 0
        else:
            client = cells.get('A', '').strip()
            sales = cells.get('C', '').strip()
            date_str = cells.get('B', '').strip()
            order_type = cells.get('F', '').strip()
            product = cells.get('I', '').strip()
            spec = cells.get('U', '').strip()  # 客户物料规格型号
            qty = safe_float(cells.get('K', '0'))  # 销售数量
            price = safe_float(cells.get('E', '0'))  # 含税单价
            unit = cells.get('J', '').strip()  # 销售单位
            is_gift = cells.get('N', '') == '是'
            try: amt = float(cells.get('D', 0) or 0)
            except: amt = 0
        
        try:
            serial = int(float(date_str))
            dt = datetime(1899, 12, 30) + timedelta(days=serial)
            date = dt.strftime('%Y-%m-%d')
        except: date = date_str
        if not date: continue
        
        is_gift = cells.get('N', '') == '是'
        if is_gift: continue
        
        detail = {'client': client[:30], 'product': product[:50], 'amount': round(amt, 2), 'sales': sales, 'spec': spec, 'unit': unit, 'qty': round(qty, 1), 'price': round(price, 2)}
        
        if '测试销售订单' in order_type:
            d = daily_tests[date]
            d['count'] += 1; d['amount'] += amt
            if len(d['details']) < 30: d['details'].append(detail)
        else:
            d = daily_sales[date]
            d['count'] += 1; d['amount'] += amt
            if len(d['details']) < 50: d['details'].append(detail)

    st = sorted(daily_tests.keys(), reverse=True)[:30]
    ss = sorted(daily_sales.keys(), reverse=True)[:30]
    
    with open(os.path.join(out_dir, 'daily_tests.json'), 'w', encoding='utf-8') as f:
        json.dump({'days': st, 'daily': {d: daily_tests[d] for d in st}}, f, ensure_ascii=False)
    with open(os.path.join(out_dir, 'daily_sales.json'), 'w', encoding='utf-8') as f:
        json.dump({'days': ss, 'daily': {d: daily_sales[d] for d in ss}}, f, ensure_ascii=False)
    print(f"  → daily_tests.json: {len(st)} days")
    print(f"  → daily_sales.json: {len(ss)} days")

    print("\n✅ All JSONs regenerated!")

if __name__ == '__main__':
    main()