{{ config(materialized='incremental', unique_key='raw_id') }}

SELECT
    _airbyte_raw_id AS raw_id,
    _airbyte_extracted_at AS extracted_at,
    _airbyte_loaded_at AS loaded_at,
    JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.report_id')) AS report_id,
    JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.campaign_id')) AS campaign_id,
    CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.clicks')) AS UNSIGNED) AS clicks,
    CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.impressions')) AS UNSIGNED) AS impressions,
    CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.conversions')) AS UNSIGNED) AS conversions,
    ROUND(
        CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.clicks')) AS UNSIGNED)
            / NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.impressions')) AS UNSIGNED), 0),
        4
    ) AS ctr,
    ROUND(
        CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.conversions')) AS UNSIGNED)
            / NULLIF(CAST(JSON_UNQUOTE(JSON_EXTRACT(_airbyte_data, '$.clicks')) AS UNSIGNED), 0),
        4
    ) AS conversion_rate
FROM airbyte_db.airbyte_db_raw__strea__nt_performance_report

    {% if is_incremental() %}
WHERE _airbyte_extracted_at > COALESCE((SELECT MAX(extracted_at) FROM {{ this }}), '2000-01-01')
    {% endif %}
