#import snowflake connector module
import snowflake.connector
from snowflake.connector.converter_null import SnowflakeNoConverterToPython
from snowflake.connector import DictCursor
from datetime import datetime
import sys
import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization

# 確定輸入的參數是否給定正確
# Sample: python data_binding.py <account> <user> <role>
if len(sys.argv) < 4 :
    print("ERROR: Please pass the following command-line parameters in order:",end='\n')
    print("account,user,role.")
    sys.exit(-1)
else:
    ACCOUNT = sys.argv[1]
    USER = sys.argv[2]
    ROLE = sys.argv[3]

with open("/Users/abehsu/Documents/Snowflake/Snowpipe_poc/rsa_key.p8", "rb") as key:
    p_key = serialization.load_pem_private_key(
        key.read(),
        password = os.environ['PRIVATE_KEY_PASSPHRASE'].encode(),
        backend=default_backend()
    )

pkb = p_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption()
)


con = snowflake.connector.connect(
    account=ACCOUNT,
    user=USER,
    role=ROLE,
    private_key=pkb,
)

con.cursor().execute("USE WAREHOUSE SF_TUTS_WH")
con.cursor().execute("USE SCHEMA TESTDB_MG.TESTSCHEMA_MG")


# #Using %(name)s as the placeholder
# con.cursor().execute("""
#     INSERT INTO TEST_TABLE(COL1, COL2)
#     VALUES (%(COL1)s, %(COL2)s)""",{'COL1':789,"COL2":'test string3'})

# #Using %s as the placeholder
# con.cursor().execute("""
#     INSERT INTO TEST_TABLE(COL1, COL2)
#     VALUES(%s,%s)
# """,(789,'TEST STRING3'))

# # Use a list object to bind data for the IN operator
# con.cursor().execute("""
# SELECT COL1,COL2 FROM TEST_TABLE
#  WHERE COL2 IN (%s)
# """,(
#     """['test string1', 'test string3']"""
# ))

# Using ? as the placeholder(Bind data in the server side)
# con = snowflake.connector.connect(
#     account=ACCOUNT,
#     user=USER,
#     role=ROLE,
#     private_key=pkb,
#     paramstyle="qmark"
# )

# con.cursor().execute("USE WAREHOUSE SF_TUTS_WH")
# con.cursor().execute("USE SCHEMA TESTDB_MG.TESTSCHEMA_MG")

# con.cursor().execute("""
#     INSERT INTO test_table(col1, col2)
#     VALUES(?, ?)""", (000,'test string-qmark')
# )

# # Using :N as the placeholder (Binding data in the server side)
# con = snowflake.connector.connect(
#     account=ACCOUNT,
#     user=USER,
#     role=ROLE,
#     private_key=pkb,
#     paramstyle="numeric"
# )

# con.cursor().execute("USE WAREHOUSE SF_TUTS_WH")
# con.cursor().execute("USE SCHEMA TESTDB_MG.TESTSCHEMA_MG")

# con.cursor().execute("""
#     INSERT INTO test_table(col1, col2)
#     VALUES(:1, :2)""", (789,'test string-numeric')
# )

# Binding datetime with TIMESTAMP (Binding data in the server side)

con = snowflake.connector.connect(
    account=ACCOUNT,
    user=USER,
    role=ROLE,
    private_key=pkb,
    paramstyle="qmark"
)

con.cursor().execute("USE WAREHOUSE SF_TUTS_WH")
con.cursor().execute("USE SCHEMA TESTDB_MG.TESTSCHEMA_MG")

con.cursor().execute("""
    CREATE OR REPLACE TABLE TESTTABLE2(
        COL1 int,
        COL2 string,
        COL3 timestamp_ltz
    )
""")

# con.cursor().execute("""
#     INSERT INTO TESTTABLE2(COL1, COL2, COL3)
#     VALUES(?,?,?)
# """,(987,"test string4",("TIMESTAMP_LTZ",datetime.now())))

# con.cursor().execute("""
#     INSERT INTO TESTTABLE2(COL1, COL2, COL3)
#     VALUES(?,?,?)
# """,(999,"test string5",datetime.now()))


# con = snowflake.connector.connect(
#     account=ACCOUNT,
#     user=USER,
#     role=ROLE,
#     private_key=pkb,
# )

# con.cursor().execute("USE WAREHOUSE SF_TUTS_WH")
# con.cursor().execute("USE SCHEMA TESTDB_MG.TESTSCHEMA_MG")

# con.cursor().execute("""
#     INSERT INTO TESTTABLE2(COL1, COL2, COL3)
#     VALUES (%(COL1)s, %(COL2)s, %(COL3)s )""",{'COL1':1000,"COL2":'test string6',"COL3":datetime.now()})