import pathlib
import uuid
import snowflake.connector as sc
import pandas as pd
import os
from dotenv import load_dotenv

from snowflake.connector.pandas_tools import write_pandas

# Load environment variables from .env file
load_dotenv()

private_key_file = pathlib.Path.home() / ".ssh/snowflake/rsa_key.p8"

conn_params = {
    'account': os.getenv('SNOWFLAKE_ACCOUNT'),
    'user': os.getenv('SNOWFLAKE_USER'),
    'role': os.getenv('SNOWFLAKE_ROLE'),
    'private_key_file': private_key_file,
    'private_key_file_pwd': os.getenv('SNOWFLAKE_KEY_PASSPHRASE'),
    'warehouse': os.getenv('SNOWFLAKE_WAREHOUSE'),
    'database': os.getenv('SNOWFLAKE_DATABASE'),
    'schema': os.getenv('SNOWFLAKE_SCHEMA')
}

ctx = sc.connect(**conn_params)
cur = ctx.cursor()
data = {
    'ID': ['1'],
    'NAME': ['test1'],
    'GLOBAL_PARTICIPANT_ID': [str(uuid.uuid4())],
    'VENDOR_ID': [123212],
    'SUBACCOUNT_ID': [123],
    'CREATED_AT': ['2022-02-02']
}
dataf = pd.DataFrame(data)
#Write local pandas dataframe to snowflake
write_pandas(ctx, dataf , 'SONGWHIP_PRESAVE_PAGE_DBT')

#Reading sql

with open('app/sql/read_sql.sql') as f:
    query = f.read()

cur.execute(query)
df = cur.fetch_pandas_all()
print(df)