from datetime import datetime, timezone
from typing import Dict, List, Optional, Tuple
from uuid import UUID

from fastapi import HTTPException, status
from sqlalchemy import and_, func, or_
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session, aliased
from sqlalchemy.sql.expression import nullslast

from src.apps.base.models.country import Country
from src.apps.web_crawler.models.web_crawler import WebCrawler
from src.apps.wine.retailer.schemas.retailer import (
    RetailerCreateSchema,
    RetailerFilterSchema,
    RetailerListOutputSchema,
    RetailerOutputSchema,
    RetailerUpdateSchema,
)
from src.apps.wine.wine.models.wine import WineDb
from src.apps.wine.wine_log.models.wine_log import MatchedLog
from src.core.config import settings
from src.core.exceptions import APIException
from src.utils.constants import API_PREFIXES
from src.utils.pagination import QueryPaginator
from src.apps.wine.retailer.models.retailer import Retailer


async def get_all_retailers(
    db: Session,
    payload: RetailerFilterSchema = None,
    page: int = 1,
    per_page: int = 10,
    paginated: bool = True,
    sort_by: List[str] = None,
) -> any:
    """
    Get rows with unique MatchedLog.code and the latest MatchedLog (by last_updated),
    joined with WebCrawler on code. Return paginated if requested, otherwise all.
    """
    try:
        query = await _build_retailer_matched_log_query(db, payload, sort_by)

        if paginated:
            offset = (page - 1) * per_page
            paginator = QueryPaginator(
                query=query,
                schema=RetailerListOutputSchema,
                url="".join([str(settings.api_base_url()), API_PREFIXES.RETAILER]),
                offset=offset,
                limit=per_page,
                use_orm=True,
            )
            return paginator.paginate()
        print(query.all())
        return [
            RetailerListOutputSchema(**{"code": code, "last_updated": last_updated})
            for code, last_updated in query.all()
        ]
    except Exception as e:
        raise APIException(
            module="get_all_retailers",
            error={"exception": str(e)},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Error retrieving retailers matched logs.",
        )


async def _build_retailer_matched_log_query(db: Session, payload: RetailerFilterSchema, sort_by: List[str] = None):
    """
    Build a query returning latest MatchedLog per code joined with WebCrawler.
    """

    # query = (
    #     db.query(MatchedLog.code, MatchedLog.last_updated)
    #     .filter(MatchedLog.deleted_at.is_(None))
    #     .filter(MatchedLog.code.isnot(None))
    #     .filter(MatchedLog.code != "")
    #     .group_by(MatchedLog.code, MatchedLog.last_updated)
    #     .order_by(MatchedLog.last_updated.desc())
    # )

    subq = (
        db.query(MatchedLog.code, func.max(MatchedLog.last_updated).label("last_updated"))
        .filter(MatchedLog.deleted_at.is_(None))
        .filter(MatchedLog.code.isnot(None))
        .filter(MatchedLog.code != "")
        .group_by(MatchedLog.code)
        .subquery()
    )

    query = db.query(subq.c.code, subq.c.last_updated)

    # Search filter: by code or crawler name
    if getattr(payload, "search", None):
        term = payload.search.strip()
        if term:
            query = query.filter(MatchedLog.code.ilike(f"%{term}%"))

    return query


async def get_retailers_map(db: Session) -> Dict[str, Retailer]:
    """
    Asynchronously creates a map of retailer codes (lowercased) to Retailer objects.

    Args:
        session (Session): SQLAlchemy session for database interaction.

    Returns:
        Dict[str, Retailer]: A dictionary mapping retailer codes to Retailer objects.
    """
    retailers_map = {}

    # Query all Retailer objects from the database
    retailers = db.query(Retailer).filter(Retailer.deleted_at.is_(None)).all()

    for retailer in retailers:
        if retailer.code:
            retailers_map[retailer.code.lower()] = retailer

    return retailers_map