import os
import gradio as gr
import pandas as pd
from groq import Groq
from dotenv import load_dotenv

os.environ['REQUESTS_CA_BUNDLE'] = '/usr/local/share/zscaler.crt'


load_dotenv()

def excel_to_csv(fi):
    df = pd.read_excel(fi)

    clm = []
    for c in df.columns:
        clm.append(c)
    
    df = df.to_csv(path_or_buf = "here2.csv")

    return gr.Dropdown(choices=clm, value=clm, label="Columns of the file", multiselect=True, allow_custom_value=True), "here2.csv"

def text_to_neo4j(rm, cm):
    prompt = """
    
    Here is a random Neo4J database with additionnal informations in '#' :  
    
    (d:Document {URL: string (#contains the url of the document), type: string (#contains the type of the document), Description: string (#contains the description of the document)} -[:TALKS_ABOUT]-> (t: Topic {title: string (#contains the title of the topic), description: string (#contains the description of the topic)}) <-[:INTERESTED_BY]- (e: Expert {name: string (#contains the name of the expert)};
    
      
    (s:Solution {url: string (#contains the url of the document the solution refers to), description: string (#contains the description of the solution})-[:SOLUTION_OF]->(d)
    (p:Problem {url: string (#contains the url of the document the problem refers to), description: string (#contains the description of the problem})-[:PROBLEM_OF]->(d);
    
    ---
    Here is a exemple of a Cypher script for this data batase: 
    
    //KeyIssues
    LOAD CSV WITH HEADERS FROM 'file:///dataKeyIssues.csv' AS row
    FIELDTERMINATOR ';'
    WITH row WHERE row.Expert IS NOT NULL AND row.Problems IS NULL  
    WITH row, split(replace(row.Description,", This topic","This topic"), 'This topic') as Descriptions, split(row.Topic, ',') as Topics, split(row.Expert, ',') AS Experts, split(row.Score, ',') as Score 
    
    MERGE (dd:Document {uri: row.Document, description: row.`Key Issue`, type: "TR"})
    
    FOREACH (i IN RANGE(0, size(Descriptions) - 2) |
        MERGE (t:Topic {name: trim(Topics[i]), description: trim(Descriptions[i+1])})
        MERGE (dd)-[r:TALKS_ABOUT{}]->(t)
             ON CREATE set r.score = Score[i]
             ON MATCH SET
             r.score = CASE WHEN Score[i] > r.score 
             THEN Score[i]
             ELSE r.score END
        MERGE (e:Expert {name: trim(Experts[i])})
        MERGE (e)-[:INTERESTED_BY]->(t)
    );
    
    LOAD CSV WITH HEADERS FROM 'file:///dataKeyIssues.csv' AS row
    FIELDTERMINATOR ';'
    WITH row WHERE  row.Problems IS NOT NULL  
    WITH row, split(replace(row.Description,", This topic","This topic"), 'This topic') as Descriptions, split(row.Topic, ',') as Topics
    
    MERGE (dd:Document {uri: row.Document, keyIssues: row.`Key Issue`, type: "TR"})
    MERGE (p:Problem {description: row.Problems})
    MERGE (p)-[:PROBLEM_OF]->(dd)
    ---
    With the first line of a CSV file, provide me a Neo4J Cypher script which implement the csv file in the database. Here are some remarks about this file : %s .Only provide a script based on the data given in the CSV file: 
    
    First line = %s
    """ % (rm,cm)

    messages = [
        {
            "role": "system",
            "content": f"You are a helpful assistant. Only show your final response to the **User Query**! Do not provide any explanations or details."
        },
        {
            "role": "user",
            "content": prompt,
        }
    ]
    
    client = Groq(api_key= os.environ["GROQ_API_KEY1"])
    chat_completion = client.chat.completions.create(
        messages=messages,
        model="llama3-70b-8192",
    )
    
    response = chat_completion.choices[0].message.content
    print(response)
    
    text_file = open("Output.txt", "w")
    
    text_file.write(response)
    
    text_file.close()
    
    return "Output.txt",response



with gr.Blocks() as demo:
    
    with gr.Tab("Excel to Neo4J"):
        gr.Markdown("### Transfer your excel data in a Neo4J database using this tool !")

        ex_fi = gr.File(file_count='single')
        csv_fi = gr.File(file_count='single')

        columns = gr.Dropdown(label="Columns of the file", multiselect=True, allow_custom_value=True)

        remarks = gr.Dropdown(["This file do not contain information about Topic or Expert Nodes", "This file do not contain information about Document Nodes", "This file do not contain information about Solution Nodes", "This file do not contain information about Problem nodes"], label="Remarks", multiselect=True, allow_custom_value=True)

        btn_submit = gr.Button("Submit")

        result_ta = gr.TextArea("Here you will find your answer !")

        result_fi = gr.File(file_count='single')
        
        ex_fi.upload(excel_to_csv, inputs=ex_fi, outputs=[columns, csv_fi])

        btn_submit.click(text_to_neo4j, inputs=[remarks, columns], outputs=[result_fi,result_ta])
        
    
demo.launch()