#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Baut/ergaenzt den 15-Min-Netto-Cache (eeg_15min_netto.csv) aus den Reports.
# Aufruf: python eeg_cache.py <datei.xlsx> ...   oder   python eeg_cache.py --all
import os, sys, csv, glob
from datetime import datetime
import openpyxl
DIR=os.path.dirname(os.path.abspath(__file__))
CACHE=os.path.join(DIR,"eeg_15min_netto.csv")
LOG=os.path.join(DIR,".eeg_ingested.txt")
GEN="Gesamte gemeinschaftliche Erzeugung"; CON="Gesamtverbrauch lt. Messung"
FMTS=("%d.%m.%Y %H:%M","%d.%m.%Y %H:%M:%S","%Y-%m-%d %H:%M:%S","%Y-%m-%d %H:%M")
def norm(s): return str(s or "").strip().lower().replace(" ","")
def pts(v):
    if isinstance(v,datetime): return v
    if v is None: return None
    s=str(v).strip()
    for f in FMTS:
        try: return datetime.strptime(s,f)
        except ValueError: pass
    return None
def heads(ws,scan=24):
    dr=cr=None; H=[]
    for i,r in enumerate(ws.iter_rows(min_row=1,max_row=scan,values_only=True)):
        H.append(r); lab=norm(r[0] if r else "")
        if lab=="energydirection": dr=i
        elif lab=="metercode": cr=i
    return dr,cr,H
def parse(path):
    wb=openpyxl.load_workbook(path,read_only=True,data_only=True)
    if "Energiedaten" not in wb.sheetnames: wb.close(); return {}
    ws=wb["Energiedaten"]
    try: ws.reset_dimensions()
    except Exception: pass
    dr,cr,H=heads(ws)
    if dr is None or cr is None: wb.close(); return {}
    drow=H[dr]; crow=H[cr]; gc=[]; cc=[]
    for c in range(1,len(crow)):
        code=str(crow[c] or ""); d=norm(drow[c]) if c<len(drow) else ""
        if d=="generation" and code.startswith(GEN): gc.append(c)
        elif d=="consumption" and code.startswith(CON): cc.append(c)
    out={}
    for r in ws.iter_rows(min_row=cr+2,values_only=True):
        if not r: continue
        ts=pts(r[0])
        if ts is None: continue
        n=len(r); g=0.0; v=0.0
        for c in gc:
            if c<n and isinstance(r[c],(int,float)): g+=r[c]
        for c in cc:
            if c<n and isinstance(r[c],(int,float)): v+=r[c]
        out[ts]=[g,v]
    wb.close(); return out
def main():
    args=sys.argv[1:]
    if not args: print("Aufruf: python eeg_cache.py <datei.xlsx> ... | --all"); return
    if args==["--all"]:
        args=sorted(glob.glob(os.path.join(DIR,"RC100418_*T*.xlsx"))+glob.glob(os.path.join(DIR,"Report_*.xlsx")))
    cache={}
    if os.path.exists(CACHE):
        with open(CACHE,newline="") as fh:
            rd=csv.reader(fh); next(rd,None)
            for row in rd:
                if len(row)>=3: cache[row[0]]=[float(row[1]),float(row[2])]
    done=set()
    if os.path.exists(LOG): done=set(x for x in open(LOG).read().split("\n") if x)
    for f in args:
        p=f if os.path.isabs(f) else os.path.join(DIR,f); b=os.path.basename(p)
        if b in done: print("  skip "+b); continue
        if not os.path.exists(p): print("  NICHT GEFUNDEN "+b); continue
        rec=parse(p); add=0
        for ts,val in rec.items():
            k=ts.strftime("%Y-%m-%d %H:%M:%S")
            if k not in cache: cache[k]=val; add+=1
        done.add(b); print("  "+b+": "+str(len(rec))+" Intervalle, "+str(add)+" neu")
    with open(CACHE,"w",newline="") as fh:
        w=csv.writer(fh); w.writerow(["zeitstempel","erzeugung_kwh","verbrauch_kwh","netto_kwh"])
        for ts,gv in sorted(cache.items()):
            g,v=gv; w.writerow([ts,round(g,4),round(v,4),round(g-v,4)])
    with open(LOG,"w") as fh: fh.write("\n".join(sorted(done)))
    print("Cache gesamt: "+str(len(cache))+" Intervalle")
if __name__=="__main__": main()