"""
Document Database Service using SQLAlchemy ORM.
Replaces the raw SQL DatabaseManager class from document_base.py
Supports both main app database (via Session) and agent-specific databases (via db_file).
When db_file is used, uses app.core.database pooled engine with WAL mode (same as askme360_questions).
"""
import json
import os
from collections import defaultdict
from datetime import datetime
from typing import Any, Dict, List, Optional, Tuple, Union
from sqlalchemy import text
from sqlalchemy.orm import Session, sessionmaker

from app.utils.file_upload_check import escape_like_pattern, sql_like_escape_char

# Max file_ids per IN (...) batch for extracted_images queries (SQLite parameter limits).
_EXTRACTED_IMAGES_ID_CHUNK = 400


class DocumentDbService:
    """
    Service for document database operations.
    
    Supports two modes:
    - Session mode: Pass a SQLAlchemy Session (for FastAPI dependency injection with ORM models)
    - DB file mode: Pass a db_file path (for agent-specific databases, uses raw SQL)
    """
    
    def __init__(self, db: Union[Session, str] = None, db_file: str = None, agent_id: Union[str, List[str]] = None):
        """
        Initialize with either a SQLAlchemy session or a database file path.
        
        Args:
            db: SQLAlchemy Session or database file path string
            db_file: Database file path (alternative to passing path as db)
            agent_id: Single agent_id string or List of agent_id strings
        """
        self._use_raw_sql = False
        self.agent_id = agent_id
        
        # Support multiple ways to pass db_file
        if db_file is not None:
            # db_file parameter takes precedence
            self._init_with_db_file(db_file)
        elif isinstance(db, str):
            # db is a string path
            self._init_with_db_file(db)
        elif db is not None:
            # db is a SQLAlchemy Session
            self.db = db
            self._owns_session = False
        else:
            raise ValueError("Either db (Session) or db_file must be provided")
    
    def _init_with_db_file(self, db_file: str):
        """Initialize with a database file path. Uses app.core.database pooled engine with WAL mode."""
        from app.core.database import get_document_engine, init_document_database
        init_document_database(db_file)
        engine = get_document_engine(db_file)
        SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
        self.db = SessionLocal()
        self._owns_session = True
        self._use_raw_sql = True
    
    def close(self):
        """Close the session if we own it"""
        if getattr(self, '_owns_session', False) and self.db:
            self.db.close()

    def _add_agent_filter(self, query: str, params: Dict[str, Any], target_agent_id: Union[str, List[str]] = None) -> str:
        """
        Helper to append agent_id filter to SQL query string and update params.
        Supports single agent_id (str) or multiple agent_ids (List[str]).
        
        Detects if 'query' is a full SQL statement or just a condition snippet.
        """
        aid = target_agent_id if target_agent_id is not None else self.agent_id
        
        upper_query = query.upper()
        # Detect if 'query' is a full SQL statement.
        is_full_query = any(query.strip().upper().startswith(kw) for kw in ["SELECT", "UPDATE", "DELETE", "INSERT"])

        if " WHERE " in upper_query:
            prefix = " AND "
        elif is_full_query:
            prefix = " WHERE "
        elif not query.strip():
            # Empty snippet, no prefix needed for the first condition
            prefix = ""
        else:
            # It's a condition snippet with existing content, use AND
            prefix = " AND "

        if not aid:
            # If no agent filter specified (neither passed nor set on instance),
            # return a query that matches nothing to enforce strict isolation.
            # This prevents accidental leakage of data across all agents.
            return query + prefix + " 1=0 "

        if isinstance(aid, list):
            if not aid:
                # If empty list, force no results by adding an impossible condition
                return query + prefix + " 1=0 "
            if len(aid) == 1:
                query += prefix + "agent_id = :agent_id"
                params["agent_id"] = aid[0]
            else:
                placeholders = ", ".join([f":aid{i}" for i in range(len(aid))])
                query += prefix + f"agent_id IN ({placeholders})"
                for i, val in enumerate(aid):
                    params[f"aid{i}"] = val
        else:
            query += prefix + "agent_id = :agent_id"
            params["agent_id"] = aid
        
        return query
    
    def save_folder(self, folder_name: str, folder_path: str, 
                    parent_path: str = "", full_path: str = "", agent_id: str = None) -> Optional[Tuple]:
        """Save or update a folder entry"""
        try:
            # Check if folder exists for this agent
            target_agent_id = agent_id if agent_id is not None else self.agent_id
            
            query = "SELECT * FROM folders WHERE folder_path = :folder_path"
            params = {"folder_path": folder_path}
            query = self._add_agent_filter(query, params, target_agent_id)
            
            result = self.db.execute(text(query), params).fetchone()
            
            if result:
                return result
            
            # Insert new folder
            self.db.execute(
                text("""INSERT INTO folders (folder_name, folder_path, parent_path, full_path, file_count, created_date, agent_id)
                    VALUES (:folder_name, :folder_path, :parent_path, :full_path, 0, :created_date, :agent_id)"""),
                {
                    "folder_name": folder_name,
                    "folder_path": folder_path,
                    "parent_path": parent_path,
                    "full_path": full_path,
                    "created_date": datetime.now().isoformat(),
                    "agent_id": agent_id if agent_id is not None else self.agent_id
                }
            )
            self.db.commit()
            
            # Return the newly created folder
            return self.db.execute(text(query), params).fetchone()
            
        except Exception as e:
            self.db.rollback()
            print(f"Error saving folder: {e}")
            return None
    
    def get_all_folders(self) -> List[Tuple]:
        """Get all folders ordered by full_path"""
        query = "SELECT folder_name, folder_path, file_count, parent_path, full_path FROM folders"
        params = {}
        query = self._add_agent_filter(query, params)
        query += " ORDER BY full_path"
        
        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]
    
    def get_files_in_folder(self, folder_path: str) -> List[Tuple]:
        """Get all files in a specific folder"""
        query = """SELECT id, filename, original_filename, file_type, file_path, 
                   folder_path, relative_path, upload_date, file_size, content_preview, 
                   full_content, image_path, thumbnail_path, agent_id 
                   FROM files WHERE folder_path = :folder_path"""
        params = {"folder_path": folder_path}
        query = self._add_agent_filter(query, params)
        query += " ORDER BY original_filename"
        
        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]

    def search_files(
        self,
        keyword: str,
        folder_path: Optional[str] = None,
        limit: Optional[int] = None,
    ) -> List[Tuple]:
        """
        Index-friendly keyword search.

        - Filters by ``agent_id`` (uses ``ix_files_folder_agent`` / ``ix_files_agent_upload``).
        - Optional ``folder_path`` substring filter (uses ``ix_files_folder_path`` when exact).
        - Matches ``original_filename``, ``folder_path``, ``content_preview`` via case-insensitive ``LIKE``.
        - Omits ``full_content`` from the projection to keep I/O small.

        Returns tuples of:
        (id, filename, original_filename, file_type, file_path, folder_path,
         relative_path, upload_date, file_size, content_preview, image_path, thumbnail_path)
        """
        kw = (keyword or "").strip()
        if not kw:
            return []
        like = f"%{kw.lower()}%"
        params: Dict[str, Any] = {"kw": like}

        query = (
            "SELECT id, filename, original_filename, file_type, file_path, "
            "folder_path, relative_path, upload_date, file_size, content_preview, "
            "image_path, thumbnail_path "
            "FROM files WHERE ("
            "LOWER(original_filename) LIKE :kw "
            "OR LOWER(folder_path) LIKE :kw "
            "OR LOWER(content_preview) LIKE :kw"
            ")"
        )

        if folder_path:
            query += " AND LOWER(folder_path) LIKE :folder_like"
            params["folder_like"] = f"%{folder_path.strip().lower()}%"

        query = self._add_agent_filter(query, params)
        query += " ORDER BY upload_date DESC"
        if limit is not None:
            query += " LIMIT :lim"
            params["lim"] = limit

        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]

    def get_files_in_folder_browse(self, folder_path: str) -> List[Tuple]:
        """Light listing: same metadata as get_files_in_folder but omits full_content (preview + paths only)."""
        query = """SELECT id, filename, original_filename, file_type, file_path,
                    folder_path, relative_path, upload_date, file_size, content_preview,
                    image_path, thumbnail_path
                    FROM files WHERE folder_path = :folder_path"""
        params = {"folder_path": folder_path}
        query = self._add_agent_filter(query, params)
        query += " ORDER BY original_filename"
        
        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]

    def get_extracted_images_rows_by_file_ids(
        self, file_ids: List[str]
    ) -> Dict[str, List[Tuple]]:
        """
        file_id -> list of (id, image_path, image_type, page_number) — same tuple shape as get_extracted_images.
        Chunked IN queries; rows ordered by file_id, page_number, id within each chunk.
        """
        if not file_ids:
            return {}
        combined: Dict[str, List[Tuple]] = defaultdict(list)
        for start in range(0, len(file_ids), _EXTRACTED_IMAGES_ID_CHUNK):
            chunk = file_ids[start : start + _EXTRACTED_IMAGES_ID_CHUNK]
            placeholders = ", ".join(f":id{i}" for i in range(len(chunk)))
            params: Dict[str, Any] = {f"id{i}": chunk[i] for i in range(len(chunk))}
            
            query = f"SELECT id, image_path, image_type, page_number, file_id FROM extracted_images WHERE file_id IN ({placeholders})"
            query = self._add_agent_filter(query, params)
            query += " ORDER BY file_id, page_number, id"
            
            rows = self.db.execute(text(query), params).fetchall()
            for row in rows:
                fid = str(row[4])
                combined[fid].append((row[0], row[1], row[2], row[3]))
        return dict(combined)

    def save_file(self, file_id: str, original_filename: str, file_type: str,
                  file_path: str, content: str, folder_path: str = "",
                  relative_path: str = "", image_path: str = "", 
                  thumbnail_path: str = "", agent_id: str = None) -> Optional[Tuple]:
        """Save or update a file entry"""
        try:
            target_agent_id = agent_id if agent_id is not None else self.agent_id
            file_size = os.path.getsize(file_path) if os.path.exists(file_path) else 0
            preview = content[:500] if len(content) > 500 else content
            filename = os.path.basename(file_path)
            upload_date = datetime.now().isoformat()
            
            # Check if file exists for this agent
            query = "SELECT id FROM files WHERE id = :file_id"
            params = {"file_id": file_id}
            query = self._add_agent_filter(query, params, target_agent_id)
            
            existing = self.db.execute(text(query), params).fetchone()
            
            if existing:
                # Update existing file
                self.db.execute(
                    text("""UPDATE files SET filename = :filename, original_filename = :original_filename,
                            file_type = :file_type, file_path = :file_path, folder_path = :folder_path,
                            relative_path = :relative_path, upload_date = :upload_date, file_size = :file_size,
                            content_preview = :content_preview, image_path = :image_path, thumbnail_path = :thumbnail_path,
                            agent_id = :agent_id
                            WHERE id = :file_id"""),
                    {
                        "file_id": file_id, "filename": filename, "original_filename": original_filename,
                        "file_type": file_type, "file_path": file_path, "folder_path": folder_path,
                        "relative_path": relative_path, "upload_date": upload_date, "file_size": file_size,
                        "content_preview": preview, "image_path": image_path, "thumbnail_path": thumbnail_path,
                        "agent_id": target_agent_id
                    }
                )
            else:
                # Create new file
                self.db.execute(
                    text("""INSERT INTO files (id, filename, original_filename, file_type, file_path, 
                            folder_path, relative_path, upload_date, file_size, content_preview, 
                            full_content, image_path, thumbnail_path, agent_id)
                            VALUES (:file_id, :filename, :original_filename, :file_type, :file_path,
                            :folder_path, :relative_path, :upload_date, :file_size, :content_preview, 
                            '', :image_path, :thumbnail_path, :agent_id)"""),
                    {
                        "file_id": file_id, "filename": filename, "original_filename": original_filename,
                        "file_type": file_type, "file_path": file_path, "folder_path": folder_path,
                        "relative_path": relative_path, "upload_date": upload_date, "file_size": file_size,
                        "content_preview": preview, "image_path": image_path, "thumbnail_path": thumbnail_path,
                        "agent_id": target_agent_id
                    }
                )
            
            self.db.commit()
            
            # Update folder file count
            if folder_path:
                self._update_folder_file_count(folder_path)
            
            return self.get_file(file_id, agent_id=target_agent_id)
            
        except Exception as e:
            self.db.rollback()
            print(f"Error saving file: {e}")
            return None
    
    def _update_folder_file_count(self, folder_path: str):
        """Update the file count for a folder"""
        try:
            params = {"folder_path": folder_path}
            count_query = "SELECT COUNT(*) FROM files WHERE folder_path = :folder_path"
            count_query = self._add_agent_filter(count_query, params)
            
            count_result = self.db.execute(text(count_query), params).fetchone()
            count = count_result[0] if count_result else 0
            
            update_query = "UPDATE folders SET file_count = :count WHERE folder_path = :folder_path"
            update_query = self._add_agent_filter(update_query, params)
            
            self.db.execute(text(update_query), {**params, "count": count})
            self.db.commit()
        except Exception as e:
            print(f"Error updating folder file count: {e}")
    
    def get_file(self, file_id: str, agent_id: str = None) -> Optional[Tuple]:
        """Get a file by ID, returns tuple"""
        target_agent_id = agent_id if agent_id is not None else self.agent_id
        query = """SELECT id, filename, original_filename, file_type, file_path, 
                    folder_path, relative_path, upload_date, file_size, content_preview, 
                    full_content, image_path, thumbnail_path, agent_id 
                    FROM files WHERE id = :file_id"""
        params = {"file_id": file_id}
        query = self._add_agent_filter(query, params, target_agent_id)
            
        result = self.db.execute(text(query), params).fetchone()
        return tuple(result) if result else None
    
    def find_files_by_name_in_subfolder(
        self,
        folder_path: str,
        rel_dir: str,
        name_substring: str,
        agent_id: str = None,
    ) -> List[Tuple]:
        """
        Files in folder_path under the same relative subfolder whose original_filename
        contains name_substring (case-insensitive LIKE). Used by CHECK_FILE dedup.
        """
        name = (name_substring or "").strip()
        if not name:
            return []

        rel_dir_norm = (rel_dir or "").replace("\\", "/").strip("/")
        escaped = escape_like_pattern(name.lower())
        like_kw = f"%{escaped}%"
        params: Dict[str, Any] = {
            "folder_path": folder_path,
            "kw": like_kw,
        }

        query = """SELECT id, filename, original_filename, file_type, file_path,
                    folder_path, relative_path, upload_date, file_size, content_preview,
                    full_content, image_path, thumbnail_path, agent_id
                    FROM files WHERE folder_path = :folder_path"""

        esc = sql_like_escape_char()
        if rel_dir_norm:
            params["rel_prefix"] = f"{rel_dir_norm}/%"
            query += " AND relative_path LIKE :rel_prefix"
        else:
            query += " AND relative_path NOT LIKE '%/%'"

        query += f" AND (LOWER(original_filename) LIKE :kw ESCAPE '{esc}')"
        query = self._add_agent_filter(query, params, agent_id)

        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]

    def get_file_by_path(self, folder_path: str, relative_path: str, agent_id: str = None) -> Optional[Tuple]:
        """
        Check if a file exists with the given logical path for an agent.
        Returns the file tuple if found, else None.
        """
        target_agent_id = agent_id if agent_id is not None else self.agent_id
        query = """SELECT id, filename, original_filename, file_type, file_path, 
                    folder_path, relative_path, upload_date, file_size, content_preview, 
                    full_content, image_path, thumbnail_path, agent_id 
                    FROM files WHERE folder_path = :folder_path AND relative_path = :relative_path"""
        params = {"folder_path": folder_path, "relative_path": relative_path}
        query = self._add_agent_filter(query, params, target_agent_id)
            
        result = self.db.execute(text(query), params).fetchone()
        return tuple(result) if result else None

    def get_file_by_disk_path_and_name(
        self,
        disk_file_path: str,
        file_name: str,
        agent_id: Optional[str] = None,
    ) -> Optional[Tuple]:
        """Match ingested file by on-disk ``file_path`` and ``original_filename`` / ``filename``."""
        path_norm = os.path.normpath(os.path.abspath(str(disk_file_path or "").strip()))
        name = str(file_name or "").strip()
        if not path_norm or not name:
            return None
        target_agent_id = agent_id if agent_id is not None else self.agent_id
        query = """SELECT id, filename, original_filename, file_type, file_path,
                    folder_path, relative_path, upload_date, file_size, content_preview,
                    full_content, image_path, thumbnail_path, agent_id
                    FROM files
                    WHERE LOWER(file_path) = LOWER(:file_path)
                      AND (original_filename = :file_name OR filename = :file_name)"""
        params = {"file_path": path_norm, "file_name": name}
        query = self._add_agent_filter(query, params, target_agent_id)
        result = self.db.execute(text(query), params).fetchone()
        return tuple(result) if result else None
    
    def get_all_files(self) -> List[Tuple]:
        """Get all files ordered by upload date descending"""
        query = """SELECT id, filename, original_filename, file_type, file_path, 
                   folder_path, relative_path, upload_date, file_size, content_preview, 
                   full_content, image_path, thumbnail_path, agent_id 
                   FROM files"""
        params = {}
        query = self._add_agent_filter(query, params)
        query += " ORDER BY upload_date DESC"
        
        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]
    
    def save_extracted_image(self, file_id: str, image_path: str,
                             image_type: str, page_number: int, agent_id: str = None) -> Optional[Tuple]:
        """Save an extracted image entry"""
        try:
            self.db.execute(
                text("""INSERT INTO extracted_images (file_id, image_path, image_type, page_number, upload_date, agent_id)
                        VALUES (:file_id, :image_path, :image_type, :page_number, :upload_date, :agent_id)"""),
                {
                    "file_id": file_id,
                    "image_path": image_path,
                    "image_type": image_type,
                    "page_number": page_number,
                    "upload_date": datetime.now().isoformat(),
                    "agent_id": agent_id if agent_id is not None else self.agent_id
                }
            )
            self.db.commit()
            return (None, image_path, image_type, page_number)
        except Exception as e:
            self.db.rollback()
            print(f"Error saving extracted image: {e}")
            return None
    
    def get_extracted_images(self, file_id: str) -> List[Tuple]:
        """Get all extracted images for a file"""
        query = """SELECT id, image_path, image_type, page_number 
                   FROM extracted_images WHERE file_id = :file_id"""
        params = {"file_id": file_id}
        query = self._add_agent_filter(query, params)
        query += " ORDER BY page_number, id"
        
        result = self.db.execute(text(query), params).fetchall()
        return [tuple(r) for r in result]
    
    def delete_file(self, file_id: str, agent_id: str = None) -> bool:
        """Delete a file and its extracted images"""
        try:
            target_agent_id = agent_id if agent_id is not None else self.agent_id
            
            # Get folder path before deleting
            query = "SELECT folder_path FROM files WHERE id = :file_id"
            params = {"file_id": file_id}
            query = self._add_agent_filter(query, params, target_agent_id)
                
            file_result = self.db.execute(text(query), params).fetchone()
            
            if not file_result:
                return False
            
            folder_path = file_result[0]
            
            # Delete extracted images
            del_img_query = "DELETE FROM extracted_images WHERE file_id = :file_id"
            del_img_query = self._add_agent_filter(del_img_query, params, target_agent_id)
            
            self.db.execute(text(del_img_query), params)
            
            # Delete file
            del_file_query = "DELETE FROM files WHERE id = :file_id"
            del_file_query = self._add_agent_filter(del_file_query, params, target_agent_id)
                
            self.db.execute(text(del_file_query), params)
            self.db.commit()
            
            # Update folder file count
            if folder_path:
                self._update_folder_file_count(folder_path)
            
            return True
            
        except Exception as e:
            self.db.rollback()
            print(f"Error deleting file: {e}")
            return False
    
    def delete_folder(self, folder_path: str) -> bool:
        """
        Delete a folder and all files in it (and subfolders).
        
        Handles both:
        - Full path (C:\\Users\\...\\folder_name)
        - Folder name only (folder_name)
        """
        try:
            # Normalize folder path
            folder_path = folder_path.strip()
            print(f"[DELETE_FOLDER] Starting deletion for folder: '{folder_path}'")
            
            # Build query conditions
            query_cond = "folder_path = :folder_path"
            params = {"folder_path": folder_path}
            query_cond = self._add_agent_filter(query_cond, params)
            
            # Try to find folder by exact match first
            folder_check = self.db.execute(
                text(f"SELECT folder_path, folder_name FROM folders WHERE {query_cond}"),
                params
            ).fetchone()
            
            # If not found, try to find by folder name (extract from path)
            if not folder_check:
                # Extract folder name from full path
                import os
                folder_name = os.path.basename(folder_path.rstrip('/').rstrip('\\'))
                print(f"[DELETE_FOLDER] Folder not found by exact path, trying folder name: '{folder_name}'")
                
                # Search by folder name
                name_cond = "folder_name = :folder_name"
                name_params = {"folder_name": folder_name}
                if self.agent_id:
                    name_cond += " AND (agent_id = :agent_id OR agent_id IS NULL OR agent_id = '')"
                    name_params["agent_id"] = self.agent_id
                
                folder_check = self.db.execute(
                    text(f"SELECT folder_path, folder_name FROM folders WHERE {name_cond}"),
                    name_params
                ).fetchone()
                
                if folder_check:
                    # Update folder_path to the one stored in DB
                    folder_path = folder_check[0]
                    print(f"[DELETE_FOLDER] Found folder by name, using DB path: '{folder_path}'")
            
            if not folder_check:
                print(f"[DELETE_FOLDER] Folder not found: '{folder_path}'")
                # List ALL folders in the database for debugging
                all_folders = self.db.execute(
                    text("SELECT folder_path, folder_name FROM folders")
                ).fetchall()
                print(f"[DELETE_FOLDER] All folders in database: {[(f[0], f[1]) for f in all_folders]}")
                return False
            
            print(f"[DELETE_FOLDER] Found folder: {folder_check}")
            
            # Get all files in this folder directly
            files = self.get_files_in_folder(folder_path)
            print(f"[DELETE_FOLDER] Found {len(files)} files in folder")
            
            # Delete each file's extracted images and the file itself
            for file_info in files:
                file_id = file_info[0]
                print(f"[DELETE_FOLDER] Deleting file and images: {file_id}")
                
                # Delete extracted images with agent filter
                img_params = {"file_id": file_id}
                img_del = self._add_agent_filter("DELETE FROM extracted_images WHERE file_id = :file_id", img_params)
                self.db.execute(text(img_del), img_params)
                
                # Delete file with agent filter
                file_params = {"file_id": file_id}
                file_del = self._add_agent_filter("DELETE FROM files WHERE id = :file_id", file_params)
                self.db.execute(text(file_del), file_params)
            
            # Delete files in subfolders using LIKE pattern
            subfolder_pattern = folder_path + "/%"
            print(f"[DELETE_FOLDER] Deleting subfolders matching pattern: '{subfolder_pattern}'")
            
            del_cond = "folder_path LIKE :pattern"
            del_params = {"pattern": subfolder_pattern}
            del_cond = self._add_agent_filter(del_cond, del_params)
            
            self.db.execute(
                text(f"DELETE FROM extracted_images WHERE file_id IN (SELECT id FROM files WHERE {del_cond})"),
                del_params
            )
            self.db.execute(
                text(f"DELETE FROM files WHERE {del_cond}"),
                del_params
            )
            self.db.execute(
                text(f"DELETE FROM folders WHERE {del_cond}"),
                del_params
            )
            
            # Delete the folder itself (exact match)
            print(f"[DELETE_FOLDER] Deleting folder record: '{folder_path}'")
            
            exact_del_cond = "folder_path = :folder_path"
            exact_del_params = {"folder_path": folder_path}
            exact_del_cond = self._add_agent_filter(exact_del_cond, exact_del_params)
                
            result = self.db.execute(
                text(f"DELETE FROM folders WHERE {exact_del_cond}"),
                exact_del_params
            )
            rowcount = result.rowcount
            print(f"[DELETE_FOLDER] Delete result rowcount: {rowcount}")
            
            # Flush and commit
            self.db.flush()
            self.db.commit()
            
            # Expire all objects in session to ensure verification query hits DB
            self.db.expire_all()
            
            # Verify deletion with agent_id filter
            verify_cond = "folder_path = :folder_path"
            verify_params = {"folder_path": folder_path}
            verify_cond = self._add_agent_filter(verify_cond, verify_params)

            verify_folder = self.db.execute(
                text(f"SELECT * FROM folders WHERE {verify_cond}"),
                verify_params
            ).fetchone()
            
            if verify_folder:
                # If it still exists, try one more time with a fresh query and commit
                print(f"[DELETE_FOLDER] WARNING: Folder still exists after commit, attempting secondary delete: {verify_folder}")
                self.db.execute(
                    text(f"DELETE FROM folders WHERE {verify_cond}"),
                    verify_params
                )
                self.db.commit()
                
                # Final verification
                final_verify = self.db.execute(
                    text(f"SELECT * FROM folders WHERE {verify_cond}"),
                    verify_params
                ).fetchone()
                
                if final_verify:
                    print(f"[DELETE_FOLDER] ERROR: Folder still exists after secondary delete: {final_verify}")
                    return False
            
            print(f"[DELETE_FOLDER] Successfully deleted folder: '{folder_path}'")
            return True
            
        except Exception as e:
            self.db.rollback()
            print(f"[DELETE_FOLDER] Error deleting folder: {e}")
            import traceback
            traceback.print_exc()
            return False
    
    def clear_all(self) -> bool:
        """Clear all documents, files, and folders"""
        try:
            if self.agent_id:
                # Delete agent's extracted_images by finding files that belong to the agent
                self.db.execute(
                    text("DELETE FROM extracted_images WHERE file_id IN (SELECT id FROM files WHERE agent_id = :agent_id)"),
                    {"agent_id": self.agent_id}
                )
                # Delete agent's files
                self.db.execute(
                    text("DELETE FROM files WHERE agent_id = :agent_id"),
                    {"agent_id": self.agent_id}
                )
                # Delete agent's folders
                self.db.execute(
                    text("DELETE FROM folders WHERE agent_id = :agent_id"),
                    {"agent_id": self.agent_id}
                )
            else:
                self.db.execute(text("DELETE FROM extracted_images"))
                self.db.execute(text("DELETE FROM files"))
                self.db.execute(text("DELETE FROM folders"))
                
            try:
                self.db.execute(text("DELETE FROM folder_scan_jobs"))
            except Exception:
                pass
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            print(f"Error clearing all: {e}")
            return False
    
    def get_file_count(self) -> int:
        """Get total number of files"""
        query = "SELECT COUNT(*) FROM files"
        params = {}
        query = self._add_agent_filter(query, params)
        result = self.db.execute(text(query), params).fetchone()
        return result[0] if result else 0
    
    def get_folder_count(self) -> int:
        """Get total number of folders"""
        query = "SELECT COUNT(*) FROM folders"
        params = {}
        query = self._add_agent_filter(query, params)
        result = self.db.execute(text(query), params).fetchone()
        return result[0] if result else 0

    # --- Resumable batched folder scan jobs ---

    def create_folder_scan_job(
        self,
        *,
        job_id: str,
        root_path: str,
        root_folder_name: str,
        dirs_json: str,
        batch_size: int = 100,
        max_file_size: Optional[int] = None,
    ) -> bool:
        """Insert folder_scan_jobs row. dirs_json = JSON array of relative directory paths (DFS preorder, '' = root)."""
        try:
            now = datetime.now().isoformat()
            stats = json.dumps({"uploaded": 0, "skipped": 0, "total_chunks": 0})
            self.db.execute(
                text(
                    """INSERT INTO folder_scan_jobs (
                        id, root_path, root_folder_name, dirs_json, dir_index, file_offset,
                        batch_size, max_file_size, status, last_error, stats_json, created_at, updated_at
                    ) VALUES (
                        :id, :root_path, :root_folder_name, :dirs_json, 0, 0,
                        :batch_size, :max_file_size, 'pending', NULL, :stats_json, :created_at, :updated_at
                    )"""
                ),
                {
                    "id": job_id,
                    "root_path": root_path,
                    "root_folder_name": root_folder_name,
                    "dirs_json": dirs_json,
                    "batch_size": int(batch_size),
                    "max_file_size": max_file_size,
                    "stats_json": stats,
                    "created_at": now,
                    "updated_at": now,
                },
            )
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            print(f"Error create_folder_scan_job: {e}")
            return False

    def get_folder_scan_job(self, job_id: str) -> Optional[Dict[str, Any]]:
        try:
            row = self.db.execute(
                text("SELECT * FROM folder_scan_jobs WHERE id = :id"),
                {"id": job_id},
            ).fetchone()
            if not row:
                return None
            # Row.__getattr__ treats unknown attrs as column names, so `.keys()` can look up column "keys".
            return dict(row._mapping)
        except Exception as e:
            print(f"Error get_folder_scan_job: {e}")
            return None

    def delete_folder_scan_jobs_touching_paths(self, paths: list[str]) -> int:
        """Delete resumable scan jobs whose root_path matches or overlaps ``paths``."""
        paths_norm = list(
            {
                os.path.normpath(os.path.abspath(str(p).strip()))
                for p in paths
                if p and str(p).strip()
            }
        )
        if not paths_norm:
            return 0

        def _related(a: str, b: str) -> bool:
            return DocumentDbService._scan_roots_related(a, b)

        try:
            rows = self.db.execute(
                text("SELECT id, root_path FROM folder_scan_jobs")
            ).fetchall()
            to_delete = []
            for row in rows:
                mapping = dict(row._mapping)
                rp = os.path.normpath(
                    os.path.abspath(str(mapping.get("root_path") or "").strip())
                )
                if not rp:
                    continue
                if any(_related(rp, p) for p in paths_norm):
                    to_delete.append(mapping["id"])
            if not to_delete:
                return 0
            for jid in to_delete:
                self.db.execute(
                    text("DELETE FROM folder_scan_jobs WHERE id = :id"),
                    {"id": jid},
                )
            self.db.commit()
            return len(to_delete)
        except Exception as e:
            self.db.rollback()
            print(f"Error delete_folder_scan_jobs_touching_paths: {e}")
            return 0

    @staticmethod
    def _normalize_scan_root(root_path: str) -> str:
        return os.path.normcase(
            os.path.normpath(os.path.abspath(str(root_path or "").strip()))
        )

    @staticmethod
    def _scan_roots_related(path_a: str, path_b: str) -> bool:
        a = DocumentDbService._normalize_scan_root(path_a)
        b = DocumentDbService._normalize_scan_root(path_b)
        if not a or not b:
            return False
        if a == b:
            return True
        sep = os.sep
        return a.startswith(b + sep) or b.startswith(a + sep)

    def get_latest_folder_scan_job_for_root(self, root_path: str) -> Optional[Dict[str, Any]]:
        """Most recent folder_scan_jobs row for a scan root path (case-insensitive on Windows)."""
        try:
            rp = self._normalize_scan_root(root_path)
            if not rp:
                return None
            rows = self.db.execute(
                text("SELECT * FROM folder_scan_jobs ORDER BY updated_at DESC")
            ).fetchall()
            for row in rows:
                mapping = dict(row._mapping)
                stored = self._normalize_scan_root(mapping.get("root_path") or "")
                if stored == rp:
                    return mapping
            return None
        except Exception as e:
            print(f"Error get_latest_folder_scan_job_for_root: {e}")
            return None

    def get_active_folder_scan_job_for_root(self, root_path: str) -> Optional[Dict[str, Any]]:
        """Pending or running job for this scan root, if any."""
        try:
            rp = self._normalize_scan_root(root_path)
            if not rp:
                return None
            rows = self.db.execute(
                text(
                    "SELECT * FROM folder_scan_jobs "
                    "WHERE status IN ('pending', 'running') "
                    "ORDER BY updated_at DESC"
                )
            ).fetchall()
            for row in rows:
                mapping = dict(row._mapping)
                stored = self._normalize_scan_root(mapping.get("root_path") or "")
                if self._scan_roots_related(stored, rp):
                    return mapping
            return None
        except Exception as e:
            print(f"Error get_active_folder_scan_job_for_root: {e}")
            return None

    def update_folder_scan_job(
        self,
        job_id: str,
        *,
        dir_index: Optional[int] = None,
        file_offset: Optional[int] = None,
        status: Optional[str] = None,
        last_error: Optional[str] = None,
        stats_json: Optional[str] = None,
    ) -> bool:
        try:
            parts = ["updated_at = :updated_at"]
            params: Dict[str, Any] = {"id": job_id, "updated_at": datetime.now().isoformat()}
            if dir_index is not None:
                parts.append("dir_index = :dir_index")
                params["dir_index"] = int(dir_index)
            if file_offset is not None:
                parts.append("file_offset = :file_offset")
                params["file_offset"] = int(file_offset)
            if status is not None:
                parts.append("status = :status")
                params["status"] = status
            if last_error is not None:
                parts.append("last_error = :last_error")
                params["last_error"] = last_error
            if stats_json is not None:
                parts.append("stats_json = :stats_json")
                params["stats_json"] = stats_json
            sql = f"UPDATE folder_scan_jobs SET {', '.join(parts)} WHERE id = :id"
            self.db.execute(text(sql), params)
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            print(f"Error update_folder_scan_job: {e}")
            return False
