"""
Admin Options Routes - User Management APIs
"""
from datetime import datetime, date, timedelta
import time
import re
import hashlib
import base64
import json
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import hashes
from cryptography.hazmat.primitives.kdf.pbkdf2 import PBKDF2HMAC
from cryptography.fernet import Fernet
from fastapi import APIRouter, HTTPException, Depends
from sqlalchemy.orm import Session
from sqlalchemy.exc import OperationalError
from sqlalchemy import text, or_, and_
from pydantic import BaseModel, field_validator, EmailStr
from typing import Optional, List, Dict, Any

from app.core.database import get_db, SessionLocal
from app.models.user import User
from app.models.agent import AgentsAccess, AccessManage, AgentManagement, AgentAdmin
from app.models.question import InitialSuggestionQuestion


# Utility function for password validation
def is_valid_password(password: str) -> bool:
    """Validate password meets requirements"""
    return (
        len(password) >= 8 and
        re.search(r'[A-Z]', password) and
        re.search(r'[a-z]', password) and
        re.search(r'\d', password) and
        re.search(r'[!@#$%^&*(),.?":{}|<>]', password)
    )


# Utility function for token encryption
def encrypt_token(id: int, name: str, email: str) -> str:
    """Encrypt token using MD5 hash"""
    combined = f"{id}:{name}:{email}"
    return hashlib.md5(combined.encode()).hexdigest()


# Email helper function for agent creation
def get_agent_create_mail(agent_name: str, agent_access: str, agent_id: str, org_name: str, org_id: str) -> tuple:
    """Generate email subject and body for agent creation notification"""
    subject = "New Agent Created by Organization"

    body = f"""
    <html>
    <body>
        <p>Dear Team,</p>
        <p>A new agent has been created by the following organization:</p>
        <ul>
            <li><strong>Organization Name:</strong> {org_name}</li>
            <li><strong>Organization ID:</strong> {org_id}</li>
        </ul>
        <p>The details of the newly created agent are as follows:</p>
        <ul>
            <li><strong>Agent Name:</strong> {agent_name}</li>
            <li><strong>Agent Access:</strong> {agent_access}</li>
            <li><strong>Agent ID:</strong> {agent_id}</li>
        </ul>
        <p>Please initiate the process to issue a new license for this agent.</p>
        <p>Best regards,<br/>askme360 Team</p>
    </body>
    </html>
    """
    return subject, body


# Request/Response Schemas
class GetAllUsersRequest(BaseModel):
    agent_id: str


class GetAllUsersResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class CreateUserRequest(BaseModel):
    agent_id: str
    user_id: str
    name: str
    email: EmailStr
    contact: str
    password: str
    passkey: str
    end_date: Optional[str] = None  # If not provided, will default to 5 years from today
    status: str = "Active"  # "Active" or "Inactive"
    
    @field_validator('name')
    @classmethod
    def validate_name(cls, v):
        if not v or not v.strip():
            raise ValueError('Name cannot be empty')
        if len(v.strip()) < 2:
            raise ValueError('Name must be at least 2 characters long')
        return v.strip()
    
    @field_validator('contact')
    @classmethod
    def validate_contact(cls, v):
        if not v or not v.strip():
            raise ValueError('Phone number cannot be empty')
        # Remove any non-digit characters for validation
        digits_only = re.sub(r'[^0-9]', '', v)
        if len(digits_only) < 10:
            raise ValueError('Phone number must have at least 10 digits')
        return v.strip()
    
    @field_validator('password')
    @classmethod
    def validate_password(cls, v):
        if not is_valid_password(v):
            raise ValueError('Password must be min 8 characters with uppercase, lowercase, digit and special character')
        return v
    
    @field_validator('status')
    @classmethod
    def validate_status(cls, v):
        if v not in ["Active", "Inactive"]:
            raise ValueError('Status must be either "Active" or "Inactive"')
        return v


class CreateUserResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class UpdateUserRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # User ID to update
    name: str
    email: EmailStr
    contact: str
    password: str
    passkey: str
    end_date: Optional[str] = None
    status: str = "Active"
    
    @field_validator('name')
    @classmethod
    def validate_name(cls, v):
        if not v or not v.strip():
            raise ValueError('Name cannot be empty')
        if len(v.strip()) < 2:
            raise ValueError('Name must be at least 2 characters long')
        return v.strip()
    
    @field_validator('contact')
    @classmethod
    def validate_contact(cls, v):
        if not v or not v.strip():
            raise ValueError('Contact cannot be empty')
        # Remove any non-digit characters for validation
        digits_only = re.sub(r'[^0-9]', '', v)
        if len(digits_only) < 10:
            raise ValueError('Contact must have at least 10 digits')
        return v.strip()
    
    @field_validator('password')
    @classmethod
    def validate_password(cls, v):
        if not is_valid_password(v):
            raise ValueError('Password must be min 8 characters with uppercase, lowercase, digit and special character')
        return v
    
    @field_validator('status')
    @classmethod
    def validate_status(cls, v):
        if v not in ["Active", "Inactive"]:
            raise ValueError('Status must be either "Active" or "Inactive"')
        return v


class UpdateUserResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class DeleteUserRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # User ID to delete


class DeleteUserResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


router = APIRouter(prefix="/admin-options", tags=["Admin Options"])


@router.post("/get-all-users", response_model=GetAllUsersResponse)
def get_all_users(
    request: GetAllUsersRequest,
    db: Session = Depends(get_db)
):
    """
    Get all users.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Returns all user records from the user_manage table.
    
    Args:
        request: GetAllUsersRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        GetAllUsersResponse with success, message, and details containing users list
    """
    try:
        # Query all users, ordered by created_at ASC (matching reference)
        users = db.query(User).order_by(User.created_at.asc()).all()
        
        # Convert to dictionaries
        users_list = []
        for user in users:
            user_dict = {
                "id": user.id,
                "name": user.name if user.name else "",
                "email": user.email if user.email else "",
                "contact": str(user.contact) if user.contact else "",
                "password": user.password if user.password else "",
                "passkey": user.passkey if user.passkey else "",
                "end_date": user.end_date if user.end_date else "",
                "status": user.status if user.status else "",
                "user_token": user.user_token if user.user_token else "",
                "created_at": user.created_at if user.created_at else "",
                "updated_at": user.updated_at if user.updated_at else ""
            }
            users_list.append(user_dict)
        
        return {
            "success": True,
            "message": "Users retrieved successfully",
            "details": {
                "users": users_list
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": "Error retrieving users: {str(e)}",
            "details": {
                "users": []
            }
        }


@router.post("/create-user", response_model=CreateUserResponse, status_code=201)
def create_user(
    request: CreateUserRequest,
    db: Session = Depends(get_db)
):
    """
    Create a new user.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Creates a new user record with validation and token generation.
    Uses retry logic for database locks to prevent write conflicts.
    
    Args:
        request: CreateUserRequest containing user details
        db: Database session
    
    Returns:
        CreateUserResponse with success, message, and details containing the created user
    """
    # Check if email already exists (before retry loop)
    existing_user = db.query(User).filter(
        User.email.ilike(request.email.lower())
    ).first()
    
    if existing_user:
        return {
            "success": False,
            "message": "Email already exists. Please use a different email.",
            "details": {
                "user": None
            }
        }
    
    # Calculate end_date if not provided (default to 5 years from today)
    if not request.end_date:
        today = date.today()
        # Add 5 years (approximately 1825 days, accounting for leap years)
        end_date = today + timedelta(days=365*5 + 1)  # +1 for leap year
        end_date_str = end_date.strftime("%Y-%m-%d")
    else:
        end_date_str = request.end_date
    
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    
    for attempt in range(max_retries):
        try:
            # Create a new session for retries (after first attempt)
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Create timestamp
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                
                # Create new user (we'll get the ID after insert)
                new_user = User(
                    name=request.name,
                    email=request.email.lower(),  # Store email in lowercase
                    contact=request.contact,
                    password=request.password,
                    passkey=request.passkey,
                    end_date=end_date_str,
                    status=request.status,
                    user_token="",  # Will be updated after we get the ID
                    created_at=current_time,
                    updated_at=current_time
                )
                
                current_db.add(new_user)
                current_db.commit()
                current_db.refresh(new_user)
                
                # Generate token with the actual user ID
                token = encrypt_token(new_user.id, new_user.name, new_user.email)
                new_user.user_token = token
                current_db.commit()
                current_db.refresh(new_user)
                
                result = {
                    "success": True,
                    "message": "User created successfully",
                    "details": {
                        "user": {
                            "id": new_user.id,
                            "name": new_user.name,
                            "email": new_user.email,
                            "contact": str(new_user.contact),
                            "password": new_user.password,
                            "passkey": new_user.passkey,
                            "end_date": new_user.end_date,
                            "status": new_user.status,
                            "user_token": new_user.user_token,
                            "created_at": new_user.created_at,
                            "updated_at": new_user.updated_at
                        }
                    }
                }
                
                return result
                
            except HTTPException:
                raise
            except OperationalError as e:
                current_db.rollback()
                # Check if it's a database locked error
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    # Exponential backoff: wait longer for each retry
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": "Error creating user: Database is locked. Please try again later.",
                        "details": {
                            "user": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": "Error creating user: {str(e)}",
                    "details": {
                        "user": None
                    }
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": "Error creating user: {str(e)}",
                "details": {
                    "user": None
                }
            }
    
    # If we've exhausted all retries
    return {
        "success": False,
        "message": "Error creating user: Database is locked after multiple retries. Please try again later.",
        "details": {
            "user": None
        }
    }


@router.post("/update-user", response_model=UpdateUserResponse)
def update_user(
    request: UpdateUserRequest,
    db: Session = Depends(get_db)
):
    """
    Update an existing user.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Updates a user record with validation and token regeneration.
    Uses retry logic for database locks to prevent write conflicts.
    
    Args:
        request: UpdateUserRequest containing user ID and updated details
        db: Database session
    
    Returns:
        UpdateUserResponse with success, message, and details containing the updated user
    """
    # Check if user exists (before retry loop)
    existing_user = db.query(User).filter(User.id == request.id).first()
    
    if not existing_user:
        return {
            "success": False,
            "message": "User not found",
            "details": {
                "user": None
            }
        }
    
    # Check if email is being changed and if new email already exists
    if existing_user.email.lower() != request.email.lower():
        email_exists = db.query(User).filter(
            User.email.ilike(request.email.lower()),
            User.id != request.id
        ).first()
        
        if email_exists:
            return {
                "success": False,
                "message": "Email already exists. Please use a different email.",
                "details": {
                    "user": None
                }
            }
    
    # Use existing end_date if not provided
    end_date_str = request.end_date if request.end_date else existing_user.end_date
    
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    
    for attempt in range(max_retries):
        try:
            # Create a new session for retries (after first attempt)
            if attempt > 0:
                current_db = SessionLocal()
                # Re-fetch user in new session
                existing_user = current_db.query(User).filter(User.id == request.id).first()
                if not existing_user:
                    return {
                        "success": False,
                        "message": "User not found",
                        "details": {
                            "user": None
                        }
                    }
            
            try:
                # Create timestamp
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                
                # Update user fields
                existing_user.name = request.name
                existing_user.email = request.email.lower()  # Store email in lowercase
                existing_user.contact = request.contact
                existing_user.password = request.password
                existing_user.passkey = request.passkey
                existing_user.end_date = end_date_str
                existing_user.status = request.status
                existing_user.updated_at = current_time
                
                # Regenerate token with updated name and email
                token = encrypt_token(existing_user.id, existing_user.name, existing_user.email)
                existing_user.user_token = token
                
                current_db.commit()
                current_db.refresh(existing_user)
                
                result = {
                    "success": True,
                    "message": "User updated successfully",
                    "details": {
                        "user": {
                            "id": existing_user.id,
                            "name": existing_user.name,
                            "email": existing_user.email,
                            "contact": str(existing_user.contact),
                            "password": existing_user.password,
                            "passkey": existing_user.passkey,
                            "end_date": existing_user.end_date,
                            "status": existing_user.status,
                            "user_token": existing_user.user_token,
                            "created_at": existing_user.created_at,
                            "updated_at": existing_user.updated_at
                        }
                    }
                }
                
                return result
                
            except HTTPException:
                raise
            except OperationalError as e:
                current_db.rollback()
                # Check if it's a database locked error
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    # Exponential backoff: wait longer for each retry
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": "Error updating user: Database is locked. Please try again later.",
                        "details": {
                            "user": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": "Error updating user: {str(e)}",
                    "details": {
                        "user": None
                    }
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": "Error updating user: {str(e)}",
                "details": {
                    "user": None
                }
            }
    
    # If we've exhausted all retries
    return {
        "success": False,
        "message": "Error updating user: Database is locked after multiple retries. Please try again later.",
        "details": {
            "user": None
        }
    }


@router.post("/delete-user", response_model=DeleteUserResponse)
def delete_user(
    request: DeleteUserRequest,
    db: Session = Depends(get_db)
):
    """
    Delete an existing user.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Deletes a user record by ID.
    Uses retry logic for database locks to prevent write conflicts.
    
    Args:
        request: DeleteUserRequest containing agent_id, user_id, and id (user ID to delete)
        db: Database session
    
    Returns:
        DeleteUserResponse with success, message, and details containing the deleted user id
    """
    max_retries = 5
    retry_delay = 0.1  # Start with 100ms delay
    
    # Use the provided session for the first attempt
    current_db = db
    
    for attempt in range(max_retries):
        try:
            # Create a new session for retries (after first attempt)
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if user exists
                user = current_db.query(User).filter(User.id == request.id).first()
                
                if not user:
                    return {
                        "success": False,
                        "message": "User not found",
                        "details": {
                            "user_id": None
                        }
                    }
                
                # Prevent deletion of user with id=1 (admin user)
                if user.id == 1:
                    return {
                        "success": False,
                        "message": "Cannot delete admin user (id=1)",
                        "details": {
                            "user_id": None
                        }
                    }
                
                # Store user details before deletion for response
                user_id = user.id
                user_name = user.name
                user_email = user.email
                
                # Delete the user
                current_db.delete(user)
                current_db.commit()
                
                result = {
                    "success": True,
                    "message": "User deleted successfully",
                    "details": {
                        "id": user_id,
                        "name": user_name,
                        "email": user_email
                    }
                }
                
                return result
                
            except HTTPException:
                raise
            except OperationalError as e:
                current_db.rollback()
                # Check if it's a database locked error
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    # Exponential backoff: wait longer for each retry
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": "Error deleting user: Database is locked. Please try again later.",
                        "details": {
                            "user_id": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": "Error deleting user: {str(e)}",
                    "details": {
                        "user_id": None
                    }
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": "Error deleting user: {str(e)}",
                "details": {
                    "user_id": None
                }
            }
    
    # If we've exhausted all retries
    return {
        "success": False,
        "message": "Error deleting user: Database is locked after multiple retries. Please try again later.",
        "details": {
            "user_id": None
        }
    }


# ========== LICENSE MANAGEMENT APIs ==========

# License key decryption utilities
def generate_key(custom_key: str, password: str) -> bytes:
    """Generate an encryption key using a custom key and password."""
    password_bytes = password.encode()
    salt = custom_key.encode()
    
    kdf = PBKDF2HMAC(
        algorithm=hashes.SHA256(),
        length=32,
        salt=salt,
        iterations=100000,
        backend=default_backend()
    )
    
    key = base64.urlsafe_b64encode(kdf.derive(password_bytes))
    return key


def decrypt_text(encrypted_text: str, key: bytes) -> Optional[str]:
    """Decrypt text using the provided key."""
    try:
        encrypted_data = base64.urlsafe_b64decode(encrypted_text)
        f = Fernet(key)
        decrypted_data = f.decrypt(encrypted_data)
        return decrypted_data.decode()
    except Exception as e:
        print(f"Decryption failed: {e}")
        return None


def generate_license_info(license_key: str, db: Session) -> Optional[Dict]:
    """Generate license info from encrypted license key"""
    try:
        # Check if license key already exists
        existing_key = db.query(AgentsAccess).filter(
            AgentsAccess.access_key == license_key
        ).first()
        
        print(existing_key)
        if existing_key:
            return False  # Key already exists
        
        # Decrypt the license key
        decryption_key = generate_key("10ed53b336edef235f1091a019caa353", "dc647eb65e6711e155375218212b3964")
        query = decrypt_text(license_key, decryption_key)
        
        if not query:
            return None
        
        # Parse the decrypted query
        pairs = query.split("&")
        parsed = {}
        for pair in pairs:
            if "=" in pair:
                key, value = pair.split("=", 1)
                parsed[key] = value.strip('"')
        
        # Extract license information
        agent_id = parsed.get("agent_id")
        agent_name = parsed.get("agent")
        license_count = parsed.get("count")
        license_number = parsed.get("license_number")
        start_date = parsed.get("start")
        end_date = parsed.get("end")
        org_id = parsed.get("org")
        
        # Check if license already exists (renewal)
        license_type = "new"
        existing_license = db.query(AgentsAccess).filter(
            AgentsAccess.license_number == license_number
        ).first()
        
        if existing_license:
            license_type = "renew"
        
        return {
            "agent_id": agent_id,
            "agent_name": agent_name,
            "license_count": license_count,
            "license_number": license_number,
            "start_date": start_date,
            "end_date": end_date,
            "license_type": license_type
        }
    except Exception as e:
        print(f"Error generating license info: {e}")
        return None


# License Management Schemas
class GetAllLicensesRequest(BaseModel):
    agent_id: str
    user_id: str


class GetAllLicensesResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class CreateLicenseRequest(BaseModel):
    agent_id: str
    user_id: str
    isValidate: bool = False
    license_key: str  # Encrypted license key


class CreateLicenseResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class UpdateLicenseRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # License record ID
    license_count: Optional[str] = None
    remain_license_count: Optional[str] = None
    end_date: Optional[str] = None
    agent_name: Optional[str] = None
    active: Optional[int] = None  # 0 or 1


class UpdateLicenseResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class DeleteLicenseRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # License record ID


class DeleteLicenseResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


@router.post("/get-all-licenses", response_model=GetAllLicensesResponse)
def get_all_licenses(
    request: GetAllLicensesRequest,
    db: Session = Depends(get_db)
):
    """
    Get all license records (agents_access).
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Returns all license records from agents_access table.
    
    Args:
        request: GetAllLicensesRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        GetAllLicensesResponse with success, message, and details containing licenses list
    """
    try:
        # Query all licenses from agents_access table
        licenses = db.query(AgentsAccess).order_by(AgentsAccess.created_at.desc()).all()
        
        # Convert to dictionaries
        licenses_list = []
        for license_record in licenses:
            license_dict = {
                "id": license_record.id,
                "agent_id": license_record.agent_id,
                "agent_name": license_record.agent_name,
                "access_key": license_record.access_key,
                "license_number": license_record.license_number,
                "license_count": license_record.license_count,
                "remain_license_count": license_record.remain_license_count,
                "start_date": license_record.start_date,
                "end_date": license_record.end_date if license_record.end_date else "",
                "active": license_record.active,
                "created_at": license_record.created_at if license_record.created_at else "",
                "updated_at": license_record.updated_at if license_record.updated_at else ""
            }
            licenses_list.append(license_dict)
        
        return {
            "success": True,
            "message": "Licenses retrieved successfully",
            "details": {
                "licenses": licenses_list
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": "Error retrieving licenses: {str(e)}",
            "details": {
                "licenses": []
            }
        }


@router.post("/create-license", response_model=CreateLicenseResponse, status_code=201)
def create_license(
    request: CreateLicenseRequest,
    db: Session = Depends(get_db)
):
    """
    Create a new license by validating and decrypting the license key.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Validates the license key, decrypts it, and creates a new license record.
    Handles both new licenses and renewals.
    
    Args:
        request: CreateLicenseRequest containing agent_id, user_id, and license_key
        db: Database session
    
    Returns:
        CreateLicenseResponse with success, message, and details containing license info
    """
    # Validate license key
    if not request.license_key or not request.license_key.strip():
        return {
            "success": False,
            "message": "License key is required",
            "details": {
                "license": None
            }
        }
    
    # Generate license info from encrypted key
    license_info = generate_license_info(request.license_key, db)
    
    if license_info is None:
        return {
            "success": False,
            "message": "Invalid license key. Decryption failed.",
            "details": {
                "license": None
            }
        }
    
    if license_info is False:
        return {
            "success": False,
            "message": "License key already exists",
            "details": {
                "license": None
            }
        }
    
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    print("license_info: ", license_info)
    print("isValidate: ", request.isValidate)
    if not request.isValidate:
        return {
            "success": True,
            "message": "License key is validated successfully",
            "details": {
                "license": {
                                "agent_id": license_info["agent_id"],
                                "agent_name": license_info["agent_name"],
                                "license_number": license_info["license_number"],
                                "license_count": license_info["license_count"],
                                "remain_license_count": license_info["license_count"],
                                "start_date": license_info["start_date"],
                                "end_date": license_info["end_date"],
                                "license_type": license_info["license_type"]
                            }
            }
        }
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                
                if license_info["license_type"] == "renew":
                    # Update existing license
                    license_record = current_db.query(AgentsAccess).filter(
                        AgentsAccess.license_number == license_info["license_number"]
                    ).first()
                    
                    if license_record:
                        license_record.license_count = license_info["license_count"]
                        license_record.end_date = license_info["end_date"]
                        license_record.updated_at = current_time
                    
                    # Also update access_manage table
                    access_records = current_db.query(AccessManage).filter(
                        AccessManage.license_number == license_info["license_number"]
                    ).all()
                    
                    for access_record in access_records:
                        access_record.end_date = license_info["end_date"]
                        access_record.updated_at = current_time
                    
                    current_db.commit()
                    
                    # Refresh to get updated values
                    if license_record:
                        current_db.refresh(license_record)
                    
                    if not license_record:
                        return {
                            "success": False,
                            "message": f"License with license_number {license_info['license_number']} not found",
                            "details": {
                                "license": None
                            }
                        }
                    
                    return {
                        "success": True,
                        "message": f"License renewed successfully. Expiration date extended to {license_info['end_date']}",
                        "details": {
                            "license": {
                                "id": license_record.id,
                                "agent_id": license_record.agent_id,
                                "agent_name": license_record.agent_name,
                                "license_number": license_record.license_number,
                                "license_count": license_record.license_count,
                                "remain_license_count": license_record.remain_license_count,
                                "start_date": license_record.start_date,
                                "end_date": license_record.end_date,
                                "license_type": "renew"
                            }
                        }
                    }
                else:
                    # Create new license
                    new_license = AgentsAccess(
                        agent_id=license_info["agent_id"],
                        agent_name=license_info["agent_name"],
                        access_key=request.license_key,
                        license_number=license_info["license_number"],
                        license_count=license_info["license_count"],
                        remain_license_count=license_info["license_count"],
                        start_date=license_info["start_date"],
                        end_date=license_info["end_date"],
                        active=1,
                        created_at=current_time,
                        updated_at=current_time
                    )
                    
                    current_db.add(new_license)
                    current_db.commit()
                    current_db.refresh(new_license)
                    
                    return {
                        "success": True,
                        "message": "License created successfully",
                        "details": {
                            "license": {
                                "id": new_license.id,
                                "agent_id": new_license.agent_id,
                                "agent_name": new_license.agent_name,
                                "license_number": new_license.license_number,
                                "license_count": new_license.license_count,
                                "remain_license_count": new_license.remain_license_count,
                                "start_date": new_license.start_date,
                                "end_date": new_license.end_date,
                                "license_type": "new"
                            }
                        }
                    }
            
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": "Error creating license: Database is locked. Please try again later.",
                        "details": {
                            "license": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error creating license: {str(e)}",
                    "details": {
                        "license": None
                    }
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": f"Error creating license: {str(e)}",
                "details": {
                    "license": None
                }
            }
    
    return {
        "success": False,
        "message": "Error creating license: Database is locked after multiple retries. Please try again later.",
        "details": {
            "license": None
        }
    }


@router.post("/update-license", response_model=UpdateLicenseResponse)
def update_license(
    request: UpdateLicenseRequest,
    db: Session = Depends(get_db)
):
    """
    Update an existing license record.
    
    Mirrors the functionality from updated/user_management/user_managment_db.py
    Updates license fields directly without requiring encrypted license key.
    Supports updating license_count, remain_license_count, end_date, agent_name, and active status.
    
    Args:
        request: UpdateLicenseRequest containing id and optional fields to update
        db: Database session
    
    Returns:
        UpdateLicenseResponse with success, message, and details containing updated license
    """
    # Validate that at least one field is provided for update
    if not any([request.license_count, request.remain_license_count, request.end_date, 
                request.agent_name, request.active is not None]):
        return {
            "success": False,
            "message": "At least one field (license_count, remain_license_count, end_date, agent_name, active) must be provided for update",
            "details": {
                "license": None
            }
        }
    
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if license exists
                license_record = current_db.query(AgentsAccess).filter(
                    AgentsAccess.id == request.id
                ).first()
                
                if not license_record:
                    return {
                        "success": False,
                        "message": f"License with id {request.id} not found",
                        "details": {
                            "license": None
                        }
                    }
                
                # Store license_number for potential access_manage update
                license_number = license_record.license_number
                
                # Build update fields dynamically
                update_fields = {}
                
                if request.license_count is not None:
                    update_fields["license_count"] = request.license_count
                    # Validate: remain_license_count should not exceed license_count
                    if request.remain_license_count is None:
                        current_remain = int(license_record.remain_license_count) if license_record.remain_license_count else 0
                        new_count = int(request.license_count) if request.license_count else 0
                        if current_remain > new_count:
                            return {
                                "success": False,
                                "message": f"remain_license_count ({current_remain}) cannot exceed license_count ({new_count})",
                                "details": {
                                    "license": None
                                }
                            }
                
                if request.remain_license_count is not None:
                    update_fields["remain_license_count"] = request.remain_license_count
                    # Validate: remain_license_count should not exceed license_count
                    current_count = int(request.license_count) if request.license_count else int(license_record.license_count) if license_record.license_count else 0
                    new_remain = int(request.remain_license_count) if request.remain_license_count else 0
                    if new_remain > current_count:
                        return {
                            "success": False,
                            "message": f"remain_license_count ({new_remain}) cannot exceed license_count ({current_count})",
                            "details": {
                                "license": None
                            }
                        }
                
                if request.end_date is not None:
                    update_fields["end_date"] = request.end_date
                    # If end_date is updated, also update access_manage table
                    # This will be done after the main update
                
                if request.agent_name is not None:
                    update_fields["agent_name"] = request.agent_name
                
                if request.active is not None:
                    if request.active not in [0, 1]:
                        return {
                            "success": False,
                            "message": "active must be either 0 or 1",
                            "details": {
                                "license": None
                            }
                        }
                    update_fields["active"] = request.active
                
                # Add updated_at
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                update_fields["updated_at"] = current_time
                
                # Update the license record
                for key, value in update_fields.items():
                    setattr(license_record, key, value)
                
                current_db.commit()
                current_db.refresh(license_record)
                
                # If end_date was updated, also update access_manage table
                if request.end_date is not None:
                    # Update all access_manage records with matching license_number using ORM
                    access_records = current_db.query(AccessManage).filter(
                        AccessManage.license_number == license_number
                    ).all()
                    
                    for access_record in access_records:
                        access_record.end_date = request.end_date
                        access_record.updated_at = current_time
                    
                    current_db.commit()
                
                return {
                    "success": True,
                    "message": "License updated successfully",
                    "details": {
                        "license": {
                            "id": license_record.id,
                            "agent_id": license_record.agent_id,
                            "agent_name": license_record.agent_name,
                            "license_number": license_record.license_number,
                            "license_count": license_record.license_count,
                            "remain_license_count": license_record.remain_license_count,
                            "start_date": license_record.start_date,
                            "end_date": license_record.end_date if license_record.end_date else "",
                            "active": license_record.active,
                            "created_at": license_record.created_at if license_record.created_at else "",
                            "updated_at": license_record.updated_at if license_record.updated_at else ""
                        }
                    }
                }
            
            except HTTPException:
                raise
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error updating license: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "license": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error updating license: {str(e)}",
                    "details": {
                        "license": None
                    }
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": f"Error updating license: {str(e)}",
                "details": {
                    "license": None
                }
            }
    
    return {
        "success": False,
        "message": "Error updating license: Database is locked after multiple retries. Please try again later.",
        "details": {
            "license": None
        }
    }


@router.post("/delete-license", response_model=DeleteLicenseResponse)
def delete_license(
    request: DeleteLicenseRequest,
    db: Session = Depends(get_db)
):
    """
    Delete a license record.
    
    Mirrors the functionality from updated/user_management/user_managment_db.py
    Deletes the license record from agents_access table.
    Note: This will not delete related access_manage records, but they will become orphaned.
    Consider checking for related access records before deletion.
    
    Args:
        request: DeleteLicenseRequest containing id (license record ID)
        db: Database session
    
    Returns:
        DeleteLicenseResponse with success, message, and details containing deleted license info
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if license exists
                license_record = current_db.query(AgentsAccess).filter(
                    AgentsAccess.id == request.id
                ).first()
                
                if not license_record:
                    return {
                        "success": False,
                        "message": f"License with id {request.id} not found",
                        "details": {
                            "license": None
                        }
                    }
                
                # Check if there are any related access_manage records using ORM
                related_access_count = current_db.query(AccessManage).filter(
                    AccessManage.license_number == license_record.license_number
                ).count()
                
                # Store license info for response
                license_info = {
                    "id": license_record.id,
                    "agent_id": license_record.agent_id,
                    "agent_name": license_record.agent_name,
                    "license_number": license_record.license_number,
                    "license_count": license_record.license_count,
                    "remain_license_count": license_record.remain_license_count
                }
                
                # Delete the license record
                current_db.delete(license_record)
                current_db.commit()
                
                response_details = {
                    "deleted_license": license_info
                }
                
                if related_access_count > 0:
                    response_details["warning"] = f"{related_access_count} related access record(s) still exist for this license. They may need to be cleaned up separately."
                
                return {
                    "success": True,
                    "message": "License deleted successfully",
                    "details": response_details
                }
            
            except HTTPException:
                raise
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error deleting license: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "license": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error deleting license: {str(e)}",
                    "details": {
                        "license": None
                    }
                }
        except HTTPException:
            raise
        except Exception as e:
            return {
                "success": False,
                "message": f"Error deleting license: {str(e)}",
                "details": {
                    "license": None
                }
            }
    
    return {
        "success": False,
        "message": "Error deleting license: Database is locked after multiple retries. Please try again later.",
        "details": {
            "license": None
        }
    }


# ========== ACCESS MANAGEMENT APIs ==========

# Access Management Schemas
class GetAllAccessRequest(BaseModel):
    agent_id: str
    user_id: str


class GetAllAccessResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class CreateAccessRequest(BaseModel):
    agent_id: str
    user_id: str
    user_id_for_access: str  # The user ID to grant access to
    license_number: str  # License number to use


class CreateAccessResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class UpdateAccessRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # Access record ID
    user_id_for_access: Optional[str] = None  # Optional: new user_id
    license_number: Optional[str] = None  # Optional: new license_number
    start_date: Optional[str] = None  # Optional: new start_date
    end_date: Optional[str] = None  # Optional: new end_date


class UpdateAccessResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class DeleteAccessRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # Access record ID


class DeleteAccessResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


@router.post("/get-all-access", response_model=GetAllAccessResponse)
def get_all_access(
    request: GetAllAccessRequest,
    db: Session = Depends(get_db)
):
    """
    Get all access records (access_manage).
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Returns all access records from access_manage table.
    
    Args:
        request: GetAllAccessRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        GetAllAccessResponse with success, message, and details containing access_records list
    """
    try:
        # Query all access records from access_manage table
        access_records = db.query(AccessManage).order_by(AccessManage.created_at.desc()).all()
        
        # Convert to dictionaries
        access_list = []
        for record in access_records:
            access_dict = {
                "id": record.id,
                "user_id": record.user_id,
                "user_name": record.user_name,
                "agent_id": record.agent_id,
                "agent_name": record.agent_name,
                "license_number": record.license_number,
                "start_date": record.start_date,
                "end_date": record.end_date,
                "created_at": record.created_at if record.created_at else "",
                "updated_at": record.updated_at if record.updated_at else ""
            }
            access_list.append(access_dict)
        
        return {
            "success": True,
            "message": "Access records retrieved successfully",
            "details": {
                "access_records": access_list
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving access records: {str(e)}",
            "details": {
                "access_records": []
            }
        }


@router.post("/create-access", response_model=CreateAccessResponse, status_code=201)
def create_access(
    request: CreateAccessRequest,
    db: Session = Depends(get_db)
):
    """
    Create a new access record.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Creates an access record and decrements the remaining license count.
    
    Args:
        request: CreateAccessRequest containing user_id_for_access and license_number
        db: Database session
    
    Returns:
        CreateAccessResponse with success, message, and details containing access record
    """
    # Validate inputs
    if not request.user_id_for_access or not request.user_id_for_access.strip():
        return {
            "success": False,
            "message": "user_id_for_access is required",
            "details": {
                "access_record": None
            }
        }
    
    if not request.license_number or not request.license_number.strip():
        return {
            "success": False,
            "message": "license_number is required",
            "details": {
                "access_record": None
            }
        }
    
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Get user info to get user_name
                user_record = current_db.query(User).filter(
                    User.id == int(request.user_id_for_access)
                ).first()
                
                if not user_record:
                    return {
                        "success": False,
                        "message": f"User with id {request.user_id_for_access} not found",
                        "details": {
                            "access_record": None
                        }
                    }
                
                user_name = user_record.name
                
                # Get license info by license_number
                license_record = current_db.query(AgentsAccess).filter(
                    AgentsAccess.license_number == request.license_number
                ).first()
                
                if not license_record:
                    return {
                        "success": False,
                        "message": f"License with license_number {request.license_number} not found",
                        "details": {
                            "access_record": None
                        }
                    }
                
                agent_id = license_record.agent_id
                agent_name = license_record.agent_name
                remain_count = int(license_record.remain_license_count) if license_record.remain_license_count else 0
                start_date = license_record.start_date
                end_date = license_record.end_date
                
                # Validate remaining license count
                if remain_count <= 0:
                    return {
                        "success": False,
                        "message": "No remaining licenses available for this license number",
                        "details": {
                            "access_record": None
                        }
                    }
                
                # Validate dates
                if start_date and end_date and start_date > end_date:
                    return {
                        "success": False,
                        "message": "Start date cannot be after end date",
                        "details": {
                            "access_record": None
                        }
                    }
                
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                
                # Create access record
                new_access = AccessManage(
                    user_id=request.user_id_for_access,
                    user_name=user_name,
                    agent_id=agent_id,
                    agent_name=agent_name,
                    license_number=request.license_number,
                    start_date=start_date,
                    end_date=end_date,
                    created_at=current_time,
                    updated_at=current_time
                )
                
                current_db.add(new_access)
                current_db.flush()  # Flush to get the ID
                
                # Decrement license count
                new_remain_count = remain_count - 1
                license_record.remain_license_count = str(new_remain_count)
                license_record.updated_at = current_time
                
                current_db.commit()
                current_db.refresh(new_access)
                
                return {
                    "success": True,
                    "message": "Access record created successfully",
                    "details": {
                        "access_record": {
                            "id": new_access.id,
                            "user_id": new_access.user_id,
                            "user_name": new_access.user_name,
                            "agent_id": new_access.agent_id,
                            "agent_name": new_access.agent_name,
                            "license_number": new_access.license_number,
                            "start_date": new_access.start_date,
                            "end_date": new_access.end_date,
                            "created_at": new_access.created_at,
                            "updated_at": new_access.updated_at
                        },
                        "remaining_license_count": new_remain_count
                    }
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "access_record": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error creating access record: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "access_record": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error creating access record: {str(e)}",
                    "details": {
                        "access_record": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "access_record": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error creating access record: {str(e)}",
                "details": {
                    "access_record": None
                }
            }
    
    return {
        "success": False,
        "message": "Error creating access record: Database is locked after multiple retries. Please try again later.",
        "details": {
            "access_record": None
        }
    }


@router.post("/update-access", response_model=UpdateAccessResponse)
def update_access(
    request: UpdateAccessRequest,
    db: Session = Depends(get_db)
):
    """
    Update an existing access record.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Updates access record fields. Validates start_date <= end_date.
    
    Args:
        request: UpdateAccessRequest containing id and optional fields to update
        db: Database session
    
    Returns:
        UpdateAccessResponse with success, message, and details containing updated access record
    """
    # Validate that at least one field is provided for update
    if not any([request.user_id_for_access, request.license_number, request.start_date, request.end_date]):
        return {
            "success": False,
            "message": "At least one field (user_id_for_access, license_number, start_date, end_date) must be provided for update",
            "details": {
                "access_record": None
            }
        }
    
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if access record exists
                access_record = current_db.query(AccessManage).filter(
                    AccessManage.id == request.id
                ).first()
                
                if not access_record:
                    return {
                        "success": False,
                        "message": f"Access record with id {request.id} not found",
                        "details": {
                            "access_record": None
                        }
                    }
                
                # Build update fields
                update_fields = {}
                user_name = access_record.user_name
                
                # Update user_id and user_name if provided
                if request.user_id_for_access:
                    user_record = current_db.query(User).filter(
                        User.id == int(request.user_id_for_access)
                    ).first()
                    
                    if not user_record:
                        return {
                            "success": False,
                            "message": f"User with id {request.user_id_for_access} not found",
                            "details": {
                                "access_record": None
                            }
                        }
                    
                    update_fields["user_id"] = request.user_id_for_access
                    update_fields["user_name"] = user_record.name
                    user_name = user_record.name
                
                # Update license_number, agent_id, agent_name if provided
                if request.license_number:
                    license_record = current_db.query(AgentsAccess).filter(
                        AgentsAccess.license_number == request.license_number
                    ).first()
                    
                    if not license_record:
                        return {
                            "success": False,
                            "message": f"License with license_number {request.license_number} not found",
                            "details": {
                                "access_record": None
                            }
                        }
                    
                    update_fields["license_number"] = request.license_number
                    update_fields["agent_id"] = license_record.agent_id
                    update_fields["agent_name"] = license_record.agent_name
                
                # Update dates if provided
                if request.start_date:
                    update_fields["start_date"] = request.start_date
                else:
                    update_fields["start_date"] = access_record.start_date
                
                if request.end_date:
                    update_fields["end_date"] = request.end_date
                else:
                    update_fields["end_date"] = access_record.end_date
                
                # Validate start_date <= end_date
                if update_fields["start_date"] and update_fields["end_date"]:
                    if update_fields["start_date"] > update_fields["end_date"]:
                        return {
                            "success": False,
                            "message": "Start date cannot be after end date",
                            "details": {
                                "access_record": None
                            }
                        }
                
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                update_fields["updated_at"] = current_time
                
                # Update the record
                for key, value in update_fields.items():
                    setattr(access_record, key, value)
                
                current_db.commit()
                current_db.refresh(access_record)
                
                return {
                    "success": True,
                    "message": "Access record updated successfully",
                    "details": {
                        "access_record": {
                            "id": access_record.id,
                            "user_id": access_record.user_id,
                            "user_name": access_record.user_name,
                            "agent_id": access_record.agent_id,
                            "agent_name": access_record.agent_name,
                            "license_number": access_record.license_number,
                            "start_date": access_record.start_date,
                            "end_date": access_record.end_date,
                            "created_at": access_record.created_at,
                            "updated_at": access_record.updated_at
                        }
                    }
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "access_record": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error updating access record: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "access_record": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error updating access record: {str(e)}",
                    "details": {
                        "access_record": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "access_record": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error updating access record: {str(e)}",
                "details": {
                    "access_record": None
                }
            }
    
    return {
        "success": False,
        "message": "Error updating access record: Database is locked after multiple retries. Please try again later.",
        "details": {
            "access_record": None
        }
    }


@router.post("/delete-access", response_model=DeleteAccessResponse)
def delete_access(
    request: DeleteAccessRequest,
    db: Session = Depends(get_db)
):
    """
    Delete an access record.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Deletes the access record and increments the remaining license count.
    
    Args:
        request: DeleteAccessRequest containing id (access record ID)
        db: Database session
    
    Returns:
        DeleteAccessResponse with success, message, and details
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if access record exists
                access_record = current_db.query(AccessManage).filter(
                    AccessManage.id == request.id
                ).first()
                
                if not access_record:
                    return {
                        "success": False,
                        "message": f"Access record with id {request.id} not found",
                        "details": {
                            "access_record": None
                        }
                    }
                
                # Store license_number before deletion
                license_number = access_record.license_number
                
                # Get current remaining license count
                license_record = current_db.query(AgentsAccess).filter(
                    AgentsAccess.license_number == license_number
                ).first()
                
                if license_record:
                    current_remain_count = int(license_record.remain_license_count) if license_record.remain_license_count else 0
                    new_remain_count = current_remain_count + 1
                else:
                    new_remain_count = None
                
                # Store access info for response
                access_info = {
                    "id": access_record.id,
                    "user_id": access_record.user_id,
                    "user_name": access_record.user_name,
                    "agent_id": access_record.agent_id,
                    "agent_name": access_record.agent_name,
                    "license_number": access_record.license_number
                }
                
                # Delete the access record
                current_db.delete(access_record)
                
                # Increment license count if license exists
                if new_remain_count is not None and license_record:
                    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    license_record.remain_license_count = str(new_remain_count)
                    license_record.updated_at = current_time
                
                current_db.commit()
                
                response_details = {
                    "deleted_access": access_info
                }
                
                if new_remain_count is not None:
                    response_details["remaining_license_count"] = new_remain_count
                
                return {
                    "success": True,
                    "message": "Access record deleted successfully",
                    "details": response_details
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "access_record": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error deleting access record: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "access_record": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error deleting access record: {str(e)}",
                    "details": {
                        "access_record": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "access_record": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error deleting access record: {str(e)}",
                "details": {
                    "access_record": None
                }
            }
    
    return {
        "success": False,
        "message": "Error deleting access record: Database is locked after multiple retries. Please try again later.",
        "details": {
            "access_record": None
        }
    }


# ==================== AGENT MANAGEMENT APIs ====================

class GetAllAgentsRequest(BaseModel):
    agent_id: str
    user_id: str


class GetAllAgentsResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class CreateAgentRequest(BaseModel):
    agent_id: str
    user_id: str
    agent_name: str
    agent_access: str
    init_training_data: str
    endpoint: str
    max_row_count: str
    database: Optional[bool] = False  # Checkbox for database data source
    api: Optional[bool] = False  # Checkbox for API data source
    file: Optional[bool] = False  # Checkbox for File data source
    default_data_source_type: str  # "Database", "API", or "File" (selected from checkboxes)
    data_source_type: str  # "Default Data Source Type" or "Custom Data Source Type"
    db_type: Optional[str] = None  # "SQL" or "Oracle" (only if Custom Data Source Type)
    host: Optional[str] = None  # Database host (only if Custom Data Source Type and SQL)
    port: Optional[str] = None  # Database port (only if Custom Data Source Type and SQL)
    username: Optional[str] = None  # Database username
    password: Optional[str] = None  # Database password
    db_name: Optional[str] = None  # Database name (only if Custom Data Source Type and SQL)
    connection: Optional[str] = None  # Connection string (only if Custom Data Source Type and Oracle)
    theme: Optional[Dict[str, Any]] = None  # Theme configuration (optional)
    oci_profile: Optional[str] = None  # OCI profile (optional, saved to extra2)
    
   


class CreateAgentResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class UpdateAgentRequest(BaseModel):
    agent_id: str
    user_id: str
    agent_name: Optional[str] = None
    agent_access: Optional[str] = None
    init_training_data: Optional[str] = None
    endpoint: Optional[str] = None
    max_row_count: Optional[str] = None
    database: Optional[bool] = None  # Checkbox for database data source
    api: Optional[bool] = None  # Checkbox for API data source
    file: Optional[bool] = None  # Checkbox for File data source
    default_data_source_type: Optional[str] = None  # "Database", "API", or "File"
    data_source_type: Optional[str] = None  # "Default Data Source Type" or "Custom Data Source Type"
    db_type: Optional[str] = None  # "SQL" or "Oracle"
    host: Optional[str] = None  # Database host
    port: Optional[str] = None  # Database port
    username: Optional[str] = None  # Database username
    password: Optional[str] = None  # Database password
    db_name: Optional[str] = None  # Database name
    connection: Optional[str] = None  # Connection string
    theme: Optional[Dict[str, Any]] = None  # Theme configuration (optional)
    oci_profile: Optional[str] = None  # OCI profile (optional, saved to extra2)
    

class UpdateAgentResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class DeleteAgentRequest(BaseModel):
    agent_id: str
    user_id: str


class DeleteAgentResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


@router.post("/get-all-agents", response_model=GetAllAgentsResponse)
def get_all_agents(
    request: GetAllAgentsRequest,
    db: Session = Depends(get_db)
):
    """
    Get all agents from agent_management table.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Returns all agents ordered by created_at ASC.
    
    Args:
        request: GetAllAgentsRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        GetAllAgentsResponse with success, message, and details containing agents list
    """
    try:
        # Query all agents using ORM
        agent_records = db.query(AgentManagement).order_by(AgentManagement.created_at.asc()).all()
        
        agents = []
        for agent in agent_records:
            agent_dict = {
                "agent_id": agent.agent_id,
                "agent_name": agent.agent_name,
                "agent_access": agent.agent_access,
                "init_training_data": agent.init_training_data,
                "endpoint": agent.endpoint,
                "extra1": agent.extra1 if agent.extra1 else "",
                "extra2": agent.extra2 if agent.extra2 else "",
                "created_at": agent.created_at if agent.created_at else "",
                "updated_at": agent.updated_at if agent.updated_at else "",
                "admin_id": agent.admin_id if agent.admin_id else "",
                "max_row_count": agent.max_row_count if agent.max_row_count else "",
                "database": agent.database if agent.database else "",
                "api": agent.api if agent.api else "",
                "file": agent.file if agent.file else "",
                "default_data_source_type": agent.default_data_source_type if agent.default_data_source_type else "",
                "data_source_type": agent.data_source_type if agent.data_source_type else "",
                "db_type": agent.db_type if agent.db_type else "",
                "host": agent.host if agent.host else "",
                "port": agent.port if agent.port else "",
                "username": agent.username if agent.username else "",
                "password": agent.password if agent.password else "",
                "db_name": agent.db_name if agent.db_name else "",
                "connection": agent.connection if agent.connection else ""
            }
            agents.append(agent_dict)
        
        return {
            "success": True,
            "message": "Agents retrieved successfully",
            "details": {
                "agents": agents
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving agents: {str(e)}",
            "details": {
                "agents": []
            }
        }


@router.post("/create-agent", response_model=CreateAgentResponse)
def create_agent(
    request: CreateAgentRequest,
    db: Session = Depends(get_db)
):
    """
    Create a new agent and generate initial suggested questions.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Creates an agent in agent_management table and generates initial suggested questions
    using AskmeService.generate_initial_questions().
    
    Args:
        request: CreateAgentRequest containing agent details
        db: Database session
    
    Returns:
        CreateAgentResponse with success, message, and details containing created agent
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if agent_id, agent_name, or agent_access already exists
                existing_agent = current_db.query(AgentManagement).filter(
                    or_(
                        AgentManagement.agent_id == request.agent_id,
                        AgentManagement.agent_name == request.agent_name,
                        AgentManagement.agent_access == request.agent_access
                    )
                ).first()
                
                if existing_agent:
                    return {
                        "success": False,
                        "message": "Agent ID, Agent Name, or Agent Access must be unique",
                        "details": {
                            "agent": None
                        }
                    }
                
                # Prepare theme (extra1) - default theme if not provided
                DEFAULT_THEME = {
                    "primaryColor": "#000000",
                    "backgroundColor": "#ffffff",
                    "secondaryBackgroundColor": "#f3f4f6",
                    "textColor": "#111827",
                    "font": "sans serif"
                }
                theme_json = json.dumps(request.theme if request.theme else DEFAULT_THEME)
                
                # Convert boolean checkboxes to "Y" or ""
                database_str = "Y" if request.database else ""
                api_str = "Y" if request.api else ""
                file_str = "Y" if request.file else ""
                
                # Handle database configuration based on data_source_type
                # Normalize data_source_type: replace underscores with spaces and handle "D"/"C" shorthand
                data_source_type_value = request.data_source_type
                if data_source_type_value == "D":
                    normalized_data_source_type = "Default Data Source Type"
                elif data_source_type_value == "C":
                    normalized_data_source_type = "Custom Data Source Type"
                else:
                    normalized_data_source_type = data_source_type_value.replace("_", " ") if data_source_type_value else ""
                
                db_type = ""
                host = ""
                port = ""
                username = ""
                password = ""
                db_name = ""
                connection = ""
                
                if normalized_data_source_type == "Default Data Source Type":
                    # Fetch organization configuration
                    from app.services.agent_config import get_all_admin_configs
                    org_details = get_all_admin_configs(config_type="C")
                    # print(org_details)
                    org_data = {item['configuration_name']: item['value'] for item in org_details}
                    print(org_data)
                    
                    db_type = org_data.get("DB_TYPE", "SQL")
                    if db_type.lower() == "sql":
                        host = org_data.get("HOST", "")
                        port = org_data.get("PORT", "")
                        username = org_data.get("DB_USERNAME", "")
                        password = org_data.get("DB_PASSWORD", "")
                        db_name = org_data.get("DB_NAME", "")
                        connection = ""
                    elif db_type.lower() == "oracle":
                        connection = org_data.get("CONNECTION_STRING", "")
                        username = org_data.get("DB_USERNAME", "")
                        password = org_data.get("DB_PASSWORD", "")
                        host = ""
                        port = ""
                        db_name = ""

                    print(db_type, host, port, username, password, db_name, connection)
                else:  # Custom Data Source Type
                    db_type_raw = request.db_type or ""
                    # Normalize db_type to proper case
                    if db_type_raw:
                        db_type_lower = db_type_raw.lower()
                        db_type = "SQL" if db_type_lower == "sql" else ("Oracle" if db_type_lower == "oracle" else db_type_raw)
                    else:
                        db_type = ""
                    if db_type.lower() == "sql":
                        host = request.host if request.host else "NA"
                        port = request.port if request.port else "NA"
                        username = request.username or ""
                        password = request.password or ""
                        db_name = request.db_name if request.db_name else "NA"
                        connection = ""
                    elif db_type.lower() == "oracle":
                        connection = request.connection if request.connection else "NA"
                        username = request.username or ""
                        password = request.password or ""
                        host = ""
                        port = ""
                        db_name = ""
                
                # Prepare extra2 (oci_profile)
                oci_profile_value = request.oci_profile if request.oci_profile else ""
                
                # Create agent record
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                new_agent = AgentManagement(
                    agent_id=request.agent_id,
                    agent_name=request.agent_name,
                    agent_access=request.agent_access,
                    init_training_data=request.init_training_data,
                    endpoint=request.endpoint,
                    admin_id="",  # Initially empty, will be set later if needed
                    max_row_count=request.max_row_count,
                    extra1=theme_json,
                    extra2=oci_profile_value,
                    database=database_str,
                    api=api_str,
                    file=file_str,
                    default_data_source_type=request.default_data_source_type,
                    data_source_type=normalized_data_source_type,  # Use normalized version (spaces instead of underscores)
                    db_type=db_type,
                    host=host,
                    port=port,
                    username=username,
                    password=password,
                    db_name=db_name,
                    connection=connection,
                    created_at=current_time,
                    updated_at=current_time
                )
                current_db.add(new_agent)
                current_db.commit()
                current_db.refresh(new_agent)
                
                # Generate initial suggested questions
                questions_saved = 0
                questions_error = None
                
                # Only generate questions if database is selected as data source
                if request.database:
                    try:
                        from app.services.askme_service import get_askme_service
                        from app.services.agent_config import get_agent_config
                        
                        # Get agent configuration
                        agent_conf = get_agent_config(agent_id=request.agent_id)
                        if not agent_conf:
                            return {
                                "success": False,
                                "message": "Could not load agent configuration",
                                "details": {
                                    "agent": None
                                }
                            }
                        
                        # Initialize AskmeService
                        askme_service = get_askme_service(agent_id=request.agent_id)
                        if not askme_service:
                            return {
                                "success": False,
                                "message": "Could not initialize AskmeService for agent",
                                "details": {
                                    "agent": None
                                }
                            }
                        
                        # Generate initial questions
                        questions_with_sql = askme_service.generate_initial_questions(
                            initial_training=request.init_training_data,
                            num_questions=25
                        )
                        
                        # Save each question
                        for q in questions_with_sql:
                            try:
                                # Determine question_type and sql based on default_data_source_type
                                # question_data_source should be the config type (data_source_type), not default_data_source_type
                                question_data_source = request.default_data_source_type
                                if request.default_data_source_type.lower() == 'database':
                                    question_type = ""
                                    sql = q.get('sql', '')
                                elif request.default_data_source_type.lower() == 'api':
                                    question_type = ""
                                    sql = ""
                                else:  # File or other
                                    question_type = ""
                                    sql = ""
                                
                                # Save initial suggested question
                                question_entry = InitialSuggestionQuestion(
                                    question=q['question'],
                                    agent_id=request.agent_id,
                                    question_data_source=question_data_source,
                                    question_type=question_type,
                                    sql=sql if sql else "",
                                    api_endpoint="",
                                    api_params="",
                                    api_response_validator=""
                                )
                                current_db.add(question_entry)
                                questions_saved += 1
                                
                            except Exception as e:
                                print(f"Error saving question: {e}")
                                continue
                        
                        current_db.commit()
                        
                    except Exception as e:
                        questions_error = str(e)
                        print(f"Error in question generation: {e}")
                        # Don't fail the agent creation if question generation fails
                
                # Send email notification
                try:
                    # Get organization details from agent_conf
                    org_id = agent_conf.get("org_id", "")
                    org_name = agent_conf.get("org_name", "")
                    sender_email = agent_conf.get("sender_email", "")
                    
                    if sender_email and org_id and org_name:
                        # Generate email content
                        subject, body = get_agent_create_mail(
                            agent_name=request.agent_name,
                            agent_access=request.agent_access,
                            agent_id=request.agent_id,
                            org_name=org_name,
                            org_id=org_id
                        )
                        
                        # Send email to admin using askme_service.send_email()
                        askme_service.send_email(
                            sender_email=sender_email,
                            sender_name="askme360",
                            recipient_email=["murali.mohan@hipl.co.in", "sumit.gupta@hipl.co.in"],
                            subject=subject,
                            body_text="Agent Created",
                            body_html=body,
                            agent_conf=agent_conf
                        )
                        print(f"Agent creation notification email sent successfully")
                    else:
                        print(f"Email notification skipped: Missing configuration (sender_email={sender_email}, org_id={org_id}, org_name={org_name})")
                except Exception as email_error:
                    # Don't fail agent creation if email fails
                    print(f"Error sending agent creation email: {email_error}")
                
                # Use the created agent object (already fetched via refresh)
                agent_dict = {
                    "agent_id": new_agent.agent_id,
                    "agent_name": new_agent.agent_name,
                    "agent_access": new_agent.agent_access,
                    "init_training_data": new_agent.init_training_data,
                    "endpoint": new_agent.endpoint,
                    "extra1": new_agent.extra1 if new_agent.extra1 else "",
                    "extra2": new_agent.extra2 if new_agent.extra2 else "",
                    "created_at": new_agent.created_at if new_agent.created_at else "",
                    "updated_at": new_agent.updated_at if new_agent.updated_at else "",
                    "admin_id": new_agent.admin_id if new_agent.admin_id else "",
                    "max_row_count": new_agent.max_row_count if new_agent.max_row_count else "",
                    "database": new_agent.database if new_agent.database else "",
                    "api": new_agent.api if new_agent.api else "",
                    "file": new_agent.file if new_agent.file else "",
                    "default_data_source_type": new_agent.default_data_source_type if new_agent.default_data_source_type else "",
                    "data_source_type": new_agent.data_source_type if new_agent.data_source_type else "",
                    "db_type": new_agent.db_type if new_agent.db_type else "",
                    "host": new_agent.host if new_agent.host else "",
                    "port": new_agent.port if new_agent.port else "",
                    "username": new_agent.username if new_agent.username else "",
                    "password": new_agent.password if new_agent.password else "",
                    "db_name": new_agent.db_name if new_agent.db_name else "",
                    "connection": new_agent.connection if new_agent.connection else ""
                }
                
                response_details = {
                    "agent": agent_dict,
                    "questions_saved": questions_saved
                }
                
                if questions_error:
                    response_details["questions_error"] = questions_error
                
                return {
                    "success": True,
                    "message": f"Agent created successfully" + (f" with {questions_saved} initial questions" if questions_saved > 0 else "") + (f" (question generation error: {questions_error})" if questions_error else ""),
                    "details": response_details
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "agent": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error creating agent: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "agent": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error creating agent: {str(e)}",
                    "details": {
                        "agent": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "agent": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error creating agent: {str(e)}",
                "details": {
                    "agent": None
                }
            }
    
    return {
        "success": False,
        "message": "Error creating agent: Database is locked after multiple retries. Please try again later.",
        "details": {
            "agent": None
        }
    }


@router.post("/update-agent", response_model=UpdateAgentResponse)
def update_agent(
    request: UpdateAgentRequest,
    db: Session = Depends(get_db)
):
    """
    Update an existing agent and regenerate initial suggested questions.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Updates agent fields and regenerates initial suggested questions if init_training_data
    or data_source_type is changed.
    
    Args:
        request: UpdateAgentRequest containing agent_id and fields to update
        db: Database session
    
    Returns:
        UpdateAgentResponse with success, message, and details containing updated agent
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if agent exists
                existing_agent = current_db.query(AgentManagement).filter(
                    AgentManagement.agent_id == request.agent_id
                ).first()
                
                if not existing_agent:
                    return {
                        "success": False,
                        "message": f"Agent with id {request.agent_id} not found",
                        "details": {
                            "agent": None
                        }
                    }
                
                # Check uniqueness if updating agent_name or agent_access
                if request.agent_name or request.agent_access:
                    conflict_filters = [AgentManagement.agent_id != request.agent_id]
                    
                    if request.agent_name:
                        conflict_filters.append(AgentManagement.agent_name == request.agent_name)
                    if request.agent_access:
                        conflict_filters.append(AgentManagement.agent_access == request.agent_access)
                    
                    conflicting = current_db.query(AgentManagement).filter(
                        and_(*conflict_filters)
                    ).first()
                    
                    if conflicting:
                        return {
                            "success": False,
                            "message": "Agent Name or Agent Access must be unique",
                            "details": {
                                "agent": None
                            }
                        }
                
                # Update fields dynamically
                has_updates = False
                
                if request.agent_name is not None:
                    existing_agent.agent_name = request.agent_name
                    has_updates = True
                
                if request.agent_access is not None:
                    existing_agent.agent_access = request.agent_access
                    has_updates = True
                
                if request.init_training_data is not None:
                    existing_agent.init_training_data = request.init_training_data
                    has_updates = True
                
                if request.endpoint is not None:
                    existing_agent.endpoint = request.endpoint
                    has_updates = True
                
                if request.max_row_count is not None:
                    existing_agent.max_row_count = request.max_row_count
                    has_updates = True
                
                # Handle database/api/file checkboxes
                if request.database is not None:
                    existing_agent.database = "Y" if request.database else ""
                    has_updates = True
                
                if request.api is not None:
                    existing_agent.api = "Y" if request.api else ""
                    has_updates = True
                
                if request.file is not None:
                    existing_agent.file = "Y" if request.file else ""
                    has_updates = True
                
                if request.default_data_source_type is not None:
                    existing_agent.default_data_source_type = request.default_data_source_type
                    has_updates = True
                
                # Handle data_source_type and database configuration
                if request.data_source_type is not None:
                    # Normalize data_source_type: handle "D"/"C" shorthand and replace underscores with spaces
                    data_source_type_value = request.data_source_type
                    if data_source_type_value == "D":
                        normalized_data_source_type = "Default Data Source Type"
                    elif data_source_type_value == "C":
                        normalized_data_source_type = "Custom Data Source Type"
                    else:
                        normalized_data_source_type = data_source_type_value.replace("_", " ") if data_source_type_value else existing_agent.data_source_type
                    existing_agent.data_source_type = normalized_data_source_type  # Use normalized version
                    has_updates = True
                    
                    # If data_source_type is being updated, handle database config
                    if normalized_data_source_type == "Default Data Source Type":
                        # Fetch organization configuration
                        from app.services.agent_config import get_all_admin_configs
                        org_details = get_all_admin_configs(config_type="C")
                        org_data = {item['configuration_name']: item['value'] for item in org_details}
                        
                        existing_agent.db_type = org_data.get("DB_TYPE", "SQL")
                        if existing_agent.db_type == "SQL":
                            existing_agent.host = org_data.get("HOST", "")
                            existing_agent.port = org_data.get("PORT", "")
                            existing_agent.username = org_data.get("DB_USERNAME", "")
                            existing_agent.password = org_data.get("DB_PASSWORD", "")
                            existing_agent.db_name = org_data.get("DB_NAME", "")
                            existing_agent.connection = ""
                        elif existing_agent.db_type == "Oracle":
                            existing_agent.connection = org_data.get("CONNECTION_STRING", "")
                            existing_agent.username = org_data.get("DB_USERNAME", "")
                            existing_agent.password = org_data.get("DB_PASSWORD", "")
                            existing_agent.host = ""
                            existing_agent.port = ""
                            existing_agent.db_name = ""
                    elif normalized_data_source_type == "Custom Data Source Type":
                        # Use provided values or keep existing if not provided
                        if request.db_type is not None:
                            # Normalize db_type to proper case
                            db_type_raw = request.db_type
                            db_type_lower = db_type_raw.lower()
                            normalized_db_type = "SQL" if db_type_lower == "sql" else ("Oracle" if db_type_lower == "oracle" else db_type_raw)
                            existing_agent.db_type = normalized_db_type
                            if normalized_db_type == "SQL":
                                existing_agent.host = request.host if request.host else (existing_agent.host or "NA")
                                existing_agent.port = request.port if request.port else (existing_agent.port or "NA")
                                existing_agent.username = request.username if request.username is not None else existing_agent.username
                                existing_agent.password = request.password if request.password is not None else existing_agent.password
                                existing_agent.db_name = request.db_name if request.db_name else (existing_agent.db_name or "NA")
                                existing_agent.connection = ""
                            elif normalized_db_type == "Oracle":
                                existing_agent.connection = request.connection if request.connection else (existing_agent.connection or "NA")
                                existing_agent.username = request.username if request.username is not None else existing_agent.username
                                existing_agent.password = request.password if request.password is not None else existing_agent.password
                                existing_agent.host = ""
                                existing_agent.port = ""
                                existing_agent.db_name = ""
                else:
                    # If data_source_type is not being updated, but db fields are provided, update them
                    if request.db_type is not None:
                        # Normalize db_type to proper case
                        db_type_raw = request.db_type
                        db_type_lower = db_type_raw.lower()
                        normalized_db_type = "SQL" if db_type_lower == "sql" else ("Oracle" if db_type_lower == "oracle" else db_type_raw)
                        existing_agent.db_type = normalized_db_type
                        has_updates = True
                        if normalized_db_type == "SQL":
                            if request.host is not None:
                                existing_agent.host = request.host if request.host else "NA"
                            if request.port is not None:
                                existing_agent.port = request.port if request.port else "NA"
                            if request.username is not None:
                                existing_agent.username = request.username
                            if request.password is not None:
                                existing_agent.password = request.password
                            if request.db_name is not None:
                                existing_agent.db_name = request.db_name if request.db_name else "NA"
                            existing_agent.connection = ""
                        elif normalized_db_type == "Oracle":
                            if request.connection is not None:
                                existing_agent.connection = request.connection if request.connection else "NA"
                            if request.username is not None:
                                existing_agent.username = request.username
                            if request.password is not None:
                                existing_agent.password = request.password
                            existing_agent.host = ""
                            existing_agent.port = ""
                            existing_agent.db_name = ""
                    else:
                        # Update individual db fields if provided
                        if request.host is not None:
                            existing_agent.host = request.host if request.host else "NA"
                            has_updates = True
                        if request.port is not None:
                            existing_agent.port = request.port if request.port else "NA"
                            has_updates = True
                        if request.username is not None:
                            existing_agent.username = request.username
                            has_updates = True
                        if request.password is not None:
                            existing_agent.password = request.password
                            has_updates = True
                        if request.db_name is not None:
                            existing_agent.db_name = request.db_name if request.db_name else "NA"
                            has_updates = True
                        if request.connection is not None:
                            existing_agent.connection = request.connection if request.connection else "NA"
                            has_updates = True
                
                if request.theme is not None:
                    existing_agent.extra1 = json.dumps(request.theme)
                    has_updates = True
                
                if request.oci_profile is not None:
                    existing_agent.extra2 = request.oci_profile if request.oci_profile else ""
                    has_updates = True
                
                if not has_updates:
                    return {
                        "success": False,
                        "message": "At least one field must be provided for update",
                        "details": {
                            "agent": None
                        }
                    }
                
                # Add updated_at
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                existing_agent.updated_at = current_time
                
                current_db.commit()
                current_db.refresh(existing_agent)
                
                # Generate initial suggested questions only if none exist for this agent
                questions_saved = 0
                questions_error = None
                
                # Check if there are any existing initial suggested questions for this agent
                existing_questions_count = current_db.query(InitialSuggestionQuestion).filter(
                    InitialSuggestionQuestion.agent_id == request.agent_id
                ).count()
                
                # Only generate questions if there are no existing questions (count is 0) and database is enabled
                if existing_questions_count == 0 and existing_agent.database == "Y":
                    try:
                        from app.services.askme_service import get_askme_service
                        from app.services.agent_config import get_agent_config
                        
                        # Use existing_agent object (already fetched and refreshed)
                        init_training_data = request.init_training_data if request.init_training_data is not None else existing_agent.init_training_data
                        default_data_source_type = request.default_data_source_type if request.default_data_source_type is not None else existing_agent.default_data_source_type
                        data_source_type = request.data_source_type if request.data_source_type is not None else existing_agent.data_source_type
                        
                        # Get agent configuration
                        agent_conf = get_agent_config(agent_id=request.agent_id)
                        if not agent_conf:
                            return {
                                "success": False,
                                "message": "Could not load agent configuration",
                                "details": {
                                    "agent": None
                                }
                            }
                        
                        # Initialize AskmeService
                        askme_service = get_askme_service(agent_id=request.agent_id)
                        if not askme_service:
                            return {
                                "success": False,
                                "message": "Could not initialize AskmeService for agent",
                                "details": {
                                    "agent": None
                                }
                            }
                        
                        # Generate initial questions
                        questions_with_sql = askme_service.generate_initial_questions(
                            initial_training=init_training_data,
                            num_questions=25
                        )
                        
                        # Save each question
                        for q in questions_with_sql:
                            try:
                                # Determine question_type and sql based on default_data_source_type
                                # question_data_source should be the config type (data_source_type), not default_data_source_type
                                question_data_source = data_source_type
                                if default_data_source_type.lower() == 'database':
                                    question_type = ""
                                    sql = q.get('sql', '')
                                elif default_data_source_type.lower() == 'api':
                                    question_type = ""
                                    sql = ""
                                else:  # File or other
                                    question_type = ""
                                    sql = ""
                                
                                # Save initial suggested question
                                question_entry = InitialSuggestionQuestion(
                                    question=q['question'],
                                    agent_id=request.agent_id,
                                    question_data_source=question_data_source,
                                    question_type=question_type,
                                    sql=sql if sql else "",
                                    api_endpoint="",
                                    api_params="",
                                    api_response_validator=""
                                )
                                current_db.add(question_entry)
                                questions_saved += 1
                                
                            except Exception as e:
                                print(f"Error saving question: {e}")
                                continue
                        
                        current_db.commit()
                        
                    except Exception as e:
                        questions_error = str(e)
                        print(f"Error in question generation: {e}")
                        # Don't fail the agent update if question generation fails
                
                # Use existing_agent object (already refreshed)
                agent_dict = {
                    "agent_id": existing_agent.agent_id,
                    "agent_name": existing_agent.agent_name,
                    "agent_access": existing_agent.agent_access,
                    "init_training_data": existing_agent.init_training_data,
                    "endpoint": existing_agent.endpoint,
                    "extra1": existing_agent.extra1 if existing_agent.extra1 else "",
                    "extra2": existing_agent.extra2 if existing_agent.extra2 else "",
                    "created_at": existing_agent.created_at if existing_agent.created_at else "",
                    "updated_at": existing_agent.updated_at if existing_agent.updated_at else "",
                    "admin_id": existing_agent.admin_id if existing_agent.admin_id else "",
                    "max_row_count": existing_agent.max_row_count if existing_agent.max_row_count else "",
                    "database": existing_agent.database if existing_agent.database else "",
                    "api": existing_agent.api if existing_agent.api else "",
                    "file": existing_agent.file if existing_agent.file else "",
                    "default_data_source_type": existing_agent.default_data_source_type if existing_agent.default_data_source_type else "",
                    "data_source_type": existing_agent.data_source_type if existing_agent.data_source_type else "",
                    "db_type": existing_agent.db_type if existing_agent.db_type else "",
                    "host": existing_agent.host if existing_agent.host else "",
                    "port": existing_agent.port if existing_agent.port else "",
                    "username": existing_agent.username if existing_agent.username else "",
                    "password": existing_agent.password if existing_agent.password else "",
                    "db_name": existing_agent.db_name if existing_agent.db_name else "",
                    "connection": existing_agent.connection if existing_agent.connection else ""
                }
                
                response_details = {
                    "agent": agent_dict
                }
                
                if questions_saved > 0:
                    response_details["questions_saved"] = questions_saved
                
                if questions_error:
                    response_details["questions_error"] = questions_error
                
                message = "Agent updated successfully"
                if questions_saved > 0:
                    message += f" with {questions_saved} initial questions generated"
                elif existing_questions_count > 0:
                    message += f" (skipped question generation - {existing_questions_count} questions already exist)"
                if questions_error:
                    message += f" (question generation error: {questions_error})"
                
                return {
                    "success": True,
                    "message": message,
                    "details": response_details
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "agent": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error updating agent: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "agent": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error updating agent: {str(e)}",
                    "details": {
                        "agent": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "agent": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error updating agent: {str(e)}",
                "details": {
                    "agent": None
                }
            }
    
    return {
        "success": False,
        "message": "Error updating agent: Database is locked after multiple retries. Please try again later.",
        "details": {
            "agent": None
        }
    }


@router.post("/delete-agent", response_model=DeleteAgentResponse)
def delete_agent(
    request: DeleteAgentRequest,
    db: Session = Depends(get_db)
):
    """
    Delete an agent and all its initial suggested questions.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Deletes the agent from agent_management table and all associated initial suggested questions.
    
    Args:
        request: DeleteAgentRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        DeleteAgentResponse with success, message, and details containing deleted agent_id
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if agent exists
                existing_agent = current_db.query(AgentManagement).filter(
                    AgentManagement.agent_id == request.agent_id
                ).first()
                
                if not existing_agent:
                    return {
                        "success": False,
                        "message": f"Agent with id {request.agent_id} not found",
                        "details": {
                            "agent": None
                        }
                    }
                
                # Store agent_name before deletion
                agent_name = existing_agent.agent_name
                
                # Delete all initial suggested questions for this agent
                deleted_questions = current_db.query(InitialSuggestionQuestion).filter(
                    InitialSuggestionQuestion.agent_id == request.agent_id
                ).delete()
                
                # Delete the agent
                current_db.delete(existing_agent)
                
                current_db.commit()
                
                return {
                    "success": True,
                    "message": "Agent deleted successfully",
                    "details": {
                        "agent_id": request.agent_id,
                        "agent_name": agent_name,
                        "deleted_questions_count": deleted_questions
                    }
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "agent_id": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error deleting agent: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "agent_id": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error deleting agent: {str(e)}",
                    "details": {
                        "agent_id": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "agent_id": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error deleting agent: {str(e)}",
                "details": {
                    "agent_id": None
                }
            }
    
    return {
        "success": False,
        "message": "Error deleting agent: Database is locked after multiple retries. Please try again later.",
        "details": {
            "agent_id": None
        }
    }


# ==================== AGENT ADMIN MANAGEMENT APIs ====================

class GetAllAgentAdminsRequest(BaseModel):
    agent_id: str
    user_id: str


class GetAllAgentAdminsResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class CreateAgentAdminRequest(BaseModel):
    agent_id: str
    user_id: str
    agent_name: str
    admin_id: str
    admin_name: str


class CreateAgentAdminResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


class DeleteAgentAdminRequest(BaseModel):
    agent_id: str
    user_id: str
    id: int  # Agent admin record ID


class DeleteAgentAdminResponse(BaseModel):
    success: bool
    message: str
    details: Dict[str, Any]


@router.post("/get-all-agent-admins", response_model=GetAllAgentAdminsResponse)
def get_all_agent_admins(
    request: GetAllAgentAdminsRequest,
    db: Session = Depends(get_db)
):
    """
    Get all agent admin records.
    
    Mirrors the functionality from updated/user_management/user_managment_db.py
    Returns all agent_admin records with joins to get agent_name and admin_name.
    
    Args:
        request: GetAllAgentAdminsRequest containing agent_id and user_id
        db: Database session
    
    Returns:
        GetAllAgentAdminsResponse with success, message, and details containing agent admins list
    """
    try:
        # Query all agent admins with joins using ORM
        agent_admin_records = db.query(
            AgentAdmin,
            AgentManagement.agent_name,
            User.name.label('admin_name'),
            User.email.label('admin_email')
        ).outerjoin(
            User, AgentAdmin.admin_id == User.id
        ).outerjoin(
            AgentManagement, AgentAdmin.agent_id == AgentManagement.agent_id
        ).order_by(
            AgentAdmin.agent_id.asc()
        ).all()
        
        agent_admins = []
        for aa, agent_name, admin_name, admin_email in agent_admin_records:
            agent_admin = {
                "id": aa.id,
                "agent_id": aa.agent_id,
                "agent_name": agent_name if agent_name else "",
                "admin_id": aa.admin_id,
                "admin_name": admin_name if admin_name else "",
                "admin_email": admin_email if admin_email else ""
            }
            agent_admins.append(agent_admin)
        
        return {
            "success": True,
            "message": "Agent admins retrieved successfully",
            "details": {
                "agent_admins": agent_admins
            }
        }
    except Exception as e:
        return {
            "success": False,
            "message": f"Error retrieving agent admins: {str(e)}",
            "details": {
                "agent_admins": []
            }
        }


@router.post("/create-agent-admin", response_model=CreateAgentAdminResponse)
def create_agent_admin(
    request: CreateAgentAdminRequest,
    db: Session = Depends(get_db)
):
    """
    Create a new agent admin relationship.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Creates an agent-admin relationship in agent_admin table.
    Validates that the agent-admin pair is unique.
    
    Args:
        request: CreateAgentAdminRequest containing agent_id, user_id, agent_name, admin_id, admin_name
        db: Database session
    
    Returns:
        CreateAgentAdminResponse with success, message, and details containing created agent admin
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if agent-admin pair already exists
                existing_pair = current_db.query(AgentAdmin).filter(
                    AgentAdmin.agent_id == request.agent_id,
                    AgentAdmin.admin_id == request.admin_id
                ).first()
                
                if existing_pair:
                    return {
                        "success": False,
                        "message": "This Agent-Admin pair already exists",
                        "details": {
                            "agent_admin": None
                        }
                    }
                
                # Verify agent exists and get agent_name if not provided
                agent_info = None
                if not request.agent_name:
                    agent_info = current_db.query(AgentManagement).filter(
                        AgentManagement.agent_id == request.agent_id
                    ).first()
                    
                    if not agent_info:
                        return {
                            "success": False,
                            "message": f"Agent with id {request.agent_id} not found",
                            "details": {
                                "agent_admin": None
                            }
                        }
                
                # Verify admin exists and get admin_name if not provided
                admin_info = None
                if not request.admin_name:
                    admin_info = current_db.query(User).filter(
                        User.id == int(request.admin_id)
                    ).first()
                    
                    if not admin_info:
                        return {
                            "success": False,
                            "message": f"Admin user with id {request.admin_id} not found",
                            "details": {
                                "agent_admin": None
                            }
                        }
                
                # Use provided names or fetch from database
                agent_name = request.agent_name if request.agent_name else (agent_info.agent_name if agent_info else "")
                admin_name = request.admin_name if request.admin_name else (admin_info.name if admin_info else "")
                
                # Create agent admin record
                current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                new_agent_admin = AgentAdmin(
                    agent_id=request.agent_id,
                    agent_name=agent_name,
                    admin_id=request.admin_id,
                    admin_name=admin_name,
                    created_at=current_time,
                    updated_at=current_time
                )
                current_db.add(new_agent_admin)
                current_db.commit()
                current_db.refresh(new_agent_admin)
                
                # Fetch created agent admin with joins
                created_agent_admin_query = current_db.query(
                    AgentAdmin,
                    AgentManagement.agent_name,
                    User.name.label('admin_name'),
                    User.email.label('admin_email')
                ).outerjoin(
                    User, AgentAdmin.admin_id == User.id
                ).outerjoin(
                    AgentManagement, AgentAdmin.agent_id == AgentManagement.agent_id
                ).filter(
                    AgentAdmin.agent_id == request.agent_id,
                    AgentAdmin.admin_id == request.admin_id
                ).first()
                
                if created_agent_admin_query:
                    aa, ag_name, u_name, u_email = created_agent_admin_query
                    agent_admin_dict = {
                        "id": aa.id,
                        "agent_id": aa.agent_id,
                        "agent_name": ag_name if ag_name else "",
                        "admin_id": aa.admin_id,
                        "admin_name": u_name if u_name else "",
                        "admin_email": u_email if u_email else ""
                    }
                else:
                    # Fallback if join fails - use the created object
                    agent_admin_dict = {
                        "id": new_agent_admin.id,
                        "agent_id": new_agent_admin.agent_id,
                        "agent_name": agent_name,
                        "admin_id": new_agent_admin.admin_id,
                        "admin_name": admin_name,
                        "admin_email": None
                    }
                
                return {
                    "success": True,
                    "message": "Agent admin created successfully",
                    "details": {
                        "agent_admin": agent_admin_dict
                    }
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                        "agent_admin": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error creating agent admin: Database is locked. Please try again later. {str(e)}",
                        "details": {
                            "agent_admin": None
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error creating agent admin: {str(e)}",
                    "details": {
                        "agent_admin": None
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                    "agent_admin": None
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error creating agent admin: {str(e)}",
                "details": {
                    "agent_admin": None
                }
            }
    
    return {
        "success": False,
        "message": "Error creating agent admin: Database is locked after multiple retries. Please try again later.",
        "details": {
            "agent_admin": None
        }
    }


@router.post("/delete-agent-admin", response_model=DeleteAgentAdminResponse)
def delete_agent_admin(
    request: DeleteAgentAdminRequest,
    db: Session = Depends(get_db)
):
    """
    Delete an agent admin relationship.
    
    Mirrors the functionality from updated/user_management/user_managment.py
    Deletes the agent-admin relationship from agent_admin table by ID.
    
    Args:
        request: DeleteAgentAdminRequest containing agent_id, user_id, and id (agent admin record ID)
        db: Database session
    
    Returns:
        DeleteAgentAdminResponse with success, message, and details containing deleted agent admin info
    """
    max_retries = 5
    retry_delay = 0.1
    
    current_db = db
    
    for attempt in range(max_retries):
        try:
            if attempt > 0:
                current_db = SessionLocal()
            
            try:
                # Check if agent admin exists and get details
                existing_agent_admin_query = current_db.query(
                    AgentAdmin,
                    AgentManagement.agent_name,
                    User.name.label('admin_name')
                ).outerjoin(
                    User, AgentAdmin.admin_id == User.id
                ).outerjoin(
                    AgentManagement, AgentAdmin.agent_id == AgentManagement.agent_id
                ).filter(
                    AgentAdmin.id == request.id
                ).first()
                
                if not existing_agent_admin_query:
                    return {
                        "success": False,
                        "message": f"Agent admin record with id {request.id} not found",
                        "details": {
                            "id": request.id,
                            "agent_id": None,
                            "agent_name": None,
                            "admin_id": None,
                            "admin_name": None
                        }
                    }
                
                aa, ag_name, u_name = existing_agent_admin_query
                
                # Store values before deletion
                agent_id = aa.agent_id
                agent_name = ag_name if ag_name else ""
                admin_id = aa.admin_id
                admin_name = u_name if u_name else ""
                
                # Delete the agent admin record
                current_db.delete(aa)
                
                current_db.commit()
                
                return {
                    "success": True,
                    "message": "Agent admin deleted successfully",
                    "details": {
                        "id": request.id,
                        "agent_id": agent_id,
                        "agent_name": agent_name,
                        "admin_id": admin_id,
                        "admin_name": admin_name
                    }
                }
            
            except HTTPException as e:
                return {
                    "success": False,
                    "message": e.detail,
                    "details": {
                         "id": request.id,
                         "agent_id": None
                    }
                }
            except OperationalError as e:
                current_db.rollback()
                if "database is locked" in str(e).lower() and attempt < max_retries - 1:
                    time.sleep(retry_delay * (2 ** attempt))
                    continue
                else:
                    return {
                        "success": False,
                        "message": f"Error deleting agent admin: Database is locked. Please try again later. {str(e)}",
                        "details": {
                             "id": request.id
                        }
                    }
            except Exception as e:
                current_db.rollback()
                return {
                    "success": False,
                    "message": f"Error deleting agent admin: {str(e)}",
                    "details": {
                         "id": request.id
                    }
                }
        except HTTPException as e:
            return {
                "success": False,
                "message": e.detail,
                "details": {
                     "id": request.id
                }
            }
        except Exception as e:
            return {
                "success": False,
                "message": f"Error deleting agent admin: {str(e)}",
                "details": {
                     "id": request.id
                }
            }
    
    return {
        "success": False,
        "message": "Error deleting agent admin: Database is locked after multiple retries. Please try again later.",
        "details": {
             "id": request.id
        }
    }

