etl_processing = """select *
from (
select
report_date,
case
when unit_of_work_code like 'spotify%' then 'spotify'
when  unit_of_work_code like 'apple%' then 'apple'
when  unit_of_work_code like 'amazonprime%' then 'amazonprime'
when  unit_of_work_code like 'amazonmusicunlimited%' then 'amazonmusicunlimited'
when  unit_of_work_code like 'amazonadsupported%' then 'amazonadsupported'
when  unit_of_work_code like 'youtubereporting%streams%' then 'youtubereporting'
when  unit_of_work_code like 'youtubereporting%demogr%' then 'youtubereporting'
when  unit_of_work_code like 'tiktokreporting-%-top_sounds_aggregation_daily' then 'tiktok sounds'
when  unit_of_work_code like 'tiktokreporting%' then 'tiktokreporting'
else 'other' end as sources,
unit_of_work_code,
r.report_name,
case
when completeness_status in ('MIN_COMPLETE', 'COMPLETE') then 'ok'
when completeness_status in ('FAILED') then 'error'
when completeness_status in ('ACTIVE') then 'missing'
else 'other' end as high_level_status,
uow.completeness_status,
case
when  aede.status = 'COMPLETE' then 'COMPLETE'
else 'NOT_CALCULATED' end as etl_status
from apps_etl_unit_of_work uow
left join apps_etl_dabricks_execution aede on uow.unit_of_work_id = aede.unit_of_work_id and aede.status ='COMPLETE'
left join apps_etl_report r on r.report_id = uow.report_id
where true
and uow.report_date >= current_date - 14
--and uow.report_date < current_date - 1
) main
where sources <> 'other'
group by report_date, sources, unit_of_work_code, report_name, high_level_status, completeness_status, etl_status
order by report_date desc, sources"""

### snowflake cursor doesn't support string sseparation, thus this var is adjusted
snowflake_sql = "SELECT  \
    c.artist_name,  \
    c.gras_id AS gras_id,  \
    c.spotify_id,  \
    c.category,  \
    a.cm_artist,  \
    (current_date) - MAX(s.facebook_max) AS fb_lag,  \
    (current_date) - MAX(s.instagram_max) AS ig_lag,  \
    (current_date) - MAX(s.twitter_max) AS twitter_lag  \
FROM delphi_exploration.exploration_sandbox.cf_rti_artist_list_clean c  \
    LEFT JOIN ds_chartmetric.raw_data.spotify_artist a ON c.spotify_id = a.spotify_artist_id  \
    LEFT JOIN delphi_exploration.chartmetric_sys.chartmetric_status s ON a.cm_artist = s.chartmetric_id  \
    LEFT JOIN delphi_exploration.chartmetric_sys.v_account_mapping vam ON vam.chartmetric_id = to_varchar(a.cm_artist)  \
WHERE true  \
    AND vam.source in ('facebook', 'twitter', 'instagram')  \
GROUP BY 1,2,3,4,5  \
ORDER BY fb_lag DESC;"


if __name__ == '__main__':
    pass