#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Réécrit UNIQUEMENT les 7 colonnes climat de toutes les lignes FR depuis Explore2
(villes via grille, dépts = moyenne des villes, régions = moyenne des dépts).
Idempotent pour les lignes déjà correctes ; corrige les lignes saisies à la main."""
import openpyxl, netCDF4, numpy as np, json
from collections import defaultdict
CLIM="/opt/projects/covalba-refonte-assets/climat"; GEO=json.load(open("scripts/geocode-cache.json"))
def load(p,v): ds=netCDF4.Dataset(p); return ds.variables['lat'][:],ds.variables['lon'][:],ds.variables[v][0]
G={}
for lvl,d in [("20","rwl20"),("27","rwl27")]:
    G[("tx30",lvl)]=load(f"{CLIM}/{d}/TX30D_yr_RWL-{lvl}_TIMEavg_GEOxy_FR-Metro_EXPLORE2-2022_MF-ADAMONT_rcp85_ENSmax.nc","TX30D")
    G[("txm",lvl)]=load(f"{CLIM}/{d}/TXm_seas-JJA_RWL-{lvl}_TIMEavg_GEOxy_FR-Metro_EXPLORE2-2022_MF-ADAMONT_rcp85_ENSmax.nc","TXm")
    G[("txx",lvl)]=load(f"{CLIM}/{d}/TXx_lr5yr_RWL-{lvl}_TIMEavg_GEOxy_FR-Metro_EXPLORE2-2022_MF-ADAMONT_rcp85_ENSmax.nc","TXx")
def at(key,la,lo,n=4):
    lat,lon,v=G[key]; d=(lat-la)**2+(lon-lo)**2; d=np.where(np.ma.getmaskarray(v),1e9,d)
    idx=np.argsort(d.ravel())[:n]; vals=[v.ravel()[i] for i in idx if d.ravel()[i]<1e8]
    return float(np.mean(vals))
def clim(la,lo):
    t20,t27=at(("tx30","20"),la,lo),at(("tx30","27"),la,lo)
    m20,m27=at(("txm","20"),la,lo)-273.15,at(("txm","27"),la,lo)-273.15
    return {"joursSup30C":round(t20),"tempMoyEstivale":round(m20,1),"picHistorique":round(at(("txx","20"),la,lo)-273.15,1),
            "joursSup30C10ans":round((t20+t27)/2),"tempMoyEstivale10ans":round((m20+m27)/2,1),
            "joursSup30C20ans":round(t27),"tempMoyEstivale20ans":round(m27,1)}
KEYS=["joursSup30C","tempMoyEstivale","picHistorique","joursSup30C10ans","tempMoyEstivale10ans","joursSup30C20ans","tempMoyEstivale20ans"]
wb=openpyxl.load_workbook("public/documents/covalba-zones-seo-DEMO.xlsx")
zs=wb["Zones"]; hdr=[c.value for c in zs[1]]; COL={h:i+1 for i,h in enumerate(hdr)}
rows=[]
for r in range(2,zs.max_row+1):
    rows.append({h:zs.cell(row=r,column=COL[h]).value for h in hdr}|{"_row":r})
# villes
ville_clim={}
for d in rows:
    if d.get("pageType")=="ville" and (d.get("pays") or "FR")=="FR":
        sv=d.get("slugVille")
        if sv in GEO:
            c=clim(GEO[sv]["lat"],GEO[sv]["lon"]); ville_clim[sv]=c
            for k in KEYS: zs.cell(row=d["_row"],column=COL[k],value=c[k])
# dépts = moyenne des villes du dept
byd=defaultdict(list)
for d in rows:
    if d.get("pageType")=="ville" and d.get("slugVille") in ville_clim:
        byd[d.get("slugDepartement")].append(ville_clim[d["slugVille"]])
dept_clim={}
for d in rows:
    if d.get("pageType")=="departement" and (d.get("pays") or "FR")=="FR":
        vs=byd.get(d.get("slugDepartement"))
        if vs:
            c={k:(round(np.mean([x[k] for x in vs])) if "jours" in k else round(np.mean([x[k] for x in vs]),1)) for k in KEYS}
            dept_clim[d.get("slugDepartement")]=c
            for k in KEYS: zs.cell(row=d["_row"],column=COL[k],value=c[k])
# régions = moyenne des dépts
byr=defaultdict(list)
for d in rows:
    if d.get("pageType")=="departement" and d.get("slugDepartement") in dept_clim:
        byr[d.get("slugRegion")].append(dept_clim[d["slugDepartement"]])
for d in rows:
    if d.get("pageType")=="region" and (d.get("pays") or "FR")=="FR":
        vs=byr.get(d.get("slugRegion"))
        if vs:
            for k in KEYS:
                val=round(np.mean([x[k] for x in vs])) if "jours" in k else round(np.mean([x[k] for x in vs]),1)
                zs.cell(row=d["_row"],column=COL[k],value=val)
wb.save("public/documents/covalba-zones-seo-DEMO.xlsx")
# valeurs pour les zones EN DUR (zones.ts)
print("=== valeurs Explore2 pour zones.ts (hand-written) ===")
for n,sv in [("Roissy","cool-roof-roissy"),("Lyon","cool-roof-lyon"),("Marseille","cool-roof-marseille")]:
    print(n, ville_clim.get(sv))
print("Seine-Saint-Denis (dept)", dept_clim.get("cool-roof-seine-saint-denis"))
print("\nlot1/lot2 recalés ? Gonesse=", ville_clim.get("cool-roof-gonesse"))