#!/usr/bin/env python3
"""
Find duplicate or orphaned assignments that could cause widget miscounts.
"""
import json
import os
import sys
from collections import Counter

candidates = ['db.json', './data/db.json', os.path.expanduser('~/public_html/jn.com/ops/data/db.json')]
db_path = next((c for c in candidates if os.path.exists(c)), None)
if not db_path:
    print("ERROR: db.json not found"); sys.exit(1)

with open(db_path, 'r') as f:
    db = json.load(f)

person_ids = {p.get('id') for p in db.get('persons', [])}

print("=" * 70)
print("ASSIGNMENT DIAGNOSTIC PER SHOW")
print("=" * 70)

for show in db.get('shows', []):
    sid = show.get('id')
    code = show.get('code', sid)
    print(f"\n--- {code} ({sid}) ---")
    
    show_assigns = [a for a in db.get('assignments', []) if a.get('showId') == sid]
    print(f"  Total assignment records: {len(show_assigns)}")
    
    # Count personId occurrences
    pid_counts = Counter(a.get('personId') for a in show_assigns)
    
    # Duplicates
    dupes = {pid: cnt for pid, cnt in pid_counts.items() if cnt > 1}
    if dupes:
        print(f"  ⚠ DUPLICATE assignments (same personId, same showId, multiple records):")
        for pid, cnt in dupes.items():
            person = next((p for p in db.get('persons', []) if p.get('id') == pid), None)
            name = f"{person.get('firstName','')} {person.get('lastName','')}" if person else "(person not found)"
            print(f"    {pid}: {cnt}x — {name}")
            for a in show_assigns:
                if a.get('personId') == pid:
                    print(f"      assignment id: {a.get('id')}")
    else:
        print(f"  ✓ No duplicate assignments")
    
    # Orphaned (personId points to a person that doesn't exist)
    orphans = [a for a in show_assigns if a.get('personId') not in person_ids]
    if orphans:
        print(f"  ⚠ ORPHANED assignments (personId points to deleted person):")
        for a in orphans:
            print(f"    assignment {a.get('id')}: personId={a.get('personId')}")
    else:
        print(f"  ✓ No orphaned assignments")
    
    # Unique personIds
    unique_pids = set(a.get('personId') for a in show_assigns)
    print(f"  Unique personIds assigned: {len(unique_pids)}")
    
    # Travel requests for this show
    show_reqs = [r for r in db.get('travelRequests', []) if r.get('showId') == sid]
    req_pids = set(r.get('personId') for r in show_reqs)
    
    # Missing forms = assigned without a request, and person exists
    missing_pids = [pid for pid in unique_pids if pid not in req_pids and pid in person_ids]
    print(f"  Travel requests submitted: {len(show_reqs)} ({len(req_pids)} unique personIds)")
    print(f"  Missing forms (unique persons, with profile): {len(missing_pids)}")
    
    # Buggy widget calculation (counts pid duplicates)
    buggy_assigned_list = [a.get('personId') for a in show_assigns]
    req_set = set(r.get('personId') for r in show_reqs)
    buggy_missing = [pid for pid in buggy_assigned_list 
                     if pid not in req_set 
                     and pid in person_ids]
    print(f"  ⚠ BUGGY count (current widget logic, with dupes): {len(buggy_missing)}")
    if len(buggy_missing) != len(missing_pids):
        print(f"    ↑ Widget would show {len(buggy_missing)} but should show {len(missing_pids)}")
        # Show which pids are getting counted multiple times
        bm_counts = Counter(buggy_missing)
        for pid, cnt in bm_counts.items():
            if cnt > 1:
                person = next((p for p in db.get('persons', []) if p.get('id') == pid), None)
                name = f"{person.get('firstName','')} {person.get('lastName','')}" if person else "(?)"
                print(f"    {pid} ({name}) counted {cnt}x")

print("\n" + "=" * 70)
print("DONE")
print("=" * 70)