# -*- coding: utf-8 -*-
import os
import oracledb
from mcp.server.fastmcp import FastMCP
from product_search import SearchSimilarProduct

sercher = SearchSimilarProduct()

mcp = FastMCP("InvoiceItemResolver")
# Oracle Wallet Configuration
WALLET_PATH = "/WALLET_PATH/Wallet_oradb23ai"
DB_ALIAS = "oradb23ai_high"                 # Alias defined on tnsnames.ora
USERNAME = "USER"
PASSWORD = "password"
os.environ["TNS_ADMIN"] = WALLET_PATH


def execute_query(query: str, params: dict = {}):
    try:
        connection = oracledb.connect(
            user=USERNAME,
            password=PASSWORD,
            dsn=DB_ALIAS,
            config_dir=WALLET_PATH,
            wallet_location=WALLET_PATH,
            wallet_password=PASSWORD
        )
        cursor = connection.cursor()
        cursor.execute(query, params)
        results = cursor.fetchall()
        cursor.close()
        connection.close()
        return results
    except Exception as e:
        print(f"[ERROR]: {e}")
        return []

def execute_ean_search(search_terms):
    results = []

    try:
        connection = oracledb.connect(user=USERNAME, password=PASSWORD, dsn=DB_ALIAS, config_dir=WALLET_PATH, wallet_location=WALLET_PATH, wallet_password=PASSWORD)
        cursor = connection.cursor()

        query = """
                SELECT * FROM TABLE(fn_advanced_search(:1))
                ORDER BY similarity DESC \
                """
        cursor.execute(query, [search_terms])

        for row in cursor:
            results.append({
                "code": row[0],
                "description": row[1],
                "similarity": row[2]
            })

        cursor.close()
        connection.close()
    except Exception as e:
        return {"error": str(e)}, 500

    return results
# --------------------- MCP TOOLS ---------------------
@mcp.tool()
def search_vectorized_product(description: str) -> dict:
    """
        Searches for a product by description using embeddings.
    """
    return sercher.search_similar_products(description)

@mcp.tool()
def resolve_ean(description: str) -> dict:
    """
        Resolves the product's EAN code based on its description.
    """

    result = execute_ean_search(description)

    if isinstance(result, list) and result:
        return {
            "code": result[0]["code"],
            "description": result[0]["description"],
            "similarity": result[0]["similarity"]
        }
    else:
        return {"error": "Search not found by EAN."}

@mcp.tool()
def search_invoices_by_criteria(customer: str = None, state: str = None, price: float = None, ean: str = None, margin: float = 0.05) -> list:
    """
        Searches for outbound invoices based on customer, state, EAN, and approximate price.
        Allows one or more fields to be omitted.
        As long as an EAN has not been established, it is not useful to use this service.
    """

    query = """
            SELECT nf.no_invoice, nf.name_customer, nf.state, nf.date_print,
                   inf.no_item, inf.code_ean, inf.description_product, inf.value_unitary
            FROM invoice nf
                     JOIN item_invoice inf ON nf.no_invoice = inf.no_invoice
            WHERE 1=1 
            """

    params = {}

    #if customer:
    query += " AND LOWER(nf.name_customer) LIKE LOWER(:customer)"
    params["customer"] = f"%{customer}%"
    #if state:
    query += " AND LOWER(nf.state) = LOWER(:state)"
    params["state"] = state
    #if ean:
    query += " AND inf.code_ean = :ean"
    params["ean"] = ean
    if price is not None:
        query += " AND inf.value_unitary BETWEEN :price_min AND :price_max"
        params["price_min"] = price * (1 - margin)
        params["price_max"] = price * (1 + margin)

    result = execute_query(query, params)

    return [
        dict(zip(
            ["no_invoice", "name_customer", "state", "date_print", "no_item", "code_ean", "description_product", "value_unitary"],
            row
        ))
        for row in result
    ]


# --------------------- EXECUÇÃO MCP ---------------------

if __name__ == "__main__":
    # Start the MCP server
    mcp.run(transport="stdio")