from datetime import datetime
from bson import ObjectId
from app.v1.models.platform.schedules import ScheduleCreate, ScheduleUpdate
from app.db import database
from typing import Optional
from pymongo import ASCENDING, DESCENDING
from app.v1.services.sequence import get_next_sequence_value_int

SCHEDULES_COLLECTION = "schedules"

def create_schedule_service(schedule: ScheduleCreate, db: database.MongoDB) -> dict:
    data = schedule.dict()
    data["created_date"] = datetime.utcnow()

    # Fetch and attach customer info
    if data.get("customer_id"):
        customer = db["customers"].find_one({"_id": ObjectId(data["customer_id"])});
        if customer:
            data["customer_info"] = {
                "first_name": customer.get("first_name"),
                "last_name": customer.get("last_name"),
                "phone": customer.get("phone"),
                "email": customer.get("email"),
                "customer_id": str(customer["_id"]),
            }

    # Fetch and attach workforce info
    if data.get("workforce_id"):
        workforce = db["workforce"].find_one({"_id": ObjectId(data["workforce_id"])});
        if workforce:
            data["workforce_info"] = {
                "first_name": workforce.get("first_name"),
                "last_name": workforce.get("last_name"),
                "mobile_number": workforce.get("mobile_number"),
                "email_id": workforce.get("email_id"),
                "workforce_id": str(workforce["_id"]),
            }

    # Fetch and attach fleet info
    if data.get("fleet_id"):
        fleet = db["fleets"].find_one({"_id": ObjectId(data["fleet_id"])});
        if fleet:
            data["fleet_info"] = {
                "fleet_name": fleet.get("fleet_name"),
                "fleet_id": str(fleet["_id"]),
            }

    data["sch_id"] = get_next_sequence_value_int("sch_id", db)

    result = db[SCHEDULES_COLLECTION].insert_one(data)
    data["schedule_id"] = str(result.inserted_id)
    return data

def get_schedule_service(schedule_id: str, db: database.MongoDB) -> dict:
    schedule = db[SCHEDULES_COLLECTION].find_one({"_id": ObjectId(schedule_id)})
    if schedule:
        schedule["schedule_id"] = str(schedule["_id"])
        return schedule
    return None

# def list_schedules_service(account_id: str, db: database.MongoDB) -> dict:
#     query = {"account_id": account_id}
#     cursor = db[SCHEDULES_COLLECTION].find(query)
#     schedules = []
#     for item in cursor:
#         item["schedule_id"] = str(item["_id"])
#         schedules.append(item)
#     return {"total_count": len(schedules), "schedules": schedules}

def update_schedule_service(schedule_id: str, update: ScheduleUpdate, db: database.MongoDB) -> dict:
    updates = {k: v for k, v in update.dict().items() if v is not None}
    db[SCHEDULES_COLLECTION].update_one({"_id": ObjectId(schedule_id)}, {"$set": updates})
    return get_schedule_service(schedule_id, db)

def delete_schedule_service(schedule_id: str, db: database.MongoDB) -> dict:
    schedule = get_schedule_service(schedule_id, db)
    if schedule:
        db[SCHEDULES_COLLECTION].delete_one({"_id": ObjectId(schedule_id)})
    return schedule


def list_schedules_service(
    account_id: str,    
    skip: int = 0,
    limit: int = 10,
    search_query: Optional[str] = None,
    status: Optional[str] = None,
    start_date_from: Optional[str] = None,
    start_date_to: Optional[str] = None,
    customer_id: Optional[str] = None,
    workforce_id: Optional[str] = None,
    fleet_id: Optional[str] = None,
    sort_by: Optional[str] = "created_date",
    order_by: Optional[int] = 1,
    db=None
):

    query = {"account_id": account_id}

    if search_query:
        query["schedule_name"] = {"$regex": search_query, "$options": "i"}

    if status:
        query["status"] = status

    # Add date range filter
    if start_date_from or start_date_to:
        date_filter = {}
        if start_date_from:
            date_filter["$gte"] = datetime.strptime(start_date_from, "%Y-%m-%d")
        if start_date_to:
            date_filter["$lte"] = datetime.strptime(start_date_to, "%Y-%m-%d")
        query["created_date"] = date_filter

    if customer_id:
        query["customer_id"] = customer_id
    if workforce_id:
        query["workforce_id"] = workforce_id
    if fleet_id:
        query["fleet_id"] = fleet_id

    #sort_order = ASCENDING if order_by == 0 else DESCENDING
    # cursor = db["schedules"].find(query).sort("_id", sort_order).skip(skip).limit(limit)
    # total_count = db["schedules"].count_documents(query)

    # Define allowed fields
    allowed_sort_fields = {
        "schedule_name": "schedule_name",
        "description": "description",
        "start_time": "start_time",
        "end_time": "end_time",
        "created_date": "created_date"
    }

    sort_field = allowed_sort_fields.get(sort_by, "created_date")
    sort_direction = ASCENDING if order_by == 0 else DESCENDING

    cursor = db["schedules"].find(query).sort(sort_field, sort_direction).skip(skip).limit(limit)
    total_count = db["schedules"].count_documents(query)

    results = []
    for doc in cursor:
        doc["schedule_id"] = str(doc["_id"])
        results.append(doc)

    return {"total_count": total_count, "schedules": results}