from datetime import datetime, timedelta
from sqlalchemy import desc, func
from sqlalchemy.orm.query import Query
from typing import Optional

from apollo_main_db.apollo.models import ApolloVisitLog
from src.constants.users.visits import VISITS_DAYS, VISITS_LIMIT, VisitDsp, VisitType
from src.db.base import session


def get_visits_query(
    user_id: str, dsp: Optional[str] = None, visit_type: str = VisitType.TRACK, limit: int = VISITS_LIMIT
) -> Query:
    """Get visits query.

    Args:
        user_id: Current user ID.
        dsp: Vendor.
        visit_type: Visit type.
        limit: Most visited limit.

    Returns:
        Query: Get visits query.

    """
    start_date = datetime.now() - timedelta(days=VISITS_DAYS)

    query = (
        session.query(
            ApolloVisitLog.instance_id.label("instance_id"),
            func.count(ApolloVisitLog.instance_id).label("count"),
            ApolloVisitLog.dsp_id.label("dsp_id"),
            func.max(ApolloVisitLog.created_at).label("last_visit_at"),
        )
        .filter(ApolloVisitLog.user_id == user_id)
        .filter(ApolloVisitLog.type_id == VisitType.ID_MAPPING[visit_type])
        .filter(ApolloVisitLog.created_at > start_date)
    )

    if dsp:
        query = query.filter(ApolloVisitLog.dsp_id == VisitDsp.ID_MAPPING[dsp])

    query = (
        query.group_by(ApolloVisitLog.instance_id)
        .order_by(desc("count"), func.max(ApolloVisitLog.created_at).desc())
        .limit(limit)
    )

    return query