#!/usr/bin/env python3
"""
JBT Travel Ops — Shawn King Duplicate Diagnosis
Run from ~/public_html/jn.com/ops/data/  (where db.json lives)
"""
import json
import os
import sys

# Find db.json — try cwd first, then standard ops data path
candidates = [
    'db.json',
    './data/db.json',
    os.path.expanduser('~/public_html/jn.com/ops/data/db.json')
]
db_path = None
for c in candidates:
    if os.path.exists(c):
        db_path = c
        break

if not db_path:
    print("ERROR: Could not find db.json. Run this script from ~/public_html/jn.com/ops/data/")
    sys.exit(1)

print(f"Reading: {db_path}\n")
with open(db_path, 'r') as f:
    db = json.load(f)

print("=" * 70)
print("ALL PERSONS WITH 'KING' IN NAME OR 'SHAWNSKING' IN EMAIL")
print("=" * 70)
king_persons = []
for p in db.get('persons', []):
    name_combined = (p.get('firstName','') + ' ' + p.get('middleName','') + ' ' + p.get('lastName','')).lower()
    email = (p.get('email','') or '').lower()
    if 'king' in name_combined or 'shawnsking' in email or 'shawn' in name_combined:
        king_persons.append(p)
        print(f"\n  ID:         {p.get('id')}")
        print(f"  Name:       '{p.get('firstName','')}' '{p.get('middleName','')}' '{p.get('lastName','')}'")
        print(f"  Email:      {p.get('email','')}")
        print(f"  Phone:      {p.get('phone','')}")
        print(f"  Title:      {p.get('title','')}")
        print(f"  Dept:       {p.get('dept','')}")
        print(f"  DOB:        {p.get('dob','')}")
        print(f"  Created:    {p.get('created','(unknown)')}")

print(f"\n  TOTAL PERSON RECORDS FOUND: {len(king_persons)}")

print("\n" + "=" * 70)
print("ALL TRAVEL REQUESTS FROM ANYONE NAMED KING / EMAIL SHAWNSKING")
print("=" * 70)
king_reqs = []
for r in db.get('travelRequests', []):
    submitted = (r.get('submittedName','') or '').lower()
    email = (r.get('submittedEmail','') or '').lower()
    if 'king' in submitted or 'shawnsking' in email:
        king_reqs.append(r)
        print(f"\n  REQ ID:       {r.get('id')}")
        print(f"  showId:       {r.get('showId')}")
        print(f"  personId:     {r.get('personId')}  <-- which person this is linked to")
        print(f"  submittedName:'{r.get('submittedName','')}'")
        print(f"  firstName:    '{r.get('firstName','')}'")
        print(f"  middleName:   '{r.get('middleName','')}'")
        print(f"  lastName:     '{r.get('lastName','')}'")
        print(f"  email:        {r.get('submittedEmail','')}")
        print(f"  status:       {r.get('status','')}")
        print(f"  source:       {r.get('source','')}")
        print(f"  submittedDate:{r.get('submittedDate','')}")

print(f"\n  TOTAL REQUEST RECORDS FOUND: {len(king_reqs)}")

print("\n" + "=" * 70)
print("ASSIGNMENTS FOR THESE PERSON IDs")
print("=" * 70)
king_person_ids = {p.get('id') for p in king_persons}
for a in db.get('assignments', []):
    if a.get('personId') in king_person_ids:
        print(f"  Assignment {a.get('id')}: personId={a.get('personId')} -> showId={a.get('showId')}")

print("\n" + "=" * 70)
print("HOTEL BOOKINGS FOR THESE PERSON IDs")
print("=" * 70)
for h in db.get('hotelBookings', []):
    if h.get('personId') in king_person_ids:
        print(f"  Hotel {h.get('id')}: personId={h.get('personId')} showId={h.get('showId')} {h.get('lastName','')}, {h.get('firstName','')} checkIn={h.get('checkIn','')}")

print("\n" + "=" * 70)
print("PER DIEMS FOR THESE PERSON IDs / REQ IDs")
print("=" * 70)
king_req_ids = {r.get('id') for r in king_reqs}
for pd in db.get('perDiems', []):
    if pd.get('personId') in king_person_ids or pd.get('reqId') in king_req_ids:
        print(f"  PD {pd.get('id')}: personId={pd.get('personId')} reqId={pd.get('reqId')} showId={pd.get('showId')}")

print("\n" + "=" * 70)
print("DIAGNOSIS")
print("=" * 70)
if len(king_persons) > 1:
    print(f"⚠ DUPLICATE PROFILES DETECTED: {len(king_persons)} person records exist for King.")
    print(f"  This is the root cause — intake form created a new profile despite an existing one.")
    print(f"\n  Person IDs: {[p.get('id') for p in king_persons]}")
    for r in king_reqs:
        linked = r.get('personId')
        match = next((p for p in king_persons if p.get('id') == linked), None)
        if match:
            mn = match.get('middleName','') or '(empty)'
            print(f"  Request {r.get('id')} (showId {r.get('showId')}) is linked to person {linked} — middleName on profile: '{mn}'")
elif len(king_persons) == 1 and len(king_reqs) > 0:
    print(f"✓ Only 1 person record exists.")
    p = king_persons[0]
    for r in king_reqs:
        if r.get('personId') == p.get('id'):
            print(f"✓ Request {r.get('id')} correctly linked to person {p.get('id')}")
            r_mid = r.get('middleName','') or '(empty)'
            p_mid = p.get('middleName','') or '(empty)'
            print(f"  middleName on REQUEST:  '{r_mid}'")
            print(f"  middleName on PROFILE:  '{p_mid}'")
            if r_mid != '(empty)' and p_mid == '(empty)':
                print(f"  ⚠ Middle name is on request but never propagated to profile.")
        else:
            print(f"⚠ Request {r.get('id')} is linked to personId={r.get('personId')} which doesn't match Shawn's profile {p.get('id')}")
else:
    print("Inconclusive — review output above manually.")