#! /usr/bin/env python3

from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from contextlib import closing
from sql import sql_queries
import snowflake.connector
import psycopg2
import smtplib
import argparse
import sys
import json
import datetime

with open('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']

db_name = config['postgres']["database"]
db_user = config['postgres']["user"]
db_password = config['postgres']["password"]
db_host = config['postgres']["host"]
db_port = config['postgres']["port"]

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, message: str, status: str) -> None:
    # Create a multipart message
    msg = MIMEMultipart()
    body_part = MIMEText(message, 'plain')
    msg['Subject'] = f"[{status}][PROD] Fivetran Facebook/Google Ads"
    msg['From'] = "support.sme@dataart.com"
    msg['To'] = recipients
    # 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(','), msg.as_string())
    server.quit()

def division(a, b):
    try:
        result = a / b
    except ZeroDivisionError:
        result = 0
    return result

def snowflake_fetch_data(sql_query) -> dict:
    ctx = snowflake.connector.connect(
        user=sf_user,
        password=sf_password,
        account=sf_account,
        warehouse=sf_warehouse
    )
    cs = ctx.cursor(snowflake.connector.DictCursor)
    try:
        cs.execute(sql_query)
        result_dict = cs.fetchall()
    finally:
        cs.close()
    ctx.close()
    return result_dict


def postgres_fetch_data(sql_query) -> list:
    result_list = []
    with closing(psycopg2.connect(database=db_name, user=db_user, password=db_password, host=db_host, port=db_port)) as connection:
        with connection.cursor() as cursor:
            cursor.execute(sql_query)
            columns = cursor.description
            rows = cursor.fetchall()
    for row in rows:
        tmp = {}
        for i in range(len(columns)):
            tmp[columns[i][0]] = row[i]
        result_list.append(tmp)
    return result_list


def check_2() -> dict:
    result_dict = {}
    full_log = {}

    ### Check 2 FIVETRAN → RAW (Expected result: 0 results  ################################################
    tmp_sf_check_2 = snowflake_fetch_data(sql_queries.sf_check_2)

    if tmp_sf_check_2:
        full_log["CHECK_2"] = {'CHECK_2': 'FAILED: Query returned more than 0 results'}
        print(f"CHECK_2: FAILED: Query retured more than 0 results")
        result_dict["CHECK_2"] = full_log["CHECK_2"]
    else:
        full_log["CHECK_2"] = {'CHECK_2_RAW_STEP_1': 'OK: Query returned 0 results'}

    return result_dict, full_log

def checks_3_to_34() -> dict:
    result_dict = {}
    full_log = {}

    ### Check 3 Validate new data arrived in EXP (Expected result: 0 results  ################################################
    tmp_sf_check_3 = snowflake_fetch_data(sql_queries.sf_check_3)

    if tmp_sf_check_3:
        full_log["CHECK_3"] = {'CHECK_3': 'FAILED: Query returned more than 0 results'}
        print(f"CHECK_3: FAILED: Query retured more than 0 results")
        result_dict["CHECK_3"] = full_log["CHECK_3"]
    else:
        full_log["CHECK_3"] = {'CHECK_3_RAW_STEP_1': 'OK: Query returned 0 results'}

    ### Check 4 Validate new data arrived in SYS (Expected result: 0 results  ################################################
    tmp_sf_check_4 = snowflake_fetch_data(sql_queries.sf_check_4)

    if tmp_sf_check_4:
        full_log["CHECK_4"] = {'CHECK_4': 'FAILED: Query returned more than 0 results'}
        print(f"CHECK_4: FAILED: Query retured more than 0 results")
        result_dict["CHECK_4"] = full_log["CHECK_4"]
    else:
        full_log["CHECK_4"] = {'CHECK_4_RAW_STEP_1': 'OK: Query returned 0 results'}

    ### Check 5 RAW → SYS - FACEBOOK_ADS_AD_SET (Expected result: Counts match inside every step) ################################################
    tmp_sf_check_5 = snowflake_fetch_data(sql_queries.sf_check_5)
    sf_check_5_src = int(tmp_sf_check_5[0]['count'])
    sf_check_5_dest = int(tmp_sf_check_5[1]['count'])
    print(f"SRC_CHECK_5: - {sf_check_5_src}")
    print(f"DEST_CHECK_5: - {sf_check_5_dest}")
    full_log["CHECK_5"] = {'SRC_CHECK_5': sf_check_5_src, 'DEST_CHECK_5': sf_check_5_dest}

    if sf_check_5_src != sf_check_5_dest:
        result_dict["CHECK_5"] = full_log["CHECK_5"]

    ### Check 6 RAW → SYS - FACEBOOK_ADS_CAMPAIGN (Expected result: Counts match inside every step) ################################################
    tmp_sf_check_6 = snowflake_fetch_data(sql_queries.sf_check_6)
    sf_check_6_src = int(tmp_sf_check_6[0]['count'])
    sf_check_6_dest = int(tmp_sf_check_6[1]['count'])
    print(f"SRC_CHECK_6: - {sf_check_6_src}")
    print(f"DEST_CHECK_6: - {sf_check_6_dest}")
    full_log["CHECK_6"] = {'SRC_CHECK_6': sf_check_6_src, 'DEST_CHECK_6': sf_check_6_dest}

    if sf_check_6_src != sf_check_6_dest:
        result_dict["CHECK_6"] = full_log["CHECK_6"]

    ### Check 7 EXP → SYS - GOOGLE_ADS_AD_SET (Expected result: Counts match inside every step) ################################################
    tmp_sf_check_7 = snowflake_fetch_data(sql_queries.sf_check_7)
    sf_check_7_src = int(tmp_sf_check_7[0]['count'])
    sf_check_7_dest = int(tmp_sf_check_7[1]['count'])
    print(f"SRC_CHECK_7: - {sf_check_7_src}")
    print(f"DEST_CHECK_7: - {sf_check_7_dest}")
    full_log["CHECK_7"] = {'SRC_CHECK_7': sf_check_7_src, 'DEST_CHECK_7': sf_check_7_dest}

    if sf_check_7_src != sf_check_7_dest:
        result_dict["CHECK_7"] = full_log["CHECK_7"]

    ### Check 8 EXP → SYS - GOOGLE_ADS_CAMPAIGN (Expected result: Counts match inside every step) ################################################
    tmp_sf_check_8 = snowflake_fetch_data(sql_queries.sf_check_8)
    sf_check_8_src = int(tmp_sf_check_8[0]['count'])
    sf_check_8_dest = int(tmp_sf_check_8[1]['count'])
    print(f"SRC_CHECK_8: - {sf_check_8_src}")
    print(f"DEST_CHECK_8: - {sf_check_8_dest}")
    full_log["CHECK_8"] = {'SRC_CHECK_8': sf_check_8_src, 'DEST_CHECK_8': sf_check_8_dest}

    if sf_check_8_src != sf_check_8_dest:
        result_dict["CHECK_8"] = full_log["CHECK_8"]


    ### Check 9 RAW → EXP (Expected results: All counts are >0) ########################################
    #Data
    tmp_sf_check_9 = snowflake_fetch_data(sql_queries.sf_check_9)
    full_log["CHECK_9"] = {}

    #Check
    for item in tmp_sf_check_9:
        if item['count'] == 0:
            result_dict["CHECK_9"] = {}

    for item in tmp_sf_check_9:
        full_log["CHECK_9"].update({item['table']: item['count']})
        print(f"CHECK_9 table {item['table']} - {item['count']}")
        if item['count'] == 0:
            result_dict["CHECK_9"].update({item['table']: item['count']})

    ### Check 13 Data transfer status - Data consistency - ACCOUNTS (expected result: Counts match between steps) ################################################
    tmp_sf_check_13 = snowflake_fetch_data(sql_queries.sf_check_13)
    tmp_pg_check_13 = postgres_fetch_data(sql_queries.pg_check_13)
    sf_check_13_count = float(tmp_sf_check_13[0]['count'])
    pg_check_13_count = float(tmp_pg_check_13[0]['count'])
    result_check_13 = division(sf_check_13_count, pg_check_13_count)
    print(f"SRC_CHECK_13: - {sf_check_13_count}")
    print(f"DEST_CHECK_13: - {pg_check_13_count}")
    print(f"CHECK_13_RESULT: - {result_check_13}")
    full_log["CHECK_13"] = {'SRC_CHECK_13': sf_check_13_count, 'DEST_CHECK_13': pg_check_13_count, 'CHECK_13_RESULT': result_check_13}

    result_check_13 = division(sf_check_13_count, pg_check_13_count)
    if result_check_13 < 1 or result_check_13 > 1.05 :
        result_dict["CHECK_13"] = full_log["CHECK_13"]

    ### Check 14 Data transfer status - Data consistency - CAMPAIGNS (expected result: Counts match between steps) ################################################
    tmp_sf_check_14 = snowflake_fetch_data(sql_queries.sf_check_14)
    tmp_pg_check_14 = postgres_fetch_data(sql_queries.pg_check_14)
    sf_check_14_count = float(tmp_sf_check_14[0]['count'])
    pg_check_14_count = float(tmp_pg_check_14[0]['count'])
    result_check_14 = division(sf_check_14_count, pg_check_14_count)
    print(f"SRC_CHECK_14: - {sf_check_14_count}")
    print(f"DEST_CHECK_14: - {pg_check_14_count}")
    print(f"CHECK_14_RESULT: - {result_check_14}")
    full_log["CHECK_14"] = {'SRC_CHECK_14': sf_check_14_count, 'DEST_CHECK_14': pg_check_14_count, 'CHECK_14_RESULT': result_check_14}

    result_check_14 = division(sf_check_14_count, pg_check_14_count)
    if result_check_14 < 1 or result_check_14 > 1.05 :
        result_dict["CHECK_14"] = full_log["CHECK_14"]

    ### Check 15 Data transfer status - Data consistency - AD_SETS (expected result: Counts match between steps) ################################################
    tmp_sf_check_15 = snowflake_fetch_data(sql_queries.sf_check_15)
    tmp_pg_check_15 = postgres_fetch_data(sql_queries.pg_check_15)
    sf_check_15_count = float(tmp_sf_check_15[0]['count'])
    pg_check_15_count = float(tmp_pg_check_15[0]['count'])
    result_check_15 = division(sf_check_15_count, pg_check_15_count)
    print(f"SRC_CHECK_15: - {sf_check_15_count}")
    print(f"DEST_CHECK_15: - {pg_check_15_count}")
    print(f"CHECK_15_RESULT: - {result_check_15}")
    full_log["CHECK_15"] = {'SRC_CHECK_15': sf_check_15_count, 'DEST_CHECK_15': pg_check_15_count, 'CHECK_15_RESULT': result_check_15}

    result_check_15 = division(sf_check_15_count, pg_check_15_count)
    if result_check_15 < 1 or result_check_15 > 1.05 :
        result_dict["CHECK_15"] = full_log["CHECK_15"]

    ### Check 16 Data transfer status - Data consistency - GOOGLE_ADGROUP_PERFORMANCE_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_16 = snowflake_fetch_data(sql_queries.sf_check_16)
    tmp_pg_check_16 = postgres_fetch_data(sql_queries.pg_check_16)
    sf_check_16_count = float(tmp_sf_check_16[0]['count'])
    pg_check_16_count = float(tmp_pg_check_16[0]['count'])
    result_check_16 = division(sf_check_16_count, pg_check_16_count)
    print(f"SRC_CHECK_16: - {sf_check_16_count}")
    print(f"DEST_CHECK_16: - {pg_check_16_count}")
    print(f"CHECK_16_RESULT: - {result_check_16}")
    full_log["CHECK_16"] = {'SRC_CHECK_16': sf_check_16_count, 'DEST_CHECK_16': pg_check_16_count, 'CHECK_16_RESULT': result_check_16}

    result_check_16 = division(sf_check_16_count, pg_check_16_count)
    if result_check_16 < 1 or result_check_16 > 1.05 :
        result_dict["CHECK_16"] = full_log["CHECK_16"]

    ### Check 17 Data transfer status - Data consistency - GOOGLE_AGE_RANGE_PERFORMANCE_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_17 = snowflake_fetch_data(sql_queries.sf_check_17)
    tmp_pg_check_17 = postgres_fetch_data(sql_queries.pg_check_17)
    sf_check_17_count = float(tmp_sf_check_17[0]['count'])
    pg_check_17_count = float(tmp_pg_check_17[0]['count'])
    result_check_17 = division(sf_check_17_count, pg_check_17_count)
    print(f"SRC_CHECK_17: - {sf_check_17_count}")
    print(f"DEST_CHECK_17: - {pg_check_17_count}")
    print(f"CHECK_17_RESULT: - {result_check_17}")
    full_log["CHECK_17"] = {'SRC_CHECK_17': sf_check_17_count, 'DEST_CHECK_17': pg_check_17_count, 'CHECK_17_RESULT': result_check_17}

    result_check_17 = division(sf_check_17_count, pg_check_17_count)
    if result_check_17 < 1 or result_check_17 > 1.05 :
        result_dict["CHECK_17"] = full_log["CHECK_17"]

    ### Check 18 Data transfer status - Data consistency - GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_18 = snowflake_fetch_data(sql_queries.sf_check_18)
    tmp_pg_check_18 = postgres_fetch_data(sql_queries.pg_check_18)
    sf_check_18_count = float(tmp_sf_check_18[0]['count'])
    pg_check_18_count = float(tmp_pg_check_18[0]['count'])
    result_check_18 = division(sf_check_18_count, pg_check_18_count)
    print(f"SRC_CHECK_18: - {sf_check_18_count}")
    print(f"DEST_CHECK_18: - {pg_check_18_count}")
    print(f"CHECK_18_RESULT: - {result_check_18}")
    full_log["CHECK_18"] = {'SRC_CHECK_18': sf_check_18_count, 'DEST_CHECK_18': pg_check_18_count, 'CHECK_18_RESULT': result_check_18}

    result_check_18 = division(sf_check_18_count, pg_check_18_count)
    if result_check_18 < 1 or result_check_18 > 1.05 :
        result_dict["CHECK_18"] = full_log["CHECK_18"]

    ### Check 19 Data transfer status - Data consistency - GOOGLE_GENDER_PERFORMANCE_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_19 = snowflake_fetch_data(sql_queries.sf_check_19)
    tmp_pg_check_19 = postgres_fetch_data(sql_queries.pg_check_19)
    sf_check_19_count = float(tmp_sf_check_19[0]['count'])
    pg_check_19_count = float(tmp_pg_check_19[0]['count'])
    result_check_19 = division(sf_check_19_count, pg_check_19_count)
    print(f"SRC_CHECK_19: - {sf_check_19_count}")
    print(f"DEST_CHECK_19: - {pg_check_19_count}")
    print(f"CHECK_19_RESULT: - {result_check_19}")
    full_log["CHECK_19"] = {'SRC_CHECK_19': sf_check_19_count, 'DEST_CHECK_19': pg_check_19_count, 'CHECK_19_RESULT': result_check_19}

    result_check_19 = division(sf_check_19_count, pg_check_19_count)
    if result_check_19 < 1 or result_check_19 > 1.05 :
        result_dict["CHECK_19"] = full_log["CHECK_19"]

    ### Check 20 Data transfer status - Data consistency - GOOGLE_GEO_PERFORMANCE_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_20 = snowflake_fetch_data(sql_queries.sf_check_20)
    tmp_pg_check_20 = postgres_fetch_data(sql_queries.pg_check_20)
    sf_check_20_count = float(tmp_sf_check_20[0]['count'])
    pg_check_20_count = float(tmp_pg_check_20[0]['count'])
    result_check_20 = division(sf_check_20_count, pg_check_20_count)
    print(f"SRC_CHECK_20: - {sf_check_20_count}")
    print(f"DEST_CHECK_20: - {pg_check_20_count}")
    print(f"CHECK_20_RESULT: - {result_check_20}")
    full_log["CHECK_20"] = {'SRC_CHECK_20': sf_check_20_count, 'DEST_CHECK_20': pg_check_20_count, 'CHECK_20_RESULT': result_check_20}

    result_check_20 = division(sf_check_20_count , pg_check_20_count)
    if result_check_20 < 1 or result_check_20 > 1.05 :
        result_dict["CHECK_20"] = full_log["CHECK_20"]

    ### Check 21 Data transfer status - Data consistency - GOOGLE_BUDGET_HISTORY (expected result: Counts match between steps) ################################################
    tmp_sf_check_21 = snowflake_fetch_data(sql_queries.sf_check_21)
    tmp_pg_check_21 = postgres_fetch_data(sql_queries.pg_check_21)
    sf_check_21_count = float(tmp_sf_check_21[0]['count'])
    pg_check_21_count = float(tmp_pg_check_21[0]['count'])
    result_check_21 = division(sf_check_21_count, pg_check_21_count)
    print(f"SRC_CHECK_21: - {sf_check_21_count}")
    print(f"DEST_CHECK_21: - {pg_check_21_count}")
    print(f"CHECK_21_RESULT: - {result_check_21}")
    full_log["CHECK_21"] = {'SRC_CHECK_21': sf_check_21_count, 'DEST_CHECK_21': pg_check_21_count, 'CHECK_21_RESULT': result_check_21}

    result_check_21 = division(sf_check_21_count, pg_check_21_count)
    if result_check_21 < 1 or result_check_21 > 1.05 :
        result_dict["CHECK_21"] = full_log["CHECK_21"]

    ### Check 22 Data transfer status - Data consistency - FACEBOOK_AGE_GENDER_DAILY_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_22 = snowflake_fetch_data(sql_queries.sf_check_22)
    tmp_pg_check_22 = postgres_fetch_data(sql_queries.pg_check_22)
    sf_check_22_count = float(tmp_sf_check_22[0]['count'])
    pg_check_22_count = float(tmp_pg_check_22[0]['count'])
    result_check_22 = division(sf_check_22_count, pg_check_22_count)
    print(f"SRC_CHECK_22: - {sf_check_22_count}")
    print(f"DEST_CHECK_22: - {pg_check_22_count}")
    print(f"CHECK_22_RESULT: - {result_check_22}")
    full_log["CHECK_22"] = {'SRC_CHECK_22': sf_check_22_count, 'DEST_CHECK_22': pg_check_22_count, 'CHECK_22_RESULT': result_check_22}

    result_check_22 = division(sf_check_22_count, pg_check_22_count)
    if result_check_22 < 1 or result_check_22 > 1.05 :
        result_dict["CHECK_22"] = full_log["CHECK_22"]

    ### Check 23 Data transfer status - Data consistency - FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_23 = snowflake_fetch_data(sql_queries.sf_check_23)
    tmp_pg_check_23 = postgres_fetch_data(sql_queries.pg_check_23)
    sf_check_23_count = float(tmp_sf_check_23[0]['count'])
    pg_check_23_count = float(tmp_pg_check_23[0]['count'])
    result_check_23 = division(sf_check_23_count, pg_check_23_count)
    print(f"SRC_CHECK_23: - {sf_check_23_count}")
    print(f"DEST_CHECK_23: - {pg_check_23_count}")
    print(f"CHECK_23_RESULT: - {result_check_23}")
    full_log["CHECK_23"] = {'SRC_CHECK_23': sf_check_23_count, 'DEST_CHECK_23': pg_check_23_count, 'CHECK_23_RESULT': result_check_23}

    result_check_23 = division(sf_check_23_count, pg_check_23_count)
    if result_check_23 < 1 or result_check_23 > 1.05 :
        result_dict["CHECK_23"] = full_log["CHECK_23"]

    ### Check 24 Data transfer status - Data consistency - FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_24 = snowflake_fetch_data(sql_queries.sf_check_24)
    tmp_pg_check_24 = postgres_fetch_data(sql_queries.pg_check_24)
    sf_check_24_count = float(tmp_sf_check_24[0]['count'])
    pg_check_24_count = float(tmp_pg_check_24[0]['count'])
    result_check_24 = division(sf_check_24_count, pg_check_24_count)
    print(f"SRC_CHECK_24: - {sf_check_24_count}")
    print(f"DEST_CHECK_24: - {pg_check_24_count}")
    print(f"CHECK_24_RESULT: - {result_check_24}")
    full_log["CHECK_24"] = {'SRC_CHECK_24': sf_check_24_count, 'DEST_CHECK_24': pg_check_24_count, 'CHECK_24_RESULT': result_check_24}

    result_check_24 = division(sf_check_24_count, pg_check_24_count)
    if result_check_24 < 1 or result_check_24 > 1.05 :
        result_dict["CHECK_24"] = full_log["CHECK_24"]

    ### Check 25 Data transfer status - Data consistency - FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_25 = snowflake_fetch_data(sql_queries.sf_check_25)
    tmp_pg_check_25 = postgres_fetch_data(sql_queries.pg_check_25)
    sf_check_25_count = float(tmp_sf_check_25[0]['count'])
    pg_check_25_count = float(tmp_pg_check_25[0]['count'])
    result_check_25 = division(sf_check_25_count, pg_check_25_count)
    print(f"SRC_CHECK_25: - {sf_check_25_count}")
    print(f"DEST_CHECK_25: - {pg_check_25_count}")
    print(f"CHECK_25_RESULT: - {result_check_25}")
    full_log["CHECK_25"] = {'SRC_CHECK_25': sf_check_25_count, 'DEST_CHECK_25': pg_check_25_count, 'CHECK_25_RESULT': result_check_25}

    result_check_25 = division(sf_check_25_count, pg_check_25_count)
    if result_check_25 < 1 or result_check_25 > 1.05 :
        result_dict["CHECK_25"] = full_log["CHECK_25"]

    ### Check 26 Data transfer status - Data consistency - FACEBOOK_COUNTRY_DAILY_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_26 = snowflake_fetch_data(sql_queries.sf_check_26)
    tmp_pg_check_26 = postgres_fetch_data(sql_queries.pg_check_26)
    sf_check_26_count = float(tmp_sf_check_26[0]['count'])
    pg_check_26_count = float(tmp_pg_check_26[0]['count'])
    result_check_26 = division(sf_check_26_count, pg_check_26_count)
    print(f"SRC_CHECK_26: - {sf_check_26_count}")
    print(f"DEST_CHECK_26: - {pg_check_26_count}")
    print(f"CHECK_26_RESULT: - {result_check_26}")
    full_log["CHECK_26"] = {'SRC_CHECK_26': sf_check_26_count, 'DEST_CHECK_26': pg_check_26_count, 'CHECK_26_RESULT': result_check_26}

    result_check_26 = division(sf_check_26_count, pg_check_26_count)
    if result_check_26 < 1 or result_check_26 > 1.05 :
        result_dict["CHECK_26"] = full_log["CHECK_26"]

    ### Check 27 Data transfer status - Data consistency - FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_27 = snowflake_fetch_data(sql_queries.sf_check_27)
    tmp_pg_check_27 = postgres_fetch_data(sql_queries.pg_check_27)
    sf_check_27_count = float(tmp_sf_check_27[0]['count'])
    pg_check_27_count = float(tmp_pg_check_27[0]['count'])
    result_check_27 = division(sf_check_27_count, pg_check_27_count)
    print(f"SRC_CHECK_27: - {sf_check_27_count}")
    print(f"DEST_CHECK_27: - {pg_check_27_count}")
    print(f"CHECK_27_RESULT: - {result_check_27}")
    full_log["CHECK_27"] = {'SRC_CHECK_27': sf_check_27_count, 'DEST_CHECK_27': pg_check_27_count, 'CHECK_27_RESULT': result_check_27}

    result_check_27 = division(sf_check_27_count, pg_check_27_count)
    if result_check_27 < 1 or result_check_27 > 1.05 :
        result_dict["CHECK_27"] = full_log["CHECK_27"]

    ### Check 28 Data transfer status - Data consistency - FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_28 = snowflake_fetch_data(sql_queries.sf_check_28)
    tmp_pg_check_28 = postgres_fetch_data(sql_queries.pg_check_28)
    sf_check_28_count = float(tmp_sf_check_28[0]['count'])
    pg_check_28_count = float(tmp_pg_check_28[0]['count'])
    result_check_28 = division(sf_check_28_count, pg_check_28_count)
    print(f"SRC_CHECK_28: - {sf_check_28_count}")
    print(f"DEST_CHECK_28: - {pg_check_28_count}")
    print(f"CHECK_28_RESULT: - {result_check_28}")
    full_log["CHECK_28"] = {'SRC_CHECK_28': sf_check_28_count, 'DEST_CHECK_28': pg_check_28_count, 'CHECK_28_RESULT': result_check_28}

    result_check_28 = division(sf_check_28_count, pg_check_28_count)
    if result_check_28 < 1 or result_check_28 > 1.05 :
        result_dict["CHECK_28"] = full_log["CHECK_28"]

    ### Check 29 Data transfer status - Data consistency - FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_29 = snowflake_fetch_data(sql_queries.sf_check_29)
    tmp_pg_check_29 = postgres_fetch_data(sql_queries.pg_check_29)
    sf_check_29_count = float(tmp_sf_check_29[0]['count'])
    pg_check_29_count = float(tmp_pg_check_29[0]['count'])
    result_check_29 = division(sf_check_29_count, pg_check_29_count)
    print(f"SRC_CHECK_29: - {sf_check_29_count}")
    print(f"DEST_CHECK_29: - {pg_check_29_count}")
    print(f"CHECK_29_RESULT: - {result_check_29}")
    full_log["CHECK_29"] = {'SRC_CHECK_29': sf_check_29_count, 'DEST_CHECK_29': pg_check_29_count, 'CHECK_29_RESULT': result_check_29}

    result_check_29 = division(sf_check_29_count, pg_check_29_count)
    if result_check_29 < 1 or result_check_29 > 1.05 :
        result_dict["CHECK_29"] = full_log["CHECK_29"]

    ### Check 30 Data transfer status - Data consistency - FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT (expected result: Counts match between steps) ################################################
    tmp_sf_check_30 = snowflake_fetch_data(sql_queries.sf_check_30)
    tmp_pg_check_30 = postgres_fetch_data(sql_queries.pg_check_30)
    sf_check_30_count = float(tmp_sf_check_30[0]['count'])
    pg_check_30_count = float(tmp_pg_check_30[0]['count'])
    result_check_30 = division(sf_check_30_count, pg_check_30_count)
    print(f"SRC_CHECK_30: - {sf_check_30_count}")
    print(f"DEST_CHECK_30: - {pg_check_30_count}")
    print(f"CHECK_30_RESULT: - {result_check_30}")
    full_log["CHECK_30"] = {'SRC_CHECK_30': sf_check_30_count, 'DEST_CHECK_30': pg_check_30_count, 'CHECK_30_RESULT': result_check_30}

    result_check_30 = division(sf_check_30_count, pg_check_30_count)
    if result_check_30 < 1 or result_check_30 > 1.05 :
        result_dict["CHECK_30"] = full_log["CHECK_30"]

    ### Check 31 Data transfer status - Data consistency - FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_31 = snowflake_fetch_data(sql_queries.sf_check_31)
    tmp_pg_check_31 = postgres_fetch_data(sql_queries.pg_check_31)
    sf_check_31_count = float(tmp_sf_check_31[0]['count'])
    pg_check_31_count = float(tmp_pg_check_31[0]['count'])
    result_check_31 = division(sf_check_31_count, pg_check_31_count)
    print(f"SRC_CHECK_31: - {sf_check_31_count}")
    print(f"DEST_CHECK_31: - {pg_check_31_count}")
    print(f"CHECK_31_RESULT: - {result_check_31}")
    full_log["CHECK_31"] = {'SRC_CHECK_31': sf_check_31_count, 'DEST_CHECK_31': pg_check_31_count, 'CHECK_31_RESULT': result_check_31}

    result_check_31 = division(sf_check_31_count, pg_check_31_count)
    if result_check_31 < 1 or result_check_31 > 1.05 :
        result_dict["CHECK_31"] = full_log["CHECK_31"]

    ### Check 32 Data transfer status - Data consistency - FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_32 = snowflake_fetch_data(sql_queries.sf_check_32)
    tmp_pg_check_32 = postgres_fetch_data(sql_queries.pg_check_32)
    sf_check_32_count = float(tmp_sf_check_32[0]['count'])
    pg_check_32_count = float(tmp_pg_check_32[0]['count'])
    result_check_32 = division(sf_check_32_count, pg_check_32_count)
    print(f"SRC_CHECK_32: - {sf_check_32_count}")
    print(f"DEST_CHECK_32: - {pg_check_32_count}")
    print(f"CHECK_32_RESULT: - {result_check_32}")
    full_log["CHECK_32"] = {'SRC_CHECK_32': sf_check_32_count, 'DEST_CHECK_32': pg_check_32_count, 'CHECK_32_RESULT': result_check_32}

    result_check_32 = division(sf_check_32_count, pg_check_32_count)
    if result_check_32 < 1 or result_check_32 > 1.05 :
        result_dict["CHECK_32"] = full_log["CHECK_32"]

    ### Check 33 Data transfer status - Data consistency - FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS (expected result: Counts match between steps) ################################################
    tmp_sf_check_33 = snowflake_fetch_data(sql_queries.sf_check_33)
    tmp_pg_check_33 = postgres_fetch_data(sql_queries.pg_check_33)
    sf_check_33_count = float(tmp_sf_check_33[0]['count'])
    pg_check_33_count = float(tmp_pg_check_33[0]['count'])
    result_check_33 = division(sf_check_33_count, pg_check_33_count)
    print(f"SRC_CHECK_33: - {sf_check_33_count}")
    print(f"DEST_CHECK_33: - {pg_check_33_count}")
    print(f"CHECK_33_RESULT: - {result_check_33}")
    full_log["CHECK_33"] = {'SRC_CHECK_33': sf_check_33_count, 'DEST_CHECK_33': pg_check_33_count, 'CHECK_33_RESULT': result_check_33}

    result_check_33 = division(sf_check_33_count, pg_check_33_count)
    if result_check_33 < 1 or result_check_33 > 1.05 :
        result_dict["CHECK_33"] = full_log["CHECK_33"]

    ### Check 34 Data transfer status - Data consistency - FACEBOOK_AD_SET_HISTORY (expected result: Counts match between steps) ################################################
    tmp_sf_check_34 = snowflake_fetch_data(sql_queries.sf_check_34)
    tmp_pg_check_34 = postgres_fetch_data(sql_queries.pg_check_34)
    sf_check_34_count = float(tmp_sf_check_34[0]['count'])
    pg_check_34_count = float(tmp_pg_check_34[0]['count'])
    result_check_34 = division(sf_check_34_count, pg_check_34_count)
    print(f"SRC_CHECK_34: - {sf_check_34_count}")
    print(f"DEST_CHECK_34: - {pg_check_34_count}")
    print(f"CHECK_34_RESULT: - {result_check_34}")
    full_log["CHECK_34"] = {'SRC_CHECK_34': sf_check_34_count, 'DEST_CHECK_34': pg_check_34_count, 'CHECK_34_RESULT': result_check_34}

    result_check_34 = division(sf_check_34_count, pg_check_34_count)
    if result_check_34 < 1 or result_check_34 > 1.05 :
        result_dict["CHECK_34"] = full_log["CHECK_34"]

    return result_dict, full_log


################# run the all steps ######################
def run_steps() -> dict:
    message_dict = {'name':"[PROD] Fivetran Facebook/Google Ads",'status':"",'date':"", 'info':"", 'details':"", 'full_log':""}
    # set date according to sql searching date, currently it's `today`
    message_dict["date"] = (datetime.datetime.now() - datetime.timedelta(days=0)).strftime("%Y-%m-%d")

    result_dict_2, monitoring_2_log = check_2()
    result_dict_3_to_34, monitoring_3_to_34_log = checks_3_to_34()

    message_dict['full_log'] = {**monitoring_2_log, **monitoring_3_to_34_log}

    if result_dict_3_to_34 or result_dict_2:
        if result_dict_2:
            # Data is partially synced with Fivetran
            message_dict['status'] = "FAILED"
            message_dict['info'] = "Data is partially synced with Fivetran"
            message_dict['details'] = {**result_dict_2, **result_dict_3_to_34}
        elif result_dict_3_to_34:
            # Data is synced with Fivetran and partially available in API
            message_dict['status'] = "FAILED"
            message_dict['info'] = "Data is synced with Fivetran and partially available in API"
            message_dict['details'] = result_dict_3_to_34
    else:
        message_dict['status'] = "OK"
        message_dict['info'] = "Data is synced with Fivetran and available in API"

    return message_dict


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()
        with open('/tmp/Fivetran_Facebook_Google_Ads_daily_log.json', 'w') as json_file:
            json.dump(result, json_file, indent=4)

        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()