import openpyxl
import json
from collections import defaultdict

print("Loading workbook...")
wb = openpyxl.load_workbook('/tmp/RefSource_sync.xlsx', data_only=True, read_only=True)

# ── WhiteList ──────────────────────────────────────────────────────────────────
ws_wl = wb['WhiteList']
# header row 4, data from row 5
# Indices: [31]=Seller, [37]=Factory, [10]=Buyer/Obligor, [18]=S&P, [21]=Country, [22]=Sector, [14]=CL, [15]=AP

wl_raw = defaultdict(lambda: defaultdict(dict))  # seller→factory→buyer→info

for row in ws_wl.iter_rows(min_row=5, values_only=True):
    if not row or len(row) <= 37: continue
    seller  = row[31]
    factory = row[37]
    buyer   = row[10]
    cr      = row[18]
    country = row[21]
    sector  = row[22]
    cl      = row[14]
    ap      = row[15]

    if not seller or not buyer: continue
    s = str(seller).strip()
    b = str(buyer).strip()
    if s in ('--','','None') or b in ('--','','None'): continue

    f = str(factory).strip() if factory and str(factory).strip() not in ('','None') else '—'
    
    cr_s      = str(cr).strip()      if cr      and str(cr).strip()      not in ('--','','None') else '—'
    country_s = str(country).strip() if country and str(country).strip() not in ('--','','None') else '—'
    sector_s  = str(sector).strip()  if sector  and str(sector).strip()  not in ('--','','None') else '—'
    cl_v      = float(cl) if isinstance(cl,(int,float)) else 0.0
    ap_v      = float(ap) if isinstance(ap,(int,float)) else 0.0

    existing = wl_raw[s][f].get(b)
    if existing is None:
        wl_raw[s][f][b] = {'cr': cr_s, 'country': country_s, 'sector': sector_s, 'cl': cl_v, 'ap': ap_v}
    else:
        # Take first non-missing values; for CL/AP aggregate
        if existing['cr'] == '—' and cr_s != '—': existing['cr'] = cr_s
        if existing['country'] == '—' and country_s != '—': existing['country'] = country_s
        if existing['sector'] == '—' and sector_s != '—': existing['sector'] = sector_s
        existing['cl'] = max(existing['cl'], cl_v)  # take max CL
        existing['ap'] += ap_v  # sum AP across records

print(f"WL: {len(wl_raw)} sellers loaded")

# ── AllFundsHistory ────────────────────────────────────────────────────────────
ws_afh = wb['AllFundsHistory']
# header row 11, data from row 12
# Indices: [4]=Seller, [5]=Buyer/Debtor, [19]=Date purchased AR, [20]=Amount purchased, [22]=Days purchased to due date

afh_raw = defaultdict(lambda: {'buyers': [], 'buyer_idx': {}, 'tx': []})

for row in ws_afh.iter_rows(min_row=12, values_only=True):
    if not row or len(row) <= 22: continue
    seller = row[4]
    buyer  = row[5]
    amount = row[20]
    date_p = row[19]
    days   = row[22]

    if not seller or not buyer or amount is None: continue
    s = str(seller).strip()
    b = str(buyer).strip()
    if s in ('None','') or b in ('None',''): continue

    try: amt = float(amount)
    except: continue
    if amt <= 0: continue

    # Date as YYYYMMDD int
    if hasattr(date_p, 'year'):
        di = date_p.year*10000 + date_p.month*100 + date_p.day
    elif isinstance(date_p, str) and len(date_p) >= 10:
        try: di = int(date_p[:4])*10000 + int(date_p[5:7])*100 + int(date_p[8:10])
        except: continue
    else:
        continue  # skip rows without valid date

    # Days
    try: ds = int(round(float(days))) if days is not None and float(days) > 0 else 0
    except: ds = 0

    d = afh_raw[s]
    if b not in d['buyer_idx']:
        d['buyer_idx'][b] = len(d['buyers'])
        d['buyers'].append(b)
    bi = d['buyer_idx'][b]
    d['tx'].append([bi, di, round(amt, 2), ds])

print(f"AFH: {len(afh_raw)} sellers, {sum(len(v['tx']) for v in afh_raw.values())} transactions")

# ── Build combined seller_data ─────────────────────────────────────────────────
all_sellers = set(wl_raw.keys()) | set(afh_raw.keys())
seller_data = {}

for s in sorted(all_sellers):
    wl = wl_raw.get(s, {})
    afh = afh_raw.get(s, {'buyers': [], 'buyer_idx': {}, 'tx': []})

    # Build WL factories structure
    factories = []
    total_cl = 0.0
    total_ap = 0.0
    for factory, buyers_dict in wl.items():
        buyer_list = []
        for buyer_name, info in buyers_dict.items():
            buyer_list.append({
                'name': buyer_name,
                'cr': info['cr'],
                'country': info['country'],
                'sector': info['sector'],
                'cl': round(info['cl'], 2),
                'ap': round(info['ap'], 2),
            })
            total_cl += info['cl']
            total_ap += info['ap']
        factories.append({'loc': factory, 'buyers': buyer_list})

    seller_data[s] = {
        'wl': {
            'factories': factories,
            'totalCL': round(total_cl, 2),
            'totalAP': round(total_ap, 2),
        },
        'afh': {
            'buyers': afh['buyers'],
            'tx': afh['tx'],  # [buyer_idx, date_yyyymmdd, amount, days]
        }
    }

# Save JSON
out_path = '/tmp/inquiry_seller_data.json'
with open(out_path, 'w', encoding='utf-8') as f:
    json.dump(seller_data, f, ensure_ascii=False, separators=(',',':'))

import os
size = os.path.getsize(out_path)
print(f"Saved to {out_path} ({size/1024:.1f} KB)")
print("Sample sellers:")
for s in list(seller_data.keys())[:3]:
    d = seller_data[s]
    n_buyers_wl = sum(len(f['buyers']) for f in d['wl']['factories'])
    n_buyers_afh = len(d['afh']['buyers'])
    n_tx = len(d['afh']['tx'])
    print(f"  {s}: WL={n_buyers_wl} buyers, AFH={n_buyers_afh} buyers, {n_tx} tx")