import sys, glob, os, re
import pandas as pd
D = sys.argv[1] if len(sys.argv) > 1 else "WoKaS_Data_Record"
m = pd.read_excel(f"{D}/WoKaS_Hydrograph_Metafile/WoKaS_Hydrograph_Metafile.xlsx")
rows = []
for f in glob.glob(f"{D}/WoKaS_Hydrograph_Datasets/*.csv"):
    wid = os.path.basename(f).split("@")[0].replace("(B)", "")
    unite, lat, lon, data = "", None, None, []
    for l in open(f, encoding="utf-8", errors="replace"):
        if l.startswith("#Discharge unit"):
            unite = l.split(":", 1)[1].strip()
        elif l.startswith("#LAT"):
            p = l.split(":", 1)[1].split()
            lat, lon = (float(p[0]), float(p[1])) if p[0] != "NA" else (None, None)
        elif not l.startswith("#") and not l.startswith("Timestamp"):
            p = l.strip().split("\t")
            if len(p) == 2:
                try:
                    data.append(float(p[1]))
                except ValueError:
                    pass
    if not data:
        continue
    s = pd.Series(data)
    s = s[s >= 0]
    k = 0.001 if unite.lower().startswith("l") else 1.0
    rows.append(dict(WoKaS_ID=wid, unite=unite, debit_moyen_m3s=round(s.mean() * k, 3), debit_etiage_q10_m3s=round(s.quantile(0.1) * k, 3), mesures=len(s), lat_fichier=lat, lon_fichier=lon))
q = pd.DataFrame(rows)
o = m[["ISO", "WoKaS_ID", "Name", "Longitude", "Latitude"]].merge(q, on="WoKaS_ID", how="left")
o.to_csv("sources_karstiques_wokas.csv", index=False, lineterminator="\n")
print(len(o), "sources ;", o.unite.value_counts().to_dict())
print(">= 1 m3/s :", (o.debit_moyen_m3s >= 1).sum(), "; étiage >= 1 :", (o.debit_etiage_q10_m3s >= 1).sum(), "; >= 3 :", (o.debit_moyen_m3s >= 3).sum())
print(o[o.debit_moyen_m3s >= 1].ISO.value_counts().to_dict())
print(o.sort_values("debit_moyen_m3s", ascending=False).head(12)[["ISO", "Name", "debit_moyen_m3s", "debit_etiage_q10_m3s"]].to_string())