#!/usr/bin/env python3
"""掃描 November 2026 (202611) ARs maturity list 並更新 state"""
import subprocess, os, json, openpyxl, re, sys
from collections import defaultdict
from datetime import datetime, timezone, timedelta

HKT = timezone(timedelta(hours=8))
GOG_ENV = {**os.environ,
    'GOG_KEYRING_PASSWORD': 'sigi_gog_2026',
    'GOG_ACCOUNT': 'derekchen@siegfriedfund.com'}

STATE = '/root/.openclaw-cooper/workspace/skills/vteam-everyday/data/veday_state.json'

def gog(*args):
    return subprocess.run(['gog','--client','derek']+list(args),
        env=GOG_ENV, capture_output=True, text=True)

def log(m):
    print(f'[NOV_SCAN {datetime.now(HKT).strftime("%H:%M HKT")}] {m}')

def main():
    ym = '202611'
    log(f'Scanning for November data: {ym}')

    r = gog('drive', 'search', f'maturity list {ym}', '--json')
    try:
        data = json.loads(r.stdout)
        files = data.get('files', [])
    except Exception as e:
        log(f'ERROR parsing search: {e}')
        log(f'stderr: {r.stderr[:200]}')
        sys.exit(1)

    FUND_MAP = {'SCP2':'B','SCP5':'C','SCP6-1':'D','SCP6-2':'E'}
    found = {}
    for f in files:
        name = f.get('name','')
        for fund in FUND_MAP:
            if fund in name and 'maturity list' in name.lower() and ym in name:
                if fund not in found:
                    found[fund] = f
                    log(f'Found: {name} (id: {f["id"]})')

    log(f'Funds found: {list(found.keys())}')

    COL = {'due_date':8,'fixed_due':9,'maturity':12,'wire_date':18}
    updates = {}

    for fund, finfo in found.items():
        path = f'/tmp/veday_nov_{fund}.xlsx'
        dl_result = gog('drive', 'download', finfo['id'], '--output', path)
        if dl_result.returncode != 0:
            log(f'ERROR downloading {fund}: {dl_result.stderr[:100]}')
            continue
        if not os.path.exists(path):
            log(f'ERROR: file not found after download: {path}')
            continue

        wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
        ws = wb[wb.sheetnames[0]]
        rows = list(ws.iter_rows(values_only=True))
        
        # Try both fund name variants for filtering
        fund_variants = {fund}
        if fund == 'SCP6-1':
            fund_variants.add('SCP6-1')
        
        data_rows = []
        for row in rows[3:]:
            if not row or not row[0]:
                continue
            # Check if row[0] matches the fund
            cell_val = str(row[0]).strip() if row[0] else ''
            if cell_val == fund or cell_val in fund_variants:
                data_rows.append(row)
        
        log(f'{fund}: found {len(data_rows)} data rows')

        by_date = defaultdict(lambda:{'count':0,'total':0.0})
        for row in data_rows:
            dt = None
            for ci in [COL['wire_date'],COL['fixed_due'],COL['due_date']]:
                if ci < len(row) and row[ci] and hasattr(row[ci],'strftime'):
                    dt = row[ci]; break
            amt = 0.0
            if COL['maturity'] < len(row) and row[COL['maturity']]:
                try: amt = float(row[COL['maturity']])
                except: pass
            if dt and amt > 0:
                by_date[dt.strftime('%Y-%m-%d')]['count'] += 1
                by_date[dt.strftime('%Y-%m-%d')]['total'] += amt

        updates[fund] = dict(by_date)
        n = sum(v['count'] for v in by_date.values())
        t = sum(v['total'] for v in by_date.values())
        log(f'{fund}: {n}筆 USD {t:,.2f}')
        
        # Show dates found
        for d in sorted(by_date.keys())[:5]:
            log(f'  {d}: {by_date[d]["count"]}筆 USD {by_date[d]["total"]:,.2f}')

    if not updates:
        log('No November data found. Cannot update state.')
        sys.exit(1)

    # Update state
    with open(STATE) as fp:
        state = json.load(fp)
    items = state.get('items',[])

    # Remove ALL B-E items (current month October data)
    items_kept = [p for p in items if p.get('category') not in FUND_MAP]
    log(f'Kept {len(items_kept)} non-B-E items (A category), removed {len(items) - len(items_kept)} B-E items')

    # Add November data for found funds
    for fund in ['SCP2','SCP5','SCP6-1','SCP6-2']:
        if fund in updates and updates[fund]:
            by_date = updates[fund]
            for d,v in sorted(by_date.items()):
                items_kept.append({'category':fund,'description':'Reinvestment',
                    'date':d,'quantity':f"{v['count']}筆_ USD {v['total']:,.2f}",
                    'progress':'','id':'__tmp__'})
            n = sum(v['count'] for v in by_date.values())
            t = sum(v['total'] for v in by_date.values())
            items_kept.append({'category':fund,'description':'── 合計 ──',
                'date':'','quantity':f'{n}筆_ USD {t:,.2f}',
                'progress':'','id':'__total__','is_total':True})
        elif fund == 'SCP6-1':
            # SCP6-1 不在搜索結果 → 11月無交易
            items_kept.append({'category':'SCP6-1','description':'Reinvestment（11月無交易）',
                'date':'2026-11-01','quantity':'0筆_ USD 0.00',
                'progress':'','id':'__tmp__'})
            items_kept.append({'category':'SCP6-1','description':'── 合計 ──',
                'date':'','quantity':'0筆_ USD 0.00',
                'progress':'','id':'__total__','is_total':True})

    state['items'] = items_kept
    with open(STATE,'w') as fp:
        json.dump(state,fp,indent=2,ensure_ascii=False)
    log(f'State updated with November data. Total items: {len(items_kept)}')

if __name__ == '__main__':
    main()