"""
km_asr_lookup.py
Checks LO.dat for tower registration numbers for problem call signs,
then looks up precise coordinates from the FCC ASR database.
"""
import os, pandas as pd

ULS_DIR = r"D:\FCC_ULS"
CSV     = r"D:\KinderMorgan\Kinder_Morgan_Network_Definition_v1.csv"

PROBLEM_CS = ['KBB27','KGY78','KKO53','KKS70','KKS72','KKS75','KKS76','KKS79',
              'KKY92','KLN51','KOE33','KOE37','KOE39','WBD92','WBD93','WBX374',
              'WBX375','WEE492','WIA840','WNTA287','WNTJ673','WNTJ675','WNTJ676',
              'WNTM247','WPNJ908','WPVN620','WQBD387','WQBG727','WQBL390','WQV68',
              'WQV69','WQW47']

print("Loading LO.dat for tower registration numbers...")
lo = pd.read_csv(os.path.join(ULS_DIR,"LO.dat"), sep="|", header=None,
                 dtype=str, on_bad_lines="skip", encoding="latin-1")

lo_cs   = lo.iloc[:,4].str.strip()
lo_loc  = lo.iloc[:,8].str.strip()
lo_type = lo.iloc[:,7].str.strip()
lo_trn  = lo.iloc[:,38].str.strip() if lo.shape[1]>38 else pd.Series([""] * len(lo))
lo_lat  = lo.iloc[:,19].str.strip()
lo_lon  = lo.iloc[:,23].str.strip()
lo_city = lo.iloc[:,12].str.strip() if lo.shape[1]>12 else pd.Series([""] * len(lo))
lo_state= lo.iloc[:,14].str.strip() if lo.shape[1]>14 else pd.Series([""] * len(lo))

prob = lo[lo_cs.isin(PROBLEM_CS)].copy()
prob_df = pd.DataFrame({
    "call_sign": lo_cs[prob.index],
    "loc_num":   lo_loc[prob.index],
    "loc_type":  lo_type[prob.index],
    "trn":       lo_trn[prob.index],
    "lat_deg":   lo_lat[prob.index],
    "lon_deg":   lo_lon[prob.index],
    "city":      lo_city[prob.index],
    "state":     lo_state[prob.index],
})

print(f"Problem call sign LO records: {len(prob_df)}")
print("\nTower registration numbers (TRN) found:")
has_trn = prob_df[prob_df["trn"].str.len() > 0]
print(has_trn[["call_sign","loc_num","loc_type","trn","lat_deg","lon_deg","city","state"]].to_string())

print("\nAll LO records for problem call signs:")
print(prob_df[["call_sign","loc_num","loc_type","trn","lat_deg","lon_deg","city","state"]].to_string())