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

merge into {label_maker_projects_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:project_name as project_name,
        $1:question as question,
        $1:instructions as instructions,
        $1:preference_dimensions as preference_dimensions,
        $1:target_review_count as target_review_count,
        $1:statsig_gate as statsig_gate,
        $1:created_by as created_by,
        $1:enabled as enabled,
        $1:priority as priority,
        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.project_name = source.project_name,
        target.question = source.question,
        target.instructions = source.instructions,
        target.preference_dimensions = source.preference_dimensions,
        target.target_review_count = source.target_review_count,
        target.statsig_gate = source.statsig_gate,
        target.created_by = source.created_by,
        target.enabled = source.enabled,
        target.priority = source.priority,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, project_name, question, instructions, preference_dimensions, target_review_count, statsig_gate, created_by, enabled, priority, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.project_name, source.question, source.instructions, source.preference_dimensions, source.target_review_count, source.statsig_gate, source.created_by, source.enabled, source.priority, source.p_date, source.p_hour);
