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

from docx import Document
from openpyxl import load_workbook

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


ITEM_RE = re.compile(r"\b([A-Z]\d{2})\b", re.IGNORECASE)


def clean(value):
    if value is None:
        return ""
    if isinstance(value, float) and value.is_integer():
        return str(int(value))
    return re.sub(r"[ \t]+", " ", str(value).replace("\r", "\n")).strip()


def norm(value):
    text = unicodedata.normalize("NFD", clean(value).upper())
    text = "".join(char for char in text if unicodedata.category(char) != "Mn")
    text = re.sub(r"[^A-Z0-9]+", " ", text)
    return re.sub(r"\s+", " ", text).strip()


def compact_lines(value):
    lines = [clean(line) for line in clean(value).split("\n")]
    return [line for line in lines if line]


def answer_from_text(value):
    text = norm(value)
    if not text:
        return ""
    if re.search(r"(?:X|1|SI|CUMPLE)\s+SI\b|\bSI\s+(?:X|1|MARCADO|CUMPLE)\b|\[X\]\s*SI|\(X\)\s*SI", text):
        return "SI"
    if re.search(r"(?:X|1|NO|NO CUMPLE)\s+NO\b|\bNO\s+(?:X|1|MARCADO)\b|\[X\]\s*NO|\(X\)\s*NO", text):
        return "NO"
    if text in {"SI", "S", "SI CUMPLE", "CUMPLE"}:
        return "SI"
    if text in {"NO", "N", "NO CUMPLE", "NO APLICA"}:
        return "NO"
    return ""


def strip_placeholders(value):
    blocked = {
        "SUSTENTO REVISADO",
        "COMENTARIO TECNICO",
        "COMENTARIO TECNICO REVISADO",
        "INDICACIONES",
        "SUSTENTO ESPERADO",
        "ASPECTO VERIFICADO",
        "MARCAR",
        "OPCIONES DE RESPUESTA",
    }
    lines = []
    for line in compact_lines(value):
        normalized = norm(line)
        if normalized in blocked:
            continue
        for prefix in ("SUSTENTO REVISADO", "COMENTARIO TECNICO"):
            if normalized.startswith(prefix) and ":" in line:
                line = clean(line.split(":", 1)[1])
                break
        if not line:
            continue
        lines.append(line)
    return "\n".join(lines).strip()


def is_metadata_placeholder(value):
    return norm(value) in {
        "ACA AGREGAR ASPECTOS A MEJORAR",
        "ACA AGREGAR LOS ACUERDOS Y COMPROMISOS DEL DE LA DOCENTE",
        "AGREGAR ASPECTOS A MEJORAR",
        "AGREGAR ACUERDOS Y COMPROMISOS",
        "DD MM AAAA",
    }


def parse_row(cells):
    values = [clean(cell) for cell in cells]
    joined = "\n".join(values)
    match = ITEM_RE.search(joined)
    if not match:
        return None

    code = match.group(1).upper()
    code_index = 0
    for index, value in enumerate(values):
        if ITEM_RE.search(value):
            code_index = index
            break

    answer = ""
    answer_index = None
    for index in range(code_index + 1, len(values)):
        detected = answer_from_text(values[index])
        if detected:
            answer = detected
            answer_index = index
            break

    if not answer:
        tail = " ".join(values[code_index + 1 :])
        detected = answer_from_text(tail)
        if detected:
            answer = detected

    start = (answer_index + 1) if answer_index is not None else code_index + 2
    indication_candidates = [strip_placeholders(value) for value in values[start:]]
    indication_candidates = [
        value
        for value in indication_candidates
        if value and not ITEM_RE.fullmatch(value.strip()) and not answer_from_text(value)
    ]

    evidence = ""
    comment = ""
    if indication_candidates:
        first_lines = compact_lines(indication_candidates[0])
        if len(first_lines) >= 2:
            evidence = first_lines[0]
            comment = "\n".join(first_lines[1:])
        else:
            evidence = indication_candidates[0]
            if len(indication_candidates) > 1:
                comment = indication_candidates[1]

    if not answer and not evidence and not comment:
        return None

    return {
        "codigo": code,
        "respuesta": answer,
        "sustento_evidencia": evidence,
        "comentario": comment,
    }


def collect_metadata_from_row(cells, metadata):
    values = [clean(cell) for cell in cells]
    labels = {
        "FECHA": "fecha",
        "DNI": "dni",
        "NIVEL": "nivel",
        "GRADO": "grado",
        "SECCION": "seccion",
        "CELULAR": "celular",
        "DOCENTE MONITOREADO": "docente",
        "MONITOR A": "monitor",
        "MONITOR": "monitor",
        "ASPECTOS A MEJORAR": "aspectos_mejora",
        "DEBILIDADES ASPECTOS A MEJORAR": "aspectos_mejora",
        "ACUERDOS Y COMPROMISOS DEL DE LA DOCENTE": "compromisos_docente",
        "ACUERDOS Y COMPROMISOS DEL DOCENTE": "compromisos_docente",
        "COMPROMISOS DEL DOCENTE": "compromisos_docente",
        "FECHA TENTATIVA DEL PROXIMO MONITOREO": "fecha_seguimiento",
        "FECHA DE SEGUIMIENTO": "fecha_seguimiento",
        "PROXIMO MONITOREO": "fecha_seguimiento",
    }
    for index, value in enumerate(values):
        key = labels.get(norm(value))
        if key and index + 1 < len(values) and values[index + 1]:
            incoming = values[index + 1]
            if not is_metadata_placeholder(incoming):
                metadata.setdefault(key, incoming)


def parse_xlsx(path):
    workbook = load_workbook(path, data_only=True, read_only=True)
    answers = {}
    metadata = {}
    for sheet in workbook.worksheets:
        for row in sheet.iter_rows(values_only=True):
            cells = [clean(cell) for cell in row]
            if not any(cells):
                continue
            collect_metadata_from_row(cells, metadata)
            parsed = parse_row(cells)
            if parsed:
                answers[parsed["codigo"]] = parsed
    return answers, metadata


def parse_docx(path):
    document = Document(path)
    answers = {}
    metadata = {}

    for table in document.tables:
        for row in table.rows:
            cells = [cell.text for cell in row.cells]
            if not any(clean(cell) for cell in cells):
                continue
            collect_metadata_from_row(cells, metadata)
            parsed = parse_row(cells)
            if parsed:
                answers[parsed["codigo"]] = parsed

    paragraphs = [paragraph.text for paragraph in document.paragraphs if clean(paragraph.text)]
    for paragraph in paragraphs:
        parsed = parse_row([paragraph])
        if parsed and parsed["codigo"] not in answers:
            answers[parsed["codigo"]] = parsed

    return answers, metadata


def parse(path):
    extension = Path(path).suffix.lower()
    if extension in {".xlsx", ".xlsm", ".xltx"}:
        return parse_xlsx(path)
    if extension == ".docx":
        return parse_docx(path)
    if extension == ".xls":
        raise RuntimeError("Los archivos .xls antiguos deben convertirse a .xlsx antes de importar.")
    if extension == ".doc":
        raise RuntimeError("Los archivos .doc antiguos deben convertirse a .docx antes de importar.")
    raise RuntimeError("Formato no admitido. Use .docx, .xlsx o .xlsm.")


def main():
    if len(sys.argv) < 2:
        raise RuntimeError("No se recibio archivo para importar.")
    path = Path(sys.argv[1])
    if not path.is_file():
        raise RuntimeError("El archivo no existe o no se puede leer.")
    answers, metadata = parse(path)
    print(
        json.dumps(
            {
                "ok": True,
                "answers": answers,
                "metadata": metadata,
                "count": len(answers),
                "source": path.name,
            },
            ensure_ascii=False,
        )
    )


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