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(google_query):
    prompt = "Farmonaut wants to classify this google search query into only one of the following categories: a. Precision Agriculture, b. API/ Development, c. Traceability, d. Plantation, e. Unrelated, f. pests, diseases and weeds, g. irrigation, h. yield forecast, i. area estimation and crop identification, j. geotagging, k. fertilizers and soil health, l. Satellite/GIS/Remote Sensing, m. agri-tech startup/ company, n. agriculture content. Answer only one category (without category alphabet). Google search query to classify: " + google_query
    completion = client.chat.completions.create(
    model="gpt-4o-mini-2024-07-18",
    messages=[
        {"role": "system", "content": "You are a expert in SEO and a representative of Farmonaut."},
        {"role": "user", "content": prompt}
    ],
    max_tokens=20,
    temperature=0
    )
    return completion.choices[0].message.content


update_excel_with_sum('Downloads/sep_data.xlsx', 'Sheet1', 2)