from datetime import datetime
from pymongo.errors import DuplicateKeyError
from app.db import database
from app.v1.integrations.SMTPmailer import send_email
from app.v1.libraries.object import str_to_objectid
from app.v1.models.platform.services import WorkforceCreate, WorkforceUpdate, Workforce, Workforces
from app.v1.models.saas.usersmodel import User
from app.v1.services.sequence import get_next_sequence_value_int
from pymongo import ASCENDING, DESCENDING

from typing import Optional

COLLECTION_NAME = "services"
USER_COLLECTION_NAME = "users"
CHECKLIST_COLLECTION_NAME = "checklist"
ADDON_COLLECTION_NAME = "addons"
SKILLS_COLLECTION_NAME = "skills"
SERVICECAT_COLLECTION_NAME = "service_category"

BASE_URL = "http://localhost:8003"
FILE_PATH = "public/services"

def create_account_service(account: WorkforceCreate, db: database.MongoDB) -> dict:
    account_data = account.dict()

    # Ensure lists are stored as arrays
    account_data["skills"] = list(account_data.get("skills", []))
    account_data["checklist"] = list(account_data.get("checklist", []))
    # Add empty array for addons if needed
    account_data["addons"] = list(account_data.get("addons", []))
    account_data.setdefault("dynamicFields", [])

    account_data["created_date"] = datetime.utcnow()    
    account_data["status"] = 'A'    

    accounts_collection = db[COLLECTION_NAME]  
    account_data["s_id"] = get_next_sequence_value_int("s_id", db)
    
    try:        
        result = accounts_collection.insert_one(account_data)
    except DuplicateKeyError as e:     

        if "rule_name" in str(e):
            raise ValueError("Pricing rule already exists.")
        else:
            raise ValueError("Duplicate key error.")
    
    # (Optional) Update the user with the new account_id if needed.
    # For example, you could call an update function here:
    # update_user_account_id(account.user_id, str(result.inserted_id), db)
    
    # Send a welcome email
    # subject = "Your Organization has been created!"
    # body = f"Hello {account.name},\n\nThank you for signing up for our platform. We're excited to have you on board!"
    # send_email(subject, body, account.email)
    
    # Prepare and return the created account data
    #account_data["_id"] = str(result.inserted_id)
    account_data["account_id"] = str(result.inserted_id)
    return account_data

def get_accounts_service(
    skip: int, 
    limit: int, 
    q: str, 
    account_type: str, 
    is_active: bool, 
    db: database.MongoDB, 
    current_user: User
) -> dict:
    collection = db[COLLECTION_NAME]
    
    # Example role check: only users with role 1 (e.g., admin , moderators, sales, accounts) can list accounts
    if current_user.get("roles", 0) != 1 and current_user.get("roles", 0) != 2 :
        raise ValueError("Not permitted to view accounts.")
    
    query = {}
    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [
            {"account_name": regex_query},
            {"name": regex_query},
            {"country": regex_query},
            {"city": regex_query}
        ]
    
    if account_type:
        query["account_type"] = account_type
    
    if is_active is not None:
        query["is_active"] = is_active
    
    accounts_cursor = collection.find(query).skip(skip).limit(limit)
    accounts_list = []
    for account in accounts_cursor:
        account["id"] = str(account["_id"])
        account["account_id"] = str(account["_id"])

        # Add URL formatting
        if "image" in account and account["image"]:
            account["image"] = f"{BASE_URL}/{FILE_PATH}/{account['image']}"
        if "map_icon" in account and account["map_icon"]:
            account["map_icon"] = f"{BASE_URL}/{FILE_PATH}/{account['map_icon']}"

        if "created_date" in account and isinstance(account["created_date"], datetime):
            account["created_date"] = account["created_date"].isoformat()
        accounts_list.append(account)
    
    #print(accounts_list)
    total_count = collection.count_documents(query)
    
    return {"total_count": total_count, "accounts": accounts_list}

def get_account_service(account_id: str, db: database.MongoDB) -> dict:
    collection = db[COLLECTION_NAME]
    account = collection.find_one({"_id": str_to_objectid(account_id)})
    if account:
        account["_id"] = str(account["_id"])
        account["account_id"] = account["_id"]

        # Add URL formatting
        if "image" in account and account["image"]:
            account["image"] = f"{BASE_URL}/{FILE_PATH}/{account['image']}"
        if "map_icon" in account and account["map_icon"]:
            account["map_icon"] = f"{BASE_URL}/{FILE_PATH}/{account['map_icon']}"
            
        account.setdefault("skills", [])
        account.setdefault("checklist", [])
        account.setdefault("addons", [])

        # ✅ Add category_inputs as a new param based on service_id
        service_id = account.get("service_id")
        if service_id:
            service_category = db[SERVICECAT_COLLECTION_NAME].find_one({"_id": str_to_objectid(service_id)})
            if service_category and "inputs" in service_category:
                account["category_inputs"] = service_category["inputs"]
            else:
                account["category_inputs"] = []
        else:
            account["category_inputs"] = []

    return account

def update_account_service(
    account_id: str, 
    account_update: WorkforceUpdate, 
    current_user: dict, 
    db: database.MongoDB
) -> dict:
    accounts_collection = db[COLLECTION_NAME]
    users_collection = db[USER_COLLECTION_NAME]
    
    existing_account = accounts_collection.find_one({"_id": str_to_objectid(account_id)})
    if not existing_account:
        raise ValueError("Account not found")
    
    user_document = users_collection.find_one({"_id": current_user["_id"]})
    if not user_document:
        raise ValueError("User not found")
    
    # Check if the current user is authorized to update the account
    if str(user_document.get("account_id")) != account_id:
        raise ValueError("Not authorized to update this account")
    
    account_updates = account_update.dict(exclude={"user_id"})
    account_updates.pop("roles", None)  # Remove roles field if present
    
    # For example, only users with role 3 may update the account
    if user_document.get("roles") == 3:  #Role 3== Account Admin
        accounts_collection.update_one({"_id": str_to_objectid(account_id)}, {"$set": account_updates})
    
    # Optionally update user data in the users collection
    user_update_data = {"name": account_update.name, "mobile": account_update.mobile}
    users_collection.update_one({"_id": current_user["_id"]}, {"$set": user_update_data})
    
    updated_account = accounts_collection.find_one({"_id": str_to_objectid(account_id)})
    if updated_account:
        updated_account["_id"] = str(updated_account["_id"])
        updated_account["account_id"] = updated_account["_id"]
        return updated_account
    else:
        raise ValueError("Account not found after update")

def delete_account_service(account_id: str, db: database.MongoDB) -> dict:
    collection = db[COLLECTION_NAME]
    account = collection.find_one({"_id": str_to_objectid(account_id)})
    if not account:
        raise ValueError("Account not found")
    collection.delete_one({"_id": str_to_objectid(account_id)})
    account["_id"] = str(account["_id"])
    account["account_id"] = account["_id"]
    return account

def get_users_service(account_id: str, skip: int, limit: int, q: Optional[str], is_active: Optional[bool], status: Optional[str], service_id: Optional[int], has_cost: Optional[bool], is_vehicle_focused: Optional[bool], created_date_from: Optional[str],
    created_date_to: Optional[str], sort_by: Optional[str], sort_order: Optional[str], db, current_user) -> dict:
    users_collection = db[COLLECTION_NAME]


    query = {}
    if account_id == "all" and current_user.get("roles" != "1"):
        raise HTTPException(status_code=403, detail="Not permitted to view all services.")

    if account_id != "all":
        query["account_id"] = account_id
    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [
            {"service_title": regex_query},
            {"service_description": regex_query}
        ]
    if is_active is not None:
        query["is_active"] = is_active
    if status:
        query["status"] = status
    if service_id is not None:
        query["service_id"] = service_id
    if has_cost is not None:
        query["has_cost"] = has_cost
    if is_vehicle_focused is not None:
        query["is_vehicle_focused"] = is_vehicle_focused

    # Add date range filter
    if created_date_from or created_date_to:
        date_filter = {}
        if created_date_from:
            date_filter["$gte"] = datetime.strptime(created_date_from, "%Y-%m-%d")
        if created_date_to:
            date_filter["$lte"] = datetime.strptime(created_date_to, "%Y-%m-%d")
        query["created_date"] = date_filter
    
    # Sorting support
    sort_fields = {
        "service_title": "service_title",
        "service_description": "service_description",
        "status": "status",
        "created_date": "created_date"
    }
    sort_field = sort_fields.get(sort_by, "created_date")  # default to created_date
    sort_direction = ASCENDING if sort_order == "asc" else DESCENDING

    users = list(users_collection.find(query).sort(sort_field, sort_direction).skip(skip).limit(limit))
    #users = list(users_collection.find(query).skip(skip).limit(limit))



    for user in users:
        user["user_id"] = str(user["_id"])        
        user["id"] = str(user["_id"])        
        for field in ['created_date', 'last_login', 'date_of_birth']:
            if field in user and isinstance(user[field], datetime):
                user[field] = user[field].isoformat()
    total_count = users_collection.count_documents(query)
    return {"total_count": total_count, "users": users}

async def update_workforce_service(account_id: str, role_update: WorkforceUpdate, db: database.MongoDB) -> dict:
    roles_collection: Collection = db[COLLECTION_NAME]
    update_data = role_update.dict(exclude_unset=True)     

    result = roles_collection.update_one({"_id": str_to_objectid(account_id)}, {"$set": update_data})
    
    if result.matched_count == 0:
        return None
    role = roles_collection.find_one({"_id": str_to_objectid(account_id)})
    
    return role

def get_select_list(account_id: str, db, current_user, q: Optional[str] = None) -> dict:
    users_collection = db[CHECKLIST_COLLECTION_NAME]

    query = {}

    # Check for permissions if not allowed to view all users
    if account_id == "all" and current_user.get("roles") != "1":
        raise HTTPException(status_code=403, detail="Not permitted to view all users.")

    # Convert account_id to ObjectId if not 'all'
    # if account_id != "all":
    #     try:
    #         query["account_id"] = ObjectId(account_id)
    #     except Exception as e:
    #         raise HTTPException(status_code=400, detail=f"Invalid ObjectId format: {account_id}")

    # Apply search query if provided
    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [{"checklist_name": regex_query}]

    # Filter by status
    query["status"] = "active"

    # Fetch users from MongoDB
    users = list(users_collection.find(query))

    # Format user data properly
    for user in users:
        user["user_id"] = str(user["_id"])  # Convert _id to string
        for field in ["created_date", "last_login", "date_of_birth"]:
            if field in user and isinstance(user[field], datetime):
                user[field] = user[field].isoformat()

    total_count = users_collection.count_documents(query)
    return {"users": users}

def get_addonselect_list(account_id: str, db, current_user, q: Optional[str] = None) -> dict:
    users_collection = db[ADDON_COLLECTION_NAME]

    query = {}

    # Check for permissions if not allowed to view all users
    if account_id == "all" and current_user.get("roles") != "1":
        raise HTTPException(status_code=403, detail="Not permitted to view all users.")

    # Convert account_id to ObjectId if not 'all'
    # if account_id != "all":
    #     try:
    #         query["account_id"] = ObjectId(account_id)
    #     except Exception as e:
    #         raise HTTPException(status_code=400, detail=f"Invalid ObjectId format: {account_id}")

    # Apply search query if provided
    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [{"addons": regex_query}]

    # Filter by status
    query["status"] = "active"

    # Fetch users from MongoDB
    users = list(users_collection.find(query))

    # Format user data properly
    for user in users:
        user["user_id"] = str(user["_id"])  # Convert _id to string
        for field in ["created_date", "last_login", "date_of_birth"]:
            if field in user and isinstance(user[field], datetime):
                user[field] = user[field].isoformat()

    total_count = users_collection.count_documents(query)
    return {"users": users}

def get_servicecatselect_list(account_id: str, db, current_user, q: Optional[str] = None) -> dict:
    users_collection = db[SERVICECAT_COLLECTION_NAME]

    query = {}

    # Check for permissions if not allowed to view all users
    if account_id == "all" and current_user.get("roles") != "1":
        raise HTTPException(status_code=403, detail="Not permitted to view all users.")

    # Convert account_id to ObjectId if not 'all'
    # if account_id != "all":
    #     try:
    #         query["account_id"] = ObjectId(account_id)
    #     except Exception as e:
    #         raise HTTPException(status_code=400, detail=f"Invalid ObjectId format: {account_id}")

    # Apply search query if provided
    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [{"addons": regex_query}]

    # Filter by status
    query["status"] = "active"

    # Fetch users from MongoDB
    users = list(users_collection.find(query))

    # Format user data properly
    for user in users:
        user["user_id"] = str(user["_id"])  # Convert _id to string
        for field in ["created_date", "last_login", "date_of_birth"]:
            if field in user and isinstance(user[field], datetime):
                user[field] = user[field].isoformat()

    total_count = users_collection.count_documents(query)
    return {"users": users}

# Sub Services
def get_subserviceselect_list(account_id: str, service_id: Optional[str], q: Optional[str],is_parent: Optional[bool], db, current_user) -> dict:
    users_collection = db[COLLECTION_NAME]

    query = {}

    # Check for permissions if not allowed to view all users
    if account_id == "all" and current_user.get("roles") != "1":
        raise HTTPException(status_code=403, detail="Not permitted to view all users.")

    # Convert account_id to ObjectId if not 'all'
    # if account_id != "all":
    #     try:
    #         query["account_id"] = ObjectId(account_id)
    #     except Exception as e:
    #         raise HTTPException(status_code=400, detail=f"Invalid ObjectId format: {account_id}")

    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [{"service_title": regex_query}]

    # is_parent logic
    if is_parent is False:
        query["is_parent"] = False

        # Only apply parent_id if is_parent is explicitly False
        if service_id is not None:
            query["parent_id"] = service_id
    else:
        # Match anything where is_parent is not explicitly False
        query["is_parent"] = {"$ne": False}

    # Filter by status
    #query["status"] = "active"

    # Fetch users from MongoDB
    users = list(users_collection.find(query))

    # Format user data properly
    for user in users:
        user["user_id"] = str(user["_id"])  # Convert _id to string
        user["id"] = str(user["_id"])  # Convert _id to string
        for field in ["created_date", "last_login", "date_of_birth"]:
            if field in user and isinstance(user[field], datetime):
                user[field] = user[field].isoformat()

    total_count = users_collection.count_documents(query)
    return {"users": users}

def get_skillsselect_list(account_id: str, db, current_user, q: Optional[str] = None) -> dict:
    users_collection = db[SKILLS_COLLECTION_NAME]
    
    query = {"status": "active"}
    
    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [{"name": regex_query}]  # Changed from "addons" to "name"

    users = list(users_collection.find(query))
    
    # Format response
    formatted_skills = []
    for skill in users:
        formatted_skills.append({
            "user_id": str(skill["_id"]),
            "name": skill.get("name", ""),
            "account_id": str(skill.get("account_id", ""))
        })
    
    return {"users": formatted_skills}