### snowflake cursor doesn't support string sseparation, thus this var is adjusted
from datetime import date, timedelta

pg_check_1 = """select
        string_agg(distinct '(bqr.query_id='||bq.query_id||' and bqr.query_version='||bqq.latest_query_version||')', ' or ')
from brandwatch_query bq
        join (
                select query_id, max(query_version) "latest_query_version"
                from brandwatch_query
                group by query_id
        ) bqq on bqq.query_id = bq.query_id and bqq.latest_query_version = bq.query_version
where true
  and bq.is_active = true
  and bq.is_valid = true
;"""
pg_check_2 = """select
        current_date "REPORT_DATE"
        ,u.report_date "uow_report_date"
        ,u.unit_of_work_id
        ,u.unit_of_work_code
        ,u.completeness_status
from unit_of_work u
where
        (
                u.report_date = current_date-2
                and u.unit_of_work_code like 'brandwatch%backfill%'
                and u.created_at >= current_date
                and u.completeness_status = 'COMPLETE'
        )
        or
        (
                u.report_date = current_date-1
                and u.unit_of_work_code like 'brandwatch%ingestion%'
                and u.created_at >= current_date
        )
        or
        (
                u.report_date = current_date-0
                and u.unit_of_work_code like 'brandwatch%queries%'
                and u.created_at >= current_date
                and u.completeness_status = 'COMPLETE'
        )
order by u.report_date desc
;"""

pg_check_3 = """select 
    u.report_date "REPORT_DATE"
    ,u.unit_of_work_id
from unit_of_work u
where true
    and u.unit_of_work_code like 'brandwatch%artist%queries%'
    and u.report_date >= current_date-14
    and u.completeness_status not in ('COMPLETE', 'CANCELLED')
order by u.report_date desc
;"""

today_minus_2 = str(date.today() - timedelta(2))
with open('results/' + today_minus_2 + '.txt') as f:
    today_minus_2_result = f.read()
today_minus_3 = str(date.today() - timedelta(3))
with open('results/' + today_minus_3 + '.txt') as f:
    today_minus_3_result = f.read()
today_minus_4 = str(date.today() - timedelta(4))
with open('results/' + today_minus_4 + '.txt') as f:
    today_minus_4_result = f.read()
today_minus_5 = str(date.today() - timedelta(5))
with open('results/' + today_minus_5 + '.txt') as f:
    today_minus_5_result = f.read()
today_minus_6 = str(date.today() - timedelta(6))
with open('results/' + today_minus_6 + '.txt') as f:
    today_minus_6_result = f.read()
today_minus_7 = str(date.today() - timedelta(7))
with open('results/' + today_minus_7 + '.txt') as f:
    today_minus_7_result = f.read()
today_minus_8 = str(date.today() - timedelta(8))
with open('results/' + today_minus_8 + '.txt') as f:
    today_minus_8_result = f.read()
today_minus_9 = str(date.today() - timedelta(9))
with open('results/' + today_minus_9 + '.txt') as f:
    today_minus_9_result = f.read()
today_minus_10 = str(date.today() - timedelta(10))
with open('results/' + today_minus_10 + '.txt') as f:
    today_minus_10_result = f.read()
today_minus_11 = str(date.today() - timedelta(11))
with open('results/' + today_minus_11 + '.txt') as f:
    today_minus_11_result = f.read()
today_minus_12 = str(date.today() - timedelta(12))
with open('results/' + today_minus_12 + '.txt') as f:
    today_minus_12_result = f.read()
today_minus_13 = str(date.today() - timedelta(13))
with open('results/' + today_minus_13 + '.txt') as f:
    today_minus_13_result = f.read()
today_minus_14 = str(date.today() - timedelta(14))
with open('results/' + today_minus_14 + '.txt') as f:
    today_minus_14_result = f.read()

pg_check_4 = f"""select
        u.report_date "REPORT_DATE"
        ,u.unit_of_work_id "UNIT_OF_WORK_ID"
        ,cs.content_status_id "CONTENT_STATUS_ID"
        ,string_agg(distinct bqr.run_id||'-'||bqr.status,', ') "RUN_IDS"
from unit_of_work u
        left outer join brandwatch_query_run bqr on bqr.unit_of_work_id = u.unit_of_work_id
        left outer join brandwatch_query_log bql on bql.run_id = bqr.run_id
        left outer join brandwatch_query bq on bq.query_id = bqr.query_id and bq.query_version = bqr.query_version
        left outer join content_status cs on cs.unit_of_work_id = u.unit_of_work_id
where true
        and u.unit_of_work_code like 'brandwatch%ingestion%'
        and (
                bqr.status is null
                or bqr.status not in (
                        'COMPLETED'
                        ,'CANCELLED'
                        ,'MANUALLY_CANCELLED'
                        ,'WAITING_STUCKED'
                )
        ) and (
                (
                        u.report_date = current_date-14
                        and
                        ( {today_minus_14_result} )
                ) or (
                        u.report_date = current_date-13
                        and
                        ( {today_minus_13_result} )
                ) or (
                        u.report_date = current_date-12
                        and
                        ( {today_minus_12_result} )
                ) or (
                        u.report_date = current_date-11
                        and
                        ( {today_minus_11_result} )
                ) or (
                        u.report_date = current_date-10
                        and
                        ( {today_minus_10_result} )
                ) or (
                        u.report_date = current_date-9
                        and
                        ( {today_minus_9_result} )
                ) or (
                        u.report_date = current_date-8
                        and
                        ( {today_minus_8_result} )
                ) or (
                        u.report_date = current_date-7
                        and
                        ( {today_minus_7_result} )
                ) or (
                        u.report_date = current_date-6
                        and
                        ( {today_minus_6_result} )
                ) or (
                        u.report_date = current_date-5
                        and
                        ( {today_minus_5_result} )
                ) or (
                        u.report_date = current_date-4
                        and
                        ( {today_minus_4_result} )
                ) or (
                        u.report_date = current_date-3
                        and
                        ( {today_minus_3_result} )
                ) or (
                        u.report_date = current_date-2
                        and
                        ( {today_minus_2_result} )
                )
        )
group by u.unit_of_work_id, u.report_date, cs.content_status_id
order by u.report_date desc
;"""

pg_check_5 = """select
	u.report_date "REPORT_DATE"
	,u.unit_of_work_id
	,u.completeness_status
	,count(distinct bqr.run_id) "runs count"
	,string_agg(distinct bqr.run_id||'-'||bqr.status, ', ')
from unit_of_work u
	left outer join brandwatch_query_run bqr on bqr.unit_of_work_id = u.unit_of_work_id
	left outer join brandwatch_query_log bql on bql.run_id = bqr.run_id
	left outer join brandwatch_query bq on bq.query_id = bqr.query_id and bq.query_version = bqr.query_version
where true
	and u.unit_of_work_code like 'brandwatch%ingestion%'
	and bqr.status = 'WAITING_STUCKED'
group by u.unit_of_work_id, u.report_date, bq.query_id, bq.query_version
having count(distinct bqr.run_id) >= 3
order by u.report_date desc
;"""

pg_check_6 = """select 
	u.report_date "REPORT_DATE"
	,case 
		when u.completeness_status = 'ACTIVE' and now() >= u.created_at + interval '10 hour' then count(distinct bql.log_id)
		when u.completeness_status = 'ACTIVE' and now() < u.created_at + interval '10 hour' then 99999999
		else -1
	end as "LOGS_CREATED"
from unit_of_work u
	left outer join brandwatch_query_run bqr on bqr.unit_of_work_id = u.unit_of_work_id
	left outer join brandwatch_query bq on bq.query_id = bqr.query_id and bq.query_version = bqr.query_version
	left outer join (
		select log_id, run_id
		from brandwatch_query_log
		where created_at > now() - interval '1 hour'
	) bql on bql.run_id = bqr.run_id
where true
	and u.unit_of_work_code like 'brandwatch%ingestion%'
    and u.unit_of_work_type = 'DAILY'
	and u.completeness_status not in ('COMPLETE')
group by u.report_date, u.unit_of_work_id, u.unit_of_work_code
order by u.report_date desc
;"""

pg_check_6_1 = """select
	u.report_date "REPORT_DATE"
	--,u.unit_of_work_id
	--,u.unit_of_work_code
	,case
		when u.completeness_status = 'ACTIVE' and now() >= u.created_at + interval '10 hour' then count(distinct bql.log_id)
		when u.completeness_status = 'ACTIVE' and now() < u.created_at + interval '10 hour' then 99999999	-- daily ingestion only starts at 10am NYC
		else -1
	end as "LOGS_CREATED"
from unit_of_work u
	left outer join brandwatch_query_run bqr on bqr.unit_of_work_id = u.unit_of_work_id
	left outer join brandwatch_query bq on bq.query_id = bqr.query_id and bq.query_version = bqr.query_version
	left outer join (
		select log_id, run_id, response_status
		from brandwatch_query_log
		where created_at > now() - interval '1 hour'	-- only logs created in the last 1 hour
	) bql on bql.run_id = bqr.run_id
where true
	and u.unit_of_work_code like 'brandwatch%ingestion%'
    and bql.response_status not in (200, 201, 202)
group by u.report_date, u.unit_of_work_id, u.unit_of_work_code
order by u.report_date desc
;"""

pg_check_7 = """select 
	u.report_date "REPORT_DATE"
	--,u.unit_of_work_id
	--,u.unit_of_work_code
	,case 
		when (select u.unit_of_work_id from unit_of_work u join content_status cs on cs.unit_of_work_id = u.unit_of_work_id where u.unit_of_work_code like 'brandwatch%ingestion%' and u.unit_of_work_type in ('DAILY') and u.completeness_status not in ('COMPLETE')) 
			is not null 
			then 88888888 -- do not execute backfill if active query runs are in progress
		when (select u.unit_of_work_id from unit_of_work u join content_status cs on cs.unit_of_work_id = u.unit_of_work_id where u.unit_of_work_code like 'brandwatch%ingestion%' and u.unit_of_work_type in ('DAILY') and u.completeness_status not in ('COMPLETE')) 
				is null 
			and u.completeness_status = 'ACTIVE' 
			and (select a.completeness_status from unit_of_work a where a.unit_of_work_code like 'brandwatch%ingestion%' and a.report_date = u.report_date + interval '1 day') 
				not in ('COMPLETE') 
			then 77777777 -- Backfill is executed in the direct order from newest to oldest. Ignore 0 logs in older report_dates
		when (select u.unit_of_work_id from unit_of_work u join content_status cs on cs.unit_of_work_id = u.unit_of_work_id where u.unit_of_work_code like 'brandwatch%ingestion%' and u.unit_of_work_type in ('DAILY') and u.completeness_status not in ('COMPLETE'))  
				is null 
			and u.completeness_status = 'ACTIVE' 
			and now() < current_date + interval '2 hour' 
			then 99999999 -- new backfill ingestion only starts at 1:30am NYC
		when (select u.unit_of_work_id from unit_of_work u join content_status cs on cs.unit_of_work_id = u.unit_of_work_id where u.unit_of_work_code like 'brandwatch%ingestion%' and u.unit_of_work_type in ('DAILY') and u.completeness_status not in ('COMPLETE')) 
 				is null 
			and u.completeness_status = 'ACTIVE' 
			and now() >= current_date + interval '2 hour' 
			and (select count(distinct brandwatch_query_run.run_id) from brandwatch_query_run where brandwatch_query_run.retrieve_at between now() - interval '1 hour' and u.last_updated_at) = 0
			then 11111111 + count(distinct bql.log_id)	-- there is a case when active unit of work can not download anything this hour, because it's waiting for all his query_runs' retrieve_at
		when (select u.unit_of_work_id from unit_of_work u join content_status cs on cs.unit_of_work_id = u.unit_of_work_id where u.unit_of_work_code like 'brandwatch%ingestion%' and u.unit_of_work_type in ('DAILY') and u.completeness_status not in ('COMPLETE')) 
 				is null 
			and u.completeness_status = 'ACTIVE' 
			and now() >= current_date + interval '2 hour' 
			then count(distinct bql.log_id)
		when u.completeness_status = 'COMPLETE' 
			and u.last_updated_at > now() - interval '1 hour' 
			then count(distinct bql.log_id)
		else -1
	end as "LOGS_CREATED"
from unit_of_work u
	left outer join brandwatch_query_run bqr on bqr.unit_of_work_id = u.unit_of_work_id
	left outer join brandwatch_query bq on bq.query_id = bqr.query_id and bq.query_version = bqr.query_version
	left outer join (
		select log_id, run_id
		from brandwatch_query_log
		where created_at > now() - interval '1 hour 10 minute'	-- only logs created in the last 1 hour; emperically we see that often enough log is created at xx:59, unit is updated at xx+1:01. As a result, monitoring's run at xx+2:00 finds the unit, but doesn't find the last log
	) bql on bql.run_id = bqr.run_id
where true
	and u.unit_of_work_code like 'brandwatch%ingestion%'
    and u.unit_of_work_type = 'BACKFILL'
	and (
		u.last_updated_at > now() - interval '1 hour'	-- updated in the last 1 hour
		or
		u.completeness_status not in ('COMPLETE')		-- waiting for execution to start
	)
	and u.report_date not in ('2022-01-01', '2022-01-02', '2022-01-03', '2022-01-04', '2022-01-05', '2022-01-06') -- These dates in DAE-10846 crash the monitoring, as there are >1 unit of work per report_date
group by u.report_date, u.unit_of_work_id, u.unit_of_work_code
order by u.report_date desc
;"""

sf_check_8 = """select * \
from DELPHI_EXPLORATION.SYS.COPY_HISTORY_FILE_STATUS \
where true \
  and DSP = 'BRANDWATCH' \
  and STATUS <> 'LOADED' \
  and CREATED_AT between dateadd(d, -1, current_timestamp()) and dateadd(h, -1, current_timestamp()) \
;"""

pg_check_9 = """select count(*) "count", 'src; PG SLZ' "table"
from brandwatch_query
;"""
sf_check_9 = """select count(*) "count", 'dest; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_META \
where true \
    and report_date = current_date();"""

pg_check_10 = """select count(*) "count", 'src; PG SLZ' "table"
from brandwatch_query_run
where true
    and brandwatch_result_id is not null
	and status in ( 'COMPLETED', 'CANCELLED' ) 
	--and report_date >= '2022-12-01'
;"""
sf_check_10 = """select count(*) "count", 'dest; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_ANALYSIS \
where true;"""

pg_check_11 = """select count(*) "count", 'src; PG SLZ' "table"
from brandwatch_query_log
where true
    and updated_at <= date_trunc('hour', current_timestamp - interval '1 hour')
;"""
sf_check_11 = """select count(*) "count", 'dest; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_LOG \
where true \
    and updated_at <= date_trunc('HOUR', dateadd(hour, -1, current_timestamp())) \
;"""

pg_check_12 = """select 
	count(distinct run_id) "count", 'src; PG SLZ' "table"
from brandwatch_query_run
where true
	and created_at >= current_date - interval '1 day' 
	and created_at < now() - interval '1 hour'
	and updated_at < now() - interval '1 hour'
;"""
sf_check_12 = """with bqr as ( \
  select * \
  from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_RUN \
  qualify row_number() over (partition by run_id order by updated_at desc nulls last) = 1  \
)  \
select count(*) "count", 'dest; SF raw' "table" \
from bqr \
where true \
    and created_at >= current_date - interval '1 day'  \
    and created_at < current_timestamp - interval '1 hour' \
    and updated_at < current_timestamp - interval '1 hour' \
;"""

sf_check_13 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_META \
where true \
    and report_date = current_date() \
union \
select count(*) "count", 'dest; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META \
where true \
    and query_id||query_version in ( \
        select query_id||query_version \
        from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_META \
        where true \
            and report_date = current_date() \
    ) \
;"""

sf_check_14 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_ANALYSIS \
where true \
union \
select count(distinct analysis_id) "count", 'desc; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_EMOTION \
where true \
;"""

sf_check_15 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_ANALYSIS raw, lateral flatten(input => BRANDWATCH_DATA:analysis:emotion) \
where true \
union \
select count(*) "count", 'desc; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_EMOTION \
where true \
;"""

sf_check_16 = """select count(*) "count", 'src; SF raw' "table"  \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_ANALYSIS raw, lateral flatten(input => BRANDWATCH_DATA:analysis:location:data) \
where true \
union \
select count(*) "count", 'dest; SF main' "table"  \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_LOCATION \
where true \
;"""

sf_check_17 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_ANALYSIS raw, lateral flatten(input => BRANDWATCH_DATA:analysis:volume) \
where true \
union \
select count(*) "count", 'desc; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_PLATFORM \
where true \
;"""

sf_check_18 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_ANALYSIS raw, lateral flatten(input => BRANDWATCH_DATA:analysis:sentiment) \
where true \
union \
select count(*) "count", 'desc; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_SENTIMENT \
where true \
;"""

sf_check_19 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_LOG \
where true \
union \
select count(*) "count", 'dest; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_LOG \
where true \
;"""

sf_check_20 = """select count(distinct RUN_ID) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.RAW.BRANDWATCH_QUERY_RUN \
where true \
union \
select count(*) "count", 'dest; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_RUN \
where true \
;"""

sf_check_21 = """select count(*) "count", 'src; SF raw' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META \
where true \
union \
select count(*) "count", 'dest; SF main' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_META \
where true \
    and query_id||query_version in ( \
        select query_id||query_version \
        from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META \
        where true \
    ) \
;"""

sf_check_22 = """select count(be.*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_EMOTION be \
    join DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
union \
select count(be.*) "count", 'desc; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_EMOTION be \
    join DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
;"""

sf_check_23 = """select count(be.*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_EMOTION be \
    join DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
union \
select count(be.*) "count", 'desc; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_EMOTION be \
    join DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
;"""

sf_check_24 = """select count(*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_LOCATION be \
    join DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
union \
select count(*) "count", 'desc; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_LOCATION be \
    join DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true;"""

sf_check_25 = """select count(*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_PLATFORM be \
    join DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
union \
select count(*) "count", 'desc; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_PLATFORM be \
    join DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true;"""

sf_check_26 = """select count(*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_SENTIMENT be \
    join DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true \
union \
select count(*) "count", 'desc; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_SENTIMENT be \
    join DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_META bqm on bqm.query_id = be.query_id and bqm.query_version = be.query_version \
where true \
    and bqm.is_active = true;"""

sf_check_27 = """select count(*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_LOG \
where true \
    and updated_at <= date_trunc('HOUR', dateadd(hour, -1, current_timestamp()))  \
union \
select count(*) "count", 'dest; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_LOG \
where true \
    and updated_at <= date_trunc('HOUR', dateadd(hour, -1, current_timestamp()))  \
;"""

sf_check_28 = """select count(*) "count", 'src; SF main' "table" \
from DELPHI_EXPLORATION.MAIN.BRANDWATCH_QUERY_RUN \
where true \
    and updated_at <= date_trunc('HOUR', dateadd(hour, -1, current_timestamp())) \
union \
select count(*) "count", 'dest; SF exp' "table" \
from DELPHI_EXPLORATION.EXP.V_BRANDWATCH_QUERY_RUN \
where true \
    and updated_at <= date_trunc('HOUR', dateadd(hour, -1, current_timestamp())) \
;"""

if __name__ == '__main__':
    pass