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

from collections import Counter
from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func

from app.models.database import get_db
from app.models.alfresco_site import AlfrescoSite
from app.models.alfresco_folder import AlfrescoFolder
from app.models.alfresco_file import AlfrescoFile
from app.models.sync_progress import SyncProgress
from app.services.timezone import iso

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


@router.get("/stats", summary="Dashboard - conteos generales", description="Estadísticas agregadas: total de sitios activos, Series, Subseries y Expedientes, archivos, Series, Subseries y Expedientes con índice y trabajos en ejecución.")
async def get_stats(db: Session = Depends(get_db)):
    sites_count = db.query(func.count(AlfrescoSite.id)).filter(AlfrescoSite.active == True).scalar() or 0
    folders_count = db.query(func.count(AlfrescoFolder.id)).scalar() or 0
    files_count = db.query(func.count(AlfrescoFile.id)).scalar() or 0
    index_count = db.query(func.count(AlfrescoFolder.id)).filter(AlfrescoFolder.has_index_xml == True).scalar() or 0
    jobs_running = db.query(func.count(SyncProgress.id)).filter(SyncProgress.status.in_(["pending", "running"])).scalar() or 0
    total_size = db.query(func.coalesce(func.sum(AlfrescoFile.size_bytes), 0)).join(
        AlfrescoFolder, AlfrescoFile.folder_id == AlfrescoFolder.id
    ).filter(
        AlfrescoFolder.deleted == False,
        AlfrescoFile.deleted == False,
        AlfrescoFile.name != "index.xml",
    ).scalar() or 0

    return {
        "sites": sites_count,
        "folders": folders_count,
        "files": files_count,
        "with_index": index_count,
        "jobs_running": jobs_running,
        "total_size": total_size,
    }


@router.get("/stats/charts", summary="Dashboard - datos para gráficos", description="Devuelve conteos de Series, Subseries y Expedientes, archivos e índices agrupados por sitio para alimentar los gráficos interactivos del dashboard.")
async def get_stats_charts(db: Session = Depends(get_db)):
    sites = db.query(AlfrescoSite).filter(AlfrescoSite.active == True).order_by(AlfrescoSite.name).all()

    folders_by_site = Counter()
    files_by_site = Counter()
    indices_by_site = Counter()

    for site in sites:
        folder_count = db.query(func.count(AlfrescoFolder.id)).filter(
            AlfrescoFolder.site_id == site.id, AlfrescoFolder.deleted == False
        ).scalar() or 0
        file_count = db.query(func.count(AlfrescoFile.id)).join(
            AlfrescoFolder, AlfrescoFile.folder_id == AlfrescoFolder.id
        ).filter(
            AlfrescoFolder.site_id == site.id,
            AlfrescoFile.deleted == False
        ).scalar() or 0
        index_count = db.query(func.count(AlfrescoFolder.id)).filter(
            AlfrescoFolder.site_id == site.id,
            AlfrescoFolder.has_index_xml == True,
            AlfrescoFolder.deleted == False
        ).scalar() or 0

        label = site.site_id
        folders_by_site[label] = folder_count
        files_by_site[label] = file_count
        indices_by_site[label] = index_count

    def to_series(counter):
        items = counter.most_common(20)
        return {
            "labels": [it[0] for it in items],
            "values": [it[1] for it in items],
        }

    return {
        "folders": to_series(folders_by_site),
        "files": to_series(files_by_site),
        "indices": to_series(indices_by_site),
    }


@router.get("/logs/recent", summary="Dashboard - logs recientes", description="Últimos registros de sincronización desde SyncProgress, ordenados por fecha descendente.")
async def get_recent_logs(limit: int = Query(10, description="Cantidad máxima de logs a retornar", ge=1, le=100), db: Session = Depends(get_db)):
    logs = db.query(SyncProgress).order_by(SyncProgress.created_at.desc()).limit(limit).all()
    return [
        {
            "id": log.id,
            "status": log.status,
            "message": log.message,
            "site_id": log.site_id,
            "created_at": iso(log.created_at),
        }
        for log in logs
    ]