"""Parses per-commune polling-station Excel workbooks into import rows.

Two known shapes are supported:

1. "Tabular" (e.g. جماعة_الشوافع.xlsx): a sheet whose name contains "مكاتب",
   with a header row containing "رقم المكتب", "اسم المؤسسة" and
   "عدد الناخبين", one row per station, ending in a total row. The commune
   name is guessed from a "ملخص" sheet cell like "جماعة: <اسم الجماعة>".

2. "Grouped" (e.g. الحدادة.xlsx): a single sheet with a header row
   containing "رقم المكتب" and "عدد الأشخاص"/"عدد الناخبين", where the "#"
   column alternates between section markers ("جماعة: <اسم>",
   "مؤسسة: <اسم>") and blank cells. Rows with a station number belong to
   whichever "مؤسسة:" marker most recently appeared above them.
"""

import re

import openpyxl

STATION_NUMBER_HEADERS = ["رقم المكتب", "رقم مكتب"]
INSTITUTION_HEADERS = ["اسم المؤسسة", "المؤسسة"]
REGISTERED_HEADERS = ["عدد الناخبين", "عدد الأشخاص"]
MARKER_HEADERS = ["#"]


def _find_header_row(sheet, required_headers):
    for row in sheet.iter_rows(min_row=1, max_row=min(10, sheet.max_row)):
        values = [str(c.value).strip() if c.value is not None else "" for c in row]
        if any(h in values for h in required_headers):
            return row[0].row, values
    return None, None


def _find_col(headers, candidates):
    for i, h in enumerate(headers):
        if h in candidates:
            return i
    return None


def _guess_commune_from_summary_sheet(workbook):
    for sheet in workbook.worksheets:
        if "ملخص" in sheet.title:
            for row in sheet.iter_rows(min_row=1, max_row=min(5, sheet.max_row)):
                for cell in row:
                    if cell.value and isinstance(cell.value, str) and "جماعة" in cell.value:
                        m = re.search(r"جماعة\s*[:：]\s*(.+)", cell.value)
                        if m:
                            return m.group(1).strip()
    return ""


def _parse_tabular_format(workbook):
    """Format 1: dedicated "مكاتب" sheet, one column per field."""
    stations_sheet = None
    for sheet in workbook.worksheets:
        if "مكاتب" in sheet.title:
            stations_sheet = sheet
            break

    if stations_sheet is None:
        return None

    header_row_idx, headers = _find_header_row(stations_sheet, STATION_NUMBER_HEADERS)
    if header_row_idx is None:
        return None

    number_col = _find_col(headers, STATION_NUMBER_HEADERS)
    institution_col = _find_col(headers, INSTITUTION_HEADERS)
    registered_col = _find_col(headers, REGISTERED_HEADERS)

    rows = []
    for row in stations_sheet.iter_rows(min_row=header_row_idx + 1, max_row=stations_sheet.max_row):
        number_val = row[number_col].value if number_col is not None else None
        if number_val is None or str(number_val).strip() == "":
            continue
        try:
            station_number = int(float(number_val))
        except (TypeError, ValueError):
            continue

        institution = ""
        if institution_col is not None and row[institution_col].value is not None:
            institution = str(row[institution_col].value).strip()

        registered = 0
        if registered_col is not None and row[registered_col].value is not None:
            try:
                registered = int(float(row[registered_col].value))
            except (TypeError, ValueError):
                registered = 0

        rows.append(
            {"name": str(station_number), "institution": institution, "official_registered": registered}
        )

    commune_guess = _guess_commune_from_summary_sheet(workbook)
    if not commune_guess:
        m = re.search(r"لجماعة\s+(.+)$", stations_sheet.title.strip())
        if m:
            commune_guess = m.group(1).strip()

    return commune_guess, rows


def _parse_grouped_format(workbook):
    """Format 2: single sheet with "جماعة:"/"مؤسسة:" section markers."""
    for sheet in workbook.worksheets:
        header_row_idx, headers = _find_header_row(sheet, STATION_NUMBER_HEADERS)
        if header_row_idx is None:
            continue

        number_col = _find_col(headers, STATION_NUMBER_HEADERS)
        registered_col = _find_col(headers, REGISTERED_HEADERS)
        marker_col = _find_col(headers, MARKER_HEADERS)
        if number_col is None or marker_col is None:
            continue

        commune_guess = ""
        current_institution = ""
        rows = []

        for row in sheet.iter_rows(min_row=header_row_idx + 1, max_row=sheet.max_row):
            marker_val = row[marker_col].value
            number_val = row[number_col].value

            has_number = number_val is not None and str(number_val).strip() != ""

            if not has_number:
                if marker_val and isinstance(marker_val, str):
                    m = re.search(r"جماعة\s*[:：]\s*(.+)", marker_val)
                    if m:
                        commune_guess = m.group(1).strip()
                        continue
                    m = re.search(r"مؤسسة\s*[:：]\s*(.+)", marker_val)
                    if m:
                        current_institution = m.group(1).strip()
                        continue
                continue

            try:
                station_number = int(float(number_val))
            except (TypeError, ValueError):
                continue

            registered = 0
            if registered_col is not None and row[registered_col].value is not None:
                try:
                    registered = int(float(row[registered_col].value))
                except (TypeError, ValueError):
                    registered = 0

            rows.append(
                {
                    "name": str(station_number),
                    "institution": current_institution,
                    "official_registered": registered,
                }
            )

        if rows:
            return commune_guess, rows

    return None


def parse_stations_workbook(file):
    """Returns (commune_guess, rows, error_message).

    rows is a list of dicts: {"name": str, "institution": str, "official_registered": int}.
    On failure, rows is [] and error_message explains why.
    """
    try:
        wb = openpyxl.load_workbook(file, data_only=True)
    except Exception as exc:
        return "", [], f"تعذرت قراءة الملف: {exc}"

    result = _parse_tabular_format(wb)
    if result is None:
        result = _parse_grouped_format(wb)

    if result is None:
        return "", [], "لم يتم التعرف على صيغة الملف. تأكد من وجود عمود \"رقم المكتب\" في إحدى الأوراق."

    commune_guess, rows = result
    if not rows:
        return commune_guess, [], "لم يتم العثور على أي صف مكتب تصويت صالح في الملف."

    return commune_guess, rows, None