import json
import re
import sys
from pathlib import Path

from openpyxl import load_workbook

if hasattr(sys.stdout, "reconfigure"):
    sys.stdout.reconfigure(encoding="utf-8")


def clean(value):
    if value is None:
        return ""
    if isinstance(value, float) and value.is_integer():
        return str(int(value))
    return str(value).replace("\n", " ").strip()


def norm(value):
    value = clean(value).upper()
    value = (
        value.replace("Á", "A")
        .replace("É", "E")
        .replace("Í", "I")
        .replace("Ó", "O")
        .replace("Ú", "U")
        .replace("Ü", "U")
    )
    value = re.sub(r"[^A-Z0-9]+", " ", value)
    return re.sub(r"\s+", " ", value).strip()


def number(value):
    text = clean(value).replace(",", ".")
    match = re.search(r"-?\d+(?:\.\d+)?", text)
    return float(match.group(0)) if match else None


def find_sheet(workbook):
    for name in workbook.sheetnames:
        if norm(name) in {"ANEXO 01", "ANEXO 1"}:
            return workbook[name]
    return workbook[workbook.sheetnames[0]]


def parse_anexo_01(ws):
    rows = []
    for row in ws.iter_rows(min_row=15, values_only=True):
        values = [clean(v) for v in row]
        joined = norm(" ".join(values))
        if not joined:
            continue
        if joined.startswith("TOTAL") or "RESUMEN" in joined:
            break

        # Columns are taken from the official Anexo 01 structure.
        cargo = values[2] if len(values) > 2 else ""
        docente = values[3] if len(values) > 3 else ""
        condicion = values[5] if len(values) > 5 else ""
        area = values[6] if len(values) > 6 else ""
        codigo_plaza = values[9] if len(values) > 9 else ""
        jornada = values[10] if len(values) > 10 else ""
        horas = number(values[12] if len(values) > 12 else "")

        if not docente and not codigo_plaza and not area:
            continue
        if "CODIGO PLAZA" in joined or "HRS DE DICTADO" in joined:
            continue

        rows.append(
            {
                "cargo": cargo,
                "docente_nombre": docente,
                "condicion_laboral": condicion,
                "area_especialidad": area,
                "codigo_plaza": codigo_plaza,
                "jornada_pedagogica": jornada,
                "horas_dictado": horas,
            }
        )
    return rows


def main(path):
    workbook = load_workbook(path, read_only=True, data_only=True)
    sheet = find_sheet(workbook)
    rows = parse_anexo_01(sheet)
    return {
        "ok": True,
        "file": Path(path).name,
        "sheet": sheet.title,
        "rows": rows,
        "total": len(rows),
    }


if __name__ == "__main__":
    try:
        print(json.dumps(main(sys.argv[1]), ensure_ascii=False))
    except Exception as exc:
        print(json.dumps({"ok": False, "error": str(exc)}, ensure_ascii=False))
        sys.exit(1)