"""
Extracts a_raat/b_raat/a_desc/b_desc for each path in the network definition CSV
by looking up call signs against the FCC ULS Microwave bulk database files.

Required input files (pipe-delimited, no header row), same folder as this script:
    HD.dat
    AN.dat
    LO.dat
"""

import pandas as pd

DAT_DIR = "."

# HD.dat: position 2 = unique_system_identifier, position 5 = call_sign (1-indexed)
hd = pd.read_csv(f"{DAT_DIR}/HD.dat", sep="|", header=None, dtype=str,
                 encoding="latin-1", on_bad_lines="skip", usecols=[1, 4])
hd.columns = ["unique_system_identifier", "call_sign"]

# AN.dat: position 2 = unique_system_identifier, position 12 = height_to_center_raat
an = pd.read_csv(f"{DAT_DIR}/AN.dat", sep="|", header=None, dtype=str,
                 encoding="latin-1", on_bad_lines="skip", usecols=[1, 11])
an.columns = ["unique_system_identifier", "height_to_center_raat"]

# LO.dat (0-indexed): col1=unique_system_identifier, col7=location_type_code (T=transmit/main site),
#         col12=location_city, col14=location_state, col42=location_name
lo = pd.read_csv(f"{DAT_DIR}/LO.dat", sep="|", header=None, dtype=str,
                 encoding="latin-1", on_bad_lines="skip", usecols=[1, 7, 12, 14, 42])
lo.columns = ["unique_system_identifier", "location_type_code", "location_city", "location_state", "location_name"]

hd = hd.dropna(subset=["call_sign"]).drop_duplicates("call_sign")
hd["call_sign"] = hd["call_sign"].str.strip()

an["height_to_center_raat"] = pd.to_numeric(an["height_to_center_raat"], errors="coerce")
an_best = an.dropna(subset=["height_to_center_raat"]).drop_duplicates("unique_system_identifier")

lo_best = lo.sort_values("location_type_code", ascending=False)  # T before R/blank
lo_best = lo_best.drop_duplicates("unique_system_identifier").copy()
lo_best["desc"] = lo_best["location_name"].fillna("").str.strip()
mask = lo_best["desc"] == ""
lo_best.loc[mask, "desc"] = (
    lo_best.loc[mask, "location_city"].fillna("") + ", " + lo_best.loc[mask, "location_state"].fillna("")
)

lookup = hd.merge(an_best[["unique_system_identifier", "height_to_center_raat"]],
                  on="unique_system_identifier", how="left") \
           .merge(lo_best[["unique_system_identifier", "desc"]],
                  on="unique_system_identifier", how="left")

lookup = lookup.set_index("call_sign")[["height_to_center_raat", "desc"]]

df = pd.read_csv("ATT_Mobility_Network_Definition_v6.csv", dtype=str)

for src_col, prefix in [("A_Call_Sign", "a"), ("b_Call_Sign", "b")]:
    df[f"{prefix}_raat"] = df[src_col].map(lookup["height_to_center_raat"])
    df[f"{prefix}_desc"] = df[src_col].map(lookup["desc"])

df.to_csv("ATT_Mobility_Network_Definition_v7.csv", index=False)

missing_a = df.loc[df["a_raat"].isna(), "A_Call_Sign"].unique()
missing_b = df.loc[df["b_raat"].isna(), "b_Call_Sign"].unique()
print(f"Missing A call signs: {len(missing_a)} -> {list(missing_a[:10])}")
print(f"Missing B call signs: {len(missing_b)} -> {list(missing_b[:10])}")