create or replace task SUNO_PROD.PROD.REVENUECAT_TRANSACTION_DATA_DAILY_INSERT
	warehouse=SUNO_PROD_OTHER_DAILY_X_SMALL
	schedule='USING CRON 0 3 * * * UTC'
	as declare
        start_time TIMESTAMP;
        p_date date;
        p_hour string;
        stage_path string;
        row_count number;
        query_sql string;
    begin
        start_time := CURRENT_TIMESTAMP();
        p_date := DATE(SYSDATE());
        stage_path := CONCAT('@SUNO_REVENUECAT/suno-revenuecat-data/', :p_date);
        query_sql := 'insert into revenuecat_transaction_data
        select
            $1 as rc_original_app_user_id,
            $2 as rc_last_seen_app_user_id_alias,
            $3 as country,
            $4 as product_identifier,
            $5 as start_time,
            $6 as end_time,
            $7 as store,
            $8 as is_auto_renewable,
            $9 as is_trial_period,
            $10 as is_in_intro_offer_period,
            $11 as is_sandbox,
            $12 as price_in_usd,
            $13 as takehome_percentage,
            $14 as store_transaction_id,
            $15 as original_store_transaction_id,
            $16 as refunded_at,
            $17 as unsubscribe_detected_at,
            $18 as billing_issues_detected_at,
            $19 as purchased_currency,
            $20 as price_in_purchased_currency,
            $21 as entitlement_identifiers,
            $22 as renewal_number,
            $23 as is_trial_conversion,
            $24 as presented_offering,
            $25 as reserved_subscriber_attributes,
            $26 as custom_subscriber_attributes,
            $27 as platform,
            $28 as tax_percentage,
            $29 as commission_percentage,
            $30 as effective_end_time,
            $31 as grace_period_end_time,
            $32 as ownership_type,
            $33 as country_source,
            $34 as experiment_id,
            $35 as experiment_variant,
            $36 as purchase_price_in_usd,
            $37 as purchase_price_in_purchased_currency,
            $38 as product_display_name,
            $39 as product_duration,
            $40 as updated_at,
            $41 as offer,
            $42 as offer_type,
            $43 as first_seen_time,
            $44 as auto_resume_time,
            date($5) as p_date
        from ' || :stage_path || ' (file_format => ''CSV_GZIP_FORMAT'', pattern => (''.*''));
        ';
        execute immediate :query_sql;
        SELECT COUNT(*) 
            INTO :row_count 
        FROM PROD.revenuecat_transaction_data 
        WHERE p_date = :p_date;
        call TASK_MONITOR_INSERT_PROC('REVENUECAT_TRANSACTION_DATA_DAILY_INSERT', :start_time, :row_count, 'SUCCESS', 'DAILY');
    end;