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

sf_check_2 = """with log as (select log._fivetran_synced, log.connector_id, connector.connector_name, try_parse_json( message_data )['table']::string as table_name, case when transformation_id is not null and message_data like '%has succeeded%' then 'transformation run success' when transformation_id is not null and message_data like '%has failed%' then 'transformation run failed' else message_event end as event_subtype from delphi_ads_data.fivetran_log.log left join delphi_ads_data.fivetran_log.connector connector         on connector.connector_id = log.connector_id ), facebook as ( select connector_name as "schema", table_name, MAX(_FIVETRAN_SYNCED) AS LAST_SYNC_DATE, DATEDIFF(day,MAX(_FIVETRAN_SYNCED), CURRENT_DATE()) AS DAYS_SINCE_LAST_UPDT, COUNT_IF( DATEDIFF(day,_FIVETRAN_SYNCED, CURRENT_DATE()) < 30) AS UPDATES_PER_LAST_MONTH, COUNT_IF( DATEDIFF(day,_FIVETRAN_SYNCED, CURRENT_DATE()) < 7) AS UPDATES_PER_LAST_WEEK from log where 1=1 and event_subtype = 'write_to_table_end' and "schema" like 'raw_facebook_ads_gr%' and table_name NOT IN (   'age_gender_daily_report_cost_per_conversion',   'link_url_asset_daily_report_cost_per_conversion',   'country_daily_report_cost_per_conversion', 'link_url_asset_daily_report_conversions',   'publisher_platform_daily_report_cost_per_conversion',   'age_gender_daily_report_action_values',   'country_daily_report_action_values',   'publisher_platform_daily_report_action_values',   'link_url_asset_daily_report_action_values',   'age_gender_daily_report_conversions',   'publisher_platform_daily_report_conversions',   'country_daily_report_conversions'   ) group by 1,2 ), google as ( select connector_name as "schema", table_name, MAX(_FIVETRAN_SYNCED) AS LAST_SYNC_DATE, DATEDIFF(day,MAX(_FIVETRAN_SYNCED), CURRENT_DATE()) AS DAYS_SINCE_LAST_UPDT, COUNT_IF( DATEDIFF(day,_FIVETRAN_SYNCED, CURRENT_DATE()) < 30) AS UPDATES_PER_LAST_MONTH, COUNT_IF( DATEDIFF(day,_FIVETRAN_SYNCED, CURRENT_DATE()) < 7) AS UPDATES_PER_LAST_WEEK from log where 1=1 and event_subtype = 'write_to_table_end' and "schema" in ( 'raw_google_ad_belgium', 'raw_google_ad_germanymcc', 'raw_google_ad_sonywavo', 'raw_google_ad_smelatiniberia', 'raw_google_ad_switzerland', 'raw_google_ad_sonymcc', 'raw_google_ad_sonymusicgroup', 'raw_google_ad_australia_newzealand', 'raw_google_ad_benelux', 'raw_google_ad_sme_uk', 'raw_google_ad_belgium' ) and table_name IN ( 'adgroup_performance_report', 'ad_performance_report', 'age_range_performance_report', 'campaign_bidding_strategy_history', 'campaign_group_performance_report', 'final_url_report', 'gender_performance_report', 'geo_performance_report' ) group by 1,2 ) select * from (select * from google union all select * from facebook) WHERE DAYS_SINCE_LAST_UPDT > 1 ;"""

sf_check_3 = """SELECT *  \
FROM ( \
  SELECT \
    "TABLE_SCHEMA", \
    "TABLE_NAME", \
    "LAST_ALTERED" AS LAST_SYNC_DATE, \
    DATEDIFF(day, LAST_ALTERED , CURRENT_DATE()) AS DAYS_SINCE_LAST_UPDT \
  FROM "DELPHI_ADS_DATA".information_schema.tables  \
  WHERE 1=1 \
    AND table_schema = 'EXP' \
    AND "TABLE_NAME" not like '%BACKUP' \
) \
WHERE DAYS_SINCE_LAST_UPDT > 0;"""

sf_check_4 = """SELECT *  \
FROM ( \
  SELECT \
    "TABLE_SCHEMA", \
    "TABLE_NAME", \
    "LAST_ALTERED" AS LAST_SYNC_DATE, \
    DATEDIFF(day, LAST_ALTERED , CURRENT_DATE()) AS DAYS_SINCE_LAST_UPDT \
  FROM "DELPHI_ADS_DATA".information_schema.tables  \
  WHERE 1=1 \
    AND table_schema = 'SYS' \
    AND "TABLE_NAME" IN ('DATA_EXPORT_LOG', \
                        'FACEBOOK_ADS_AD_SET', \
                        'FACEBOOK_ADS_CAMPAIGN', \
                        'FACEBOOK_CAMPAIGN_ARTIST_LINKS',  \
                        'FACEBOOK_CAMPAIGN_PROJECT_LINKS', \
                        'GOOGLE_ADS_AD_SET', \
                        'GOOGLE_ADS_CAMPAIGN', \
                        'GOOGLE_CAMPAIGN_ARTIST_LINKS', \
                        'GOOGLE_CAMPAIGN_PROJECT_LINKS', \
                        'TIKTOK_ADS_AD_SET', \
                        'TIKTOK_ADS_CAMPAIGN' \
                        ) \
) \
WHERE DAYS_SINCE_LAST_UPDT > 0 \
;"""

sf_check_5 = """select count(distinct ADSET_ID) "count", 'src; SF raw' "table" \
from ( \
    select  \
      ADSET_ID,  \
      _fivetran_synced \
    from "DELPHI_ADS_DATA"."RAW_FACEBOOK_ADS"."AGE_GENDER_DAILY_REPORT" \
    where CAMPAIGN_ID is not null \
        and ADSET_ID is not null \
        and ACCOUNT_ID is not null \
     \
    union  \
     \
    select  \
      ADSET_ID, \
      _fivetran_synced \
    from "DELPHI_ADS_DATA"."RAW_FACEBOOK_ADS"."COUNTRY_DAILY_REPORT" \
    where CAMPAIGN_ID is not null \
        and ADSET_ID is not null \
        and ACCOUNT_ID is not null    \
     \
    union  \
     \
    select  \
      ADSET_ID, \
      _fivetran_synced  \
    from "DELPHI_ADS_DATA"."RAW_FACEBOOK_ADS"."PUBLISHER_PLATFORM_DAILY_REPORT" \
    where CAMPAIGN_ID is not null \
        and ADSET_ID is not null \
        and ACCOUNT_ID is not null \
) \
where _fivetran_synced < ( \
  select max(greatest(created_at,updated_at))  \
  from  "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_AD_SET" \
) \
 \
union \
 \
select count(distinct AD_SET_ID) "count", 'dest; SF sys' "table" \
from "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_AD_SET" \
where created_at < current_date() -1  \
; """

sf_check_6 = """select count(*) "count", 'src; SF raw' "table" \
from ( \
    select CAMPAIGN_ID \
    from "DELPHI_ADS_DATA"."RAW_FACEBOOK_ADS"."AGE_GENDER_DAILY_REPORT" \
    where CAMPAIGN_ID is not null \
        and OBJECTIVE is not null \
    union  \
    select CAMPAIGN_ID \
    from "DELPHI_ADS_DATA"."RAW_FACEBOOK_ADS"."COUNTRY_DAILY_REPORT" \
    where CAMPAIGN_ID is not null \
        and OBJECTIVE is not null \
    union  \
    select CAMPAIGN_ID \
    from "DELPHI_ADS_DATA"."RAW_FACEBOOK_ADS"."PUBLISHER_PLATFORM_DAILY_REPORT" \
    where CAMPAIGN_ID is not null \
        and OBJECTIVE is not null \
) \
union  \
select count(distinct CAMPAIGN_ID) "count", 'dest; SF sys' "table" \
from "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_CAMPAIGN";"""

sf_check_7 = """select count(*) "count", 'src; SF exp' "table" \
from ( \
    select AD_GROUP_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_ADGROUP_PERFORMANCE_REPORT" \
    union select AD_GROUP_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_AGE_RANGE_PERFORMANCE_REPORT" \
    union select AD_GROUP_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_GENDER_PERFORMANCE_REPORT" \
    union select AD_GROUP_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_GEO_PERFORMANCE_REPORT" \
)  \
union  \
select count(distinct AD_SET_ID) "count", 'dest; SF sys' "table" \
from "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_AD_SET";"""

sf_check_8 = """select count(distinct(CAMPAIGN_ID)) "count", 'src; SF exp' "table" \
from ( \
    select CAMPAIGN_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_ADGROUP_PERFORMANCE_REPORT" \
    union select CAMPAIGN_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_AGE_RANGE_PERFORMANCE_REPORT" \
    union select CAMPAIGN_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_GENDER_PERFORMANCE_REPORT" \
    union select CAMPAIGN_ID from "DELPHI_ADS_DATA"."EXP"."GOOGLE_GEO_PERFORMANCE_REPORT" \
)  \
union  \
select count(distinct CAMPAIGN_ID) "count", 'dest; SF sys' "table" \
from "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN";"""

sf_check_9 = """select count(*) "count", 'FACEBOOK_AD_SET_HISTORY' "table" from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AD_SET_HISTORY" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_AGE_GENDER_DAILY_REPORT' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_CAMPAIGN_HISTORY' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_CAMPAIGN_HISTORY" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_COUNTRY_DAILY_REPORT' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS' from  "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_ADGROUP_PERFORMANCE_REPORT' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_ADGROUP_PERFORMANCE_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_AD_PERFORMANCE_REPORT' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_AD_PERFORMANCE_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_AGE_RANGE_PERFORMANCE_REPORT' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_AGE_RANGE_PERFORMANCE_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_BUDGET_HISTORY' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_BUDGET_HISTORY" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_GENDER_PERFORMANCE_REPORT' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_GENDER_PERFORMANCE_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED) \
union select count(*), 'GOOGLE_GEO_PERFORMANCE_REPORT' from  "DELPHI_ADS_DATA"."EXP"."GOOGLE_GEO_PERFORMANCE_REPORT" where CURRENT_DATE()-1 = TO_DATE(_FIVETRAN_SYNCED);"""


sf_check_13 = """SELECT  \
  COUNT(*) "count", 'src; SF sys' "table" \
FROM ( \
    SELECT 'facebook' as dsp, ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_ACCOUNT" \
    UNION ALL \
    SELECT 'google' as dsp, ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT" \
    UNION ALL \
    SELECT 'tiktok' as dsp, ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."TIKTOK_ADS_ACCOUNT" \
);"""
pg_check_13 = """select  
  count(*) "count", 'dest; PG' "table"
from PUBLIC.ADS_ACCOUNT;"""

sf_check_14 = """SELECT  \
  COUNT(*) "count", 'src; SF sys' "table" \
FROM ( \
    SELECT 'facebook' as dsp, CAMPAIGN_ID  \
    FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_CAMPAIGN"  \
    WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_ACCOUNT") \
      AND OBJECTIVE IS NOT NULL \
      AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG") \
    UNION ALL \
    SELECT 'google' as dsp, CAMPAIGN_ID \
    FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN"  \
    WHERE CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT") \
      AND OBJECTIVE IS NOT NULL \
      AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG") \
    UNION ALL \
    SELECT 'tiktok' as dsp, CAMPAIGN_ID  \
    FROM "DELPHI_ADS_DATA"."SYS"."TIKTOK_ADS_CAMPAIGN"  \
    WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."TIKTOK_ADS_ACCOUNT") \
      AND OBJECTIVE_TYPE IS NOT NULL \
      AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG") \
);"""
pg_check_14 = """select 
  count(*) "count", 'dest; PG' "table"
from PUBLIC.ADS_CAMPAIGN 
where 1=1
  and objective is not null
  and account_id in (select ACCOUNT_ID from PUBLIC.ADS_ACCOUNT);"""

sf_check_15 = """SELECT  \
  COUNT(*) "count", 'src; SF sys' "table" \
FROM ( \
    SELECT 'facebook' as dsp, AD_SET_ID  \
    FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_AD_SET" \
    WHERE CAMPAIGN_ID IN ( \
        SELECT CAMPAIGN_ID  \
        FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_CAMPAIGN" \
        WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_ACCOUNT") \
    ) \
    AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG") \
     \
    UNION ALL \
   \
    SELECT 'google' as dsp, AD_SET_ID  \
    FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_AD_SET" \
    WHERE CAMPAIGN_ID IN ( \
        SELECT CAMPAIGN_ID  \
        FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN" \
        WHERE CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT")     \
    ) \
    AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG") \
   \
    UNION ALL \
   \
    SELECT 'tiktok' as dsp, AD_SET_ID  \
    FROM "DELPHI_ADS_DATA"."SYS"."TIKTOK_ADS_AD_SET" \
    WHERE CAMPAIGN_ID IN ( \
        SELECT CAMPAIGN_ID  \
        FROM "DELPHI_ADS_DATA"."SYS"."TIKTOK_ADS_CAMPAIGN" \
        WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."TIKTOK_ADS_ACCOUNT")     \
    ) \
    AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG")   \
);"""
pg_check_15 = """select 
  count(*) "count", 'dest; PG' "table"
FROM PUBLIC.ADS_AD_SET;"""

sf_check_16 = """SELECT count(distinct customer_id, campaign_id, ad_group_id, ad_network_type_1, date) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_ADGROUP_PERFORMANCE_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
  AND CAMPAIGN_ID IS NOT NULL \
  AND CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT") \
  AND AD_GROUP_ID IN (SELECT AD_SET_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_AD_SET") \
  AND CAMPAIGN_ID IN (SELECT CAMPAIGN_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN") \
;"""
pg_check_16 = """select count(*) "count", 'dest; PG' "table" 
from (
  select customer_id, campaign_id, ad_group_id, ad_network_type_1, date
  from GOOGLE.ADGROUP_PERFORMANCE_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by customer_id, campaign_id, ad_group_id, ad_network_type_1, date
) a;"""

sf_check_17 = """SELECT count(distinct customer_id, campaign_id, ad_group_id, ad_network_type_1, date, criteria) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_AGE_RANGE_PERFORMANCE_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
  AND CAMPAIGN_ID IS NOT NULL \
  AND CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT") \
  AND AD_GROUP_ID IN (SELECT AD_SET_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_AD_SET") \
  AND CAMPAIGN_ID IN (SELECT CAMPAIGN_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN");"""
pg_check_17 = """select count(*) "count", 'dest; PG' "table" 
from (
  select customer_id, campaign_id, ad_group_id, ad_network_type_1, date, criteria
  from GOOGLE.AGE_RANGE_PERFORMANCE_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by customer_id, campaign_id, ad_group_id, ad_network_type_1, date, criteria
) a;"""

sf_check_18 = """SELECT count(distinct customer_id, campaign_id, ad_network_type_1, date) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
  AND CAMPAIGN_ID IS NOT NULL \
  AND CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT") \
  AND CAMPAIGN_ID IN (SELECT CAMPAIGN_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN");"""
pg_check_18 = """select count(*) "count", 'dest; PG' "table" 
from (
  select customer_id, campaign_id, ad_network_type_1, date
  from GOOGLE.CAMPAIGN_GROUP_PERFORMANCE_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by customer_id, campaign_id, ad_network_type_1, date
) a;"""

sf_check_19 = """SELECT count(distinct customer_id, campaign_id, ad_group_id, ad_network_type_1, date, criteria) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_GENDER_PERFORMANCE_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
  AND CAMPAIGN_ID IS NOT NULL \
  AND CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT") \
  AND AD_GROUP_ID IN (SELECT AD_SET_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_AD_SET") \
  AND CAMPAIGN_ID IN (SELECT CAMPAIGN_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN");"""
pg_check_19 = """select count(*) "count", 'dest; PG' "table" 
from (
  select customer_id, campaign_id, ad_group_id, ad_network_type_1, date, criteria
  from GOOGLE.GENDER_PERFORMANCE_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by customer_id, campaign_id, ad_group_id, ad_network_type_1, date, criteria
) a;"""

sf_check_20 = """SELECT count(distinct customer_id, campaign_id, ad_group_id, ad_network_type_1, date, country_criteria_id) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_GEO_PERFORMANCE_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
  AND CAMPAIGN_ID IS NOT NULL \
  AND CUSTOMER_ID IN (SELECT ACCOUNT_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_ACCOUNT") \
  AND AD_GROUP_ID IN (SELECT AD_SET_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_AD_SET") \
  AND CAMPAIGN_ID IN (SELECT CAMPAIGN_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN");"""
pg_check_20 = """select count(*) "count", 'dest; PG' "table" 
from (
  select customer_id, campaign_id, ad_group_id, ad_network_type_1, date, country_criteria_id
  from GOOGLE.GEO_PERFORMANCE_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by customer_id, campaign_id, ad_group_id, ad_network_type_1, date, country_criteria_id
) a;"""

sf_check_21 = """SELECT count(distinct id, campaign_id) "count", 'src; SF exp' "table"
FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_BUDGET_HISTORY"
WHERE 1=1
  AND UPDATED_AT >= dateadd(day, -90, current_date)
  AND CAMPAIGN_ID IS NOT NULL
  AND CAMPAIGN_ID IN (SELECT CAMPAIGN_ID FROM "DELPHI_ADS_DATA"."SYS"."GOOGLE_ADS_CAMPAIGN")
  AND ID IN (SELECT BUDGET_ID FROM "DELPHI_ADS_DATA"."EXP"."GOOGLE_CAMPAIGN_GROUP_PERFORMANCE_REPORT");"""
pg_check_21 = """select count(*) "count", 'dest; PG' "table" 
from (
  select id, campaign_id
  from GOOGLE.budget_history
  where 1=1
    and updated_at >= current_date - interval '90' day
  group by id, campaign_id
) a;"""

sf_check_22 = """SELECT COUNT(distinct ADSET_ID, AGE , GENDER, DATE) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_22 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, AGE , GENDER, DATE
  FROM FACEBOOK.AGE_GENDER_DAILY_REPORT
  where 1=1
    and created_at >= current_date - interval '90' day
  group by ADSET_ID, AGE , GENDER, DATE
) a;"""

sf_check_23 = """SELECT COUNT(distinct ADSET_ID, AGE , GENDER, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT_ACTIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_23 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, AGE , GENDER, DATE, INDEX
  FROM FACEBOOK.age_gender_daily_report_actions
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, AGE , GENDER, DATE, INDEX
) a;"""

sf_check_24 = """SELECT COUNT(distinct ADSET_ID, AGE , GENDER, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT_CONVERSIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_24 = """select count(*) "count", 'dest; PG' "table"
from (
  select ADSET_ID, AGE , GENDER, DATE, INDEX
  FROM FACEBOOK.age_gender_daily_report_conversions
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, AGE , GENDER, DATE, INDEX
) a;"""

sf_check_25 = """SELECT COUNT(distinct ADSET_ID, AGE , GENDER, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AGE_GENDER_DAILY_REPORT_UNIQUE_ACTIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_25 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, AGE , GENDER, DATE, INDEX
  FROM FACEBOOK.age_gender_daily_report_unique_actions
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, AGE , GENDER, DATE, INDEX
) a;"""

sf_check_26 = """SELECT COUNT(distinct ADSET_ID, COUNTRY, DATE) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_26 = """select count(*) "count", 'dest; PG' "table"
from (
  select ADSET_ID, COUNTRY, DATE
  FROM FACEBOOK.COUNTRY_DAILY_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, COUNTRY, DATE
) a;"""

sf_check_27 = """SELECT COUNT(distinct ADSET_ID, COUNTRY, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT_ACTIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_27 = """select count(*) "count", 'dest; PG' "table"
from (
  select ADSET_ID, COUNTRY, DATE, INDEX
  FROM FACEBOOK.COUNTRY_DAILY_REPORT_ACTIONS
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, COUNTRY, DATE, INDEX
) a;"""

sf_check_28 = """SELECT COUNT(distinct ADSET_ID, COUNTRY, DATE, INDEX) "count", 'scr; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT_CONVERSIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_28 = """select count(*) "count", 'dest; PG' "table"
from (
  select ADSET_ID, COUNTRY, DATE, INDEX
  FROM FACEBOOK.COUNTRY_DAILY_REPORT_CONVERSIONS
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, COUNTRY, DATE, INDEX
) a;"""

sf_check_29 = """SELECT COUNT(distinct ADSET_ID, COUNTRY, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_29 = """select count(*) "count", 'dest; PG' "table"
from (
  select ADSET_ID, COUNTRY, DATE, INDEX
  FROM FACEBOOK.COUNTRY_DAILY_REPORT_UNIQUE_ACTIONS
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, COUNTRY, DATE, INDEX
) a;"""

sf_check_30 = """SELECT COUNT(distinct ADSET_ID, PUBLISHER_PLATFORM, DATE) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_30 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, PUBLISHER_PLATFORM, DATE
  FROM FACEBOOK.PUBLISHER_PLATFORM_DAILY_REPORT
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, PUBLISHER_PLATFORM, DATE
) a;"""

sf_check_31 = """SELECT COUNT(distinct ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_31 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX
  FROM FACEBOOK.PUBLISHER_PLATFORM_DAILY_REPORT_ACTIONS
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX
) a;"""

sf_check_32 = """SELECT COUNT(distinct ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_32 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX
  FROM FACEBOOK.PUBLISHER_PLATFORM_DAILY_REPORT_CONVERSIONS
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX
) a;"""

sf_check_33 = """SELECT COUNT(distinct ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS" \
WHERE 1=1 \
  AND DATE >= dateadd(day, -90, current_date) \
;"""
pg_check_33 = """select count(*) "count", 'dest; PG' "table" 
from (
  select ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX
  FROM FACEBOOK.PUBLISHER_PLATFORM_DAILY_REPORT_UNIQUE_ACTIONS
  where 1=1
    and date >= current_date - interval '90' day
  group by ADSET_ID, PUBLISHER_PLATFORM, DATE, INDEX
) a;"""

sf_check_34 = """SELECT COUNT(*) "count", 'src; SF exp' "table" \
FROM "DELPHI_ADS_DATA"."EXP"."FACEBOOK_AD_SET_HISTORY" fash \
WHERE exists( \
    SELECT * \
    FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_CAMPAIGN" ac \
    WHERE CAMPAIGN_ID = fash.CAMPAIGN_ID \
    AND OBJECTIVE is not null \
    AND exists( \
        SELECT *  \
        FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_ACCOUNT"  \
        WHERE ACCOUNT_ID = ac.ACCOUNT_ID \
    ) \
) \
AND exists( \
    SELECT *  \
    FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_ACCOUNT"  \
    WHERE ACCOUNT_ID = fash.ACCOUNT_ID \
) \
AND exists( \
    SELECT * \
    FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_AD_SET" aas \
    WHERE AD_SET_ID = fash.ADSET_ID \
    AND exists( \
        SELECT *  \
        FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_CAMPAIGN"  \
        WHERE CAMPAIGN_ID = aas.CAMPAIGN_ID \
    ) \
    AND exists( \
        SELECT *  \
        FROM "DELPHI_ADS_DATA"."SYS"."FACEBOOK_ADS_ACCOUNT"  \
        WHERE ACCOUNT_ID = aas.ACCOUNT_ID \
    ) \
) \
AND UPDATED_AT <= (SELECT max(EXPORT_TIMESTAMP_UTC) FROM "DELPHI_ADS_DATA"."SYS"."DATA_EXPORT_LOG");  """
pg_check_34 = """SELECT COUNT(*) "count", 'dest; PG' "table"
FROM FACEBOOK.AD_SET_HISTORY;"""

if __name__ == '__main__':
    pass