### snowflake cursor doesn't support string sseparation, thus this var is adjusted

## TEST CHECK_01
####check_01="""select QUERY_ID "id", EXECUTION_STATUS "result" \
####from table(DELPHI_AE_REPORTING.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
####    WAREHOUSE_NAME => 'AE_REPORTING_PROD_WH', \
####    result_limit => 3 \
####)) q \
####where q.USER_NAME = 'SYSTEM' \
####    and datediff(hour, START_TIME, current_timestamp()) <= 40 \
####order by q.START_TIME desc;""",

sf_queries_1_4 = dict(
check_01="""select EXECUTION_STATUS = 'SUCCESS' \
    or EXECUTION_STATUS = 'RUNNING' \
    or EXECUTION_STATUS = 'BLOCKED' "result" \
from table(DELPHI_AE_REPORTING.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
    WAREHOUSE_NAME => 'AE_REPORTING_PROD_WH', \
    result_limit => 1000 \
)) q \
where q.QUERY_TEXT like 'merge into MAIN.AE_RETARGETING_DASHBOARD%' \
    and q.USER_NAME = 'SYSTEM' \
    and datediff(hour, START_TIME, current_timestamp()) <= 40 \
order by q.START_TIME desc;""",
check_02="""select EXECUTION_STATUS = 'SUCCESS' \
    or EXECUTION_STATUS = 'RUNNING' \
    or EXECUTION_STATUS = 'BLOCKED' "result" \
from table(DELPHI_AE_REPORTING.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
    WAREHOUSE_NAME => 'AE_REPORTING_PROD_WH', \
    result_limit => 1000 \
)) q \
where q.QUERY_TEXT like '%CDC_AE_MEMBER_DASHBOARD_DATA_AE_RETARGETING_DASHBOARD%' \
    and q.USER_NAME = 'SYSTEM' \
    and datediff(hour, START_TIME, current_timestamp()) <= 40 \
order by q.START_TIME desc;""",
check_03="""select EXECUTION_STATUS = 'SUCCESS' \
    or EXECUTION_STATUS = 'RUNNING' \
    or EXECUTION_STATUS = 'BLOCKED' "result" \
from table(DELPHI_AE_REPORTING.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
    WAREHOUSE_NAME => 'AE_REPORTING_PROD_WH', \
    result_limit => 1000 \
)) q \
where q.QUERY_TEXT like '%CDC_AE_ACTIVITY_AE_RETARGETING_DASHBOARD%' \
    and q.USER_NAME = 'SYSTEM' \
    and datediff(hour, START_TIME, current_timestamp()) <= 40 \
order by q.START_TIME desc;""",
check_04="""select EXECUTION_STATUS = 'SUCCESS' \
    or EXECUTION_STATUS = 'RUNNING' \
    or EXECUTION_STATUS = 'BLOCKED' "result" \
from table(DELPHI_AE_REPORTING.information_schema.QUERY_HISTORY_BY_WAREHOUSE( \
    WAREHOUSE_NAME => 'AE_REPORTING_PROD_WH', \
    result_limit => 1000 \
)) q \
where q.QUERY_TEXT like '%CDC_AE_BUSINESS_UNIT_AE_RETARGETING_DASHBOARD%' \
    and q.USER_NAME = 'SYSTEM' \
    and datediff(hour, START_TIME, current_timestamp()) <= 40 \
order by q.START_TIME desc;"""
)

sf_queries_5_8 = dict(
check_05="""select QUERY_ID, QUERY_TEXT, STATE, ERROR_MESSAGE, SCHEDULED_TIME, COMPLETED_TIME \
from table(DELPHI_AE_REPORTING.information_schema.task_history( \
    scheduled_time_range_start=>dateadd('hour',-40,current_timestamp()), \
    result_limit => 10000, \
    task_name=>'AE_RETARGETING_DASHBOARD_MERGE')) \
where DATABASE_NAME = 'DELPHI_AE_REPORTING' and ERROR_CODE is not null and STATE <> 'SKIPPED' \
order by SCHEDULED_TIME desc;""",
check_06="""select QUERY_ID, QUERY_TEXT, STATE, ERROR_MESSAGE, SCHEDULED_TIME, COMPLETED_TIME \
from table(DELPHI_AE_REPORTING.information_schema.task_history( \
    scheduled_time_range_start=>dateadd('hour',-40,current_timestamp()), \
    result_limit => 10000, \
    task_name=>'AE_RETARGETING_DASHBOARD_UPDATE_MEMBER_DATA')) \
where DATABASE_NAME = 'DELPHI_AE_REPORTING' and ERROR_CODE is not null and STATE <> 'SKIPPED' \
order by SCHEDULED_TIME desc;""",
check_07="""select QUERY_ID, QUERY_TEXT, STATE, ERROR_MESSAGE, SCHEDULED_TIME, COMPLETED_TIME \
from table(DELPHI_AE_REPORTING.information_schema.task_history( \
    scheduled_time_range_start=>dateadd('hour',-40,current_timestamp()), \
    result_limit => 10000, \
    task_name=>'AE_RETARGETING_DASHBOARD_UPDATE_ACTIVITY_DATA')) \
where DATABASE_NAME = 'DELPHI_AE_REPORTING' and ERROR_CODE is not null and STATE <> 'SKIPPED' \
order by SCHEDULED_TIME desc;""",
check_08="""select QUERY_ID, QUERY_TEXT, STATE, ERROR_MESSAGE, SCHEDULED_TIME, COMPLETED_TIME \
from table(DELPHI_AE_REPORTING.information_schema.task_history( \
    scheduled_time_range_start=>dateadd('hour',-40,current_timestamp()), \
    result_limit => 10000, \
    task_name=>'AE_RETARGETING_DASHBOARD_UPDATE_BUSINESS_UNIT_DATA')) \
where DATABASE_NAME = 'DELPHI_AE_REPORTING' and ERROR_CODE is not null and STATE <> 'SKIPPED' \
order by SCHEDULED_TIME desc;"""
)

sf_queries_9 = dict(
check_09 = """with \
    cte_rt (rt_count) AS ( \
        select count(*) as rt_count \ 
          from DELPHI_AE_REPORTING.MAIN.AE_RETARGETING_DASHBOARD \
    ), \
    cte_cdc_di as ( \
        select \
            zeroifnull(sum(iff(METADATA$ACTION = 'INSERT', 1, 0))) as cdc_i_count, \
            zeroifnull(sum(iff(METADATA$ACTION = 'DELETE', 1, 0))) as cdc_d_count \
        from DELPHI_EXPLORATION.MAIN.CDC_AE_ACTIVITIES_FEED_AE_RETARGETING_DASHBOARD \
        where METADATA$ISUPDATE = false \
    ) \
select rt_count + cdc_i_count - cdc_d_count as count, 'RETARGETING_DASHBOARD + data from stream' as table_name from cte_rt, cte_cdc_di \
union \
select count(*), 'ACTIVITIES_FEED' \
from DELPHI_EXPLORATION.MAIN.AE_ACTIVITIES_FEED;"""
)

if __name__ == '__main__':
    pass