"""
Database Writer Service
Dedicated service that processes the in-memory write queue and writes to the database.
This is the ONLY thread that performs database writes, eliminating lock contention.
"""

import asyncio
import datetime
from typing import List, Optional, Dict, Any
from sqlalchemy import select, update
from sqlalchemy.exc import IntegrityError
from ws_package.utils.db_write_queue import (
    db_write_queue,
    WriteOperationType,
    WriteQueueItem
)
from ws_package.utils.db_lock_manager import db_lock_manager
from ws_package.models.base import (
    async_session,
    ActivityInterval,
    Screenshots,
    Appmonitor,
    UserDetail,
    OrgConfigurations,
    AppConfig,
    S3details,  # Backward compatibility alias
    FixedMode,
    ActivityPercentage
)
from ws_package.entities.activity_entity import (
    ActivityIntervalEntity
)
from ws_package.entities.user_entity import UserEntity
from ws_package.entities.org_entity import OrgEntity
from ws_package.entities.s3bucket_entity import S3BucketEntity
from ws_package.entities.activity_entity import ActivityPercentageEntity
from ws_package.log.writer import LogWriter

logger = LogWriter.file(__name__)

# Database operation timeout
DB_OPERATION_TIMEOUT = 5.0


class DatabaseWriterService:
    """
    Service that processes write queue and performs batch database writes.
    
    This is the single writer thread that handles all database writes,
    eliminating lock contention between multiple threads.
    """
    
    def __init__(self):
        """Initialize the database writer service"""
        self.is_running = False
        self.process_interval = 5.0  # Process queue every 5 seconds
        self.batch_size = 50  # Process up to 50 items per batch
        self._stop_event: Optional[asyncio.Event] = None
        
        # Operation handlers mapping
        self._operation_handlers = {
            # INSERT operations
            WriteOperationType.ACTIVITY_INTERVAL: self._write_activity_interval,
            WriteOperationType.SCREENSHOT: self._write_screenshot,
            WriteOperationType.APP_MONITOR: self._write_app_monitor,
            WriteOperationType.USER: self._write_user,
            WriteOperationType.ORG: self._write_org,
            WriteOperationType.S3_DETAILS: self._write_s3_details,
            WriteOperationType.FIXED_MODE: self._write_fixed_mode,
            WriteOperationType.ACTIVITY_PERCENTAGE: self._write_activity_percentage,
            WriteOperationType.NOTES: self._write_notes,
    WriteOperationType.BULK_UPDATE: self._write_bulk_update,
            
            # UPDATE operations
            WriteOperationType.UPDATE_ACTIVITY_INTERVAL: self._update_activity_interval,
            WriteOperationType.UPDATE_SCREENSHOT: self._update_screenshot,
            WriteOperationType.UPDATE_USER: self._update_user,
            WriteOperationType.UPDATE_ORG: self._update_org,
            WriteOperationType.UPDATE_ACTIVITY_PERCENTAGE: self._update_activity_percentage,
        }
        
        logger.info("DatabaseWriterService initialized")
    
    async def start(self, stop_event: asyncio.Event):
        """
        Start the database writer service.
        
        Args:
            stop_event: Event to signal when to stop processing
        """
        if self.is_running:
            logger.warning("Database writer service is already running")
            return
        
        self.is_running = True
        self._stop_event = stop_event
        logger.info("Starting database writer service")
        
        try:
            await self._process_loop()
        except Exception as e:
            logger.error("Database writer service error: %s", e, exc_info=True)
        finally:
            self.is_running = False
            logger.info("Database writer service stopped")
    
    async def _process_loop(self):
        """Main processing loop"""
        while not self._stop_event.is_set():
            try:
                # Process pending queue items
                await self._process_batch()
                
                # Wait before next iteration
                try:
                    await asyncio.wait_for(
                        self._stop_event.wait(),
                        timeout=self.process_interval
                    )
                    # If we get here, stop event was set
                    break
                except asyncio.TimeoutError:
                    # Timeout means interval elapsed, continue processing
                    pass
                    
            except Exception as e:
                logger.error("Error in database writer loop: %s", e, exc_info=True)
                # Wait a bit before retrying on error
                await asyncio.sleep(2)

        # CRITICAL: Process all remaining queue items before stopping
        logger.info("Stop event set, processing remaining queue items before shutdown...")
        max_remaining_iterations = 10  # Process up to 10 more batches
        for i in range(max_remaining_iterations):
            remaining_count = db_write_queue.get_total_queue_size()
            if remaining_count == 0:
                logger.info("All queue items processed, shutting down")
                break
            logger.info("Processing remaining %s queue items (iteration %s/%s)...", remaining_count, i+1, max_remaining_iterations)
            await self._process_batch()
            await asyncio.sleep(0.5)  # Small delay between batches
        
        final_remaining = db_write_queue.get_total_queue_size()
        if final_remaining > 0:
            logger.warning("⚠ %s queue items still remaining after shutdown processing", final_remaining)
        else:
            logger.info("✓ All queue items processed successfully")
    
    async def _process_batch(self):
        """Process a batch of queue items from all operation types"""
        try:
            total_processed = 0
            total_failed = 0
            
            # Process each operation type
            for operation_type in WriteOperationType:
                # CRITICAL FIX: Removed stop_event check here!
                # This function is called by process_immediately() which must
                # process the queue even when stop_event is set
                
                # Get pending items for this operation type
                pending_items = db_write_queue.claim_pending_items(
                    operation_type,
                    limit=self.batch_size
                )
                
                if not pending_items:
                    continue
                
                logger.debug(
                    f"Processing {len(pending_items)} {operation_type.value} items"
                )
                
                # Process items
                handler = self._operation_handlers.get(operation_type)
                if not handler:
                    logger.error("No handler for operation type: %s", operation_type)
                    continue
                
                try:
                    # Batch write to database
                    success_count, failed_items = await handler(pending_items)
                    
                    # Remove successfully processed items
                    if success_count > 0:
                        successful_items = [
                            item for item in pending_items
                            if item not in failed_items
                        ]
                        db_write_queue.remove_processed(operation_type, successful_items)
                        total_processed += success_count
                    
                    # Mark failed items
                    if failed_items:
                        removed = db_write_queue.mark_failed(operation_type, failed_items)
                        total_failed += len(failed_items)
                        if removed > 0:
                            logger.warning(
                                f"Removed {removed} {operation_type.value} items "
                                f"after max retries"
                            )
                
                except Exception as e:
                    logger.error(
                        f"Error processing {operation_type.value} batch: {e}",
                        exc_info=True
                    )
                    # Mark all items as failed
                    db_write_queue.mark_failed(operation_type, pending_items)
                    total_failed += len(pending_items)
            
            if total_processed > 0:
                logger.info(
                    f"Processed {total_processed} write operations, "
                    f"failed: {total_failed}"
                )
            
            # Log queue statistics periodically
            stats = db_write_queue.get_stats()
            total_queue_size = db_write_queue.get_total_queue_size()
            if total_queue_size > 0:
                logger.debug(
                    f"Queue stats: total={total_queue_size}, "
                    f"enqueued={stats['total_enqueued']}, "
                    f"processed={stats['total_processed']}, "
                    f"failed={stats['total_failed']}"
                )
                
        except Exception as e:
            logger.error("Error in batch processing: %s", e, exc_info=True)

    def _is_activity_interval_unique_violation(self, error: Exception) -> bool:
        error_text = str(error).lower()
        return (
            "unique constraint failed" in error_text
            and "activity_interval.start_time" in error_text
            and "activity_interval.end_time" in error_text
        )

    async def _commit_activity_intervals_individually(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """
        Fallback path used after a batch commit fails.

        This isolates each insert so one duplicate row caused by the DB unique
        constraint does not fail the entire batch.
        """
        success_count = 0
        failed_items: List[WriteQueueItem] = []

        async with async_session() as session:
            for item in items:
                try:
                    entity = ActivityIntervalEntity(**item.data)

                    existing_query = select(ActivityInterval.id).where(
                        ActivityInterval.start_time == entity.start_time,
                        ActivityInterval.end_time == entity.end_time,
                    ).limit(1)
                    existing_result = await session.execute(existing_query)
                    existing_id = existing_result.scalar_one_or_none()

                    if existing_id is not None:
                        logger.warning(
                            f"[DB_WRITE] Skipping duplicate ActivityInterval during fallback: "
                            f"id={existing_id}, start={entity.start_time}, end={entity.end_time}"
                        )
                        success_count += 1
                        continue

                    session.add(ActivityInterval(**entity.model_dump()))

                    try:
                        await asyncio.wait_for(
                            session.commit(),
                            timeout=DB_OPERATION_TIMEOUT
                        )
                        success_count += 1
                    except IntegrityError as integrity_error:
                        await session.rollback()
                        if self._is_activity_interval_unique_violation(integrity_error):
                            logger.warning(
                                f"[DB_WRITE] Unique constraint skip for ActivityInterval during fallback: "
                                f"start={entity.start_time}, end={entity.end_time}"
                            )
                            success_count += 1
                        else:
                            logger.error(
                                f"[DB_WRITE] Integrity error while writing ActivityInterval fallback item: {integrity_error}",
                                exc_info=True
                            )
                            failed_items.append(item)
                    except Exception as commit_error:
                        await session.rollback()
                        logger.error(
                            f"[DB_WRITE] Failed to commit ActivityInterval fallback item: {commit_error}",
                            exc_info=True
                        )
                        failed_items.append(item)
                except Exception as item_error:
                    logger.error(
                        f"[DB_WRITE] Failed preparing ActivityInterval fallback item: {item_error}",
                        exc_info=True
                    )
                    failed_items.append(item)

        return success_count, failed_items
    
    async def _write_activity_interval(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write ActivityInterval items"""
        logger.info("[DB_WRITE] _write_activity_interval called with %s items", len(items))
        success_count = 0
        failed_items = []
        
        try:
            logger.info("[DB_WRITE] Acquiring write lock for %s ActivityInterval items", len(items))
            with db_lock_manager.write_lock():
                logger.info("[DB_WRITE] Write lock acquired, creating database session")
                async with async_session() as session:
                    items_to_commit: List[WriteQueueItem] = []
                    seen_keys = set()

                    for item in items:
                        try:
                            # Reconstruct entity from data
                            entity = ActivityIntervalEntity(**item.data)
                            identity_key = (
                                entity.start_time,
                                entity.end_time,
                            )

                            if identity_key in seen_keys:
                                logger.warning(
                                    f"[DB_WRITE] Skipping duplicate ActivityInterval already present in batch: "
                                    f"start={entity.start_time}, end={entity.end_time}"
                                )
                                success_count += 1
                                continue

                            existing_query = select(ActivityInterval.id).where(
                                ActivityInterval.start_time == entity.start_time,
                                ActivityInterval.end_time == entity.end_time,
                            ).limit(1)
                            existing_result = await session.execute(existing_query)
                            existing_id = existing_result.scalar_one_or_none()

                            if existing_id is not None:
                                seen_keys.add(identity_key)
                                logger.warning(
                                    f"[DB_WRITE] Skipping duplicate ActivityInterval already in database: "
                                    f"id={existing_id}, start={entity.start_time}, end={entity.end_time}"
                                )
                                success_count += 1
                                continue
                            
                            # Create database model
                            activity_interval = ActivityInterval(
                                **entity.model_dump()
                            )
                            session.add(activity_interval)
                            items_to_commit.append(item)
                            seen_keys.add(identity_key)
                            
                        except Exception as e:
                            logger.error(
                                f"Error preparing ActivityInterval item: {e}",
                                exc_info=True
                            )
                            failed_items.append(item)
                    
                    # Commit all items in one transaction
                    if items_to_commit:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                            success_count += len(items_to_commit)
                            logger.info(
                                f"✓ Successfully committed {len(items_to_commit)} ActivityInterval items to database"
                            )
                        except asyncio.TimeoutError:
                            logger.error(
                                f"Database commit timed out for ActivityInterval batch"
                            )
                            # Mark all as failed
                            failed_items.extend(items_to_commit)
                        except IntegrityError as commit_error:
                            await session.rollback()
                            if self._is_activity_interval_unique_violation(commit_error):
                                logger.warning(
                                    "[DB_WRITE] Batch commit hit unique start/end constraint for activity_interval. "
                                    "Retrying items individually."
                                )
                                fallback_success_count, fallback_failed_items = await self._commit_activity_intervals_individually(
                                    items_to_commit
                                )
                                success_count += fallback_success_count
                                failed_items.extend(fallback_failed_items)
                            else:
                                logger.error(
                                    f"❌ Integrity error committing ActivityInterval batch: {commit_error}",
                                    exc_info=True
                                )
                                failed_items.extend(items_to_commit)
                        except Exception as commit_error:
                            logger.error(
                                f"❌ Database commit FAILED for {len(items_to_commit)} ActivityInterval items: {commit_error}",
                                exc_info=True
                            )
                            await session.rollback()
                            # Mark all as failed
                            failed_items.extend(items_to_commit)
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error(
                f"Error writing ActivityInterval batch: {e}",
                exc_info=True
            )
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_screenshot(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write Screenshot items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            from ws_package.entities.screenshot_entity import ScreenshotEntity
                            entity = ScreenshotEntity(**item.data)
                            screenshot = Screenshots(**entity.model_dump())
                            session.add(screenshot)
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing Screenshot item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing Screenshot batch: %s", e, exc_info=True)
                            try:
                                await session.rollback()
                            except Exception:
                                pass
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()

        except Exception as e:
            logger.error("Error writing Screenshot batch: %s", e, exc_info=True)
            failed_items = items

        return success_count, failed_items
    
    async def _write_app_monitor(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write Appmonitor items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            app_monitor = Appmonitor(**item.data)
                            session.add(app_monitor)
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing Appmonitor item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing Appmonitor batch: %s", e, exc_info=True)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing Appmonitor batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_user(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write User items (upsert: update if exists, insert if not)"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            entity = UserEntity(**item.data)
                            entity_data = entity.model_dump()
                            user_id = entity_data.get('user_id')
                            
                            # Convert sd_token from str to int (UserDetail stores as int, UserEntity uses str)
                            if 'sd_token' in entity_data and isinstance(entity_data['sd_token'], str):
                                try:
                                    entity_data['sd_token'] = int(entity_data['sd_token'])
                                except (ValueError, TypeError):
                                    logger.warning("[UserWrite] Could not convert sd_token to int: %s", entity_data['sd_token'])
                            
                            # Check if user with this user_id already exists
                            from ws_package.models.base import UserDetail
                            from sqlalchemy import select
                            query = select(UserDetail).where(UserDetail.user_id == user_id)
                            result = await session.execute(query)
                            existing_user = result.scalar_one_or_none()
                            
                            if existing_user:
                                # Update existing user
                                logger.info("[UserWrite] Updating existing user: user_id=%s, org_id=%s, token=%s..., sd_token=%s", user_id, entity_data.get('org_id'), (entity_data.get('token') or '')[:30], entity_data.get('sd_token'))
                                for key, value in entity_data.items():
                                    if hasattr(existing_user, key):
                                        old_value = getattr(existing_user, key)
                                        setattr(existing_user, key, value)
                                        if key in ['token', 'sd_token', 'org_id']:
                                            logger.info("[UserWrite] Updated %s: %s -> %s", key, old_value, value)
                            else:
                                # Insert new user
                                logger.info("[UserWrite] Inserting new user: user_id=%s, org_id=%s", user_id, entity_data.get('org_id'))
                                user = UserDetail(**entity_data)
                                session.add(user)
                            
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing User item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                            logger.info("[UserWrite] Successfully saved %s user record(s)", success_count)
                        except Exception as e:
                            logger.error("Error committing User batch: %s", e, exc_info=True)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing User batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_org(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write Org items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            entity = OrgEntity(**item.data)
                            # Exclude idle_time_limit_in_seconds - it's not a database field
                            # It's only used in memory by ActivityController
                            org_data = entity.model_dump(exclude={'idle_time_limit_in_seconds'})
                            org = OrgConfigurations(**org_data)
                            session.add(org)
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing Org item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing Org batch: %s", e, exc_info=True)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing Org batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_s3_details(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write S3Details items (INSERT or UPDATE based on org_id)"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            org_id = item.data.get('org_id')
                            if not org_id:
                                logger.error("S3Details item missing org_id")
                                failed_items.append(item)
                                continue
                            
                            # Check if record exists for this org_id
                            query = select(AppConfig).where(AppConfig.org_id == org_id)
                            result = await session.execute(query)
                            existing_config = result.scalar_one_or_none()
                            
                            if existing_config:
                                # UPDATE existing record
                                # Encryption will be handled automatically by model event handlers
                                for key, value in item.data.items():
                                    if hasattr(existing_config, key) and key != 'id':
                                        setattr(existing_config, key, value)
                                logger.debug("Updating existing S3Details for org_id: %s", org_id)
                            else:
                                # INSERT new record
                                # Encryption will be handled automatically by model event handlers
                                app_config = AppConfig(**item.data)
                                session.add(app_config)
                                logger.debug("Inserting new S3Details for org_id: %s", org_id)
                            
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing S3Details item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                            logger.debug("Successfully wrote %s S3Details items", success_count)
                        except Exception as e:
                            logger.error("Error committing S3Details batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing S3Details batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_fixed_mode(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write FixedMode items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            from ws_package.entities.mode_entity import ModeEntity
                            entity = ModeEntity(**item.data)
                            fixed_mode = FixedMode(**entity.model_dump())
                            session.add(fixed_mode)
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing FixedMode item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing FixedMode batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing FixedMode batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_activity_percentage(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write ActivityPercentage items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            entity = ActivityPercentageEntity(**item.data)
                            activity_percentage = ActivityPercentage(
                                **entity.model_dump()
                            )
                            session.add(activity_percentage)
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing ActivityPercentage item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing ActivityPercentage batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing ActivityPercentage batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _write_notes(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch write Notes items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            from ws_package.models.base import Notes
                            # item.data is a dict with activity_interval_id and note
                            notes_entry = Notes(**item.data)
                            session.add(notes_entry)
                            success_count += 1
                        except Exception as e:
                            logger.error("Error preparing Notes item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing Notes batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing Notes batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items

    async def _write_bulk_update(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch process bulk updates"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            # item.data contains {"updates": [...]}
                            updates = item.data.get("updates", [])
                            table_name = item.table_name
                            
                            if table_name == "activity_interval":
                                from ws_package.models.base import ActivityInterval
                                model = ActivityInterval
                            else:
                                logger.warning("Unsupported table for bulk update: %s", table_name)
                                failed_items.append(item)
                                continue
                                
                            for update_data in updates:
                                record_id = update_data.get("id")
                                if not record_id:
                                    continue
                                    
                                # Remove id from update data
                                values = {k: v for k, v in update_data.items() if k != "id"}
                                
                                # Execute update
                                stmt = (
                                    update(model)
                                    .where(model.id == record_id)
                                    .values(**values)
                                )
                                await session.execute(stmt)
                            
                            success_count += 1
                            
                        except Exception as e:
                            logger.error("Error preparing bulk update item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing bulk update batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error writing bulk update batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
 
    async def process_immediately(self, ignore_stop_event: bool = False) -> int:

        logger.info("[FLUSH] process_immediately() called - checking queue status")
        initial_queue_size = db_write_queue.get_total_queue_size()
        logger.info("[FLUSH] Initial queue size: %s", initial_queue_size)
        
        # Log queue size for each operation type
        for op_type in WriteOperationType:
            size = db_write_queue.get_queue_size(op_type)
            if size > 0:
                logger.info("[FLUSH] Queue has %s items of type %s", size, op_type.value)
        
        stats_before = db_write_queue.get_stats()
        processed_before = stats_before["total_processed"]
        max_iterations = 100  # Prevent infinite loops
        # Flush even when the writer loop has stopped (e.g. killed on timer restart).
        should_ignore_stop = ignore_stop_event or not self.is_running

        for iteration in range(max_iterations):
            if not should_ignore_stop and self._stop_event and self._stop_event.is_set():
                logger.warning(
                    "[FLUSH] Stopped early: db writer stop event is set "
                    "(queue size=%s)",
                    db_write_queue.get_total_queue_size(),
                )
                break
            
            # Process one batch
            logger.info("[FLUSH] Processing batch iteration %s", iteration + 1)
            await self._process_batch()
            
            # Check if queue is empty
            if db_write_queue.get_total_queue_size() == 0:
                logger.info("[FLUSH] Queue empty after %s iterations", iteration + 1)
                break
        
        stats_after = db_write_queue.get_stats()
        processed_delta = stats_after["total_processed"] - processed_before
        remaining = db_write_queue.get_total_queue_size()

        if processed_delta > 0:
            logger.info("[FLUSH] ✓ Immediately processed %s queue items", processed_delta)
        elif initial_queue_size > 0 and remaining > 0:
            logger.warning(
                "[FLUSH] Queue still has %s items after flush (writer running=%s)",
                remaining,
                self.is_running,
            )
        
        return processed_delta
    
    def get_status(self) -> Dict[str, Any]:
        """Get writer service status"""
        stats = db_write_queue.get_stats()
        return {
            "is_running": self.is_running,
            "process_interval": self.process_interval,
            "batch_size": self.batch_size,
            "queue_stats": stats,
            "total_queue_size": db_write_queue.get_total_queue_size()
        }
    
    # UPDATE operation handlers
    async def _update_activity_interval(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch update ActivityInterval items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            # Get the record to update
                            if item.record_id:
                                query = select(ActivityInterval).where(ActivityInterval.id == item.record_id)
                                result = await session.execute(query)
                                activity_interval = result.scalar_one_or_none()
                                
                                if activity_interval:
                                    # Update fields from data
                                    for key, value in item.data.items():
                                        if hasattr(activity_interval, key):
                                            setattr(activity_interval, key, value)
                                    success_count += 1
                                else:
                                    logger.warning("ActivityInterval with id %s not found", item.record_id)
                                    failed_items.append(item)
                            else:
                                logger.error("UPDATE_ACTIVITY_INTERVAL requires record_id")
                                failed_items.append(item)
                        except Exception as e:
                            logger.error("Error updating ActivityInterval item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing ActivityInterval update batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error updating ActivityInterval batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _update_screenshot(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch update Screenshot items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            if item.record_id:
                                query = select(Screenshots).where(Screenshots.id == item.record_id)
                                result = await session.execute(query)
                                screenshot = result.scalar_one_or_none()
                                
                                if screenshot:
                                    for key, value in item.data.items():
                                        if hasattr(screenshot, key):
                                            setattr(screenshot, key, value)
                                    success_count += 1
                                else:
                                    logger.warning("Screenshot with id %s not found", item.record_id)
                                    failed_items.append(item)
                            else:
                                logger.error("UPDATE_SCREENSHOT requires record_id")
                                failed_items.append(item)
                        except Exception as e:
                            logger.error("Error updating Screenshot item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing Screenshot update batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error updating Screenshot batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _update_user(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch update User items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            if item.record_id:
                                query = select(UserDetail).where(UserDetail.id == item.record_id)
                                result = await session.execute(query)
                                user = result.scalar_one_or_none()
                                
                                if user:
                                    for key, value in item.data.items():
                                        if hasattr(user, key):
                                            setattr(user, key, value)
                                    success_count += 1
                                else:
                                    logger.warning("User with id %s not found", item.record_id)
                                    failed_items.append(item)
                            else:
                                logger.error("UPDATE_USER requires record_id")
                                failed_items.append(item)
                        except Exception as e:
                            logger.error("Error updating User item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing User update batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error updating User batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _update_org(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch update Org items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            if item.record_id:
                                query = select(OrgConfigurations).where(OrgConfigurations.id == item.record_id)
                                result = await session.execute(query)
                                org = result.scalar_one_or_none()
                                
                                if org:
                                    for key, value in item.data.items():
                                        if hasattr(org, key):
                                            setattr(org, key, value)
                                    success_count += 1
                                else:
                                    logger.warning("Org with id %s not found", item.record_id)
                                    failed_items.append(item)
                            else:
                                logger.error("UPDATE_ORG requires record_id")
                                failed_items.append(item)
                        except Exception as e:
                            logger.error("Error updating Org item: %s", e)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing Org update batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error updating Org batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items
    
    async def _update_activity_percentage(
        self,
        items: List[WriteQueueItem]
    ) -> tuple[int, List[WriteQueueItem]]:
        """Batch update ActivityPercentage items"""
        success_count = 0
        failed_items = []
        
        try:
            with db_lock_manager.write_lock():
                async with async_session() as session:
                    for item in items:
                        try:
                            if item.record_id:
                                # Update by specific ID
                                query = select(ActivityPercentage).where(ActivityPercentage.id == item.record_id)
                                result = await session.execute(query)
                                activity_percentage = result.scalar_one_or_none()
                                
                                if activity_percentage:
                                    for key, value in item.data.items():
                                        if hasattr(activity_percentage, key):
                                            setattr(activity_percentage, key, value)
                                    success_count += 1
                                else:
                                    logger.warning("ActivityPercentage with id %s not found", item.record_id)
                                    failed_items.append(item)
                            else:
                                # Match by start and stop times to find the correct slot record
                                # This ensures we update the right slot, not just the latest by ID
                                start_time = item.data.get('start')
                                stop_time = item.data.get('stop')
                                
                                if start_time and stop_time:
                                    # Convert string datetime to datetime object if needed
                                    if isinstance(start_time, str):
                                        try:
                                            # Try ISO format first
                                            if 'T' in start_time:
                                                start_time = datetime.datetime.fromisoformat(start_time.replace('Z', '+00:00'))
                                            else:
                                                # Try common formats
                                                for fmt in ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f']:
                                                    try:
                                                        start_time = datetime.datetime.strptime(start_time, fmt)
                                                        break
                                                    except ValueError:
                                                        continue
                                        except (ValueError, AttributeError) as e:
                                            logger.warning("Could not parse start_time '%s': %s", start_time, e)
                                            start_time = None
                                    
                                    if isinstance(stop_time, str):
                                        try:
                                            # Try ISO format first
                                            if 'T' in stop_time:
                                                stop_time = datetime.datetime.fromisoformat(stop_time.replace('Z', '+00:00'))
                                            else:
                                                # Try common formats
                                                for fmt in ['%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M:%S.%f']:
                                                    try:
                                                        stop_time = datetime.datetime.strptime(stop_time, fmt)
                                                        break
                                                    except ValueError:
                                                        continue
                                        except (ValueError, AttributeError) as e:
                                            logger.warning("Could not parse stop_time '%s': %s", stop_time, e)
                                            stop_time = None
                                    
                                    if start_time and stop_time:
                                        # Find record matching this slot's start and stop times
                                        query = select(ActivityPercentage).where(
                                            ActivityPercentage.start == start_time,
                                            ActivityPercentage.stop == stop_time
                                        ).limit(1)
                                        result = await session.execute(query)
                                        activity_percentage = result.scalar_one_or_none()
                                        
                                        if activity_percentage:
                                            # Update existing record for this slot
                                            for key, value in item.data.items():
                                                if hasattr(activity_percentage, key):
                                                    setattr(activity_percentage, key, value)
                                            success_count += 1
                                        else:
                                            # No record exists for this slot, create a new one
                                            activity_percentage = ActivityPercentage(**item.data)
                                            session.add(activity_percentage)
                                            success_count += 1
                                    else:
                                        # Failed to parse times, fallback to latest by ID
                                        logger.warning("ActivityPercentage update: failed to parse start/stop times, using latest by ID")
                                        query = select(ActivityPercentage).order_by(ActivityPercentage.id.desc()).limit(1)
                                        result = await session.execute(query)
                                        activity_percentage = result.scalar_one_or_none()
                                        
                                        if activity_percentage:
                                            for key, value in item.data.items():
                                                if hasattr(activity_percentage, key):
                                                    setattr(activity_percentage, key, value)
                                            success_count += 1
                                        else:
                                            # No record exists, create a new one
                                            activity_percentage = ActivityPercentage(**item.data)
                                            session.add(activity_percentage)
                                            success_count += 1
                                else:
                                    # Fallback: if start/stop not provided, use latest by ID (old behavior)
                                    logger.warning("ActivityPercentage update: start/stop times not provided, using latest by ID")
                                    query = select(ActivityPercentage).order_by(ActivityPercentage.id.desc()).limit(1)
                                    result = await session.execute(query)
                                    activity_percentage = result.scalar_one_or_none()
                                    
                                    if activity_percentage:
                                        for key, value in item.data.items():
                                            if hasattr(activity_percentage, key):
                                                setattr(activity_percentage, key, value)
                                        success_count += 1
                                    else:
                                        # No record exists, create a new one
                                        activity_percentage = ActivityPercentage(**item.data)
                                        session.add(activity_percentage)
                                        success_count += 1
                        except Exception as e:
                            logger.error("Error updating ActivityPercentage item: %s", e, exc_info=True)
                            failed_items.append(item)
                    
                    if success_count > 0:
                        try:
                            await asyncio.wait_for(
                                session.commit(),
                                timeout=DB_OPERATION_TIMEOUT
                            )
                        except Exception as e:
                            logger.error("Error committing ActivityPercentage update batch: %s", e)
                            failed_items.extend(items[:success_count])
                            success_count = 0
                    else:
                        await session.rollback()
        
        except Exception as e:
            logger.error("Error updating ActivityPercentage batch: %s", e, exc_info=True)
            failed_items = items
        
        return success_count, failed_items


# Global instance
db_writer_service = DatabaseWriterService()

