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

MERGE INTO {clip_table_name} AS target
USING 
(
    SELECT 
        $1:id AS id,
        $1:created_at AS created_at,
        $1:updated_at AS updated_at,
        $1:time_used AS time_used,
        $1:metadata AS metadata,
        $1:user_id AS user_id,
        $1:status AS status,
        $1:discord_message_id AS discord_message_id,
        $1:prompt_id AS prompt_id,
        $1:request_id AS request_id,
        $1:is_generated AS is_generated,
        $1:s3_id AS s3_id,
        $1:upvote_count AS upvote_count,
        $1:batch_index AS batch_index,
        $1:model_name AS model_name,
        $1:prompt_text AS prompt_text,
        $1:daily_theme_id AS daily_theme_id,
        $1:is_deleted AS is_deleted,
        $1:image_s3_id AS image_s3_id,
        $1:is_public AS is_public,
        $1:dislike_count AS dislike_count,
        $1:flag_count AS flag_count,
        $1:play_count AS play_count,
        $1:skip_count AS skip_count,
        $1:title AS title,
        $1:slug AS slug,
        $1:created_session_id AS created_session_id,
        $1:allow_comments AS allow_comments,
        $1:creation_source AS creation_source,
        $1:is_hidden AS is_hidden,
        $1:display_tags AS display_tags,
        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.time_used = source.time_used,
        target.metadata = source.metadata,
        target.user_id = source.user_id,
        target.status = source.status,
        target.discord_message_id = source.discord_message_id,
        target.prompt_id = source.prompt_id,
        target.request_id = source.request_id,
        target.is_generated = source.is_generated,
        target.s3_id = source.s3_id,
        target.upvote_count = source.upvote_count,
        target.batch_index = source.batch_index,
        target.model_name = source.model_name,
        target.prompt_text = source.prompt_text,
        target.daily_theme_id = source.daily_theme_id,
        target.is_deleted = source.is_deleted,
        target.image_s3_id = source.image_s3_id,
        target.is_public = source.is_public,
        target.dislike_count = source.dislike_count,
        target.flag_count = source.flag_count,
        target.play_count = source.play_count,
        target.skip_count = source.skip_count,
        target.title = source.title,
        target.slug = source.slug,
        target.created_session_id = source.created_session_id,
        target.allow_comments = source.allow_comments,
        target.creation_source = source.creation_source,
        target.is_hidden = source.is_hidden,
        target.display_tags = source.display_tags,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
WHEN NOT MATCHED THEN
    INSERT (
        id, created_at, updated_at, time_used, metadata, user_id, status, 
        discord_message_id, prompt_id, request_id, is_generated, s3_id, 
        upvote_count, batch_index, model_name, prompt_text, daily_theme_id, 
        is_deleted, image_s3_id, is_public, dislike_count, flag_count, 
        play_count, skip_count, title, slug, p_date, p_hour,
        created_session_id, allow_comments, creation_source, is_hidden, display_tags
    )
    VALUES (
        source.id, source.created_at, source.updated_at, source.time_used, 
        source.metadata, source.user_id, source.status, source.discord_message_id, 
        source.prompt_id, source.request_id, source.is_generated, source.s3_id, 
        source.upvote_count, source.batch_index, source.model_name, source.prompt_text, 
        source.daily_theme_id, source.is_deleted, source.image_s3_id, source.is_public, 
        source.dislike_count, source.flag_count, source.play_count, source.skip_count, 
        source.title, source.slug, source.p_date, source.p_hour,
        source.created_session_id, source.allow_comments, source.creation_source, 
        source.is_hidden, source.display_tags
    );

