# staff/management/commands/import_staff.py
from django.core.management.base import BaseCommand
from django.contrib.auth import get_user_model
from staff.models import Staff
import pandas as pd
from datetime import datetime

User = get_user_model()

class Command(BaseCommand):
    help = 'Import staff from Excel file'
    
    def add_arguments(self, parser):
        parser.add_argument('excel_file', type=str, help='Path to Excel file')
        parser.add_argument('--sheet', type=str, default='Staff List', help='Sheet name to import')
        parser.add_argument('--preview', action='store_true', help='Preview only, do not import')
    
    def handle(self, *args, **options):
        excel_file = options['excel_file']
        sheet_name = options['sheet']
        preview = options['preview']
        
        try:
            df = pd.read_excel(excel_file, sheet_name=sheet_name)
        except Exception as e:
            self.stdout.write(self.style.ERROR(f'Error reading file: {e}'))
            return
        
        if preview:
            self.stdout.write(self.style.SUCCESS(f'Preview of {sheet_name}:'))
            self.stdout.write(f'Total rows: {len(df)}')
            self.stdout.write('\nFirst 5 rows:')
            self.stdout.write(df.head().to_string())
            return
        
        imported = 0
        errors = []
        
        for index, row in df.iterrows():
            try:
                # Map columns based on your Excel structure
                name = str(row.get('Name', '')).strip()
                if not name:
                    continue
                
                # Parse name
                name_parts = name.split()
                first_name = name_parts[0]
                last_name = ' '.join(name_parts[1:]) if len(name_parts) > 1 else ''
                
                # Create username
                username = name.replace(' ', '').lower()
                
                # Get role from Group column
                role_group = str(row.get('Group', '')).strip().lower()
                role_map = {
                    'super admin': 'proprietor',
                    'admin': 'admin',
                    'teacher': 'teacher',
                }
                role = role_map.get(role_group, 'teacher')
                
                # Get department from Subgroup
                department = str(row.get('Subgroup', '')).strip().lower()
                dept_map = {
                    'primary': 'english',
                    'nursery': 'nursery',
                    'bilingual': 'bilingual',
                    'financial': 'finance',
                }
                department = dept_map.get(department, 'english')
                
                # Create user
                user, created = User.objects.get_or_create(
                    username=username,
                    defaults={
                        'first_name': first_name,
                        'last_name': last_name,
                        'email': f"{username}@raphabethel.org",
                        'is_staff': True,
                    }
                )
                
                # Get phone number
                phone = str(row.get('Contact Number', '')).strip().replace(' ', '')
                
                # Create staff profile
                staff, staff_created = Staff.objects.get_or_create(
                    user=user,
                    defaults={
                        'employee_id': f"STF{user.id:04d}",
                        'role': role,
                        'department': department,
                        'phone_number': phone,
                        'school_id': 1,
                        'is_active': True,
                        'imported_from': excel_file,
                    }
                )
                
                if staff_created:
                    imported += 1
                    self.stdout.write(f'  ✓ Imported: {name} ({role})')
                
            except Exception as e:
                errors.append(f"Row {index + 2}: {str(e)}")
        
        self.stdout.write(self.style.SUCCESS(f'\n✅ Successfully imported {imported} staff members'))
        
        if errors:
            self.stdout.write(self.style.WARNING(f'\n⚠️ {len(errors)} errors occurred:'))
            for error in errors[:5]:
                self.stdout.write(f'  • {error}')