from datetime import datetime, timezone
from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from src.utils.constants import API_PREFIXES
from src.core.config import settings
from src.utils.pagination import QueryPaginator
from src.core.exceptions import APIException
from src.apps.base.models.country import Country
from sqlalchemy.exc import IntegrityError
from typing import Optional
from src.apps.wine.appellation.models.appellation import Appellation
from src.apps.wine.appellation.schemas.appellation import (
    AppellationCreateSchema,
    AppellationOutputSchema,
    AppellationUpdateSchema
)
from src.apps.base.services.country import get_country_by_id
from src.apps.base.services.region import get_region_by_id,get_sub_region_by_id
from src.apps.base.services.locations import get_location_by_id

async def get_appellations(
    db: Session,
    page: int = 1,
    per_page: int = 10,
) -> any:
    try:
        offset = (page - 1) * per_page
        query = db.query(Appellation).filter(Appellation.deleted_at.is_(None)).order_by(Appellation.date_created.desc())
        paginator = QueryPaginator(
            query=query, 
            schema=AppellationOutputSchema, 
            url="".join([str(settings.api_base_url()), API_PREFIXES.APPELLATION]), 
            offset=offset, 
            limit=per_page, 
            use_orm=True
        )
        return paginator.paginate()
    except Exception as e:
        raise APIException(
            module="get_appellations",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to fetch appellations."
        )

async def get_appellations_by_id(
    db: Session,
    appellation_id: int
) -> AppellationOutputSchema:
    try:
        appellation = db.query(Appellation).filter(
            Appellation.id == appellation_id,
            Appellation.deleted_at.is_(None)
        ).first()       
        if not appellation:
            raise APIException(
                module="get_appellations_by_id",
                error={"appellation_id": appellation_id},
                status_code=status.HTTP_404_NOT_FOUND,
                message="Appellation not found."
            )        
        return appellation
    except Exception as e:
        raise APIException(
            module="get_appellations_by_id",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to fetch appellation by ID."
        )
        
async def create_appellation(
    db: Session,
    payload: AppellationCreateSchema
) -> AppellationOutputSchema:
    
    country = await get_country_by_id(db, payload.country_id) if payload.country_id else None
    region = await get_region_by_id(db, payload.region_id) if payload.region_id else None
    sub_region = await get_sub_region_by_id(db, payload.sub_region_id) if payload.sub_region_id else None
    
    try:
        new_appellation = Appellation(
            name=payload.name,
            country_id=country.id if country else None,
            region_id=region.id if region else None,
            sub_region_id=sub_region.id if sub_region else None,
            date_created=datetime.now(timezone.utc),
            last_updated=datetime.now(timezone.utc)
        )
        db.add(new_appellation)
        db.commit()
        db.refresh(new_appellation)
        return AppellationOutputSchema.model_validate(new_appellation)
    except Exception as e:
        db.rollback()
        raise APIException(
            module="create_appellation",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to create appellation."
        )
        
async def update_appellation(
    db: Session,
    appellation_id: int,
    payload: AppellationUpdateSchema
) -> AppellationOutputSchema:
    
    country = await get_country_by_id(db, payload.country_id) if payload.country_id else None
    region = await get_region_by_id(db, payload.region_id) if payload.region_id else None
    sub_region = await get_sub_region_by_id(db, payload.sub_region_id) if payload.sub_region_id else None
    appellation = await get_appellations_by_id(db, appellation_id)
    
    try:
        appellation.name = payload.name if payload.name else appellation.name
        appellation.country_id = country.id if country else appellation.country_id
        appellation.region_id = region.id if region else appellation.region_id
        appellation.sub_region_id = sub_region.id if sub_region else appellation.sub_region_id
        
        db.commit()
        db.refresh(appellation)
        return AppellationOutputSchema.model_validate(appellation)
    except Exception as e:
        db.rollback()
        raise APIException(
            module="update_appellation",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to update appellation."
        )
        
async def delete_appellation(
    db: Session,
    appellation_id: int
) -> AppellationOutputSchema:
    appellation = await get_appellations_by_id(db, appellation_id)
    
    try:
        appellation.deleted_at = datetime.now(timezone.utc)
        db.commit()
        db.refresh(appellation)
        return None
    except Exception as e:
        db.rollback()
        raise APIException(
            module="delete_appellation",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to delete appellation."
        )