import time
import openpyxl
import my_new_openai


def update_excel_with_sums(filename):
    # Load the workbook and select the active worksheet
    workbook = openpyxl.load_workbook(filename)
    sheet = workbook.active

    # Iterate through each row in the sheet
    for row in sheet.iter_rows(min_row=1, min_col=2, max_col=3):
        Bn, Cn = row  # Assuming B and C are columns 2 and 3 respectively
        vector = my_new_openai.vectorize_data(f"{Bn.value}: {Cn.value}") if Bn.value and Cn.value else 0
        if vector != 0:
            for val in vector:
                sheet.cell(row=Bn.row, column=4+vector.index(val)).value = val

    # Save the workbook
    workbook.save(filename)
    print(f"Updated the file '{filename}' with vectors in column D.")


def load_vectorstore_from_excel(filename):
    # returns a dictonary
    # Load the workbook and select the active worksheet
    workbook = openpyxl.load_workbook(filename)
    sheet = workbook.active

    # Iterate through each row in the sheet
    vec_store = {}
    for row in range(3, 634):
        vec = []
        for col in range(0, 1536):
            val = sheet.cell(row=row, column=4+col).value
            vec.append(val)
        vec_store[str(sheet.cell(row=row, column=1).value)] = vec
    return vec_store


if __name__ == '__main__':
    #update_excel_with_sums("KBOB_Klassifizierung.xlsx")
    t = time.time()

    vec_store = load_vectorstore_from_excel("data/KBOB_Klassifizierung.xlsx")

    print(time.time()-t)
    for e in vec_store.keys():
        print(f"{e}: {vec_store[e][0]}, {vec_store[e][1]}, .... {vec_store[e][-1]}")