#!/usr/bin/env python3
"""
JBT Travel Ops — Duncan Chinnock Profile Merge
Merges P76658107cpw97 (duncan@tvprompt.com, dupe) INTO P08735756 (gmail, keeper)
- Reassigns hotel booking, travel request, per diem, dupe assignment to keeper
- Saves duplicate's email to keeper's coordNotes
- Deletes the duplicate person record
- Removes redundant duplicate assignment to S2 (keeper already has one)

Run from anywhere — script auto-finds db.json.
Creates a timestamped backup before writing.
"""
import json
import os
import sys
import shutil
from datetime import datetime

KEEPER_ID = 'P08735756'
DUPE_ID = 'P76658107cpw97'
DUPE_EMAIL = 'duncan@tvprompt.com'

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: Could not find db.json")
    sys.exit(1)

# Backup first
backup_path = db_path + '.backup-' + datetime.now().strftime('%Y%m%d-%H%M%S')
shutil.copy2(db_path, backup_path)
print(f"✓ Backup created: {backup_path}")

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

# Verify both profiles exist before doing anything
keeper = next((p for p in db.get('persons', []) if p.get('id') == KEEPER_ID), None)
dupe = next((p for p in db.get('persons', []) if p.get('id') == DUPE_ID), None)

if not keeper:
    print(f"ERROR: Keeper profile {KEEPER_ID} not found. Aborting.")
    sys.exit(1)
if not dupe:
    print(f"ERROR: Duplicate profile {DUPE_ID} not found. Maybe already merged?")
    sys.exit(1)

print(f"✓ Found keeper: {keeper.get('firstName')} {keeper.get('lastName')} ({keeper.get('email')})")
print(f"✓ Found dupe:   {dupe.get('firstName')} {dupe.get('lastName')} ({dupe.get('email')})")
print()

changes = []

# 1. Append dupe's email to keeper's coordNotes
existing_notes = keeper.get('coordNotes', '') or ''
note_to_add = f"Secondary email: {DUPE_EMAIL} (merged from duplicate profile {DUPE_ID} on {datetime.now().strftime('%Y-%m-%d')})"
if note_to_add not in existing_notes:
    keeper['coordNotes'] = (existing_notes + '\n' + note_to_add).strip() if existing_notes else note_to_add
    changes.append(f"Added secondary email to keeper's coordNotes")

# 2. Reassign hotel bookings
for h in db.get('hotelBookings', []):
    if h.get('personId') == DUPE_ID:
        h['personId'] = KEEPER_ID
        # Also fix title/dept on the hotel booking to match keeper
        if keeper.get('title'): h['title'] = keeper['title']
        if keeper.get('dept'): h['dept'] = keeper['dept']
        changes.append(f"Reassigned hotel booking {h.get('id')} to keeper")

# 3. Reassign travel requests + update submittedEmail to keeper's email
for r in db.get('travelRequests', []):
    if r.get('personId') == DUPE_ID:
        r['personId'] = KEEPER_ID
        old_email = r.get('submittedEmail', '')
        if keeper.get('email'):
            r['submittedEmail'] = keeper['email']
        changes.append(f"Reassigned travel request {r.get('id')} to keeper (submittedEmail updated from {old_email} to {keeper.get('email','')})")

# 4. Reassign per diems
for pd in db.get('perDiems', []):
    if pd.get('personId') == DUPE_ID:
        pd['personId'] = KEEPER_ID
        changes.append(f"Reassigned per diem {pd.get('id')} to keeper")

# 5. Reassign approvals (in case any)
for ap in db.get('approvals', []):
    if ap.get('personId') == DUPE_ID:
        ap['personId'] = KEEPER_ID
        changes.append(f"Reassigned approval {ap.get('id')} to keeper")

# 6. Reassign legacy bookings (in case any)
for b in db.get('bookings', []):
    if b.get('personId') == DUPE_ID:
        b['personId'] = KEEPER_ID
        changes.append(f"Reassigned booking {b.get('id')} to keeper")

# 7. Reassign notes (in case any)
for n in db.get('notes', []):
    if n.get('personId') == DUPE_ID:
        n['personId'] = KEEPER_ID
        changes.append(f"Reassigned note {n.get('id')} to keeper")

# 8. Handle assignments — reassign dupe's assignments to keeper, then dedupe
keeper_show_ids = {a.get('showId') for a in db.get('assignments', []) if a.get('personId') == KEEPER_ID}
new_assignments = []
removed_count = 0
for a in db.get('assignments', []):
    if a.get('personId') == DUPE_ID:
        if a.get('showId') in keeper_show_ids:
            # Keeper already assigned to this show — drop the dupe's assignment
            removed_count += 1
            changes.append(f"Removed redundant dupe assignment {a.get('id')} (keeper already on {a.get('showId')})")
            continue
        else:
            a['personId'] = KEEPER_ID
            keeper_show_ids.add(a.get('showId'))
            changes.append(f"Reassigned assignment {a.get('id')} to keeper for {a.get('showId')}")
    new_assignments.append(a)
db['assignments'] = new_assignments

# 9. Delete the duplicate person record
db['persons'] = [p for p in db.get('persons', []) if p.get('id') != DUPE_ID]
changes.append(f"Deleted duplicate person record {DUPE_ID}")

# Save
with open(db_path, 'w') as f:
    json.dump(db, f, indent=2)

print("=" * 70)
print(f"MERGE COMPLETE — {len(changes)} changes:")
print("=" * 70)
for c in changes:
    print(f"  • {c}")
print()
print(f"✓ Database saved to: {db_path}")
print(f"✓ Backup at: {backup_path}")
print()
print("NEXT STEPS:")
print("  1. Restart the Node process so the in-memory DB reloads:")
print("       kill -9 `lsof -t -i :3000`")
print("  2. Hard-refresh your browser to clear cached DB state")
print("  3. Verify Duncan now shows ONE profile in Crew Database")
print("  4. Check his itinerary — should show BOTH NMDC and ACF shows")