#!/usr/bin/env python3
import json, openpyxl
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.drawing.image import Image as XLImage
d=json.load(open("deep.json"))
B=d['prog']['basis']; R=d['prog']['repr']
wb=openpyxl.load_workbook("QAF_Vergleich_G60_DP.xlsx")
HEAD=PatternFill('solid',fgColor='037493'); HF=Font(color='FFFFFF',bold=True)
RED=PatternFill('solid',fgColor='F8C9C9'); GRN=PatternFill('solid',fgColor='CDEBD3')
def head(ws,cells):
    ws.append(cells)
    for c in range(1,len(cells)+1): ws.cell(ws.max_row,c).fill=HEAD; ws.cell(ws.max_row,c).font=HF

# --- Kostenstruktur ---
ks=wb.create_sheet("Kostenstruktur")
head(ks,["Bucket","Basis €","Basis %","RePricing €","RePricing %","Delta €","Delta %"])
order=['Material','Labor','Manufacturing','ScrapB','SGA','Profit']
nm={'Material':'Material','Labor':'Labor','Manufacturing':'Manufacturing','ScrapB':'Scrap','SGA':'SG&A','Profit':'Profit'}
for k in order:
    ks.append([nm[k],B[k],B['_pct'][k]/100,R[k],R['_pct'][k]/100,R[k]-B[k],(R[k]/B[k]-1) if B[k] else None])
    rr=ks.max_row
    for cc in (2,4,6): ks.cell(rr,cc).number_format='0.00'
    for cc in (3,5): ks.cell(rr,cc).number_format='0%'
    ks.cell(rr,7).number_format='+0.0%;-0.0%'
    ks.cell(rr,7).fill=RED if R[k]>B[k] else GRN
ks.append(["Sales (Angebotspreis)",B['Sales'],1,R['Sales'],1,R['Sales']-B['Sales'],(R['Sales']/B['Sales']-1)])
rr=ks.max_row
for cc in (2,4,6): ks.cell(rr,cc).number_format='0.00'
for cc in (3,5): ks.cell(rr,cc).number_format='0%'
ks.cell(rr,7).number_format='+0.0%;-0.0%'
for c in range(1,8): ks.cell(rr,c).font=Font(bold=True)
for col,w in zip("ABCDEFG",[22,11,9,12,11,10,9]): ks.column_dimensions[col].width=w
try: ks.add_image(XLImage("c1_kostenstruktur.png"),"I2"); ks.add_image(XLImage("c2_waterfall.png"),"I24")
except Exception as e: print("img1",e)

# --- Produktion ---
pr=wb.create_sheet("Produktion")
head(pr,["Reiter","Bauteil","Cycle [s] B","Cycle [s] R","#MA B","#MA R","Scrap/Step B","Scrap/Step R","MachRate B","MachRate R","Ineff B","Ineff R"])
for p in d['prod']:
    pr.append([p['t'],p['part'],p['cyc_b'],p['cyc_r'],p['emp_b'],p['emp_r'],
               p['scr_b'],p['scr_r'],p['mr_b'],p['mr_r'],p['inef_b'],p['inef_r']])
    rr=pr.max_row
    for cc in (7,8,11,12): pr.cell(rr,cc).number_format='0%'
    # highlight changed cycle/employees
    if p['cyc_b']!=p['cyc_r']: pr.cell(rr,4).fill=RED
    if (p['emp_b'] or 0)!=(p['emp_r'] or 0): pr.cell(rr,6).fill=RED
for col,w in zip("ABCDEFGHIJKL",[10,22,10,10,7,7,11,11,10,10,8,8]): pr.column_dimensions[col].width=w
pr.freeze_panes="C2"
try: pr.add_image(XLImage("c4_produktion.png"),"N2")
except Exception as e: print("img4",e)

# --- Einsparung (Jahre) ---
es=wb.create_sheet("Einsparung (Jahre)")
es.append([f"Δ je Fahrzeugsatz: {d['dset']:.2f} €  (Basis {d['set_b']:.2f} → RePr {d['set_r']:.2f})"])
es.append([])
head(es,["Jahr","Volumen","Mehrkosten €","Mehrkosten M€","kumuliert M€"])
cum=0
for y,v,a in zip(d['years'],d['volume'],d['annual']):
    cum+=a/1e6
    es.append([y,v,a,a/1e6,cum])
    rr=es.max_row
    es.cell(rr,2).number_format='#,##0'; es.cell(rr,3).number_format='#,##0'
    es.cell(rr,4).number_format='0.00'; es.cell(rr,5).number_format='0.0'
es.append(["Σ Laufzeit",sum(d['volume']),sum(d['annual']),sum(d['annual'])/1e6,cum])
rr=es.max_row
es.cell(rr,2).number_format='#,##0'; es.cell(rr,3).number_format='#,##0'
es.cell(rr,4).number_format='0.0'; es.cell(rr,5).number_format='0.0'
for c in range(1,6): es.cell(rr,c).font=Font(bold=True)
es.append([]); es.append(["Hinweis: Obergrenze (Annahme alle Positionen je Fahrzeug). Variantenscharf in der App niedriger."])
for col,w in zip("ABCDE",[12,12,14,13,12]): es.column_dimensions[col].width=w
try: es.add_image(XLImage("c5_jahre.png"),"G2")
except Exception as e: print("img5",e)

wb.save("QAF_Vergleich_G60_DP.xlsx")
print("Excel extended: + Kostenstruktur, Produktion, Einsparung (Jahre)")
print("sheets:",wb.sheetnames)