import json
db = json.load(open("/home/rmlac2fmr/public_html/jn.com/ops/data/db.json"))

# Replicate Ops stats logic (what the frontend counts)
ops_total = 0
ops_records = []
for pd in db.get("perDiems", []):
    if pd.get("showId") != "S2":
        continue
    req = next((r for r in db["travelRequests"] if r["id"] == pd.get("reqId") and r.get("showId") == "S2"), None)
    if req and req.get("bookingDetails", {}).get("pdEligible") == False:
        continue
    days = pd.get("manualDays") if pd.get("manualDays") is not None else pd.get("autoDays", 0)
    rate = pd.get("manualRate") if pd.get("manualRate") is not None else pd.get("autoRate", 0)
    total = (days or 0) * (rate or 0)
    ops_total += total
    ops_records.append(pd)

# Replicate push logic (what gets sent to Sheets)
push_total = 0
push_records = []
for pd in db.get("perDiems", []):
    if pd.get("showId") != "S2":
        continue
    req = next((r for r in db["travelRequests"] if r["id"] == pd.get("reqId") and r.get("showId") == "S2"), None)
    if not req:
        continue
    bd = req.get("bookingDetails", {})
    isIneligible = bd.get("pdEligible") == False or (req.get("checklist") or {}).get("pd_ineligible")
    if isIneligible:
        continue
    days = pd.get("manualDays") if pd.get("manualDays") is not None else pd.get("autoDays", 0)
    rate = pd.get("manualRate") if pd.get("manualRate") is not None else pd.get("autoRate", 0)
    total = (days or 0) * (rate or 0)
    push_total += total
    push_records.append(pd)

print(f"Ops stats: {len(ops_records)} records, ${ops_total:,}")
print(f"Push logic: {len(push_records)} records, ${push_total:,}")
print(f"Difference: {len(ops_records) - len(push_records)} records, ${ops_total - push_total:,}")

# Find records in Ops but not in push
ops_ids = set(p["id"] for p in ops_records)
push_ids = set(p["id"] for p in push_records)
missing = ops_ids - push_ids

if missing:
    print(f"\n=== Records in Ops total but NOT pushed to Sheets ===")
    for pid in missing:
        pd = next(p for p in db["perDiems"] if p["id"] == pid)
        req = next((r for r in db["travelRequests"] if r["id"] == pd.get("reqId")), None)
        person = next((p for p in db["persons"] if p["id"] == pd.get("personId")), None)
        name = f"{person.get('lastName','')}, {person.get('firstName','')}" if person else "?"
        days = pd.get("manualDays") if pd.get("manualDays") is not None else pd.get("autoDays", 0)
        rate = pd.get("manualRate") if pd.get("manualRate") is not None else pd.get("autoRate", 0)
        total = (days or 0) * (rate or 0)
        
        reason = "UNKNOWN"
        if not req:
            reason = "No matching travel request"
        else:
            bd = req.get("bookingDetails", {})
            cl = req.get("checklist", {})
            if cl.get("pd_ineligible"):
                reason = "checklist.pd_ineligible = True"
            if bd.get("pdEligible") == False:
                reason += " + pdEligible=false"
        
        print(f"  {name}: ${total} (days={days}, rate={rate})")
        print(f"    PD ID: {pd['id']}, Req ID: {pd.get('reqId','?')}")
        print(f"    Reason excluded from push: {reason}")
else:
    print("\nNo mismatch found — same records in both.")