app.py

import gradio as gr
import requests
import pandas as pd
from rapidfuzz import process, fuzz
from docx import Document
from datetime import datetime
from docx.oxml import OxmlElement
from docx.shared import Cm

bens_df = pd.read_excel('bens_tab.xlsx')

data_crawler = pd.read_csv('data_crawler.csv', index_col=False)
data_crawler = data_crawler[['Title', 'Price', 'Currency', 'Condition', 'Link', 'Marketplace']]

def get_report(query, df_nearest):
    doc = Document()

    # Adding a title
    doc.add_heading('RELATÓRIO DE AVALIAÇÃO DE VALOR DE MERCADO – EXP.', level=1)

    # Adding some details
    doc.add_paragraph('Solicitante:')
    doc.add_paragraph('Interessado:')
    doc.add_paragraph('Proprietário:')
    doc.add_paragraph('Objetivo da Avaliação: Definir o valor de mercado dos bens abaixo discriminados para registro no Patrimônio do Município.')

    # Adding section heading and content
    doc.add_heading('1. Características do(s) Avaliando(s):', level=2)
    doc.add_paragraph('1.1 Avaliando 01:')
    doc.add_paragraph(f'Descrição: {query}')

    # Add evaluation section
    doc.add_heading('2. Avaliação:', level=2)
    doc.add_paragraph('2.1 Avaliando 01')

    # Add market research section
    doc.add_heading('3. Pesquisa de Mercado:', level=2)

    # Filter and rename columns in df_nearest
    df_nearest_filtered = df_nearest[['ID', 'Title', 'Price', 'Link', 'Marketplace']].copy()
    df_nearest_filtered.columns = ['Item', 'Descrição', 'Preço', 'Link', 'Fornecedor']


    # Here you'll insert the df_nearest_filtered as a table
    if not df_nearest_filtered.empty:
        table = doc.add_table(rows=1, cols=len(df_nearest_filtered.columns))
        table.style = 'Table Grid'
        table.autofit = False 
        table.allow_autofit = False
        table.columns[0].width = Cm(3)
        table.rows[0].cells[0].width = Cm(3)
        hdr_cells = table.rows[0].cells
        for i, column_name in enumerate(df_nearest_filtered.columns):
            hdr_cells[i].text = column_name

        for index, row in df_nearest_filtered.iterrows():
            row_cells = table.add_row().cells
            for i, value in enumerate(row):
                if i == 3:
                    row_cells[i].text = str(value)
                else:
                    row_cells[i].text = str(value)


    today_date = datetime.now().strftime('%d/%m/%Y')
    doc.add_paragraph(f'Lajeado/RS, {today_date}')

    doc.add_paragraph('Membros da Comissão:')

    # Save the document
    doc.save('laudo.docx')
    

def fetch_data_to_dataframe(query, limit=50, source="mercadolibre"):
    if source == "mercadolibre":
        BASE_URL = "https://api.mercadolibre.com/sites/MLB/search"
        params = {'q': query, 'limit': limit}
        response = requests.get(BASE_URL, params=params)
        if response.status_code == 200:
            data = response.json()
            items = data.get('results', [])
            df = pd.DataFrame(items)[['title', 'price', 'currency_id', 'condition', 'permalink']]
            df.columns = ['Title', 'Price', 'Currency', 'Condition', 'Link']
            df['Marketplace'] = "Mercado Livre"
            return df
    return pd.DataFrame()

def refinar_resultados(df, include_word=[]):
    df['Title'] = df['Title'].astype(str)

    # Define a list of keywords to exclude, indicating multiples
    exclude_keywords = ["kit", "conjunto", "pacote", "caixa", "unidades", "Kits", " e "]

    # Add conditional exclusion for words not included in the query
    exclude_patterns = [keyword for keyword in exclude_keywords if keyword not in include_word]

    # Combine all exclude patterns into a single regex pattern
    exclude_pattern = r'\b(' + '|'.join(exclude_patterns) + r')\b|\b(\d+)\s*(unidade|pacotes|caixas)\b'

    # Perform the filtering in one operation
    df_refinado = df[~df['Title'].str.contains(exclude_pattern, case=False, regex=True, na=False)]

    return df_refinado


def get_best_match(query, choices, limit=50):
    # Using RapidFuzz for improved performance and fuzzy matching
    matches = process.extract(query, choices, scorer=fuzz.WRatio, limit=limit)
    return [match[0] for match in matches if match[1] > 75]
    
def match_query_words_in_titles(query, title):
    """
    Check if all words in the query have a close match within the title.
    Returns True if all words match to a certain degree; False otherwise.
    """
    query_words = query.lower().split()
    match_threshold = 75  # Adjust this threshold as needed

    for word in query_words:
        # Find the best match for each word in the query within the title
        match_score = fuzz.partial_ratio(word, title.lower())
        if match_score < match_threshold:
            return False  # If any word doesn't match well enough, return False

    return True  # All words matched well enough

def filtrar_itens_similares(df, termo_pesquisa, limit=50):
    # Apply the match function to each title, filtering for those that match the query words
    matches = df['Title'].apply(lambda title: match_query_words_in_titles(termo_pesquisa, title))
    df_filtrado = df[matches]

    # Further refine the list to the top N matches based on overall similarity to the query
    df_filtrado['Overall_Similarity'] = df_filtrado['Title'].apply(lambda title: fuzz.WRatio(termo_pesquisa, title))
    df_filtrado = df_filtrado.sort_values('Overall_Similarity', ascending=False).head(limit)

    return df_filtrado

def calcular_fator_avaliacao(titulo, EC, PU):
    filtered_df = bens_df[bens_df['TITULO'] == titulo]
    if filtered_df.empty:
        return None  # Or handle the error as needed

    bem_info = filtered_df.iloc[0]
    VU, VR = bem_info['VIDA_UTIL'], bem_info['VALOR_RESIDUAL']
    ec_pontuacao = {'Excelente': 10, 'Bom': 8, 'Regular': 5, 'Péssimo': 2}[EC]

    PU, PVU, PUB = float(PU), min(10 - ((PU - 1) * (10 / VU)), 10), min(10 - (((VU - PU) - 1) * (10 / VU)), 10)
    fator_avaliacao = max((4 * ec_pontuacao + 6 * PVU - 3 * PUB) / 100, VR)
    return fator_avaliacao

def select_nearest_items(df, query):
    # Lower the title similarity threshold if necessary
    df['Title_Similarity'] = df['Title'].apply(lambda x: fuzz.WRatio(query, x))
    df_filtered = df[df['Title_Similarity'] > 75]  # Adjusted threshold

    # Calculate mode price in a more inclusive manner
    mode_price = df_filtered['Price'].mode()
    if mode_price.empty:
        target_price = df_filtered['Price'].median()
    else:
        target_price = mode_price.min()

    df_filtered['Distance'] = (df_filtered['Price'] - target_price).abs()
    df_sorted = df_filtered.sort_values(['Distance', 'Title_Similarity'], ascending=[True, False])

    # Ensure diversity in marketplaces
    marketplaces_selected = set()
    results = []

    for _, row in df_sorted.iterrows():
        if row['Marketplace'] not in marketplaces_selected and len(marketplaces_selected) < 5:
            results.append(row)
            marketplaces_selected.add(row['Marketplace'])

        if len(results) >= 5:
            break

    return pd.DataFrame(results)


def search_with_fallback(query, df, limit=50):
    query_parts = query.split()
    include_conjunto = "conjunto" in query.lower()
    
    for i in range(len(query_parts), 0, -1):
        simplified_query = " ".join(query_parts[:i])
        include_word = ["conjunto"] if include_conjunto else []  # Ensure include_word is a list
        df_refinado = refinar_resultados(df, include_word=include_word)
        df_filtrado = filtrar_itens_similares(df_refinado, simplified_query, limit=limit)
        
        if not df_filtrado.empty:
            return df_filtrado

    return pd.DataFrame()


def integrated_app(query, titulo, EC, PU, selected_rows):
    include_conjunto = "conjunto" in query.lower()
    df_mercadolibre = fetch_data_to_dataframe(query, 50, "mercadolibre")
    df_combined = pd.concat([df_mercadolibre, data_crawler], ignore_index=True)

    if df_combined.empty:
        return "Nenhum dado encontrado. Tente uma consulta diferente.", pd.DataFrame()

    # Pass whether "conjunto" is part of the original query
    include_word = ["conjunto"] if include_conjunto else []
    df_refined = refinar_resultados(df_combined, include_word=include_word)

    df_similares = search_with_fallback(query, df_refined)

    if df_similares.empty:
        return "Nenhum item similar encontrado.", pd.DataFrame()

    df_nearest = select_nearest_items(df_similares, query)
    if df_nearest.empty:
        return "Nenhum resultado próximo encontrado.", pd.DataFrame()

    if selected_rows.strip():
        selected_indices = [int(idx) for idx in selected_rows.split(',') if idx.isdigit()]
        df_nearest = df_nearest.iloc[selected_indices]

    # After calculating df_nearest and before returning it
    df_nearest.reset_index(drop=True, inplace=True)
    df_nearest['ID'] = df_nearest.index

    # Reorder DataFrame columns to make ID first
    column_order = ['ID'] + [col for col in df_nearest.columns if col != 'ID']
    df_nearest = df_nearest[column_order]


    fator_avaliacao = calcular_fator_avaliacao(titulo, EC, PU)
    valor_avaliacao = df_nearest['Price'].mean() * fator_avaliacao
    get_report(query, df_nearest)
    return f"Valor Médio do Bem: R$ {df_nearest['Price'].mean():.2f}, Fator de Avaliação: {fator_avaliacao*100:.2f}%, Valor de Avaliação: R$ {valor_avaliacao:.2f}", df_nearest, 'laudo.docx'


iface = gr.Interface(fn=integrated_app,
                     inputs=[gr.Textbox(label="Digite sua consulta"),
                             gr.Dropdown(label="Classificação Contábil do Bem", choices=bens_df['TITULO'].unique().tolist(), value="MOBILIÁRIO EM GERAL"),
                             gr.Radio(label="Estado de Conservação do Bem", choices=['Excelente', 'Bom', 'Regular', 'Péssimo'], value="Excelente"),
                             gr.Number(label="Período utilizado (anos)", value=1),
                             gr.Textbox(label="IDs das linhas selecionadas (separadas por vírgula)", placeholder="Exemplo: 0,2,5")],  # Using Textbox for IDs
                     outputs=[gr.Textbox(label="Cálculo"), gr.Dataframe(label="Resultados da Pesquisa"), gr.File(label="Laudo de Avaliação")],
                     theme=gr.themes.Monochrome(),
                     title=f"""
                     <p style="text-align: left;">
                     <b><span style='color: grey; font-size: 50px;'>aval</span><span style='color: orange; font-size: 50px;'>ia</span><span style='color: grey; font-size: 50px;'>.se</span></b>
                     """,
                     description="""<p style="text-align: left;">Aplicativo para avaliação de Bens Móveis</p>""")


iface.launch()