-- Upsert data from RDS to Snowflake
-- This query inserts new records and updates existing ones based on ID and partition

merge into {onetime_free_usage_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:free_cover_generations_used as free_cover_generations_used,
        $1:free_cover_generations_limit as free_cover_generations_limit,
        $1:free_persona_generations_used as free_persona_generations_used,
        $1:free_persona_generations_limit as free_persona_generations_limit,
        $1:free_web_remasters_limit as free_web_remasters_limit,
        $1:free_web_remasters_used as free_web_remasters_used,
        $1:free_mobile_remasters_limit as free_mobile_remasters_limit,
        $1:free_mobile_remasters_used as free_mobile_remasters_used,
        $1:free_mobile_v4_gens_limit as free_mobile_v4_gens_limit,
        $1:free_mobile_v4_gens_used as free_mobile_v4_gens_used,
        $1:free_web_v4_gens_limit as free_web_v4_gens_limit,
        $1:free_web_v4_gens_used as free_web_v4_gens_used,
        $1:user_id as user_id,
        date(to_timestamp(created_at)) as p_date,
        hour(to_timestamp(created_at)) as p_hour
    from {stage_path} (file_format => 'BASIC_PARQUET_LOADER', pattern => ('.*.parquet'))
) as source
on (target.id = source.id and target.p_date = source.p_date and target.p_hour = source.p_hour)
when matched then
    update set 
        target.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.free_cover_generations_used = source.free_cover_generations_used,
        target.free_cover_generations_limit = source.free_cover_generations_limit,
        target.free_persona_generations_used = source.free_persona_generations_used,
        target.free_persona_generations_limit = source.free_persona_generations_limit,
        target.free_web_remasters_limit = source.free_web_remasters_limit,
        target.free_web_remasters_used = source.free_web_remasters_used,
        target.free_mobile_remasters_limit = source.free_mobile_remasters_limit,
        target.free_mobile_remasters_used = source.free_mobile_remasters_used,
        target.free_mobile_v4_gens_limit = source.free_mobile_v4_gens_limit,
        target.free_mobile_v4_gens_used = source.free_mobile_v4_gens_used,
        target.free_web_v4_gens_limit = source.free_web_v4_gens_limit,
        target.free_web_v4_gens_used = source.free_web_v4_gens_used,
        target.user_id = source.user_id,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, free_cover_generations_used, free_cover_generations_limit, free_persona_generations_used, free_persona_generations_limit, free_web_remasters_limit, free_web_remasters_used, free_mobile_remasters_limit, free_mobile_remasters_used, free_mobile_v4_gens_limit, free_mobile_v4_gens_used, free_web_v4_gens_limit, free_web_v4_gens_used, user_id, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.free_cover_generations_used, source.free_cover_generations_limit, source.free_persona_generations_used, source.free_persona_generations_limit, source.free_web_remasters_limit, source.free_web_remasters_used, source.free_mobile_remasters_limit, source.free_mobile_remasters_used, source.free_mobile_v4_gens_limit, source.free_mobile_v4_gens_used, source.free_web_v4_gens_limit, source.free_web_v4_gens_used, source.user_id, source.p_date, source.p_hour);
