from openai import OpenAI
import time
import traceback
client = OpenAI(
  api_key = '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'
)

import pandas as pd

import openpyxl

def update_excel_with_sum(filename, sheet_name, target_column):
    # Load the workbook and select the specified sheet
    workbook = openpyxl.load_workbook(filename)
    sheet = workbook[sheet_name]
    
    # Iterate through each row starting from the first data row
    for row in range(1, sheet.max_row + 1):
        # Get the value from the previous column
        previous_column_value = sheet.cell(row=row, column=target_column-1).value
        try:
            new_value = classify_emailid(previous_column_value)
        except:
            new_value = traceback.format_exc()
        print((previous_column_value, new_value))
        #time.sleep(0.1)
        # # Ensure that the previous column value is a number, if not, handle it
        # if isinstance(previous_column_value, (int, float)):
        #     # Calculate the sum of the row number and previous column value
        #     new_value = row + previous_column_value
        # else:
        #     # If previous column value is not a number, just store the row number
        #     new_value = row
        
        # Update the target column with the calculated value
        sheet.cell(row=row, column=target_column).value = new_value
    
    # Save the workbook
    workbook.save(filename)



def classify_emailid(email_id):
    prompt = "if this keyword is not in english return NOT ENGLISH. if it is in English and missing spaces between words, add spaces between the words and return the updated keyword. Keyword: " + email_id + ". Return only the updated keyword and no other text."
    completion = client.chat.completions.create(
    model="gpt-4o-mini-2024-07-18",
    messages=[
        {"role": "system", "content": "You are a expert in english language"},
        {"role": "user", "content": prompt}
    ],
    max_tokens=10,
    temperature=0
    )
    return completion.choices[0].message.content


update_excel_with_sum('filtered_scrape_data.xlsx', 'Sheet1', 2)