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

from openpyxl import load_workbook
from openpyxl.styles import Alignment

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


load_dotenv()


def read_excel_and_process(fi):
    excel_file = pd.ExcelFile(fi)

    new_data = {}
    row_names = ['8:30-9:50', '10-11:20', '11:30-12:50', '1-2:20', '2:30-3:50', '4-5:20', '6-7:20', '7:30-8:50']
    col_names = ['Time', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday/Sunday']
    column_widths = [15, 40, 40, 40, 40, 40, 40]

    for t in row_names:
        new_data[t] = {}
        for d in col_names:
            new_data[t][d] = []

    for sheet_name in excel_file.sheet_names:
        # print(f"Processing sheet: {sheet_name}")

        df = excel_file.parse(sheet_name)

        # row_index = df.index
        # col_names = df.columns
        for i in range(0, len(row_names)):
            for j in range(1, len(col_names)):
                cell_value = df.iat[i, j]
                if pd.isna(cell_value):
                    continue
                if str(cell_value).find('Common') > -1:
                    continue

                row_name = row_names[i]
                col_name = col_names[j]

                new_data[row_name][col_name].append("{}, {}".format(str(cell_value).strip(), str(sheet_name).strip()))
                print(f"Sheet: {sheet_name}, Row: {row_name}, Column: {col_name}, Value: {cell_value}")

    for t, v in new_data.items():
        for d, vv in v.items():
            new_data[t][d].sort()
            new_data[t][d] = "\n".join(new_data[t][d])

    data_2 = []
    for t, v in new_data.items():
        v['Time'] = t
        data_2.append(v)

    df = pd.DataFrame.from_dict(data_2)
    # df.to_excel("visualization.xlsx", index=False)

    with pd.ExcelWriter('visualization.xlsx', engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='Sheet1', index=False)

        workbook = writer.book
        worksheet = writer.sheets['Sheet1']

        for i, width in enumerate(column_widths):
            column_letter = chr(65 + i)  # change index col to（A, B, C,...）
            worksheet.column_dimensions[column_letter].width = width

            for cell in worksheet[column_letter]:
                cell.alignment = Alignment(wrap_text=True)
    
    return "visualization.xlsx"



# 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"


with gr.Blocks() as demo:
    
    # with gr.Tab("Course Schedule Visualization"):

    gr.Markdown("## Visualize your course schedule.")

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

    result_fi = gr.File(file_count='single')
    
    # upload to process
    ex_fi.upload(read_excel_and_process, inputs=ex_fi, outputs=result_fi)

    # click to process
    # btn_submit = gr.Button("Submit")
    # btn_submit.click(text_to_neo4j, inputs=[remarks, columns], outputs=[result_fi,result_ta])/
        
    
demo.launch()