"""
fetch_missing_coords.py
Finds paths with suspiciously long distances (likely bad B-side coords),
then fetches correct coordinates from FCC ULS website for those call signs.
Patches the network definition CSV with correct coordinates.

Run after build + cleanup:
    python fetch_missing_coords.py
"""
import os, time, math
import pandas as pd
import urllib.request, json

CSV = r"D:\KinderMorgan\Kinder_Morgan_Network_Definition_v1.csv"
THRESHOLD_MILES = 60.0  # paths longer than this likely have bad B-side coords

def haversine_miles(lat1,lon1,lat2,lon2):
    R=3958.8
    lat1,lon1,lat2,lon2=map(math.radians,[lat1,lon1,lat2,lon2])
    dlat=lat2-lat1; dlon=lon2-lon1
    a=math.sin(dlat/2)**2+math.cos(lat1)*math.cos(lat2)*math.sin(dlon/2)**2
    return R*2*math.asin(math.sqrt(a))

def calc_bearing(lat1,lon1,lat2,lon2):
    lat1,lon1,lat2,lon2=map(math.radians,[lat1,lon1,lat2,lon2])
    dlon=lon2-lon1
    x=math.sin(dlon)*math.cos(lat2)
    y=math.cos(lat1)*math.sin(lat2)-math.sin(lat1)*math.cos(lat2)*math.cos(dlon)
    return (math.degrees(math.atan2(x,y))+360)%360

def fetch_fcc_location(callsign):
    """Fetch site coordinates from FCC ULS API for a given call sign.
    Returns (lat, lon, city, state) or None."""
    url = f"https://wireless2.fcc.gov/UlsApp/UlsApi/getLicenseV4?callsign={callsign}&format=json"
    try:
        req = urllib.request.Request(url, headers={"User-Agent":"Mozilla/5.0"})
        with urllib.request.urlopen(req, timeout=15) as r:
            data = json.loads(r.read().decode())
        # Dig into license locations
        lics = data.get("licenses", {}).get("license", [])
        if isinstance(lics, dict): lics = [lics]
        for lic in lics:
            locs = lic.get("licenseLocations", {}).get("licenseLocation", [])
            if isinstance(locs, dict): locs = [locs]
            for loc in locs:
                lat = loc.get("latitude")
                lon = loc.get("longitude")
                if lat and lon:
                    try:
                        lat_f = float(lat)
                        lon_f = float(lon)
                        if lat_f != int(lat_f) or lon_f != int(lon_f):  # precise
                            city  = loc.get("locationCity","")
                            state = loc.get("locationState","")
                            return lat_f, lon_f, city, state
                    except (ValueError, TypeError):
                        continue
    except Exception as e:
        print(f"    FCC fetch error for {callsign}: {e}")
    return None

# ── Load CSV ──────────────────────────────────────────────────────────────────
df = pd.read_csv(CSV, dtype=str)
df["Path_Length_Miles"] = pd.to_numeric(df["Path_Length_Miles"], errors="coerce")
df["A_Latitude"]  = pd.to_numeric(df["A_Latitude"],  errors="coerce")
df["A_Longitude"] = pd.to_numeric(df["A_Longitude"], errors="coerce")
df["B_Latitude"]  = pd.to_numeric(df["B_Latitude"],  errors="coerce")
df["B_Longitude"] = pd.to_numeric(df["B_Longitude"], errors="coerce")

long_paths = df[df["Path_Length_Miles"] > THRESHOLD_MILES].copy()
print(f"Paths over {THRESHOLD_MILES} miles: {len(long_paths)}")
print(long_paths[["path_num","A_Call_Sign","B_Call_Sign","Path_Length_Miles"]].to_string())

# Collect unique B-side call signs needing coord lookup
b_cs_needed = set(long_paths["B_Call_Sign"].unique())
print(f"\nFetching FCC coords for {len(b_cs_needed)} B-side call signs...")

coord_cache = {}
for cs in sorted(b_cs_needed):
    print(f"  {cs}...", end=" ", flush=True)
    result = fetch_fcc_location(cs)
    if result:
        lat, lon, city, state = result
        coord_cache[cs] = (lat, lon, city, state)
        print(f"lat={lat:.5f} lon={lon:.5f} ({city}, {state})")
    else:
        print("no precise coords found")
    time.sleep(0.3)  # be polite to FCC servers

# ── Patch the dataframe ───────────────────────────────────────────────────────
patched = 0
for idx, row in df.iterrows():
    if row["Path_Length_Miles"] > THRESHOLD_MILES and row["B_Call_Sign"] in coord_cache:
        lat, lon, city, state = coord_cache[row["B_Call_Sign"]]
        a_lat = row["A_Latitude"]; a_lon = row["A_Longitude"]
        df.at[idx, "B_Latitude"]  = lat
        df.at[idx, "B_Longitude"] = lon
        if pd.isna(row["b_state"]) or row["b_state"] == "":
            df.at[idx, "b_state"] = state
        # Recompute geometry
        try:
            new_miles = round(haversine_miles(a_lat,a_lon,lat,lon), 3)
            new_a_brg = round(calc_bearing(a_lat,a_lon,lat,lon), 2)
            new_b_brg = round(calc_bearing(lat,lon,a_lat,a_lon), 2)
            df.at[idx, "Path_Length_Miles"] = new_miles
            df.at[idx, "A_Bearing"] = new_a_brg
            df.at[idx, "B_Bearing"] = new_b_brg
            print(f"  Patched {row['path_num']} {row['A_Call_Sign']}->{row['B_Call_Sign']}: "
                  f"{row['Path_Length_Miles']:.1f} -> {new_miles:.1f} miles")
            patched += 1
        except Exception as e:
            print(f"  Could not recompute geometry for {row['path_num']}: {e}")

print(f"\nPatched {patched} paths")
df.to_csv(CSV, index=False)
print(f"Saved -> {CSV}")

print(f"\nPath length distribution after patch:")
print(df["Path_Length_Miles"].describe())
print(f"\nPaths still over {THRESHOLD_MILES} miles:")
still_long = df[df["Path_Length_Miles"] > THRESHOLD_MILES]
if still_long.empty:
    print("  None.")
else:
    print(still_long[["path_num","A_Call_Sign","B_Call_Sign","Path_Length_Miles","b_state"]].to_string())