import pandas as pd
import gradio as gr
import pymssql
import csv

from sentence_transformers import SentenceTransformer
from sklearn.metrics.pairwise import cosine_similarity #유사도 검색 기능을 사용하기 위함
import glob

df = pd.read_csv('./data/real/_UnivSample_20231113.csv')

encoder = SentenceTransformer('jhgan/ko-sroberta-multitask') # 선택 모델 (ko가 붙은건 한국어 지원)

df['embedding'] = pd.Series([[]] * len(df)) # dummy
df['embedding'] = df['query'].map(lambda x: list(encoder.encode(x))) 

# 챗봇의 답변을 처리하는 함수
def respond(message, chat_history):
    
    embedding = encoder.encode(message)

    df['distance'] = df['embedding'].map(lambda x: cosine_similarity([embedding], [x]).squeeze())
    answer = df.loc[df['distance'].idxmax()]

    chat_history.append([message, answer['answer']])
    
    # historySave(message=message, answer=str(answer['answer']).replace("'",""))
    # historySave(message=message, answer="")
    
    return "", chat_history

# def historySave(message, answer):

#     conn = pymssql.connect(host=r"(local)", database='Chatbot_Manage', charset='utf8')
#     conn.autocommit(True) # 오토 커밋 활성화
#     # Connection 으로부터 Cursor 생성
#     cursor = conn.cursor()

#     SystemType = "SentenceModel"
    
#     # SQL문 실행'
#     _sql = "EXEC ChatHistory_InsUpd '" + SystemType + "','" + message + "', '" + answer + "'"
#     cursor.execute(_sql)
    
#     conn.close()    ## 연결 끊기

# 챗봇 설명
title = """
<div style="text-align: center; max-width: 500px; margin: 0 auto;">
    <div>
        <h1>Woody's Chatbot V2</h1>
    </div>
    <p style="margin-bottom: 10px; font-size: 94%">
        sentence_transformers를 이용한 Chatbot
    </p>
</div>
"""

# 꾸미기
css="""
#col-container {max-width: 700px; margin-left: auto; margin-right: auto;}
"""
with gr.Blocks(css=css) as UnivChatbot:
    with gr.Column(elem_id="col-container"):
        gr.HTML(title)

        chatbot = gr.Chatbot(label="대학 챗봇시스템(LLM Sentence)", elem_id="chatbot") # 상단 좌측 
                
        with gr.Row():
            with gr.Column(scale=9):
                msg = gr.Textbox(label="입력", placeholder="궁금하신 내역을 입력하여 주세요.", elem_id="InputQuery", show_label=False, container=False)
            
        with gr.Row():
            with gr.Column(scale=1):
                submit = gr.Button("전송", variant="primary")
            with gr.Column(scale=1):
                clear = gr.Button("초기화", variant="stop")
                

    # 사용자의 입력을 제출(submit)하면 respond 함수가 호출.
    msg.submit(respond, [msg, chatbot], [msg, chatbot])

    submit.click(respond, [msg, chatbot], [msg, chatbot])

    # '초기화' 버튼을 클릭하면 채팅 기록을 초기화.
    clear.click(lambda: None, None, chatbot, queue=False)
    
UnivChatbot.launch()