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

### Step 1. Check query in Postgres
pg_query = """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
where r.data_source_id = 15
	and uow.completeness_status != 'CANCELLED'
	and not uow.unit_of_work_code like 'appreciationengine-%-activityfeed-v1' 
	and (
	    content_status.content_status not in ('COMPLETE', 'ON_HOLD', 'CANCELLED', 'MISSING')
		or
	    (
			content_status.content_status = 'MISSING'
			and meta_data is not null
			and meta_data::text not in ('{}', '[]', '"null"', '""')
		)
    )
	and (
	    (uow.report_date between current_date-14 and current_date-1 and uow.report_id<>203)
	    or
	    (uow.report_date between current_date-14 and current_date-2 and uow.report_id=203)
    )
group by uow.report_date
order by uow.report_date desc"""

### Step 1.1 
pg_query_1_1="""select c.report_date "REPORT_DATE", 
       string_agg(distinct concat(c.report_name||' ('||c.licensor_name||')'), ',') "NOT_FULLY_DISASSEMBLED_REPORTS"
from (select uow.report_date, 
             uow.unit_of_work_id, 
             uow.unit_of_work_code, 
             l.licensor_name, 
             r.report_name, 
             uow.timeslot, 
             uow.completeness_status, 
             cs.content_status_id, 
             cs.context,  
             cs.content_status, 
             sum(case when dcs.disassemble_status = 'COMPLETE' then 1 else 0 end) as disassemble_completed_count
       from unit_of_work uow
       left join content_status cs on uow.unit_of_work_id = cs.unit_of_work_id 
       left join disassemble_content_status dcs on dcs.content_status_id = cs.content_status_id
       left join report r on uow.report_id = r.report_id 
       left join licensor l on uow.licensor_id = l.licensor_id 
       where uow.report_id in (137,138,139,140,141,150,159,204)
       and uow.report_date between (current_date - 14) and (current_date - 1)
       and not uow.unit_of_work_code like 'appreciationengine-%-activityfeed-v2' 
       and cs.content_status = 'COMPLETE'
       and cs.content_size <> 0
       group by 1,2,3,4,5,6,7,8,9,10
       having sum(case when dcs.disassemble_status = 'COMPLETE' then 1 else 0 end) < 1) c
group by c.report_date;"""

### Step 1.2
pg_query_1_2 = """select c.report_date "REPORT_DATE",
       string_agg(c.context, ',') "MISSING_AE_CONTEXTS"
from (select uow.report_date,
             cs.context
       from unit_of_work uow
       left join content_status cs on uow.unit_of_work_id = cs.unit_of_work_id
       left join report r on uow.report_id = r.report_id
       left join licensor l on uow.licensor_id = l.licensor_id
       where uow.report_id in (139)
       and uow.report_date between (current_date - 14) and (current_date - 1)
       and cs.content_status<>'COMPLETE'
       and uow.completeness_status<>'CANCELLED'
    order by cs.context
) c
group by c.report_date;"""

### Step 2. RAW_SCHEMA: verify that no ingestions failed during the last two weeks
sf_query = """with  \
    cte_ae_reports as ( \
        select distinct \
            REPORT_NAME \
        from DELPHI_EXPLORATION.SYS.DSP_REPORTS \
        where DSP = 'APPRECIATIONENGINE' \
    ), \
    cte_two_weeks_dates as ( \
        select dateadd(day, -1*row_number() over (order by null), current_date()) as REPORT_DATE \
        from table(GENERATOR(ROWCOUNT=>14)) \
    ), \
    cte_licensors as ( \
        select 'sme' as LICENSOR union  \
        select 'theorchard' union  \
        select 'n/a' \
    ), \
    cte_full_scope as ( \
        select distinct \
            REPORT_NAME, LICENSOR, REPORT_DATE \
        from cte_ae_reports \
             cross join cte_two_weeks_dates \
             cross join cte_licensors \
    ), \
    cte_chfs as ( \
        select \
            REPORT_NAME,  \
            iff(REPORT_NAME in ('SMF_MAPPING', 'SMF_MCR_METADATA'),  \
                to_date(left(split_part(FILE_NAME, '_', 3), 8), 'YYYYMMDD'), REPORT_DATE) as REPORT_DATE,  \
            nvl(LICENSOR, 'n/a') as LICENSOR, \
            sum(iff(STATUS = 'LOADED', 1, 0)) as LOADED_COUNT, \
            sum(iff(STATUS != 'LOADED', 1, 0)) as NOT_LOADED_COUNT \
        from DELPHI_EXPLORATION.SYS.COPY_HISTORY_FILE_STATUS \
        where DSP = 'APPRECIATIONENGINE' \
          and iff(REPORT_NAME in ('SMF_MAPPING', 'SMF_MCR_METADATA'),  \
                  to_date(left(split_part(FILE_NAME, '_', 3), 8), 'YYYYMMDD'), REPORT_DATE) in (select REPORT_DATE  \
                                                                                                from cte_two_weeks_dates) \
        group by  \
            REPORT_NAME,  \
            iff(REPORT_NAME in ('SMF_MAPPING', 'SMF_MCR_METADATA'),  \
                to_date(left(split_part(FILE_NAME, '_', 3), 8), 'YYYYMMDD'), REPORT_DATE),  \
            LICENSOR \
    ) \
select \
    cfs.REPORT_DATE, \
    count(*) as MISSING_REPORTS_COUNT, \
    listagg(concat(cfs.REPORT_NAME, iff(cfs.LICENSOR = 'n/a', '', concat(' (', cfs.LICENSOR, ')') )), ', ') as MISSING_REPORTS \
from cte_full_scope cfs \
     left join cte_chfs chfs on cfs.REPORT_NAME = chfs.REPORT_NAME  \
                            and cfs.LICENSOR = chfs.LICENSOR \
                            and cfs.REPORT_DATE = chfs.REPORT_DATE \
where (chfs.LOADED_COUNT is null or chfs.NOT_LOADED_COUNT > 0) \
  and not (cfs.REPORT_NAME = 'MEMBERSALL' and cfs.REPORT_DATE < '2021-10-11')  \
  and ( \
          (cfs.REPORT_NAME in ('SMF_MAPPING', 'SMF_MCR_METADATA') and cfs.LICENSOR = 'n/a') or  \
          (cfs.REPORT_NAME not in ('SMF_MAPPING', 'SMF_MCR_METADATA') and cfs.LICENSOR <> 'n/a') \
      ) \
  and not (cfs.REPORT_NAME in ('SMF_MAPPING', 'SMF_MCR_METADATA') and cfs.REPORT_DATE < current_date() - 1) \
  and not (cfs.REPORT_NAME in ('SMF_MAPPING', 'SMF_MCR_METADATA') and dayname(cfs.REPORT_DATE) in ('Sat', 'Sun'))  \
group by cfs.REPORT_DATE \
order by cfs.REPORT_DATE desc;"""

if __name__ == '__main__':
    pass