import os
import sys
import subprocess
subprocess.check_call('pip install -r /opt/ml/processing/input/dependencies/requirements-2.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)}")
        


def save_dataframe_s3(df, folder, prefix, run_id):
    
    '''Saving table to S3'''
    
    s3 = boto3.client('s3', region_name='us-east-1')
    bucket_name = 'dev-cucumbers'
    filepath = "{}/{}_{}.csv".format(folder, prefix, run_id)
    csv_buffer = df.to_csv(index=False).encode('utf-8')
    s3.put_object(Body=csv_buffer, Bucket=bucket_name, Key=filepath)
    print(f"Table saved to S3 bucket: {bucket_name}, with file name: {filepath}")

    
def get_argument(args, name, default_value = None):
    
    ''' Getting arguments from processing script '''
    
    
    arg_name = "--" + name
    if arg_name in args:
        index = args.index("--" + name) + 1
        return args[index]
    else:
        return default_value
    
def get_mandatory_argument(args, name):
    
    ''' Getting mandatory arguments from processing script '''
    
    res = get_argument(args, name)
    if res is None:
        raise "Missing script mandatory argument --{}".format(name)
    else:
        return res


if __name__ == '__main__':
    
    
    RUN_ID = get_mandatory_argument(sys.argv, "run-id")
    FOLDER = get_mandatory_argument(sys.argv, "folder")
    
    with snowflake_connector_factory(get_snowflake_creds()) as cs:
        try:
            cs.execute("USE WAREHOUSE DEV_PERFORMANCE_WAREHOUSE;")
            cs.execute(""" 
            select 
                isrc_key,
                isrc,
                transaction_country_code,
                activity_date,
                track_name,
                release_date,
                artist_name,
                artist_id,
                streams 
            from intelligence.dbt_prod_project_moments.project_moments_spotify_streams_140_day_dynamic_key_identifier;
            """)
            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)
        save_dataframe_s3(df, folder=FOLDER, prefix='MOMENTS_source', run_id=RUN_ID)