SELECT 
    ad_set_id AS ad_group_id,
    campaign_id,
    daily_budget AS default_bid,
    ad_set_name AS ad_group_name,
    status AS serving_status,
    status AS state
FROM 
    snapshot_facebook_ads_ad_set_history_data

SELECT 
    campaign_name AS campaign_name,
    CASE 
        WHEN campaign_status = 'ACTIVE' THEN 'enabled'
        WHEN campaign_status = 'PAUSED' THEN 'paused'
        WHEN campaign_status = 'DELETED' THEN NULL
    END AS campaign_state,
    end_timestamp AS end_date,
    CASE 
        WHEN campaign_status = 'ACTIVE' THEN 'CAMPAIGN_STATUS_ENABLED'
        WHEN campaign_status = 'PAUSED' THEN 'CAMPAIGN_PAUSED'
        WHEN campaign_status = 'DELETED' THEN NULL
    END AS serving_status,
    start_timestamp AS start_date
FROM 
    snapshot_facebook_ads_campaign_history_data

SELECT 
    stg.ad_set_id AS ad_group_id,
    stg.ad_set_name AS ad_group_name,
    CASE 
        WHEN snap.status = 'ACTIVE' THEN 'enabled'
        WHEN snap.status = 'PAUSED' THEN 'paused'
        WHEN snap.status = 'DELETED' THEN 'disabled'
        ELSE NULL
    END AS ad_group_state,
    stg.campaign_id,
    stg.start_date AS creation_date,
    stg.last_updated AS last_updated_date,
    CASE 
        WHEN snap.status = 'ACTIVE' THEN 'AD_STATUS_LIVE'
        WHEN snap.status = 'PAUSED' THEN 'CAMPAIGN_PAUSED'
        ELSE NULL
    END AS serving_status
FROM 
    stg_facebook_ads_ad_set_history_data stg
LEFT JOIN
    snapshot_facebook_ads_ad_set_history_data snap
    ON stg.ad_set_id = snap.ad_set_id  -- Assuming this is the correct join condition
WHERE 
    stg.ad_set_id IS NOT NULL  -- Ensure we only get rows with valid ad_set_id

SELECT 
    fb.link_clicks AS clicks,
    fb.ad_spend AS cost,
    fb.impressions,
    fb.performance_date
FROM 
    stg_facebook_ads_basic_ad_data fb
JOIN 
    snapshot_facebook_ads_ad_set_history_data ad_set
    ON fb.adset_name = ad_set.ad_set_name

SELECT 
    b.ad_spend,
    CASE 
        WHEN c.daily_budget_cents IS NOT NULL THEN 'DAILY_BUDGET'
        ELSE NULL
    END AS budget_type,
    b.link_clicks AS clicks,
    c.daily_budget_cents / 100 AS daily_budget_amount,
    b.impressions,
    b.performance_date AS report_date
FROM 
    stg_facebook_ads_basic_ad_data b
JOIN 
    snapshot_facebook_ads_ad_history_data a ON b.ad_id = a.ad_id
JOIN 
    snapshot_facebook_ads_campaign_history_data c ON a.campaign_id = c.campaign_id

SELECT 
    COALESCE(s.daily_budget_cents, s.lifetime_budget_cents) / 100.0 AS budget_amount,
    CASE WHEN s.daily_budget_cents IS NOT NULL THEN 'daily' ELSE NULL END AS budget_type,
    stg.campaign_id,
    stg.campaign_name,
    CASE 
        WHEN s.campaign_status = 'ACTIVE' THEN 'enabled'
        WHEN s.campaign_status = 'PAUSED' THEN 'paused'
        WHEN s.campaign_status = 'DELETED' THEN NULL
    END AS campaign_state,
    stg.creation_timestamp AS creation_date,
    stg.end_timestamp AS end_date,
    stg.last_update_timestamp AS last_updated_date,
    CASE 
        WHEN s.campaign_status = 'ACTIVE' THEN 'CAMPAIGN_STATUS_ENABLED'
        WHEN s.campaign_status = 'PAUSED' THEN 'CAMPAIGN_PAUSED'
        WHEN s.campaign_status = 'DELETED' THEN NULL
    END AS serving_status,
    stg.start_timestamp AS start_date
FROM 
    snapshot_facebook_ads_campaign_history_data s
FULL OUTER JOIN
    stg_facebook_ads_campaign_history_data stg
ON 
    s.campaign_id = stg.campaign_id

SELECT 
    saa.account_currency AS budget_currency,
    sfb.link_clicks AS clicks,
    sfb.ad_spend AS cost,
    sfc.daily_budget_cents / 100 AS daily_budget_amount,
    sfb.impressions,
    sfb.performance_date AS report_date
FROM 
    stg_facebook_ads_basic_ad_data sfb
JOIN 
    snapshot_facebook_ads_account_history_data saa ON sfb.account_id = saa.account_id
JOIN 
    snapshot_facebook_ads_campaign_history_data sfc ON sfb.campaign_id = sfc.campaign_id
WHERE 
    saa.is_latest = TRUE
    AND sfc.is_latest = TRUE

SELECT 
    c.creation_timestamp AS creation_date,
    a.account_currency AS currency_code,
    c.campaign_id AS entry_id,
    c.last_update_timestamp AS last_updated_date,
    CASE 
        WHEN c.campaign_status = 'ACTIVE' THEN 'PORTFOLIO_STATUS_ENABLED'
        WHEN c.campaign_status = 'PAUSED' THEN 'PORTFOLIO_STATUS_PAUSED'
        ELSE NULL
    END AS operational_status,
    CASE 
        WHEN c.campaign_status = 'ACTIVE' THEN 'enabled'
        WHEN c.campaign_status = 'PAUSED' THEN 'paused'
        ELSE NULL
    END AS portfolio_state,
    c.account_id AS profile_id
FROM 
    stg_facebook_ads_campaign_history_data c
JOIN 
    snapshot_facebook_ads_account_history_data a ON c.account_id = a.account_id

SELECT 
    account_id AS account_id,
    country_code AS country_code,
    CASE 
        WHEN account_currency = 'USD' THEN 'USD'
        WHEN account_currency = 'GBP' THEN 'GBP'
        WHEN account_currency = 'JPY' THEN 'JPY'
        -- Add more currency mappings as needed
        ELSE account_currency  -- Default case if no mapping is specified
    END AS currency_code,
    account_timezone AS timezone
FROM 
    stg_facebook_ads_account_history_data