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 ""
    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 sheet_matrix(ws):
    return [[clean(cell.value) for cell in row] for row in ws.iter_rows()]


def cell(matrix, row, col):
    try:
        return matrix[row][col]
    except Exception:
        return ""


def read_metadata(wb):
    metadata = {}

    if "Parametros" in wb.sheetnames:
        rows = sheet_matrix(wb["Parametros"])
        for row in rows:
            label = norm(row[0] if row else "")
            if label.startswith("NIVEL"):
                metadata["level_code"] = clean(row[1]) if len(row) > 1 else ""
                metadata["level"] = clean(row[2]) if len(row) > 2 else ""
            elif label.startswith("ANO ACADEMICO"):
                metadata["year"] = int(float(row[1])) if len(row) > 1 and clean(row[1]) else None
            elif label.startswith("PERIODO") and "PROMOCIONAL" not in label:
                metadata["period_code"] = clean(row[1]) if len(row) > 1 else ""
                metadata["period"] = clean(row[2]) if len(row) > 2 else ""
            elif label.startswith("GRADO"):
                metadata["grade_code"] = clean(row[1]) if len(row) > 1 else ""
                metadata["grade"] = clean(row[2]) if len(row) > 2 else ""
            elif label.startswith("SECCION"):
                metadata["section_code"] = clean(row[1]) if len(row) > 1 else ""
                metadata["section"] = clean(row[2]) if len(row) > 2 else ""

    if "Generalidades" in wb.sheetnames:
        rows = sheet_matrix(wb["Generalidades"])
        for row in rows:
            joined = " ".join(row)
            if "Nivel :" in joined and not metadata.get("level"):
                for index, value in enumerate(row):
                    if norm(value) == "NIVEL" and index + 1 < len(row):
                        metadata["level"] = clean(row[index + 1])
            if "Año académico" in joined and not metadata.get("year"):
                for value in row:
                    if re.fullmatch(r"20\d{2}", clean(value)):
                        metadata["year"] = int(clean(value))
            if "Período de evaluación" in joined:
                for value in row:
                    if "BIMESTRE" in norm(value):
                        metadata["period"] = clean(value)
            if "Grado :" in joined and not metadata.get("grade"):
                for index, value in enumerate(row):
                    if norm(value) == "GRADO" and index + 1 < len(row):
                        metadata["grade"] = clean(row[index + 1])
                    if norm(value) == "SECCION" and index + 1 < len(row):
                        metadata["section"] = clean(row[index + 1])

    return metadata


def read_area_names(wb):
    areas = {}
    if "Generalidades" not in wb.sheetnames:
        return areas

    rows = sheet_matrix(wb["Generalidades"])
    for row in rows:
        left = clean(row[1]) if len(row) > 1 else ""
        right = clean(row[2]) if len(row) > 2 else ""
        match = re.match(r"^([0-9]+)\s*:", left)
        if match and right:
            areas[match.group(1)] = right

    return areas


def parse_area_sheet(ws, area_names):
    rows = sheet_matrix(ws)
    if len(rows) < 3:
        return [], []

    sheet_name = ws.title
    area_code = sheet_name.split("-", 1)[0].strip()
    area_name = area_names.get(area_code, sheet_name.split("-", 1)[-1].strip())

    header = rows[0]
    subheader = rows[1]
    pairs = []
    col = 3
    while col < len(header):
        code = clean(header[col])
        kind = norm(cell(rows, 1, col))
        if code and kind == "NL":
            pairs.append((col, code))
            col += 2
        else:
            col += 1

    legends = {}
    for row in rows:
        text = " ".join(clean(v) for v in row if clean(v))
        match = re.match(r"^([0-9]{2})\s*=\s*(.+)$", text)
        if match:
            legends[match.group(1)] = match.group(2).strip()

    students = []
    grades = []
    for index, row in enumerate(rows[2:], start=3):
        first = clean(row[0]) if len(row) > 0 else ""
        student_code = clean(row[1]) if len(row) > 1 else ""
        student = clean(row[2]) if len(row) > 2 else ""
        if not first and not student_code and not student:
            continue
        if norm(first) == "LEYENDA" or norm(student_code) == "LEYENDA" or norm(student).startswith("LEYENDA"):
            break
        if not student:
            continue

        students.append({"dni": first, "student_code": student_code, "student": student, "row": index})
        for col, comp_code in pairs:
            literal = clean(row[col]) if col < len(row) else ""
            conclusion = clean(row[col + 1]) if col + 1 < len(row) else ""
            if not literal and not conclusion:
                continue
            grades.append(
                {
                    "sheet": sheet_name,
                    "row": index,
                    "dni": first,
                    "student_code": student_code,
                    "student": student,
                    "area_code": area_code,
                    "area": area_name,
                    "competency_code": comp_code,
                    "competency": legends.get(comp_code, comp_code),
                    "grade": literal,
                    "conclusion": conclusion,
                }
            )

    return students, grades


def main(path):
    workbook = load_workbook(path, read_only=True, data_only=True)
    metadata = read_metadata(workbook)
    area_names = read_area_names(workbook)
    all_students = {}
    all_grades = []

    for name in workbook.sheetnames:
        if name in {"Generalidades", "Parametros"}:
            continue
        students, grades = parse_area_sheet(workbook[name], area_names)
        for student in students:
            key = student.get("student_code") or student.get("dni") or student.get("student")
            all_students[key] = student
        all_grades.extend(grades)

    return {
        "ok": True,
        "file": str(Path(path).name),
        "metadata": metadata,
        "students": list(all_students.values()),
        "grades": all_grades,
    }


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)