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

merge into {generated_lyrics_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:interaction_type as interaction_type,
        $1:resulted_in_error as resulted_in_error,
        $1:is_complete as is_complete,
        $1:lyrics_request_id as lyrics_request_id,
        $1:generation_request_id as generation_request_id,
        $1:create_session_token as create_session_token,
        $1:prompt_text as prompt_text,
        $1:model_name as model_name,
        $1:frontend_model_selection as frontend_model_selection,
        $1:model_generated_title as model_generated_title,
        $1:model_generated_lyrics as model_generated_lyrics,
        $1:model_generated_tags as model_generated_tags,
        $1:title_used as title_used,
        $1:tags_used as tags_used,
        $1:create_custom_used_model_output as create_custom_used_model_output,
        $1:user_id as user_id,
        $1:selected_for_ab as selected_for_ab,
        $1:context_lyrics_edit as context_lyrics_edit,
        $1:context_lyrics_prefix as context_lyrics_prefix,
        $1:context_lyrics_suffix as context_lyrics_suffix,
        $1:infill_undone as infill_undone,
        $1:platform as platform,
        $1:source as source,
        $1:experiment_id as experiment_id,
        $1:lyrics_model_key as lyrics_model_key,
        $1:position as position,
        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.interaction_type = source.interaction_type,
        target.resulted_in_error = source.resulted_in_error,
        target.is_complete = source.is_complete,
        target.lyrics_request_id = source.lyrics_request_id,
        target.generation_request_id = source.generation_request_id,
        target.create_session_token = source.create_session_token,
        target.prompt_text = source.prompt_text,
        target.model_name = source.model_name,
        target.frontend_model_selection = source.frontend_model_selection,
        target.model_generated_title = source.model_generated_title,
        target.model_generated_lyrics = source.model_generated_lyrics,
        target.model_generated_tags = source.model_generated_tags,
        target.title_used = source.title_used,
        target.tags_used = source.tags_used,
        target.create_custom_used_model_output = source.create_custom_used_model_output,
        target.user_id = source.user_id,
        target.selected_for_ab = source.selected_for_ab,
        target.context_lyrics_edit = source.context_lyrics_edit,
        target.context_lyrics_prefix = source.context_lyrics_prefix,
        target.context_lyrics_suffix = source.context_lyrics_suffix,
        target.infill_undone = source.infill_undone,
        target.platform = source.platform,
        target.source = source.source,
        target.experiment_id = source.experiment_id,
        target.lyrics_model_key = source.lyrics_model_key,
        target.position = source.position,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, interaction_type, resulted_in_error, is_complete, lyrics_request_id, generation_request_id, create_session_token, prompt_text, model_name, frontend_model_selection, model_generated_title, model_generated_lyrics, model_generated_tags, title_used, tags_used, create_custom_used_model_output, user_id, selected_for_ab, context_lyrics_edit, context_lyrics_prefix, context_lyrics_suffix, infill_undone, platform, source, experiment_id, lyrics_model_key, position, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.interaction_type, source.resulted_in_error, source.is_complete, source.lyrics_request_id, source.generation_request_id, source.create_session_token, source.prompt_text, source.model_name, source.frontend_model_selection, source.model_generated_title, source.model_generated_lyrics, source.model_generated_tags, source.title_used, source.tags_used, source.create_custom_used_model_output, source.user_id, source.selected_for_ab, source.context_lyrics_edit, source.context_lyrics_prefix, source.context_lyrics_suffix, source.infill_undone, source.platform, source.source, source.experiment_id, source.lyrics_model_key, source.position, source.p_date, source.p_hour);

