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

merge into {survey_questions_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:prompt as prompt,
        $1:type as type,
        $1:options as options,
        $1:group_id as group_id,
        $1:order_number as order_number,
        $1:is_active as is_active,
        $1:is_required as is_required,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        date(to_timestamp(updated_at)) as p_date,
        hour(to_timestamp(updated_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.prompt = source.prompt,
        target.type = source.type,
        target.options = source.options,
        target.group_id = source.group_id,
        target.order_number = source.order_number,
        target.is_active = source.is_active,
        target.is_required = source.is_required,
        target.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, prompt, type, options, group_id, order_number, is_active, is_required, created_at, updated_at, p_date, p_hour)
    values (source.id, source.prompt, source.type, source.options, source.group_id, source.order_number, source.is_active, source.is_required, source.created_at, source.updated_at, source.p_date, source.p_hour);

