#!/usr/bin/env python3
"""
migrate_to_prod.py  v2
======================
Migrates data from local SQLite JSON fixture → production MySQL.

Fixes in v2:
  - Strips Unicode replacement chars (\\xEF\\xBF\\xBD / U+FFFD) from all text
    fields before writing — MySQL utf8mb3 tables reject them.
  - Staff users already exist in prod → builds user_map by matching username
    instead of relying on dry-run pk mapping.
  - Skips staff user creation by default (--skip-staff flag).

Usage (run on the server inside the virtualenv):

    # Classes, subjects, students, fees only (staff already in prod):
    OPENBLAS_NUM_THREADS=1 python migrate_to_prod.py \\
        --fixture all_db_backup.json --skip-staff --skip-attendance

    # Everything:
    OPENBLAS_NUM_THREADS=1 python migrate_to_prod.py --fixture all_db_backup.json

    # Dry-run preview:
    OPENBLAS_NUM_THREADS=1 python migrate_to_prod.py \\
        --fixture all_db_backup.json --dry-run --skip-staff
"""

import os, sys, json, argparse
from pathlib import Path

# ── CLI ───────────────────────────────────────────────────────────────────────
parser = argparse.ArgumentParser()
parser.add_argument('--fixture', default='all_db_backup.json')
parser.add_argument('--dry-run', action='store_true')
parser.add_argument('--skip-staff', action='store_true',
                    help='Skip user/staff creation (staff already in prod)')
parser.add_argument('--skip-attendance', action='store_true')
parser.add_argument('--settings', default='backend.settings_production')
args = parser.parse_args()

# ── Django setup ──────────────────────────────────────────────────────────────
sys.path.insert(0, str(Path(__file__).resolve().parent))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', args.settings)
os.environ.setdefault('OPENBLAS_NUM_THREADS', '1')
os.environ.setdefault('OMP_NUM_THREADS', '1')

import django
django.setup()

from core.models import CustomUser, School
from academics.models import SchoolClass, Subject, ClassSubject, Attendance
from finance.models import FeeStructure, ExpenseCategory
from staff.models import Staff
from students.models import Student

DRY = args.dry_run
if DRY:
    print("DRY RUN - nothing will be written\n")

# ── Load fixture ──────────────────────────────────────────────────────────────
fixture_path = Path(args.fixture)
if not fixture_path.exists():
    sys.exit(f"Fixture not found: {fixture_path}")

with open(fixture_path, encoding='utf-8') as f:
    raw = json.load(f)

print(f"Loaded {len(raw)} records from {fixture_path.name}\n")


# ── Helpers ───────────────────────────────────────────────────────────────────
def by_model(name):
    return [r for r in raw if r['model'] == name]

def clean(value):
    """Strip Unicode replacement characters that MySQL utf8mb3 rejects."""
    if not isinstance(value, str):
        return value
    return value.replace('\ufffd', '').replace('\x00', '').strip()

stats = {}
def log(model, action, name=''):
    stats.setdefault(model, {'created': 0, 'updated': 0, 'skipped': 0})
    stats[model][action] += 1
    sym = {'created': '+', 'updated': '~', 'skipped': '-'}[action]
    print(f"  {sym} [{model}] {action:8s}  {name}")


# ══════════════════════════════════════════════════════════════════════════════
# 1. SCHOOL
# ══════════════════════════════════════════════════════════════════════════════
print("--- 1. SCHOOL ---")

school_map = {}
for r in by_model('core.school'):
    f = r['fields']
    if DRY:
        print(f"  Would upsert: {f['name']}")
        continue
    obj, created = School.objects.update_or_create(
        code=f['code'],
        defaults={
            'name':            clean(f['name']),
            'address':         clean(f.get('address', '')),
            'city':            clean(f.get('city', '')),
            'quarter':         clean(f.get('quarter', '')),
            'phone':           clean(f.get('phone', '')),
            'email':           clean(f.get('email', '')),
            'website':         clean(f.get('website', '')),
            'principal_name':  clean(f.get('principal_name', '')),
            'principal_phone': clean(f.get('principal_phone', '')),
            'academic_year':   f.get('academic_year', '2025-2026'),
            'current_term':    f.get('current_term', 1),
            'is_bilingual':    f.get('is_bilingual', True),
        }
    )
    school_map[r['pk']] = obj
    log('school', 'created' if created else 'updated', f['name'])

default_school = School.objects.first()
# If school already existed and was updated, populate map
if not school_map:
    for r in by_model('core.school'):
        try:
            school_map[r['pk']] = School.objects.get(code=r['fields']['code'])
        except School.DoesNotExist:
            pass
if not school_map:
    school_map[1] = default_school
print()


# ══════════════════════════════════════════════════════════════════════════════
# 2. SUBJECTS
# ══════════════════════════════════════════════════════════════════════════════
print("--- 2. SUBJECTS ---")

subject_map = {}
for r in by_model('academics.subject'):
    f = r['fields']
    if DRY:
        print(f"  Would upsert: {f['name']}")
        subject_map[r['pk']] = r['pk']
        continue
    obj, created = Subject.objects.update_or_create(
        code=f['code'],
        defaults={
            'name':        clean(f['name']),
            'name_fr':     clean(f.get('name_fr', '')),
            'category':    f.get('category', 'core'),
            'description': clean(f.get('description', '')),
        }
    )
    subject_map[r['pk']] = obj
    log('subject', 'created' if created else 'updated', f['name'])
print()


# ══════════════════════════════════════════════════════════════════════════════
# 3. CLASSES  (pass 1 - no class_teacher yet)
# ══════════════════════════════════════════════════════════════════════════════
print("--- 3. SCHOOL CLASSES ---")

class_map = {}
for r in by_model('academics.schoolclass'):
    f = r['fields']
    if DRY:
        print(f"  Would upsert: {f['code']}")
        class_map[r['pk']] = r['pk']
        continue
    obj, created = SchoolClass.objects.update_or_create(
        code=f['code'],
        academic_year=f.get('academic_year', '2025-2026'),
        defaults={
            'name':         clean(f['name']),
            'level':        f['level'],
            'section':      f.get('section', 'A'),
            'section_type': f.get('section_type', 'english'),
            'capacity':     f.get('capacity', 40),
            'room_number':  f.get('room_number', ''),
            'class_teacher': None,
        }
    )
    class_map[r['pk']] = obj
    log('schoolclass', 'created' if created else 'updated', f['code'])
print()


# ══════════════════════════════════════════════════════════════════════════════
# 4. FEE STRUCTURES
# ══════════════════════════════════════════════════════════════════════════════
print("--- 4. FEE STRUCTURES ---")

for r in by_model('finance.feestructure'):
    f = r['fields']
    name    = clean(f['name'])
    name_fr = clean(f.get('name_fr', ''))
    if DRY:
        print(f"  Would upsert: {name}")
        continue
    obj, created = FeeStructure.objects.update_or_create(
        name=name,
        academic_year=f.get('academic_year', '2025-2026'),
        defaults={
            'name_fr':            name_fr,
            'fee_type':           f['fee_type'],
            'class_level':        f['class_level'],
            'term':               f['term'],
            'amount':             f['amount'],
            'payment_frequency':  f.get('payment_frequency', 'termly'),
            'is_mandatory':       f.get('is_mandatory', True),
            'description':        clean(f.get('description', '')),
            'description_fr':     clean(f.get('description_fr', '')),
            'is_active':          f.get('is_active', True),
        }
    )
    log('feestructure', 'created' if created else 'updated', name[:55])
print()


# ══════════════════════════════════════════════════════════════════════════════
# 5. EXPENSE CATEGORIES
# ══════════════════════════════════════════════════════════════════════════════
print("--- 5. EXPENSE CATEGORIES ---")

for r in by_model('finance.expensecategory'):
    f = r['fields']
    if DRY:
        print(f"  Would upsert: {f['name']}")
        continue
    obj, created = ExpenseCategory.objects.update_or_create(
        name=clean(f['name']),
        defaults={
            'name_fr':     clean(f.get('name_fr', '')),
            'description': clean(f.get('description', '')),
            'is_active':   f.get('is_active', True),
        }
    )
    log('expensecategory', 'created' if created else 'updated', f['name'])
print()


# ══════════════════════════════════════════════════════════════════════════════
# 6 & 7. USERS + STAFF
# ══════════════════════════════════════════════════════════════════════════════
user_records = {r['pk']: r['fields'] for r in by_model('core.customuser')}
user_map  = {}
staff_map = {}

if args.skip_staff:
    print("--- 6-7. USERS/STAFF --- skipped (--skip-staff)")
    # Build maps from existing prod DB so class-teacher linking works
    for old_pk, f in user_records.items():
        try:
            user_map[old_pk] = CustomUser.objects.get(username=f['username'])
        except CustomUser.DoesNotExist:
            pass
    for r in by_model('staff.staff'):
        u = user_map.get(r['fields']['user'])
        if u:
            try:
                staff_map[r['pk']] = Staff.objects.get(user=u)
            except Staff.DoesNotExist:
                pass
    print(f"  Mapped {len(user_map)} users, {len(staff_map)} staff from prod DB\n")

else:
    print("--- 6. CUSTOM USERS ---")
    for old_pk, f in user_records.items():
        if f.get('is_superuser'):
            try:
                user_map[old_pk] = CustomUser.objects.get(username=f['username'])
            except CustomUser.DoesNotExist:
                pass
            print(f"  - skipped superuser: {f['username']}")
            continue
        if DRY:
            print(f"  Would upsert: {f['username']}")
            continue
        defaults = {
            'first_name': f.get('first_name', ''),
            'last_name':  f.get('last_name', ''),
            'email':      f.get('email', ''),
            'is_staff':   f.get('is_staff', False),
            'is_active':  f.get('is_active', True),
            'user_type':  f.get('user_type', 'teacher'),
            'phone':      f.get('phone', ''),
            'mobile_money_number': f.get('mobile_money_number', ''),
            'employee_id':  f.get('employee_id'),
            'teacher_id':   f.get('teacher_id', ''),
            'access_level': f.get('access_level', 1),
            'oversees_section': f.get('oversees_section', 'none'),
            'financial_access': f.get('financial_access', False),
            'is_primary_contact': f.get('is_primary_contact', False),
        }
        obj, created = CustomUser.objects.get_or_create(
            username=f['username'], defaults=defaults)
        if not created:
            for k, v in defaults.items():
                setattr(obj, k, v)
            obj.save()
        else:
            obj.password = f['password']
            obj.save()
        user_map[old_pk] = obj
        log('customuser', 'created' if created else 'updated', f['username'])

    print()
    print("--- 7. STAFF PROFILES ---")
    for r in by_model('staff.staff'):
        f = r['fields']
        user_obj = user_map.get(f['user'])
        if not user_obj:
            log('staff', 'skipped', f"pk={r['pk']} no user")
            continue
        homeroom_obj = class_map.get(f.get('homeroom_class'))
        if DRY:
            print(f"  Would upsert staff for: {user_obj}")
            continue
        obj, created = Staff.objects.update_or_create(
            user=user_obj,
            defaults={
                'school':       school_map.get(f.get('school'), default_school),
                'employee_id':  f.get('employee_id', ''),
                'title':        f.get('title', ''),
                'date_of_birth': f.get('date_of_birth'),
                'gender':       f.get('gender', 'M'),
                'nationality':  f.get('nationality', 'Cameroonian'),
                'marital_status': f.get('marital_status', 'single'),
                'phone_country_code': f.get('phone_country_code', '+237'),
                'phone_number': f.get('phone_number', ''),
                'alt_phone_country_code': f.get('alt_phone_country_code', '+237'),
                'alternate_phone': f.get('alternate_phone', ''),
                'email':        f.get('email', ''),
                'address':      clean(f.get('address', '')),
                'quarter':      f.get('quarter', ''),
                'city':         f.get('city', 'Douala'),
                'emergency_contact_name': f.get('emergency_contact_name', ''),
                'emergency_phone_country_code': f.get('emergency_phone_country_code', '+237'),
                'emergency_contact_phone': f.get('emergency_contact_phone', ''),
                'emergency_contact_relationship': f.get('emergency_contact_relationship', ''),
                'role':         f.get('role', 'teacher'),
                'department':   f.get('department', ''),
                'employment_status': f.get('employment_status', 'permanent'),
                'hire_date':    f.get('hire_date'),
                'qualifications': clean(f.get('qualifications', '')),
                'specialization': f.get('specialization', ''),
                'years_of_experience': f.get('years_of_experience', 0),
                'is_class_teacher': f.get('is_class_teacher', False),
                'homeroom_class': homeroom_obj,
                'bank_name':    f.get('bank_name', ''),
                'notes':        clean(f.get('notes', '')),
                'is_active':    f.get('is_active', True),
            }
        )
        staff_map[r['pk']] = obj
        log('staff', 'created' if created else 'updated',
            f"{user_obj.first_name} {user_obj.last_name}")
    print()


# ══════════════════════════════════════════════════════════════════════════════
# 7b. LINK CLASS TEACHERS
# ══════════════════════════════════════════════════════════════════════════════
print("--- 7b. LINK CLASS TEACHERS ---")

for r in by_model('academics.schoolclass'):
    f = r['fields']
    old_teacher_pk = f.get('class_teacher')
    if not old_teacher_pk:
        continue
    new_class = class_map.get(r['pk'])
    new_staff = staff_map.get(old_teacher_pk)
    if not new_class or not new_staff:
        print(f"  - {f['code']} teacher pk={old_teacher_pk} not mapped")
        continue
    if DRY:
        print(f"  Would link {f['code']} -> staff_pk={old_teacher_pk}")
        continue
    new_class.class_teacher = new_staff
    new_class.save(update_fields=['class_teacher'])
    print(f"  ~ {new_class.code:10s} -> {new_staff.user.get_full_name()}")
print()


# ══════════════════════════════════════════════════════════════════════════════
# 8. STUDENTS
# ══════════════════════════════════════════════════════════════════════════════
print("--- 8. STUDENTS ---")

student_map = {}
for r in by_model('students.student'):
    f = r['fields']
    new_class = class_map.get(f.get('current_class'))
    if DRY:
        print(f"  Would upsert: {f['first_name']} {f['last_name']}")
        continue
    obj, created = Student.objects.update_or_create(
        student_id=f['student_id'],
        defaults={
            'first_name':    clean(f['first_name']),
            'last_name':     clean(f['last_name']),
            'date_of_birth': f.get('date_of_birth'),
            'place_of_birth': clean(f.get('place_of_birth') or ''),
            'gender':        f.get('gender', 'M'),
            'nationality':   f.get('nationality', 'Cameroonian'),
            'home_address':  clean(f.get('home_address', '')),
            'quarter':       f.get('quarter', ''),
            'city':          f.get('city', 'Douala'),
            'phone_number':  f.get('phone_number', ''),
            'email':         f.get('email', ''),
            'emergency_contact_name':  f.get('emergency_contact_name', ''),
            'emergency_contact_phone': f.get('emergency_contact_phone', ''),
            'emergency_contact_relationship': f.get('emergency_contact_relationship', ''),
            'blood_group':   f.get('blood_group', 'unknown'),
            'allergies':     f.get('allergies', ''),
            'chronic_conditions': f.get('chronic_conditions', ''),
            'medications':   f.get('medications', ''),
            'admission_date': f.get('admission_date'),
            'admission_class': f.get('admission_class', ''),
            'current_class': new_class,
            'status':        f.get('status', 'active'),
            'notes':         clean(f.get('notes', '')),
            'imported_from': f.get('imported_from', ''),
        }
    )
    student_map[r['pk']] = obj
    log('student', 'created' if created else 'updated',
        f"{f['first_name']} {f['last_name']} ({f['student_id']})")
print()


# ══════════════════════════════════════════════════════════════════════════════
# 9. CLASS SUBJECTS
# ══════════════════════════════════════════════════════════════════════════════
print("--- 9. CLASS SUBJECT ASSIGNMENTS ---")

for r in by_model('academics.classsubject'):
    f = r['fields']
    new_class   = class_map.get(f['school_class'])
    new_subject = subject_map.get(f['subject'])
    new_teacher = staff_map.get(f.get('teacher'))

    if not new_class or not new_subject:
        print(f"  - skipped: class_pk={f['school_class']} subject_pk={f['subject']} not mapped")
        continue
    if DRY:
        print(f"  Would upsert: class_pk={f['school_class']} / subject_pk={f['subject']}")
        continue
    obj, created = ClassSubject.objects.update_or_create(
        school_class=new_class,
        subject=new_subject,
        defaults={
            'teacher':       new_teacher,
            'hours_per_week': f.get('hours_per_week', 0),
        }
    )
    log('classsubject', 'created' if created else 'updated',
        f"{new_class.code} / {new_subject.name}")
print()


# ══════════════════════════════════════════════════════════════════════════════
# 10. ATTENDANCE
# ══════════════════════════════════════════════════════════════════════════════
attendance_records = by_model('academics.attendance')
if args.skip_attendance:
    print(f"--- 10. ATTENDANCE --- skipped ({len(attendance_records)} records)\n")
else:
    print(f"--- 10. ATTENDANCE ({len(attendance_records)} records) ---")
    n_created = n_skipped = 0
    for r in attendance_records:
        f = r['fields']
        new_student = student_map.get(f['student'])
        new_class   = class_map.get(f['school_class'])
        marked_by   = user_map.get(f.get('marked_by'))
        if not new_student or not new_class or DRY:
            n_skipped += 1
            continue
        obj, created = Attendance.objects.get_or_create(
            student=new_student, date=f['date'], school_class=new_class,
            defaults={
                'status':    f.get('status', 'present'),
                'period':    f.get('period', 'full_day'),
                'marked_by': marked_by,
                'remarks':   f.get('remarks', ''),
            }
        )
        if created: n_created += 1
        else: n_skipped += 1
    print(f"  + created: {n_created}  |  - already existed: {n_skipped}")
    print()


# ══════════════════════════════════════════════════════════════════════════════
# SUMMARY
# ══════════════════════════════════════════════════════════════════════════════
print("--- SUMMARY ---")
for model, counts in stats.items():
    print(f"  {model:22s}  +{counts['created']:3d} created  "
          f"~{counts['updated']:3d} updated  -{counts['skipped']:3d} skipped")
print()
if DRY:
    print("DRY RUN complete - nothing written.")
else:
    print("Migration complete!")
    print("Verify at:")
    print("  https://raphabethel.org/portal/staff/")
    print("  https://raphabethel.org/portal/students/")
    print("  https://raphabethel.org/portal/academics/classes/")
    print("  https://raphabethel.org/portal/finance/fees/")