#!/usr/bin/env python3
"""Deep extraction v2: 7-bucket cost structure, production drivers, annual impact."""
import json, re, statistics, warnings
import openpyxl
from openpyxl.utils import column_index_from_string as CI
warnings.filterwarnings("ignore")
MASTERS={'SBM_Matrix','SBM_Dropdown','Preise 02','Serie 02'}

def n(v):
    try: return float(v)
    except: return None

def natkey(s):
    m=re.match(r'(\d+)',s); return (int(m.group(1)) if m else 999, s)

def load(fn):
    wb=openpyxl.load_workbook(fn,data_only=True)
    # INPUT surcharge rates
    I=wb['INPUT']
    rate=lambda r: n(I.cell(r,3).value) or 0
    rates=dict(ovFK_d=rate(22),ovMAT_d=rate(23),pfFK_d=rate(24),pfMAT_d=rate(25),
               ovFK_s=rate(26),ovMAT_s=rate(27),pfFK_s=rate(28),pfMAT_s=rate(29))
    # annual volumes (read FIRST, before detail loop)
    S=wb['Stückzahlen']
    years=[S.cell(4,c).value for c in range(3,12)]
    vol_total=[n(S.cell(5,c).value) for c in range(3,12)]
    dets=sorted([s for s in wb.sheetnames if '_2' in s and s not in MASTERS],key=natkey)
    out={}
    for t in dets:
        ws=wb[t]
        g=lambda col,r: n(ws.cell(r,CI(col)).value)
        rec={'part':ws['C15'].value or ''}
        # row41 totals
        for col,k in [('W','W'),('Y','Y'),('AS','Pers'),('AT','Mach'),('BB','MfgTot'),
                      ('BD','Scrap'),('BF','HK'),('DI','Sur'),('DJ','Sales'),('DG','TC')]:
            rec[k]=n(ws.cell(41,CI(col)).value) or 0
        # SG&A / Profit split by recomputing per component (rows 15-38, currency L=EUR)
        sga=prof=0.0
        steps=[]   # production steps
        for r in range(15,39):
            L=ws.cell(r,CI('L')).value
            AX=g('AX',r) or 0; W=g('W',r) or 0
            E=str(ws.cell(r,CI('E')).value or '').lower()
            if L=='EUR':
                if E=='directed':
                    sga+= AX*rates['ovFK_d'] + W*rates['ovMAT_d']
                    prof+=AX*rates['pfFK_d'] + W*rates['pfMAT_d']
                else:
                    sga+= AX*rates['ovFK_s'] + W*rates['ovMAT_s']
                    prof+=AX*rates['pfFK_s'] + W*rates['pfMAT_s']
            # production step = row with cycle time AO>0
            ao=g('AO',r)
            if ao and ao>0:
                steps.append(dict(row=r,cycle=ao,ppc=g('AP',r),emp=g('AQ',r),
                                  ineff=g('AS',r),machrate=g('AT',r),scrapstep=g('BC',r)))
        rec['SGA']=sga; rec['Profit']=prof; rec['steps']=steps
        # buckets
        rec['Material']=rec['W']+rec['Y']
        rec['Labor']=rec['Pers']
        rec['Manufacturing']=rec['MfgTot']-rec['Pers']   # machine + remaining mfg
        rec['ScrapB']=rec['Scrap']
        out[t]=rec
    wb.close()
    return out, rates, years, vol_total

B,RB,yrs,volB = load("basis_QAF.xlsm")
R,RR,yrsR,volR = load("repricing_QAF.xlsm")
common=[t for t in B if t in R]

# validate SG&A+Profit ~ DI(Sur)
val=[]
for t in common[:10]:
    di=R[t]['Sur']; sp=R[t]['SGA']+R[t]['Profit']
    if di>0.01: val.append(abs(sp-di)/di)
print("SG&A+Profit vs Surcharges mean rel.err (first10):",round(statistics.mean(val),4) if val else "n/a")

# program 7-bucket aggregate
def agg(D,key): return sum(D[t][key] for t in common)
buckets=['Material','Labor','Manufacturing','ScrapB','SGA','Profit']
prog={}
for src,D in [('basis',B),('repr',R)]:
    tot=agg(D,'Sales')
    prog[src]=dict(Sales=tot, **{b:agg(D,b) for b in buckets})
    prog[src]['_pct']={b:agg(D,b)/tot*100 for b in buckets}
print("\n=== PROGRAM 7-BUCKET (€ per vehicle-set, Σ all positions) ===")
print(f"{'bucket':14} {'basis €':>9} {'%':>6}   {'repr €':>9} {'%':>6}   Δ%")
for b in buckets+['Sales']:
    pb=prog['basis'][b]; pr=prog['repr'][b]
    sb=prog['basis']['_pct'].get(b); sr=prog['repr']['_pct'].get(b)
    d=(pr/pb-1)*100 if pb else 0
    print(f"{b:14} {pb:9.2f} {('%.0f%%'%sb) if sb else '':>6}   {pr:9.2f} {('%.0f%%'%sr) if sr else '':>6}   {d:+.1f}%")

# production driver summary (primary step = max machrate row per tab)
def primary(rec):
    s=[x for x in rec['steps'] if x.get('machrate')]
    return max(s,key=lambda x:x['machrate'] or 0) if s else (rec['steps'][0] if rec['steps'] else None)
prod=[]
for t in common:
    pb=primary(B[t]); pr=primary(R[t])
    if pb and pr:
        prod.append(dict(t=t,part=B[t]['part'],
            cyc_b=pb['cycle'],cyc_r=pr['cycle'],
            emp_b=pb['emp'],emp_r=pr['emp'],
            scr_b=pb['scrapstep'],scr_r=pr['scrapstep'],
            mr_b=pb['machrate'],mr_r=pr['machrate'],
            inef_b=pb['ineff'],inef_r=pr['ineff']))
print(f"\nProduction steps captured: {len(prod)} tabs")
# how many changed cycle time / employees
cyc_ch=sum(1 for p in prod if p['cyc_b']!=p['cyc_r'])
emp_ch=sum(1 for p in prod if (p['emp_b'] or 0)!=(p['emp_r'] or 0))
scr=[(p['scr_b'],p['scr_r']) for p in prod]
print(f"  cycle-time changed: {cyc_ch}/{len(prod)} | #employees changed: {emp_ch}/{len(prod)}")
print(f"  scrap/step basis set: {sorted(set(round(p['scr_b'],3) for p in prod if p['scr_b'] is not None))}"
      f" -> repr set: {sorted(set(round(p['scr_r'],3) for p in prod if p['scr_r'] is not None))}")
print(f"  inefficiency basis: {sorted(set(p['inef_b'] for p in prod if p['inef_b'] is not None))}"
      f" -> repr: {sorted(set(p['inef_r'] for p in prod if p['inef_r'] is not None))}")

# annual impact: per-set Δ price × annual volume (illustrative upper bound, all positions)
set_b=prog['basis']['Sales']; set_r=prog['repr']['Sales']; dset=set_r-set_b
years=[int(y) for y in yrs if y]
volume=[v or 0 for v in volR][:len(years)]
annual=[dset*v for v in volume]
print(f"\nPer-set price: {set_b:.2f} -> {set_r:.2f}  Δ={dset:.2f} €/set")
print("Annual added cost (illustrative, all positions × volume):")
for y,v,a in zip(years,volume,annual):
    print(f"  {y}: vol={v:,.0f}  Mehrkosten={a/1e6:.2f} M€")
print(f"  TOTAL runtime: vol={sum(volume):,.0f}  Mehrkosten={sum(annual)/1e6:.1f} M€")

json.dump(dict(prog=prog,prod=prod,years=years,volume=volume,annual=annual,
               set_b=set_b,set_r=set_r,dset=dset,buckets=buckets,
               common=common,
               sales_by_tab={t:{'b':B[t]['Sales'],'r':R[t]['Sales'],'part':B[t]['part']} for t in common}),
          open("deep.json","w"))
print("\nwrote deep.json")