import os
import sys
import time
import concurrent.futures
import platform
import psutil
import sqlite3
import shutil
import tempfile
from pathlib import Path
from difflib import SequenceMatcher
from ws_package.log.writer import LogWriter

# Optional imports for Windows-specific functionality
try:
    import win32gui
    import win32process
    WINDOWS_AVAILABLE = True
except ImportError:
    WINDOWS_AVAILABLE = False

# Optional imports for macOS-specific functionality
try:
    import subprocess
    MACOS_AVAILABLE = True
except ImportError:
    MACOS_AVAILABLE = False

logger = LogWriter.file(__name__)

# Wall-clock for the backup+copy leg (get_*_with_copy). A fast read-only snapshot is tried first
# inside get_chrome_url_from_db / get_firefox_url_from_db (no backup). Env overrides the copy leg.
# Override with env WORKSTATUS_BROWSER_DB_TIMEOUT_SEC (float).
_DEFAULT_BROWSER_DB_READ_TIMEOUT_SEC = 75.0
_BROWSER_DB_IMMUTABLE_CONNECT_TIMEOUT_SEC = 5.0
_BROWSER_DB_TIMEOUT_MIN = 20.0
_BROWSER_DB_TIMEOUT_MAX = 180.0


def _effective_browser_db_timeout_sec() -> float:
    raw = (os.environ.get("WORKSTATUS_BROWSER_DB_TIMEOUT_SEC") or "").strip()
    if not raw:
        return _DEFAULT_BROWSER_DB_READ_TIMEOUT_SEC
    try:
        v = float(raw)
    except ValueError:
        return _DEFAULT_BROWSER_DB_READ_TIMEOUT_SEC
    return max(_BROWSER_DB_TIMEOUT_MIN, min(_BROWSER_DB_TIMEOUT_MAX, v))


def _run_browser_db_read(func, timeout_sec, *args, **kwargs):
    """Run a sync browser DB read in a worker with a strict timeout (Windows, macOS, Linux)."""
    executor = concurrent.futures.ThreadPoolExecutor(max_workers=1)
    try:
        future = executor.submit(func, *args, **kwargs)
        return future.result(timeout=timeout_sec)
    except concurrent.futures.TimeoutError:
        logger.warning(
            "Browser database read timed out after %ss (%s); giving up so monitoring continues "
            "(raise WORKSTATUS_BROWSER_DB_TIMEOUT_SEC, max %ss)",
            timeout_sec,
            getattr(func, "__name__", str(func)),
            int(_BROWSER_DB_TIMEOUT_MAX),
        )
        return None
    finally:
        executor.shutdown(wait=False)


def _normalize_browser_db_path(db_path):
    """Absolute, resolved path to the live DB so SQLite sees a stable path on every OS."""
    if not db_path:
        return db_path
    try:
        return os.path.realpath(os.path.abspath(db_path))
    except (OSError, ValueError, TypeError):
        return db_path


def get_browser_temp_directory():
    """
    Writable directory for temp browser DB copies (used on Windows, macOS, and Linux).

    Same location is used by copy-then-query-then-delete flows so behavior is consistent
    across platforms; failures fall back to a system temp Workstatus subdirectory.
    """
    if platform.system() == "Darwin":  # macOS
        # Use Application Support directory (same as logs)
        temp_dir = Path.home() / "Library/Application Support/Workstatus/temp_browser_dbs"
        try:
            temp_dir.mkdir(parents=True, exist_ok=True)
            return str(temp_dir)
        except (PermissionError, OSError):
            # Fallback to system temp
            temp_dir = Path(tempfile.gettempdir()) / "Workstatus" / "temp_browser_dbs"
            temp_dir.mkdir(parents=True, exist_ok=True)
            return str(temp_dir)
    elif platform.system() == "Windows":
        if getattr(sys, 'frozen', False):
            # Running as frozen executable
            try:
                appdata_dir = Path(os.environ.get('APPDATA', '')) / "Workstatus" / "temp_browser_dbs"
                appdata_dir.mkdir(parents=True, exist_ok=True)
                return str(appdata_dir)
            except (PermissionError, OSError):
                # Fallback to executable directory
                temp_dir = Path(sys.executable).parent / "temp_browser_dbs"
                temp_dir.mkdir(parents=True, exist_ok=True)
                return str(temp_dir)
        else:
            # Development mode
            temp_dir = Path.cwd() / "temp_browser_dbs"
            temp_dir.mkdir(parents=True, exist_ok=True)
            return str(temp_dir)
    else:
        # Linux and other platforms
        temp_dir = Path.home() / "Workstatus" / "temp_browser_dbs"
        try:
            temp_dir.mkdir(parents=True, exist_ok=True)
            return str(temp_dir)
        except (PermissionError, OSError):
            # Fallback to system temp
            temp_dir = Path(tempfile.gettempdir()) / "Workstatus" / "temp_browser_dbs"
            temp_dir.mkdir(parents=True, exist_ok=True)
            return str(temp_dir)

def get_active_window_info():
    """
    Get information about the currently active window.
    Returns:
        tuple: (process_name, pid) or (None, None) if not available
    """
    system = platform.system().lower()
    
    if system == "windows" and WINDOWS_AVAILABLE:
        try:
            hwnd = win32gui.GetForegroundWindow()
            if not hwnd:
                return None, None
            
            _, pid = win32process.GetWindowThreadProcessId(hwnd)
            process = psutil.Process(pid)
            return process.name().replace(".exe", "").lower(), pid
        except Exception:
            return None, None
    
    elif system == "darwin" and MACOS_AVAILABLE:  # macOS
        try:
            # Use AppleScript to get the frontmost application
            script = '''
            tell application "System Events"
                set frontApp to first application process whose frontmost is true
                return name of frontApp
            end tell
            '''
            result = subprocess.run(['osascript', '-e', script], 
                                  capture_output=True, text=True, timeout=5)
            if result.returncode == 0:
                app_name = result.stdout.strip().lower()
                # Find the process ID
                for proc in psutil.process_iter(['pid', 'name']):
                    if proc.info['name'].lower() == app_name:
                        return app_name, proc.info['pid']
            return None, None
        except Exception:
            return None, None
    
    elif system == "linux":
        try:
            # Try to get active window using xdotool (if available)
            result = subprocess.run(['xdotool', 'getactivewindow', 'getwindowpid'], 
                                  capture_output=True, text=True, timeout=5)
            if result.returncode == 0:
                pid = int(result.stdout.strip())
                process = psutil.Process(pid)
                return process.name().lower(), pid
            return None, None
        except Exception:
            return None, None
    
    return None, None

def get_browser_db_paths():
    """
    Get browser database paths for all supported browsers on the current system.
    Returns:
        dict: Dictionary mapping browser names to their database paths
    """
    system = platform.system().lower()
    browser_paths = {}
    
    if system == "windows":
        browser_paths = {
            "chrome": [
                os.path.expanduser("~/AppData/Local/Google/Chrome/User Data"),
                os.path.expanduser("~/AppData/Roaming/Google/Chrome/User Data")
            ],
            "msedge": [
                os.path.expanduser("~/AppData/Local/Microsoft/Edge/User Data"),
                os.path.expanduser("~/AppData/Roaming/Microsoft/Edge/User Data")
            ],
            "brave": [
                os.path.expanduser("~/AppData/Local/BraveSoftware/Brave-Browser/User Data"),
                os.path.expanduser("~/AppData/Roaming/BraveSoftware/Brave-Browser/User Data")
            ],
            "firefox": [
                os.path.expanduser("~/AppData/Roaming/Mozilla/Firefox/Profiles"),
                os.path.expanduser("~/AppData/Local/Mozilla/Firefox/Profiles")
            ],
            "opera": [
                os.path.expanduser("~/AppData/Roaming/Opera Software/Opera Stable"),
                os.path.expanduser("~/AppData/Local/Opera Software/Opera Stable")
            ],
            "vivaldi": [
                os.path.expanduser("~/AppData/Local/Vivaldi/User Data"),
                os.path.expanduser("~/AppData/Roaming/Vivaldi/User Data")
            ],
            "chromium": [
                os.path.expanduser("~/AppData/Local/Chromium/User Data"),
                os.path.expanduser("~/AppData/Roaming/Chromium/User Data")
            ]
        }
    
    elif system == "darwin":  # macOS
        browser_paths = {
            "chrome": [
                os.path.expanduser("~/Library/Application Support/Google/Chrome"),
                os.path.expanduser("~/Library/Application Support/Google/Chrome Beta"),
                os.path.expanduser("~/Library/Application Support/Google/Chrome Dev")
            ],
            "safari": [
                os.path.expanduser("~/Library/Safari"),
                os.path.expanduser("~/Library/Containers/com.apple.Safari/Data/Library/Safari")
            ],
            "firefox": [
                os.path.expanduser("~/Library/Application Support/Firefox/Profiles"),
                os.path.expanduser("~/Library/Mozilla/Firefox/Profiles")
            ],
            "brave": [
                os.path.expanduser("~/Library/Application Support/BraveSoftware/Brave-Browser"),
                os.path.expanduser("~/Library/Application Support/BraveSoftware/Brave-Browser-Beta")
            ],
            "msedge": [
                os.path.expanduser("~/Library/Application Support/Microsoft Edge"),
                os.path.expanduser("~/Library/Application Support/Microsoft Edge Beta")
            ],
            "opera": [
                os.path.expanduser("~/Library/Application Support/com.operasoftware.Opera"),
                os.path.expanduser("~/Library/Application Support/com.operasoftware.Opera Beta")
            ],
            "vivaldi": [
                os.path.expanduser("~/Library/Application Support/Vivaldi"),
                os.path.expanduser("~/Library/Application Support/Vivaldi Beta")
            ],
            "chromium": [
                os.path.expanduser("~/Library/Application Support/Chromium")
            ]
        }
    
    elif system == "linux":
        browser_paths = {
            "chrome": [
                os.path.expanduser("~/.config/google-chrome"),
                os.path.expanduser("~/.config/google-chrome-beta"),
                os.path.expanduser("~/.config/google-chrome-unstable")
            ],
            "firefox": [
                os.path.expanduser("~/.mozilla/firefox"),
                os.path.expanduser("~/.firefox"),
                os.path.expanduser("~/.snap/firefox/common/.mozilla/firefox"),
                os.path.expanduser("~/snap/firefox/common/.mozilla/firefox"),
                os.path.expanduser("~/.var/app/org.mozilla.firefox/data/.mozilla/firefox"),  # Flatpak
                os.path.expanduser("~/.local/share/flatpak/app/org.mozilla.firefox/current/active/files/.mozilla/firefox")  # Flatpak system
            ],
            "brave": [
                os.path.expanduser("~/.config/BraveSoftware/Brave-Browser"),
                os.path.expanduser("~/.config/BraveSoftware/Brave-Browser-Beta")
            ],
            "msedge": [
                os.path.expanduser("~/.config/microsoft-edge"),
                os.path.expanduser("~/.config/microsoft-edge-beta"),
                os.path.expanduser("~/.config/microsoft-edge-dev")
            ],
            "opera": [
                os.path.expanduser("~/.config/opera"),
                os.path.expanduser("~/.config/opera-beta")
            ],
            "vivaldi": [
                os.path.expanduser("~/.config/vivaldi"),
                os.path.expanduser("~/.config/vivaldi-beta")
            ],
            "chromium": [
                os.path.expanduser("~/.config/chromium"),
                os.path.expanduser("~/.config/chromium-dev")
            ],
            "waterfox": [
                os.path.expanduser("~/.waterfox"),
                os.path.expanduser("~/.mozilla/waterfox")
            ],
            "pale-moon": [
                os.path.expanduser("~/.moonchild productions/pale moon"),
                os.path.expanduser("~/.mozilla/palemoon")
            ]
        }
    
    return browser_paths

def find_browser_databases(browser_name, browser_paths):
    """
    Find database files for a specific browser.
    Args:
        browser_name: Name of the browser
        browser_paths: List of possible paths for the browser
    Returns:
        list: List of database file paths
    """
    db_paths = []
    
    for root_path in browser_paths:
        if not os.path.exists(root_path):
            continue
            
        try:
            if browser_name == "firefox":
                # Firefox uses places.sqlite
                try:
                    for profile in os.listdir(root_path):
                        profile_path = os.path.join(root_path, profile)
                        if os.path.isdir(profile_path):
                            places_db = os.path.join(profile_path, "places.sqlite")
                            if os.path.exists(places_db):
                                db_paths.append(places_db)
                                logger.info("Found Firefox database: %s", places_db)
                except Exception as e:
                    logger.info("Error scanning Firefox profiles in %s: %s", root_path, e)
            
            elif browser_name == "safari":
                # Safari uses History.db
                history_db = os.path.join(root_path, "History.db")
                if os.path.exists(history_db):
                    db_paths.append(history_db)
                
                # Also check for WebKit databases
                webkit_path = os.path.join(root_path, "WebKit")
                if os.path.exists(webkit_path):
                    for item in os.listdir(webkit_path):
                        item_path = os.path.join(webkit_path, item)
                        if os.path.isdir(item_path):
                            webkit_db = os.path.join(item_path, "History.db")
                            if os.path.exists(webkit_db):
                                db_paths.append(webkit_db)
            
            else:
                # Chrome-based browsers use History file
                for profile in os.listdir(root_path):
                    if profile.startswith("Profile") or profile == "Default":
                        profile_path = os.path.join(root_path, profile)
                        if os.path.isdir(profile_path):
                            history_file = os.path.join(profile_path, "History")
                            if os.path.exists(history_file):
                                db_paths.append(history_file)
                            
                            # Also check for other database files
                            for db_file in ["Login Data", "Web Data", "Cookies"]:
                                db_path = os.path.join(profile_path, db_file)
                                if os.path.exists(db_path):
                                    db_paths.append(db_path)
        
        except (PermissionError, OSError) as e:
            logger.info("Permission denied accessing %s: %s", root_path, e)
            continue
    
    return db_paths

# Known browser process/window names; when passed as tab_title we treat as "no title" and use most recent URL
_BROWSER_NAMES_FOR_SKIP_TITLE = frozenset({
    "chrome", "google chrome", "chromium", "firefox", "safari", "brave", "msedge",
    "microsoft edge", "opera", "vivaldi", "waterfox", "pale-moon", "palemoon",
})


def _is_browser_name_as_tab_title(tab_title):
    """If tab_title is the app name (browser name), we should not match it to page titles; use most recent URL."""
    if not tab_title or not isinstance(tab_title, str):
        return True
    t = tab_title.lower().strip()
    if t in _BROWSER_NAMES_FOR_SKIP_TITLE:
        return True
    for name in _BROWSER_NAMES_FOR_SKIP_TITLE:
        if name in t or t in name:
            return True
    return False


def get_url_from_browser_database(browser_name, tab_title=None, limit=100):
    """
    Get URL from browser database based on tab title or return recent URLs.
    
    When tab_title is a browser name (e.g. from active window), we ignore it and return
    the most recently visited URL so we capture at least one URL. When tab_title is a
    real page title, we fuzzy-match against history to find the best URL.
    
    Args:
        browser_name: Name of the browser
        tab_title: Tab title to match (optional); if this is the browser app name, treated as None
        limit: Maximum number of URLs to fetch for title matching (higher = more candidates)
        
    Returns:
        str: URL if found, None otherwise
    """
    try:
        # If caller passed browser name as tab_title (e.g. active app name), use "most recent URL" path
        if _is_browser_name_as_tab_title(tab_title):
            tab_title = None
        # Get browser-specific database paths instead of all active browsers
        all_browser_paths = get_browser_db_paths()
        
        # Normalize browser name to lowercase for case-insensitive matching
        browser_name_lower = browser_name.lower() if browser_name else None
        
        if not browser_name_lower or browser_name_lower not in all_browser_paths:
            logger.info("Browser '%s' not supported", browser_name)
            return None
            
        browser_paths = all_browser_paths[browser_name_lower]
        # Use normalized name for consistency
        db_paths = find_browser_databases(browser_name_lower, browser_paths)
        
        if not db_paths:
            logger.info("No database paths found for %s", browser_name)
            return None
            
        # Build list of profile DBs with mtime (most recent first) so we try all profiles
        available_dbs = []
        history_dbs = []
        for db_path in db_paths:
            if os.path.exists(db_path):
                mtime = os.path.getmtime(db_path)
                available_dbs.append((mtime, db_path))
                if 'History' in db_path or 'places.sqlite' in db_path or 'History.db' in db_path:
                    history_dbs.append((mtime, db_path))
        if history_dbs:
            history_dbs.sort(key=lambda x: x[0], reverse=True)
            dbs_to_try = [p for _, p in history_dbs]
        else:
            available_dbs.sort(key=lambda x: x[0], reverse=True)
            dbs_to_try = [p for _, p in available_dbs]
        if not dbs_to_try:
            logger.info("No accessible database found for %s", browser_name)
            return None
        logger.debug("Trying %s profile(s) for %s (all profiles)", len(dbs_to_try), browser_name)
        # Try each profile until we get a URL (capture from whichever profile has the active tab)
        for db_path in dbs_to_try:
            try:
                logger.debug("Attempting to fetch URL from %s: %s", browser_name, db_path)
                if browser_name_lower == "firefox":
                    url = _run_browser_db_read(
                        get_firefox_url_from_db,
                        _effective_browser_db_timeout_sec(),
                        db_path,
                        tab_title,
                        limit,
                    )
                elif browser_name_lower == "safari":
                    # Safari uses History.db with its own schema (history_items + history_visits).
                    # get_safari_url_from_db() handles the backup-copy + correct table layout.
                    url = _run_browser_db_read(
                        get_safari_url_from_db,
                        _effective_browser_db_timeout_sec(),
                        db_path,
                        tab_title,
                        limit,
                    )
                else:
                    url = _run_browser_db_read(
                        get_chrome_url_from_db,
                        _effective_browser_db_timeout_sec(),
                        db_path,
                        tab_title,
                        limit,
                    )
                if url and url.strip():
                    return url.strip()
            except Exception as per_profile_e:
                logger.debug("Profile %s failed: %s, trying next profile", db_path, per_profile_e)
                continue
        return None
        
    except Exception as e:
        logger.info("Error fetching URL from browser database: %s", e)
        return None

def get_safari_url_from_db(db_path, tab_title=None, limit=50):
    """
    Read the most-recently-visited URL from Safari's History.db.

    Safari's schema differs completely from Chrome:
      history_items(id, url, domain_expansion, visit_count, ...)
      history_visits(id, history_item, visit_time, title, ...)
    where visit_time is seconds since the CoreData epoch (2001-01-01 00:00:00 UTC),
    NOT the Unix epoch used by Chrome.

    Safari holds an exclusive WAL lock while running, so we copy via the SQLite
    backup API (handles WAL correctly) before querying.  The copy is unlinked
    immediately after the read so we never leave stale files on disk.

    Returns the URL string, or None if the DB is locked / unreadable.
    """
    import time as _time

    norm = _normalize_browser_db_path(db_path)
    if not os.path.isfile(norm):
        return None

    temp_db = None
    try:
        temp_dir = get_browser_temp_directory()
        temp_db = os.path.join(
            temp_dir, f"safari_history_temp_{int(_time.time() * 1_000_000)}_{os.getpid()}.db"
        )

        # --- copy via backup API (handles WAL; shutil.copy2 causes "malformed" errors) ---
        src = sqlite3.connect(norm, timeout=5)
        try:
            dst = sqlite3.connect(temp_db, timeout=5)
            try:
                try:
                    src.backup(dst, sleep=0)
                except TypeError:
                    src.backup(dst)
            finally:
                dst.close()
        finally:
            src.close()

        # --- query the copy ---
        url_found = None
        conn = sqlite3.connect(temp_db, timeout=5)
        try:
            cursor = conn.cursor()
            if tab_title and not _is_browser_name_as_tab_title(tab_title):
                # Match against page title when we have a meaningful title.
                cursor.execute("""
                    SELECT hi.url, hv.title, hv.visit_time
                    FROM history_visits hv
                    JOIN history_items hi ON hv.history_item = hi.id
                    WHERE hv.title IS NOT NULL AND hv.title != ''
                    ORDER BY hv.visit_time DESC
                    LIMIT ?
                """, (limit,))
                rows = cursor.fetchall()
                tab_lower = tab_title.lower()
                for row_url, row_title, _ in rows:
                    if row_title and tab_lower in row_title.lower():
                        url_found = row_url
                        break
                # Fall back to most-recent URL if title match failed
                if not url_found and rows:
                    url_found = rows[0][0]
            else:
                cursor.execute("""
                    SELECT hi.url
                    FROM history_visits hv
                    JOIN history_items hi ON hv.history_item = hi.id
                    WHERE hi.url IS NOT NULL AND hi.url != ''
                    ORDER BY hv.visit_time DESC
                    LIMIT 1
                """)
                row = cursor.fetchone()
                if row:
                    url_found = row[0]
        finally:
            conn.close()

        _unlink_browser_temp_copy(temp_db)
        temp_db = None

        if url_found:
            logger.debug("[Safari] URL from History.db: %s", url_found)
        else:
            logger.debug("[Safari] No URL found in History.db (DB may be empty or just opened)")
        return url_found or None

    except sqlite3.OperationalError as e:
        err = str(e).lower()
        if "locked" in err or "busy" in err:
            logger.debug("[Safari] History.db locked (Safari is open and writing); returning None")
        else:
            logger.warning("[Safari] SQLite error reading History.db: %s", e)
        return None
    except Exception as e:
        logger.warning("[Safari] Unexpected error reading History.db: %s: %s", type(e).__name__, e)
        return None
    finally:
        _unlink_browser_temp_copy(temp_db)


def get_firefox_url_from_db(db_path, tab_title=None, limit=50):
    """
    Firefox URL: try read-only snapshot (immutable URI) first to avoid long locks; if that fails,
    copy places.sqlite via SQLite backup, query, delete temp copy.
    """
    norm = _normalize_browser_db_path(db_path)
    if not os.path.isfile(norm):
        return None
    url = _try_firefox_places_readonly_immutable(norm, tab_title, limit)
    if url:
        logger.info("Firefox URL from read-only snapshot (immutable=1); skipped SQLite backup")
        return url
    logger.info("Firefox immutable read missed URL or failed; using SQLite backup copy path")
    return _get_firefox_url_with_copy(norm, tab_title, limit)


def _unlink_browser_temp_copy(path):
    """Remove a temp DB file as soon as it is unneeded (primary cleanup, not only in finally)."""
    if path and os.path.isfile(path):
        try:
            os.remove(path)
        except OSError:
            pass


def _pick_chrome_url_from_rows(results, result, tab_title):
    """Map Chrome urls+visits rows to a single URL (same rules as copy path)."""
    if tab_title:
        if not results:
            return None
        best_match = None
        best_ratio = 0.0
        for url, title, visit_time in results:
            if title:
                ratio = SequenceMatcher(None, tab_title.lower(), title.lower()).ratio()
                if ratio > best_ratio:
                    best_ratio = ratio
                    best_match = url
        if best_match and best_ratio >= 0.8:
            logger.info(
                "Found Chrome URL for tab '%s': %s (similarity: %s)",
                tab_title,
                best_match,
                best_ratio,
            )
            return best_match
        url, title, visit_time = results[0]
        logger.info("Chrome: no title match, using most recent URL: %s", url)
        return url
    if not result:
        return None
    url, title, visit_time = result
    logger.debug("Found most recent Chrome URL: %s", url)
    return url


def _pick_firefox_url_from_rows(results, result, tab_title):
    """Map Firefox places+historyvisits rows to a single URL."""
    if tab_title:
        if not results:
            return None
        best_match = None
        best_ratio = 0.0
        for url, title, visit_date in results:
            if title:
                ratio = SequenceMatcher(None, tab_title.lower(), title.lower()).ratio()
                if ratio > best_ratio:
                    best_ratio = ratio
                    best_match = url
        if best_match and best_ratio >= 0.8:
            logger.info(
                "Found Firefox URL for tab '%s': %s (similarity: %s)",
                tab_title,
                best_match,
                best_ratio,
            )
            return best_match
        url, title, visit_date = results[0]
        logger.info("Firefox: no title match, using most recent URL: %s", url)
        return url
    if not result:
        return None
    url, title, visit_date = result
    logger.info("Found most recent Firefox URL: %s", url)
    return url


def _try_chrome_history_readonly_immutable(db_path, tab_title=None, limit=50):
    """
    Read Chrome/WebKit History without SQLite backup(). Uses URI immutable=1 so SQLite avoids
    long exclusive coordination with a live Chrome writer (may see a slightly stale snapshot).
    """
    conn = None
    try:
        uri = Path(db_path).resolve().as_uri() + "?mode=ro&immutable=1"
        conn = sqlite3.connect(
            uri,
            uri=True,
            timeout=_BROWSER_DB_IMMUTABLE_CONNECT_TIMEOUT_SEC,
            isolation_level=None,
        )
        cur = conn.cursor()
        if tab_title:
            cur.execute(
                """
                SELECT urls.url, urls.title, visits.visit_time
                FROM urls
                JOIN visits ON urls.id = visits.url
                WHERE urls.title IS NOT NULL AND urls.title != ''
                ORDER BY visits.visit_time DESC
                LIMIT ?
                """,
                (limit,),
            )
            rows = cur.fetchall()
            return _pick_chrome_url_from_rows(rows, None, tab_title)
        cur.execute(
            """
            SELECT urls.url, urls.title, visits.visit_time
            FROM urls
            JOIN visits ON urls.id = visits.url
            WHERE urls.url IS NOT NULL AND urls.url != ''
            ORDER BY visits.visit_time DESC
            LIMIT 1
            """
        )
        row = cur.fetchone()
        return _pick_chrome_url_from_rows(None, row, None)
    except (sqlite3.OperationalError, sqlite3.DatabaseError, OSError, ValueError, TypeError) as e:
        logger.debug(
            "Chrome read-only immutable open skipped (%s): %s",
            type(e).__name__,
            e,
        )
        return None
    finally:
        if conn:
            conn.close()


def _try_firefox_places_readonly_immutable(db_path, tab_title=None, limit=50):
    """Same as Chrome immutable path for Firefox places.sqlite."""
    conn = None
    try:
        uri = Path(db_path).resolve().as_uri() + "?mode=ro&immutable=1"
        conn = sqlite3.connect(
            uri,
            uri=True,
            timeout=_BROWSER_DB_IMMUTABLE_CONNECT_TIMEOUT_SEC,
            isolation_level=None,
        )
        cur = conn.cursor()
        if tab_title:
            cur.execute(
                """
                SELECT url, title, visit_date
                FROM moz_places
                JOIN moz_historyvisits ON moz_places.id = moz_historyvisits.place_id
                WHERE title IS NOT NULL AND title != ''
                ORDER BY visit_date DESC
                LIMIT ?
                """,
                (limit,),
            )
            rows = cur.fetchall()
            return _pick_firefox_url_from_rows(rows, None, tab_title)
        cur.execute(
            """
            SELECT url, title, visit_date
            FROM moz_places
            JOIN moz_historyvisits ON moz_places.id = moz_historyvisits.place_id
            WHERE url IS NOT NULL AND url != ''
            ORDER BY visit_date DESC
            LIMIT 1
            """
        )
        row = cur.fetchone()
        return _pick_firefox_url_from_rows(None, row, None)
    except (sqlite3.OperationalError, sqlite3.DatabaseError, OSError, ValueError, TypeError) as e:
        logger.debug(
            "Firefox read-only immutable open skipped (%s): %s",
            type(e).__name__,
            e,
        )
        return None
    finally:
        if conn:
            conn.close()


def _get_firefox_url_with_copy(db_path, tab_title=None, limit=50):
    """
    Copy Firefox places DB to a temp file via SQLite backup, query the copy, then unlink the
    temp file immediately after the connection closes (finally only if an error left it behind).
    """
    import time

    temp_db = None
    try:
        db_path = _normalize_browser_db_path(db_path)
        if not os.path.isfile(db_path):
            return None
        temp_dir = get_browser_temp_directory()
        temp_db = os.path.join(
            temp_dir, f"firefox_places_temp_{int(time.time() * 1000000)}_{os.getpid()}.db"
        )
        _src = sqlite3.connect(db_path, timeout=30)
        try:
            _dst = sqlite3.connect(temp_db, timeout=30)
            try:
                try:
                    # sleep=0 avoids default 0.25s pauses between page batches (Python 3.7+).
                    _src.backup(_dst, sleep=0)
                except TypeError:
                    _src.backup(_dst)
            finally:
                _dst.close()
        finally:
            _src.close()
        logger.info("Firefox database copied to: %s", temp_db)

        results = None
        result = None
        conn = sqlite3.connect(temp_db, timeout=5)
        try:
            cursor = conn.cursor()
            if tab_title:
                cursor.execute("""
                    SELECT url, title, visit_date
                    FROM moz_places
                    JOIN moz_historyvisits ON moz_places.id = moz_historyvisits.place_id
                    WHERE title IS NOT NULL AND title != ''
                    ORDER BY visit_date DESC
                    LIMIT ?
                """, (limit,))
                results = cursor.fetchall()
            else:
                cursor.execute("""
                    SELECT url, title, visit_date
                    FROM moz_places
                    JOIN moz_historyvisits ON moz_places.id = moz_historyvisits.place_id
                    WHERE url IS NOT NULL AND url != ''
                    ORDER BY visit_date DESC
                    LIMIT 1
                """)
                result = cursor.fetchone()
        finally:
            conn.close()

        # Drop temp copy immediately after read while rows are already in memory
        _unlink_browser_temp_copy(temp_db)
        temp_db = None

        return _pick_firefox_url_from_rows(results, result, tab_title)

    except Exception as e:
        logger.info("Error accessing Firefox database: %s", e)
        return None
    finally:
        if temp_db and os.path.isfile(temp_db):
            _unlink_browser_temp_copy(temp_db)


def get_chrome_url_from_db(db_path, tab_title=None, limit=50):
    """
    Chrome/WebKit History URL: try read-only snapshot (immutable URI) first to avoid long
    SQLite backup() while Chrome holds the DB; if that fails, backup to temp, query, delete copy.
    """
    norm = _normalize_browser_db_path(db_path)
    if not os.path.isfile(norm):
        return None
    url = _try_chrome_history_readonly_immutable(norm, tab_title, limit)
    if url:
        logger.debug("Chrome URL from read-only snapshot (immutable=1); skipped SQLite backup")
        return url
    logger.info("Chrome immutable read missed URL or failed; using SQLite backup copy path")
    return _get_chrome_url_with_copy(norm, tab_title, limit)


def _get_chrome_url_with_copy(db_path, tab_title=None, limit=50):
    """
    Copy Chrome History to a temp file via SQLite backup, query the copy, then unlink the
    temp file immediately after the connection closes (finally only if an error left it behind).
    """
    import time

    temp_db = None
    try:
        db_path = _normalize_browser_db_path(db_path)
        if not os.path.isfile(db_path):
            return None
        temp_dir = get_browser_temp_directory()
        temp_db = os.path.join(
            temp_dir, f"chrome_history_temp_{int(time.time() * 1000000)}_{os.getpid()}.db"
        )
        _src = sqlite3.connect(db_path, timeout=30)
        try:
            _dst = sqlite3.connect(temp_db, timeout=30)
            try:
                try:
                    # sleep=0 avoids default 0.25s pauses between page batches (Python 3.7+).
                    _src.backup(_dst, sleep=0)
                except TypeError:
                    _src.backup(_dst)
            finally:
                _dst.close()
        finally:
            _src.close()
        logger.info("Chrome database copied to: %s", temp_db)

        results = None
        result = None
        conn = sqlite3.connect(temp_db, timeout=5)
        try:
            cursor = conn.cursor()
            if tab_title:
                cursor.execute("""
                    SELECT urls.url, urls.title, visits.visit_time
                    FROM urls
                    JOIN visits ON urls.id = visits.url
                    WHERE urls.title IS NOT NULL AND urls.title != ''
                    ORDER BY visits.visit_time DESC
                    LIMIT ?
                """, (limit,))
                results = cursor.fetchall()
            else:
                cursor.execute("""
                    SELECT urls.url, urls.title, visits.visit_time
                    FROM urls
                    JOIN visits ON urls.id = visits.url
                    WHERE urls.url IS NOT NULL AND urls.url != ''
                    ORDER BY visits.visit_time DESC
                    LIMIT 1
                """)
                result = cursor.fetchone()
        finally:
            conn.close()

        # Drop temp copy immediately after read while rows are already in memory
        _unlink_browser_temp_copy(temp_db)
        temp_db = None

        if tab_title:
            if not results:
                return None
            best_match = None
            best_ratio = 0.0
            for url, title, visit_time in results:
                if title:
                    ratio = SequenceMatcher(None, tab_title.lower(), title.lower()).ratio()
                    if ratio > best_ratio:
                        best_ratio = ratio
                        best_match = url
            if best_match and best_ratio >= 0.8:
                logger.info(
                    "Found Chrome URL for tab '%s': %s (similarity: %s)",
                    tab_title,
                    best_match,
                    best_ratio,
                )
                return best_match
            url, title, visit_time = results[0]
            logger.info("Chrome: no title match, using most recent URL: %s", url)
            return url

        if not result:
            return None
        url, title, visit_time = result
        logger.debug("Found most recent Chrome URL: %s", url)
        return url

    except Exception as e:
        logger.info("Error accessing Chrome database: %s", e)
        return None
    finally:
        if temp_db and os.path.isfile(temp_db):
            _unlink_browser_temp_copy(temp_db)


async def get_browser_url_with_fallback(tab_title=None):
    """
    URL detection via the active browser's history database.

    Args:
        tab_title: Tab title to match (optional)

    Returns:
        str: URL if found, None otherwise
    """
    try:
        # Step 1: Try to get URL from browser database
        logger.debug("=== Attempting browser database URL detection ===")
        
        # Get active window info to determine browser
        app_name, pid = get_active_window_info()
        
        if not app_name:
            logger.info("Could not determine active application")
            return None
        
        # Get all browser paths for current system
        all_browser_paths = get_browser_db_paths()
        
        # Check if the active app is a supported browser
        browser_name = None
        for browser in all_browser_paths.keys():
            if browser in app_name or app_name in browser:
                browser_name = browser
                break
        
        if browser_name:
            logger.info("Detected browser: %s", browser_name)
            
            # Try to get URL from browser database
            db_url = get_url_from_browser_database(browser_name, tab_title)
            
            if db_url and db_url.strip():
                logger.info(" Successfully retrieved URL from %s database: %s", browser_name, db_url)
                return db_url.strip()
            else:
                logger.info(" No URL found in %s database", browser_name)
        else:
            logger.info("Active app '%s' is not a recognized browser", app_name)
        
        logger.info(" Browser database URL detection found no result")
        return None
        
    except Exception as e:
        logger.info("Error in comprehensive URL detection: %s", e)
        return None

def get_all_browser_databases():
    """
    Get database paths for all installed browsers on the current system.
    Returns:
        dict: Dictionary mapping browser names to their database paths
    """
    all_browser_paths = get_browser_db_paths()
    all_databases = {}
    
    for browser_name, browser_paths in all_browser_paths.items():
        db_paths = find_browser_databases(browser_name, browser_paths)
        if db_paths:
            all_databases[browser_name] = db_paths
    
    return all_databases

def is_browser_running(browser_name):
    """
    Check if a specific browser is currently running.
    Args:
        browser_name: Name of the browser to check
    Returns:
        bool: True if browser is running, False otherwise
    """
    try:
        for proc in psutil.process_iter(['pid', 'name']):
            if browser_name.lower() in proc.info['name'].lower():
                return True
        return False
    except Exception:
        return False

def get_running_browsers():
    """
    Get list of currently running browsers.
    Returns:
        list: List of browser names that are currently running
    """
    all_browser_paths = get_browser_db_paths()
    running_browsers = []
    
    for browser_name in all_browser_paths.keys():
        if is_browser_running(browser_name):
            running_browsers.append(browser_name)
    
    return running_browsers

def inspect_temp_databases():
    """
    Helper function to inspect temporary database files that were kept for debugging.
    """
    import os
    import sqlite3
    import glob
    
    # Look for temp files with both old and new naming patterns
    temp_dir = get_browser_temp_directory()
    temp_files = []
    temp_files.extend(glob.glob(os.path.join(temp_dir, 'firefox_places_temp*.db')))
    temp_files.extend(glob.glob(os.path.join(temp_dir, 'chrome_history_temp*.db')))
    
    logger.info("=== Temporary Database Inspection ===")
    logger.info("Temp directory: %s", temp_dir)
    logger.info()
    
    for temp_file in temp_files:
        if os.path.exists(temp_file):
            file_size = os.path.getsize(temp_file)
            logger.info(" Found: %s", temp_file)
            logger.info("  Size: %s bytes (%s MB)", file_size, file_size/1024/1024)
            
            conn = None
            try:
                conn = sqlite3.connect(temp_file, timeout=5)
                cursor = conn.cursor()

                if 'firefox' in temp_file:
                    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
                    tables = cursor.fetchall()
                    logger.info("  Tables: %s", [table[0] for table in tables])

                    cursor.execute("SELECT COUNT(*) FROM moz_places")
                    places_count = cursor.fetchone()[0]
                    logger.info("  Places entries: %s", places_count)

                    cursor.execute("SELECT COUNT(*) FROM moz_historyvisits")
                    visits_count = cursor.fetchone()[0]
                    logger.info("  History visits: %s", visits_count)

                elif 'chrome' in temp_file:
                    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
                    tables = cursor.fetchall()
                    logger.info("  Tables: %s", [table[0] for table in tables])

                    cursor.execute("SELECT COUNT(*) FROM urls")
                    urls_count = cursor.fetchone()[0]
                    logger.info("  URLs entries: %s", urls_count)

                    cursor.execute("SELECT COUNT(*) FROM visits")
                    visits_count = cursor.fetchone()[0]
                    logger.info("  Visits entries: %s", visits_count)

                logger.info()

            except Exception as e:
                logger.warning("  Error inspecting database: %s", e)
                logger.info()
            finally:
                if conn:
                    conn.close()
        else:
            logger.info(" Not found: %s", temp_file)
            logger.info()
    
    logger.info("=== End Inspection ===")

def debug_browser_detection():
    """
    Debug function to help troubleshoot browser detection issues.
    """
    logger.info("=== Browser Detection Debug ===")
    logger.info("Platform: %s %s", platform.system(), platform.release())
    
    # Check active window
    app_name, pid = get_active_window_info()
    logger.info("Active application: %s (PID: %s)", app_name, pid)
    
    # Check all browser paths
    all_browser_paths = get_browser_db_paths()
    logger.info("\nSupported browsers: %s", list(all_browser_paths.keys()))
    
    # Check which paths exist
    for browser, paths in all_browser_paths.items():
        logger.info("\n%s:", browser)
        for path in paths:
            exists = os.path.exists(path)
            logger.info("  %s: %s", path, 'EXISTS' if exists else 'NOT FOUND')
            if exists:
                try:
                    contents = os.listdir(path)
                    logger.info("    Contents: %s%s", contents, '...' if len(contents) > 5 else '')
                except Exception as e:
                    logger.info("    Error reading directory: %s", e)
    
    # Check running browsers
    running_browsers = get_running_browsers()
    logger.info("\nRunning browsers: %s", running_browsers)
    
    logger.info("=== End Debug ===")

def debug_url_fetch_failure(tab_title=None, browser_name=None):
    """
    Comprehensive debugging function to identify why URL fetching fails from database.
    
    Args:
        tab_title: Tab title to test matching
        browser_name: Specific browser to test (optional)
    """
    logger.info("=== URL Fetch Failure Debug ===")
    logger.info("Testing tab title: '%s'", tab_title)
    logger.info("Target browser: %s", browser_name or 'Auto-detect')
    logger.info()
    
    # Step 1: Check active window detection
    logger.info("1. ACTIVE WINDOW DETECTION:")
    app_name, pid = get_active_window_info()
    if app_name:
        logger.info("    Active app detected: %s (PID: %s)", app_name, pid)
    else:
        logger.info("    Could not detect active application")
        logger.info("   → This will cause URL fetch failure")
        return
    
    # Step 2: Check browser detection
    logger.info("\n2. BROWSER DETECTION:")
    all_browser_paths = get_browser_db_paths()
    detected_browser = None
    
    for browser in all_browser_paths.keys():
        if browser in app_name or app_name in browser:
            detected_browser = browser
            break
    
    if detected_browser:
        logger.info("    Browser detected: %s", detected_browser)
        if browser_name and detected_browser != browser_name:
            logger.info("   ⚠ Warning: Expected '%s' but detected '%s'", browser_name, detected_browser)
    else:
        logger.info("    Active app '%s' is not a recognized browser", app_name)
        logger.info("   → Supported browsers:", list(all_browser_paths.keys()))
        logger.info("   → This will cause URL fetch failure")
        return
    
    # Step 3: Check database file existence
    logger.info("\n3. DATABASE FILE EXISTENCE (%s):", detected_browser)
    browser_paths = all_browser_paths[detected_browser]
    db_paths = find_browser_databases(detected_browser, browser_paths)
    
    if db_paths:
        logger.info("    Found %s database file(s):", len(db_paths))
        for i, db_path in enumerate(db_paths, 1):
            logger.info("     %s. %s", i, db_path)
            
            # Check file permissions
            try:
                if os.access(db_path, os.R_OK):
                    logger.info("         Readable")
                else:
                    logger.info("         Not readable (permission denied)")
            except Exception as e:
                logger.info("         Access error: %s", e)
    else:
        logger.info("    No database files found")
        logger.info("   → This will cause URL fetch failure")
        return
    
    # Step 4: Test database content
    logger.info("\n4. DATABASE CONTENT ANALYSIS:")
    for i, db_path in enumerate(db_paths, 1):
        logger.info("\n   Testing database %s: %s", i, os.path.basename(db_path))
        
        try:
            # Create temporary copy
            temp_dir = get_browser_temp_directory()
            temp_db = os.path.join(temp_dir, f'debug_temp_{i}.db')
            # Use SQLite backup API — handles WAL files; shutil.copy2 causes "malformed" errors
            _src = sqlite3.connect(db_path, timeout=5)
            try:
                _dst = sqlite3.connect(temp_db, timeout=5)
                try:
                    _src.backup(_dst)
                finally:
                    _dst.close()
            finally:
                _src.close()
            logger.info("    Database copied to: %s", temp_db)
            
            conn = sqlite3.connect(temp_db, timeout=5)
            cursor = conn.cursor()

            if detected_browser == "firefox":
                # Firefox analysis
                cursor.execute("SELECT COUNT(*) FROM moz_places")
                places_count = cursor.fetchone()[0]
                logger.info("    Places entries: %s", places_count)
                
                cursor.execute("SELECT COUNT(*) FROM moz_historyvisits")
                visits_count = cursor.fetchone()[0]
                logger.info("    History visits: %s", visits_count)
                
                if places_count == 0:
                    logger.info("    No places entries - empty history")
                elif visits_count == 0:
                    logger.info("    No visit entries - empty history")
                else:
                    logger.info("    Database has content")
                    
                    # Test tab title matching
                    if tab_title:
                        logger.info("\n   Testing tab title matching for: '%s'", tab_title)
                        cursor.execute("""
                            SELECT url, title, visit_date 
                            FROM moz_places 
                            JOIN moz_historyvisits ON moz_places.id = moz_historyvisits.place_id
                            WHERE title IS NOT NULL AND title != ''
                            ORDER BY visit_date DESC 
                            LIMIT 10
                        """)
                        
                        results = cursor.fetchall()
                        logger.info("   Found %s recent entries with titles", len(results))
                        
                        if results:
                            logger.info("   Recent titles:")
                            for j, (url, title, visit_date) in enumerate(results[:5], 1):
                                logger.info("     %s. '%s' -> %s", j, title, url)
                            
                            # Test similarity matching
                            logger.info("\n   Testing similarity matching:")
                            best_match = None
                            best_ratio = 0.0
                            
                            for url, title, visit_date in results:
                                if title:
                                    ratio = SequenceMatcher(None, tab_title.lower(), title.lower()).ratio()
                                    if ratio > best_ratio:
                                        best_ratio = ratio
                                        best_match = url
                                    logger.info("     '%s' -> similarity: %s", title, ratio)
                            
                            logger.info("\n   Best match: %s (similarity: %s)", best_match, best_ratio)
                            if best_ratio >= 0.8:
                                logger.info("    Would find match (threshold: 0.8)")
                            else:
                                logger.info("    No match found (threshold: 0.8)")
                                logger.info("   → Consider lowering threshold or improving tab title")
                        else:
                            logger.info("    No entries with titles found")
                            
            else:
                # Chrome analysis
                cursor.execute("SELECT COUNT(*) FROM urls")
                urls_count = cursor.fetchone()[0]
                logger.info("    URLs entries: %s", urls_count)
                
                cursor.execute("SELECT COUNT(*) FROM visits")
                visits_count = cursor.fetchone()[0]
                logger.info("    Visits entries: %s", visits_count)
                
                if urls_count == 0:
                    logger.info("    No URL entries - empty history")
                elif visits_count == 0:
                    logger.info("    No visit entries - empty history")
                else:
                    logger.info("    Database has content")
                    
                    # Test tab title matching
                    if tab_title:
                        logger.info("\n   Testing tab title matching for: '%s'", tab_title)
                        cursor.execute("""
                            SELECT urls.url, urls.title, visits.visit_time
                            FROM urls
                            JOIN visits ON urls.id = visits.url
                            WHERE urls.title IS NOT NULL AND urls.title != ''
                            ORDER BY visits.visit_time DESC
                            LIMIT 10
                        """)
                        
                        results = cursor.fetchall()
                        logger.info("   Found %s recent entries with titles", len(results))
                        
                        if results:
                            logger.info("   Recent titles:")
                            for j, (url, title, visit_time) in enumerate(results[:5], 1):
                                logger.info("     %s. '%s' -> %s", j, title, url)
                            
                            # Test similarity matching
                            logger.info("\n   Testing similarity matching:")
                            best_match = None
                            best_ratio = 0.0
                            
                            for url, title, visit_time in results:
                                if title:
                                    ratio = SequenceMatcher(None, tab_title.lower(), title.lower()).ratio()
                                    if ratio > best_ratio:
                                        best_ratio = ratio
                                        best_match = url
                                    logger.info("     '%s' -> similarity: %s", title, ratio)
                            
                            logger.info("\n   Best match: %s (similarity: %s)", best_match, best_ratio)
                            if best_ratio >= 0.8:
                                logger.info("    Would find match (threshold: 0.8)")
                            else:
                                logger.info("    No match found (threshold: 0.8)")
                                logger.info("   → Consider lowering threshold or improving tab title")
                        else:
                            logger.info("    No entries with titles found")
            
            conn.close()
            conn = None

            # Clean up temp file
            if os.path.exists(temp_db):
                os.remove(temp_db)

        except Exception as e:
            if 'conn' in locals() and conn:
                conn.close()
            logger.warning("    Database analysis failed: %s", e)
            logger.info("   → This will cause URL fetch failure")
    
    # Step 5: Summary and recommendations
    logger.info("\n5. SUMMARY AND RECOMMENDATIONS:")
    logger.info("   Common reasons for URL fetch failure:")
    logger.info("   • Tab title doesn't match any history entries (similarity < 0.8)")
    logger.info("   • Browser history is empty or very limited")
    logger.info("   • Wrong browser profile detected")
    logger.info("   • Database file is locked or inaccessible")
    logger.info("   • Active window is not a recognized browser")
    logger.info("   • Tab title is too generic or dynamic")
    
    logger.info("\n   Solutions to try:")
    logger.info("   • Lower similarity threshold from 0.8 to 0.6")
    logger.info("   • Use more specific tab titles")
    logger.info("   • Check if browser is actually active")
    logger.info("   • Verify browser history has recent entries")
    logger.info("   • Test with different browser profiles")
    
    logger.info("=== End URL Fetch Debug ===")

def test_url_fetch_with_specific_browser(tab_title, browser_name):
    """
    Test URL fetching with a specific browser (for debugging purposes).
    
    Args:
        tab_title: Tab title to test
        browser_name: Browser to test (chrome, firefox, etc.)
    """
    logger.info("=== Testing URL Fetch with %s ===", browser_name.upper())
    logger.info("Tab title: '%s'", tab_title)
    logger.info()
    
    # Get browser database paths
    all_browser_paths = get_browser_db_paths()
    
    if browser_name not in all_browser_paths:
        logger.info(" Browser '%s' not supported", browser_name)
        logger.info("Supported browsers: %s", list(all_browser_paths.keys()))
        return
    
    browser_paths = all_browser_paths[browser_name]
    db_paths = find_browser_databases(browser_name, browser_paths)
    
    if not db_paths:
        logger.info(" No database files found for %s", browser_name)
        return
    
    logger.info(" Found %s database file(s) for %s", len(db_paths), browser_name)
    
    # Test each database
    for i, db_path in enumerate(db_paths, 1):
        logger.info("\n--- Testing Database %s: %s ---", i, os.path.basename(db_path))
        
        try:
            if browser_name == "firefox":
                url = get_firefox_url_from_db(db_path, tab_title)
            else:
                url = get_chrome_url_from_db(db_path, tab_title)
            
            if url:
                logger.info(" SUCCESS: Found URL -> %s", url)
            else:
                logger.info(" FAILED: No URL found for '%s'", tab_title)
                
        except Exception as e:
            logger.info(" ERROR: %s", e)
    
    logger.info("\n=== End %s Test ===", browser_name.upper())

def get_active_browser_db_paths():
    """
    Detect the active browser window and return its history database path(s)
    for all profiles under that browser.
    Returns:
        list: List of paths to browser history databases (.sqlite or .db)
    """
    # Get active window info
    app_name, pid = get_active_window_info()
    
    if not app_name:
        logger.info("Could not determine active application")
        return []

    # Get all browser paths for current system
    all_browser_paths = get_browser_db_paths()
    
    # Check if the active app is a supported browser
    browser_name = None
    for browser in all_browser_paths.keys():
        if browser in app_name or app_name in browser:
            browser_name = browser
            break
    
    if not browser_name:
        logger.info("Active app '%s' is not a recognized browser", app_name)
        return []

    logger.info("Detected active browser: %s", browser_name)
    
    # Find database files for the active browser
    db_paths = find_browser_databases(browser_name, all_browser_paths[browser_name])

    return db_paths

def cleanup_old_temp_files(max_age_seconds=300):
    """
    Clean up old temporary database files to prevent disk space accumulation.
    
    Args:
        max_age_seconds: Maximum age of temp files in seconds (default: 5 minutes)
    """
    try:
        import glob
        import time
        
        temp_dir = get_browser_temp_directory()
        temp_patterns = [
            os.path.join(temp_dir, "chrome_history_temp*.db"),
            os.path.join(temp_dir, "firefox_places_temp*.db")
        ]
        
        current_time = time.time()
        cleaned_count = 0
        
        for pattern in temp_patterns:
            temp_files = glob.glob(pattern)
            for temp_file in temp_files:
                if os.path.exists(temp_file):
                    file_age = current_time - os.path.getmtime(temp_file)
                    if file_age > max_age_seconds:
                        try:
                            os.remove(temp_file)
                            cleaned_count += 1
                            logger.info("Cleaned up old temp file: %s (age: %ss)", os.path.basename(temp_file), file_age)
                        except Exception as e:
                            logger.warning("Could not remove temp file %s: %s", temp_file, e)
        
        if cleaned_count > 0:
            logger.info("Cleaned up %s old temporary database files", cleaned_count)
            
    except Exception as e:
        logger.error("Error during temp file cleanup: %s", e)

async def main():
    """
    Main function to demonstrate the browser database detection functionality.
    """
    import sys
    
    # Check for debug flag
    if "--debug" in sys.argv:
        debug_browser_detection()
        return
    
    # Check for inspect flag
    if "--inspect" in sys.argv:
        inspect_temp_databases()
        return
    
    # Check for cleanup flag
    if "--cleanup" in sys.argv:
        cleanup_old_temp_files()
        return
    
    logger.info("Running on %s %s", platform.system(), platform.release())
    logger.info("=" * 50)
    
    # Show all available browsers
    logger.info("Available browsers:")
    all_databases = get_all_browser_databases()
    for browser, paths in all_databases.items():
        logger.info("  %s: %s database(s) found", browser, len(paths))
    
    logger.info("\nRunning browsers:")
    running_browsers = get_running_browsers()
    for browser in running_browsers:
        logger.info("  %s", browser)
    
    logger.info("\nActive browser URL detection:")
    logger.info("Press Ctrl+C to stop...")
    logger.info("Use --debug flag for detailed troubleshooting")
    logger.info("Use --inspect flag to inspect temporary database files")
    
    try:
        while True:
            time.sleep(5)
            
            # Test the new comprehensive URL detection
            logger.info("\n" + "="*60)
            logger.info("Testing comprehensive URL detection...")
            
            url = await get_browser_url_with_fallback()
            
            if url:
                logger.info(" Final URL result: %s", url)
            else:
                logger.info(" No URL could be detected")
            
            logger.info("-" * 30)
    except KeyboardInterrupt:
        logger.info("\nStopped by user")

if __name__ == "__main__":
    import asyncio
    asyncio.run(main())
