import json
import re
import sys
import unicodedata
from datetime import time
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

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

DAYS = {
    4: ("LUNES", 1),
    5: ("MARTES", 2),
    6: ("MIERCOLES", 3),
    7: ("JUEVES", 4),
    8: ("VIERNES", 5),
}

GRADE_NAMES = {
    "1": "PRIMERO",
    "2": "SEGUNDO",
    "3": "TERCERO",
    "4": "CUARTO",
    "5": "QUINTO",
    "6": "SEXTO",
}

SECONDARY_GRADE_NAMES = {
    "1": "PRIMERO",
    "2": "SEGUNDO",
    "3": "TERCERO",
    "4": "CUARTO",
    "5": "QUINTO",
}

AREA_ALIASES = {
    "CC SS": "Ciencias Sociales",
    "CCSS": "Ciencias Sociales",
    "CIENCIAS SOCIALES": "Ciencias Sociales",
    "COM": "Comunicación",
    "COMUNICACION": "Comunicación",
    "MAT": "Matemática",
    "MATEMATICA": "Matemática",
    "CYT": "Ciencia y Tecnología",
    "CIENCIA Y TECNOLOGIA": "Ciencia y Tecnología",
    "EF": "Educación Física",
    "E F": "Educación Física",
    "E FISICA": "Educación Física",
    "EDUCACION FISICA": "Educación Física",
    "REL": "Educación Religiosa",
    "RELIGION": "Educación Religiosa",
    "TUTORIA": "Tutoria",
    "ING": "Inglés como Lengua Extranjera",
    "INGLES": "Inglés como Lengua Extranjera",
    "ARTE": "Arte y Cultura",
    "ARTE Y CULTURA": "Arte y Cultura",
    "DPCC": "Desarrollo Personal, Ciudadanía y Cívica",
    "EPT": "Educación para el Trabajo",
}


def clean(value):
    if value is None:
        return ""
    if isinstance(value, time):
        return value.strftime("%H:%M:%S")
    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 = "".join(
        char for char in unicodedata.normalize("NFD", value)
        if unicodedata.category(char) != "Mn"
    )
    value = value.replace("º", "").replace("°", "")
    value = re.sub(r"[^A-Z0-9]+", " ", value)
    return re.sub(r"\s+", " ", value).strip()


def merged_value(ws, row, col):
    value = ws.cell(row, col).value
    if value not in (None, ""):
        return value
    for merged in ws.merged_cells.ranges:
        if merged.min_row <= row <= merged.max_row and merged.min_col <= col <= merged.max_col:
            return ws.cell(merged.min_row, merged.min_col).value
    return value


def sheet_kind(ws):
    if "EE FIS" in norm(ws.title):
        return "PRI"
    first = norm(ws.cell(1, 1).value)
    if "PRIMARIA" in first:
        return "PRI"
    if "SECUNDARIA" in first:
        return "SEC"
    return ""


def teacher_from_sheet(ws):
    for row in range(16, min(ws.max_row, 30) + 1):
        label = norm(ws.cell(row, 1).value)
        if label in {"NOMBRE Y APELLIDOS", "DIRECTOR", "INSTALACIONES DE LA I E", "PROFESOR POR HORAS"}:
            continue
        value = clean(ws.cell(row, 1).value)
        if value and "#REF" not in value:
            return value
    return ""


def turn_from_sheet(ws):
    return "Mañana"


def grade_section_from_title(title):
    text = norm(title)
    if text.startswith("1"):
        return "PRIMERO", "U"
    if text.startswith("2"):
        return "SEGUNDO", "U"
    if text.startswith("3") and "B" in text:
        return "TERCERO", "B"
    if text.startswith("3"):
        return "TERCERO", "A"
    if text.startswith("4"):
        return "CUARTO", "U"
    if text.startswith("5") and "B" in text:
        return "QUINTO", "B"
    if text.startswith("5"):
        return "QUINTO", "A"
    if text.startswith("6"):
        return "SEXTO", "U"
    return "", ""


def parse_primary_physical_grade(value):
    text = norm(value)
    match = re.search(r"\b([1-6])\s*([ABU])?\b", text)
    if not match:
        return "", ""
    return GRADE_NAMES.get(match.group(1), ""), match.group(2) or "U"


def normalize_primary_area(value):
    text = norm(value)
    if not text or "RECREO" in text:
        return ""
    if "PLAN LECTOR" in text or "REF COMUNICACION" in text or "COMUNICACION" in text:
        return "Comunicación"
    if "MAT" in text:
        return "Matemática"
    if "PERSONAL" in text:
        return "Personal Social"
    if "CIENCIA" in text or "CIECNCIA" in text:
        return "Ciencia y Tecnología"
    if "FISICA" in text:
        return "Educación Física"
    if "RELIG" in text:
        return "Educación Religiosa"
    if "ARTE" in text:
        return "Arte y Cultura"
    if "TUTORIA" in text:
        return "Tutoria"
    return clean(value)


def normalize_secondary_area(value, default_area=""):
    text = norm(value)
    if not text or "RECREO" in text:
        return ""
    if "AT PP FF" in text or "ATENCION" in text:
        return "Atención a padres de familia"
    text = re.sub(r"^\d\s*[AB]\s*(Y\s*[AB])?\s*", "", text).strip()
    text = re.sub(r"^\d\s*[AB]\s*Y\s*[AB]\s*", "", text).strip()
    default_norm = norm(default_area)
    if (not text or "REFUERZO" in text) and default_norm:
        for key, label in AREA_ALIASES.items():
            if key in default_norm:
                return label
    for key, label in AREA_ALIASES.items():
        if key in text:
            return label
    for key, label in AREA_ALIASES.items():
        if key in default_norm:
            return label
    return clean(value)


def parse_secondary_classrooms(value):
    text = norm(value)
    if not text or "RECREO" in text or "AT PP FF" in text:
        return [("", "")]
    dual = re.search(r"\b([1-5])\s*A\s*Y\s*B\b", text)
    if dual:
        grade = SECONDARY_GRADE_NAMES.get(dual.group(1), "")
        return [(grade, "A"), (grade, "B")]
    rows = []
    for match in re.finditer(r"\b([1-5])\s*([AB])\b", text):
        rows.append((SECONDARY_GRADE_NAMES.get(match.group(1), ""), match.group(2)))
    return rows or [("", "")]


def parse_schedule_matrix(ws, level, file_name):
    rows = []
    teacher = teacher_from_sheet(ws)
    if not teacher:
        return rows
    turn = turn_from_sheet(ws)
    default_area = clean(ws.cell(5, 2).value) or ws.title
    is_primary_physical = level == "PRI" and "EE FIS" in norm(ws.title)
    base_grade, base_section = grade_section_from_title(ws.title)

    for row in range(7, min(ws.max_row, 16) + 1):
        start = clean(merged_value(ws, row, 2))
        end = clean(merged_value(ws, row, 3))
        if not re.match(r"^\d{2}:\d{2}:\d{2}$", start) or not re.match(r"^\d{2}:\d{2}:\d{2}$", end):
            continue
        for col, (day, order) in DAYS.items():
            raw = clean(merged_value(ws, row, col))
            normalized = norm(raw)
            if not raw or "RECREO" in normalized:
                continue

            if level == "PRI":
                area = "Educación Física" if is_primary_physical else normalize_primary_area(raw)
                if is_primary_physical:
                    grade, section = parse_primary_physical_grade(raw)
                else:
                    grade, section = base_grade, base_section
                classrooms = [(grade, section)]
            else:
                area = normalize_secondary_area(raw, default_area)
                classrooms = parse_secondary_classrooms(raw)

            if not area:
                continue
            for grade, section in classrooms:
                rows.append({
                    "nivel": level,
                    "docente_nombre": teacher,
                    "grado": grade,
                    "seccion": section,
                    "aula_texto": (grade + " " + section).strip(),
                    "dia_semana": day,
                    "dia_orden": order,
                    "bloque_orden": row - 6,
                    "hora_inicio": start,
                    "hora_fin": end,
                    "actividad": raw,
                    "area_nombre": area,
                    "turno": turn,
                    "hoja_excel": ws.title,
                    "fila_excel": row,
                    "columna_excel": get_column_letter(col),
                    "fuente_archivo": file_name,
                })
    return rows


def parse_workbook(path):
    wb = load_workbook(path, data_only=True)
    file_name = Path(path).name
    rows = []
    for ws in wb.worksheets:
        if ws.title.upper() == "DATOS" or ws.title.upper() == "PRIMARIA":
            continue
        level = sheet_kind(ws)
        if not level:
            continue
        # The secondary workbook includes stale classroom tabs with broken formulas.
        if level == "SEC" and not teacher_from_sheet(ws):
            continue
        rows.extend(parse_schedule_matrix(ws, level, file_name))
    levels = sorted(set(row["nivel"] for row in rows))
    return {
        "ok": True,
        "file": file_name,
        "levels": levels,
        "rows": rows,
        "total": len(rows),
    }


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