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

pg_slz_status_check_1 = """select
	distinct
	uow.report_date "REPORT_DATE",
	string_agg(distinct concat(r.report_name||' ('||l.licensor_name||')'), ',') "MISSING_REPORTS"
from content_status 
  join unit_of_work uow on uow.unit_of_work_id = content_status.unit_of_work_id
  join report r on r.report_id = uow.report_id
  join licensor l on l.licensor_id = uow.licensor_id
  join data_source ds ON ds.data_source_id = r.data_source_id
where ds.data_source_name = 'apple' 
  and r.report_name LIKE 'charts%' 
  and uow.completeness_status != 'CANCELLED'
  and content_status.content_status in ('ACTIVE', 'MISSING', 'FAILED', 'COMPLETE_VOLATILE')
  and uow.report_date > current_date-14
  and uow.report_date < current_date
  and content_status.content_size IS NULL
group by uow.report_date
order by uow.report_date DESC;"""


sf_slz_raw_check_2 = """use "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE";  \
select count(*) from ( \
 select * from table(information_schema.copy_history(table_name=>'dim_apple_music_artist', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'dim_apple_music_track', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'dim_apple_music_track_preview', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'fact_apple_music_chart_track', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'fact_apple_music_metadata', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'link_apple_music_track_artist', start_time=> dateadd(day, -1, current_timestamp()))) \
);"""

sf_slz_raw_check_3 = """use "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE";  \
select count(*) from ( \
 select * from table(information_schema.copy_history(table_name=>'dim_apple_music_artist', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'dim_apple_music_track', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'dim_apple_music_track_preview', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'fact_apple_music_chart_track', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'fact_apple_music_metadata', start_time=> dateadd(day, -1, current_timestamp()))) \
 union \
 select * from table(information_schema.copy_history(table_name=>'link_apple_music_track_artist', start_time=> dateadd(day, -1, current_timestamp()))) \
) where status != 'LOADED';"""

sf_raw_main_check_4 = """select count(*) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."FACT_APPLE_MUSIC_METADATA"  \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA";"""

sf_raw_sys_check_5 = """select count(*) "count" \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
where status not in ('SKIPPED','COMPLETE')  \
and chart_date -1 < current_date()  \
union \
select count(*) "count" \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
where chart_date -1 > current_date();"""

sf_raw_main_check_6 = """select count(distinct apple_music_artist_id ,apple_music_country_id ) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."DIM_APPLE_MUSIC_ARTIST"  \
where job_id in ( \
  select job_id  \
  from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
  where status = 'COMPLETE' \
)  \
union \
select count(distinct apple_music_artist_id ,apple_music_country_id ) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_ARTIST";"""

sf_raw_main_check_7 = """select count(distinct apple_music_country_id ) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK"  \
where job_id in ( \
  select job_id  \
  from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
  where status = 'COMPLETE' \
) \
union \
select count (*)"count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_CHART";"""

sf_raw_main_check_8 = """select count(distinct apple_music_track_id ) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK"  \
where job_id in ( \
  select job_id  \
  from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
  where status = 'COMPLETE' \
) \
union \
select count (*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK";"""

sf_raw_main_check_9 = """select count(distinct apple_music_track_id,apple_music_country_id ) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK"  \
where job_id in ( \
  select job_id  \
  from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
  where status = 'COMPLETE' \
)  \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_LOCALIZED";"""

sf_raw_main_check_10 = """select count(distinct apple_music_track_id,apple_music_country_id ) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_PREVIEW"  \
where job_id in ( \
  select job_id  \
  from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA"  \
  where status = 'COMPLETE' \
) \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_PREVIEW";"""

sf_raw_main_check_11 = """select count(*) "count", 'src; SF raw' "table" \
from ( \
  select distinct APPLE_MUSIC_TRACK_ID, APPLE_MUSIC_ARTIST_ID, RANK \
  from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."LINK_APPLE_MUSIC_TRACK_ARTIST" A \
  join "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA" V on A.JOB_ID = V.JOB_ID \
  where STATUS = 'COMPLETE' \
  qualify dense_rank() over (partition by APPLE_MUSIC_TRACK_ID order by CHART_DATE desc ,right(a.job_id, 17 ) desc ) = 1 \
) \
union \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."LINK_APPLE_MUSIC_TRACK_ARTIST";"""

sf_raw_main_check_12 = """select count(*) "count", 'src; SF raw' "table" \
from ( \
 select distinct apple_music_country_id,a.chart_date,current_position  \
 from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK" A \
 join "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_APPLE_MUSIC_METADATA" V on A.JOB_ID = V.JOB_ID \
 where STATUS = 'COMPLETE' \
 qualify dense_rank() over (partition by apple_music_country_id,a.chart_date order by a.CHART_DATE desc ,right(a.job_id, 17 ) desc ) = 1  \
) \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_PUBLIC" ; """

sf_main_main_check_13 = """select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK"  \
union  \
select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_PUBLIC"; """

sf_main_main_check_14 = """select count(*) "count", 'src; SF main'    from (( \
select distinct apple_music_chart_id,apple_music_track_id \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE".FACT_APPLE_MUSIC_CHART_TRACK  \
minus \
select distinct apple_music_chart_id,apple_music_track_id \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_LIFETIME" ) \
union ( \
select distinct apple_music_chart_id, isrc \
from  "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE".FACT_APPLE_MUSIC_CHART_TRACK  \
minus  \
select distinct apple_music_chart_id, isrc \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_LIFETIME" ) )  \
union  \
select 0 "count" , 'expected; SF main'; """

sf_main_etl_check_15 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_ARTIST"  \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_ARTIST"; """

sf_main_etl_check_16 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_CHART" \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_CHART"; """

sf_mian_etl_check_17 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK"  \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK";"""

sf_mian_etl_check_18 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_LOCALIZED"  \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_LOCALIZED";"""

sf_mian_etl_check_19 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_PREVIEW"  \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_PREVIEW";"""

sf_mian_etl_check_20 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK"  \
union  \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK";"""

sf_mian_etl_check_21 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_LIFETIME" \
union \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_LIFETIME";"""

sf_mian_etl_check_22 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_APPLE"."LINK_APPLE_MUSIC_TRACK_ARTIST" \
union \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."LINK_APPLE_MUSIC_TRACK_ARTIST";"""

pg_delphi_mian_status_check_23 = """SELECT uow_meta.unit_of_work.uow_id,uow_meta.unit_of_work.status,uow_meta.audit_log.extra_data,uow_meta.audit_log.new_status
FROM uow_meta.unit_of_work
INNER JOIN uow_meta.audit_log
ON uow_meta.unit_of_work.uow_id = uow_meta.audit_log.uow_id
WHERE status = 'IMPORT_FAILED'
AND project = 'pd_charts_apple_music'
AND new_status = 'IMPORT_FAILED'
AND uow_meta.audit_log.created_at >= current_date - 2;"""

sf_dts_check_24 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_ARTIST";"""
pg_mian_dts_check_24 = """select count(*) "count", 'dest; PG' "table" 
from apple.dim_apple_music_artist_localized;"""

sf_dts_check_25 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK_LOCALIZED";"""
pg_mian_dts_check_25 = """select count(*) "count", 'dest; PG' "table" 
from apple.dim_apple_music_track_localized;"""

sf_dts_check_26 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK";"""
pg_mian_dts_check_26 = """select count(*) "count", 'dest; PG' "table" 
from apple.fact_apple_music_chart_track;"""

sf_dts_check_27 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."FACT_APPLE_MUSIC_CHART_TRACK_LIFETIME";"""
pg_mian_dts_check_27 = """select count(*) "count", 'dest; PG' "table" 
from apple.fact_apple_music_chart_track_lifetime;"""

sf_dts_check_28 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_CHART";"""
pg_mian_dts_check_28 = """select count(*) "count", 'dest; PG' "table" 
from apple.dim_apple_music_chart;"""

sf_dts_check_29 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."LINK_APPLE_MUSIC_TRACK_ARTIST";"""
pg_mian_dts_check_29 = """select count(*) "count", 'dest; PG main' "table" 
from apple.link_apple_music_track_artist;"""

sf_dts_check_30 = """select count(distinct apple_music_artist_id) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_ARTIST";"""
pg_mian_dts_check_30 = """select count(*) "count", 'dest; PG' "table" 
from apple.dim_apple_music_artist;"""

sf_dts_check_31 = """select count(*) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_APPLE"."DIM_APPLE_MUSIC_TRACK";"""
pg_mian_dts_check_31 = """select count(*) "count", 'dest; PG main' "table" 
from apple.dim_apple_music_track; """

pg_markets_days_check_32="""SELECT count(*) "count"
FROM (
SELECT generate_series(current_date - 16, current_date -1 , INTERVAL '1 day')::date AS date_input,
apple_music_chart_id ||
generate_series(current_date - 16, current_date -1 , INTERVAL '1 day')::date AS all_possible_chart_dates
FROM apple.dim_apple_music_chart damc 
) a
WHERE all_possible_chart_dates NOT IN (SELECT DISTINCT apple_music_chart_id || chart_date
FROM apple.fact_apple_music_chart_track famct 
WHERE chart_date >= current_date - 16)"""

sf_new_markets_check_33="""select count(*) "count" from ( \
select distinct(trim(lower(COUNTRY))) from DS_CHARTMETRIC.RAW_DATA.APPLE_MUSIC_DAILY_TOP_CHART   \
where timestp >= current_date() - 30 and  COUNTRY != 'GLOBAL' \
minus  \
select distinct(APPLE_MUSIC_COUNTRY_ID) from DELPHI_PUBLIC_DATA_MAIN.CHARTS_APPLE.DIM_APPLE_MUSIC_CHART ) """

if __name__ == '__main__':
    pass