import json
import os
import re
from datetime import datetime
from bson import ObjectId
from pymongo.errors import DuplicateKeyError
from fastapi import HTTPException, status
from dotenv import load_dotenv
from typing import Optional

from app.v1.libraries.object import str_to_objectid
from ...models.platform.workforcecategorymodel import (
    WorkforceCategory, WorkforceCategoryBase, WorkforceCategoryUpdate, WorkforceCategoryResponseList
)
from app.v1.services.sequence import get_next_sequence_value_int
from pymongo import ASCENDING, DESCENDING

load_dotenv()

# Collection names
COLLECTION_NAME = "workforcecategory"

def create_workforcecategory_service(workforcecategory: WorkforceCategoryBase, background_tasks, db) -> dict:
    workforcecategories_collection = db[COLLECTION_NAME]

    # Check if workforce category exists by category name
    if workforcecategories_collection.find_one({"category_name": workforcecategory.category_name}):
        raise HTTPException(status_code=400, detail="Workforce category with this name already exists.")

    # Prepare workforce category data
    new_workforcecategory_data = workforcecategory.dict()
    new_workforcecategory_data["created_date"] = datetime.utcnow()
    new_workforcecategory_data["last_updated"] = datetime.utcnow()

    new_workforcecategory_data["workforce_category_id"] = get_next_sequence_value_int("workforce_category_id", db)

    try:
        new_workforcecategory = workforcecategories_collection.insert_one(new_workforcecategory_data)
        new_workforcecategory_data["_id"] = str(new_workforcecategory.inserted_id)
    except DuplicateKeyError:
        raise HTTPException(status_code=400, detail="Workforce category with this name already exists.")

    # Prepare JSON-compatible workforce category data
    json_compatible_workforcecategory_data = json.loads(json.dumps(new_workforcecategory_data, default=str))
    return {"workforcecategory_data": json_compatible_workforcecategory_data}

def get_workforcecategories_service(skip: int, limit: int, q: Optional[str], status: Optional[str], sort_by: Optional[str], sort_order: Optional[str], db, current_user) -> dict:
    workforcecategories_collection = db[COLLECTION_NAME]
    query = {}

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

    # Sorting logic
    sort_fields = {
        "category_name": "category_name",
        "description": "description",
        "status": "status",
        "created_date": "created_date"
    }
    sort_field = sort_fields.get(sort_by, "created_date")
    sort_direction = ASCENDING if sort_order == "asc" else DESCENDING

    cursor = workforcecategories_collection.find(query).sort(sort_field, sort_direction).skip(skip).limit(limit)
    workforcecategories = list(cursor)

    #workforcecategories = list(workforcecategories_collection.find(query).skip(skip).limit(limit))
    for workforcecategory in workforcecategories:
        workforcecategory["workforcecategory_id"] = str(workforcecategory["_id"])  # Add `workforcecategory_id`
        for field in ['created_date', 'last_updated']:
            if field in workforcecategory and isinstance(workforcecategory[field], datetime):
                workforcecategory[field] = workforcecategory[field].isoformat()
    total_count = workforcecategories_collection.count_documents(query)

    # Use the correct key name: `workforce_categories`
    return {"total_count": total_count, "workforce_categories": workforcecategories}

def read_workforcecategory_service(workforcecategory_id: str, db) -> dict:
    workforcecategory = db[COLLECTION_NAME].find_one({"_id": str_to_objectid(workforcecategory_id)})
    if workforcecategory:
        workforcecategory["id"] = str(workforcecategory["_id"])
    return workforcecategory

def update_workforcecategory_service(workforcecategory_id: str, workforcecategory_data: WorkforceCategoryUpdate, db) -> dict:
    workforcecategories_collection = db[COLLECTION_NAME]
    existing_workforcecategory = workforcecategories_collection.find_one({"_id": str_to_objectid(workforcecategory_id)})
    if not existing_workforcecategory:
        raise HTTPException(status_code=404, detail="Workforce category not found")
    
    update_data = {key: value for key, value in workforcecategory_data.dict().items() if value is not None}
    result = workforcecategories_collection.update_one({"_id": str_to_objectid(workforcecategory_id)}, {"$set": update_data})
    
    if result.matched_count == 0:
        raise HTTPException(status_code=404, detail="Workforce category not found")

    updated_workforcecategory = workforcecategories_collection.find_one({"_id": str_to_objectid(workforcecategory_id)})
    if updated_workforcecategory:
        updated_workforcecategory["id"] = str(updated_workforcecategory.pop("_id"))  # Change `_id` to `id`
        return updated_workforcecategory

    raise HTTPException(status_code=404, detail="Workforce category not found after update")

def delete_workforcecategory_service(workforcecategory_id: str, db) -> dict:
    workforcecategories_collection = db[COLLECTION_NAME]
    workforcecategory = workforcecategories_collection.find_one({"_id": str_to_objectid(workforcecategory_id)})
    if not workforcecategory:
        raise HTTPException(status_code=404, detail="Workforce category not found")
    # Convert '_id' to 'id' to match the response model
    workforcecategory["id"] = str(workforcecategory["_id"])
    del workforcecategory["_id"]  # Remove the '_id' field from the response
    workforcecategories_collection.delete_one({"_id": str_to_objectid(workforcecategory_id)})
    return workforcecategory