import snowflake.connector
import sys
import json

user = ""
password = ""
account = ""
warehouse = ""

### Arguments and checks description
# copy_history_file_status - Monitoring COPY_HISTORY_FILE_STATUS for any errors for last 1 day
### https://data-analytics.atlassian.net/wiki/spaces/DDPS/pages/999620854/EXP+Snowflake+fails+monitoring#Monitoring-COPY_HISTORY_FILE_STATUS-for-any-errors.

### Failed failed query history checks for last 7 hours by warehouse
# exploration_sys_prod_wh - EXPLORATION_SYS_PROD_WH warehouse failed query history for last 7 hours
# fivetran_warehouse - FIVETRAN_WAREHOUSE warehouse failed query history for last 7 hours
### https://data-analytics.atlassian.net/wiki/spaces/DDPS/pages/999620854/EXP+Snowflake+fails+monitoring#Monitoring-System-history


sql_query = {
    'copy_history_file_status': "select dsp,report_name,licensor,status,error_count,first_error_message from DELPHI_EXPLORATION.SYS.COPY_HISTORY_FILE_STATUS where error_count>0 and last_load_time >= dateadd(day, -1, current_timestamp());",
    'copy_history_file_status_full': "select dsp,report_name,licensor,status,error_count,first_error_message from DELPHI_EXPLORATION.SYS.COPY_HISTORY_FILE_STATUS where error_count>0;",
    'exploration_sys_prod_wh': "select query_id, \
                                        warehouse_name, \
                                        database_name, \
                                        schema_name, \
                                        user_name, \
                                        role_name, \
                                        execution_status, \
                                        error_code, \
                                        error_message \
                                        from table(delphi_exploration.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
                                                                                                WAREHOUSE_NAME => 'EXPLORATION_SYS_PROD_WH', \
                                                                                                END_TIME_RANGE_START => dateadd(hour, -3, current_timestamp()), \
                                                                                                END_TIME_RANGE_END => current_timestamp(), \
                                                                                                RESULT_LIMIT => 3000)) \
                                                                                where EXECUTION_STATUS like 'FAILED%' order by start_time desc;",
    'exploration_sys_prod_hl_wh': "select query_id, \
                                        warehouse_name, \
                                        database_name, \
                                        schema_name, \
                                        user_name, \
                                        role_name, \
                                        execution_status, \
                                        error_code, \
                                        error_message \
                                        from table(delphi_exploration.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
                                                                                                WAREHOUSE_NAME => 'EXPLORATION_SYS_PROD_HL_WH', \
                                                                                                END_TIME_RANGE_START => dateadd(hour, -3, current_timestamp()), \
                                                                                                END_TIME_RANGE_END => current_timestamp(), \
                                                                                                RESULT_LIMIT => 3000)) \
                                                                                where EXECUTION_STATUS like 'FAILED%' order by start_time desc;",
        'fivetran_warehouse': """select query_id, \
                                    warehouse_name, \
                                                                        database_name, \
                                    schema_name, \
                                    user_name, \
                                    role_name, \
                                    execution_status, \
                                    error_code, \
                                    error_message \
                        from table(delphi_ads_data.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
                                WAREHOUSE_NAME => 'FIVETRAN_WAREHOUSE', \
                                END_TIME_RANGE_START => dateadd(hour, -3, current_timestamp()), \
                                END_TIME_RANGE_END => current_timestamp(), \
                                RESULT_LIMIT => 5000)) \
                        where   EXECUTION_STATUS like 'FAILED%' and error_message not like 'Invalid%clustering%keys%or%table%' order by start_time desc;""",


       'ads_data_warehouse': "select query_id, \
                                    warehouse_name, \
                                    database_name, \
                                    schema_name, \
                                    user_name, \
                                    role_name, \
                                    execution_status, \
                                    error_code, \
                                    error_message \
                        from table(delphi_ads_data.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
                                WAREHOUSE_NAME => 'ADS_DATA_PROD_WH', \
                                END_TIME_RANGE_START => dateadd(hour, -3, current_timestamp()), \
                                END_TIME_RANGE_END => current_timestamp(), \
                                RESULT_LIMIT => 5000)) \
                        where   EXECUTION_STATUS like 'FAILED%' order by start_time desc;",

        'ae_reporting_prod_warehouse': "select query_id, \
                                    warehouse_name, \
                                    database_name, \
                                    schema_name, \
                                    user_name, \
                                    role_name, \
                                    execution_status, \
                                    error_code, \
                                    error_message \
                        from table(delphi_ads_data.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
                                WAREHOUSE_NAME => 'AE_REPORTING_PROD_WH', \
                                END_TIME_RANGE_START => dateadd(hour, -3, current_timestamp()), \
                                END_TIME_RANGE_END => current_timestamp(), \
                                RESULT_LIMIT => 5000)) \
                        where   EXECUTION_STATUS like 'FAILED%' order by start_time desc;"

}


### get query data in dictionary format
def snowflake_fetch_data(sql_req):
    ctx = snowflake.connector.connect(
        user=user,
        password=password,
        warehouse=warehouse,
        account=account

    )
    cs = ctx.cursor(snowflake.connector.DictCursor)
    try:
        cs.execute(sql_req)
        result_dict = cs.fetchall()
    finally:
        cs.close()
    ctx.close()
    return result_dict


### transform dictionary to csv file


if __name__ == '__main__':
    if len(sys.argv) > 1:
        arg = str(sys.argv[1])
        sf_data = snowflake_fetch_data(sql_query[arg])
        if sf_data:
            print(json.dumps(sf_data, indent=1))
        else:
            print("")
    else:
        print("no arguments were given")