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

merge into {user_captcha_state_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:last_captcha_completed_at as last_captcha_completed_at,
        $1:captcha_required as captcha_required,
        $1:failed_last_token_validation as failed_last_token_validation,
        $1:last_hcaptcha_score as last_hcaptcha_score,
        $1:gens_since_last_captcha as gens_since_last_captcha,
        $1:state_checks_since_last_gen as state_checks_since_last_gen,
        $1:total_interactive_challenges_completed as total_interactive_challenges_completed,
        $1:user_id as user_id,
        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.created_at = source.created_at,
        target.updated_at = source.updated_at,
        target.last_captcha_completed_at = source.last_captcha_completed_at,
        target.captcha_required = source.captcha_required,
        target.failed_last_token_validation = source.failed_last_token_validation,
        target.last_hcaptcha_score = source.last_hcaptcha_score,
        target.gens_since_last_captcha = source.gens_since_last_captcha,
        target.state_checks_since_last_gen = source.state_checks_since_last_gen,
        target.total_interactive_challenges_completed = source.total_interactive_challenges_completed,
        target.user_id = source.user_id,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, last_captcha_completed_at, captcha_required, failed_last_token_validation, last_hcaptcha_score, gens_since_last_captcha, state_checks_since_last_gen, total_interactive_challenges_completed, user_id, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.last_captcha_completed_at, source.captcha_required, source.failed_last_token_validation, source.last_hcaptcha_score, source.gens_since_last_captcha, source.state_checks_since_last_gen, source.total_interactive_challenges_completed, source.user_id, source.p_date, source.p_hour);

