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 pymongo import ASCENDING, DESCENDING

from app.v1.libraries.object import str_to_objectid
from ...models.platform.workforcemodel import (
    Workforce, WorkforceBase, WorkforceUpdate, WorkforceResponseList
)
from app.v1.services.sequence import get_next_sequence_value_int

load_dotenv()

# Collection name
COLLECTION_NAME = "workforce"

def create_workforce_service(workforce: WorkforceBase, db) -> dict:
    workforce_collection = db[COLLECTION_NAME]

    # Check for existing mobile/email
    if workforce_collection.find_one({"$or": [
        {"mobile_number": workforce.mobile_number},
        {"email_id": workforce.email_id}
    ]}):
        raise HTTPException(status_code=400, detail="Workforce with this contact information already exists")

    # Create base data from model
    workforce_data = workforce.dict()  # Changed variable name from new_workforce_data to workforce_data
    workforce_data.update({
        "created_date": datetime.utcnow(),
        "last_updated": datetime.utcnow(),
        "status": "active"  # Added default status
    })

    # Explicitly include photo even if None
    if "photo" not in workforce_data:
        workforce_data["photo"] = None

    try:
        # Insert the complete document
        workforce_data["w_id"] = get_next_sequence_value_int("w_id", db)
        result = workforce_collection.insert_one(workforce_data)
        
        # Get the inserted document
        inserted_data = workforce_collection.find_one({"_id": result.inserted_id})
        
        # Convert for response
        inserted_data["_id"] = str(inserted_data["_id"])
        if inserted_data.get("photo"):
            inserted_data["photo"] = f"{BASE_URL}/public/workforce/{inserted_data['photo']}"
        
        return inserted_data
        
    except DuplicateKeyError:
        raise HTTPException(status_code=400, detail="Duplicate workforce entry")

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

    if q:
        regex_query = {"$regex": q, "$options": "i"}
        query["$or"] = [
            {"first_name": regex_query},
            {"last_name": regex_query},
            {"email_id": regex_query},
            {"mobile_number": regex_query}
        ]
    if status:
        query["status"] = status

    # 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
    sort_fields = {
        "first_name": "first_name",
        "last_name": "last_name",
        "mobile_number": "mobile_number",
        "email_id": "email_id",
        "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 = workforce_collection.find(query).sort(sort_field, sort_direction).skip(skip).limit(limit)
    workforce = list(cursor)

    #workforce = list(workforce_collection.find(query).skip(skip).limit(limit))

    for entry in workforce:
        entry["workforce_id"] = str(entry["_id"])
        for field in ['created_date', 'last_updated', 'date_of_birth', 'join_date']:
            if field in entry and isinstance(entry[field], datetime):
                entry[field] = entry[field].isoformat()

    total_count = workforce_collection.count_documents(query)
    return {"total_count": total_count, "workforce": workforce}


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

def update_workforce_service(workforce_id: str, workforce_data: WorkforceUpdate, db) -> dict:
    workforce_collection = db[COLLECTION_NAME]
    existing = workforce_collection.find_one({"_id": str_to_objectid(workforce_id)})
    if not existing:
        raise HTTPException(status_code=404, detail="Workforce not found")

    update_data = {k: v for k, v in workforce_data.dict().items() if v is not None}
    result = workforce_collection.update_one(
        {"_id": str_to_objectid(workforce_id)},
        {"$set": update_data}
    )
    
    if result.matched_count == 0:
        raise HTTPException(status_code=404, detail="Workforce not found")

    updated = workforce_collection.find_one({"_id": str_to_objectid(workforce_id)})
    if updated:
        updated["id"] = str(updated.pop("_id"))
        return updated

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

def delete_workforce_service(workforce_id: str, db) -> dict:
    workforce_collection = db[COLLECTION_NAME]
    workforce = workforce_collection.find_one({"_id": str_to_objectid(workforce_id)})
    if not workforce:
        raise HTTPException(status_code=404, detail="Workforce not found")
    workforce["id"] = str(workforce["_id"])
    del workforce["_id"]
    workforce_collection.delete_one({"_id": str_to_objectid(workforce_id)})
    return workforce