product_mapper.py

"""
Product Name Mapping and Disambiguation Utilities.

This module provides utilities for mapping natural language product names
to database product codes, with smart handling of ambiguous references.

Features:
    - Product name to code mapping
    - Fuzzy matching for partial names
    - Ambiguity detection when multiple products match
    - User-friendly disambiguation messages

Functions:
    map_product_to_code(name): Map product name to database code
    get_product_mapping_dict(): Get full mapping dictionary
    check_query_ambiguity(query): Detect ambiguous product references
    analyze_query_for_products(query): Extract product references from query

Ambiguity Handling:
    When a query like "wireless mouse sales" matches multiple products
    (e.g., "Wireless Mouse" and "Wireless Mouse Pro"), the system
    detects this and provides a helpful disambiguation message.

Example:
    >>> from product_mapper import check_query_ambiguity, map_product_to_code
    >>>
    >>> # Check for ambiguity
    >>> is_ambiguous, matches, msg = check_query_ambiguity("wireless mouse")
    >>> if is_ambiguous:
    ...     print(msg)  # "Found multiple products: Wireless Mouse, Wireless Mouse Pro"
    >>>
    >>> # Direct mapping
    >>> code = map_product_to_code("laptop pro")
    >>> print(code)  # "PROD_LAPTOP_001"

Author: Markus van Kempen (markus.van.kempen@gmail.com)
"""

import os
import sqlite3

# Product name to code mapping (with similar names for ambiguity testing)
PRODUCT_CODE_MAPPING: dict[str, str] = {
    # Laptops
    "laptop": "PROD_LAPTOP_001",  # Default to Laptop Pro
    "laptop pro": "PROD_LAPTOP_001",
    "laptop professional": "PROD_LAPTOP_002",
    "laptop basic": "PROD_LAPTOP_003",
    # Smartphones
    "smartphone": "PROD_PHONE_002",  # Default to Smartphone X
    "smartphone x": "PROD_PHONE_002",
    "smartphone pro": "PROD_PHONE_003",
    "smartphone basic": "PROD_PHONE_004",
    "phone": "PROD_PHONE_002",  # Default to Smartphone X
    "phone x": "PROD_PHONE_002",
    "phone pro": "PROD_PHONE_003",
    # Tablets
    "tablet": "PROD_TABLET_003",  # Default to Tablet Air
    "tablet air": "PROD_TABLET_003",
    "tablet pro": "PROD_TABLET_004",
    # Books
    "book": "PROD_BOOK_004",  # Default to Programming Guide
    "programming guide": "PROD_BOOK_004",
    "programming guide advanced": "PROD_BOOK_005",
    # Mice
    "mouse": "PROD_MOUSE_005",  # Default to Wireless Mouse
    "wireless mouse": "PROD_MOUSE_005",
    "wireless mouse pro": "PROD_MOUSE_006",
    # Keyboards
    "keyboard": "PROD_KEYBOARD_006",  # Default to Mechanical Keyboard
    "mechanical keyboard": "PROD_KEYBOARD_006",
    "mechanical keyboard rgb": "PROD_KEYBOARD_007",
}


def map_product_to_code(product_name: str) -> str:
    """
    Map product name to database code.

    Args:
        product_name: Product name from user query

    Returns:
        Product code

    Raises:
        ValueError: If product name cannot be mapped
    """
    product_lower = product_name.lower().strip()

    # Exact match
    if product_lower in PRODUCT_CODE_MAPPING:
        return PRODUCT_CODE_MAPPING[product_lower]

    # Case-insensitive search
    for key, code in PRODUCT_CODE_MAPPING.items():
        if key.lower() == product_lower:
            return PRODUCT_CODE_MAPPING[key]

    # Partial match (contains)
    for key, code in PRODUCT_CODE_MAPPING.items():
        if product_lower in key or key in product_lower:
            return code

    raise ValueError(
        f"Product '{product_name}' not found in mapping. Available products: {', '.join(PRODUCT_CODE_MAPPING.keys())}"
    )


def find_ambiguous_products(query: str) -> list[tuple[str, str]]:
    """
    Find products that match the query but may be ambiguous.

    Args:
        query: User query string

    Returns:
        List of tuples (product_name, product_code) that match the query
    """
    query_lower = query.lower()
    matches = []

    # Find all products that match (exact or partial)
    for product_name, product_code in PRODUCT_CODE_MAPPING.items():
        if product_name in query_lower or query_lower in product_name:
            matches.append((product_name, product_code))

    # Remove duplicates (same product_code)
    seen_codes = set()
    unique_matches = []
    for name, code in matches:
        if code not in seen_codes:
            seen_codes.add(code)
            unique_matches.append((name, code))

    return unique_matches


def check_query_ambiguity(query: str) -> tuple[bool, list[tuple[str, str]]]:
    """
    Check if a query contains ambiguous product references.

    Args:
        query: User query string

    Returns:
        Tuple of (is_ambiguous, list_of_matches)
    """
    matches = find_ambiguous_products(query)

    # If we have multiple different product codes, it's ambiguous
    unique_codes = set(code for _, code in matches)
    is_ambiguous = len(unique_codes) > 1

    return is_ambiguous, matches


def get_product_mapping_dict() -> dict[str, str]:
    """Get the full product mapping dictionary."""
    return PRODUCT_CODE_MAPPING.copy()


def extract_products_from_query(query: str) -> list:
    """
    Extract potential product names from query.

    Args:
        query: User query string

    Returns:
        List of potential product names found
    """
    query_lower = query.lower()
    found_products = []

    for product_name in PRODUCT_CODE_MAPPING:
        if product_name in query_lower:
            found_products.append(product_name)

    return found_products


def get_matching_products_from_db(search_term: str) -> list[dict]:
    """
    Get actual products from database that match a search term.

    Args:
        search_term: The product name or keyword to search for

    Returns:
        List of dicts with product_name and product counts
    """
    db_path = os.getenv("DATABASE_PATH", "./data/database.db")

    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        # Find all products matching the search term (case-insensitive)
        cursor.execute(
            """
            SELECT DISTINCT product_name, product_code, category
            FROM products
            WHERE LOWER(product_name) LIKE LOWER(?)
            ORDER BY product_name
        """,
            (f"%{search_term}%",),
        )

        results = []
        for row in cursor.fetchall():
            results.append({"product_name": row[0], "product_code": row[1], "category": row[2]})

        conn.close()
        return results
    except Exception as e:
        print(f"Error in get_matching_products_from_db: {e}")
        return []


def detect_product_ambiguity(search_term: str) -> tuple[bool, list[dict], str]:
    """
    Detect if a search term matches multiple products in the database.

    Args:
        search_term: The product term from user query (e.g., "wireless mouse")

    Returns:
        Tuple of (is_ambiguous, matching_products, suggestion)
        - is_ambiguous: True if multiple products match
        - matching_products: List of matching product dicts
        - suggestion: Suggestion for handling (exact_match, group_by, clarify)
    """
    matching = get_matching_products_from_db(search_term)

    if len(matching) == 0:
        return False, [], "no_match"
    elif len(matching) == 1:
        return False, matching, "exact_match"
    else:
        # Multiple matches - check if one is exact match
        exact_match = None
        for product in matching:
            if product["product_name"].lower() == search_term.lower():
                exact_match = product
                break

        if exact_match:
            # User might want exact match, but also show similar products
            return True, matching, "exact_preferred"
        else:
            # No exact match, need clarification
            return True, matching, "clarify"


def build_disambiguation_response(search_term: str, matching_products: list[dict]) -> str:
    """
    Build a user-friendly message about ambiguous product matches.

    Args:
        search_term: The search term used
        matching_products: List of matching products

    Returns:
        Formatted message for the user
    """
    if len(matching_products) <= 1:
        return ""

    msg = f"🔍 **Multiple products match '{search_term}':**\n\n"
    for i, product in enumerate(matching_products, 1):
        msg += f"  {i}. **{product['product_name']}** ({product['category']})\n"

    msg += "\n💡 *Results include all matching products. For a specific product, use the exact name.*"
    return msg


def get_exact_product_filter(search_term: str) -> str | None:
    """
    Get SQL filter for exact product match if available.

    Args:
        search_term: The search term

    Returns:
        SQL WHERE clause for exact match, or None
    """
    matching = get_matching_products_from_db(search_term)

    for product in matching:
        if product["product_name"].lower() == search_term.lower():
            # Exact match found
            return f"product_name = '{product['product_name']}'"

    return None


def suggest_grouped_query(base_sql: str, search_term: str) -> str:
    """
    Suggest a query that groups by product_name for ambiguous searches.

    Args:
        base_sql: The original SQL query
        search_term: The search term that matched multiple products

    Returns:
        Modified SQL that groups by product_name
    """
    # If query doesn't already have GROUP BY product_name, suggest adding it
    if "GROUP BY" not in base_sql.upper():
        if "FROM sales" in base_sql.lower():
            # Add GROUP BY product_name
            if "WHERE" in base_sql.upper():
                # Insert GROUP BY before ORDER BY or at end
                if "ORDER BY" in base_sql.upper():
                    return base_sql.replace("ORDER BY", "GROUP BY product_name ORDER BY")
                else:
                    return base_sql.rstrip(";") + " GROUP BY product_name;"

    return base_sql


def analyze_query_for_products(user_query: str) -> dict:
    """
    Analyze a user query to detect product references and potential ambiguity.

    Args:
        user_query: The natural language query

    Returns:
        Dict with analysis results:
        - has_product_reference: bool
        - search_terms: list of product terms found
        - is_ambiguous: bool
        - matching_products: list of all matching products
        - disambiguation_message: str (if ambiguous)
        - suggestion: str (exact_match, group_by, clarify)
    """
    import re

    query_lower = user_query.lower()

    # Common product keywords to look for (must match as whole words)
    product_keywords = [
        "laptop",
        "laptops",
        "phone",
        "phones",
        "smartphone",
        "smartphones",
        "tablet",
        "tablets",
        "mouse",
        "keyboard",
        "keyboards",
        "book",
        "books",
        "guide",
        "wireless",
        "mechanical",
        "pro",  # Only match as standalone word, not in "products"
        "basic",
        "professional",
        "air",
        "rgb",
    ]

    # Find product terms in query (using word boundaries to avoid false matches)
    found_terms = []
    for keyword in product_keywords:
        # Use word boundary regex to match whole words only
        # This prevents "pro" from matching in "products"
        pattern = r"\b" + re.escape(keyword) + r"\b"
        if re.search(pattern, query_lower):
            # Normalize plural forms to singular for consistency
            normalized = (
                keyword.rstrip("s") if keyword.endswith("s") and keyword not in ["wireless", "keyboards"] else keyword
            )
            if normalized not in found_terms:
                found_terms.append(normalized)

    if not found_terms:
        return {
            "has_product_reference": False,
            "search_terms": [],
            "is_ambiguous": False,
            "matching_products": [],
            "disambiguation_message": "",
            "suggestion": "none",
        }

    # Check for multi-word product names
    compound_terms = []
    if "wireless mouse" in query_lower:
        compound_terms.append("wireless mouse")
    if "mechanical keyboard" in query_lower:
        compound_terms.append("mechanical keyboard")
    if "laptop pro" in query_lower:
        compound_terms.append("laptop pro")
    if "smartphone pro" in query_lower:
        compound_terms.append("smartphone pro")

    # Use compound terms if found, otherwise use single keywords
    search_terms = compound_terms if compound_terms else found_terms

    # Check each term for ambiguity
    all_matching = []
    is_ambiguous = False
    suggestion = "none"

    for term in search_terms:
        ambiguous, matching, term_suggestion = detect_product_ambiguity(term)
        all_matching.extend(matching)
        if ambiguous:
            is_ambiguous = True
            suggestion = term_suggestion

    # Remove duplicates
    seen = set()
    unique_matching = []
    for p in all_matching:
        if p["product_name"] not in seen:
            seen.add(p["product_name"])
            unique_matching.append(p)

    disambiguation_msg = ""
    if is_ambiguous and unique_matching:
        disambiguation_msg = build_disambiguation_response(
            search_terms[0] if search_terms else "product", unique_matching
        )

    return {
        "has_product_reference": True,
        "search_terms": search_terms,
        "is_ambiguous": is_ambiguous,
        "matching_products": unique_matching,
        "disambiguation_message": disambiguation_msg,
        "suggestion": suggestion,
    }