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

merge into {video_hook_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:original_clip as original_clip,
        $1:status as status,
        $1:title as title,
        $1:start_clip_timestamp as start_clip_timestamp,
        $1:end_clip_timestamp as end_clip_timestamp,
        $1:rendered_video_s3_id as rendered_video_s3_id,
        $1:rendered_video_preview_s3_id as rendered_video_preview_s3_id,
        $1:thumbnail_image_s3_id as thumbnail_image_s3_id,
        $1:raw_video_upload_id as raw_video_upload_id,
        $1:user_id as user_id,
        $1:is_test as is_test,
        $1:allow_comments as allow_comments,
        $1:caption as caption,
        $1:show_lyrics as show_lyrics,
        $1:content_rating as content_rating,
        parse_json($1:content_rating_tags) as content_rating_tags,
        $1:video_duration as video_duration,
        parse_json($1:video_streaming_resolutions) as video_streaming_resolutions,
        $1:is_deleted as is_deleted,
        $1:rendered_watermarked_video_s3_id as rendered_watermarked_video_s3_id,
        $1:staff_review_status as staff_review_status,
        parse_json($1:video_upload_ids) as video_upload_ids,
        $1:creation_source as creation_source,
        $1:metadata_id as metadata_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.original_clip = source.original_clip,
        target.status = source.status,
        target.title = source.title,
        target.start_clip_timestamp = source.start_clip_timestamp,
        target.end_clip_timestamp = source.end_clip_timestamp,
        target.rendered_video_s3_id = source.rendered_video_s3_id,
        target.rendered_video_preview_s3_id = source.rendered_video_preview_s3_id,
        target.thumbnail_image_s3_id = source.thumbnail_image_s3_id,
        target.raw_video_upload_id = source.raw_video_upload_id,
        target.user_id = source.user_id,
        target.is_test = source.is_test,
        target.allow_comments = source.allow_comments,
        target.caption = source.caption,
        target.show_lyrics = source.show_lyrics,
        target.content_rating = source.content_rating,
        target.content_rating_tags = source.content_rating_tags,
        target.video_duration = source.video_duration,
        target.video_streaming_resolutions = source.video_streaming_resolutions,
        target.is_deleted = source.is_deleted,
        target.rendered_watermarked_video_s3_id = source.rendered_watermarked_video_s3_id,
        target.staff_review_status = source.staff_review_status,
        target.video_upload_ids = source.video_upload_ids,
        target.creation_source = source.creation_source,
        target.metadata_id = source.metadata_id,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, original_clip, status, title, start_clip_timestamp, end_clip_timestamp, rendered_video_s3_id, rendered_video_preview_s3_id, thumbnail_image_s3_id, raw_video_upload_id, user_id, is_test, allow_comments, caption, show_lyrics, content_rating, content_rating_tags, video_duration, video_streaming_resolutions, is_deleted, rendered_watermarked_video_s3_id, staff_review_status, video_upload_ids, creation_source, metadata_id, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.original_clip, source.status, source.title, source.start_clip_timestamp, source.end_clip_timestamp, source.rendered_video_s3_id, source.rendered_video_preview_s3_id, source.thumbnail_image_s3_id, source.raw_video_upload_id, source.user_id, source.is_test, source.allow_comments, source.caption, source.show_lyrics, source.content_rating, source.content_rating_tags, source.video_duration, source.video_streaming_resolutions, source.is_deleted, source.rendered_watermarked_video_s3_id, source.staff_review_status, source.video_upload_ids, source.creation_source, source.metadata_id, source.p_date, source.p_hour);

