#! /usr/bin/env python3

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from contextlib import closing
from logging import info
import snowflake.connector
import psycopg2
import smtplib
import re
import argparse
import sys
import json
import datetime
#import boto3

# connection handles
sf_conn = None
pg_conn = None

bcc_rec = ['andrey.minyaylo@dataart.com','andrey.minyaylo@dataart.com']

with open('/root/cron/GDPR/config.json', 'r') as f:
    config = json.load(f)

sf_user = config['snowflake']["user"]
sf_password = config['snowflake']["password"]
sf_account = config['snowflake']["account"]
sf_warehouse = config['snowflake']['warehouse']

local_db_name = config['failure_counter']["database"]
local_db_user = config['failure_counter']["user"]
local_db_password = config['failure_counter']["password"]
local_db_host = config['failure_counter']["host"]
local_db_port = config['failure_counter']["port"]

failure_count_increase = "UPDATE failed_count SET failed_count = failed_count + 1, last = 'FAILED', last_updated = NOW() WHERE count_id = 'aegdprd';"
failure_count_to_zero = "UPDATE failed_count SET failed_count = 0, last = 'OK', last_updated = NOW() WHERE count_id = 'aegdprd';"

def postgres_update_failure_count(sql_query):
    with closing(psycopg2.connect(database=local_db_name, user=local_db_user, password=local_db_password, host=local_db_host, port=local_db_port)) as connection:
        with connection.cursor() as cursor:
            cursor.execute(sql_query)
            connection.commit()

def create_arg_parser():
    parser = argparse.ArgumentParser()
    parser.add_argument('-m', '--mail',
                        nargs='+',
                        action='store',
                        dest='recipients',
                        type=str)
    parser.add_argument('-p', '--print', action="store_const", const=True)
    return parser

def send_mail(recipients: str, bcc_rec: str, message: str, status: str) -> None:
    # Create a multipart message
    msg = MIMEMultipart()
    body_part = MIMEText(message, 'plain')
    msg['Subject'] = f"[{status}] Appreciation Engine GDPR daily monitoring"
    msg['From'] = "support.sme@dataart.com"
    msg['To'] = recipients 
    #msg['Bcc'] = bcc_rec
    # Add body to email
    msg.attach(body_part)
    # Create SMTP object
    server = smtplib.SMTP('relay1.dataart.com', 25)
    server.sendmail(msg['From'], msg['To'].split(',') + bcc_rec, msg.as_string())
    server.quit()


def snowflake_fetch_data(sql_query) -> dict:
    # fetching data from snowflake
    global sf_conn
    if not sf_conn:
        try:
            sf_conn = snowflake.connector.connect(
                user=sf_user,
                password=sf_password,
                account=sf_account,
                warehouse=sf_warehouse,
                network_timeout=1200
            )
        except:
            print("Error: unable to connect to Snowflake")
            return False

    cs = sf_conn.cursor(snowflake.connector.DictCursor)
    try:
        cs.execute(sql_query)
        result_dict = cs.fetchall()
    except snowflake.connector.errors.ProgrammingError as sf_error:
        # default error message
        print("Snowflake error: " + sf_error)
        # customer error message
        print('Error {0} ({1}): {2}'.format(sf_error.errno, sf_error.sqlstate, sf_error.msg))
    finally:
        cs.close()

    return result_dict

def check_18():

    # Step 18: Daily clean-up wipes out all already mapped members from AE GDPR:yes tables
    sql_query = """select distinct mp.ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_MEMBER" mp \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on mp.ID = mgum.MEMBER_ID \
where mp.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct md.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_DOMAINS" md \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on md.MEMBER_ID = mgum.MEMBER_ID \
where md.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct mpr.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_PROFILES" mpr \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on mpr.MEMBER_ID = mgum.MEMBER_ID \
where mpr.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct ms.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_SERVICES" ms \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on ms.MEMBER_ID = mgum.MEMBER_ID \
where ms.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct mo.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_OPT_INS" mo \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on mo.MEMBER_ID = mgum.MEMBER_ID \
where mo.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct ml.ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_LOGINS" ml \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on ml.ID = mgum.MEMBER_ID \
where ml.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct af.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_ACTIVITIES_FEED" af \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on af.MEMBER_ID = mgum.MEMBER_ID \
where af.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct afp.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."MAIN"."AE_ACTIVITIES_FEED_PERSONAL" afp \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on afp.MEMBER_ID = mgum.MEMBER_ID \
where afp.LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct raf.MEMBER_ID as MEMBER_ID from "DELPHI_EXPLORATION"."RAW"."AE_ACTIVITIES_FEED" raf \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on raf.MEMBER_ID = mgum.MEMBER_ID \
where raf.REPORT_LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct rml.ID as MEMBER_ID from "DELPHI_EXPLORATION"."RAW"."AE_MEMBERS_LOGINS" rml \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on rml.ID = mgum.MEMBER_ID \
where rml.REPORT_LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz) \
  union \
select distinct me.ID as MEMBER_ID from "DELPHI_EXPLORATION"."RAW"."AE_MEMBER_EXTENDED" me \
join "DELPHI_EXPLORATION"."MAIN"."AE_MEMBERS_GDPR_UNLISTED_MAPPING" mgum on me.ID = mgum.MEMBER_ID \
where me.REPORT_LOAD_TIMESTAMP < cast(current_date()||' 03:00:00 -0700' as timestamp_ltz);"""

    result_raw = snowflake_fetch_data(sql_query)
    if result_raw != False:
        # we have got some rows from snowflake
        result_rows = len(result_raw)
        print("Check 18: rows = " + str(result_rows))
        return result_rows
    else:
        return False


################# run steps ######################

def run_steps() -> dict:
    message_dict = {'status':"",'date':"", 'info':""}
    # set date according to sql searching date
    message_dict["date"] = (datetime.datetime.now() - datetime.timedelta(days=0)).strftime("%Y-%m-%d")

    failed_18 = False
    result_18 = check_18()
    if result_18 is False or result_18 > 0:
        failed_18 = True

    full_log = {}
    if not failed_18:
        # All steps were successfull
        postgres_update_failure_count(failure_count_to_zero)
        message_dict['status'] = "OK"
        message_dict['info'] = "GDPR daily monitoring: all step completed successfully, no deviations found."
    else:
        # some step(s) have failed
        postgres_update_failure_count(failure_count_increase)
        message_dict['status'] = "FAILED"
        message_dict['info'] = "GDPR daily monitoring: some steps have failed"

        if result_18 > 0:
            message_dict['step_18_details'] = "Step 18 failed: Result = " + str(result_18) + " rows"
        if result_18 is False:
            message_dict['step_18_details'] = "Step 18 failed: Result = SQL query failed"

    full_log["Step 18 rows"] = result_18
    message_dict['full_log'] = full_log
    return message_dict

# Close all connections on exit
def exit():
    global sf_conn
    global pg_conn

    if sf_conn:
        sf_conn.close()

    if pg_conn:
        pg_conn.close()


if __name__ == '__main__':
    # # check command line parameters and print or email result
    parser = create_arg_parser()
    arg_list = parser.parse_args()

    if len(sys.argv[1:]) > 0:

        result = run_steps()

        if arg_list.recipients:
            recipients = arg_list.recipients[0]
            send_mail(recipients=recipients, message=json.dumps(result, indent=4), status=result['status'])

        if arg_list.print:
            print(json.dumps(result, indent=4))
    else:
        parser.print_help()

exit()