import json
import re
import sys
from pathlib import Path

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter


MONTH_START_COL = 3


def safe_sheet_title(value, fallback):
    text = re.sub(r"[\[\]\:\*\?\/\\]", " ", str(value or "")).strip()
    text = re.sub(r"\s+", " ", text)
    return (text or fallback)[:31]


def int_value(value):
    try:
        return int(value or 0)
    except (TypeError, ValueError):
        return 0


def sum_formula(col_letter, rows):
    if not rows:
        return 0
    return "=SUM(" + ",".join(f"{col_letter}{row}" for row in rows) + ")"


def style_cell(cell, fill=None, font=None, border=None, alignment=None):
    if fill is not None:
        cell.fill = fill
    if font is not None:
        cell.font = font
    if border is not None:
        cell.border = border
    if alignment is not None:
        cell.alignment = alignment


def apply_range_style(ws, min_row, max_row, min_col, max_col, border, alignment):
    for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
        for cell in row:
            cell.border = border
            if alignment is not None:
                cell.alignment = alignment


def status_style(key):
    fills = {
        "asistio": PatternFill("solid", fgColor="D9EAF7"),
        "tardanza": PatternFill("solid", fgColor="FFFFFF"),
        "justificada_total": PatternFill("solid", fgColor="E2F0D9"),
        "falta_total": PatternFill("solid", fgColor="FCE4D6"),
    }
    fonts = {
        "asistio": Font(name="Arial", size=10, bold=False, color="000000"),
        "tardanza": Font(name="Arial", size=10, bold=False, color="C55A11"),
        "justificada_total": Font(name="Arial", size=10, bold=False, color="38761D"),
        "falta_total": Font(name="Arial", size=10, bold=False, color="C00000"),
    }
    return fills.get(key, fills["asistio"]), fonts.get(key, fonts["asistio"])


def write_level_sheet(wb, payload, level):
    months = payload.get("months") or []
    statuses = payload.get("statuses") or []
    title = safe_sheet_title(level.get("label"), "ASISTENCIA")
    ws = wb.create_sheet(title=title)

    thin = Side(style="thin", color="000000")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)
    center = Alignment(horizontal="center", vertical="center", wrap_text=True)
    left = Alignment(horizontal="left", vertical="center", wrap_text=True)
    month_header_fill = PatternFill("solid", fgColor="FFFFFF")
    summary_header_fill = PatternFill("solid", fgColor="B6E7A7")
    grade_fill = PatternFill("solid", fgColor="E2F0D9")
    title_font = Font(name="Abadi", size=14, bold=True, color="008000")
    header_font = Font(name="Avenir", size=12, bold=True, color="000000")
    grade_font = Font(name="Aptos Narrow", size=11, bold=False, color="000000")

    last_month_col = MONTH_START_COL + len(months) - 1
    total_col = last_month_col + 1
    last_col = total_col

    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=last_col)
    ws.cell(1, 1, str(level.get("label") or "")).font = title_font
    ws.cell(1, 1).alignment = center
    ws.row_dimensions[1].height = 24

    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=2)
    for index, month in enumerate(months, MONTH_START_COL):
        ws.cell(2, index, str(month.get("label") or ""))
    ws.cell(2, total_col, "ACUMULADO")
    ws.row_dimensions[2].height = 89.4
    for col in range(1, last_col + 1):
        month_alignment = Alignment(horizontal="center", vertical="center", textRotation=90, wrap_text=True)
        style_cell(ws.cell(2, col), fill=month_header_fill, font=header_font, border=border, alignment=month_alignment)

    source_rows = {str(status.get("key") or ""): [] for status in statuses}
    row = 3
    for grade in level.get("grades") or []:
        grade_start = row
        grade_end = row + max(0, len(statuses) - 1)
        if grade_end > grade_start:
            ws.merge_cells(start_row=grade_start, start_column=1, end_row=grade_end, end_column=1)
        ws.cell(grade_start, 1, str(grade.get("short") or grade.get("label") or ""))
        style_cell(ws.cell(grade_start, 1), fill=grade_fill, font=grade_font, border=border, alignment=center)

        for status in statuses:
            key = str(status.get("key") or "")
            label = str(status.get("label") or key)
            fill, font = status_style(key)
            ws.cell(row, 2, label)
            for col, month in enumerate(months, MONTH_START_COL):
                month_key = str(int_value(month.get("number")))
                bucket = (grade.get("months") or {}).get(month_key) or {}
                ws.cell(row, col, int_value(bucket.get(key)))
            if months:
                ws.cell(row, total_col, f"=SUM({get_column_letter(MONTH_START_COL)}{row}:{get_column_letter(last_month_col)}{row})")
            else:
                ws.cell(row, total_col, 0)
            for col in range(2, last_col + 1):
                alignment = left if col == 2 else center
                style_cell(ws.cell(row, col), fill=fill, font=font, border=border, alignment=alignment)
            ws.row_dimensions[row].height = 14.25
            source_rows.setdefault(key, []).append(row)
            row += 1

    row += 2
    summary_header_row = row
    ws.merge_cells(start_row=summary_header_row, start_column=1, end_row=summary_header_row, end_column=2)
    for col, month in enumerate(months, MONTH_START_COL):
        ws.cell(summary_header_row, col, str(month.get("label") or ""))
    ws.cell(summary_header_row, total_col, "ACUMULADO")
    ws.row_dimensions[summary_header_row].height = 80.4
    for col in range(1, last_col + 1):
        alignment = Alignment(horizontal="center", vertical="center", textRotation=90, wrap_text=True)
        style_cell(ws.cell(summary_header_row, col), fill=summary_header_fill, font=header_font, border=border, alignment=alignment)

    row = summary_header_row + 1
    for status in statuses:
        key = str(status.get("key") or "")
        label = str(status.get("label") or key)
        fill, font = status_style(key)
        ws.cell(row, 2, label)
        for col in range(MONTH_START_COL, last_month_col + 1):
            ws.cell(row, col, sum_formula(get_column_letter(col), source_rows.get(key, [])))
        if months:
            ws.cell(row, total_col, f"=SUM({get_column_letter(MONTH_START_COL)}{row}:{get_column_letter(last_month_col)}{row})")
        else:
            ws.cell(row, total_col, 0)
        for col in range(1, last_col + 1):
            style_cell(ws.cell(row, col), fill=fill, font=font, border=border, alignment=center)
        ws.cell(row, 2).alignment = left
        ws.row_dimensions[row].height = 14.25
        row += 1

    apply_range_style(ws, 1, max(2, row - 1), 1, last_col, border, None)

    ws.column_dimensions["A"].width = 13
    ws.column_dimensions["B"].width = 18
    if months:
        ws.column_dimensions[get_column_letter(MONTH_START_COL)].width = 7.25
    for col in range(MONTH_START_COL + 1, total_col + 1):
        ws.column_dimensions[get_column_letter(col)].width = 12
    ws.freeze_panes = "C3"
    ws.sheet_view.showGridLines = False
    ws.page_setup.orientation = "landscape"
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.sheet_properties.pageSetUpPr.fitToPage = True

    if months and statuses:
        chart = BarChart()
        chart.type = "col"
        chart.style = 10
        chart.title = ""
        chart.y_axis.title = ""
        chart.x_axis.title = ""
        chart.legend.position = "b"
        data = Reference(
            ws,
            min_col=2,
            max_col=last_month_col,
            min_row=summary_header_row + 1,
            max_row=summary_header_row + len(statuses),
        )
        categories = Reference(ws, min_col=MONTH_START_COL, max_col=last_month_col, min_row=summary_header_row)
        chart.add_data(data, titles_from_data=True, from_rows=True)
        chart.set_categories(categories)
        for series, color in zip(chart.series, ["4472C4", "ED7D31", "70AD47", "C00000"]):
            series.graphicalProperties.solidFill = color
            series.graphicalProperties.line.solidFill = color
        chart.height = 9.8
        chart.width = 22.5
        ws.add_chart(chart, f"B{row + 1}")


def build_workbook(payload):
    wb = Workbook()
    wb.remove(wb.active)
    levels = payload.get("levels") or []
    if not levels:
        ws = wb.create_sheet("SIN DATOS")
        ws["A1"] = "No se encontraron registros para los filtros seleccionados."
        return wb

    for level in levels:
        write_level_sheet(wb, payload, level)
    return wb


def main():
    if len(sys.argv) < 3:
        print(json.dumps({"ok": False, "error": "Uso: resumen_asistencia_exporter.py entrada.json salida.xlsx"}))
        return 1

    input_path = Path(sys.argv[1])
    output_path = Path(sys.argv[2])
    try:
        payload = json.loads(input_path.read_text(encoding="utf-8-sig"))
        wb = build_workbook(payload)
        output_path.parent.mkdir(parents=True, exist_ok=True)
        wb.save(output_path)
        print(json.dumps({"ok": True, "file": str(output_path)}))
        return 0
    except Exception as exc:
        print(json.dumps({"ok": False, "error": str(exc)}))
        return 1


if __name__ == "__main__":
    raise SystemExit(main())