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

### Step 1. Check query in Postgres
pg_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_id = 8
    and (
		uow.completeness_status not in ('COMPLETE', 'MIN_COMPLETE', 'CANCELLED')
		or 
		content_status.content_status in ('MISSING', 'FAILED')
	)
    and uow.report_date > current_date-14
	and uow.report_date <= current_date-1
group by uow.report_date
order by uow.report_date DESC;"""

### snowflake cursor doesn't support string sseparation, thus this var is adjusted
sf_check_2 = """select count(*) "count" \
from table("DELPHI_EXPLORATION"."INFORMATION_SCHEMA".copy_history(table_name=>'DELPHI_EXPLORATION.RAW.SME_MAX_ALBUM_MAPPING', start_time=> dateadd(day, -1, current_timestamp()))) \
where STATUS != 'Loaded' \
union \
select count(*) "count" \
from table("DELPHI_EXPLORATION"."INFORMATION_SCHEMA".copy_history(table_name=>'DELPHI_EXPLORATION.RAW.SME_MAX_TRACK_MAPPING', start_time=> dateadd(day, -1, current_timestamp()))) \
where STATUS != 'Loaded'; """

sf_check_3 = """select left(file_name, 22 ) = ('report_date=' || DATEADD(day,-1,  current_date() )) "result" \
from table("DELPHI_EXPLORATION"."INFORMATION_SCHEMA".copy_history(table_name=>'DELPHI_EXPLORATION.RAW.SME_MAX_ALBUM_MAPPING', start_time=> dateadd(day, -1, current_timestamp())));"""

sf_check_4 = """select left(file_name, 22 ) = ('report_date=' || DATEADD(day,-1,  current_date() )) "result" \
from table("DELPHI_EXPLORATION"."INFORMATION_SCHEMA".copy_history(table_name=>'DELPHI_EXPLORATION.RAW.SME_MAX_TRACK_MAPPING', start_time=> dateadd(day, -1, current_timestamp())));"""

sf_check_5 = """with \
  raw as ( \
    select distinct \
      PRODUCT_KY, \
      COUNTRY_KY, \
      iff(APPLE_ID is null or APPLE_ID = 'NULL', 'NO_VALUE', APPLE_ID) as APPLE_ID, \
      iff(APPLE_STATUS is null or APPLE_STATUS = 'NULL', 'NO_VALUE', APPLE_STATUS) as APPLE_STATUS, \
      iff(UPC is null or UPC = 'NULL', 'NO_VALUE', UPC) as UPC, \
      iff(DIGITAL_ASIN is null or DIGITAL_ASIN = 'NULL', 'NO_VALUE', DIGITAL_ASIN) as DIGITAL_ASIN, \
      iff(GRID is null or GRID = 'NULL', 'NO_VALUE', GRID) as GRID \
    from "DELPHI_EXPLORATION"."RAW"."SME_MAX_ALBUM_MAPPING" \
    where REPORT_DATE > dateadd(day, -20, current_date()) \
      and PRODUCT_KY is not null \
      and COUNTRY_KY is not null \
  ) \
select * \
from raw \
where not exists ( \
                  select * \
                  from "DELPHI_EXPLORATION"."EXP"."SME_MAX_ALBUM_MAPPING" \
                  where PRODUCT_KY = raw.PRODUCT_KY \
                    and COUNTRY_KY = raw.COUNTRY_KY \
                    and APPLE_ID = raw.APPLE_ID \
                    and APPLE_STATUS = raw.APPLE_STATUS \
                    and UPC = raw.UPC \
                    and DIGITAL_ASIN = raw.DIGITAL_ASIN \
                    and GRID = raw.GRID \
                 );"""

sf_check_6 = """with \
  raw as ( \
    select distinct \
      PRODUCT_KY, \
      COUNTRY_KY, \
      iff(APPLE_ID is null or APPLE_ID = 'NULL', 'NO_VALUE', APPLE_ID) as APPLE_ID, \
      iff(APPLE_STATUS is null or APPLE_STATUS = 'NULL', 'NO_VALUE', APPLE_STATUS) as APPLE_STATUS, \
      iff(UPC is null or UPC = 'NULL', 'NO_VALUE', UPC) as UPC, \
      iff(DIGITAL_ASIN is null or DIGITAL_ASIN = 'NULL', 'NO_VALUE', DIGITAL_ASIN) as DIGITAL_ASIN, \
      iff(GRID is null or GRID = 'NULL', 'NO_VALUE', GRID) as GRID \
    from "DELPHI_EXPLORATION"."RAW"."SME_MAX_ALBUM_MAPPING" \
    where REPORT_DATE > dateadd(day, -20, current_date()) \
      and PRODUCT_KY is not null \
      and COUNTRY_KY is not null \
  ), \
  exp as ( \
    select * \
    from "DELPHI_EXPLORATION"."EXP"."SME_MAX_ALBUM_MAPPING" e \
         inner join raw r on e.PRODUCT_KY = r.PRODUCT_KY \
                          and e.COUNTRY_KY = r.COUNTRY_KY \
                          and e.APPLE_ID = r.APPLE_ID \
                          and e.APPLE_STATUS = r.APPLE_STATUS \
                          and e.UPC = r.UPC \
                          and e.DIGITAL_ASIN = r.DIGITAL_ASIN \
                          and e.GRID = r.GRID \
  ) \
select count(*) "count", 'dest; SF exp' \
from exp \
  union \
select count(*) "count", 'src; SF raw' \
from raw;"""

sf_check_7 = """select count(*) "count", 'src; SF exp' \
from "DELPHI_EXPLORATION"."EXP"."SME_MAX_TRACK_MAPPING" SMAM \
where not exists( \
  select 1 \
  from "DELPHI_EXPLORATION"."APPS_ETL"."SME_MAX_TRACK_MAPPING" EM \
  where OPERATION_FLAG != 'D' \
    and equal_null(EM.ISRC, SMAM.ISRC) \
    and equal_null(EM.TRACK_APPLE_ID, SMAM.TRACK_APPLE_ID) \
    and equal_null(EM.ALBUM_APPLE_ID, SMAM.ALBUM_APPLE_ID) \
    and equal_null(EM.ALBUM_GRID, SMAM.ALBUM_GRID) \
) \
union \
select count(*), 'dest; SF apps_etl' \
from "DELPHI_EXPLORATION"."APPS_ETL"."V_SME_MAX_TRACK_MAPPING";"""

sf_check_8 = """select count(*) "count", 'src; SF exp' \
from "DELPHI_EXPLORATION"."EXP"."SME_MAX_ALBUM_MAPPING" SMTM \
where not exists( \
  select 1 \
  from "DELPHI_EXPLORATION"."APPS_ETL"."SME_MAX_ALBUM_MAPPING" EM \
  where OPERATION_FLAG != 'D' \
    and equal_null(EM.APPLE_ID, SMTM.APPLE_ID) \
    and equal_null(EM.UPC, SMTM.UPC) \
    and equal_null(EM.GRID, SMTM.GRID) \
) \
union \
select count(*), 'dest; SF apps_etl' \
from "DELPHI_EXPLORATION"."APPS_ETL"."V_SME_MAX_ALBUM_MAPPING";"""

sf_check_9 = """select count(*) "count" \
from "DELPHI_EXPLORATION"."SYS"."DATA_ENABLEMENT_CONTROL"  \
where dsp = 'SME MAX' \
  and report_name = 'ALL'  \
  and date_enabled = DATEADD(day,-2,  current_date()); """

if __name__ == '__main__':
    pass