from typing import List, Dict, Any, Optional

from core.logger_config import get_logger
from .queries import (
    in_placeholders,
    QUERY_USER_BASIC_DETAILS,
    QUERY_USER_IDS_KEYSET,
    QUERY_USER_IDS_KEYSET_WITH_RECENT_ACTIVITY_LOGS,
    QUERY_USER_HAS_ACTIVITY_LOG_SINCE,
    QUERY_ACTIVITY_LOGS,
    QUERY_ORDERS,
    QUERY_ORDER_ITEMS,
    QUERY_IMPRESSIONS,
    QUERY_REVIEWS_AND_RATINGS,
    QUERY_CART_DATA,
    QUERY_CART_ITEMS_IN_WINDOW,
    QUERY_CART_STATS,
    QUERY_CART_TOP_CONSIDERED_NOT_BOUGHT,
    QUERY_CART_CONSIDERED_NOT_BOUGHT_BY_CATEGORY,
    QUERY_DIETARY_PREFERENCES,
    QUERY_DIETARY_PREFERENCE_LABELS,
)

logger = get_logger(__name__)


class DataFetcher:
    """
    Handles raw SQL execution for user-centric profiling.
    Strictly follows the 'One User at a Time' principle.
    All temporal queries require start_date and end_date.
    """

    def __init__(self, db_connection):
        self.db = db_connection

    def _execute(self, query: str, params: tuple = ()) -> List[Dict[str, Any]]:
        return self.db.execute(query, params)

    def fetch_user_basic_details(self, user_id: int) -> Dict[str, Any]:
        logger.info(f"Fetching basic details for User {user_id}")
        results = self._execute(QUERY_USER_BASIC_DETAILS, (user_id,))
        return results[0] if results else {}

    def fetch_user_ids_keyset(
        self,
        last_id: int,
        page_size: int,
        *,
        recent_activity_days: Optional[int] = None,
    ) -> List[int]:
        """
        User ids with id > last_id, ascending, at most page_size.

        If ``recent_activity_days`` is set, only users with ≥1 ``user_activity_logs`` row
        in that many days (SQL EXISTS).
        """
        if recent_activity_days is not None and recent_activity_days > 0:
            rows = self._execute(
                QUERY_USER_IDS_KEYSET_WITH_RECENT_ACTIVITY_LOGS,
                (page_size, last_id, recent_activity_days),
            )
        else:
            rows = self._execute(QUERY_USER_IDS_KEYSET, (page_size, last_id))
        return [int(r["id"]) for r in rows]

    def user_has_recent_activity_logs(self, user_id: int, activity_days: int) -> bool:
        """True if ``dbo.user_activity_logs`` has ≥1 row for user in the last ``activity_days``."""
        if activity_days <= 0:
            return True
        rows = self._execute(QUERY_USER_HAS_ACTIVITY_LOG_SINCE, (user_id, activity_days))
        if not rows:
            return False
        return int(rows[0].get("has_activity") or 0) == 1

    def fetch_activity_logs(self, user_id: int, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        logger.info(f"Fetching activity logs for User {user_id} between {start_date} and {end_date}")
        return self._execute(QUERY_ACTIVITY_LOGS, (user_id, start_date, end_date))

    def fetch_orders(self, user_id: int, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        return self._execute(QUERY_ORDERS, (user_id, start_date, end_date))

    def fetch_order_items(self, order_ids: List[int]) -> List[Dict[str, Any]]:
        if not order_ids:
            return []
        query = QUERY_ORDER_ITEMS.format(in_placeholders(len(order_ids)))
        return self._execute(query, tuple(order_ids))

    def fetch_impressions(self, user_id: int, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        return self._execute(QUERY_IMPRESSIONS, (user_id, start_date, end_date))

    def fetch_reviews_and_ratings(self, user_id: int, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        return self._execute(QUERY_REVIEWS_AND_RATINGS, (user_id, start_date, end_date))

    def fetch_cart_data(self, user_id: int, start_date: str, end_date: str) -> List[Dict[str, Any]]:
        return self._execute(QUERY_CART_DATA, (user_id, start_date, end_date))

    def fetch_cart_add_to_cart_signals(
        self, user_id: int, start_date: str, end_date: str
    ) -> Dict[str, Any]:
        """
        Runs the four add_to_cart_logs queries and returns a single dict:
        items_in_window, stats, top_considered_not_bought, considered_not_bought_by_category.
        Uses the same window as the rest of the profile. DB or schema failures propagate
        (no silent empty structure) so the pipeline can fail loudly instead of looking like no cart activity.
        """
        params = (user_id, start_date, end_date)
        empty_stats = {
            "add_to_cart_events_per_item_ordered": 0.0,
            "count_distinct_items_added_but_never_ordered": 0,
            "count_distinct_items_added_then_removed_from_cart": 0,
            "total_add_to_cart_events_in_window": 0,
            "total_items_ordered_in_window": 0,
        }
        try:
            items_in_window = self._execute(QUERY_CART_ITEMS_IN_WINDOW, params)

            # QUERY_CART_STATS: 6 x (user_id, start_date, end_date)
            stats_params = params * 6
            stats_rows = self._execute(QUERY_CART_STATS, stats_params)
            stats = stats_rows[0] if stats_rows else empty_stats

            top_considered_not_bought = self._execute(
                QUERY_CART_TOP_CONSIDERED_NOT_BOUGHT, params * 2
            )

            considered_not_bought_by_category = self._execute(
                QUERY_CART_CONSIDERED_NOT_BOUGHT_BY_CATEGORY, params * 2
            )

            return {
                "items_in_window": items_in_window or [],
                "stats": stats,
                "top_considered_not_bought": top_considered_not_bought or [],
                "considered_not_bought_by_category": considered_not_bought_by_category or [],
            }
        except Exception:
            logger.exception(
                "fetch_cart_add_to_cart_signals failed (user_id=%s, window=%s..%s)",
                user_id,
                start_date,
                end_date,
            )
            raise

    def fetch_dietary_preferences(self, user_id: int) -> Dict[str, Any]:
        results = self._execute(QUERY_DIETARY_PREFERENCES, (user_id,))
        return results[0] if results else {}

    def fetch_dietary_preference_labels(self, slugs: List[str]) -> List[Dict[str, Any]]:
        if not slugs:
            return []
        query = QUERY_DIETARY_PREFERENCE_LABELS.format(in_placeholders(len(slugs)))
        return self._execute(query, tuple(slugs))