#!/usr/bin/env python3
# Live-Nowcast v2 (wetter-konditioniert) fuer EEG Strom-Signal.
# Tagesprognose = Klimatologie(month,slot) + Slot-Steigungen auf Strahlungs-/Temperatur-Anomalie (heutiges Wetter).
# Band aus KONDITIONALEN Residuen (nach Wetter) -> enger. Fusion mit Live (live.db).
# Entscheidungsstufen: gesichert 90 % und CONF_HI (Default 99 %). P50 nur Anzeige. -> Tabelle 'nowcast'.
import os, sqlite3, json, time, csv
from datetime import datetime
from zoneinfo import ZoneInfo
import pandas as pd, numpy as np

BASE=os.environ.get("BASE_DIR",".")
DB=os.environ.get("DB_PATH", os.path.join(BASE,"live.db"))
HIST=os.environ.get("HIST_CSV", os.path.join(BASE,"eeg_15min_netto.csv"))
RAD_CSV=os.environ.get("RAD_CSV", os.path.join(BASE,"eeg_wetter_cache.csv"))
TEMP_CSV=os.environ.get("TEMP_CSV", os.path.join(BASE,"eeg_wetter_temp_cache.csv"))
TZ=ZoneInfo(os.environ.get("TZ_NAME","Europe/Vienna"))
TOTAL_MEMBERS=int(os.environ.get("TOTAL_MEMBERS","23"))
FRESH=int(os.environ.get("FRESH_SEC","90"))
CONF_HI=float(os.environ.get("CONF_HI","0.99")); QHI=round(1.0-CONF_HI,4)
KW=4.0

def build_forecast_model():
    df=pd.read_csv(HIST); t=pd.to_datetime(df['zeitstempel'])
    df['date']=t.dt.strftime('%Y-%m-%d'); df['month']=t.dt.month; df['slot']=t.dt.hour*4+t.dt.minute//15
    rad=pd.read_csv(RAD_CSV).rename(columns={'strahlung_mj_m2':'rad'}); rad['date']=pd.to_datetime(rad['datum']).dt.strftime('%Y-%m-%d')
    tmp=pd.read_csv(TEMP_CSV).rename(columns={'temp_mean_c':'temp'}); tmp['date']=pd.to_datetime(tmp['datum']).dt.strftime('%Y-%m-%d')
    df=df.merge(rad[['date','rad']],on='date',how='left').merge(tmp[['date','temp']],on='date',how='left')
    clim=df.groupby(['month','slot'])['netto_kwh'].mean()
    radm=df.groupby('month')['rad'].mean(); tmpm=df.groupby('month')['temp'].mean()
    df['ra']=df['rad']-df['month'].map(radm); df['ta']=df['temp']-df['month'].map(tmpm)
    df['res']=df['netto_kwh']-df.set_index(['month','slot']).index.map(clim).astype(float).values
    def slp(g):
        X=g[['ra','ta']].values; y=g['res'].values; m=~np.isnan(X).any(1)&~np.isnan(y)
        if m.sum()<30: return (0.0,0.0)
        b,*_=np.linalg.lstsq(X[m],y[m],rcond=None); return (float(b[0]),float(b[1]))
    sl=df.groupby('slot').apply(slp,include_groups=False)
    a_rad={int(s):v[0] for s,v in sl.items()}; a_temp={int(s):v[1] for s,v in sl.items()}
    df['cres']=df['res']-df['slot'].map(a_rad)*df['ra'].fillna(0)-df['slot'].map(a_temp)*df['ta'].fillna(0)
    condq=df.groupby(['month','slot'])['cres'].quantile([QHI,0.10]).unstack()
    return dict(clim=clim,radm=radm,tmpm=tmpm,a_rad=a_rad,a_temp=a_temp,condq=condq)

def daily_val(path,col,date_iso):
    try:
        with open(path,encoding="utf-8") as f:
            for row in csv.DictReader(f):
                if str(row.get('datum',''))[:10]==date_iso: return float(row[col])
    except Exception: pass
    return None

def fc(M,month,slot,rad_t,temp_t):
    m=float(M['clim'].get((month,slot),0.0))
    ra=(rad_t-float(M['radm'].get(month,0.0))) if rad_t is not None else 0.0
    ta=(temp_t-float(M['tmpm'].get(month,0.0))) if temp_t is not None else 0.0
    m=m+M['a_rad'].get(slot,0.0)*ra+M['a_temp'].get(slot,0.0)*ta
    cq=M['condq']; key=(month,slot)
    g90=m+(float(cq.loc[key,0.10]) if key in cq.index else 0.0)
    ghi=m+(float(cq.loc[key,QHI])  if key in cq.index else 0.0)
    return m,g90,ghi

def shares(con):
    try:
        d=pd.read_sql("SELECT mitglied,e_wh_final FROM intervalle",con)
        if len(d):
            s=d.groupby('mitglied')['e_wh_final'].apply(lambda x:float(np.abs(x).mean())); tot=s.sum()
            if tot>0: return (s/tot).to_dict()
    except Exception: pass
    return {}

def run_once(con,M):
    now=datetime.now(TZ); t0=now.replace(minute=(now.minute//15)*15,second=0,microsecond=0)
    frac=min(max((now-t0).total_seconds()/900.0,1e-3),1.0); slot=t0.hour*4+t0.minute//15
    today=t0.date().isoformat()
    rad_t=daily_val(RAD_CSV,'strahlung_mj_m2',today); temp_t=daily_val(TEMP_CSV,'temp_mean_c',today)
    m,g90,ghi=fc(M,t0.month,slot,rad_t,temp_t)
    live=pd.read_sql("SELECT mitglied,e_wh FROM live WHERE updated> ?",con,params=(int(time.time())-FRESH,))
    n=len(live); sh=shares(con)
    cov=(sum(sh.get(x,0) for x in live['mitglied']) if sh else n/max(TOTAL_MEMBERS,1)); cov=min(max(cov,0.0),1.0)
    obs=(-live['e_wh'].sum()/1000.0) if n else 0.0
    sharpen=m+obs-cov*m*frac; k=1-cov*frac
    ncg90=sharpen+(g90-m)*k; ncghi=sharpen+(ghi-m)*k
    out=dict(t0=t0.isoformat(),frac=round(frac,3),n_live=n,coverage=round(cov,3),conf_hi=CONF_HI,
             wetter=1 if (rad_t is not None or temp_t is not None) else 0,
             p50=round(sharpen*KW,2),gesichert90=round(ncg90*KW,2),gesichert_hi=round(ncghi*KW,2),
             ueberschuss90=round(max(0,ncg90)*KW,2),ueberschuss_hi=round(max(0,ncghi)*KW,2),
             fc_p50=round(m*KW,2),fc_g90=round(g90*KW,2),fc_g_hi=round(ghi*KW,2),updated=int(time.time()))
    con.execute("""CREATE TABLE IF NOT EXISTS nowcast(t0 TEXT PRIMARY KEY,frac REAL,n_live INT,coverage REAL,conf_hi REAL,
        wetter INT,p50 REAL,gesichert90 REAL,gesichert_hi REAL,ueberschuss90 REAL,ueberschuss_hi REAL,
        fc_p50 REAL,fc_g90 REAL,fc_g_hi REAL,updated INT)""")
    con.execute("""INSERT OR REPLACE INTO nowcast VALUES(:t0,:frac,:n_live,:coverage,:conf_hi,:wetter,:p50,:gesichert90,
        :gesichert_hi,:ueberschuss90,:ueberschuss_hi,:fc_p50,:fc_g90,:fc_g_hi,:updated)""",out)
    con.commit(); return out

def main():
    M=build_forecast_model(); con=sqlite3.connect(DB)
    print(f"Nowcast v2 (Wetter) laeuft, CONF_HI={CONF_HI} ...")
    while True:
        try: print(json.dumps(run_once(con,M),ensure_ascii=False))
        except Exception as e: print("ERR",e)
        time.sleep(30)

if __name__=="__main__": main()