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

merge into {bot_jail_table_name} as target
using 
(
    select 
        $1:id as id,
        $1:created_at as created_at,
        $1:updated_at as updated_at,
        $1:username as username,
        $1:status as status,
        $1:total_jailed_count as total_jailed_count,
        $1:latest_jailed_reason as latest_jailed_reason,
        $1:latest_jailed_at as latest_jailed_at,
        $1:latest_released_at as latest_released_at,
        $1:total_copycat_violation_count as total_copycat_violation_count,
        $1:latest_copycat_violation_at as latest_copycat_violation_at,
        $1:total_api_violation_count as total_api_violation_count,
        $1:api_counter as api_counter,
        $1:latest_api_violation_at as latest_api_violation_at,
        $1:total_hcaptcha_violation_count as total_hcaptcha_violation_count,
        $1:hcaptcha_score as hcaptcha_score,
        $1:latest_hcaptcha_violation_at as latest_hcaptcha_violation_at,
        $1:user_id as user_id,
        $1:copycat_name as copycat_name,
        $1:user_ip as user_ip,
        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.username = source.username,
        target.status = source.status,
        target.total_jailed_count = source.total_jailed_count,
        target.latest_jailed_reason = source.latest_jailed_reason,
        target.latest_jailed_at = source.latest_jailed_at,
        target.latest_released_at = source.latest_released_at,
        target.total_copycat_violation_count = source.total_copycat_violation_count,
        target.latest_copycat_violation_at = source.latest_copycat_violation_at,
        target.total_api_violation_count = source.total_api_violation_count,
        target.api_counter = source.api_counter,
        target.latest_api_violation_at = source.latest_api_violation_at,
        target.total_hcaptcha_violation_count = source.total_hcaptcha_violation_count,
        target.hcaptcha_score = source.hcaptcha_score,
        target.latest_hcaptcha_violation_at = source.latest_hcaptcha_violation_at,
        target.user_id = source.user_id,
        target.copycat_name = source.copycat_name,
        target.user_ip = source.user_ip,
        target.p_date = source.p_date,
        target.p_hour = source.p_hour
when not matched then
    insert (id, created_at, updated_at, username, status, total_jailed_count, latest_jailed_reason, latest_jailed_at, latest_released_at, total_copycat_violation_count, latest_copycat_violation_at, total_api_violation_count, api_counter, latest_api_violation_at, total_hcaptcha_violation_count, hcaptcha_score, latest_hcaptcha_violation_at, user_id, copycat_name, user_ip, p_date, p_hour)
    values (source.id, source.created_at, source.updated_at, source.username, source.status, source.total_jailed_count, source.latest_jailed_reason, source.latest_jailed_at, source.latest_released_at, source.total_copycat_violation_count, source.latest_copycat_violation_at, source.total_api_violation_count, source.api_counter, source.latest_api_violation_at, source.total_hcaptcha_violation_count, source.hcaptcha_score, source.latest_hcaptcha_violation_at, source.user_id, source.copycat_name, source.user_ip, source.p_date, source.p_hour);

