from datetime import datetime
from uuid import UUID
from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from src.core.exceptions import APIException
from src.utils.pagination import QueryPaginator
from sqlalchemy import func, cast, Integer,and_,or_
from src.apps.wine.word_alias.models.word_alias import WordAlias
from src.apps.wine.wine_log.schemas.job_log import JobLogCreateSchema,JobLogFilterSchema,JobLogOutputSchema,JobLogSchema,JobLogUpdateSchema
from src.apps.wine.wine_log.models.wine_log import JobLog
from src.utils.constants import API_PREFIXES
from src.core.config import settings
from typing import Dict, List
from sqlalchemy.sql.expression import nullslast
from src.core.exceptions import APIException


async def get_all_job_logs_service(
    db: Session, payload: JobLogFilterSchema, page: int = 1, per_page: int = 10, sort_by: List[str] = None
) -> Dict:
    """Get all job logs with pagination and filtering."""
    
    query, _ = await _build_job_log_query(db, payload, sort_by)
    offset = (page - 1) * per_page

    paginator = QueryPaginator(
        query=query,
        schema=JobLogOutputSchema,
        url="".join([str(settings.api_base_url()), API_PREFIXES.JOB_LOG]),
        offset=offset,
        limit=per_page,
    )

    return paginator.paginate()

async def get_job_log_by_id_service(db: Session, job_log_id: int) -> JobLogOutputSchema:
    """Get a job log by its ID."""
    try: 
        job_log = db.query(JobLog).filter(JobLog.id == job_log_id, JobLog.deleted_at.is_(None)).first()
        
        if not job_log:
            raise APIException(
                module="get_job_log_by_id_service",
                error=f"Job log with ID {job_log_id} not found.",
                status_code=status.HTTP_404_NOT_FOUND,
                message="Job log not found."
            )
        
        return job_log
    except Exception as e:
        raise APIException(
            module="get_job_log_by_id_service",
            error=str(e),
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error retrieving job log by ID."
        )

async def create_job_log_service(db: Session, payload: JobLogCreateSchema) -> JobLogOutputSchema:
    """Create a new job log."""
    try:
        new_job_log = JobLog(**payload.model_dump(exclude_unset=True))
        db.add(new_job_log)
        db.commit()
        db.refresh(new_job_log)
        return JobLogOutputSchema.model_validate(new_job_log)
    except Exception as e:
        db.rollback()
        raise APIException(
            module="create_job_log_service",
            error=str(e),
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error creating job log."
        )
        
async def update_job_log_service(db: Session, job_log_id: int, payload: JobLogUpdateSchema) -> JobLogOutputSchema:
    """Update an existing job log."""
    # Fetch the ORM JobLog instance
    job_log_obj = await get_job_log_by_id_service(db, job_log_id)
    try:
        for key, value in payload.model_dump(exclude_unset=True).items():
            setattr(job_log_obj, key, value)
        db.commit()
        db.refresh(job_log_obj)
        return JobLogOutputSchema.model_validate(job_log_obj)
    except Exception as e:
        db.rollback()
        raise APIException(
            module="update_job_log_service",
            error=str(e),
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error updating job log."
        )
        
async def delete_job_log_service(db: Session, job_log_id: int) -> None:
    """Delete a job log by its ID."""
    # Fetch the ORM JobLog instance
    job_log_obj = await get_job_log_by_id_service(db, job_log_id)
    try:
        job_log_obj.deleted_at = datetime.utcnow()
        db.commit()
        return None
    except Exception as e:
        db.rollback()
        raise APIException(
            module="delete_job_log_service",
            error=str(e),
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error deleting job log."
        )

async def _build_job_log_query(db: Session, payload: JobLogFilterSchema, sort_by: List[str] = None) -> tuple:
    """Build the query for job logs based on filters and sorting."""
    query = db.query(JobLog).filter(JobLog.deleted_at.is_(None))

    if hasattr(payload, 'status') and payload.status:
        status_list = [s.strip() for s in str(payload.status).split(',') if s.strip()]
        if status_list:
            query = query.filter(JobLog.status.in_(status_list))
            
    if hasattr(payload, 'search') and payload.search:
        search_term = payload.search.strip()
        if search_term:
            query = query.filter(
                or_(
                    JobLog.code.ilike(f"%{search_term}%"),
                    JobLog.name.ilike(f"%{search_term}%"),
                    JobLog.url.ilike(f"%{search_term}%"),
                    JobLog.message.ilike(f"%{search_term}%")
                )
            )

    # Apply sorting if provided
    sort_query = []
    if sort_by:
        for s in sort_by:
            try:
                col = s.replace("-", "")
                if col in [
                    "code",
                    "name",
                    "total_records",
                    "total_prices",
                    "job_date",
                    "date_start",
                    "date_end",
                    "date_created",
                    "last_updated",
                ]:
                    sort_order = getattr(JobLog, col)

                    if s.startswith("-"):
                        sort_order = sort_order.desc()

                    sort_query.append(nullslast(sort_order))
                else:
                    raise APIException(
                        module="get_all_job_logs_service",
                        error=f"Cannot sort with unidentified column '{s}'",
                        status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
                        message="Error retrieving job logs."
                    )
            except AttributeError:
                raise APIException(
                    module="get_all_job_logs_service",
                    error=f"Cannot sort with unidentified column '{s}'",
                    status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
                    message="Error retrieving job logs."
                )

        if sort_query:
            query = query.order_by(*sort_query)

    return query, sort_query