import re

import pandas as pd
from django.core.management.base import BaseCommand
from django.db.models import Q
from django.db import transaction

from apps.organizations.models import Department, Hospital, Staff

LOCATION_TO_HOSPITAL = {
    "Suwaidi": "HH-S",
    "Nuzha": "HH-N",
    "Olaya": "HH-A",
    "السويدي": "HH-S",
    "النزهة": "HH-N",
    "العليا": "HH-A",
}

HR_NAME_ALIASES = {
    "Accident And Emergency": "Emergency Medicine Department",
    "Corporate Administration": "Executive Administration",
    "Senior Management Offices": "Executive Administration",
    "Corporate Communication Department": "Patient Experience Department",
    "Marketing Department": "Patient Experience Department",
    "Porter Department": "Security Department",
    "Transportation Department": "Security Department",
    "Supply Chain": "Facility Management & Maintenance Department",
    "Pharmacy Warehouse Alaziziyah": "Pharmacy Department",
    "Family Medicine": "Outpatient Department",
    "Business Development": "Executive Administration",
    "Continuous Medical Education Managment": "Medical Administration",
    "Academic Education And Training Affairs": "Medical Administration",
    "Innovation Communication Management": "Medical Administration",
    "Transformation And Change Management": "Medical Administration",
    "Talent Acquisition Department": "HR Department",
    "Internal Audit": "Financial Collection & Claims Department",
    "Legal Affairs Department": "Executive Administration",
    "Cybersecurity Management": "Information Technology Department",
    "Rehabilitation Center": "Outpatient Department",
}


def normalize_name(name):
    if not name:
        return ""
    return re.sub(r"\s+", " ", name.strip()).strip()


class Command(BaseCommand):
    help = "Import staff from employees.xlsx (both Arabic Sheet1 + English Sheet2)"

    def add_arguments(self, parser):
        parser.add_argument("--file", default="data/employees.xlsx")
        parser.add_argument("--update-existing", action="store_true")

    def handle(self, *args, **options):
        filepath = options["file"]
        update_existing = options["update_existing"]

        hospitals = {h.code: h for h in Hospital.objects.all()}
        if not hospitals:
            self.stderr.write("No hospitals found. Create them first.")
            return

        self.stdout.write(f"Available hospitals: {list(hospitals.keys())}")

        df_ar = pd.read_excel(filepath, header=None, skiprows=3, sheet_name=0)
        df_ar.columns = [
            "_skip", "employee_number", "name_ar", "manager", "national_id",
            "location_ar", "department_ar", "section_ar", "job_title_ar",
            "mobile", "personal_email", "work_email",
        ]
        df_ar = df_ar.drop(columns=["_skip"])
        df_ar = df_ar[df_ar["employee_number"].notna()]
        df_ar = df_ar[df_ar["name_ar"].notna()]
        df_ar["employee_id"] = df_ar["employee_number"].apply(
            lambda x: str(int(x)) if pd.notna(x) and str(x).strip().replace(".", "").isdigit() else None
        )

        df_en = pd.read_excel(filepath, header=None, skiprows=3, sheet_name="Sheet2")
        df_en.columns = [
            "_skip", "employee_number", "name", "manager", "national_id",
            "location", "department", "section", "job_title", "country",
            "mobile", "personal_email", "work_email",
        ]
        df_en = df_en.drop(columns=["_skip"])
        df_en = df_en[df_en["employee_number"].notna()]
        df_en = df_en[df_en["name"].notna()]
        df_en["employee_id"] = df_en["employee_number"].apply(
            lambda x: str(int(x)) if pd.notna(x) and str(x).strip().replace(".", "").isdigit() else None
        )

        en_lookup = {}
        for _, row in df_en.iterrows():
            eid = row.get("employee_id")
            if eid:
                en_lookup[eid] = row

        self.stdout.write(f"Arabic records: {len(df_ar)}, English records: {len(df_en)}")

        dept_cache = {}
        stats = {
            "created": 0, "updated": 0, "skipped": 0,
            "no_dept": 0, "no_hospital": 0, "errors": 0,
        }
        staff_map = {}
        existing_staff = {s.employee_id: s for s in Staff.objects.all()}

        with transaction.atomic():
            for idx, (_, row_ar) in enumerate(df_ar.iterrows(), 1):
                try:
                    emp_id = row_ar.get("employee_id")
                    if not emp_id:
                        continue

                    name_ar_raw = normalize_name(str(row_ar.get("name_ar", "")))
                    if not name_ar_raw:
                        continue

                    parts_ar = name_ar_raw.split(None, 1)
                    first_name_ar = parts_ar[0] if parts_ar else ""
                    last_name_ar = parts_ar[1] if len(parts_ar) > 1 else ""

                    row_en = en_lookup.get(emp_id)

                    if row_en is not None:
                        name_en = normalize_name(str(row_en.get("name", "")))
                        parts_en = name_en.split(None, 1)
                        first_name = parts_en[0] if parts_en else ""
                        last_name = parts_en[1] if len(parts_en) > 1 else ""
                        location_en = normalize_name(str(row_en.get("location", "")))
                        dept_en = normalize_name(str(row_en.get("department", "")))
                        section_en = normalize_name(str(row_en.get("section", "")))
                        job_title_en = normalize_name(str(row_en.get("job_title", "")))
                        country = normalize_name(str(row_en.get("country", "")))
                        mobile_en = normalize_name(str(row_en.get("mobile", "")))
                        work_email_en = normalize_name(str(row_en.get("work_email", "")))
                        personal_email_en = normalize_name(str(row_en.get("personal_email", "")))
                    else:
                        name_en = ""
                        first_name = first_name_ar
                        last_name = last_name_ar
                        location_en = ""
                        dept_en = ""
                        section_en = ""
                        job_title_en = ""
                        country = ""
                        mobile_en = ""
                        work_email_en = ""
                        personal_email_en = ""

                    manager_raw = str(row_ar.get("manager", "")).strip() if pd.notna(row_ar.get("manager")) else ""
                    national_id = str(row_ar.get("national_id", "")).strip() if pd.notna(row_ar.get("national_id")) else ""
                    job_title_ar_val = normalize_name(str(row_ar.get("job_title_ar", "")))
                    dept_ar = normalize_name(str(row_ar.get("department_ar", "")))
                    section_ar = normalize_name(str(row_ar.get("section_ar", "")))
                    location_ar = normalize_name(str(row_ar.get("location_ar", "")))
                    mobile_ar = normalize_name(str(row_ar.get("mobile", "")))
                    personal_email_ar = normalize_name(str(row_ar.get("personal_email", "")))
                    work_email_ar = normalize_name(str(row_ar.get("work_email", "")))

                    hospital_code = LOCATION_TO_HOSPITAL.get(location_en) or LOCATION_TO_HOSPITAL.get(location_ar)
                    hospital = hospitals.get(hospital_code) if hospital_code else None

                    if not hospital:
                        stats["no_hospital"] += 1
                        continue

                    department = None
                    if dept_en and hospital_code:
                        cache_key = (hospital_code, dept_en.lower())
                        if cache_key not in dept_cache:
                            dept_cache[cache_key] = self._find_dept(
                                hospitals[hospital_code], dept_en
                            )
                        department = dept_cache[cache_key]

                    if not department and dept_en:
                        stats["no_dept"] += 1

                    existing = existing_staff.get(emp_id)

                    staff_data = {
                        "name": name_en or name_ar_raw,
                        "first_name": first_name or first_name_ar,
                        "last_name": last_name or last_name_ar,
                        "name_ar": name_ar_raw,
                        "first_name_ar": first_name_ar,
                        "last_name_ar": last_name_ar,
                        "staff_type": "other",
                        "department_type": "",
                        "job_title": job_title_en or job_title_ar_val,
                        "job_title_ar": job_title_ar_val,
                        "specialization": "",
                        "email": work_email_en or personal_email_en or work_email_ar or personal_email_ar,
                        "phone": mobile_en or mobile_ar,
                        "hospital": hospital,
                        "department": department,
                        "civil_id": national_id,
                        "location": location_en or location_ar,
                        "location_ar": location_ar,
                        "department_name": dept_en or dept_ar,
                        "department_name_ar": dept_ar,
                        "section": section_en or section_ar,
                        "section_ar": section_ar,
                        "subsection": "",
                        "subsection_ar": "",
                        "country": country,
                        "status": "active",
                    }

                    if existing and update_existing:
                        for k, v in staff_data.items():
                            setattr(existing, k, v)
                        existing.save()
                        staff_map[emp_id] = existing
                        stats["updated"] += 1
                    elif existing:
                        staff_map[emp_id] = existing
                        stats["skipped"] += 1
                    else:
                        staff = Staff(employee_id=emp_id, **staff_data)
                        staff.save()
                        staff_map[emp_id] = staff
                        stats["created"] += 1

                    if idx % 500 == 0:
                        self.stdout.write(f"  Processed {idx}/{len(df_ar)}...")

                except Exception as e:
                    self.stdout.write(self.style.ERROR(f"  [{idx}] Error: {e}"))
                    stats["errors"] += 1

            self.stdout.write("\nLinking managers...")
            manager_lookup = {}
            for _, row in df_ar.iterrows():
                eid = row.get("employee_id")
                mgr_raw = str(row.get("manager", "")).strip() if pd.notna(row.get("manager")) else ""
                if eid and mgr_raw:
                    manager_lookup[eid] = mgr_raw

            linked = 0
            for emp_id, staff in staff_map.items():
                manager_raw = manager_lookup.get(emp_id, "")
                if not manager_raw:
                    continue
                m = re.match(r"^(\d+)\s*-", manager_raw)
                if not m:
                    continue
                mgr_id = m.group(1)
                mgr = staff_map.get(mgr_id)
                if mgr and staff.report_to != mgr:
                    staff.report_to = mgr
                    staff.save(update_fields=["report_to"])
                    linked += 1

            self.stdout.write(f"  Linked {linked} manager relationships")

        self.stdout.write(self.style.SUCCESS(f"\nDone!"))
        self.stdout.write(f"  Created: {stats['created']}")
        self.stdout.write(f"  Updated: {stats['updated']}")
        self.stdout.write(f"  Skipped: {stats['skipped']}")
        self.stdout.write(f"  No dept match: {stats['no_dept']}")
        self.stdout.write(f"  No hospital match: {stats['no_hospital']}")
        self.stdout.write(f"  Errors: {stats['errors']}")

    def _find_dept(self, hospital, hr_dept_name):
        dept = Department.objects.filter(
            hospital=hospital, status="active"
        ).filter(
            Q(hr_name__iexact=hr_dept_name) | Q(name_en__iexact=hr_dept_name)
        ).first()
        if dept:
            return dept
        alias_name_en = HR_NAME_ALIASES.get(hr_dept_name)
        if alias_name_en:
            return Department.objects.filter(
                hospital=hospital, name_en__iexact=alias_name_en, status="active"
            ).first()
        return None
