"""
Distribution Routes
Email and Distribution List Management APIs
"""
import time
from datetime import datetime
from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy.orm import Session
from sqlalchemy import func
from sqlalchemy.exc import OperationalError
from pydantic import BaseModel
from typing import Optional, List, Dict, Any

from app.core.database import get_db, SessionLocal
from app.models.distribution import DistributionList, DistributionListEmail
from fastapi.concurrency import run_in_threadpool


# Schemas
class GetDistributionListsRequest(BaseModel):
    """Request model for getting distribution lists"""
    user_id: str


class DistributionListCreateRequest(BaseModel):
    """Request model for creating a distribution list with emails"""
    name: str
    description: Optional[str] = None
    user_id: str
    emails: List[Dict[str, str]]  # List of {"email": str, "name": str}


class AddEmailToDistributionListRequest(BaseModel):
    """Request model for adding an email to a distribution list"""
    list_id: int
    email: str
    name: Optional[str] = None


class DeleteDistributionListRequest(BaseModel):
    """Request model for deleting a distribution list"""
    list_id: int


class DeleteEmailFromDistributionListRequest(BaseModel):
    """Request model for deleting an email from a distribution list"""
    email_id: int  # ID of the email to delete
    list_id: int  # ID of the distribution list (for validation)


class DistributionListResponse(BaseModel):
    """Response model for distribution list"""
    id: int
    name: str
    description: Optional[str]
    user_id: str
    is_active: bool
    email_count: int
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None
    class Config:
        from_attributes = True


class DistributionEmailResponse(BaseModel):
    """Response model for distribution list email"""
    id: int
    distribution_list_id: int
    email: str
    name: Optional[str]
    is_active: bool
    created_at: Optional[datetime] = None
    class Config:
        from_attributes = True


router = APIRouter(prefix="/distributions", tags=["Distributions"])


@router.post("/lists")
async def get_distribution_lists(request: GetDistributionListsRequest, db: Session = Depends(get_db)):
    """
    Get distribution lists created by a user.
    Returns lists with nested email objects and email count.
    
    Body:
        user_id: User ID who created the lists
    """
    try:
        # Query distribution lists
        def fetch_lists():
            return db.query(DistributionList).filter(
                DistributionList.created_by == request.user_id,
                DistributionList.is_active == True
            ).order_by(
                DistributionList.created_at.desc()
            ).all()

        lists = await run_in_threadpool(fetch_lists)
        
        # Format response with nested emails
        result = []
        for dist_list in lists:
            # Get emails for this list
            def fetch_emails(list_id):
                return db.query(DistributionListEmail).filter(
                    DistributionListEmail.distribution_list_id == list_id,
                    DistributionListEmail.is_active == True
                ).order_by(
                    DistributionListEmail.email
                ).all()

            emails = await run_in_threadpool(fetch_emails, dist_list.id)
            
            # Format email objects
            email_list = []
            for email in emails:
                email_list.append({
                    "id": email.id,
                    "email": email.email,
                    "name": email.name,
                    "is_active": email.is_active,
                    "created_at": email.created_at.isoformat() if email.created_at else None
                })
            
            result.append({
                "id": dist_list.id,
                "name": dist_list.name,
                "description": dist_list.description,
                "user_id": dist_list.created_by,
                "is_active": dist_list.is_active,
                "email_count": len(email_list),
                "emails": email_list,
                "created_at": dist_list.created_at.isoformat() if dist_list.created_at else None,
                "updated_at": dist_list.updated_at.isoformat() if dist_list.updated_at else None
            })
        
        return {
            "success": True,
            "message": "Distribution lists retrieved successfully",
            "details": {"distribution_lists": result}
        }
    except Exception as e:
        return {
            "success": False, 
            "message": f"Error retrieving distribution lists: {str(e)}",
            "details": {}
        }


@router.post("/lists/create", status_code=201)
async def create_distribution_list(request: DistributionListCreateRequest, db: Session = Depends(get_db)):
    """
    Create a new distribution list with emails.
    Uses retry logic for database locks and ensures database connection is closed after response.
    
    Body:
        name: Name of the distribution list
        description: Optional description
        user_id: User ID creating the list
        emails: List of email dictionaries with "email" and optional "name" keys
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = await run_in_threadpool(SessionLocal)
                    manually_created_session = True
                
                try:
                    # Create distribution list
                    def create_list_transaction():
                        dist_list = DistributionList(
                            name=request.name,
                            description=request.description,
                            created_by=request.user_id
                        )
                        current_db.add(dist_list)
                        current_db.flush()  # Flush to get the ID without committing
                        
                        list_id = dist_list.id
                        
                        # Add emails
                        for email_data in request.emails:
                            email = DistributionListEmail(
                                distribution_list_id=list_id,
                                email=email_data.get('email', ''),
                                name=email_data.get('name', '')
                            )
                            current_db.add(email)
                        
                        current_db.commit()
                        current_db.refresh(dist_list)
                        return dist_list

                    dist_list = await run_in_threadpool(create_list_transaction)
                    list_id = dist_list.id
                    
                    result = {
                        "success": True,
                        "message": "Distribution list created successfully",
                        "details": {
                            "id": list_id,
                            "name": dist_list.name,
                            "description": dist_list.description,
                            "user_id": dist_list.created_by,
                            "is_active": dist_list.is_active,
                            "created_at": dist_list.created_at.isoformat() if dist_list.created_at else None
                        }
                    }
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    return result
                    
                except OperationalError as e:
                    await run_in_threadpool(current_db.rollback)
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Error creating distribution list: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error creating distribution list: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    current_db.close()
                return {
                    "success": False,
                    "message": f"Error creating distribution list: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error creating distribution list: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                await run_in_threadpool(current_db.close)
            except Exception:
                pass


@router.post("/emails/add")
async def add_email_to_distribution_list(request: AddEmailToDistributionListRequest, db: Session = Depends(get_db)):
    """
    Add an email to a distribution list.
    Uses retry logic for database locks and ensures database connection is closed after response.
    
    Body:
        list_id: ID of the distribution list
        email: Email address to add
        name: Optional name for the email
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = await run_in_threadpool(SessionLocal)
                    manually_created_session = True
                
                try:
                    # Check if distribution list exists
                    def check_list_exists():
                        return current_db.query(DistributionList).filter(
                            DistributionList.id == request.list_id,
                            DistributionList.is_active == True
                        ).first()

                    dist_list = await run_in_threadpool(check_list_exists)
                    
                    if not dist_list:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Distribution list with id {request.list_id} not found",
                            "details": {}
                        }
                    
                    # Check if email already exists (using unique constraint handling)
                    def check_email_exists():
                        return current_db.query(DistributionListEmail).filter(
                            DistributionListEmail.distribution_list_id == request.list_id,
                            DistributionListEmail.email == request.email
                        ).first()

                    existing_email = await run_in_threadpool(check_email_exists)
                    
                    if existing_email:
                        if existing_email.is_active:
                            if manually_created_session:
                                await run_in_threadpool(current_db.close)
                            return {
                                "success": False,
                                "message": "Email already exists in this distribution list",
                                "details": {}
                            }
                        else:
                            # Reactivate existing email
                            def reactivate_email():
                                existing_email.is_active = True
                                existing_email.name = request.name or existing_email.name
                                current_db.commit()
                                current_db.refresh(existing_email)
                                return existing_email

                            existing_email = await run_in_threadpool(reactivate_email)
                            
                            result = {
                                "success": True,
                                "message": "Email reactivated and added to list successfully",
                                "details": {
                                    "id": existing_email.id,
                                    "email": existing_email.email,
                                    "name": existing_email.name,
                                    "is_active": existing_email.is_active
                                }
                            }
                            
                            # Close manually created session before returning
                            if manually_created_session:
                                await run_in_threadpool(current_db.close)
                            
                            return result
                    
                    # Create new email entry
                    def create_email_entry():
                        email = DistributionListEmail(
                            distribution_list_id=request.list_id,
                            email=request.email,
                            name=request.name or ''
                        )
                        current_db.add(email)
                        current_db.commit()
                        current_db.refresh(email)
                        return email

                    email = await run_in_threadpool(create_email_entry)
                    
                    result = {
                        "success": True,
                        "message": "Email added to list successfully",
                        "details": {
                            "id": email.id,
                            "email": email.email,
                            "name": email.name,
                            "is_active": email.is_active,
                            "created_at": email.created_at.isoformat() if email.created_at else None
                        }
                    }
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    return result
                    
                except OperationalError as e:
                    await run_in_threadpool(current_db.rollback)
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Error adding email to distribution list: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error adding email to distribution list: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    await run_in_threadpool(current_db.close)
                return {
                    "success": False,
                    "message": f"Error adding email to distribution list: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error adding email to distribution list: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                current_db.close()
            except Exception:
                pass


@router.post("/lists/delete")
async def delete_distribution_list(request: DeleteDistributionListRequest, db: Session = Depends(get_db)):
    """
    Delete a distribution list (mailing list).
    Performs a soft delete by setting is_active to False.
    Uses retry logic for database locks and ensures database connection is closed after response.
    
    Body:
        list_id: ID of the distribution list to delete
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = await run_in_threadpool(SessionLocal)
                    manually_created_session = True
                
                try:
                    # Find the distribution list
                    def find_list():
                        return current_db.query(DistributionList).filter(
                            DistributionList.id == request.list_id
                        ).first()

                    dist_list = await run_in_threadpool(find_list)
                    
                    if not dist_list:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Distribution list with id {request.list_id} not found",
                            "details": {}
                        }
                    
                    # Soft delete: set is_active to False
                    def soft_delete_list():
                        dist_list.is_active = False
                        current_db.commit()

                    await run_in_threadpool(soft_delete_list)
                    
                    result = {
                        "success": True,
                        "message": "Distribution list deleted successfully",
                        "details": {
                            "id": request.list_id
                        }
                    }
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    return result
                    
                except OperationalError as e:
                    await run_in_threadpool(current_db.rollback)
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Error deleting distribution list: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error deleting distribution list: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    await run_in_threadpool(current_db.close)
                return {
                    "success": False,
                    "message": f"Error deleting distribution list: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error deleting distribution list: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                current_db.close()
            except Exception:
                pass


@router.post("/emails/delete")
async def delete_email_from_distribution_list(request: DeleteEmailFromDistributionListRequest, db: Session = Depends(get_db)):
    """
    Delete a single email from a distribution list.
    Performs a soft delete by setting is_active to False.
    Uses retry logic for database locks and ensures database connection is closed after response.
    
    Body:
        email_id: ID of the email to delete
        list_id: ID of the distribution list (for validation)
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    manually_created_session = False
    
    try:
        for attempt in range(max_retries):
            try:
                # Create a new session for retries (after first attempt)
                if attempt > 0:
                    current_db = await run_in_threadpool(SessionLocal)
                    manually_created_session = True
                
                try:
                    # Check if distribution list exists and is active
                    def get_active_list():
                        return current_db.query(DistributionList).filter(
                            DistributionList.id == request.list_id,
                            DistributionList.is_active == True
                        ).first()

                    dist_list = await run_in_threadpool(get_active_list)
                    
                    if not dist_list:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Distribution list with id {request.list_id} not found or is inactive",
                            "details": {}
                        }
                    
                    # Find the email by ID and verify it belongs to the specified list
                    def get_email_entry():
                        return current_db.query(DistributionListEmail).filter(
                            DistributionListEmail.id == request.email_id,
                            DistributionListEmail.distribution_list_id == request.list_id
                        ).first()
                    
                    email_entry = await run_in_threadpool(get_email_entry)
                    
                    if not email_entry:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Email with id {request.email_id} not found in distribution list {request.list_id}",
                            "details": {}
                        }
                    
                    # Soft delete: set is_active to False
                    def soft_delete_email():
                        email_entry.is_active = False
                        current_db.commit()

                    await run_in_threadpool(soft_delete_email)
                    
                    result = {
                        "success": True,
                        "message": "Email deleted from distribution list successfully",
                        "details": {
                            "id": email_entry.id,
                            "email": email_entry.email,
                            "list_id": request.list_id
                        }
                    }
                    
                    # Close manually created session before returning
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    
                    return result
                    
                except OperationalError as e:
                    await run_in_threadpool(current_db.rollback)
                    # Check if it's a database locked error
                    if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                        # Close the current session before retry
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                            manually_created_session = False
                        # Exponential backoff: wait longer for each retry
                        time.sleep(retry_delay * (2 ** attempt))
                        continue
                    else:
                        if manually_created_session:
                            await run_in_threadpool(current_db.close)
                        return {
                            "success": False,
                            "message": f"Error deleting email from distribution list: Database is locked. Please try again later. {str(e)}",
                            "details": {}
                        }
                except Exception as e:
                    await run_in_threadpool(current_db.rollback)
                    if manually_created_session:
                        await run_in_threadpool(current_db.close)
                    return {
                        "success": False,
                        "message": f"Error deleting email from distribution list: {str(e)}",
                        "details": {}
                    }
            except HTTPException:
                raise
            except Exception as e:
                if manually_created_session and current_db:
                    await run_in_threadpool(current_db.close)
                return {
                    "success": False,
                    "message": f"Error deleting email from distribution list: {str(e)}",
                    "details": {}
                }
        
        # If we've exhausted all retries
        return {
            "success": False,
            "message": "Error deleting email from distribution list: Database is locked after multiple retries. Please try again later.",
            "details": {}
        }
    finally:
        # Ensure any manually created session is closed
        if manually_created_session and current_db:
            try:
                await run_in_threadpool(current_db.close)
            except Exception:
                pass
