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 SubAppellation
from src.apps.wine.appellation.schemas.sub_appellation import (
    SubAppellationCreateSchema,
    SubAppellationUpdateSchema,
    SubAppellationOutputSchema
)
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
from src.apps.wine.appellation.services.appellation import get_appellations_by_id

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

async def get_sub_appellations_by_id(
    db: Session,
    sub_appellation_id: int
) -> SubAppellationOutputSchema:
    try:
        sub_appellation = db.query(SubAppellation).filter(
            SubAppellation.id == sub_appellation_id,
            SubAppellation.deleted_at.is_(None)
        ).first()      
        if not sub_appellation:
            raise APIException(
                module="get_sub_appellations_by_id",
                error={"sub_appellation_id": sub_appellation_id},
                status_code=status.HTTP_404_NOT_FOUND,
                message="Sub-Appellation not found."
            )    
        return sub_appellation
    except Exception as e:
        raise APIException(
            module="get_sub_appellations_by_id",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to fetch sub-appellation by ID."
        )
        
async def create_sub_appellation(
    db: Session,
    payload: SubAppellationCreateSchema
) -> SubAppellationOutputSchema:
    
    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, payload.appellation_id) if payload.appellation_id else None
    
    try:
        sub_appellation = SubAppellation(
            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,
            appellation_id=appellation.id if appellation else None
        )
        db.add(sub_appellation)
        db.commit()
        db.refresh(sub_appellation)
        return SubAppellationOutputSchema.model_validate(sub_appellation)
    except Exception as e:
        db.rollback()
        raise APIException(
            module="create_sub_appellation",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to create sub-appellation."
        )
        
async def update_sub_appellation(
    db: Session,
    sub_appellation_id: int,
    payload: SubAppellationUpdateSchema
) -> SubAppellationOutputSchema:
    
    sub_appellation = await get_sub_appellations_by_id(db, sub_appellation_id)
    
    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, payload.appellation_id) if payload.appellation_id else None
    
    try:
        sub_appellation.name = payload.name or sub_appellation.name
        sub_appellation.country_id = country.id if country else sub_appellation.country_id
        sub_appellation.region_id = region.id if region else sub_appellation.region_id
        sub_appellation.sub_region_id = sub_region.id if sub_region else sub_appellation.sub_region_id
        sub_appellation.appellation_id = appellation.id if appellation else sub_appellation.appellation_id
        
        db.commit()
        db.refresh(sub_appellation)
        return SubAppellationOutputSchema.model_validate(sub_appellation)
    except Exception as e:
        db.rollback()
        raise APIException(
            module="update_sub_appellation",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to update sub-appellation."
        )
        
async def delete_sub_appellation(
    db: Session,
    sub_appellation_id: int
) -> None:
    sub_appellation = await get_sub_appellations_by_id(db, sub_appellation_id)
    
    try:
        sub_appellation.deleted_at = datetime.now(timezone.utc)
        db.commit()
        db.refresh(sub_appellation)
        return None
    except Exception as e:
        db.rollback()
        raise APIException(
            module="delete_sub_appellation",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to delete sub-appellation."
        )