"""
SQL queries for user-centric profiling. Read-only; all temporal queries use start_date/end_date.
"""


def in_placeholders(n: int) -> str:
    """Returns ?, ?, ... (n times) for IN (...). ``n`` must be >= 1 (empty IN () is invalid SQL)."""
    if n <= 0:
        raise ValueError("in_placeholders requires n > 0 (empty IN lists are invalid)")
    return ", ".join(["?"] * n)


# Params: (user_id,)
QUERY_USER_BASIC_DETAILS = "SELECT * FROM users WHERE id = ?"

# Params: (page_size, last_id) — keyset page of user ids (use last_id=0 for first page)
QUERY_USER_IDS_KEYSET = """
SELECT TOP (?) id FROM users WHERE id > ? ORDER BY id ASC
"""

# Params: (page_size, last_id, activity_days) — keyset, only users with ≥1 user_activity_logs row in window
QUERY_USER_IDS_KEYSET_WITH_RECENT_ACTIVITY_LOGS = """
SELECT TOP (?) u.id
FROM users u
WHERE u.id > ?
AND EXISTS (
    SELECT 1 FROM dbo.user_activity_logs a
    WHERE a.user_id = u.id
    AND a.created_at >= DATEADD(day, -?, GETDATE())
)
ORDER BY u.id ASC
"""

# No params — same row universe as ``QUERY_USER_IDS_KEYSET`` (all ``users.id``).
QUERY_COUNT_ALL_USER_IDS = """
SELECT COUNT_BIG(*) AS n FROM users
"""

# Params: (activity_days,) — same predicate as ``QUERY_USER_IDS_KEYSET_WITH_RECENT_ACTIVITY_LOGS``.
QUERY_COUNT_USER_IDS_WITH_RECENT_ACTIVITY = """
SELECT COUNT_BIG(*) AS n
FROM users u
WHERE EXISTS (
    SELECT 1 FROM dbo.user_activity_logs a
    WHERE a.user_id = u.id
    AND a.created_at >= DATEADD(day, -?, GETDATE())
)
"""

# Params: (user_id, activity_days) — has_activity 0 or 1
QUERY_USER_HAS_ACTIVITY_LOG_SINCE = """
SELECT CAST(
    CASE WHEN EXISTS (
        SELECT 1 FROM dbo.user_activity_logs
        WHERE user_id = ?
        AND created_at >= DATEADD(day, -?, GETDATE())
    ) THEN 1 ELSE 0 END
AS int) AS has_activity
"""

# Params: (user_id, start_date, end_date)
QUERY_ACTIVITY_LOGS = """
SELECT * FROM user_activity_logs
WHERE user_id = ?
AND created_at BETWEEN ? AND ?
ORDER BY created_at DESC
"""

# Params: (user_id, start_date, end_date)
QUERY_ORDERS = """
SELECT om.*, rd.restaurant_name,
       coa.area AS order_area, coa.city AS order_city
FROM restaurant_order_master om
LEFT JOIN restaurant_details rd ON rd.restaurant_id = om.restaurant_id
LEFT JOIN customer_orders_address coa ON coa.order_id = om.id
WHERE om.user_id = ?
AND om.is_deleted = 0
AND om.created_at BETWEEN ? AND ?
ORDER BY om.created_at DESC
"""

# Params: (order_id, ...) - use QUERY_ORDER_ITEMS.format(in_placeholders(len(order_ids)))
QUERY_ORDER_ITEMS = """
SELECT oi.*, rd.restaurant_name
FROM order_item_details oi
LEFT JOIN restaurant_details rd ON rd.restaurant_id = oi.restaurant_id
WHERE oi.order_id IN ({})
"""

# Params: (user_id, start_date, end_date)
QUERY_IMPRESSIONS = """
SELECT * FROM restaurant_impression_logs
WHERE user_id = ?
AND created_at BETWEEN ? AND ?
"""

# Params: (user_id, start_date, end_date)
QUERY_REVIEWS_AND_RATINGS = """
SELECT r.*, rt.rating, rd.restaurant_name
FROM reviews r
LEFT JOIN ratings rt ON rt.review_id = r.id
LEFT JOIN restaurant_details rd ON rd.restaurant_id = r.restaurant_id
WHERE r.user_id = ?
AND r.is_deleted = 0
AND r.status = 1
AND r.created_at BETWEEN ? AND ?
ORDER BY r.created_at DESC
"""

# Params: (user_id, start_date, end_date)
QUERY_CART_DATA = """
SELECT cm.*, rd.restaurant_name
FROM cart_master cm
LEFT JOIN restaurant_details rd ON rd.restaurant_id = cm.restaurant_id
WHERE cm.user_id = ?
AND cm.created_at BETWEEN ? AND ?
ORDER BY cm.created_at DESC
"""

# Params: (user_id,)
QUERY_DIETARY_PREFERENCES = """
SELECT c.dietary_preferences, c.special_requests
FROM customers c
WHERE c.user_id = ?
"""

# Params: (slug, ...) - use QUERY_DIETARY_PREFERENCE_LABELS.format(in_placeholders(len(slugs)))
QUERY_DIETARY_PREFERENCE_LABELS = "SELECT slug FROM dietary_preferences WHERE slug IN ({})"

# --- Cart add_to_cart_logs signals. Params: (user_id, start_date, end_date) for each. ---

# 1. Items in cart: per-item cart activity in window (all actions: add + remove)
QUERY_CART_ITEMS_IN_WINDOW = """
SELECT
  acl.item_id,
  ISNULL(mid.item_name, 'Unknown') AS item_name,
  COUNT(*) AS total_cart_events_in_window
FROM add_to_cart_logs acl
LEFT JOIN menu_items_details mid ON mid.menu_item_id = acl.item_id AND mid.language_id = 1
WHERE acl.user_id = ?
  AND acl.created_at BETWEEN ? AND ?
GROUP BY acl.item_id, mid.item_name
ORDER BY total_cart_events_in_window DESC
"""

# 2. Stats: one row (cart vs order ratio, counts)
QUERY_CART_STATS = """
SELECT
  ISNULL(CAST(total_add_to_cart_events_in_window AS FLOAT) / NULLIF(total_items_ordered_in_window, 0), 0) AS add_to_cart_events_per_item_ordered,
  count_distinct_items_added_but_never_ordered,
  count_distinct_items_added_then_removed_from_cart,
  total_add_to_cart_events_in_window,
  total_items_ordered_in_window
FROM (
  SELECT
    (SELECT COUNT(*) FROM add_to_cart_logs a
     WHERE a.user_id = ? AND a.created_at BETWEEN ? AND ?
     AND LOWER(LTRIM(RTRIM(ISNULL(a.action, '')))) = 'add') AS total_add_to_cart_events_in_window,
    (SELECT COUNT(*) FROM order_item_details oi
     INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id
     WHERE rom.user_id = ? AND rom.is_deleted = 0
     AND rom.created_at BETWEEN ? AND ?) AS total_items_ordered_in_window,
    (SELECT COUNT(DISTINCT acl.item_id) FROM add_to_cart_logs acl
     WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ?
     AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add'
     AND NOT EXISTS (
       SELECT 1 FROM order_item_details oi
       INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id
       WHERE rom.user_id = ? AND rom.is_deleted = 0
         AND rom.created_at BETWEEN ? AND ?
         AND oi.menu_item_id = acl.item_id
     )) AS count_distinct_items_added_but_never_ordered,
    (SELECT COUNT(*) FROM (
       SELECT acl.item_id FROM add_to_cart_logs acl
       WHERE acl.user_id = ? AND acl.created_at BETWEEN ? AND ?
         AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add'
       INTERSECT
       SELECT acl2.item_id FROM add_to_cart_logs acl2
       WHERE acl2.user_id = ? AND acl2.created_at BETWEEN ? AND ?
         AND LOWER(LTRIM(RTRIM(ISNULL(acl2.action, '')))) = 'remove'
     ) t) AS count_distinct_items_added_then_removed_from_cart
) s
"""

# 3. Top "considered but not bought": items added to cart but never ordered (TOP 10)
QUERY_CART_TOP_CONSIDERED_NOT_BOUGHT = """
SELECT TOP 10
  acl.item_id,
  ISNULL(mid.item_name, 'Unknown') AS item_name,
  COUNT(*) AS times_added_to_cart_but_never_ordered
FROM add_to_cart_logs acl
LEFT JOIN menu_items_details mid ON mid.menu_item_id = acl.item_id AND mid.language_id = 1
WHERE acl.user_id = ?
  AND acl.created_at BETWEEN ? AND ?
  AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add'
  AND NOT EXISTS (
    SELECT 1 FROM order_item_details oi
    INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id
    WHERE rom.user_id = ? AND rom.is_deleted = 0
      AND rom.created_at BETWEEN ? AND ?
      AND oi.menu_item_id = acl.item_id
  )
GROUP BY acl.item_id, mid.item_name
ORDER BY times_added_to_cart_but_never_ordered DESC
"""

# 4. Considered but not bought by category
QUERY_CART_CONSIDERED_NOT_BOUGHT_BY_CATEGORY = """
SELECT
  ISNULL(cd.title, 'Unknown') AS category_name,
  COUNT(DISTINCT acl.item_id) AS distinct_items_added_but_never_ordered_in_this_category
FROM add_to_cart_logs acl
INNER JOIN menu_items mi ON mi.id = acl.item_id
LEFT JOIN category_details cd ON cd.category_id = mi.category_id AND cd.language_id = 1
WHERE acl.user_id = ?
  AND acl.created_at BETWEEN ? AND ?
  AND LOWER(LTRIM(RTRIM(ISNULL(acl.action, '')))) = 'add'
  AND NOT EXISTS (
    SELECT 1 FROM order_item_details oi
    INNER JOIN restaurant_order_master rom ON rom.id = oi.order_id
    WHERE rom.user_id = ? AND rom.is_deleted = 0
      AND rom.created_at BETWEEN ? AND ?
      AND oi.menu_item_id = acl.item_id
  )
GROUP BY cd.title, mi.category_id
ORDER BY distinct_items_added_but_never_ordered_in_this_category DESC
"""