Build a custom AI client (harness) in Peliqan

# See also https://docs.streamlit.io/knowledge-base/tutorials/build-conversational-apps

import json
openai_api = pq.connect('OpenAI')

st.title("AI Chatbot")

if "messages" not in st.session_state:
    st.session_state.messages = []
else:
    messages = [
        {"role": m["role"], "content": m["content"]}
        for m in st.session_state.messages
    ]

# Render full history again on page refresh
for message in st.session_state.messages:
    with st.chat_message(message["role"]):
        st.markdown(message["content"])

if prompt := st.chat_input("How can I help ?"):
    messages.append({"role": "user", "content": prompt})
    
    with st.chat_message("user"): # Show user icon (red)
        st.markdown(prompt)

    with st.chat_message("assistant"): # Show bot icon (yellow)
        request = {
            "model": "gpt-5",
            "messages": messages,
            "reasoning_effort": "minimal"
        }    
        llm_answer = openai_api.get('completion_raw', request)

        if not "choices" in llm_answer:
            st.warning("Something went wrong. ")
            st.write(llm_answer)
        else:
            message = llm_answer["choices"][0]["message"]
            answer_content = message["content"]
            st.write(answer_content)
    
    st.session_state.messages = messages

# See also https://docs.streamlit.io/knowledge-base/tutorials/build-conversational-apps

import json
openai_conn = pq.connect('OpenAI')
dbconn = pq.dbconnect(pq.DW_NAME)

st.title("AI Chatbot for Finance")

if "messages" not in st.session_state:
    st.session_state.messages = []

# Show message history
for message in st.session_state.messages:
    with st.chat_message(message["role"]):
        if not isinstance(message["content"], list):
            st.markdown(message["content"])
        else:
            for query_to_run in message["content"]:
                description = query_to_run["description"]
                query = query_to_run["query"]
                st.text(description)

# Handle new chat input from user
if question := st.chat_input("How can I help ?"):
    
    with st.chat_message("user"):
        st.markdown(question)

    with st.chat_message("assistant"):
        
        prompt = """
            The user has a number of datasets, below is a list of all the tables with their columns.
            Based on the question of the user, write SQL SELECT queries to find answers in these tables.
            Use LIKE statements with % at the start and end, to find relevant rows.
            Use LOWER() in the LIKE statements on both sides to make it case insensitive.
            Your answer should be a JSON with an array of SQL SELECT queries. For each item in the array,
            provide an object with 2 keys: description and query.
            The description key explains what part of the question is answered.
            The query key contains the SQL SELECT query to execute.
            """
        
        all_tables = """
            Table chargebee.invoices, columns: invoice_id, invoice_status, invoice_date, invoice_total, invoice_customer_id.
            Table chargebee.customers, columns: customer_id, customer_company, customer_first_name, customer_last_name, customer_email.
            """

        prompt = prompt + "\n\n List of tables from the user: \n" + all_tables
        prompt = prompt + "\n\n The user question is: " + question
        prompt = prompt.replace('\n', '. ')

        messages = []
        for m in st.session_state.messages:
            if not isinstance(m["content"], list):
                messages.append({"role": m["role"], "content": m["content"]})
            else:
                content = ""
                for query_to_run in m["content"]:
                    content += query_to_run["description"] + ": " + query_to_run["query"] + ". "
                messages.append({"role": m["role"], "content": content})
        
        messages.append({"role": "user", "content": prompt})
        st.session_state.messages.append({"role": "user", "content": question})
        
        #response = openai_conn.get('completion', message = prompt, role = "user") # without history (context)
        response = openai_conn.get('completion_raw', { "model": "gpt-3.5-turbo", "messages": messages, "temperature": 0.7 })
        
        content = response["choices"][0]["message"]["content"]
        content = content.replace('```json', '').replace('```', '') # rarely added by OpenAI in response
        response_content = json.loads(content)
       
        if "queries" in response_content:
            queries_to_run = response_content["queries"]
        else:
            queries_to_run = response_content
        #st.json(queries_to_run)
        
        for query_to_run in queries_to_run:
            description = query_to_run["description"]
            query = query_to_run["query"]
            df = dbconn.fetch(pq.DW_NAME, query = query.replace("\n", " "), df = True)
            df = df.loc[:,~df.columns.duplicated()].copy() # remove duplicate column names
            st.text(description)
            #st.code(query, language = "SQL")
            st.dataframe(df)
    
        st.session_state.messages.append({"role": "assistant", "content": queries_to_run})