import sys
sys.path.insert(0, "/var/www/html/metafoliadorv2")

from app.schemas import SyncSiteRequest

from fastapi import APIRouter, Depends, HTTPException, Path, Query
from fastapi.responses import Response, FileResponse
from sqlalchemy.orm import Session

from app.models.database import get_db, tenant_db_url_ctx, SessionLocal
from app.models.alfresco_site import AlfrescoSite
from app.models.alfresco_connection import AlfrescoConnection
from app.models.alfresco_folder import AlfrescoFolder
from app.models.sync_progress import SyncProgress
from app.services.auth_service import decrypt_credential
from app.config import settings
import httpx
import io
import os
import uuid
import threading
from lxml import etree
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from reportlab.lib.pagesizes import A4, landscape
from reportlab.lib.units import cm
from reportlab.lib import colors
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.enums import TA_CENTER, TA_LEFT

router = APIRouter(prefix="/api/v1/folders", tags=["folders"])


def _serialize_folder(f: AlfrescoFolder) -> dict:
    return {
        "id": f.id,
        "parent_id": f.parent_id,
        "name": f.name,
        "node_path": f.node_path,
        "has_index_xml": f.has_index_xml,
        "index_xml_node_id": f.index_xml_node_id,
        "level": f.level if f.level else 0,
        "has_children": getattr(f, "has_children", False),
        "is_closed": bool(getattr(f, "is_closed", False)),
        "synced_at": f.synced_at.isoformat() if f.synced_at else None,
    }


@router.get("/site/{site_id}", summary="Obtener cuadro de clasificación", description="Devuelve todas las Series, Subseries y Expedientes de un sitio como lista plana. El frontend construye el cuadro jerárquico.")
async def get_folders_tree(site_id: int = Path(..., description="ID del sitio Alfresco"), db: Session = Depends(get_db)):
    site = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
    if not site:
        raise HTTPException(status_code=404, detail="Sitio no encontrado")

    doc_lib = db.query(AlfrescoFolder).filter(
        AlfrescoFolder.site_id == site_id,
        AlfrescoFolder.name == 'documentLibrary'
    ).first()
    
    doc_lib_id = doc_lib.id if doc_lib else None

    # Return ONLY root folders for lazy loading
    folders = db.query(AlfrescoFolder).filter(
        AlfrescoFolder.site_id == site_id,
        AlfrescoFolder.deleted == False,
        (AlfrescoFolder.parent_id == site.root_folder_id) | 
        (AlfrescoFolder.parent_id == None) | 
        (AlfrescoFolder.parent_id == doc_lib_id)
    ).order_by(AlfrescoFolder.name).all()

    folder_ids = [f.id for f in folders]
    from sqlalchemy import func
    child_counts = db.query(AlfrescoFolder.parent_id, func.count(AlfrescoFolder.id)).filter(
        AlfrescoFolder.parent_id.in_(folder_ids),
        AlfrescoFolder.deleted == False
    ).group_by(AlfrescoFolder.parent_id).all()
    has_children_map = {row[0]: row[1] > 0 for row in child_counts}

    items = []
    for f in folders:
        node = _serialize_folder(f)
        node["has_children"] = has_children_map.get(f.id, False)
        items.append(node)

    return {
        "site_id": site_id,
        "site_name": site.name,
        "site_title": site.title if site.title else site.name,
        "root_folder_id": site.root_folder_id,
        "items": items,
        "total": len(items),
    }


@router.get("/{folder_id}/children", summary="Obtener subseries y expedientes", description="Devuelve las subseries y expedientes hijas directas de una Serie, Subserie o Expediente específico.")
async def get_folder_children(folder_id: str = Path(..., description="Node ID de la Serie, Subserie o Expediente padre en Alfresco"), db: Session = Depends(get_db)):
    folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
    if not folder:
        raise HTTPException(status_code=404, detail="Serie, Subserie o Expediente no encontrado")

    children = db.query(AlfrescoFolder).filter(
        AlfrescoFolder.parent_id == folder_id,
        AlfrescoFolder.deleted == False
    ).order_by(AlfrescoFolder.name).all()

    child_ids = [c.id for c in children]
    from sqlalchemy import func
    child_counts = db.query(AlfrescoFolder.parent_id, func.count(AlfrescoFolder.id)).filter(
        AlfrescoFolder.parent_id.in_(child_ids),
        AlfrescoFolder.deleted == False
    ).group_by(AlfrescoFolder.parent_id).all()
    has_children_map = {row[0]: row[1] > 0 for row in child_counts}

    items = []
    for c in children:
        node = _serialize_folder(c)
        node["has_children"] = has_children_map.get(c.id, False)
        items.append(node)

    return {
        "folder_id": folder_id,
        "folder_name": folder.name,
        "items": items,
    }


@router.get("/site/{site_id}/search", summary="Buscar carpetas", description="Busca carpetas por nombre usando ILIKE.")
async def search_folders(site_id: int = Path(...), q: str = Query(...), db: Session = Depends(get_db)):
    if not q or len(q) < 2:
        return {"items": []}
    folders = db.query(AlfrescoFolder).filter(
        AlfrescoFolder.site_id == site_id,
        AlfrescoFolder.deleted == False,
        AlfrescoFolder.name.ilike(f"%{q}%")
    ).limit(50).all()
    
    items = []
    for f in folders:
        items.append(_serialize_folder(f))
    return {"items": items}


@router.post("/site/{site_id}/sync", summary="Sincronizar sitio completo", description="Dispara una tarea Celery que recorre recursivamente Series, Subseries y Expedientes y archivos desde Alfresco, detecta eliminados y regenera índices electrónicos.")
async def sync_site_folders(site_id: int = Path(..., description="ID del sitio a sincronizar"), body: SyncSiteRequest = None, db: Session = Depends(get_db)):
    deep = body.deep
    generate_index = body.generate_index

    existing = db.query(SyncProgress).filter(
        SyncProgress.site_id == site_id,
        SyncProgress.folder_id == None,
        SyncProgress.status.in_(["pending", "running"]),
    ).first()
    if existing:
        raise HTTPException(status_code=409, detail="Ya hay una sincronizacion en curso para este sitio")

    site = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
    if not site:
        raise HTTPException(status_code=404, detail="Sitio no encontrado")
    if not site.root_folder_id:
        raise HTTPException(status_code=400, detail="root_folder_id no configurado")

    from app.worker_tasks import sync_site_folders as celery_sync_site
    db_url = tenant_db_url_ctx.get() or settings.database_url
    task = celery_sync_site.delay(site_id, deep, generate_index, db_url)

    progress = SyncProgress(
        site_id=site_id,
        task_id=task.id,
        status="pending",
        message="Enviado a cola de sincronizacion..."
    )
    db.add(progress)
    db.commit()

    return {"ok": True, "task_id": task.id, "status": "pending", "message": "Sincronizacion en segundo plano iniciada"}


@router.get("/site/{site_id}/status", summary="Estado de sincronización del sitio", description="Consulta el progreso del último job de sincronización para el sitio.")
async def get_sync_status(site_id: int = Path(..., description="ID del sitio"), db: Session = Depends(get_db)):
    progress = db.query(SyncProgress).filter(
        SyncProgress.site_id == site_id,
        SyncProgress.folder_id == None,
    ).order_by(SyncProgress.created_at.desc()).first()

    if not progress:
        return {"status": "none", "message": "Sin sincronizaciones"}

    return {
        "status": progress.status,
        "progress": progress.progress,
        "message": progress.message,
        "task_id": progress.task_id,
        "total_folders": progress.total_folders,
    }


@router.get("/folder/{folder_id}/status", summary="Estado de sincronización de Serie, Subserie o Expediente", description="Consulta el progreso del último job de sincronización para una Serie, Subserie o Expediente específico.")
async def get_folder_sync_status(folder_id: str = Path(..., description="Node ID de la Serie, Subserie o Expediente"), db: Session = Depends(get_db)):
    progress = db.query(SyncProgress).filter(
        SyncProgress.folder_id == folder_id,
    ).order_by(SyncProgress.created_at.desc()).first()

    if not progress:
        return {"status": "none", "message": "Sin sincronizaciones"}

    return {
        "status": progress.status,
        "progress": progress.progress,
        "message": progress.message,
        "task_id": progress.task_id,
    }


@router.post("/{folder_id}/sync", summary="Sincronizar Serie, Subserie o Expediente", description="Dispara una tarea Celery para sincronizar los hijos de una Serie, Subserie o Expediente y regenerar su índice electrónico.")
async def sync_folder_children(folder_id: str = Path(..., description="Node ID de la Serie, Subserie o Expediente a sincronizar"), body: SyncSiteRequest = None, db: Session = Depends(get_db)):
    deep = body.deep
    generate_index = body.generate_index

    folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
    if not folder:
        raise HTTPException(status_code=404, detail="Serie, Subserie o Expediente no encontrado")

    existing = db.query(SyncProgress).filter(
        SyncProgress.folder_id == folder_id,
        SyncProgress.status.in_(["pending", "running"]),
    ).first()
    if existing:
        raise HTTPException(status_code=409, detail="Ya hay una sincronizacion en curso para esta Serie, Subserie o Expediente")

    from app.worker_tasks import sync_folder_children as celery_sync_folder
    db_url = tenant_db_url_ctx.get() or settings.database_url
    task = celery_sync_folder.delay(folder_id, deep, generate_index, db_url)

    progress = SyncProgress(
        site_id=folder.site_id,
        folder_id=folder_id,
        task_id=task.id,
        status="pending",
        message=f"Sincronizando {folder.name}..."
    )
    db.add(progress)
    db.commit()

    return {"ok": True, "task_id": task.id, "status": "pending"}


@router.get("/{node_id}/index-xml", summary="Obtener índice electrónico", description="Descarga el contenido del archivo index.xml desde Alfresco para una Serie, Subserie, Expediente o node ID. Si el archivo no existe en Alfresco, marca el elemento sin índice.")
async def get_folder_index_xml(node_id: str = Path(..., description="Node ID de la Serie, Subserie, Expediente o del archivo index.xml en Alfresco"), db: Session = Depends(get_db)):
    # node_id can be either the folder UUID or the index.xml file UUID
    index_xml_node_id = None
    folder_name = ""
    folder_id = None

    # Try as folder first
    folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == node_id).first()
    if folder:
        folder_name = folder.name
        folder_id = folder.id
        if folder.index_xml_node_id:
            index_xml_node_id = folder.index_xml_node_id
        # Also look for any folder that has this as index_xml_node_id
    if not index_xml_node_id:
        folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.index_xml_node_id == node_id).first()
        if folder:
            folder_name = folder.name
            folder_id = folder.id
            index_xml_node_id = node_id
    if not index_xml_node_id:
        # Try the node_id directly as the Alfresco content node
        index_xml_node_id = node_id
        folder_name = "index.xml"

    # Find any connection to use
    conn = db.query(AlfrescoConnection).filter(AlfrescoConnection.active == True).first()
    if not conn:
        # Try to find via a folder that has this index
        folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.index_xml_node_id == node_id).first()
        if folder:
            site = db.query(AlfrescoSite).filter(AlfrescoSite.id == folder.site_id).first()
            conn = db.query(AlfrescoConnection).filter(AlfrescoConnection.id == site.connection_id).first()
    if not conn or not conn.active:
        raise HTTPException(status_code=400, detail="Conexion no disponible")

    pwd = decrypt_credential(conn.password_hash)
    auth = httpx.BasicAuth(conn.username, pwd)

    try:
        url = f"{conn.base_url}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{index_xml_node_id}/content"
        async with httpx.AsyncClient(timeout=30, verify=False) as client:
            resp = await client.get(url, auth=auth)
            if resp.status_code == 404:
                if folder_id:
                    fld = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
                    if fld:
                        fld.has_index_xml = False
                        fld.index_xml_node_id = None
                        db.commit()
                raise HTTPException(status_code=404, detail="El indice electronico no existe o fue eliminado de Alfresco")
            if resp.status_code != 200:
                raise HTTPException(status_code=502, detail=f"Alfresco respondio HTTP {resp.status_code}")
            return {"ok": True, "content": resp.text, "folder_name": folder_name, "folder_id": folder_id}
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


def _make_session(db_url: str):
    from sqlalchemy import create_engine, event
    from sqlalchemy.orm import sessionmaker
    from app.models.database import _set_session_utc
    engine = create_engine(db_url)
    event.listen(engine, "connect", _set_session_utc)
    return sessionmaker(autocommit=False, autoflush=False, bind=engine)()


def _get_folder_hierarchy(db2, folder_id):
    """Recorre la cadena de padres desde folder_id hasta el root.
    Retorna (dependencia, serie, subserie, ruta_completa).
    La dependencia es el primer nivel bajo documentLibrary, serie el segundo, subserie el tercero.
    La ruta excluye documentLibrary."""
    ancestors = []
    current_id = folder_id
    while current_id:
        f = db2.query(AlfrescoFolder).filter(AlfrescoFolder.id == current_id).first()
        if not f:
            break
        ancestors.append(f)
        current_id = f.parent_id

    ancestors.reverse()

    # Eliminar documentLibrary si es el primer elemento
    cleaned = [a for a in ancestors if a.name != "documentLibrary"]

    dependencia = cleaned[0].name if len(cleaned) >= 1 else ""
    serie = cleaned[1].name if len(cleaned) >= 2 else ""
    subserie = cleaned[2].name if len(cleaned) >= 3 else ""
    ruta = " / ".join(a.name for a in cleaned)

    return dependencia, serie, subserie, ruta


def _generate_index_report_file(db, index_xml_node_id, folder_name, fmt, job_id, db_url, folder_id):
    """Hilo de fondo: descarga index.xml, genera Excel/PDF, guarda en /tmp y actualiza SyncProgress."""
    from app.services.auth_service import decrypt_credential
    db2 = _make_session(db_url)
    try:
        dependencia, serie, subserie, ruta = "", "", "", folder_name
        if folder_id:
            try:
                dependencia, serie, subserie, ruta = _get_folder_hierarchy(db2, folder_id)
            except Exception:
                pass

        conn = db2.query(AlfrescoConnection).filter(AlfrescoConnection.active == True).first()
        if not conn:
            raise Exception("No hay conexion Alfresco activa")

        pwd = decrypt_credential(conn.password_hash)
        auth = httpx.BasicAuth(conn.username, pwd)
        url = f"{conn.base_url}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{index_xml_node_id}/content"
        resp = httpx.get(url, auth=auth, timeout=30, verify=False)
        if resp.status_code != 200:
            raise Exception(f"Alfresco respondio HTTP {resp.status_code}")

        root = etree.fromstring(resp.content)
        docs = root.findall("DocumentoIndizado")

        columns = [
            "Id", "Orden", "Nombre Documento", "Tipologia Documental", "Fecha Creacion",
            "Fecha Incorporacion", "Autor", "Version", "Valor Huella",
            "Funcion Resumen", "Pagina Inicio", "Pagina Fin", "Formato", "Tamano"
        ]

        def _text(el, tag):
            child = el.find(tag)
            return child.text.strip() if child is not None and child.text else ""

        rows = []
        for doc in docs:
            rows.append([
                _text(doc, "Id"),
                _text(doc, "Orden_Documento_Expediente"),
                _text(doc, "Nombre_Documento"),
                _text(doc, "Tipologia_Documental"),
                _text(doc, "Fecha_Creacion_Documento"),
                _text(doc, "Fecha_Incorporacion_Expediente"),
                _text(doc, "Autor"),
                _text(doc, "Version"),
                _text(doc, "Valor_Huella"),
                _text(doc, "Funcion_Resumen"),
                _text(doc, "Pagina_Inicio"),
                _text(doc, "Pagina_Fin"),
                _text(doc, "Formato"),
                _text(doc, "Tamano"),
            ])

        safe_name = folder_name.replace("/", "-").replace(" ", "_")[:60]

        header_info = [
            ("Dependencia", dependencia),
            ("Serie", serie),
            ("Subserie", subserie),
            ("Ruta", ruta),
        ]

        info_font = Font(name="Calibri", size=9, bold=False)
        info_label_font = Font(name="Calibri", size=9, bold=True, color="6366F1")

        if fmt == "excel":
            wb = Workbook()
            ws = wb.active
            ws.title = "Indice Electronico"

            header_font = Font(name="Calibri", size=10, bold=True, color="FFFFFF")
            header_fill = PatternFill(start_color="6366F1", end_color="6366F1", fill_type="solid")
            header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
            cell_font = Font(name="Calibri", size=9)
            cell_align = Alignment(vertical="center", wrap_text=True)
            thin_border = Border(
                left=Side(style="thin", color="D1D5DB"),
                right=Side(style="thin", color="D1D5DB"),
                top=Side(style="thin", color="D1D5DB"),
                bottom=Side(style="thin", color="D1D5DB"),
            )

            info_row = 1
            for label, value in header_info:
                cl = ws.cell(row=info_row, column=1, value=label)
                cl.font = info_label_font
                cl.alignment = Alignment(vertical="center")
                cv = ws.cell(row=info_row, column=2, value=value)
                cv.font = info_font
                cv.alignment = Alignment(vertical="center")
                info_row += 1

            table_start = info_row + 1
            for ci, col_name in enumerate(columns, 1):
                cell = ws.cell(row=table_start, column=ci, value=col_name)
                cell.font = header_font
                cell.fill = header_fill
                cell.alignment = header_align
                cell.border = thin_border

            for ri, row_data in enumerate(rows, table_start + 1):
                for ci, val in enumerate(row_data, 1):
                    cell = ws.cell(row=ri, column=ci, value=val)
                    cell.font = cell_font
                    cell.alignment = cell_align
                    cell.border = thin_border

            col_widths = [30, 6, 32, 22, 14, 14, 20, 10, 24, 10, 10, 10, 10, 10]
            last_col_letter = ""
            for ci, w in enumerate(col_widths, 1):
                letter = ws.cell(row=table_start, column=ci).column_letter
                ws.column_dimensions[letter].width = w
                last_col_letter = letter

            data_end = table_start + len(rows)
            ws.auto_filter.ref = f"A{table_start}:{last_col_letter}{data_end}"
            ws.freeze_panes = f"A{table_start + 1}"

            ext = ".xlsx"
            path = f"/tmp/index_report_{job_id}{ext}"
            wb.save(path)

        else:
            pdf_buffer = io.BytesIO()
            page_w = landscape(A4)
            doc_pdf = SimpleDocTemplate(pdf_buffer, pagesize=page_w, leftMargin=1*cm, rightMargin=1*cm, topMargin=1*cm, bottomMargin=1*cm)
            styles = getSampleStyleSheet()

            header_style = ParagraphStyle("header_style", parent=styles["Normal"], fontName="Helvetica-Bold", fontSize=7, textColor=colors.white, alignment=TA_CENTER, leading=9)
            cell_style = ParagraphStyle("cell_style", parent=styles["Normal"], fontName="Helvetica", fontSize=7, alignment=TA_LEFT, leading=9)

            hdr_cells = [Paragraph(c, header_style) for c in columns]
            table_data = [hdr_cells]
            for row_data in rows:
                table_data.append([Paragraph(v[:60] if v else "", cell_style) for v in row_data])

            col_widths_pdf = [60, 26, 120, 80, 56, 56, 80, 40, 100, 40, 40, 40, 44, 40]
            tbl = Table(table_data, colWidths=col_widths_pdf, repeatRows=1)
            tbl.setStyle(TableStyle([
                ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#6366F1")),
                ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
                ("ALIGN", (0, 0), (-1, 0), "CENTER"),
                ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
                ("GRID", (0, 0), (-1, -1), 0.5, colors.HexColor("#D1D5DB")),
                ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.white, colors.HexColor("#F3F4F6")]),
                ("TOPPADDING", (0, 0), (-1, -1), 3),
                ("BOTTOMPADDING", (0, 0), (-1, -1), 3),
                ("LEFTPADDING", (0, 0), (-1, -1), 4),
                ("RIGHTPADDING", (0, 0), (-1, -1), 4),
            ]))

            info_style = ParagraphStyle("info_style", parent=styles["Normal"], fontName="Helvetica", fontSize=8, alignment=TA_LEFT, leading=12)
            info_label_style = ParagraphStyle("info_label", parent=styles["Normal"], fontName="Helvetica-Bold", fontSize=8, textColor=colors.HexColor("#6366F1"), alignment=TA_LEFT, leading=12)

            elements = []
            for label, value in header_info:
                elements.append(Paragraph(f"<font color='#6366F1'><b>{label}:</b></font> {value}", info_style))
            elements.append(Spacer(1, 0.4*cm))
            elements.append(tbl)
            doc_pdf.build(elements)

            ext = ".pdf"
            path = f"/tmp/index_report_{job_id}{ext}"
            with open(path, "wb") as f:
                f.write(pdf_buffer.getvalue())

        db3 = _make_session(db_url)
        try:
            p = db3.query(SyncProgress).filter(SyncProgress.task_id == job_id).first()
            if p:
                p.status = "completed"
                p.message = f"Reporte {fmt.upper()} generado"
                p.progress = 100
                db3.commit()
        finally:
            db3.close()

    except Exception as e:
        db3 = _make_session(db_url)
        try:
            p = db3.query(SyncProgress).filter(SyncProgress.task_id == job_id).first()
            if p:
                p.status = "error"
                p.error_message = str(e)[:500]
                db3.commit()
        finally:
            db3.close()
    finally:
        db2.close()


@router.get("/{node_id}/index-report", summary="Generar reporte del indice electronico", description="Inicia la generacion asincrona de un reporte tabla (Excel o PDF) desde el index.xml. Devuelve un job_id para consultar estado y descargar.")
async def start_index_report(
    node_id: str = Path(..., description="Node ID del archivo index.xml en Alfresco o de la Serie, Subserie o Expediente"),
    format: str = Query("excel", description="Formato de salida: excel o pdf"),
    db: Session = Depends(get_db)
):
    if format not in ("excel", "pdf"):
        raise HTTPException(status_code=400, detail="Formato invalido. Use 'excel' o 'pdf'")

    index_xml_node_id = None
    folder_name = ""
    site_id = None

    folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == node_id).first()
    if folder:
        folder_name = folder.name
        site_id = folder.site_id
        if folder.index_xml_node_id:
            index_xml_node_id = folder.index_xml_node_id
    if not index_xml_node_id:
        folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.index_xml_node_id == node_id).first()
        if folder:
            folder_name = folder.name
            site_id = folder.site_id
            index_xml_node_id = node_id
    if not index_xml_node_id:
        index_xml_node_id = node_id
        folder_name = "index.xml"

    if not index_xml_node_id:
        raise HTTPException(status_code=404, detail="No se encontro el indice electronico")

    job_id = str(uuid.uuid4())
    progress = SyncProgress(
        task_id=job_id,
        site_id=site_id,
        status="running",
        message=f"Generando reporte {format.upper()} del indice..."
    )
    db.add(progress)
    db.commit()

    db_url = tenant_db_url_ctx.get() or settings.database_url
    report_folder_id = folder.id if folder else None

    def run_report():
        _generate_index_report_file(db, index_xml_node_id, folder_name, format, job_id, db_url, report_folder_id)

    threading.Thread(target=run_report, daemon=True).start()
    return {"ok": True, "job_id": job_id}


@router.get("/index-report-status", summary="Estado de reporte de indice", description="Consulta el estado de generacion de un reporte de indice electronico por su job_id.")
async def index_report_status(
    job_id: str = Query(..., description="ID del trabajo"),
    db: Session = Depends(get_db)
):
    progress = db.query(SyncProgress).filter(SyncProgress.task_id == job_id).first()
    if not progress:
        return {"status": "not_found"}
    return {"status": progress.status, "error": progress.error_message or ""}


@router.get("/index-report-download", summary="Descargar reporte de indice", description="Descarga el archivo Excel o PDF del reporte de indice electronico generado.")
async def index_report_download(
    job_id: str = Query(..., description="ID del trabajo"),
    format: str = Query("excel", description="Formato del archivo generado"),
    db: Session = Depends(get_db)
):
    progress = db.query(SyncProgress).filter(SyncProgress.task_id == job_id).first()
    if not progress or progress.status != "completed":
        raise HTTPException(status_code=404, detail="Reporte no disponible")

    ext = ".xlsx" if format == "excel" else ".pdf"
    content_type = (
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        if format == "excel" else "application/pdf"
    )
    path = f"/tmp/index_report_{job_id}{ext}"
    if not os.path.exists(path):
        raise HTTPException(status_code=404, detail="Archivo no encontrado")

    db.delete(progress)
    db.commit()

    from app.services.timezone import now as tznow
    ts = tznow().strftime("%Y%m%d_%H%M%S")
    fname = f"indice_{ts}{ext}"
    return FileResponse(path, filename=fname, media_type=content_type)


@router.delete("/site/{site_id}", summary="Eliminar cuadro de clasificación", description="Borra todas las Series, Subseries y Expedientes del sitio de la base de datos local (no afecta Alfresco).")
async def clear_site_folders(site_id: int = Path(..., description="ID del sitio"), db: Session = Depends(get_db)):
    db.query(AlfrescoFolder).filter(AlfrescoFolder.site_id == site_id).delete()
    db.commit()
    return {"ok": True, "message": "Cuadro de clasificación eliminado"}