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

merge into {hook_comment_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:content as content,
        $1:track_time_second as track_time_second,
        $1:is_deleted as is_deleted,
        $1:deleted_by as deleted_by,
        $1:num_likes as num_likes,
        $1:num_replies as num_replies,
        parse_json($1:raw_mentions) as raw_mentions,
        $1:hook_id as hook_id,
        $1:parent_id as parent_id,
        $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.content = source.content,
        target.track_time_second = source.track_time_second,
        target.is_deleted = source.is_deleted,
        target.deleted_by = source.deleted_by,
        target.num_likes = source.num_likes,
        target.num_replies = source.num_replies,
        target.raw_mentions = source.raw_mentions,
        target.hook_id = source.hook_id,
        target.parent_id = source.parent_id,
        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, content, track_time_second, is_deleted, deleted_by, num_likes, num_replies, raw_mentions, hook_id, parent_id, user_id, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.content, source.track_time_second, source.is_deleted, source.deleted_by, source.num_likes, source.num_replies, source.raw_mentions, source.hook_id, source.parent_id, source.user_id, source.p_date, source.p_hour);


