"""
Nutricore Food Solution - Agent Tools (7 buyer-focused tools)
"""

import json
from agents import function_tool
from database import get_db, DELIVERY_INFO, RECIPES


# ─── Helpers ──────────────────────────────────────────────────────────────────

def _row_to_dict(row) -> dict:
    return dict(row) if row else {}


def _get_product_by_id(product_id: int) -> dict:
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT * FROM products WHERE id = %s", (product_id,))
    row = cur.fetchone()
    cur.close()
    conn.close()
    return _row_to_dict(row)


# ─── Tool Implementations ─────────────────────────────────────────────────────

def browse_products_impl(category: str = "") -> dict:
    conn = get_db()
    cur = conn.cursor()
    if category and category.lower() != "all":
        cur.execute(
            "SELECT id, name, category, weight, price, description, image FROM products WHERE LOWER(category) LIKE %s ORDER BY id",
            (f"%{category.lower()}%",)
        )
    else:
        cur.execute(
            "SELECT id, name, category, weight, price, description, image FROM products ORDER BY id"
        )
    rows = cur.fetchall()
    cur.close()
    conn.close()

    products = [dict(r) for r in rows]
    categories = list({p["category"] for p in products})
    return {
        "total": len(products),
        "categories": sorted(categories),
        "products": products
    }


def get_product_info_impl(product_id: int) -> dict:
    p = _get_product_by_id(product_id)
    if not p:
        return {"error": f"Product with id {product_id} not found"}
    return p


def find_product_for_goal_impl(health_goal: str) -> dict:
    GOAL_MAP = {
        "weight_loss": "weight_loss",
        "immunity": "immunity",
        "energy": "energy",
        "skin_health": "skin_health",
        "digestion": "digestion",
        "detox": "detox",
    }
    goal = GOAL_MAP.get(health_goal.lower().replace(" ", "_"), health_goal.lower())

    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "SELECT id, name, category, weight, price, description, benefits FROM products WHERE health_goals LIKE %s",
        (f"%{goal}%",)
    )
    rows = cur.fetchall()
    cur.close()
    conn.close()

    if not rows:
        return {"error": f"No products found for goal: {health_goal}"}

    products = [dict(r) for r in rows]

    GOAL_DESCRIPTIONS = {
        "weight_loss": "These products support weight loss through fiber, low calories, and metabolism boost.",
        "immunity": "These products are rich in Vitamin C, antioxidants, and immune-boosting compounds.",
        "energy": "These products provide natural energy through nutrients, iron, and carbohydrates.",
        "skin_health": "These products contain antioxidants, Vitamin C, and nutrients for glowing skin.",
        "digestion": "These products contain fiber, enzymes, and probiotics that support healthy digestion.",
        "detox": "These products help cleanse the body and support liver and kidney function.",
    }

    return {
        "health_goal": health_goal,
        "explanation": GOAL_DESCRIPTIONS.get(goal, f"Products recommended for {health_goal}"),
        "total": len(products),
        "recommended_products": products
    }


def get_recipe_ideas_impl(product_id: int = 0) -> dict:
    if product_id and product_id > 0:
        recipes = RECIPES.get(product_id, [])
        product = _get_product_by_id(product_id)
        if not product:
            return {"error": f"Product {product_id} not found"}
        if not recipes:
            return {
                "product": product.get("name"),
                "message": f"Mix 1-2 tbsp of {product.get('name')} in smoothies, juices, or warm water.",
                "recipes": []
            }
        return {
            "product": product.get("name"),
            "recipes": recipes
        }

    # General recipes
    all_recipes = []
    for pid, recipe_list in RECIPES.items():
        p = _get_product_by_id(pid)
        if p and recipe_list:
            all_recipes.append({
                "product_id": pid,
                "product_name": p.get("name"),
                "recipe": recipe_list[0]
            })

    return {
        "message": "Here are some popular recipes using our powders:",
        "recipes": all_recipes[:6]
    }


def compare_products_impl(product_id_1: int, product_id_2: int) -> dict:
    p1 = _get_product_by_id(product_id_1)
    p2 = _get_product_by_id(product_id_2)

    if not p1:
        return {"error": f"Product {product_id_1} not found"}
    if not p2:
        return {"error": f"Product {product_id_2} not found"}

    return {
        "comparison": {
            "product_1": {
                "id": p1["id"],
                "name": p1["name"],
                "category": p1["category"],
                "weight": p1["weight"],
                "price": p1["price"],
                "benefits": p1["benefits"],
                "health_goals": p1["health_goals"],
                "how_to_use": p1["how_to_use"],
            },
            "product_2": {
                "id": p2["id"],
                "name": p2["name"],
                "category": p2["category"],
                "weight": p2["weight"],
                "price": p2["price"],
                "benefits": p2["benefits"],
                "health_goals": p2["health_goals"],
                "how_to_use": p2["how_to_use"],
            }
        },
        "price_difference": abs(p1["price"] - p2["price"]),
        "cheaper": p1["name"] if p1["price"] < p2["price"] else p2["name"],
    }


def check_price_and_delivery_impl(product_id: int, city: str = "") -> dict:
    p = _get_product_by_id(product_id)
    if not p:
        return {"error": f"Product {product_id} not found"}

    city_key = city.lower().strip() if city else "other"
    delivery = DELIVERY_INFO.get(city_key, DELIVERY_INFO["other"])

    product_price = p["price"]
    delivery_charge = delivery["charge"]
    total = product_price + delivery_charge

    return {
        "product": {"id": p["id"], "name": p["name"], "weight": p["weight"], "price": product_price},
        "delivery": {
            "city": delivery["city"],
            "charge": delivery_charge,
            "estimated_time": delivery["days"]
        },
        "total_amount": total,
        "note": "Free delivery on orders above PKR 3000"
    }


def place_order_impl(name: str, phone: str, address: str, products: list) -> dict:
    if not name or not phone or not address:
        return {"error": "Please provide name, phone, and address to place order."}
    if not products:
        return {"error": "Please specify at least one product to order."}

    total = 0
    order_items = []
    conn = get_db()

    cur = conn.cursor()
    for item in products:
        product_id = item.get("product_id") or item.get("id")
        qty = item.get("quantity", 1)
        if product_id:
            cur.execute("SELECT id, name, price FROM products WHERE id = %s", (product_id,))
            row = cur.fetchone()
            if row:
                p = dict(row)
                subtotal = p["price"] * qty
                total += subtotal
                order_items.append({"product": p["name"], "qty": qty, "price": p["price"], "subtotal": subtotal})

    city = address.split(",")[-1].strip().lower() if "," in address else "other"
    delivery = DELIVERY_INFO.get(city, DELIVERY_INFO["other"])
    total += delivery["charge"]

    import json as _json
    cur.execute(
        "INSERT INTO orders (name, phone, address, city, products, total_amount) VALUES (%s, %s, %s, %s, %s, %s) RETURNING id",
        (name, phone, address, city, _json.dumps(order_items), total)
    )
    order_id = cur.fetchone()["id"]
    cur.close()
    conn.commit()
    conn.close()

    return {
        "success": True,
        "order_id": order_id,
        "customer": name,
        "items": order_items,
        "delivery_charge": delivery["charge"],
        "total_amount": total,
        "estimated_delivery": delivery["days"],
        "message": f"Order #{order_id} confirmed! Our team will contact you on {phone} via WhatsApp within 1 hour to confirm your order."
    }


# ─── Function Tools ───────────────────────────────────────────────────────────

@function_tool
def browse_products(category: str = "") -> str:
    """Browse all products or filter by category.

    Args:
        category: Optional category filter - 'Fruit Powders', 'Vegetable Powders', 'Green Leaf Powders', or empty for all
    """
    result = browse_products_impl(category)
    return str(result)


@function_tool
def get_product_info(product_id: int) -> str:
    """Get full details about a specific product.

    Args:
        product_id: The product ID (1-12)
    """
    result = get_product_info_impl(product_id)
    return str(result)


@function_tool
def find_product_for_goal(health_goal: str) -> str:
    """Find products recommended for a specific health goal.

    Args:
        health_goal: One of: weight_loss, immunity, energy, skin_health, digestion, detox
    """
    result = find_product_for_goal_impl(health_goal)
    return str(result)


@function_tool
def get_recipe_ideas(product_id: int = 0) -> str:
    """Get recipe ideas for a product or general recipe suggestions.

    Args:
        product_id: Optional product ID (1-12). Use 0 for general recipes.
    """
    result = get_recipe_ideas_impl(product_id)
    return str(result)


@function_tool
def compare_products(product_id_1: int, product_id_2: int) -> str:
    """Compare two products side by side.

    Args:
        product_id_1: First product ID
        product_id_2: Second product ID
    """
    result = compare_products_impl(product_id_1, product_id_2)
    return str(result)


@function_tool
def check_price_and_delivery(product_id: int, city: str = "") -> str:
    """Check product price and delivery charges for a city.

    Args:
        product_id: The product ID
        city: Delivery city (lahore, karachi, islamabad, rawalpindi, faisalabad, multan, peshawar, or other)
    """
    result = check_price_and_delivery_impl(product_id, city)
    return str(result)


@function_tool
def place_order(name: str, phone: str, address: str, products: list) -> str:
    """Place a new order for the customer.

    Args:
        name: Customer full name
        phone: WhatsApp number
        address: Full delivery address
        products: List of dicts with product_id and quantity e.g. [{"product_id": 1, "quantity": 2}]
    """
    result = place_order_impl(name, phone, address, products)
    return str(result)