from io import BytesIO
from pathlib import Path

import pandas as pd

from config import OUTPUT_DIR


SUMMARY_COLUMNS = [
    "grupo", "proyecto", "total_tareas", "completadas",
    "en_progreso", "por_hacer", "vencidas", "porcentaje_avance",
]

TASK_COLUMNS = [
    "grupo", "proyecto", "tarea", "bucket", "estado", "porcentaje",
    "prioridad", "fecha_inicio", "fecha_vencimiento", "asignados", "vencida",
]


def export_to_excel(projects: list[dict], output_path: Path | str | None = None) -> bytes:
    """
    Genera un Excel con 3 hojas: Resumen, Tareas y Vencidas.
    Devuelve los bytes del archivo para descarga en Streamlit.
    """
    summary_rows = [{k: p[k] for k in SUMMARY_COLUMNS} for p in projects]

    all_tasks = []
    for project in projects:
        all_tasks.extend(project["tareas"])

    tasks_df = pd.DataFrame(all_tasks)
    if not tasks_df.empty:
        tasks_df = tasks_df[TASK_COLUMNS]

    overdue_df = tasks_df[tasks_df["vencida"] == True].copy() if not tasks_df.empty else pd.DataFrame()

    buffer = BytesIO()
    with pd.ExcelWriter(buffer, engine="openpyxl") as writer:
        pd.DataFrame(summary_rows).to_excel(writer, sheet_name="Resumen", index=False)
        tasks_df.to_excel(writer, sheet_name="Tareas", index=False)
        overdue_df.to_excel(writer, sheet_name="Vencidas", index=False)

    excel_bytes = buffer.getvalue()

    if output_path:
        path = Path(output_path)
        path.parent.mkdir(parents=True, exist_ok=True)
        path.write_bytes(excel_bytes)

    return excel_bytes


def default_output_path() -> Path:
    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
    timestamp = pd.Timestamp.now().strftime("%Y%m%d_%H%M%S")
    return OUTPUT_DIR / f"reporte_planner_{timestamp}.xlsx"