#!/usr/bin/env python3
"""
JBT Travel Ops — Duplicate Assignment Dedupe APPLY
For each (personId, showId) pair with multiple assignment records, keeps the
assignment with the OLDEST id and deletes the others.

Travel requests, hotel bookings, per diems, and bookingDetails are linked by
personId+showId — NOT by assignment.id — so deleting duplicate assignments
does not touch any travel data.

Creates a timestamped backup of db.json before writing.
"""
import json
import os
import sys
import re
import shutil
from datetime import datetime
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)

# 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)

# 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)

def extract_ts(aid):
    m = re.match(r'^A(\d+)', aid or '')
    return int(m.group(1)) if m else 0

# Build the keeper IDs set + the deletion list
keeper_ids = set()
to_delete_ids = set()
deletion_log = []

for (pid, sid), assigns in groups.items():
    if len(assigns) < 2:
        keeper_ids.add(assigns[0].get('id'))
        continue
    assigns_sorted = sorted(assigns, key=lambda a: extract_ts(a.get('id', '')))
    keeper = assigns_sorted[0]
    keeper_ids.add(keeper.get('id'))
    for d in assigns_sorted[1:]:
        to_delete_ids.add(d.get('id'))
        deletion_log.append({
            'personId': pid,
            'showId': sid,
            'kept': keeper.get('id'),
            'deleted': d.get('id')
        })

# Apply deletion
before_count = len(db.get('assignments', []))
db['assignments'] = [a for a in db.get('assignments', []) if a.get('id') not in to_delete_ids]
after_count = len(db.get('assignments', []))

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

print(f"\n{'=' * 70}")
print(f"DEDUPE COMPLETE")
print(f"{'=' * 70}")
print(f"  Assignments before: {before_count}")
print(f"  Assignments after:  {after_count}")
print(f"  Deleted:            {before_count - after_count}")
print(f"  Backup at:          {backup_path}")

if deletion_log:
    print(f"\nDeleted assignments (kept oldest, removed newer duplicates):")
    for d in deletion_log:
        print(f"  personId={d['personId']} showId={d['showId']} | kept {d['kept']} | deleted {d['deleted']}")

print(f"\nNEXT STEPS:")
print(f"  1. Restart Node so server reloads the cleaned DB:")
print(f"       kill -9 `lsof -t -i :3000`")
print(f"  2. Hard-refresh your browser to clear cached state")
print(f"  3. Verify Missing Forms widget shows correct count")