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

sf_check_1 = """with \
    cte_mcc as ( \
        select count(*) as MCC_COUNT \
        from DELPHI_EXPLORATION.SYS.MIN_COMPLETE_CONFIG \
        where DSP = 'SPOTIFY' \
          and REPORT_NAME = 'STREAMS' \
    ), \
    cte_ifs as ( \
        select \
            REPORT_DATE, count(distinct LICENSOR, CONTEXT) as CONTEXT_CNT \
        from DELPHI_EXPLORATION.SYS.INBOUND_FILE_STATUS \
        where DSP = 'SPOTIFY' \
          and REPORT_NAME = 'STREAMS' \
          and REPORT_DATE between current_date() - 35 and current_date() - 5 \
          and STATUS in ('LOADED', 'L_OUTDATED') \
          and PROCESSING_COMPLETED = 1 \
        group by REPORT_DATE \
        having count(distinct LICENSOR, CONTEXT) > (select MCC_COUNT from cte_mcc) \
    ) \
select cast((min(CONTEXT_CNT) + (select min(MCC_COUNT) from cte_mcc)) / 2 as int) as CONTEXT_COUNT_LAST_MONTH_THRESHOLD \
from cte_ifs;"""

### sf_check_2 = Query #2 is in main_daily.py script

sf_check_3 = """select DATE_ENABLED \
from DELPHI_EXPLORATION.SYS.DATA_ENABLEMENT_CONTROL \
where DSP = 'SPOTIFY' \
  and REPORT_NAME = 'STREAMS' \
  and DATE_ENABLED between current_date() - 4 and current_date() - 2 \
group by DATE_ENABLED \
having count(distinct LICENSOR) >= 4 \
order by DATE_ENABLED desc;"""

sf_check_4 = """select REPORT_DATE, PROCESSING_COMPLETED, count(*) as UNPROCESSED_CONTEXT_COUNT \
from DELPHI_EXPLORATION.SYS.INBOUND_FILE_STATUS \
where DSP = 'SPOTIFY' \
  and REPORT_NAME = 'STREAMS' \
  and REPORT_DATE between current_date() - 15 and current_date() - 2 \
  and STATUS in ('LOADED', 'L_OUTDATED') \
  and PROCESSING_COMPLETED in (0, -1, -2) \
group by REPORT_DATE, PROCESSING_COMPLETED \
order by REPORT_DATE desc;"""

sf_check_5 = """select distinct REPORT_DATE \
from DELPHI_EXPLORATION.SYS.SPOTIFY_JUNO_TOP_STREAMS_CACHE \
where REPORT_DATE between current_date() - 4 and current_date() - 2 \
order by REPORT_DATE desc;"""

sf_check_6 = """select distinct REPORT_DATE \
from DELPHI_EXPLORATION.EXP.SPOTIFY_JUNO_TOP_STREAMS \
where REPORT_DATE between current_date() - 4 and current_date() - 2 \
order by REPORT_DATE desc;"""

sf_check_7 = """select distinct  \
    LOAD_KEY as LATEST_SOURCE_KEY_AVAILABLE, \
    (select max(LOAD_ID) from DELPHI_EXPLORATION.RAW.SAMIS_FIRST_SALE_DATE_REPORT) as RAW_LATEST_KEY, \
    (select max(LOAD_ID) from DELPHI_EXPLORATION.MAIN.SAMIS_FIRST_SALE_DATE_REPORT) as MAIN_LATEST_KEY \
from DS_IST.SAMIS.FEED_FRONTLINE_CATALOG \
where LOAD_KEY >= RAW_LATEST_KEY \
   or LOAD_KEY >= MAIN_LATEST_KEY;"""

sf_check_8 = """select datediff(d, max(greatest(CREATED_AT, nvl(UPDATED_AT, '2000-01-01'))), current_timestamp()) as DAYS_AFTER_LAST_REFRESH \
from DELPHI_EXPLORATION.MAIN.SAMIS_FIRST_SALE_DATE_REPORT;"""

if __name__ == '__main__':
    pass