#!/usr/bin/env python3
"""
JBT Travel Ops — Duplicate Assignment Dedupe DRY RUN
Shows what WOULD be deleted but does NOT modify the database.

Strategy: For each (personId, showId) pair with multiple assignment records,
keep the assignment with the OLDEST id (lowest timestamp prefix) and mark
the others for deletion.

Travel requests, hotel bookings, per diems, and bookingDetails are linked
by personId+showId — NOT by assignment.id — so deleting duplicate assignment
records does NOT touch any travel data. This script confirms that for each
duplicate by listing all linked records.
"""
import json
import os
import sys
import re
from collections import defaultdict

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)

print(f"Reading: {db_path}\n")
print("=" * 78)
print("DUPLICATE ASSIGNMENT DRY RUN — NO CHANGES WILL BE MADE")
print("=" * 78)

# Group assignments by (personId, showId)
groups = defaultdict(list)
for a in db.get('assignments', []):
    key = (a.get('personId'), a.get('showId'))
    groups[key].append(a)

# Helper to extract timestamp from assignment ID for sorting
# Format: A<timestamp><suffix>  e.g. A1776372839473uumS2 -> 1776372839473
def extract_ts(aid):
    m = re.match(r'^A(\d+)', aid or '')
    return int(m.group(1)) if m else 0

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

total_to_delete = 0
total_dupes = 0

for (pid, sid), assigns in sorted(groups.items(), key=lambda x: (x[0][1] or '', x[0][0] or '')):
    if len(assigns) < 2:
        continue
    total_dupes += 1
    
    show = shows.get(sid, {})
    person = persons.get(pid, {})
    name = f"{person.get('firstName','')} {person.get('lastName','')}".strip() or '(person not found)'
    show_code = show.get('code', sid)
    
    # Sort by timestamp — oldest first
    assigns_sorted = sorted(assigns, key=lambda a: extract_ts(a.get('id','')))
    keeper = assigns_sorted[0]
    deletables = assigns_sorted[1:]
    
    print(f"\n{name}  —  {show_code}  ({len(assigns)} assignments)")
    print(f"  personId: {pid}    showId: {sid}")
    print(f"  ✓ KEEP:   {keeper.get('id')}  (oldest)")
    for d in deletables:
        print(f"  ✗ DELETE: {d.get('id')}")
        total_to_delete += 1
    
    # Show linked travel data — proves it's not affected by which assignment ID we keep
    linked_reqs = [r for r in db.get('travelRequests', []) 
                   if r.get('personId') == pid and r.get('showId') == sid]
    linked_hotels = [h for h in db.get('hotelBookings', []) 
                     if h.get('personId') == pid and h.get('showId') == sid]
    linked_pds = [p for p in db.get('perDiems', []) 
                  if p.get('personId') == pid and p.get('showId') == sid]
    
    print(f"  Linked data (preserved — not stored on assignments):")
    if linked_reqs:
        for r in linked_reqs:
            bd = r.get('bookingDetails') or {}
            has_flight = bool(bd.get('inbound1') or bd.get('outbound1') or bd.get('in1') or bd.get('out1'))
            has_hotel_in_bd = bool(bd.get('hotelName') or bd.get('hotelCheckIn'))
            has_train = bool(bd.get('trainInbound') or bd.get('trainOutbound'))
            extras = []
            if has_flight: extras.append('flight data')
            if has_train: extras.append('train data')
            if has_hotel_in_bd: extras.append('hotel data in modal')
            extras_str = (' [' + ', '.join(extras) + ']') if extras else ''
            print(f"    Travel Request: {r.get('id')} status={r.get('status','')} method={r.get('travelMethod','')}{extras_str}")
    else:
        print(f"    Travel Request: (none)")
    if linked_hotels:
        for h in linked_hotels:
            print(f"    Hotel Booking:  {h.get('id')} hotel={h.get('hotel','')} checkIn={h.get('checkIn','')} checkOut={h.get('checkOut','')}")
    else:
        print(f"    Hotel Booking:  (none)")
    if linked_pds:
        for p in linked_pds:
            print(f"    Per Diem:       {p.get('id')} eligible={p.get('eligible','')}")
    else:
        print(f"    Per Diem:       (none)")

print("\n" + "=" * 78)
print(f"SUMMARY")
print("=" * 78)
print(f"  Duplicate (personId, showId) pairs found: {total_dupes}")
print(f"  Total assignment records that WOULD be deleted: {total_to_delete}")
print(f"  No travel requests, hotel bookings, or per diems are touched.")
print(f"  Strategy: keep the OLDEST assignment ID per pair, delete the rest.")
print()
print(f"This was a DRY RUN. Database has NOT been modified.")
print(f"If output looks correct, run dedupe_assignments_apply.py to make it real.")