import os
import json
from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

# Path to your credentials JSON file
GOOGLE_SHEETS_CREDENTIALS_FILE = os.path.join(os.path.dirname(__file__), 'danone-autoupdate-spreadsheet-18b00fb29271.json')

# ID of the Google Spreadsheet (from the URL)
SPREADSHEET_ID = '1MNwvSn5woMZ9XId6jAN88-KGl41TRRzf-oPhUxEeXDc'

# Name of the sheet within the spreadsheet
SHEET_NAME = 'Sheet1'

def append_to_google_sheet(candidate_full_name, danone_id, candidate_type, completion_date):
    # Load credentials from the JSON keyfile
    credentials = Credentials.from_service_account_file(GOOGLE_SHEETS_CREDENTIALS_FILE, scopes=['https://www.googleapis.com/auth/spreadsheets'])

    # Build the Sheets API client
    service = build('sheets', 'v4', credentials=credentials)

    # Read the existing data from the sheet to check if the danone_id already exists
    sheet = service.spreadsheets().values().get(spreadsheetId=SPREADSHEET_ID, range=f'{SHEET_NAME}!A:D').execute()
    existing_rows = sheet.get('values', [])

    # Check if the danone_id already exists in the sheet (assuming it's in column B)
    for row in existing_rows:
        if row[1] == danone_id:  # Assuming 'danone_id' is in column B (index 1)
            print(f"User {danone_id} already exists in Google Sheet.")
            return  # Exit the function if the user already exists

    # Prepare the data to be added (e.g., Candidate's name, Danone ID, candidate type, and completion date)
    values = [
        [candidate_full_name, danone_id, candidate_type, completion_date]
    ]

    body = {
        'values': values
    }

    # Append the data to the spreadsheet
    result = service.spreadsheets().values().append(
        spreadsheetId=SPREADSHEET_ID,
        range=f'{SHEET_NAME}!B1',  # Start appending at the first column
        valueInputOption='USER_ENTERED',
        body=body
    ).execute()

    return result