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

merge into {grouped_user_notification_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:type as type,
        $1:content_id as content_id,
        $1:sender_id_1 as sender_id_1,
        $1:sender_id_2 as sender_id_2,
        $1:count as count,
        $1:is_read as is_read,
        $1:is_active as is_active,
        $1:last_opeartion_at as last_opeartion_at,
        $1:send_at as send_at,
        $1:user_id as user_id,
        $1:sender_id_3 as sender_id_3,
        $1:seocndary_conetent_id as seocndary_conetent_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.type = source.type,
        target.content_id = source.content_id,
        target.sender_id_1 = source.sender_id_1,
        target.sender_id_2 = source.sender_id_2,
        target.count = source.count,
        target.is_read = source.is_read,
        target.is_active = source.is_active,
        target.last_opeartion_at = source.last_opeartion_at,
        target.send_at = source.send_at,
        target.user_id = source.user_id,
        target.sender_id_3 = source.sender_id_3,
        target.seocndary_conetent_id = source.seocndary_conetent_id
when not matched then
    insert (id, created_at, updated_at, type, content_id, sender_id_1, sender_id_2, count, is_read, is_active, last_opeartion_at, send_at, user_id, sender_id_3, seocndary_conetent_id, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.type, source.content_id, source.sender_id_1, source.sender_id_2, source.count, source.is_read, source.is_active, source.last_opeartion_at, source.send_at, source.user_id, source.sender_id_3, source.seocndary_conetent_id, source.p_date, source.p_hour);

