"""
km_diagnose3.py - check FR and LO records for KM call signs, no PA available
"""
import os
import pandas as pd

ULS_DIR = r"D:\FCC_ULS"

# ── Get KM active call signs (same logic as main script) ──────────────────────
print("Loading EN + HD to get active KM call signs...")
en = pd.read_csv(os.path.join(ULS_DIR, "EN.dat"), sep="|", header=None,
                 dtype=str, on_bad_lines="skip", encoding="latin-1")
en_name = en[7].str.strip().str.lower().fillna("")
km_en = en[en_name.str.contains("kinder morgan", na=False)]
km_callsigns_all = set(km_en[4].str.strip().dropna().unique())

MW_SERVICES = {"MG","MW","CF","TP","TI","WU","YD","GB"}
hd = pd.read_csv(os.path.join(ULS_DIR, "HD.dat"), sep="|", header=None,
                 dtype=str, on_bad_lines="skip", encoding="latin-1")
hd_cs   = hd[4].str.strip()
hd_stat = hd[5].str.strip()
hd_svc  = hd[6].str.strip()
hd_active_mw = hd[(hd_svc.isin(MW_SERVICES)) & (hd_stat == "A")]
active_km_cs = set(hd_active_mw[hd_cs.isin(km_callsigns_all)][4].str.strip().unique())
print(f"  Active KM MW call signs: {len(active_km_cs)}")

# ── Check FR.dat for KM call signs ───────────────────────────────────────────
print("\nLoading FR.dat (KM rows only)...")
fr_cols = ["record_type","usid","uls_file_number","ebf_number",
           "call_sign","action_performed","location_number","antenna_number",
           "class_station_code","op_altitude_code","frequency_assigned","frequency_upper_band",
           "frequency_authorized_bw","frequency_number","status_code","date_first_used"]
fr = pd.read_csv(os.path.join(ULS_DIR, "FR.dat"), sep="|", header=None,
                 names=fr_cols[:16], dtype=str, on_bad_lines="skip", encoding="latin-1")
fr["freq_mhz"] = pd.to_numeric(fr["frequency_assigned"], errors="coerce")

km_fr = fr[fr["call_sign"].str.strip().isin(active_km_cs)].copy()
print(f"  KM FR rows: {len(km_fr)}")

if not km_fr.empty:
    print(f"  Freq range: {km_fr['freq_mhz'].min():.1f} - {km_fr['freq_mhz'].max():.1f} MHz")
    in_6ghz = km_fr[(km_fr["freq_mhz"] >= 5925) & (km_fr["freq_mhz"] <= 7125)]
    print(f"  Rows in 6 GHz band: {len(in_6ghz)}")
    print(f"\n  Sample KM FR rows:")
    print(km_fr[["call_sign","location_number","antenna_number","freq_mhz"]].head(20).to_string())

# ── Check LO.dat for KM call signs ───────────────────────────────────────────
print("\nLoading LO.dat (KM rows only)...")
lo_cols = ["record_type","usid","uls_file_number","ebf_number",
           "call_sign","action_performed","location_action_performed","location_type_code",
           "location_class_code","location_number","site_status","corresponding_fixed_location",
           "location_address","location_city","location_county","location_state",
           "radius_of_operation","area_of_operation_code","clearance_indicator",
           "ground_elevation","lat_degrees","lat_minutes","lat_seconds","lat_direction",
           "long_degrees","long_minutes","long_seconds","long_direction",
           "max_lat_degrees","max_lat_minutes","max_lat_seconds","max_lat_direction",
           "max_long_degrees","max_long_minutes","max_long_seconds","max_long_direction",
           "nepa","quiet_zone_notification_date","tower_registration_number",
           "height_of_support_structure","overall_height_of_structure","structure_type",
           "airport_id","location_name","location_description"]
lo = pd.read_csv(os.path.join(ULS_DIR, "LO.dat"), sep="|", header=None,
                 names=lo_cols[:45], dtype=str, on_bad_lines="skip", encoding="latin-1")

km_lo = lo[lo["call_sign"].str.strip().isin(active_km_cs)].copy()
print(f"  KM LO rows: {len(km_lo)}")
if not km_lo.empty:
    print(f"  Location type codes: {km_lo['location_type_code'].value_counts().to_dict()}")
    print(f"\n  Sample KM LO rows:")
    print(km_lo[["call_sign","location_number","location_type_code",
                  "location_city","location_state","lat_degrees","long_degrees"]].head(20).to_string())

# ── Check PA.dat more carefully ───────────────────────────────────────────────
print("\nChecking PA.dat...")
pa_cols = ["record_type","usid","uls_file_number","ebf_number",
           "call_sign","action_performed","location_number","antenna_number",
           "msa_bsa_number","path_number","transmit_location_number","transmit_antenna_number",
           "receiver_location_number","receiver_antenna_number","microwave_service_code",
           "receiver_call_sign","bearing","path_length","passive_receiver_indicator",
           "status_code","date_entered"]
pa = pd.read_csv(os.path.join(ULS_DIR, "PA.dat"), sep="|", header=None,
                 names=pa_cols[:21], dtype=str, on_bad_lines="skip", encoding="latin-1")
print(f"  Total PA rows: {len(pa)}")
print(f"  Sample PA call signs: {pa['call_sign'].str.strip().value_counts().head(5).to_dict()}")

# Try stripping and checking again
pa_cs_stripped = pa["call_sign"].str.strip()
km_pa = pa[pa_cs_stripped.isin(active_km_cs)]
print(f"  KM rows in PA (stripped): {len(km_pa)}")

# Check a few specific KM call signs directly
sample_cs = sorted(list(active_km_cs))[:10]
print(f"\n  Checking {len(sample_cs)} sample KM call signs in PA:")
for cs in sample_cs:
    n = (pa_cs_stripped == cs).sum()
    n_rx = (pa["receiver_call_sign"].str.strip() == cs).sum()
    fr_n = (km_fr["call_sign"].str.strip() == cs).sum() if not km_fr.empty else "?"
    print(f"    {cs}: PA_tx={n}  PA_rx={n_rx}  FR_rows={fr_n}")