from datetime import datetime, timezone
from sqlalchemy.orm import Session
from fastapi import HTTPException, status
from src.core.exceptions import APIException
from src.apps.base.models.regions import Region, SubRegion
from src.core.config import settings
from src.utils.constants import API_PREFIXES
from src.apps.base.schemas.region import (
    RegionCreateSchema,
    RegionUpdateSchema,
    RegionOutSchema,
    SubRegionCreateSchema,
    SubRegionUpdateSchema,
    SubRegionOutSchema,
    RegionFilterSchema,
    SubRegionFilterSchema
)
from src.apps.base.services.country import get_country_by_id
from src.apps.base.services.region import get_region_by_id
from src.utils.pagination import QueryPaginator
from src.apps.base.models.country import Country
from uuid import UUID
from sqlalchemy.exc import IntegrityError
from typing import Optional, List

# ------------------------ SubRegion ------------------------

async def get_sub_region_by_id(db: Session, sub_region_id: int) -> SubRegion:
    try:
        sub_region = db.query(SubRegion).filter(
            SubRegion.id == sub_region_id,
            SubRegion.deleted_at.is_(None)
        ).first()
        if not sub_region:
            raise APIException(
                module="SubRegion",
                error={"exception": "Sub-region not found"},
                status_code=status.HTTP_404_NOT_FOUND,
                message="Sub-region not found"
            )
        return sub_region
    except Exception as e:
        raise APIException(
            module="SubRegion",
            error={"exception": str(e)},
            status_code=status.HTTP_404_NOT_FOUND,
            message="Error fetching sub-region by ID."
        )


async def get_all_sub_regions(
    db: Session,
    page: int = 1,
    per_page: int = 10,
    sort_by: List[str] = None,
    payload: Optional[SubRegionFilterSchema] = None,
):
    try:
        offset = (page - 1) * per_page
        query = db.query(SubRegion).filter(SubRegion.deleted_at.is_(None))
         
        # applied sorting       
        sort_query = build_subregion_sort_query(sort_by)
        if sort_query:
            query = query.order_by(*sort_query)
            
        # applied filters
        query = apply_subregion_filters(query, payload)

        paginator = QueryPaginator(
            query=query,
            schema=SubRegionOutSchema,
            url="".join([str(settings.api_base_url()), API_PREFIXES.SUB_REGION]),
            offset=offset,
            limit=per_page,
            use_orm=True,
        )
        return paginator.paginate()
    except Exception as e:
        raise APIException(
            module="get_all_sub_regions",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Failed to fetch sub-regions."
        )


async def create_sub_region(db: Session, payload: SubRegionCreateSchema):
    try:
        region = await get_region_by_id(db, payload.region_id)
            
        sub_region = SubRegion(
            name=payload.name,
            region_id= region.id, 
        )

        db.add(sub_region)
        db.commit()
        db.refresh(sub_region)
        return sub_region
    except Exception as e:
        db.rollback()
        raise APIException(
            module="SubRegion",
            error={"exception": str(e)},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Error creating sub-region."
        )


async def update_sub_region(db: Session, sub_region_id: int, payload: SubRegionUpdateSchema) -> SubRegion:
    sub_region = await get_sub_region_by_id(db, sub_region_id)
    try:
        update_data = payload.dict(exclude_unset=True)

        # if "region_uuid" in update_data:
        #     region = db.query(Region).filter(
        #         Region.uuid == update_data["region_uuid"],
        #         Region.is_deleted == False
        #     ).first()
        #     if not region:
        #         raise HTTPException(status_code=404, detail="Region not found")
        #     sub_region.region_id = region.id 
        if "region_id" in update_data:
            region = await get_region_by_id(db, update_data["region_id"])
        for field, value in update_data.items():
                setattr(sub_region, field, value)

        db.commit()
        db.refresh(sub_region)
        return sub_region
    except Exception as e:
        db.rollback()
        raise APIException(
            module="SubRegion",
            error={"exception": str(e)},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Error updating sub-region."
        )


async def delete_sub_region(db: Session, sub_region_id: int) -> None:
    sub_region = await get_sub_region_by_id(db, sub_region_id)
    try:
        sub_region.is_deleted = True
        sub_region.deleted_at = datetime.now(timezone.utc)
        db.commit()
        return None
    except Exception as e:
        db.rollback()
        raise APIException(
            module="SubRegion",
            error={"exception": str(e)},
            status_code=status.HTTP_400_BAD_REQUEST,
            message="Error deleting sub-region."
        )
        
def build_subregion_sort_query(sort_by: List[str] = None):
    """Builds a list of SQLAlchemy sort columns for SubRegion."""
    sort_query = []
    sort_fields = sort_by if sort_by else ["-created_at"]
    for s in sort_fields:
        try:
            col = s.replace("-", "")
            if col in ["name", "created_at", "updated_at", "region_id"]:
                sort_order = getattr(SubRegion, col)
                if s.startswith("-"):
                    sort_order = sort_order.desc()
                sort_query.append(sort_order)
            else:
                raise APIException(
                    module="get_all_sub_regions",
                    error=f"Cannot sort with unidentified column '{s}'",
                    status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
                    message="Error retrieving sub-regions."
                )
        except AttributeError:
            raise APIException(
                module="get_all_sub_regions",
                error=f"Cannot sort with unidentified column '{s}'",
                status_code=status.HTTP_422_UNPROCESSABLE_ENTITY,
                message="Error retrieving sub-regions."
            )
    return sort_query

def apply_subregion_filters(query, payload):
    """Apply filters to SubRegion query based on payload fields."""
    if not payload:
        return query
    if payload.search:
        search_term = f"%{payload.search}%"
        query = query.filter(SubRegion.name.ilike(search_term))
    if payload and payload.region_id:
            region_ids = [int(rid) for rid in payload.region_id.split(",") if rid.isdigit()]
            if region_ids:
                query = query.filter(SubRegion.region_id.in_(region_ids))
    return query