import os
import sys
import subprocess
subprocess.check_call('pip install -r /opt/ml/processing/input/dependencies/requirements-maze.txt', shell=True)

import logging
logging.basicConfig(
   level=logging.INFO,
   format='%(asctime)s.%(msecs)03d %(levelname)s %(module)s - %(funcName)s: %(message)s',
   datefmt='%Y-%m-%d %H:%M:%S',
)

import snowflake.connector
import pandas as pd
import pyecharts as echarts
import boto3 
import re
from getpass import getpass
from absl import logging


log_level = "DEBUG"
ticket_code = "EXP_1"

logging.set_verbosity(log_level)
logging.debug("READY!!!")

sec_id = 'dev/sagemaker-notebook-instance/SNOWFLAKE_PASSWORD'


def get_secret_value(name, version=None):
    
    """Gets the value of a secret.

    Version (if defined) is used to retrieve a particular version of
    the secret.

    """
    secrets_client = boto3.client("secretsmanager", region_name='us-east-1')
    kwargs = {'SecretId': name}
    if version is not None:
        kwargs['VersionStage'] = version
    response = secrets_client.get_secret_value(**kwargs)
    return response


def get_snowflake_creds(username="SAGEMAKER", account="orchard",
                        warehouse="DEV_OWS_ENGINEERING"):
    """
    Fetches and returns snowflake creds for connecting to snowflake

    Please use this within the scope of a function if using this on a shared instance
    This is so that the password is in memory only when its needed and gets dropped 
    once its no longer required.

    returns:
    - creds (dict) - a dictionary containing user creds

    """
    creds = {
      "user":  username,
      "password": get_secret_value(sec_id)['SecretString'],
      "account": "orchard",
      "warehouse": warehouse,
      "protocol": 'https'
    }
    return creds


def snowflake_connector_factory(creds=None):
    """
    A Factory for creating snowflake connectors.

    This returns the cursor after opening a session with snowflake.

    params:
    - creds - snowflake credentials 

    returns:
    - cursor - snowflake session cursor
    """
    try:
        if creds:
            _creds = creds
        else:
            _creds = get_snowflake_creds()
        return snowflake.connector.connect(**_creds).cursor()
    except Exception as e:
        logging.error(f"Something went wrong - {str(e)}")


def _is_version_number(s):
    "Check and returns true if its a version number"
    return re.search("^[0-9][.0-9]*[0-9]$", s) is not None


def test_connection():
    """ tests connection to snowflake """
    with snowflake_connector_factory() as cs:
        try:
            cs.execute("SELECT current_version()")
            one_row = cs.fetchone()
            assert len(one_row) == 1
            assert _is_version_number(one_row[0])
            logging.info(f"Your snowflake version - {one_row[0]} PASSED!")
        except Exception as e:
            logging.error(f"Something went wrong - {str(e)}")


if __name__ == '__main__':

    
    with snowflake_connector_factory(get_snowflake_creds()) as cs:
        try:
            cs.execute("USE WAREHOUSE DEV_PERFORMANCE_WAREHOUSE;")
            cs.execute(""" 
            select * from dev_engineering.eimpara.maze_country_table;
            """)

            rows = cs.fetchall()
        except Exception as e:
            logging.error(f"Something went wrong - {str(e)}")
        
        data_df = pd.DataFrame(rows, columns=list(map(lambda meta: meta[0], cs.description)))
        df = data_df.drop_duplicates().copy()
        print(data_df.shape)
        print(df.shape)