#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 retrieving_column_meta.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")


# Retrieving Column Metadata
# method 1
cur = con.cursor()

cur.execute("""
    SELECT * FROM TEST_TABLE;
""")

"""
# Columns information in a tuple:
#         - name
#         - type_code
#         - display_size
#         - internal_size
#         - precision
#         - scale
#         - null_ok
#         """

print(','.join([col[0] for col in cur.description]))

# method2
print(','.join(
    [col[0] for col in con.cursor().execute("SELECT * FROM TEST_TABLE;").description]
    )
)