import os
import config
import snowflake.connector
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
import pandas as pd


def connect_to_snowflake(config):

    """
    Basic function to connect to snowflake. Requires a config contaning
    connection credentials
    """

    with open(config.private_key_path, "rb") as key:
        p_key = serialization.load_pem_private_key(
            key.read(),
            password=config.private_key_passphrase.encode(),
            backend=default_backend(),
        )

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

    connection = snowflake.connector.connect(
        user=config.user,
        account=config.account,
        private_key=pkb,
        warehouse=config.warehouse,
    )

    return connection.cursor()


def execute_sql(sql):

    """
    This function accepts sql input and returns a Pandas DataFrame of the output
    """

    cursor = connect_to_snowflake(config)

    results = cursor.execute(sql)

    df = pd.DataFrame(results, columns=[x[0].lower() for x in results.description])

    return df