"""
Management command to populate staff.department ForeignKey from department_name text field.

This command:
1. Finds all staff with department_name text but NULL department ForeignKey
2. Matches department_name to actual Department records
3. Updates the department ForeignKey
4. Optionally creates missing departments with --create-missing
5. Optionally deletes and recreates all departments with --force-create

Usage:
    python manage.py migrate_staff_departments
    python manage.py migrate_staff_departments --dry-run
    python manage.py migrate_staff_departments --hospital-code=H001
    python manage.py migrate_staff_departments --create-missing
    python manage.py migrate_staff_departments --force-create
    python manage.py migrate_staff_departments --force-create --no-confirm
"""
from django.core.management.base import BaseCommand
from django.db import transaction
from apps.organizations.models import Staff, Department


class Command(BaseCommand):
    help = 'Populate staff.department ForeignKey from department_name text field'

    def add_arguments(self, parser):
        parser.add_argument(
            '--dry-run',
            action='store_true',
            dest='dry_run',
            default=False,
            help='Show what would be updated without making changes',
        )
        parser.add_argument(
            '--hospital-code',
            dest='hospital_code',
            default=None,
            help='Only process staff from this hospital code',
        )
        parser.add_argument(
            '--fuzzy',
            action='store_true',
            dest='fuzzy_match',
            default=False,
            help='Also try fuzzy matching for department names',
        )
        parser.add_argument(
            '--create-missing',
            action='store_true',
            dest='create_missing',
            default=False,
            help='Create missing departments if they do not exist (get-or-create)',
        )
        parser.add_argument(
            '--force-create',
            action='store_true',
            dest='force_create',
            default=False,
            help='Delete all existing departments and recreate from department_name (DESTRUCTIVE)',
        )
        parser.add_argument(
            '--no-confirm',
            action='store_true',
            dest='no_confirm',
            default=False,
            help='Skip confirmation prompt for --force-create',
        )

    def handle(self, *args, **options):
        dry_run = options['dry_run']
        hospital_code = options['hospital_code']
        fuzzy_match = options['fuzzy_match']
        create_missing = options['create_missing']
        force_create = options['force_create']
        no_confirm = options['no_confirm']

        # Force-create mode: delete and recreate all departments
        if force_create:
            self.handle_force_create(
                dry_run=dry_run,
                hospital_code=hospital_code,
                no_confirm=no_confirm
            )
            return

        # Normal mode: match/create missing
        # Build queryset
        staff_qs = Staff.objects.select_related('hospital').filter(
            department__isnull=True,  # ForeignKey is NULL
            department_name__isnull=False,  # Text field has value
        ).exclude(
            department_name=''  # Exclude empty strings
        )

        if hospital_code:
            staff_qs = staff_qs.filter(hospital__code=hospital_code)
            self.stdout.write(f"Filtering by hospital code: {hospital_code}")

        total_count = staff_qs.count()
        self.stdout.write(f"Found {total_count} staff records with department_name but no department ForeignKey")

        if total_count == 0:
            self.stdout.write(self.style.SUCCESS("No staff records need migration."))
            return

        # Statistics
        exact_matches = 0
        fuzzy_matches = 0
        no_match = 0
        multiple_matches = 0
        created_departments = 0
        created_count = 0

        # Track unmatched department names for reporting
        unmatched_departments = set()
        ambiguous_matches = {}
        
        # Track created departments to avoid duplicates
        created_dept_cache = {}

        for staff in staff_qs:
            dept_name = staff.department_name.strip()
            hospital_id = staff.hospital_id

            # Try exact match (case-insensitive)
            exact_dept = Department.objects.filter(
                hospital_id=hospital_id,
                name__iexact=dept_name,
                status='active'
            ).first()

            if exact_dept:
                exact_matches += 1
                if not dry_run:
                    staff.department = exact_dept
                    staff.save(update_fields=['department'])
                self.stdout.write(f"  ✓ EXACT: {staff.get_full_name()} -> {exact_dept.name}")
                continue

            # Try partial match if fuzzy enabled
            if fuzzy_match:
                partial_depts = Department.objects.filter(
                    hospital_id=hospital_id,
                    name__icontains=dept_name,
                    status='active'
                )

                if partial_depts.count() == 1:
                    fuzzy_matches += 1
                    matched_dept = partial_depts.first()
                    if not dry_run:
                        staff.department = matched_dept
                        staff.save(update_fields=['department'])
                    self.stdout.write(f"  ~ FUZZY: {staff.get_full_name()} -> {matched_dept.name} (from '{dept_name}')")
                    continue
                elif partial_depts.count() > 1:
                    multiple_matches += 1
                    ambiguous_matches[staff.id] = {
                        'staff_name': staff.get_full_name(),
                        'department_name': dept_name,
                        'matches': [d.name for d in partial_depts]
                    }
                    self.stdout.write(f"  ? MULTIPLE: {staff.get_full_name()} '{dept_name}' matches: {[d.name for d in partial_depts]}")
                    continue

            # No match found - check if we should create it
            if create_missing:
                # Check cache first to avoid creating duplicates in same run
                cache_key = (hospital_id, dept_name.lower())
                if cache_key in created_dept_cache:
                    # Use already created department from this run
                    new_dept = created_dept_cache[cache_key]
                    created_count += 1
                    if not dry_run:
                        staff.department = new_dept
                        staff.save(update_fields=['department'])
                    self.stdout.write(f"  + REUSE CREATED: {staff.get_full_name()} -> {new_dept.name}")
                    continue
                
                # Get or create the department
                # Generate a code from the department name
                code = dept_name.upper().replace(' ', '_').replace('-', '_')[:20]
                # Ensure code is unique by adding suffix if needed
                base_code = code
                suffix = 1
                while Department.objects.filter(hospital_id=hospital_id, code=code).exists():
                    code = f"{base_code[:17]}_{suffix}"
                    suffix += 1
                
                if not dry_run:
                    with transaction.atomic():
                        new_dept, was_created = Department.objects.get_or_create(
                            hospital_id=hospital_id,
                            name__iexact=dept_name,
                            defaults={
                                'name': dept_name,
                                'name_ar': dept_name,  # Use same name for Arabic initially
                                'code': code,
                                'status': 'active',
                            }
                        )
                        # If it already existed but wasn't matched, update staff
                        if not was_created:
                            # Department existed but with different case - update name
                            new_dept.name = dept_name
                            new_dept.save(update_fields=['name'])
                else:
                    # Dry run - simulate creation
                    new_dept = type('Department', (), {'name': dept_name, 'id': 'NEW'})()
                    was_created = True
                
                if was_created or True:  # Always count for dry-run
                    created_departments += 1
                    created_dept_cache[cache_key] = new_dept
                    created_count += 1
                    if not dry_run:
                        staff.department = new_dept
                        staff.save(update_fields=['department'])
                    action = "CREATED" if was_created else "LINKED"
                    self.stdout.write(f"  + {action}: {staff.get_full_name()} -> {new_dept.name}")
                continue
            
            # No match found and not creating
            no_match += 1
            unmatched_departments.add((staff.hospital.name, dept_name))
            self.stdout.write(f"  ✗ NO MATCH: {staff.get_full_name()} '{dept_name}'")

        # Summary
        self.stdout.write("\n" + "=" * 60)
        self.stdout.write("MIGRATION SUMMARY")
        self.stdout.write("=" * 60)
        self.stdout.write(f"Total staff processed: {total_count}")
        self.stdout.write(self.style.SUCCESS(f"  Exact matches: {exact_matches}"))
        if fuzzy_match:
            self.stdout.write(self.style.WARNING(f"  Fuzzy matches: {fuzzy_matches}"))
            self.stdout.write(self.style.WARNING(f"  Multiple matches (skipped): {multiple_matches}"))
        if create_missing:
            self.stdout.write(self.style.SUCCESS(f"  Departments created: {created_departments}"))
            self.stdout.write(self.style.SUCCESS(f"  Staff linked to new departments: {created_count}"))
        self.stdout.write(self.style.ERROR(f"  No match found: {no_match}"))

        if dry_run:
            self.stdout.write(self.style.WARNING("\nDRY RUN - No changes were made"))
            self.stdout.write("Run without --dry-run to apply changes")
        else:
            self.stdout.write(self.style.SUCCESS(f"\nSuccessfully updated {exact_matches + fuzzy_matches} staff records"))

        # Report unmatched departments
        if unmatched_departments:
            self.stdout.write("\n" + "=" * 60)
            self.stdout.write("UNMATCHED DEPARTMENTS (need manual creation or name fix)")
            self.stdout.write("=" * 60)
            for hospital_name, dept_name in sorted(unmatched_departments):
                self.stdout.write(f"  {hospital_name}: '{dept_name}'")

        # Report ambiguous matches
        if ambiguous_matches:
            self.stdout.write("\n" + "=" * 60)
            self.stdout.write("AMBIGUOUS MATCHES (multiple departments matched)")
            self.stdout.write("=" * 60)
            for staff_id, info in ambiguous_matches.items():
                self.stdout.write(f"  {info['staff_name']} ('{info['department_name']}')")
                for match in info['matches']:
                    self.stdout.write(f"    - {match}")

        # Suggest creating missing departments
        if unmatched_departments and not dry_run and not create_missing:
            self.stdout.write("\n" + "=" * 60)
            self.stdout.write("TIP: Run with --create-missing to auto-create departments")
            self.stdout.write("     Or create them manually in Django Admin")

    def handle_force_create(self, dry_run, hospital_code, no_confirm):
        """Delete all existing departments and recreate from department_name field."""
        self.stdout.write(self.style.WARNING("\n" + "=" * 60))
        self.stdout.write(self.style.WARNING("FORCE-CREATE MODE"))
        self.stdout.write(self.style.WARNING("=" * 60))
        self.stdout.write(self.style.WARNING("This will DELETE ALL EXISTING DEPARTMENTS and recreate them."))
        self.stdout.write(self.style.WARNING("This is a DESTRUCTIVE operation!\n"))

        # Build querysets
        dept_qs = Department.objects.all()
        staff_qs = Staff.objects.select_related('hospital').filter(
            department_name__isnull=False
        ).exclude(
            department_name=''
        )

        if hospital_code:
            dept_qs = dept_qs.filter(hospital__code=hospital_code)
            staff_qs = staff_qs.filter(hospital__code=hospital_code)
            self.stdout.write(f"Filtering by hospital code: {hospital_code}")

        # Count what will be affected
        dept_count = dept_qs.count()
        staff_count = staff_qs.count()
        
        # Get unique department names from staff
        unique_dept_names = set()
        for staff in staff_qs:
            dept_name = staff.department_name.strip()
            if dept_name:
                unique_dept_names.add((staff.hospital_id, dept_name))

        self.stdout.write(f"\nDepartments to be DELETED: {dept_count}")
        self.stdout.write(f"Staff records to be updated: {staff_count}")
        self.stdout.write(f"New departments to be CREATED: {len(unique_dept_names)}")

        if dry_run:
            self.stdout.write(self.style.WARNING("\nDRY RUN - No changes will be made"))
            self.stdout.write("\nDepartments that would be deleted:")
            for dept in dept_qs:
                self.stdout.write(f"  - {dept.hospital.name}: {dept.name}")
            self.stdout.write("\nDepartments that would be created:")
            for hospital_id, dept_name in sorted(unique_dept_names):
                self.stdout.write(f"  + {dept_name}")
            return

        # Confirmation
        if not no_confirm:
            self.stdout.write("\n" + "=" * 60)
            confirm = input("Are you sure you want to proceed? Type 'yes' to confirm: ")
            if confirm.lower() != 'yes':
                self.stdout.write(self.style.ERROR("Operation cancelled."))
                return

        # Execute force-create
        self.stdout.write("\nProceeding with force-create...")
        
        with transaction.atomic():
            # Step 1: Clear department foreign keys on staff
            self.stdout.write("Step 1: Clearing staff.department foreign keys...")
            staff_qs.update(department=None)
            self.stdout.write(self.style.SUCCESS(f"  Cleared {staff_count} staff records"))

            # Step 2: Delete all existing departments
            self.stdout.write("Step 2: Deleting existing departments...")
            deleted_count, _ = dept_qs.delete()
            self.stdout.write(self.style.SUCCESS(f"  Deleted {deleted_count} departments"))

            # Step 3: Create new departments from department_name
            self.stdout.write("Step 3: Creating new departments from department_name...")
            created_departments = {}
            created_count = 0
            
            for staff in staff_qs:
                dept_name = staff.department_name.strip()
                hospital_id = staff.hospital_id
                
                if not dept_name:
                    continue
                
                cache_key = (hospital_id, dept_name.lower())
                
                # Check if we already created this department
                if cache_key in created_departments:
                    new_dept = created_departments[cache_key]
                else:
                    # Generate a unique code
                    code = dept_name.upper().replace(' ', '_').replace('-', '_')[:20]
                    base_code = code
                    suffix = 1
                    # Ensure uniqueness within created departments
                    while any(d.code == code and d.hospital_id == hospital_id for d in created_departments.values()):
                        code = f"{base_code[:17]}_{suffix}"
                        suffix += 1
                    
                    # Create the department
                    new_dept = Department.objects.create(
                        hospital_id=hospital_id,
                        name=dept_name,
                        name_ar=dept_name,
                        code=code,
                        status='active',
                    )
                    created_departments[cache_key] = new_dept
                    created_count += 1
                    self.stdout.write(f"  + Created: {new_dept.name}")
                
                # Link staff to department
                staff.department = new_dept
                staff.save(update_fields=['department'])

            self.stdout.write(self.style.SUCCESS(f"  Created {created_count} unique departments"))

        # Summary
        self.stdout.write("\n" + "=" * 60)
        self.stdout.write(self.style.SUCCESS("FORCE-CREATE COMPLETE"))
        self.stdout.write("=" * 60)
        self.stdout.write(f"Departments deleted: {deleted_count}")
        self.stdout.write(f"Departments created: {created_count}")
        self.stdout.write(f"Staff records updated: {staff_count}")