SELECT
    'Google Ads' AS platform,
    AVG(CAST(NULLIF(campaign_budget, '') AS FLOAT)) AS average_daily_budget
FROM
    googleads_campaigns
WHERE
    status = 'ENABLED'
    AND campaign_budget ~ '^[0-9]+$'
UNION ALL
SELECT
    'Facebook Marketing' AS platform,
    AVG(CAST(NULLIF(daily_budget, '') AS FLOAT)) AS average_daily_budget
FROM
    facebookmarketing_campaigns
WHERE
    status = 'ACTIVE'
    AND daily_budget ~ '^[0-9]+$';

WITH google_ads AS (
    SELECT
        CASE
            WHEN status = 'ENABLED' THEN 'ACTIVE'
            WHEN status = 'PAUSED' THEN 'PAUSED'
            WHEN status = 'REMOVED' THEN 'DELETED'
        END AS status,
        COUNT(*) AS campaign_count
    FROM
        googleads_campaigns
    WHERE status IN ('ENABLED', 'PAUSED', 'REMOVED')
    GROUP BY
        CASE
            WHEN status = 'ENABLED' THEN 'ACTIVE'
            WHEN status = 'PAUSED' THEN 'PAUSED'
            WHEN status = 'REMOVED' THEN 'DELETED'
        END
),

meta_ads AS (
    SELECT
        status,
        COUNT(*) AS campaign_count
    FROM
        facebookmarketing_campaigns
    WHERE status IN ('ACTIVE', 'PAUSED', 'DELETED')
    GROUP BY
  status
)

-- Combining Results into Pivot Table
SELECT
    COALESCE(google.status, meta.status) AS status,
    COALESCE(google.campaign_count, 0) AS google_ads,
    COALESCE(meta.campaign_count, 0) AS facebook_ads
FROM
    google_ads google
FULL OUTER JOIN
    meta_ads meta
ON google.status = meta.status
ORDER BY status;

SELECT
    'Google Ads' AS platform,
    CAST(customer_id AS TEXT) AS account_id,
    CAST(id AS TEXT) AS campaign_id,
    name,
    status,
    CAST(start_date AS TIMESTAMP) AS start_time,
    CAST(end_date AS TIMESTAMP) AS stop_time,
    campaign_budget,
    primary_status
FROM
    googleads_campaigns
WHERE
    status = 'ENABLED'

UNION ALL

SELECT
    'Facebook Marketing' AS platform,
    account_id,
    id AS campaign_id,
    name,
    status,
    start_time,
    stop_time,
    daily_budget,
    effective_status AS primary_status
FROM
    facebookmarketing_campaigns
WHERE
    status = 'ACTIVE';