import re
from decimal import Decimal
from pathlib import Path

from django.core.management.base import BaseCommand

from openpyxl import load_workbook

from apps.standards.models import (
    StandardSource,
    StandardCategory,
    Standard,
    StandardCompliance,
)
from apps.organizations.models import Hospital

DATA_DIR = Path("data/Documents")
NUZHA_ID = "b81b9f41-2044-4160-9e6a-9be6583cddbc"

STATUS_MAP = {
    "محقق بشكل كامل": Standard.ComplianceStatus.MET,
    "محقق جزئيا": Standard.ComplianceStatus.PARTIALLY_MET,
    "غير محقق": Standard.ComplianceStatus.NOT_MET,
    "مطبق": Standard.ComplianceStatus.MET,
    "مطبق جزئيا": Standard.ComplianceStatus.PARTIALLY_MET,
    "غير مطبق": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق جزئيا": Standard.ComplianceStatus.PARTIALLY_MET,
    "غير مطبق جزئياً": Standard.ComplianceStatus.PARTIALLY_MET,
    "غير مطبق كليا": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق كلياً": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق نهائيا": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق نهائياً": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق ": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق  ": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق   ": Standard.ComplianceStatus.NOT_MET,
    "غير مطبق    ": Standard.ComplianceStatus.NOT_MET,
}

METHOD_MAP = {
    "مراجعة المستند": Standard.AssessmentMethod.DOCUMENT_REVIEW,
    "مراجعة الملف الوظيفي": Standard.AssessmentMethod.DOCUMENT_REVIEW,
    "مراجعة الملف الوظيفي/ لجنة الجودة/ مقابلة القيادة التنفيذية": Standard.AssessmentMethod.MULTIPLE,
    "مقابلة الموظفين": Standard.AssessmentMethod.STAFF_INTERVIEW,
    "مقابلة الموظفين/ ملاحظة": Standard.AssessmentMethod.MULTIPLE,
    "ملاحظة": Standard.AssessmentMethod.OBSERVATION,
    "ملاحظة/ مقابلة الموظفين": Standard.AssessmentMethod.MULTIPLE,
    "إثبات": Standard.AssessmentMethod.EVIDENCE,
    "إثبات ": Standard.AssessmentMethod.EVIDENCE,
    "إثبات/ لجنة الجودة": Standard.AssessmentMethod.MULTIPLE,
    "مراجعة المستند/ مقابلة الموظفين": Standard.AssessmentMethod.MULTIPLE,
    "مقابلة الموظفين/ إثبات": Standard.AssessmentMethod.MULTIPLE,
    "ملاحظة/ مقابلة الموظفين": Standard.AssessmentMethod.MULTIPLE,
    "مراجعة المستند/مراجعة السجلات الطبية المغلقة": Standard.AssessmentMethod.DOCUMENT_REVIEW,
    "لجنة الجودة": Standard.AssessmentMethod.QUALITY_COMMITTEE,
    "مقابلة القيادة التنفيذية/ إثبات": Standard.AssessmentMethod.MULTIPLE,
}

PRIORITY_MAP = {
    "عالي": StandardCompliance.Priority.HIGH,
    "عالي ": StandardCompliance.Priority.HIGH,
    "متوسط": StandardCompliance.Priority.MEDIUM,
    "منخفض": StandardCompliance.Priority.LOW,
}

CBAHI_SHEET_CATEGORIES = {
    "القيادة": ("Leadership", "القيادة"),
    "حقوق المرضى وذويهم": ("Patient Rights", "حقوق المرضى وذويهم"),
}

MOH_FRAMEWORK_CATEGORIES = {
    "1": ("Commitment & Leadership", "الالتزام والقيادة", 23),
    "2": ("Advocacy & Participation", "الدعاية والمشاركة", 21),
    "3": ("Resources & Empowerment", "توفير الموارد والتمكين", 26),
    "4": ("Learning & Action", "التعلم والعمل", 18),
    "5": ("Governance & Policies", "الحوكمة والسياسات", 12),
}

MOH_CORRECTIVE_CATEGORIES = {
    "الالتزام والقيادة": ("Commitment & Leadership", "الالتزام والقيادة"),
    "الدعاية والمشاركة": ("Advocacy & Participation", "الدعاية والمشاركة"),
    "توفير الموارد والتمكين": ("Resources & Empowerment", "توفير الموارد والتمكين"),
    "التعلم والعمل": ("Learning & Action", "التعلم والعمل"),
    "الحوكمة و السياسات": ("Governance & Policies", "الحوكمة والسياسات"),
    "الحوكمة و السياسات  ": ("Governance & Policies", "الحوكمة والسياسات"),
}


class Command(BaseCommand):
    help = "Import standards data from Excel files"

    def handle(self, *args, **options):
        hospital = Hospital.objects.get(id=NUZHA_ID)

        cbahi_source = self._ensure_source("CBAHI", "سباهي", "CBAHI")
        moh_source = self._ensure_source("MOH", "وزارة الصحة", "MOH")

        self._clear_existing()

        self.stdout.write("Importing CBAHI standards...")
        self._import_cbahi(cbahi_source, hospital)

        self.stdout.write("Importing MOH corrective action...")
        self._import_moh_corrective(moh_source, hospital)

        self.stdout.write("Importing MOH assessment framework...")
        self._import_moh_framework(moh_source, hospital)

        self.stdout.write(self.style.SUCCESS("Import complete!"))

    def _ensure_source(self, name, name_ar, code):
        source, _ = StandardSource.objects.get_or_create(
            code=code,
            defaults={"name": name, "name_ar": name_ar},
        )
        return source

    def _clear_existing(self):
        StandardCompliance.objects.all().delete()
        Standard.objects.all().delete()
        StandardCategory.objects.filter(source__isnull=False).delete()

    def _map_status(self, status_text):
        if not status_text:
            return Standard.ComplianceStatus.NOT_ASSESSED
        s = str(status_text).strip()
        return STATUS_MAP.get(s, Standard.ComplianceStatus.NOT_ASSESSED)

    def _map_method(self, method_text):
        if not method_text:
            return ""
        m = str(method_text).strip()
        return METHOD_MAP.get(m, "")

    def _map_priority(self, priority_text):
        if not priority_text:
            return ""
        p = str(priority_text).strip()
        return PRIORITY_MAP.get(p, "")

    def _import_cbahi(self, source, hospital):
        wb = load_workbook(DATA_DIR / "Standards - CBAHI - Patient Affairs.xlsx", data_only=True)

        for sheet_name, (en_name, ar_name) in CBAHI_SHEET_CATEGORIES.items():
            ws = wb[sheet_name]
            category = StandardCategory.objects.create(
                name=en_name,
                name_ar=ar_name,
                source=source,
                order=1 if sheet_name == "القيادة" else 2,
            )

            current_parent = None
            order = 0

            for row_idx in range(4, ws.max_row + 1):
                col_a = ws.cell(row_idx, 1).value
                col_b = ws.cell(row_idx, 2).value
                col_c = ws.cell(row_idx, 3).value
                col_d = ws.cell(row_idx, 4).value
                col_e = ws.cell(row_idx, 5).value

                if not col_a and not col_b:
                    continue

                col_a_str = str(col_a).strip() if col_a else ""
                col_b_str = str(col_b).strip() if col_b else ""

                is_heading = bool(col_a_str and not col_b_str and "رقم" in col_a_str)

                if is_heading:
                    order += 1
                    match = re.search(r"رقم\s+(\d+)", col_a_str)
                    heading_num = match.group(1) if match else str(order)
                    current_parent = Standard.objects.create(
                        code=f"CBAHI-{sheet_name[:3]}-{heading_num}",
                        title=col_a_str,
                        title_ar=col_a_str,
                        source=source,
                        category=category,
                        is_heading=True,
                        order_within_category=order,
                    )
                    continue

                if col_a_str and col_b_str:
                    order += 1
                    code = col_a_str
                    if not code.startswith("CBAHI"):
                        code = f"CBAHI-{sheet_name[:3]}-{col_a_str}"

                    standard = Standard.objects.create(
                        code=code,
                        title=col_b_str,
                        title_ar=col_b_str,
                        source=source,
                        category=category,
                        parent_standard=current_parent,
                        assessment_method=self._map_method(col_c),
                        assessment_method_ar=str(col_c).strip() if col_c else "",
                        order_within_category=order,
                    )

                    status = self._map_status(col_d)
                    if status != Standard.ComplianceStatus.NOT_ASSESSED or col_e:
                        StandardCompliance.objects.create(
                            hospital=hospital,
                            standard=standard,
                            status=status,
                            status_ar=str(col_d).strip() if col_d else "",
                            recommendations=str(col_e).strip() if col_e else "",
                        )

    def _import_moh_corrective(self, source, hospital):
        wb = load_workbook(DATA_DIR / "Standards - MOH - الاجراء التصحيحي.xlsx", data_only=True)
        ws = wb.active

        categories = {}
        current_category = None
        order = 0

        for row_idx in range(3, ws.max_row + 1):
            col_a = ws.cell(row_idx, 1).value
            col_b = ws.cell(row_idx, 2).value
            col_d = ws.cell(row_idx, 4).value
            col_e = ws.cell(row_idx, 5).value
            col_f = ws.cell(row_idx, 6).value
            col_g = ws.cell(row_idx, 7).value
            col_h = ws.cell(row_idx, 8).value

            if not col_b:
                continue

            col_a_str = str(col_a).strip() if col_a else ""
            col_b_str = str(col_b).strip() if col_b else ""

            if col_a_str and col_b_str and len(col_a_str) > 3:
                cat_info = MOH_CORRECTIVE_CATEGORIES.get(col_a_str)
                if cat_info:
                    en_name, ar_name = cat_info
                    if en_name not in categories:
                        category = StandardCategory.objects.create(
                            name=f"MOH - {en_name}",
                            name_ar=ar_name,
                            source=source,
                            order=list(MOH_CORRECTIVE_CATEGORIES.keys()).index(col_a_str.strip().rstrip()) + 1,
                        )
                        categories[en_name] = category
                    current_category = categories[en_name]
                    order = 0

            if not current_category:
                continue

            order += 1
            code = f"MOH-CA-{current_category.order:02d}-{order:03d}"

            standard = Standard.objects.create(
                code=code,
                title=col_b_str,
                title_ar=col_b_str,
                source=source,
                category=current_category,
                order_within_category=order,
            )

            status = self._map_status(col_d)
            has_corrective = bool(col_f and str(col_f).strip())
            priority = self._map_priority(col_g)

            StandardCompliance.objects.create(
                hospital=hospital,
                standard=standard,
                status=status,
                status_ar=str(col_d).strip() if col_d else "",
                target_status=StandardCompliance.ComplianceStatus.MET if has_corrective else "",
                corrective_action=str(col_f).strip() if col_f else "",
                priority=priority,
                target_date=str(col_h).strip() if col_h else "",
            )

    def _import_moh_framework(self, source, hospital):
        wb = load_workbook(DATA_DIR / "Standards - MOH - الاطار الاسترشادي.xlsx", data_only=True)
        ws = wb["H"]

        categories = {}
        current_category = None
        current_section = None
        order = 0

        for row_idx in range(6, ws.max_row + 1):
            col_a = ws.cell(row_idx, 1).value
            col_b = ws.cell(row_idx, 2).value
            col_c = ws.cell(row_idx, 3).value
            col_d = ws.cell(row_idx, 4).value
            col_e = ws.cell(row_idx, 5).value
            col_f = ws.cell(row_idx, 6).value
            col_g = ws.cell(row_idx, 7).value
            col_h = ws.cell(row_idx, 8).value
            col_n = ws.cell(row_idx, 14).value

            if not col_a and not col_b:
                continue

            col_a_str = str(col_a).strip() if col_a else ""
            col_b_str = str(col_b).strip() if col_b else ""

            if col_a_str in MOH_FRAMEWORK_CATEGORIES and "نقطة" in col_b_str:
                info = MOH_FRAMEWORK_CATEGORIES[col_a_str]
                en_name, ar_name, max_score = info
                category = StandardCategory.objects.create(
                    name=f"MOH Framework - {en_name}",
                    name_ar=ar_name,
                    source=source,
                    order=int(col_a_str),
                    max_score=max_score,
                )
                categories[col_a_str] = category
                current_category = category
                current_section = col_a_str
                order = 0
                continue

            if not current_category:
                continue

            is_sub_section = re.match(r"^\d+\.\d+$", col_a_str)
            if is_sub_section and col_b_str and not col_c:
                order += 1
                current_section_detail = col_a_str
                Standard.objects.create(
                    code=f"MOH-FW-{current_category.order}-{col_a_str}",
                    title=col_b_str,
                    title_ar=col_b_str,
                    source=source,
                    category=current_category,
                    is_heading=True,
                    order_within_category=order,
                )
                continue

            if col_a_str and col_b_str:
                try:
                    int(col_a_str)
                except ValueError:
                    continue

                order += 1
                code = f"MOH-FW-{current_category.order}-{order:03d}"

                assessment_code = ""
                if col_c:
                    ac = str(col_c).strip()
                    valid_codes = [c[0] for c in StandardCompliance.AssessmentCode.choices]
                    if ac in valid_codes:
                        assessment_code = ac

                assessment_code_target = ""
                if col_d:
                    act = str(col_d).strip()
                    valid_codes = [c[0] for c in StandardCompliance.AssessmentCode.choices]
                    if act in valid_codes:
                        assessment_code_target = act

                score = None
                max_score = None
                if col_e is not None:
                    try:
                        score = Decimal(str(col_e).strip())
                    except Exception:
                        pass
                if col_f is not None:
                    try:
                        ms = str(col_f).strip()
                        if ms:
                            max_score = Decimal(ms)
                    except Exception:
                        pass

                status = Standard.ComplianceStatus.NOT_ASSESSED
                status_ar = ""
                if col_n:
                    status_ar = str(col_n).strip()
                    status = STATUS_MAP.get(status_ar, Standard.ComplianceStatus.NOT_ASSESSED)
                elif assessment_code:
                    if assessment_code.startswith("TM"):
                        status = Standard.ComplianceStatus.MET
                    elif assessment_code.startswith("PM"):
                        status = Standard.ComplianceStatus.PARTIALLY_MET
                    elif assessment_code.startswith("NM"):
                        status = Standard.ComplianceStatus.NOT_MET

                standard = Standard.objects.create(
                    code=code,
                    title=col_b_str,
                    title_ar=col_b_str,
                    source=source,
                    category=current_category,
                    order_within_category=order,
                )

                StandardCompliance.objects.create(
                    hospital=hospital,
                    standard=standard,
                    status=status,
                    status_ar=status_ar,
                    assessment_code=assessment_code,
                    assessment_code_target=assessment_code_target,
                    score=score,
                    max_score=max_score,
                    supporting_documents=str(col_g).strip() if col_g else "",
                    action_note=str(col_h).strip() if col_h else "",
                )
