"""
Centralized Database Lock Manager
Resolves database lock contention and deadlock issues
"""

import threading
import time
import traceback
from typing import Optional, Dict, Any
from contextlib import contextmanager
from ws_package.log.writer import LogWriter
import sqlite3

logger = LogWriter.file(__name__)

class DatabaseLockManager:
    """
    Centralized lock manager to prevent deadlocks and improve concurrency
    """
    
    def __init__(self):
        # Proper reader-writer lock implementation
        # Use Lock to protect the internal state
        self._readers_lock = threading.Lock()
        # Semaphore to allow multiple readers
        self._read_semaphore = threading.Semaphore(0)
        # Lock for write operations (exclusive access)
        self._write_lock = threading.Lock()
        # Track active operations for debugging
        self._active_readers = 0
        self._active_writers = 0
        self._waiting_readers = 0
        self._waiting_writers = 0
        # Track which threads hold locks to prevent deadlocks
        self._readers = set()
        # Track reader acquisition times for stuck reader detection
        self._reader_acquire_times = {}  # thread_id -> acquisition_time
        # Track reader thread info for debugging
        self._reader_thread_info = {}  # thread_id -> thread_info
        # Maximum time a reader can hold a lock (seconds) - prevents infinite holds
        # Reduced to 30s to force-release before write timeout (60s) occurs
        self._max_read_lock_hold_time = 30.0
        # Lock ordering to prevent deadlocks
        self._lock_order = {
            'read': 1,
            'write': 2
        }
        self._thread_locks = {}  # Track locks held by each thread
        # Initialize lock statistics
        self._lock_stats = {
            'read_waits': 0,
            'write_waits': 0,
            'deadlock_preventions': 0,
            'stuck_readers_detected': 0
        }
    
    def _check_lock_order(self, lock_type: str) -> bool:
        """
        Check if acquiring this lock would violate lock ordering
        Returns True if safe to acquire, False if potential deadlock
        """
        current_thread = threading.current_thread()
        thread_id = id(current_thread)
        
        if thread_id not in self._thread_locks:
            self._thread_locks[thread_id] = []
        
        current_order = self._lock_order.get(lock_type, 999)
        held_orders = [self._lock_order.get(lt, 999) for lt in self._thread_locks[thread_id]]

        # Prevent acquiring a lower-order lock while holding higher-order locks
        # Example: holding 'write'(2) then trying to acquire 'read'(1) should be blocked.
        if held_orders and max(held_orders) > current_order:
            logger.warning("Potential deadlock prevented: trying to acquire %s lock while holding higher-order locks", lock_type)
            return False
        
        return True
    
    def _register_lock(self, lock_type: str):
        """Register that current thread is holding a lock"""
        current_thread = threading.current_thread()
        thread_id = id(current_thread)
        
        if thread_id not in self._thread_locks:
            self._thread_locks[thread_id] = []
        
        self._thread_locks[thread_id].append(lock_type)
    
    def _unregister_lock(self, lock_type: str):
        """Unregister that current thread is no longer holding a lock"""
        current_thread = threading.current_thread()
        thread_id = id(current_thread)
        
        if thread_id in self._thread_locks:
            try:
                self._thread_locks[thread_id].remove(lock_type)
                if not self._thread_locks[thread_id]:
                    del self._thread_locks[thread_id]
            except ValueError:
                pass  # Lock type not in list
    
    @contextmanager
    def read_lock(self, timeout: float = 30.0):
        """
        Acquire read lock for database read operations
        Multiple readers can access simultaneously
        
        CRITICAL FIX: Added watchdog timer to ensure lock is released even if async operations hang.
        This prevents stuck readers when async operations inside the context manager hang indefinitely.
        """
        start_time = time.time()
        thread = threading.current_thread()
        thread_id = id(thread)
        acquired = False
        watchdog_timer = None
        # Maximum time a read lock can be held (safety mechanism for async hangs)
        max_lock_hold_time = 25.0  # Slightly less than default timeout to ensure cleanup
        
        def _force_release_lock():
            """Watchdog function to force-release lock if held too long (async hang detection)"""
            if thread_id in self._readers:
                thread_info = self._reader_thread_info.get(thread_id, {})
                thread_name = thread_info.get('thread_name', f'Thread-{thread_id}')
                thread_ident = thread_info.get('thread_ident', 'Unknown')
                acquire_time = self._reader_acquire_times.get(thread_id, time.time())
                hold_time = time.time() - acquire_time
                
                logger.error(
                    f"FORCE-RELEASING stuck read lock: Thread {thread_name} (ID: {thread_ident}, Object ID: {thread_id}) "
                    f"held lock for {hold_time:.1f}s (>{max_lock_hold_time}s). "
                    f"Possible async operation hang detected."
                )
                with self._readers_lock:
                    if thread_id in self._readers:
                        self._active_readers = max(0, self._active_readers - 1)
                        self._readers.discard(thread_id)
                        self._reader_acquire_times.pop(thread_id, None)
                        self._reader_thread_info.pop(thread_id, None)
                        if thread_id in self._thread_locks:
                            self._thread_locks[thread_id] = [lt for lt in self._thread_locks[thread_id] if lt != 'read']
                            if not self._thread_locks[thread_id]:
                                del self._thread_locks[thread_id]
                        
                        # If this was the last reader and there are waiting writers, wake them
                        if self._active_readers == 0 and self._waiting_writers > 0:
                            self._read_semaphore.release()
                        
                        logger.warning("Force-released stuck read lock for thread %s (ID: %s)", thread_name, thread_ident)
        
        # Initialize watchdog timer to None to ensure it's always defined in finally block
        watchdog_timer = None
        try:
            # If the current thread already holds a write lock, a read is implicitly allowed.
            current_locks = self._thread_locks.get(thread_id, [])
            if 'write' in current_locks:
                logger.debug("Read lock elided: thread already holds write lock")
                yield
                return
            
            # CRITICAL FIX: Handle re-entrant read locks
            # If thread already holds a read lock, just register another one but don't increment active_readers
            # This ensures active_readers tracks number of THREADS, not number of LOCKS
            if 'read' in current_locks:
                self._register_lock('read')
                try:
                    yield
                finally:
                    self._unregister_lock('read')
                return
            
            # Check lock ordering to prevent deadlocks
            if not self._check_lock_order('read'):
                self._lock_stats['deadlock_preventions'] += 1
                raise RuntimeError("Lock ordering violation prevented - potential deadlock")
            
            # Acquire readers lock to modify state
            with self._readers_lock:
                # Writer priority: if writers are waiting, block new readers
                # This prevents writer starvation
                if self._active_writers == 0 and self._waiting_writers == 0:
                    # Safety check: if we have too many readers, something is wrong
                    # Lowered threshold from 100 to 50 to catch issues earlier
                    if self._active_readers > 50:
                        logger.error("CRITICAL: Abnormal number of active readers detected: %s. Possible lock leak!", self._active_readers)
                        # Force cleanup of very old readers (more aggressive threshold)
                        current_time = time.time()
                        cleaned = 0
                        for rid, acquire_time in list(self._reader_acquire_times.items()):
                            # More aggressive: clean up readers holding for > 30 seconds (instead of 120s)
                            if current_time - acquire_time > 30.0:
                                self._active_readers = max(0, self._active_readers - 1)
                                self._readers.discard(rid)
                                self._reader_acquire_times.pop(rid, None)
                                self._reader_thread_info.pop(rid, None)
                                if rid in self._thread_locks:
                                    self._thread_locks[rid] = [lt for lt in self._thread_locks[rid] if lt != 'read']
                                    if not self._thread_locks[rid]:
                                        del self._thread_locks[rid]
                                cleaned += 1
                        if cleaned > 0:
                            logger.warning("Emergency cleanup: Removed %s stale reader(s)", cleaned)
                    
                    self._active_readers += 1
                    self._readers.add(thread_id)
                    self._reader_acquire_times[thread_id] = time.time()
                    # Capture stack trace to help identify where the reader is stuck
                    stack_trace = ''.join(traceback.format_stack()[-5:-1])  # Last 4 frames (excluding current)
                    self._reader_thread_info[thread_id] = {
                        'thread_name': thread.name if hasattr(thread, 'name') and thread.name else f"Thread-{thread.ident}",
                        'thread_ident': thread.ident,
                        'acquire_time': time.time(),
                        'stack_trace': stack_trace  # Store stack trace for debugging
                    }
                    self._register_lock('read')
                    acquired = True
                    
                    # CRITICAL FIX: Start watchdog timer to force-release lock if async operation hangs
                    # This ensures the lock is released even if the async operation never completes
                    watchdog_timer = threading.Timer(max_lock_hold_time, _force_release_lock)
                    watchdog_timer.daemon = True  # Don't prevent program exit
                    watchdog_timer.start()
                    
                    logger.debug("Read lock acquired immediately. Active readers: %s", self._active_readers)
                else:
                    # Wait for writers to finish - writer priority ensures we don't starve writers
                    self._waiting_readers += 1
                    logger.debug("Read lock waiting: %s writer(s) waiting, %s writer(s) active", self._waiting_writers, self._active_writers)
            
            # Wait with timeout if not acquired immediately
            if not acquired:
                start_wait = time.time()
                # FIXED: Start with higher sleep interval to reduce initial CPU usage
                # Exponential backoff: 0.05s → 0.075s → 0.1125s → ... → 0.5s max
                sleep_interval = 0.05  # Start at 50ms instead of 10ms to reduce scheduler penalties
                idle_count = 0
                while not acquired:
                    with self._readers_lock:
                        # Check if writers have finished - writer priority
                        if self._active_writers == 0 and self._waiting_writers == 0:
                            if self._active_readers == 0:
                                # Signal waiting readers
                                for _ in range(min(self._waiting_readers, 100)):  # Limit to prevent all waking at once
                                    self._read_semaphore.release()
                            self._active_readers += 1
                            self._waiting_readers = max(0, self._waiting_readers - 1)
                            self._readers.add(thread_id)
                            self._reader_acquire_times[thread_id] = time.time()
                            # Capture stack trace to help identify where the reader is stuck
                            stack_trace = ''.join(traceback.format_stack()[-5:-1])  # Last 4 frames (excluding current)
                            self._reader_thread_info[thread_id] = {
                                'thread_name': thread.name if hasattr(thread, 'name') and thread.name else f"Thread-{thread.ident}",
                                'thread_ident': thread.ident,
                                'acquire_time': time.time(),
                                'stack_trace': stack_trace  # Store stack trace for debugging
                            }
                            self._register_lock('read')
                            acquired = True
                            
                            # CRITICAL FIX: Start watchdog timer after acquiring lock
                            watchdog_timer = threading.Timer(max_lock_hold_time, _force_release_lock)
                            watchdog_timer.daemon = True
                            watchdog_timer.start()
                            
                            logger.debug("Read lock acquired after wait. Active readers: %s", self._active_readers)
                            break
                    
                    # Check timeout
                    elapsed = time.time() - start_wait
                    if elapsed >= timeout:
                        self._lock_stats['read_waits'] += 1
                        logger.warning("Read lock timeout after %ss", elapsed)
                        logger.warning("Lock state: %s active writers, %s waiting writers, %s active readers", self._active_writers, self._waiting_writers, self._active_readers)
                        raise TimeoutError(f"Could not acquire read lock within {timeout} seconds")
                    
                    # Exponential backoff to reduce CPU usage during long waits
                    # FIXED: Better backoff curve - 0.05s → 0.075s → 0.1125s → ... → 0.5s max
                    idle_count += 1
                    sleep_interval = min(0.05 * (1.5 ** idle_count), 0.5)  # Cap at 500ms instead of 100ms
                    time.sleep(sleep_interval)
            
            # Perform the read operation
            # CRITICAL: Even if async operations hang here, the watchdog timer will force-release the lock
            yield
            
        except Exception as e:
            logger.error("Error in read lock context: %s", e)
            # Cleanup _waiting_readers if we incremented it but didn't acquire
            if not acquired:
                with self._readers_lock:
                    self._waiting_readers = max(0, self._waiting_readers - 1)
            raise
        finally:
            # CRITICAL FIX: Cancel watchdog timer if lock is released normally
            if watchdog_timer is not None:
                watchdog_timer.cancel()
            
            if acquired:
                with self._readers_lock:
                    # Double-check: only release if still in readers set (watchdog might have already released)
                    if thread_id in self._readers:
                        self._active_readers = max(0, self._active_readers - 1)
                        self._readers.discard(thread_id)
                        self._reader_acquire_times.pop(thread_id, None)  # Clean up acquisition time
                        self._reader_thread_info.pop(thread_id, None)  # Clean up thread info
                        self._unregister_lock('read')
                        
                        # If this was the last reader and there are waiting writers, wake them
                        if self._active_readers == 0 and self._waiting_writers > 0:
                            self._read_semaphore.release()
                        
                        logger.debug("Read lock released. Active readers: %s", self._active_readers)
                    else:
                        # Watchdog already released the lock
                        logger.debug("Read lock already force-released by watchdog for thread %s", thread_id)
    
    @contextmanager
    def write_lock(self, timeout: float = 60.0):
        """
        Acquire write lock for database write operations
        Exclusive access - no other operations allowed
        
        Args:
            timeout: Maximum time to wait for lock acquisition (default: 60.0 seconds)
        """
        start_time = time.time()
        acquired = False
        thread = threading.current_thread()
        thread_ident = thread.ident
        thread_key = id(thread)
        thread_name = thread.name if hasattr(thread, 'name') and thread.name else f"Thread-{thread_ident}"
        reentrant_write = False
        upgraded_from_read = False
        downgraded_read_was_last = False
        downgraded_read_count = 0
        
        try:
            # Fast-path: if this thread already holds write, make write_lock re-entrant (no-op body)
            current_locks = self._thread_locks.get(thread_key, [])
            if 'write' in current_locks:
                reentrant_write = True
                logger.debug("Thread %s entering re-entrant write lock", thread_ident)
                yield
                return

            # If holding a read lock, perform a safe, non-atomic upgrade by temporarily releasing one read
            # and restoring it after the write section completes. This avoids deadlock while preserving outer read context.
            if current_locks.count('read') > 0:
                with self._readers_lock:
                    # Downgrade all read locks held by this thread before upgrading to write
                    if thread_key in self._readers and self._active_readers > 0:
                        downgraded_read_count = current_locks.count('read')
                        # CRITICAL FIX: Only decrement by 1 since active_readers tracks threads, not locks
                        self._active_readers = max(0, self._active_readers - 1)
                        # Ensure we don't go negative
                        if self._active_readers < 0:
                            logger.warning("active_readers would be negative after downgrade, resetting to 0")
                            self._active_readers = 0
                        # Remove all 'read' entries from this thread's locks
                        self._thread_locks[thread_key] = [lt for lt in self._thread_locks[thread_key] if lt != 'read']
                        # Since no read remains, remove from readers set
                        self._readers.discard(thread_key)
                        downgraded_read_was_last = True
                        upgraded_from_read = True
                        logger.debug("Temporarily downgraded %s read lock(s) for upgrade to write lock", downgraded_read_count)

            # Normal ordering check (will pass after temporary downgrade if we were holding a read)
            if not self._check_lock_order('write'):
                self._lock_stats['deadlock_preventions'] += 1
                raise RuntimeError("Lock ordering violation prevented - potential deadlock")
            
            logger.debug("Thread %s (%s) attempting to acquire write lock (timeout: %ss)", thread_name, thread_ident, timeout)
            
            # Acquire write lock first
            acquired_lock = self._write_lock.acquire(timeout=min(timeout, 30.0))  # Cap at 30s for lock acquisition itself
            if not acquired_lock:
                self._lock_stats['write_waits'] += 1
                elapsed_time = time.time() - start_time
                with self._readers_lock:
                    stats = {
                        'active_readers': self._active_readers,
                        'active_writers': self._active_writers,
                        'waiting_writers': self._waiting_writers,
                        'waiting_readers': self._waiting_readers
                    }
                logger.error("Write lock acquisition failed after %ss (requested: %ss)", elapsed_time, timeout)
                logger.error("Lock state: %s", stats)
                raise TimeoutError(f"Could not acquire write lock within {timeout} seconds")
            
            # Mark that we're waiting for readers to finish
            # This blocks new readers from acquiring locks (writer priority)
            with self._readers_lock:
                self._waiting_writers += 1
                # Ensure waiting_writers never goes negative (safeguard against cleanup issues)
                if self._waiting_writers < 0:
                    logger.warning("waiting_writers was negative (%s), resetting to 0", self._waiting_writers)
                    self._waiting_writers = 0
                logger.debug("Writer %s waiting for %s active reader(s) to finish", thread_name, self._active_readers)
            
            # Wait for all readers to finish with better diagnostics
            wait_start = time.time()
            # FIXED: Start with higher sleep interval to reduce initial CPU usage
            sleep_interval = 0.05  # Start at 50ms instead of 10ms to reduce scheduler penalties
            idle_count = 0
            last_log_time = wait_start
            while True:
                with self._readers_lock:
                    # CRITICAL: Check for data inconsistency - active readers but no tracking
                    if self._active_readers > 0 and len(self._reader_acquire_times) == 0:
                        logger.error(
                            f"DATA INCONSISTENCY DETECTED: {self._active_readers} active reader(s) but no tracking data! "
                            f"Readers in _readers set: {list(self._readers)}. "
                            f"This suggests a reader was force-released but _active_readers wasn't decremented properly."
                        )
                        # Force-correct the inconsistency
                        self._active_readers = 0
                        self._readers.clear()
                        logger.warning("Force-corrected reader count inconsistency")
                    
                    if self._active_readers == 0:
                        self._active_writers += 1
                        self._waiting_writers = max(0, self._waiting_writers - 1)
                        self._register_lock('write')
                        acquired = True
                        logger.debug("Write lock acquired by %s. Active writers: %s", thread_name, self._active_writers)
                        break
                    
                    # Continuous monitoring for abnormal reader counts (prevents 134 readers issue)
                    if self._active_readers > 50:
                        logger.error("CRITICAL: Abnormal reader count (%s) detected during write wait. Emergency cleanup!", self._active_readers)
                        current_time = time.time()
                        cleaned = 0
                        for rid, acquire_time in list(self._reader_acquire_times.items()):
                            # Force-release any reader holding for > 20 seconds
                            if current_time - acquire_time > 20.0:
                                self._active_readers = max(0, self._active_readers - 1)
                                self._readers.discard(rid)
                                self._reader_acquire_times.pop(rid, None)
                                self._reader_thread_info.pop(rid, None)
                                if rid in self._thread_locks:
                                    self._thread_locks[rid] = [lt for lt in self._thread_locks[rid] if lt != 'read']
                                    if not self._thread_locks[rid]:
                                        del self._thread_locks[rid]
                                cleaned += 1
                        if cleaned > 0:
                            logger.error("EMERGENCY CLEANUP: Force-released %s stuck reader(s) during write wait", cleaned)
                        # After cleanup, check if we can acquire lock now
                        if self._active_readers == 0:
                            self._active_writers += 1
                            self._waiting_writers = max(0, self._waiting_writers - 1)
                            self._register_lock('write')
                            acquired = True
                            logger.warning("Write lock acquired after emergency cleanup!")
                            break
                    
                    # CRITICAL FIX: Check for stuck readers more frequently and aggressively
                    elapsed = time.time() - wait_start
                    current_time_check = time.time()
                    if current_time_check - last_log_time >= 5.0:
                        # Check for stuck readers (holding lock for more than 15 seconds)
                        # More aggressive detection since we're waiting for write
                        stuck_readers = []
                        current_time = time.time()
                        for reader_id, acquire_time in list(self._reader_acquire_times.items()):
                            hold_time = current_time - acquire_time
                            # Detect stuck readers after 10 seconds (more aggressive)
                            if hold_time > 10.0:
                                thread_info = self._reader_thread_info.get(reader_id, {})
                                stuck_readers.append((reader_id, hold_time, thread_info))
                        
                        if stuck_readers:
                            self._lock_stats['stuck_readers_detected'] += len(stuck_readers)
                            logger.warning("Write lock waiting %ss: %s active reader(s), %s potentially stuck (held >10s)", elapsed, self._active_readers, len(stuck_readers))
                            for reader_id, hold_time, thread_info in stuck_readers:
                                thread_name = thread_info.get('thread_name', 'Unknown')
                                thread_ident = thread_info.get('thread_ident', 'Unknown')
                                logger.warning(
                                    f"  STUCK READER DETECTED: Thread {thread_name} (ID: {thread_ident}, Object ID: {reader_id}) "
                                    f"has held read lock for {hold_time:.1f}s. This is blocking write operations."
                                )
                        
                        # Always log detailed info about ALL active readers when waiting
                        if self._active_readers > 0:
                            logger.warning("ACTIVE READERS DETAILS (blocking write lock): %s reader(s) active", self._active_readers)
                            current_time = time.time()
                            
                            # Log tracked readers
                            tracked_readers = list(self._reader_acquire_times.items())
                            if tracked_readers:
                                for reader_id, acquire_time in tracked_readers:
                                    hold_time = current_time - acquire_time
                                    thread_info = self._reader_thread_info.get(reader_id, {})
                                    thread_name = thread_info.get('thread_name', 'Unknown')
                                    thread_ident = thread_info.get('thread_ident', 'Unknown')
                                    stack_trace = thread_info.get('stack_trace', 'No stack trace available')
                                    logger.warning(
                                        f"  - Reader: Thread {thread_name} (ID: {thread_ident}, Object ID: {reader_id}) "
                                        f"holding lock for {hold_time:.1f}s"
                                    )
                                    if hold_time > 5.0:  # Only show stack trace for readers holding > 5s
                                        logger.debug("    Stack trace when lock was acquired:\n%s", stack_trace)
                            else:
                                # No tracking data - log all readers in _readers set
                                logger.error("  WARNING: %s active reader(s) but NO tracking data in _reader_acquire_times!", self._active_readers)
                                logger.error("  Readers in _readers set: %s", list(self._readers))
                                for reader_id in list(self._readers):
                                    thread_info = self._reader_thread_info.get(reader_id, {})
                                    thread_name = thread_info.get('thread_name', 'Unknown')
                                    thread_ident = thread_info.get('thread_ident', 'Unknown')
                                    logger.error(
                                        f"  - Untracked Reader: Thread {thread_name} (ID: {thread_ident}, Object ID: {reader_id}) "
                                        f"- NO ACQUIRE TIME TRACKED (data inconsistency detected!)"
                                    )
                        
                        last_log_time = current_time_check
                        
                        # CRITICAL FIX: Force-release readers holding locks for > 20 seconds (reduced from 30s)
                        # This prevents writers from timing out due to stuck readers
                        if stuck_readers:
                            force_released = []
                            for reader_id, hold_time, thread_info in stuck_readers:
                                # More aggressive: force-release after 20 seconds instead of 30
                                if hold_time > 20.0:  # Reduced from self._max_read_lock_hold_time (30.0)
                                    self._active_readers = max(0, self._active_readers - 1)
                                    self._readers.discard(reader_id)
                                    self._reader_acquire_times.pop(reader_id, None)
                                    self._reader_thread_info.pop(reader_id, None)
                                    # Find and remove read locks from thread_locks
                                    if reader_id in self._thread_locks:
                                        self._thread_locks[reader_id] = [lt for lt in self._thread_locks[reader_id] if lt != 'read']
                                        if not self._thread_locks[reader_id]:
                                            del self._thread_locks[reader_id]
                                    thread_name = thread_info.get('thread_name', f'Thread-{reader_id}')
                                    force_released.append((reader_id, hold_time, thread_name))
                                    logger.error("FORCE-RELEASED stuck reader: %s (id=%s), held lock for %ss (>20s)", thread_name, reader_id, hold_time)
                            
                            if force_released:
                                logger.warning("Force-released %s stuck reader(s) to unblock writer", len(force_released))
                                # After force-release, check if we can acquire lock now
                                if self._active_readers == 0:
                                    self._active_writers += 1
                                    self._waiting_writers = max(0, self._waiting_writers - 1)
                                    self._register_lock('write')
                                    acquired = True
                                    logger.warning("Write lock acquired after force-releasing stuck readers!")
                                    break
                    
                    # Log progress every 5 seconds
                    if current_time_check - last_log_time >= 5.0:
                        logger.debug("Write lock waiting %ss: %s active reader(s) still active", elapsed, self._active_readers)
                        last_log_time = time.time()
                
                # Check timeout
                elapsed = time.time() - wait_start
                if elapsed >= timeout:
                    # Before timing out, make one final aggressive attempt to force-release ALL readers
                    # This handles cases where stuck reader detection might have missed them
                    with self._readers_lock:
                        # CRITICAL: If we have ANY active readers and we're timing out, force-release them
                        # This prevents the "No stuck readers detected" issue when readers exist but aren't tracked properly
                        current_time = time.time()
                        force_released = []
                        all_reader_ids = list(self._readers)  # Get all registered readers
                        
                        # If we have active readers but no tracking data, something is wrong - force cleanup
                        if self._active_readers > 0 and len(self._reader_acquire_times) == 0:
                            logger.error("CRITICAL: %s active readers but no tracking data! Force-clearing all readers.", self._active_readers)
                            self._active_readers = 0
                            self._readers.clear()
                            # Clear thread_locks for all reader IDs we found
                            for rid in all_reader_ids:
                                if rid in self._thread_locks:
                                    self._thread_locks[rid] = [lt for lt in self._thread_locks[rid] if lt != 'read']
                                    if not self._thread_locks[rid]:
                                        del self._thread_locks[rid]
                            if self._active_readers == 0:
                                self._active_writers += 1
                                self._waiting_writers = max(0, self._waiting_writers - 1)
                                self._register_lock('write')
                                acquired = True
                                logger.warning("Write lock acquired after emergency force-clear of %s readers!", len(all_reader_ids))
                                break
                        
                        # Emergency cleanup: if we have abnormal reader count, force cleanup
                        if self._active_readers > 50:
                            logger.error("EMERGENCY: Abnormal reader count (%s) detected during write timeout. Forcing cleanup!", self._active_readers)
                            cleaned = 0
                            for rid, acquire_time in list(self._reader_acquire_times.items()):
                                # Force-release any reader holding for > 15 seconds (very aggressive)
                                if current_time - acquire_time > 15.0:
                                    self._active_readers = max(0, self._active_readers - 1)
                                    self._readers.discard(rid)
                                    self._reader_acquire_times.pop(rid, None)
                                    self._reader_thread_info.pop(rid, None)
                                    if rid in self._thread_locks:
                                        self._thread_locks[rid] = [lt for lt in self._thread_locks[rid] if lt != 'read']
                                        if not self._thread_locks[rid]:
                                            del self._thread_locks[rid]
                                    cleaned += 1
                            if cleaned > 0:
                                logger.error("EMERGENCY CLEANUP: Force-released %s stuck reader(s) during write timeout", cleaned)
                                # If we cleaned up and now have no readers, try to acquire write lock immediately
                                if self._active_readers == 0:
                                    self._active_writers += 1
                                    self._waiting_writers = max(0, self._waiting_writers - 1)
                                    self._register_lock('write')
                                    acquired = True
                                    logger.warning("Write lock acquired after emergency cleanup!")
                                    break  # Exit the wait loop, we got the lock
                        
                        # If still not acquired, force-release ANY reader holding for > 15s (very aggressive at timeout)
                        # This catches readers that might have been missed by periodic checks
                        if not acquired and self._active_readers > 0:
                            current_time = time.time()
                            for rid, acquire_time in list(self._reader_acquire_times.items()):
                                hold_time = current_time - acquire_time
                                # VERY AGGRESSIVE: Force-release any reader holding > 15s when we're about to timeout
                                # This prevents the "1 active reader" timeout issue
                                if hold_time > 15.0:
                                    self._active_readers = max(0, self._active_readers - 1)
                                    self._readers.discard(rid)
                                    self._reader_acquire_times.pop(rid, None)
                                    thread_info = self._reader_thread_info.pop(rid, None)
                                    if rid in self._thread_locks:
                                        self._thread_locks[rid] = [lt for lt in self._thread_locks[rid] if lt != 'read']
                                        if not self._thread_locks[rid]:
                                            del self._thread_locks[rid]
                                    thread_name = thread_info.get('thread_name', f'Thread-{rid}') if thread_info else f'Thread-{rid}'
                                    force_released.append((rid, hold_time, thread_name))
                                    logger.error("FORCE-RELEASED stuck reader during timeout: %s (id=%s), held lock for %ss", thread_name, rid, hold_time)
                            
                            # Also check if we have readers in _readers set but not in _reader_acquire_times (data inconsistency)
                            missing_tracking = []
                            for rid in list(self._readers):
                                if rid not in self._reader_acquire_times:
                                    missing_tracking.append(rid)
                                    self._active_readers = max(0, self._active_readers - 1)
                                    self._readers.discard(rid)
                                    self._reader_thread_info.pop(rid, None)
                                    if rid in self._thread_locks:
                                        self._thread_locks[rid] = [lt for lt in self._thread_locks[rid] if lt != 'read']
                                        if not self._thread_locks[rid]:
                                            del self._thread_locks[rid]
                            if missing_tracking:
                                logger.error("FORCE-RELEASED %s reader(s) with missing tracking data during timeout", len(missing_tracking))
                                force_released.extend([(rid, 0.0, f'Thread-{rid}') for rid in missing_tracking])
                            
                            if force_released and self._active_readers == 0:
                                # If we force-released all readers, try to acquire write lock
                                self._active_writers += 1
                                self._waiting_writers = max(0, self._waiting_writers - 1)
                                self._register_lock('write')
                                acquired = True
                                logger.warning("Write lock acquired after force-releasing %s stuck reader(s)!", len(force_released))
                                break  # Exit the wait loop, we got the lock
                        
                        # Cleanup waiting_writers since we're timing out
                        if not acquired:
                            self._waiting_writers = max(0, self._waiting_writers - 1)
                            
                            # Detailed diagnostics on timeout
                            stuck_readers = []
                            current_time = time.time()
                            for reader_id, acquire_time in list(self._reader_acquire_times.items()):
                                hold_time = current_time - acquire_time
                                thread_info = self._reader_thread_info.get(reader_id, {})
                                stuck_readers.append((reader_id, hold_time, thread_info))
                            
                            # Also include readers that are in _readers but not tracked
                            for reader_id in list(self._readers):
                                if reader_id not in self._reader_acquire_times:
                                    stuck_readers.append((reader_id, 0.0, self._reader_thread_info.get(reader_id, {})))
                            
                            stats = {
                                'active_readers': self._active_readers,
                                'active_writers': self._active_writers,
                                'waiting_writers': self._waiting_writers,
                                'waiting_readers': self._waiting_readers,
                                'stuck_readers': stuck_readers,
                                'readers_set_size': len(self._readers),
                                'tracking_size': len(self._reader_acquire_times)
                            }
                        
                        self._lock_stats['write_waits'] += 1
                        logger.error("Write lock timeout after %ss waiting for readers to finish", elapsed)
                        logger.error("Thread: %s (%s)", thread_name, thread_ident)
                        logger.error("Lock state: active_readers=%s, active_writers=%s, waiting_writers=%s", stats['active_readers'], stats['active_writers'], stats['waiting_writers'])
                        logger.error("Tracking mismatch: _readers set size=%s, _reader_acquire_times size=%s", stats['readers_set_size'], stats['tracking_size'])
                        if stuck_readers:
                            logger.error("Stuck readers detected (%s):", len(stuck_readers))
                            for reader_id, hold_time, thread_info in stuck_readers:
                                thread_name_reader = thread_info.get('thread_name', f'Thread-{reader_id}') if thread_info else f'Thread-{reader_id}'
                                logger.error("  Stuck reader: %s (id=%s) holding lock for %ss", thread_name_reader, reader_id, hold_time)
                        else:
                            logger.error("No stuck readers detected - but active_readers=%s, readers_set_size=%s", stats['active_readers'], stats['readers_set_size'])
                    
                    self._write_lock.release()
                    raise TimeoutError(f"Could not acquire write lock within {timeout} seconds")
                
                # Exponential backoff to reduce CPU usage during long waits
                # FIXED: Better backoff curve - 0.05s → 0.075s → 0.1125s → ... → 0.5s max
                idle_count += 1
                sleep_interval = min(0.05 * (1.5 ** idle_count), 0.5)  # Cap at 500ms for consistency
                time.sleep(sleep_interval)
            
            # Add SQLite-specific retry mechanism
            max_retries = 3
            retry_count = 0
            
            while retry_count < max_retries:
                try:
                    yield
                    break  # Success, exit retry loop
                except sqlite3.OperationalError as e:
                    if "database is locked" in str(e).lower():
                        retry_count += 1
                        if retry_count < max_retries:
                            wait_time = min(0.1 * (2 ** retry_count), 1.0)  # Exponential backoff, max 1 second
                            logger.warning("Database locked, retrying in %ss (attempt %s/%s)", wait_time, retry_count, max_retries)
                            time.sleep(wait_time)
                        else:
                            logger.error("Database lock failed after %s retries: %s", max_retries, e)
                            raise
                    else:
                        raise  # Re-raise non-lock related SQLite errors
                except Exception as e:
                    raise  # Re-raise non-SQLite errors
            
        except Exception as e:
            logger.error("Error in write lock context (thread: %s): %s", thread_name, e, exc_info=True)
            # Make sure to cleanup waiting_writers if we incremented it
            with self._readers_lock:
                if self._waiting_writers > 0 and not acquired:
                    self._waiting_writers = max(0, self._waiting_writers - 1)
            # If we downgraded from read but failed to acquire/execute write, try to restore the read state immediately
            if upgraded_from_read and not acquired and not reentrant_write:
                with self._readers_lock:
                    # Restore downgraded reads on failure
                    if downgraded_read_count > 0:
                        self._active_readers += downgraded_read_count
                        self._readers.add(thread_key)
                        # Re-register all reads
                        for _ in range(downgraded_read_count):
                            self._register_lock('read')
            raise
        finally:
            if acquired:
                with self._readers_lock:
                    self._active_writers = max(0, self._active_writers - 1)
                    self._unregister_lock('write')
                self._write_lock.release()
                elapsed_time = time.time() - start_time
                logger.debug("Write lock released by thread %s after %ss. Active writers: %s", thread_ident, elapsed_time, self._active_writers)
            
            # If we temporarily downgraded from read for an upgrade, restore the read lock now
            if upgraded_from_read and not reentrant_write:
                with self._readers_lock:
                    # Restore the exact number of reads we downgraded
                    if downgraded_read_count > 0:
                        # CRITICAL FIX: Only increment by 1 since active_readers tracks threads, not locks
                        self._active_readers += 1
                        self._readers.add(thread_key)
                        for _ in range(downgraded_read_count):
                            self._register_lock('read')
                        logger.debug("Restored %s read lock(s) after write upgrade", downgraded_read_count)
    
    @contextmanager
    def safe_write_lock(self, timeout: float = 60.0):
        """
        Safe write lock that prevents deadlocks by checking for conflicts
        
        Args:
            timeout: Maximum time to wait for lock acquisition (default: 60.0 seconds)
        """
        start_time = time.time()
        
        # Check if we're already holding a read lock (potential deadlock)
        current_thread_id = id(threading.current_thread())
        with self._readers_lock:
            if current_thread_id in self._readers:
                self._lock_stats['deadlock_preventions'] += 1
                logger.warning("Potential deadlock prevented: thread holding read lock trying to acquire write lock")
        
        with self.write_lock(timeout):
            yield
    
    def get_stats(self) -> dict:
        """Get lock usage statistics"""
        with self._readers_lock:
            return {
                'active_readers': self._active_readers,
                'active_writers': self._active_writers,
                'waiting_readers': self._waiting_readers,
                'waiting_writers': self._waiting_writers,
                'stats': self._lock_stats.copy(),
                'reader_hold_times': {
                    thread_id: time.time() - acquire_time
                    for thread_id, acquire_time in self._reader_acquire_times.items()
                }
            }
    
    def reset_stats(self):
        """Reset lock statistics"""
        self._lock_stats = {
            'read_waits': 0,
            'write_waits': 0,
            'deadlock_preventions': 0,
            'stuck_readers_detected': 0
        }

# Global instance - single point of control for main database (ws_monitor.db)
# Single database approach: All tables including ActivityPercentage are stored in ws_monitor.db
db_lock_manager = DatabaseLockManager()

# DEPRECATED: activity_percentage_lock_manager is no longer needed with single database approach
# Kept for backward compatibility but not used - all operations use db_lock_manager
activity_percentage_lock_manager = DatabaseLockManager()

# Backward compatibility - provide old-style lock interface
class LegacyLockWrapper:
    """
    Wrapper to maintain backward compatibility with existing code
    Gradually migrate from individual locks to centralized manager
    """
    
    def __init__(self):
        self._manager = db_lock_manager
    
    def __enter__(self):
        # Use write lock for backward compatibility
        self._context = self._manager.write_lock()
        return self._context.__enter__()
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        return self._context.__exit__(exc_type, exc_val, exc_tb)

# Create backward-compatible locks for gradual migration
def create_legacy_lock():
    """Create a legacy lock wrapper for backward compatibility"""
    return LegacyLockWrapper()
