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

pg_slz_status_check_1 = """select
	-- Monitoring
	distinct
	uow.report_date "REPORT_DATE",
	string_agg(distinct concat(r.report_name||' ('||l.licensor_name||') for countries: '||uow2.contexts), ', ') "MISSING_REPORTS"
	-- Debug
	/*uow.report_date,
	r.report_name,
	uow.unit_of_work_id,
	uow.completeness_status,
	uow.is_force_complete,
	content_status.content_status,
	content_status.* */
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
	left outer join (
		select
			uow.unit_of_work_id,
			string_agg(distinct upper(cs.context)||' ('||lower(cs.content_status::text)||', '||cs.content_status_id||')', ', ') "contexts"
		from unit_of_work uow
			join content_status cs on uow.unit_of_work_id = cs.unit_of_work_id
		where true
			and cs.content_status in ('ACTIVE', 'MISSING', 'FAILED', 'COMPLETE_VOLATILE')
			and cs.content_size IS NULL
			and not (cs.context = 'cy' and uow.completeness_status in ('COMPLETE', 'ACTIVE') and cs.content_status in ('MISSING', 'ACTIVE'))	-- DAE-6849; TEMPORARY: until we receive the guidance to further steps from partner dev. Check email chain "Spotify Charts - Missing Chart - Cyprus" from Karan Kapoor
			and not (cs.context = 'ru' and uow.completeness_status in ('COMPLETE', 'ACTIVE') and cs.content_status in ('MISSING', 'ACTIVE'))	-- DAE-7426; context RU is currently unavailable for Spotify charts
		group by uow.unit_of_work_id
	) uow2 on uow2.unit_of_work_id = uow.unit_of_work_id
where ds.data_source_name = 'spotify' 
    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-1
	and content_status.content_size IS NULL
	and not (content_status.context = 'cy' and uow.completeness_status in ('COMPLETE', 'ACTIVE') and content_status.content_status in ('MISSING', 'ACTIVE'))	-- DAE-6849; TEMPORARY: until we receive the guidance to further steps from partner dev. Check email chain "Spotify Charts - Missing Chart - Cyprus" from Karan Kapoor
	and not (content_status.context = 'ru' and uow.completeness_status in ('COMPLETE', 'ACTIVE') and content_status.content_status in ('MISSING', 'ACTIVE'))	-- DAE-7426; context RU is currently unavailable for Spotify charts
	and uow.unit_of_work_id not in (141889,141890,142078,142079)  -- DAE-7626: those four units were manually moved to status COMPLETE, although their contexts are not downloaded
group by uow.report_date
order by uow.report_date DESC;"""

sf_slz_raw_check_2 = """use "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY";  \
select count(*) "count" from ( \
select * from table(information_schema.copy_history(table_name=>'DIM_SPOTIFY_ARTIST', start_time=> dateadd(day, -1, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'DIM_SPOTIFY_TRACK', start_time=> dateadd(day, -1, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'FACT_SPOTIFY_CHART_TRACK', start_time=> dateadd(day, -1, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'FACT_SPOTIFY_METADATA', start_time=> dateadd(day, -1, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'LINK_SPOTIFY_TRACK_ARTIST', start_time=> dateadd(day, -1, current_timestamp()))) \
);"""

sf_slz_raw_check_3 = """use "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY";  \
select count(*) "count" from ( \
select * from table(information_schema.copy_history(table_name=>'DIM_SPOTIFY_ARTIST', start_time=> dateadd(day, -5, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'DIM_SPOTIFY_TRACK', start_time=> dateadd(day, -5, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'FACT_SPOTIFY_CHART_TRACK', start_time=> dateadd(day, -5, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'FACT_SPOTIFY_METADATA', start_time=> dateadd(day, -5, current_timestamp()))) \
union \
select * from table(information_schema.copy_history(table_name=>'LINK_SPOTIFY_TRACK_ARTIST', start_time=> dateadd(day, -5, current_timestamp()))) \
) where status != 'LOADED'"""

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

sf_raw_sys_check_5 = """select count(*) "count" \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA"  \
where status not in ('SKIPPED','COMPLETE')  \
and date -1 < current_date() and datediff('day' , date, CREATED_AT )  < 7 \
union \
select count(*) "count" \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA"  \
where date -1 > current_date();"""

sf_raw_sys_check_6 = """select count(*) "count" \
from ( \
select date,get(split(JOB_ID, '-'), 3)::text \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA" \
where date >= current_date() -14 \
and date < current_date()  \
and status = 'COMPLETE'   \
group by date, get(split(JOB_ID, '-'), 3)::text );"""

sf_raw_main_check_7 = """select count(distinct SPOTIFY_ARTIST_ID) "count", 'src; SF raw' "table" \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."DIM_SPOTIFY_ARTIST"  \
where job_id in ( \
select job_id  \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA" \
where status in ('COMPLETE')) \
union \
select count(distinct SPOTIFY_ARTIST_ID) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."DIM_SPOTIFY_ARTIST";"""

sf_raw_main_check_8 = """select count(distinct CHART_ID) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK" \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."DIM_SPOTIFY_CHART";"""

sf_raw_main_check_9 = """select count(distinct SPOTIFY_TRACK_ID ,SPOTIFY_COUNTRY_ID) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."DIM_SPOTIFY_TRACK"   \
where job_id in ( \
select job_id  \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA" \
where status in ('COMPLETE')) \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."DIM_SPOTIFY_TRACK";"""

sf_raw_main_check_10 = """select count(distinct CHART_DATE, CHART_ID, CURRENT_POSITION) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK"   \
where job_id in ( \
select job_id  \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA"  \
where status in ('COMPLETE')) \
union \
select count(distinct CHART_DATE, CHART_ID, CURRENT_POSITION) "count",'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK_PUBLIC";"""

sf_main_main_check_11 = """select count(distinct CHART_DATE, CHART_ID, CURRENT_POSITION) "count", 'src; SF raw' "table"  \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK"   \
where job_id in ( \
select job_id  \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA"  \
where status in ('COMPLETE')) \
union \
select count(*) "count",'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK_PUBLIC"; """

sf_main_main_check_12 = """select count(distinct TRACK_ID, CHART_ID ) "count", 'src; SF raw' "table" \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK" \
where job_id in ( \
select job_id \
from "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA" \
where status in ('COMPLETE') \
and  (TRACK_ID, CHART_ID ) != ('1ji6PoTBfVXqycvsXdgZgp','regional_daily_tr')) \
union \
select count(distinct spotify_chart_id , spotify_track_id) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK_LIFETIME";
"""

sf_raw_main_check_13 = """select count(*) "count", 'src; SF raw' "table" \
from ( \
select distinct SPOTIFY_TRACK_ID, SPOTIFY_COUNTRY_ID, RANK \
from "DELPHI_PUBLIC_DATA_RAW"."CHARTS_SPOTIFY"."LINK_SPOTIFY_TRACK_ARTIST" A \
join "DELPHI_PUBLIC_DATA_MAIN"."SYS"."FACT_SPOTIFY_METADATA" V on A.JOB_ID = V.JOB_ID \
where STATUS = 'COMPLETE' \
qualify dense_rank() over (partition by SPOTIFY_TRACK_ID,SPOTIFY_COUNTRY_ID order by split_part(A.JOB_ID, '_', -1) desc ,date desc  ,A.JOB_ID desc ) = 1) \
union \
select count(*) "count", 'dest; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."LINK_SPOTIFY_TRACK_ARTIST";"""

sf_main_main_check_14 = """select count(*) "count", 'src; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK"_PUBLIC \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK";"""

sf_main_main_check_15 = """select count(distinct SPOTIFY_CHART_ID ,SPOTIFY_TRACK_ID, coalesce(ISRC, 'na')) "count", 'src; SF main' "table" \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK_LIFETIME" \
union \
select count(*) "count", 'dest; SF main' "table"  \
from "DELPHI_PUBLIC_DATA_MAIN"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK_LIFETIME";"""

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

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

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

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

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

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

pg_delphi_mian_status_check_22 = """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_spotify'
AND new_status = 'IMPORT_FAILED'
AND uow_meta.audit_log.created_at >= current_date - 2;"""

sf_dts_check_23 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_SPOTIFY"."DIM_SPOTIFY_ARTIST";"""
pg_mian_dts_check_23 = """select count(*) "count", 'dest; PG' "table"
from spotify.DIM_SPOTIFY_ARTIST;"""

sf_dts_check_24 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_SPOTIFY"."DIM_SPOTIFY_CHART";"""
pg_mian_dts_check_24 = """select count(*) "count", 'dest; PG' "table"
from spotify.DIM_SPOTIFY_CHART;"""

sf_dts_check_25 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_SPOTIFY"."DIM_SPOTIFY_TRACK";"""
pg_mian_dts_check_25 = """select count(*) "count", 'dest; PG' "table"
from spotify.DIM_SPOTIFY_TRACK;"""

sf_dts_check_26 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK";"""
pg_mian_dts_check_26 = """select count(*) "count", 'dest; PG' "table"
from spotify.FACT_SPOTIFY_CHART_TRACK;"""

sf_dts_check_27 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_SPOTIFY"."FACT_SPOTIFY_CHART_TRACK_LIFETIME";"""
pg_mian_dts_check_27 = """select count(*) "count", 'dest; PG' "table"
from spotify.FACT_SPOTIFY_CHART_TRACK_LIFETIME;"""

sf_dts_check_28 = """select count(*) "count", 'src; SF etl' "table" \
from "DELPHI_PUBLIC_DATA_ETL"."CHARTS_SPOTIFY"."LINK_SPOTIFY_TRACK_ARTIST"; """
pg_mian_dts_check_28 = """select count(*) "count", 'dest; PG' "table"
from spotify.LINK_SPOTIFY_TRACK_ARTIST;"""

pg_markets_days_check_29="""SELECT count(*)
FROM (
	SELECT 
		generate_series(current_date - 14, current_date - 2, INTERVAL '1 day')::date AS date_input,
		spotify_chart_id || generate_series(current_date - 14, current_date - 2, INTERVAL '1 day')::date AS all_possible_chart_dates
	FROM spotify.dim_spotify_chart
	WHERE spotify_country_id NOT IN ('ad', 'ru')
		and not ("type"  = 'viral' and breakdown = 'weekly')
) a
WHERE all_possible_chart_dates NOT IN (
	SELECT DISTINCT 
		spotify_chart_id || chart_date
	FROM spotify.fact_spotify_chart_track
	WHERE chart_date >= current_date - 14
)
	AND (
		all_possible_chart_dates LIKE '%daily%' 
		OR
		(
			all_possible_chart_dates LIKE '%weekly%' 
		 	AND 
			extract(DOW FROM date_input) = 4
		)
)"""

sf_new_markets_check_30="""select count(*) "count" from ( \
select distinct(lower(COUNTRY)) from  DS_CHARTMETRIC.RAW_DATA.SPOTIFY_CHART  \
where timestp >= current_date() - 30 and  COUNTRY != 'GLOBAL' \
minus  \
select distinct(SPOTIFY_COUNTRY_ID) from DELPHI_PUBLIC_DATA_MAIN.CHARTS_SPOTIFY.DIM_SPOTIFY_CHART ) """

if __name__ == '__main__':
    pass