from shillelagh.backends.apsw.db import connect
from google.oauth2 import service_account
import os
import json
# Set up environment variable for authentication
# If using a service account, set the environment variable for authentication

credentials_json = os.getenv('GOOGLE_API_JSON_CONTENT')
# Load the credentials from the JSON string
credentials_info = json.loads(credentials_json)
# credentials = service_account.Credentials.from_service_account_info(credentials_info)

# os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'location-455719-d08225f31b58.json'

# URL format: https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit
#  https://docs.google.com/spreadsheets/d/1CMdSSTxjw9ooYDzmAufL0j3EtUxAQ6vdwbAL5e3t7aY/edit?usp=sharing
spreadsheet_id = '1CMdSSTxjw9ooYDzmAufL0j3EtUxAQ6vdwbAL5e3t7aY'
sheet_name = 'Config'
sheet_table = {
    "Location": 0,
    "Config": 1310222727
}
url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit?gid={sheet_table[sheet_name]}"


# Connect to the Google Sheet
connection = connect(
    ":memory:", 
    adapter_kwargs={
        "gsheetsapi": {
            # "service_account_file": 'location-455719-d08225f31b58.json'
            "service_account_info": credentials_info,
        }
    }
)
cursor = connection.cursor()


# Example query to read data
rows = cursor.execute(f"SELECT * FROM '{url}' LIMIT 1").fetchall()
# rowid = rows[0][0]
config = rows[0][1]
config = json.loads(config)
config["last_run"] = config.get("last_run", 0) + 1
updated_config = json.dumps(config)
print(rows)
# cursor.execute(f"DELETE FROM '{url}' ")
try:
    cursor.execute(
        f"UPDATE '{url}' SET config = ? WHERE id = ?",
        (updated_config, 1)
    )

except Exception as e:
    print(f"Error inserting data: {e}")