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

import logging
from app.services.index_generator import build_index_xml_content, compute_and_update_hash, upload_index_xml_to_alfresco
from app.worker import celery_app
from app.models.database import SessionLocal
from app.services.timezone import now as tznow
from app.services.timezone import iso, parse_iso

logger = logging.getLogger(__name__)


@celery_app.task(name="sync_site_folders", bind=True)
def sync_site_folders(self, site_id: int, deep: bool = False, generate_index: bool = True, db_url: str = None):
    import sys, os
    sys.path.insert(0, "/var/www/html/metafoliadorv2")
    from sqlalchemy import create_engine, event
    from sqlalchemy.orm import sessionmaker
    from app.models.database import _set_session_utc
    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

    if not db_url:
        db_url = settings.database_url
    engine = create_engine(db_url)
    event.listen(engine, "connect", _set_session_utc)
    Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    db = Session()
    task_id = self.request.id

    # Ensure initial progress record has correct site_id
    from app.models.sync_progress import SyncProgress as SP
    initial = db.query(SP).filter(SP.task_id == task_id).first()
    if initial and not initial.site_id:
        initial.site_id = site_id
        db.commit()

    try:
        site = db.query(AlfrescoSite).filter(AlfrescoSite.id == site_id).first()
        if not site:
            _update_progress(db, task_id, "error", 0, "Sitio no encontrado")
            return {"ok": False, "error": "Sitio no encontrado"}
        if not site.root_folder_id:
            _update_progress(db, task_id, "error", 0, "Sitio sin root_folder_id")
            return {"ok": False, "error": "root_folder_id no configurado"}

        conn = db.query(AlfrescoConnection).filter(AlfrescoConnection.id == site.connection_id).first()
        if not conn or not conn.active or not conn.username or not conn.password_hash:
            _update_progress(db, task_id, "error", 0, "Conexion no disponible")
            return {"ok": False, "error": "Conexion no disponible"}

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

        _update_progress(db, task_id, "running", 5, f"Conectando a Alfresco: {site.name}")

        # Count total first-level children to estimate progress
        total_created = 0
        total_updated = 0
        total_discovered = 0

        def fetch_children_sync(client, parent_id, parent_path, level, root_parent=False):
            nonlocal total_created, total_updated, total_discovered

            children_base = f"{conn.base_url}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{parent_id}/children"
            results = []
            sort_idx = 0
            alfresco_file_ids = set()
            fetch_ok = True
            skip_count = 0
            while True:
                children_url = f"{children_base}?maxItems=500&skipCount={skip_count}&include=properties,aspectNames"
                resp = client.get(children_url, auth=auth)
                if resp.status_code != 200:
                    fetch_ok = False
                    break
                data = resp.json().get("list", {})
                entries = data.get("entries", [])
                count = len(entries)
                pagination = data.get("pagination", {})
                has_more = pagination.get("hasMoreItems", False)
                for entry in entries:
                    child = entry.get("entry", {})
                    child_id = child.get("id", "")
                    child_name = child.get("name", "")
                    sort_idx += 1

                    if child.get("isFolder"):
                        child_path = f"{parent_path}/{child_name}"
                        child_type = child.get("nodeType", "")
                        has_children = child.get("hasChildren", False)

                        existing = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == child_id).first()
                        if existing:
                            existing.name = child_name
                            existing.node_path = child_path
                            existing.folder_type = child_type
                            existing.has_children = has_children
                            existing.synced_at = tznow()
                            total_updated += 1
                        else:
                            actual_parent = None if root_parent else parent_id
                            f = AlfrescoFolder(
                                id=child_id, site_id=site_id, parent_id=actual_parent,
                                name=child_name, node_path=child_path, folder_type=child_type,
                                has_children=has_children, level=level,
                                synced_at=tznow(),
                            )
                            db.add(f)
                            total_created += 1

                        total_discovered += 1
                        results.append({"id": child_id, "name": child_name, "path": child_path, "has_children": has_children})
                    elif child.get("isFile"):
                        alfresco_file_ids.add(child_id)
                        _upsert_file(db, child, parent_id, sort_idx, site_id)

                if not has_more and count < 500:
                    break
                skip_count += count

            if fetch_ok:
                _mark_deleted_files(db, parent_id, alfresco_file_ids, conn.base_url, auth, generate_index)
            else:
                logger.error(f"fetch_children_sync: error listando {parent_id}, se omite deteccion de archivos eliminados")

            for r in results:
                _check_index_xml(db, client, auth, conn.base_url, r["id"], site_id)
            db.commit()
            return results

        with httpx.Client(timeout=120, verify=False) as client:
            _update_progress(db, task_id, "running", 2, f"Obteniendo Series, Subseries y Expedientes de {site.name}...")
            root_children = fetch_children_sync(client, site.root_folder_id, site.root_folder_path or f"/Sites/{site.name}", 1, root_parent=True)

            # For both modes, start from documentLibrary children for better progress granularity
            children = root_children
            doclib = next((c for c in root_children if c["name"] == "documentLibrary"), None)
            if doclib:
                cname = doclib.get("name", "")
                cid = doclib.get("id", "")
                cpath = doclib.get("path", "")
                _update_progress(db, task_id, "running", 2, "Obteniendo Series, Subseries y Expedientes de documentLibrary...")
                children = fetch_children_sync(client, cid, cpath, 2)
                # Mark documentLibrary as synced
                existing = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == cid).first()
                if existing:
                    existing.synced_at = tznow()
                db.commit()

                        # Check index.xml for each first-level folder
            for ch in children:
                _check_index_xml(db, client, auth, conn.base_url, ch["id"], site_id)
            db.commit()

            total_children = len(children)
            _update_progress(db, task_id, "running", 5, f"{total_children} Series, Subseries y Expedientes en primer nivel")

            if deep and children:
                for idx, child in enumerate(children):
                    cname = child.get("name", "")
                    cid = child.get("id", "")
                    cpath = child.get("path", "")
                    base_pct = int((idx / total_children) * 100) if total_children > 0 else 50
                    if base_pct < 1: base_pct = 1
                    if base_pct > 99: base_pct = 99
                    _update_progress(db, task_id, "running", base_pct, "Serie/Subserie/Exp. " + str(idx+1) + "/" + str(total_children) + ": " + cname)

                    queue = [(cid, cpath, 3)]
                    sub_total = len(queue)
                    sub_processed = 0
                    while queue:
                        pid, ppath, lvl = queue.pop(0)
                        grandkids = fetch_children_sync(client, pid, ppath, lvl)
                        queue.extend([(g["id"], g["path"], lvl + 1) for g in grandkids])
                        sub_processed += 1
                        if total_children > 1:
                            step = 100.0 / total_children
                            sub_pct = base_pct + int(step * (sub_processed / max(sub_total, 1)))
                            if sub_pct > base_pct and sub_pct < 100:
                                _update_progress(db, task_id, "running", sub_pct, "Serie/Subserie/Exp. " + str(idx+1) + "/" + str(total_children) + ": " + cname)
            elif not deep:
                for idx, child in enumerate(children):
                    cname = child.get("name", "")
                    pct = int((idx / total_children) * 100) if total_children > 0 else 50
                    if pct < 1: pct = 1
                    if pct > 99: pct = 99
                    _update_progress(db, task_id, "running", pct, "Serie/Subserie/Exp. " + str(idx+1) + "/" + str(total_children) + ": " + cname)

        if generate_index:
            _update_progress(db, task_id, "running", 95, "Generando indices electronicos...", site_id)
            all_fids = db.query(AlfrescoFolder.id).filter(AlfrescoFolder.site_id == site_id, AlfrescoFolder.deleted == False).all()
            total_fids = len(all_fids)
            for idx, (fid,) in enumerate(all_fids):
                if idx % 50 == 0:
                    pct = 95 + int(5 * idx / max(total_fids, 1))
                    _update_progress(db, task_id, "running", min(pct, 99), f"Indices: {idx}/{total_fids}", site_id)
                _generate_index_for_folder(db, fid, conn.base_url, auth)
        site.last_sync_at = tznow()
        db.commit()

        _update_progress(db, task_id, "completed", 100, f"Completado: {total_created} creados, {total_updated} actualizados")
        return {"ok": True, "created": total_created, "updated": total_updated, "total": total_discovered}

    except Exception as e:
        logger.error(f"sync_site_folders failed for site {site_id}, task {task_id}: {e}")
        _update_progress(db, task_id, "error", 0, f"Error: {str(e)[:200]}")
        db.rollback()
        return {"ok": False, "error": str(e)}
    finally:
        db.close()


@celery_app.task(name="sync_folder_children", bind=True)
def sync_folder_children(self, folder_id: str, deep: bool = False, generate_index: bool = True, db_url: str = None):
    # Similar logic for individual folder sync
    import sys, os
    sys.path.insert(0, "/var/www/html/metafoliadorv2")
    from sqlalchemy import create_engine, event
    from sqlalchemy.orm import sessionmaker
    from app.models.database import _set_session_utc
    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

    if not db_url:
        db_url = settings.database_url
    engine = create_engine(db_url)
    event.listen(engine, "connect", _set_session_utc)
    Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    db = Session()
    task_id = self.request.id

    try:
        folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
        if not folder:
            _update_progress(db, task_id, "error", 0, "Serie, Subserie o Expediente no encontrado")
            return {"ok": False}

        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 or not conn.username or not conn.password_hash:
            _update_progress(db, task_id, "error", 0, "Conexion no disponible")
            return {"ok": False}

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

        _update_progress(db, task_id, "running", 5, f"Sincronizando {folder.name}...")

        total_created = 0
        total_updated = 0
        total_discovered = 0
        synced_folder_ids = [folder_id]

        def fetch_save(client, parent_id, parent_path, level):
            nonlocal total_created, total_updated, total_discovered, synced_folder_ids
            children_base = f"{conn.base_url}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{parent_id}/children"
            results = []
            sort_idx = 0
            alfresco_file_ids = set()
            fetch_ok = True
            skip_count = 0
            while True:
                url = f"{children_base}?maxItems=500&skipCount={skip_count}&include=properties,aspectNames"
                resp = client.get(url, auth=auth)
                if resp.status_code != 200:
                    fetch_ok = False
                    break
                data = resp.json().get("list", {})
                entries = data.get("entries", [])
                count = len(entries)
                pagination = data.get("pagination", {})
                has_more = pagination.get("hasMoreItems", False)
                for entry in entries:
                    child = entry.get("entry", {})
                    child_id = child.get("id", "")
                    child_name = child.get("name", "")
                    sort_idx += 1

                    if child.get("isFolder"):
                        child_path = f"{parent_path}/{child_name}"
                        has_children = child.get("hasChildren", False)

                        existing = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == child_id).first()
                        if existing:
                            existing.name = child_name
                            existing.node_path = child_path
                            existing.has_children = has_children
                            existing.synced_at = tznow()
                            if existing.parent_id != parent_id:
                                existing.parent_id = parent_id
                                existing.level = level
                            total_updated += 1
                        else:
                            f2 = AlfrescoFolder(
                                id=child_id, site_id=folder.site_id, parent_id=parent_id,
                                name=child_name, node_path=child_path,
                                folder_type=child.get("nodeType", ""),
                                has_children=has_children, level=level,
                                synced_at=tznow(),
                            )
                            db.add(f2)
                            total_created += 1

                        total_discovered += 1
                        synced_folder_ids.append(child_id)
                        results.append({"id": child_id, "name": child_name, "path": child_path, "has_children": has_children})
                    elif child.get("isFile"):
                        alfresco_file_ids.add(child_id)
                        _upsert_file(db, child, parent_id, sort_idx, folder.site_id)

                if not has_more and count < 500:
                    break
                skip_count += count

            if fetch_ok:
                _mark_deleted_files(db, parent_id, alfresco_file_ids, conn.base_url, auth, generate_index)
            else:
                logger.error(f"fetch_save: error listando {parent_id}, se omite deteccion de archivos eliminados")

            for r in results:
                _check_index_xml(db, client, auth, conn.base_url, r["id"], folder.site_id)
            db.commit()
            return results

        with httpx.Client(timeout=120, verify=False) as client:
            _check_index_xml(db, client, auth, conn.base_url, folder_id, folder.site_id)
            db.commit()
            children = fetch_save(client, folder_id, folder.node_path, folder.level + 1)
            total_children = len(children)

            if deep and children:
                for idx, child in enumerate(children):
                    cname = child.get("name", "")
                    cid = child.get("id", "")
                    cpath = child.get("path", "")
                    base_pct = int((idx / total_children) * 100) if total_children > 0 else 50
                    if base_pct < 1: base_pct = 1
                    if base_pct > 99: base_pct = 99
                    _update_progress(db, task_id, "running", base_pct, "Serie/Subserie/Exp. " + str(idx+1) + "/" + str(total_children) + ": " + cname)

                    queue = [(cid, cpath, folder.level + 2)]
                    sub_total = len(queue)
                    sub_processed = 0
                    while queue:
                        pid, ppath, lvl = queue.pop(0)
                        grandkids = fetch_save(client, pid, ppath, lvl)
                        queue.extend([(g["id"], g["path"], lvl + 1) for g in grandkids])
                        sub_processed += 1
                        if total_children > 1:
                            step = 100.0 / total_children
                            sub_pct = base_pct + int(step * (sub_processed / max(sub_total, 1)))
                            if sub_pct > base_pct and sub_pct < 100:
                                _update_progress(db, task_id, "running", sub_pct, "Serie/Subserie/Exp. " + str(idx+1) + "/" + str(total_children) + ": " + cname)
            elif not deep:
                for idx, child in enumerate(children):
                    cname = child.get("name", "")
                    pct = int((idx / total_children) * 100) if total_children > 0 else 50
                    if pct < 1: pct = 1
                    if pct > 99: pct = 99
                    _update_progress(db, task_id, "running", pct, "Serie/Subserie/Exp. " + str(idx+1) + "/" + str(total_children) + ": " + cname)

        folder.has_children = len(children) > 0
        folder.synced_at = tznow()
        db.commit()

        if generate_index:
            _update_progress(db, task_id, "running", 95, "Generando indices electronicos...")
            total_fids = len(synced_folder_ids)
            for idx, fid in enumerate(synced_folder_ids):
                if idx % 25 == 0:
                    pct = 95 + int(5 * idx / max(total_fids, 1))
                    _update_progress(db, task_id, "running", min(pct, 99), f"Indices: {idx}/{total_fids}")
                _generate_index_for_folder(db, fid, conn.base_url, auth)

        _update_progress(db, task_id, "completed", 100, f"OK: {total_created} creados, {total_updated} actualizados")
        return {"ok": True, "created": total_created, "updated": total_updated}

    except Exception as e:
        logger.error(f"_check_index_xml failed for {folder_id}: {e}")
        _update_progress(db, task_id, "error", 0, f"Error: {str(e)[:200]}")
        db.rollback()
        return {"ok": False}
    finally:
        db.close()



def _mark_deleted_files(db, folder_id, alfresco_file_ids, conn_base_url, auth, generate_index=True):
    """Marca como deleted los archivos en DB que ya no existen en Alfresco."""
    from app.models.alfresco_file import AlfrescoFile
    try:
        db_files = db.query(AlfrescoFile).filter(
            AlfrescoFile.folder_id == folder_id,
            AlfrescoFile.deleted == False
        ).all()

        deleted_any = False
        for dbf in db_files:
            if dbf.name == "index.xml":
                continue
            if dbf.id not in alfresco_file_ids:
                dbf.deleted = True
                dbf.synced_at = tznow()
                deleted_any = True
                logger.info(f"File marked as deleted: {dbf.name} ({dbf.id})")

        if deleted_any and generate_index:
            db.flush()
            _generate_index_for_folder(db, folder_id, conn_base_url, auth)
    except Exception as e:
        logger.error(f"_mark_deleted_files error: {e}")


def _upsert_file(db, entry, folder_id, sort_order, site_id_val):
    from app.models.alfresco_file import AlfrescoFile
    try:
        file_id = entry.get("id", "")
        name = entry.get("name", "")
        content = entry.get("content", {}) or {}
        props = entry.get("properties", {}) or {}
        aspects = entry.get("aspectNames", []) or []
        created_by = entry.get("createdByUser", {}) or {}
        existing = db.query(AlfrescoFile).filter(AlfrescoFile.id == file_id).first()
        new_data = {
            "folder_id": folder_id,
            "name": name,
            "mimetype": content.get("mimeType", ""),
            "size_bytes": content.get("sizeInBytes", 0),
            "alfresco_created_at": parse_iso(entry.get("createdAt")),
            "alfresco_modified_at": parse_iso(entry.get("modifiedAt")),
            "version": props.get("cm:versionLabel", "1.0"),
            "properties_json": {"properties": props, "aspects": aspects, "createdByUser": created_by},
            "sort_order": sort_order,
            "folios": 1,
            "synced_at": tznow(),
        }
        if existing:
            changed = False
            new_version = props.get("cm:versionLabel", "1.0")
            if existing.version and new_version and existing.version != new_version:
                existing.hash_sha256 = None
                changed = True
                logger.info(f"Version changed for {name}: {existing.version} -> {new_version}, hash cleared")
            for k, v in new_data.items():
                if k == "folios": continue
                current = getattr(existing, k, None)
                if current != v:
                    setattr(existing, k, v)
                    changed = True
            existing.deleted = False
            existing.synced_at = tznow()
            if changed:
                logger.info(f"File updated: {name}")
        else:
            f = AlfrescoFile(id=file_id, **new_data)
            db.add(f)
            logger.info(f"File created: {name}")
    except Exception as e:
        logger.error(f"_upsert_file error: {e}")


def _format_created_by_user(created_by: dict) -> str:
    """Formatea createdByUser como 'displayName (id)'."""
    if not created_by:
        return ""
    display_name = created_by.get("displayName", "") or ""
    user_id = created_by.get("id", "") or ""
    if display_name and user_id:
        return f"{display_name} ({user_id})"
    return display_name or user_id or ""


def _get_trd_hierarchy_info(db, folder):
    """
    Calcula Serie y Subserie según las reglas TRD:
    Nivel 1 bajo documentLibrary: Oficinas / Dependencias
    Nivel 2: Serie
    Nivel 3: Subserie
    Nivel 4+: Expediente / subcarpetas

    Reglas:
    - Si la carpeta indexada es nivel 3: Serie = nivel 2, Subserie = "" (vacío).
    - Si la carpeta indexada es nivel 1 o 2: Serie = "", Subserie = "".
    - Si la carpeta indexada es nivel 4+: Serie = nivel 2, Subserie = nivel 3.
    """
    from app.models.alfresco_folder import AlfrescoFolder
    ancestors = []
    curr = folder
    while curr:
        ancestors.append(curr)
        if curr.parent_id:
            curr = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == curr.parent_id).first()
        else:
            break
    ancestors.reverse()
    cleaned = [a.name for a in ancestors if a.name != "documentLibrary"]

    if folder.node_path:
        parts = [p for p in folder.node_path.strip("/").split("/") if p]
        if "documentLibrary" in parts:
            idx = parts.index("documentLibrary")
            path_cleaned = parts[idx + 1:]
            if len(path_cleaned) > len(cleaned):
                cleaned = path_cleaned

    total = len(cleaned)
    if total >= 4:
        serie = cleaned[1]
        subserie = cleaned[2]
    elif total == 3:
        serie = cleaned[1]
        subserie = ""
    else:
        serie = ""
        subserie = ""

    return serie, subserie


def _generate_index_for_folder(db, folder_id, conn_base_url, auth, force=False):
    """Genera/actualiza el index.xml para una Serie, Subserie o Expediente con los archivos que contiene.
    Si la carpeta esta cerrada (is_closed) se omite, salvo que force=True."""
    from app.models.alfresco_file import AlfrescoFile
    from app.models.alfresco_folder import AlfrescoFolder
    from app.models.alfresco_site import AlfrescoSite
    from app.models.alfresco_connection import AlfrescoConnection
    from app.models.alfresco_tipology import AlfrescoTipology
    try:
        folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
        if not folder:
            return

        if getattr(folder, "is_closed", False) and not force:
            logger.info(f"Folder {folder.name} esta cerrado, se omite la regeneracion del indice")
            return

        files = db.query(AlfrescoFile).filter(
            AlfrescoFile.folder_id == folder_id
        ).order_by(AlfrescoFile.alfresco_created_at.asc().nulls_last(), AlfrescoFile.name.asc()).all()

        active_files = [fl for fl in files if not fl.deleted and fl.name != "index.xml"]

        if not active_files and not any(fl.deleted for fl in files if fl.name != "index.xml"):
            logger.info(f"No files in folder {folder.name}, skipping index.xml")
            return

        for fl in active_files:
            if not fl.hash_sha256:
                logger.info(f"Computing hash for {fl.name}...")
                compute_and_update_hash(db, fl, conn_base_url, auth, fl.id)

        tipologies = db.query(AlfrescoTipology).filter(
            AlfrescoTipology.deleted_at == None,
            AlfrescoTipology.value != None,
            AlfrescoTipology.value != "",
        ).all()
        tipologia_keys = [t.value for t in tipologies]

        db.refresh(folder)
        files = db.query(AlfrescoFile).filter(
            AlfrescoFile.folder_id == folder_id
        ).order_by(AlfrescoFile.alfresco_created_at.asc().nulls_last(), AlfrescoFile.name.asc()).all()

        for idx, fl in enumerate(files, 1):
            if fl.sort_order != idx:
                fl.sort_order = idx

        files_data = [
            {
                "id": fl.id,
                "name": fl.name,
                "mimetype": fl.mimetype,
                "size_bytes": fl.size_bytes,
                "alfresco_created_at": iso(fl.alfresco_created_at) or "",
                "folios": fl.folios,
                "hash_sha256": fl.hash_sha256 or "",
                "version": fl.version or "",
                "created_by_user": _format_created_by_user((fl.properties_json or {}).get("createdByUser", {})),
                "properties": (fl.properties_json or {}).get("properties", {}),
            }
            for fl in files if fl.name != "index.xml" and not fl.deleted
        ]

        # Jerarquia TRD
        serie, subserie = _get_trd_hierarchy_info(db, folder)

        # Control de acceso y entidad de contacto
        site = folder.site or db.query(AlfrescoSite).filter(AlfrescoSite.id == folder.site_id).first()
        control_acceso = "Publico"
        if site and site.visibility:
            if site.visibility.strip().upper() == "PUBLIC":
                control_acceso = "Publico"
            else:
                control_acceso = "Privado"

        conn_name = ""
        if site:
            conn = site.connection or db.query(AlfrescoConnection).filter(AlfrescoConnection.id == site.connection_id).first()
            if conn and conn.name:
                conn_name = conn.name

        # Fechas de creacion y actualizacion del indice
        existing_index_file = db.query(AlfrescoFile).filter(
            AlfrescoFile.folder_id == folder_id,
            AlfrescoFile.name == "index.xml",
            AlfrescoFile.deleted == False,
        ).first()

        now_dt = tznow()
        fecha_creacion = None
        if existing_index_file:
            c_at = existing_index_file.alfresco_created_at or existing_index_file.created_at
            if c_at:
                if hasattr(c_at, "strftime"):
                    fecha_creacion = c_at.strftime("%Y-%m-%d %H:%M:%S")
                else:
                    fecha_creacion = str(c_at)[:19].replace("T", " ")

        if not fecha_creacion:
            fecha_creacion = now_dt.strftime("%Y-%m-%d %H:%M:%S")

        fecha_actualizacion = now_dt.strftime("%Y-%m-%d %H:%M:%S")

        expediente_info = {
            "identificador": folder.id,
            "fecha_creacion_indice": fecha_creacion,
            "fecha_actualizacion_indice": fecha_actualizacion,
            "nombre_de_expediente": folder.name,
            "serie_del_expediente": serie,
            "subserie_del_expediente": subserie,
            "estado_expediente": "Cerrado" if getattr(folder, "is_closed", False) else "Abierto",
            "control_de_acceso": control_acceso,
            "nombre_entidad_contacto": conn_name,
        }

        from app.models.alfresco_folder_history import AlfrescoFolderHistory
        history_records = db.query(AlfrescoFolderHistory).filter(
            AlfrescoFolderHistory.folder_id == folder_id
        ).order_by(AlfrescoFolderHistory.id.asc()).all()
        historial = [
            {
                "type": h.type or "",
                "date": iso(h.date) or "",
                "user": h.user or "",
                "comment": h.comment or "",
            }
            for h in history_records
        ]

        xml_content = build_index_xml_content(files_data, folder.name, folder.node_path or "", tipologia_keys, expediente_info, historial)
        success, node_id, err_msg = upload_index_xml_to_alfresco(conn_base_url, auth, folder_id, xml_content)
        if success:
            folder.has_index_xml = True
            if node_id:
                folder.index_xml_node_id = node_id
            folder.synced_at = tznow()
            _upsert_index_xml_file(db, folder_id, node_id, xml_content, conn_base_url, auth)
            db.commit()
            logger.info(f"index.xml generated for folder {folder.name} ({len(files_data)} files)")
        else:
            logger.error(f"Failed to upload index.xml for {folder.name}: {err_msg}")
    except Exception as e:
        logger.error(f"_generate_index_for_folder error: {e}")


def _upsert_index_xml_file(db, folder_id, node_id, xml_content, conn_base_url, auth):
    """Actualiza o crea el registro de index.xml en alfresco_files con la versión, tamaño, fecha y hash reales de Alfresco."""
    from app.models.alfresco_file import AlfrescoFile
    import hashlib
    import httpx
    if not node_id:
        return
    try:
        node_url = f"{conn_base_url.rstrip('/')}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{node_id}"
        node_entry = {}
        try:
            with httpx.Client(timeout=15, verify=False) as client:
                resp = client.get(node_url, auth=auth)
                if resp.status_code == 200:
                    node_entry = resp.json().get("entry", {})
        except Exception as e:
            logger.warning(f"Error consultando nodo index.xml {node_id} en Alfresco: {e}")

        props = node_entry.get("properties", {}) or {}
        content_info = node_entry.get("content", {}) or {}
        aspects = node_entry.get("aspectNames", []) or []
        created_by = node_entry.get("createdByUser", {}) or {}

        raw_bytes = xml_content.encode("utf-8") if isinstance(xml_content, str) else xml_content
        xml_sha256 = hashlib.sha256(raw_bytes).hexdigest()
        xml_size = content_info.get("sizeInBytes") or len(raw_bytes)
        version_label = props.get("cm:versionLabel") or "1.0"
        modified_at = parse_iso(node_entry.get("modifiedAt")) or tznow()
        created_at = parse_iso(node_entry.get("createdAt")) or tznow()

        existing = db.query(AlfrescoFile).filter(AlfrescoFile.id == node_id).first()
        if existing:
            existing.folder_id = folder_id
            existing.name = "index.xml"
            existing.mimetype = "text/xml"
            existing.size_bytes = xml_size
            existing.alfresco_modified_at = modified_at
            if not existing.alfresco_created_at and created_at:
                existing.alfresco_created_at = created_at
            existing.version = version_label
            existing.properties_json = {"properties": props, "aspects": aspects, "createdByUser": created_by}
            existing.hash_sha256 = xml_sha256
            existing.synced_at = tznow()
            existing.updated_at = tznow()
            existing.deleted = False
            logger.info(f"Registro index.xml actualizado en DB para carpeta {folder_id} (version {version_label})")
        else:
            max_so = db.query(AlfrescoFile.sort_order).filter(AlfrescoFile.folder_id == folder_id).order_by(AlfrescoFile.sort_order.desc()).first()
            sort_order = (max_so[0] + 1) if max_so and max_so[0] else 1
            f = AlfrescoFile(
                id=node_id,
                folder_id=folder_id,
                name="index.xml",
                mimetype="text/xml",
                size_bytes=xml_size,
                alfresco_created_at=created_at,
                alfresco_modified_at=modified_at,
                version=version_label,
                properties_json={"properties": props, "aspects": aspects, "createdByUser": created_by},
                sort_order=sort_order,
                folios=1,
                hash_sha256=xml_sha256,
                synced_at=tznow(),
                deleted=False,
            )
            db.add(f)
            logger.info(f"Registro index.xml creado en DB para carpeta {folder_id} (version {version_label})")
    except Exception as e:
        logger.error(f"_upsert_index_xml_file error: {e}")


def _check_index_xml(db, client, auth, base_url, folder_id, site_id):
    from app.models.alfresco_folder import AlfrescoFolder
    logger.info(f"[INDEX-CHECK] Checking {folder_id}")
    try:
        found = False
        # Primero resolver por relativePath: en algunas instalaciones index.xml
        # existe pero no aparece en el listado de hijos.
        node_url = f"{base_url}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{folder_id}?relativePath=index.xml"
        resp = client.get(node_url, auth=auth)
        if resp.status_code == 200:
            entry = resp.json().get("entry", {})
            if entry.get("isFile") and entry.get("name", "") == "index.xml":
                node_id = entry.get("id", "")
                folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
                if folder:
                    folder.has_index_xml = True
                    folder.index_xml_node_id = node_id
                found = True

        base_url_children = f"{base_url}/alfresco/api/-default-/public/alfresco/versions/1/nodes/{folder_id}/children"
        skip_count = 0
        while not found:
            url = f"{base_url_children}?maxItems=200&skipCount={skip_count}"
            resp = client.get(url, auth=auth)
            if resp.status_code != 200:
                break
            data = resp.json().get("list", {})
            entries = data.get("entries", [])
            count = len(entries)
            pagination = data.get("pagination", {})
            has_more = pagination.get("hasMoreItems", False)
            for e in entries:
                entry = e.get("entry", {})
                if entry.get("isFile") and entry.get("name", "") == "index.xml":
                    node_id = entry.get("id", "")
                    folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
                    if folder:
                        folder.has_index_xml = True
                        folder.index_xml_node_id = node_id
                    found = True
                    break
            if found:
                break
            if not has_more and count < 200:
                break
            skip_count += count
        if not found:
            folder = db.query(AlfrescoFolder).filter(AlfrescoFolder.id == folder_id).first()
            if folder and folder.has_index_xml:
                folder.has_index_xml = False
                folder.index_xml_node_id = None
                logger.info(f"[INDEX-CHECK] index.xml removed from {folder.name}")
        return found
    except Exception as e:
        logger.error(f"_check_index_xml failed for {folder_id}: {e}")
    return False

def _update_progress(db, task_id, status, progress, message, site_id_val=0):
    from app.models.sync_progress import SyncProgress
    record = db.query(SyncProgress).filter(SyncProgress.task_id == task_id).first()
    if record:
        record.status = status
        record.progress = progress
        record.message = message
        if site_id_val and not record.site_id:
            record.site_id = site_id_val
    else:
        # Don't create duplicates - the initial record already exists from the API call
        pass
    db.commit()