import sys
sys.path.insert(0, "/var/www/html/metafoliadorv2")
from app.schemas import CreateSiteRequest, UpdateSiteRequest

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

from app.models.database import get_db
from app.models.alfresco_site import AlfrescoSite
from app.models.alfresco_connection import AlfrescoConnection
from app.services.timezone import now as tznow
from app.services.timezone import iso

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


@router.get("", summary="Listar sitios", description="Lista todos los sitios Alfresco registrados. Filtrable por connection_id, activos y búsqueda por nombre/título.")
async def list_sites(
    connection_id: int = Query(None, description="Filtrar por ID de conexión Alfresco"),
    active_only: bool = Query(False, description="Filtrar solo sitios activos"),
    search: str = Query(None, description="Buscar por nombre o título del sitio"),
    db: Session = Depends(get_db),
):
    q = db.query(AlfrescoSite)
    if connection_id:
        q = q.filter(AlfrescoSite.connection_id == connection_id)
    if active_only:
        q = q.filter(AlfrescoSite.active == True)
    if search:
        q = q.filter(
            (AlfrescoSite.name.ilike(f"%{search}%")) |
            (AlfrescoSite.title.ilike(f"%{search}%"))
        )
    sites = q.order_by(AlfrescoSite.name).all()
    site_sizes = _get_site_sizes(db)
    return [_serialize(s, site_sizes.get(s.id, 0)) for s in sites]


@router.get("/{site_id}", summary="Obtener sitio", description="Devuelve el detalle completo de un sitio Alfresco por su ID.")
async def get_site(site_id: int = Path(..., description="ID del sitio"), db: Session = Depends(get_db)):
    s = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
    if not s:
        raise HTTPException(status_code=404, detail="Sitio no encontrado")
    site_sizes = _get_site_sizes(db)
    return _serialize(s, site_sizes.get(s.id, 0))


@router.post("", status_code=201, summary="Crear sitio", description="Registra un nuevo sitio Alfresco manualmente, vinculado a una conexión existente.")
async def create_site(body: CreateSiteRequest, db: Session = Depends(get_db)):
    required = ["name", "site_id", "connection_id"]
    for field in required:
        if not getattr(body, field):
            raise HTTPException(status_code=400, detail=f"'{field}' es requerido")

    conn = db.query(AlfrescoConnection).filter(AlfrescoConnection.id == body.connection_id).first()
    if not conn:
        raise HTTPException(status_code=400, detail="Conexion no existe")

    s = AlfrescoSite(
        connection_id=body.connection_id,
        name=body.name,
        site_id=body.site_id,
        title=body.title or body.name,
        description=body.description or "",
        visibility=body.visibility or "PUBLIC",
        root_folder_id=body.root_folder_id or "",
        root_folder_path=body.root_folder_path or "",
        document_library_id=body.document_library_id or "",
        site_preset=body.site_preset or "",
        site_role=body.site_role or "",
        metadata_json=body.metadata_json,
        active=body.active if body.active is not None else True,
    )
    db.add(s)
    db.commit()
    db.refresh(s)
    return _serialize(s)


@router.put("/{site_id}", summary="Actualizar sitio", description="Modifica los metadatos de un sitio Alfresco. Permite activar/desactivar.")
async def update_site(site_id: int = Path(..., description="ID del sitio"), body: UpdateSiteRequest = None, db: Session = Depends(get_db)):
    s = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
    if not s:
        raise HTTPException(status_code=404, detail="Sitio no encontrado")

    data = body.model_dump(exclude_unset=True)
    updatable = [
        "name", "title", "description", "visibility", "root_folder_id",
        "root_folder_path", "document_library_id", "site_preset",
        "site_role", "metadata_json", "connection_id",
    ]
    for field in updatable:
        if field in data and data[field] is not None:
            setattr(s, field, data[field])
    if "active" in data and data["active"] is not None:
        s.active = data["active"]

    db.commit()
    db.refresh(s)
    return _serialize(s)


@router.delete("/{site_id}", summary="Eliminar sitio", description="Elimina permanentemente un sitio y todos sus datos de Series, Subseries, Expedientes y archivos.")
async def delete_site(site_id: int = Path(..., description="ID del sitio a eliminar"), db: Session = Depends(get_db)):
    s = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
    if not s:
        raise HTTPException(status_code=404, detail="Sitio no encontrado")
    db.delete(s)
    db.commit()
    return {"ok": True}


@router.post("/{site_id}/sync", summary="Sincronizar sitio", description="Actualiza la marca de sincronización del sitio. La sincronización real se dispara desde folders.")
async def sync_site(site_id: int = Path(..., description="ID del sitio a sincronizar"), db: Session = Depends(get_db)):
    s = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
    if not s:
        raise HTTPException(status_code=404, detail="Sitio no encontrado")
    s.last_sync_at = tznow()
    db.commit()
    return {"ok": True, "last_sync_at": iso(s.last_sync_at)}


def _get_site_sizes(db) -> dict:
    """Retorna {site_id: total_size_bytes}."""
    from sqlalchemy import text
    rows = db.execute(text("""
        SELECT af.site_id, COALESCE(SUM(afi.size_bytes), 0)
        FROM alfresco_folders af
        JOIN alfresco_files afi ON afi.folder_id = af.id AND afi.deleted = false AND afi.name != 'index.xml'
        WHERE af.deleted = false
        GROUP BY af.site_id
    """)).fetchall()
    return {r[0]: r[1] for r in rows}


def _format_size(bytes_val):
    if not bytes_val or bytes_val == 0:
        return "—"
    if bytes_val < 1024:
        return f"{bytes_val} B"
    elif bytes_val < 1048576:
        return f"{bytes_val / 1024:.1f} KB"
    elif bytes_val < 1073741824:
        return f"{bytes_val / 1048576:.1f} MB"
    elif bytes_val < 1099511627776:
        return f"{bytes_val / 1073741824:.2f} GB"
    else:
        return f"{bytes_val / 1099511627776:.2f} TB"


def _serialize(s: AlfrescoSite, site_size: int = 0) -> dict:
    return {
        "id": s.id,
        "connection_id": s.connection_id,
        "connection_name": s.connection.name if s.connection else None,
        "name": s.name,
        "site_id": s.site_id,
        "title": s.title,
        "description": s.description,
        "visibility": s.visibility,
        "root_folder_id": s.root_folder_id,
        "root_folder_path": s.root_folder_path,
        "document_library_id": s.document_library_id,
        "site_preset": s.site_preset,
        "site_role": s.site_role,
        "metadata_json": s.metadata_json,
        "active": s.active,
        "last_sync_at": iso(s.last_sync_at),
        "repo_size": site_size,
        "repo_size_human": _format_size(site_size),
        "created_at": iso(s.created_at),
        "updated_at": iso(s.updated_at),
    }