#!/usr/bin/env python3
"""
SupplierPulse QAF-Vergleich Engine (MVP)
Reproduces the 'QAF Vergleich' per-Reiter output from any two BMW QAF workbooks
(Basis vs RePricing), then adds context/pattern recognition:
  - driver-diff: which master INPUT rates changed
  - classify each metric delta as SYSTEMATIC (matches a global lever) vs PART-SPECIFIC
Usage: python3 qaf_engine.py basis.xlsm repricing.xlsm
"""
import sys, json, warnings, statistics
from collections import defaultdict, Counter
import openpyxl
from openpyxl.utils import column_index_from_string as CI
warnings.filterwarnings("ignore")

# the 11 output positions: (cell-col, row, headline, bereich)
METRICS = [
    ('W',  'Material total',          'Material'),
    ('Y',  'Raw material surcharge',  'Material'),
    ('BB', 'Manufacturing total',     'Manufacturing'),
    ('AS', 'Personnel (Lohn)',        'Manufacturing'),
    ('AT', 'Machine',                 'Manufacturing'),
    ('AV', 'FEK',                     'Manufacturing'),
    ('BD', 'Scrap (Mfg)',             'Manufacturing'),
    ('BF', 'HK (Total prod. costs)',  'HK'),
    ('DI', 'Surcharges',              'Surcharges'),
    ('DJ', 'Quotation Price',         'Price'),
    ('DG', 'Total Costs',             'Total Costs'),
]
ROW = 41
# AS41/AT41/AV41 etc -> but note: in row41 these are the SUMMARY cells (Personnel sum etc),
# different from the per-component AS15(=Ineff) etc. Output uses row41 cells.

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

def load(fn):
    return openpyxl.load_workbook(fn, data_only=True, read_only=True)

def part_name(ws):
    return ws['C15'].value or ''

def metrics_for(ws):
    out={}
    for col,head,ber in METRICS:
        out[head]=num(ws.cell(ROW, CI(col)).value)
    return out

def driver_for(ws):
    """per-tab cost drivers from component row 15"""
    g=lambda c: num(ws.cell(15, CI(c)).value)
    return {'machine_rate':g('AT'),'ineff':g('AS'),'labour_rate':g('AR'),
            'cycle':g('AO'),'ppc':g('AP')}

def input_card(wb):
    ws=wb['INPUT']; card={}
    for r in range(20,60):
        lab=ws.cell(r,2).value
        val=ws.cell(r,3).value
        if lab not in (None,'') :
            card[f"C{r}:{str(lab).strip()[:40]}"]=val
    return card

def main(fb, fr):
    B=load(fb); R=load(fr)
    common=[s for s in B.sheetnames if s.endswith('_2') and s in R.sheetnames
            and s not in ('SBM_Matrix',)]
    rows=[]; machine_ratios=[]; tab_drivers={}
    for t in common:
        wb=B[t]; wr=R[t]
        pn=part_name(wb) or part_name(wr)
        mb=metrics_for(wb); mr=metrics_for(wr)
        db=driver_for(wb); dr=driver_for(wr); tab_drivers[t]=(db,dr)
        if db['machine_rate'] and dr['machine_rate']:
            machine_ratios.append(dr['machine_rate']/db['machine_rate'])
        for col,head,ber in METRICS:
            b=mb[head]; rr=mr[head]
            if b is None and rr is None: continue
            d = (rr-b) if (b is not None and rr is not None) else None
            dp = (d/b*100) if (d is not None and b not in (None,0)) else None
            rows.append(dict(reiter=t,part=pn,cell=f"{col}{ROW}",headline=head,
                             bereich=ber,basis=b,repricing=rr,delta=d,delta_pct=dp))
    # global machine lever
    glob_ratio = statistics.median(machine_ratios) if machine_ratios else None
    # classify machine deltas
    for r in rows:
        r['flag']=''
    for t,(db,dr) in tab_drivers.items():
        if db['machine_rate'] and dr['machine_rate'] and glob_ratio:
            rt=dr['machine_rate']/db['machine_rate']
            if abs(rt-glob_ratio)/glob_ratio>0.05:   # deviates >5% from global lever
                for r in rows:
                    if r['reiter']==t and r['headline'] in ('Machine','Manufacturing total','FEK','HK (Total prod. costs)','Quotation Price','Total Costs'):
                        r['flag']='PART-SPECIFIC (machine rate deviates from global lever)'
    return rows, glob_ratio, input_card(B), input_card(R), len(common)

if __name__=='__main__':
    fb,fr=sys.argv[1],sys.argv[2]
    rows,glob,cb,cr,n=main(fb,fr)
    # rate-card diff
    print("="*70); print("MASTER INPUT RATE-CARD CHANGES (global levers)"); print("="*70)
    for k in cb:
        a,b=cb.get(k),cr.get(k)
        try:
            if a is not None and b is not None and abs(float(a)-float(b))>1e-9:
                chg=f"{(float(b)/float(a)-1)*100:+.0f}%" if float(a)!=0 else "n/a"
                print(f"  {k:46} {str(a)[:9]:>9} -> {str(b)[:9]:<9} {chg}")
        except:
            if a!=b: print(f"  {k:46} {str(a)[:9]:>9} -> {str(b)[:9]:<9}")
    print(f"\nGlobal machine-rate lever (median ratio): x{glob:.3f} = {(glob-1)*100:+.0f}%")
    print(f"Reiter compared: {n}   output rows: {len(rows)}")
    # part-specific flags
    flagged=sorted({r['reiter'] for r in rows if r['flag']})
    print(f"\nPART-SPECIFIC anomalies (deviate from global machine lever): {flagged}")
    # validation sample
    print("\nVALIDATION sample (01_2, 06_2):")
    for r in rows:
        if r['reiter'] in ('01_2','06_2') and r['headline'] in ('Machine','Quotation Price'):
            print(f"  {r['reiter']:7} {r['headline']:16} basis={r['basis']:.4f} repr={r['repricing']:.4f} d%={r['delta_pct']:+.1f} {r['flag']}")
    # dump full csv
    import csv
    with open('qaf_vergleich_out.csv','w',newline='') as f:
        w=csv.DictWriter(f,fieldnames=list(rows[0].keys())); w.writeheader(); w.writerows(rows)
    print("\nWrote qaf_vergleich_out.csv")