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 src.apps.wine.producer.models.producer import Producer,ProducerKeyword,ProducerNoise
from sqlalchemy import func, cast, Integer,and_,or_
from src.apps.wine.producer.schemas.producer import ProducerFilterSchema,ProducerCreateSchema,ProducerUpdateSchema,ProducerOutputSchema
from src.utils.constants import API_PREFIXES
from src.apps.wine.producer.services.producer_noise import create_producer_noise
from src.apps.wine.producer.schemas.producer_keyword import *
from src.apps.wine.producer.schemas.producer_noise import *
from src.core.config import settings
from src.apps.wine.producer.services.producer_keyword import get_producer_keyword_by_id,create_producer_keyword,update_producer_keyword

async def get_producers(
    db: Session,
    payload: ProducerFilterSchema,
    page: int = 1,
    per_page: int = 10,
) -> any:
    try:
        keywords = [w.strip() for w in payload.keywords.split(",") if w.strip()] if payload.keywords else []
        producers = [p.strip() for p in payload.producers.split(",") if p.strip()] if payload.producers else []
        offset = (page - 1) * per_page
        query = db.query(Producer).order_by(Producer.name_show.asc())
        query = query.filter(Producer.deleted_at == None)
        if payload.filter_id:
            query = query.filter(Producer.literal.ilike(f"%{payload.filter_id}%"))
        if keywords:
            if payload.search_partial:
                query = query.filter(
                    and_(
                        *[Producer.name_show.ilike(f"%{keyword}%") for keyword in keywords]
                    )
                )
            else:
                query = query.filter(
                    and_(
                        *[
                            Producer.name_show.ilike(f"{keyword} %") |  
                            Producer.name_show.ilike(f"% {keyword} %") | 
                            Producer.name_show.ilike(f"% {keyword}") | 
                            Producer.name_show.ilike(f"{keyword}")      
                            for keyword in keywords
                        ]
                    )
                )
        paginator = QueryPaginator(
            query=query, schema=ProducerOutputSchema, url="".join([str(settings.api_base_url()), API_PREFIXES.PRODUCER]), offset=offset, limit=per_page, use_orm=True
        )

        return paginator.paginate()
    except Exception as e:
        raise APIException(
            module="get_producers",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error retrieving producers."
        )
        
async def get_producer_by_id(db: Session, producer_id: int) -> Producer:
    """
    Retrieve a producer by ID.
    """
    try:
        producer = db.query(Producer).filter(Producer.id == producer_id, Producer.deleted_at == None).first()
        if not producer:
            raise APIException(
                module="get_producer_by_id",
                error={"exception": "Producer not found"},
                status_code=status.HTTP_404_NOT_FOUND,
                message="Producer not found."
            )
        return ProducerOutputSchema.model_validate(producer)
    except Exception as e:
        raise APIException(
            module="get_producer_by_id",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error retrieving producer by ID."
        )
  
async def get_literal_by_reg_code(db: Session, reg_code: str) -> any:
    try:
        # Validate reg_code
        if not reg_code or len(reg_code) < 2:
            raise APIException(
                module="get_literal_by_reg_code",
                error={"reg_code": "reg_code must be at least 2 characters long."},
                status_code=status.HTTP_400_BAD_REQUEST,
                message="Invalid reg_code."
            )
        prefix = reg_code[:2].lower()
        if not prefix.isalpha():
            raise APIException(
                module="get_literal_by_reg_code",
                error={"reg_code": "reg_code prefix must contain only alphabetic characters."},
                status_code=status.HTTP_400_BAD_REQUEST,
                message="Invalid reg_code prefix."
            )
        max_num = db.query(
            func.max(
                cast(func.nullif(func.substr(Producer.literal, 3), ''), Integer)
            )
        ).filter(
            Producer.literal.ilike(f"{prefix}%"),
            func.length(Producer.literal) > 2,
            func.substr(Producer.literal, 1, 2) == prefix
        ).scalar() or 0
        new_literal = f"{prefix}{max_num + 1}"
        return new_literal
    except Exception as e:
        raise APIException(
            module="get_literal_by_reg_code",
            error={"exception": str(e)},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Error retrieving literal by reg_code."
        )  
    
async def update_producer(
    db: Session,
    producer_id: int,
    payload: ProducerUpdateSchema
) -> Producer:
    """
    Update a producer's details.
    """
    try:
        producer = db.query(Producer).filter(Producer.id == producer_id, Producer.deleted_at == None).first()
        if not producer:
            raise APIException(
                module="update_producer",
                error={"producer_id": "Producer not found."},
                status_code=status.HTTP_404_NOT_FOUND,
                message="Producer not found."
            )
        
        producer.name = payload.name_show
        producer.name_show = payload.name_show
        db.commit()
        
        # Update or create producer keyword if provided
        if payload.keyword is not None:
            keyword_obj = await get_producer_keyword_by_id(
                db=db,
                producer_id=producer.id
            )
            if keyword_obj:
                await update_producer_keyword(
                    db=db,
                    producer_id=producer.id,
                    keyword_data=ProducerKeywordUpdateSchema(keyword=payload.keyword)
                )
            else:
                await create_producer_keyword(
                    db=db,
                    producer_keyword=ProducerKeywordAdditionSchema(
                        producer_id=producer.id,
                        keyword=payload.keyword,
                    )
                )
        db.refresh(producer)
        return ProducerOutputSchema.model_validate(producer)
    except Exception as e:
        raise APIException(
            module="update_producer",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error updating producer."
        )
        
async def create_producer(
    db: Session,
    payload: ProducerCreateSchema
) -> Producer:
    """
    Create a new producer.
    """
    created_producer = Producer(
        name=payload.producer_show,
        name_show=payload.producer_show,
        literal=payload.literal,
    )
    try:
        db.add(created_producer)
        db.commit()
        db.refresh(created_producer)
    except Exception as e:
        raise APIException(
            module="create_wine",
            error={"exception": str(e)},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Error creating producer."
        )
    if payload.producer_keyword:
        await create_producer_keyword(
            db=db,
            producer_keyword=ProducerKeywordAdditionSchema(
                producer_id=created_producer.id,
                keyword=payload.producer_keyword,
            )
        )
    if payload.producer_noise:
        for noise in payload.producer_noise:
            await create_producer_noise(
                db=db,
                producer_noise=ProducerNoiseAdditionSchema(
                    producer_id=created_producer.id,
                    noise=noise,
                )
            )
    db.refresh(created_producer)
    return ProducerOutputSchema.model_validate(created_producer)