"""
Spatially match each station to the catchment polygon that actually
contains it (point-in-polygon), rather than guessing at a shared ID
field across BD TOPO and Hub'Eau. This is the correct approach when
two datasets don't share a clean join key.
"""
import sys
sys.path.insert(0, ".")
from pathlib import Path
import pandas as pd
import geopandas as gpd

from src.data.loaders.bdtopo_hydro import BDTopoHydroLoader

loader = BDTopoHydroLoader(data_path=Path("datasets/bdtopo_hydro"))
catchments = loader.load_catchments()
catchments_metric = catchments.to_crs(epsg=2154)  # Lambert-93 for accurate area in meters
catchments["area_km2"] = catchments_metric.geometry.area / 1e6

stations = pd.read_csv("datasets/station_elevations.csv")
hubeau = pd.read_csv("datasets/catchment_area.csv").dropna(subset=["catchment_area_km2"])

stations_gdf = gpd.GeoDataFrame(
    stations,
    geometry=gpd.points_from_xy(stations["longitude"], stations["latitude"]),
    crs="EPSG:4326",
).to_crs(catchments.crs)

# Spatial join: which catchment polygon contains each station?
joined = gpd.sjoin(stations_gdf, catchments[["area_km2", "toponyme", "geometry"]],
                    how="left", predicate="within")

result = joined[["station_code", "toponyme", "area_km2"]].rename(
    columns={"area_km2": "bdtopo_area_km2", "toponyme": "bdtopo_catchment_name"}
)
result = result.merge(hubeau[["station_code", "catchment_area_km2"]], on="station_code", how="left")
result = result.rename(columns={"catchment_area_km2": "hubeau_area_km2"})
result["ratio"] = result["bdtopo_area_km2"] / result["hubeau_area_km2"]

print(result.to_string(index=False))
print()
matched = result.dropna(subset=["bdtopo_area_km2", "hubeau_area_km2"])
print(f"Stations with BOTH a containing BD TOPO polygon AND a Hub'Eau value: {len(matched)}")
if len(matched) > 0:
    print(f"Area ratio (BD TOPO / Hub'Eau) — mean: {matched['ratio'].mean():.2f}, "
          f"median: {matched['ratio'].median():.2f}")
    print("(A ratio near 1.0 across most stations would confirm both sources agree; "
          "large/inconsistent ratios would suggest the polygons represent something "
          "other than the gauge's own catchment -- e.g. a downstream aggregate, or "
          "no polygon actually contains that point due to bbox edge effects.)")