CREATE VIEW facebook_ad_account.ad_history_view   AS (
  SELECT
  	CASE
  		WHEN 
  			LAG(updated_time) over (PARTITION by id ORDER BY updated_time) is NULL 
                    THEN created_time 
        ELSE updated_time 
        END AS valid_from
        , COALESCE(LEAD(updated_time) 
                   over (PARTITION by id ORDER BY updated_time), CAST('2100-01-01' AS TIMESTAMP)) 
             AS valid_to
                    , *
  FROM facebook_ad_account.ad_history
)

SELECT
    md5(extract('epoch' from i.date)  || '-' || i.ad_id) as enriched_activity_id,
    i.date  as enriched_ts,
    i.date, 
    'facebook' as ad_source, 
    u.ad_name, 
    NULL as campaign_name,
    u.utm_source, 
    u.utm_campaign,
    u.utm_medium,
    u.utm_content,
    i.impressions,
    i.inline_link_clicks as clicks,
    i.spend as spend
FROM facebook.facebook_ads i 
LEFT OUTER JOIN (
    SELECT 
        ah.account_id, 
  			ah.name as ad_name, 
  			ah.id as ad_id, 
  			t.utm_medium, 
  			t.utm_campaign, 
  			t.utm_content,  
  			t.utm_source, 
  			valid_from, 
  			valid_to
    FROM facebook_ad_account.ad_history_view ah
    LEFT OUTER JOIN (
        SELECT _fivetran_synced::DATE as synced_at, 
            t.creative_id, 
            MIN(CASE WHEN key = 'utm_medium' THEN value END ) AS utm_medium,
            MIN(CASE WHEN key = 'utm_source' THEN value END ) AS utm_source,
            MIN(CASE WHEN key = 'utm_campaign' THEN value END ) AS utm_campaign,
            MIN(CASE WHEN key = 'utm_content' THEN value END ) AS utm_content 
        FROM facebook_ad_account.url_tag t
        group by 1, 2 
        ) t
        on (t.creative_id = ah.creative_id 
            AND ah._fivetran_synced::DATE = t.synced_at)
) u
    on (i.account_id = u.account_id 
        AND i.ad_id = u.ad_id 
        AND i.date >= u.valid_from 
        AND i.date <= u.valid_to)
WHERE i.date < SYSDATE::DATE

SELECT
    CONCAT(CAST( UNIX_DATE(i.date) AS STRING), CONCAT("-", CAST( i.ad_id AS STRING))) as enriched_activity_id
    , CAST( i.date AS TIMESTAMP) AS enriched_ts
    , i.date
    , "facebook" AS ad_source
    , u.ad_name
    , CAST(NULL AS STRING) AS campaign_name
    , u.utm_medium
    , u.utm_source
    , u.utm_campaign
    , u.utm_content
    , coalesce(i.impressions, 0) as impressions
    , coalesce(i.inline_link_clicks,0) AS clicks
    , coalesce(i.spend,0) as spend
FROM facebook.basic_ads AS i
LEFT JOIN (
    SELECT
        ah.name AS ad_name
        , ah.id AS ad_id
        , ah.account_id
        , ah.valid_from
        , ah.valid_to
        , t.utm_medium
        , t.utm_source
        , t.utm_campaign
        , t.utm_content
    FROM facebook_ad_account.ad_history_view AS ah 
    LEFT JOIN (
        SELECT
            CAST( _fivetran_synced AS DATE) AS synced_at
            , creative_id
            , MIN(CASE WHEN key = "utm_medium" THEN value END) AS utm_medium
            , MIN(CASE WHEN key = "utm_source" THEN value END) AS utm_source
            , MIN(CASE WHEN key = "utm_campaign" THEN value END) AS utm_campaign
            , MIN(CASE WHEN key = "utm_content" THEN value END) AS utm_content
        FROM facebook_ad_account.url_tag AS t
        GROUP BY 1, 2
    ) AS t
        ON (
            t.creative_id = ah.creative_id  AND
            t.synced_at = CAST( ah._fivetran_synced AS DATE)
        )
) AS u
    ON (
        i.account_id = u.account_id  AND
        i.ad_id = CAST( u.ad_id AS STRING)  AND
        CAST( i.date AS TIMESTAMP) >= CAST( u.valid_from AS TIMESTAMP)  AND
        CAST( i.date AS TIMESTAMP) < CAST( u.valid_to AS TIMESTAMP)
    )

WHERE CAST( i.date AS TIMESTAMP) < CURRENT_TIMESTAMP()