create or replace task SUNO_PROD.PROD.AF_INAPPS_RETARGETING_DAILY_INSERT
    warehouse=SUNO_PROD_RDS_DAILY_X_SMALL
    schedule='USING CRON 0 11 * * * UTC'
as
    declare
        start_time TIMESTAMP;
        max_ingestion_time timestamp;
        row_count number;
    begin
        start_time := CURRENT_TIMESTAMP();
        SELECT COALESCE(MAX(_INGESTION_TIME), '0001-01-01')
            INTO :max_ingestion_time
        FROM SUNO_PROD.PROD.AF_INAPPS_RETARGETING;

        INSERT INTO SUNO_PROD.PROD.AF_INAPPS_RETARGETING (
            ADVERTISING_ID,
            AF_AD,
            AF_AD_ID,
            AF_AD_TYPE,
            AF_ADSET,
            AF_ADSET_ID,
            AF_ATTRIBUTION_LOOKBACK,
            AF_C_ID,
            AF_CHANNEL,
            AF_COST_VALUE,
            AF_KEYWORDS,
            AF_SITEID,
            AF_SUB5,
            APP_ID,
            APP_NAME,
            APP_TYPE,
            APP_VERSION,
            APPSFLYER_ID,
            ATT,
            ATTRIBUTED_TOUCH_TIME,
            ATTRIBUTED_TOUCH_TYPE,
            BUNDLE_ID,
            CAMPAIGN,
            CAMPAIGN_TYPE,
            CARRIER,
            CITY,
            CONVERSION_TYPE,
            COUNTRY_CODE,
            CUSTOM_DATA,
            CUSTOMER_USER_ID,
            DEVICE_CATEGORY,
            DEVICE_DOWNLOAD_TIME,
            DEVICE_MODEL,
            DMA,
            DT,
            ENGAGEMENT_TYPE,
            EVENT_NAME,
            EVENT_REVENUE,
            EVENT_REVENUE_CURRENCY,
            EVENT_REVENUE_USD,
            EVENT_SOURCE,
            EVENT_TIME,
            EVENT_VALUE,
            H,
            IDFA,
            IDFV,
            INSTALL_TIME,
            IP,
            IS_LAT,
            IS_ORGANIC,
            IS_PRIMARY_ATTRIBUTION,
            IS_RETARGETING,
            KEYWORD_ID,
            KEYWORD_MATCH_TYPE,
            LANGUAGE,
            MATCH_TYPE,
            MEDIA_SOURCE,
            NETWORK_ACCOUNT_ID,
            OPERATOR,
            ORIGINAL_URL,
            OS_VERSION,
            PLATFORM,
            POSTAL_CODE,
            REGION,
            RETARGETING_CONVERSION_TYPE,
            SDK_VERSION,
            STATE,
            USER_AGENT,
            WIFI,
            _INGESTION_TIME
        )
        SELECT
            ADVERTISING_ID,
            AF_AD,
            AF_AD_ID,
            AF_AD_TYPE,
            AF_ADSET,
            AF_ADSET_ID,
            AF_ATTRIBUTION_LOOKBACK,
            AF_C_ID,
            AF_CHANNEL,
            AF_COST_VALUE,
            AF_KEYWORDS,
            AF_SITEID,
            AF_SUB5,
            APP_ID,
            APP_NAME,
            APP_TYPE,
            APP_VERSION,
            APPSFLYER_ID,
            ATT,
            ATTRIBUTED_TOUCH_TIME,
            ATTRIBUTED_TOUCH_TYPE,
            BUNDLE_ID,
            CAMPAIGN,
            CAMPAIGN_TYPE,
            CARRIER,
            CITY,
            CONVERSION_TYPE,
            COUNTRY_CODE,
            CUSTOM_DATA,
            CUSTOMER_USER_ID,
            DEVICE_CATEGORY,
            DEVICE_DOWNLOAD_TIME,
            DEVICE_MODEL,
            DMA,
            DT,
            ENGAGEMENT_TYPE,
            EVENT_NAME,
            EVENT_REVENUE,
            EVENT_REVENUE_CURRENCY,
            EVENT_REVENUE_USD,
            EVENT_SOURCE,
            EVENT_TIME,
            EVENT_VALUE,
            H,
            IDFA,
            IDFV,
            INSTALL_TIME,
            IP,
            IS_LAT,
            IS_ORGANIC,
            IS_PRIMARY_ATTRIBUTION,
            IS_RETARGETING,
            KEYWORD_ID,
            KEYWORD_MATCH_TYPE,
            LANGUAGE,
            MATCH_TYPE,
            MEDIA_SOURCE,
            NETWORK_ACCOUNT_ID,
            OPERATOR,
            ORIGINAL_URL,
            OS_VERSION,
            PLATFORM,
            POSTAL_CODE,
            REGION,
            RETARGETING_CONVERSION_TYPE,
            SDK_VERSION,
            STATE,
            USER_AGENT,
            WIFI,
            _INGESTION_TIME
        FROM APPSFLYER.SNOWFLAKE.INAPPS_RETARGETING_VIEW
        WHERE _INGESTION_TIME > :max_ingestion_time;

        SELECT COUNT(*)
            INTO :row_count
        FROM SUNO_PROD.PROD.AF_INAPPS_RETARGETING
        WHERE _INGESTION_TIME > :max_ingestion_time;
        call TASK_MONITOR_INSERT_PROC('AF_INAPPS_RETARGETING_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
    end
;
