authen.public

Parameters

Name	Type	Mode
i_row	jsonb	IN
i_conn_data	jsonb	IN
result	jsonb	OUT

Definition

DECLARE
    v_payload         jsonb;
    v_headers         jsonb;
    v_response        text;
    v_result          jsonb;
    v_sync_ts         timestamptz;
    v_sync_mode       text;
    v_http            int := 0;
    v_push_ok         boolean := false;
    v_log_ok          boolean := false;
    v_commit_ok       boolean := false;
    v_retry           int := 0;
    
    v_conn            text;
    v_trace_id        uuid;
    v_service_code    text;
    v_prop_uuid       uuid;
    v_api_key         text;
    v_endpoint        text;
    v_last_sync       timestamptz;
    v_max_days        int;
    
    sql_update_channel_response text;
    sql_commit_state            text;
    v_new_stamp                 timestamptz;
BEGIN
    -- แยกข้อมูลจาก input
    v_conn := i_row->>'connection';
    v_trace_id := (i_row->>'trace_id')::uuid;
    v_result := (i_row->'data'->'result_data'->'result');
    
    -- แยกข้อมูล connection
    v_service_code := i_conn_data->>'service_code';
    v_prop_uuid := (i_conn_data->>'prop_uuid')::uuid;
    v_api_key := i_conn_data->>'api_key';
    v_endpoint := i_conn_data->>'endpoint';
    v_last_sync := (i_conn_data->>'last_sync')::timestamptz;
    v_max_days := COALESCE((i_conn_data->>'max_days')::int, 400);
    
    -- กำหนด sync mode
    v_sync_mode := CASE WHEN v_last_sync IS NULL THEN 'FULL' ELSE 'ONCHANGE' END;
    
    -- กรณีไม่มีข้อมูลเปลี่ยนแปลง
    IF v_result IS NULL OR v_result = 'null'::jsonb
       OR (jsonb_typeof(v_result) = 'array' AND jsonb_array_length(v_result) = 0) THEN

        v_sync_ts := (i_row->'data'->'result_data'->'metadata'->>'sync_times')::timestamptz;
        IF v_sync_ts IS NULL THEN v_sync_ts := now(); END IF;
        v_new_stamp := v_sync_ts;

        INSERT INTO sync_stamp(service_code, property_id, last_sync, updated_at)
        VALUES (v_service_code, v_prop_uuid, v_new_stamp, now())
        ON CONFLICT (service_code, property_id)
        DO UPDATE SET last_sync = EXCLUDED.last_sync, updated_at = now();

        result := i_row || jsonb_build_object('sync_mode', v_sync_mode, 'note', 'no_changes');
        RETURN NEXT;
        RETURN;
    END IF;

    -- สร้าง payload และ headers
    v_payload := jsonb_build_object('values', v_result);
    v_headers := jsonb_build_object('user-api-key', COALESCE(v_api_key,''));

    -- Hardcode endpoint (Channex API)
    v_endpoint := 'https://app.channex.io/api/v1/restrictions';

    -- Debug: แสดง api_key และ endpoint ก่อน push
    PERFORM public.sp_system_notify(
        'SYNC RATE HTTP DEBUG',
        format('Trace: %s | Endpoint: %s | API-Key length: %s | API-Key prefix: %s',
               v_trace_id, v_endpoint,
               length(COALESCE(v_api_key,'')),
               left(COALESCE(v_api_key,'(empty)'), 8)),
        NULL, NULL
    );

    -- Retry logic: ลองสูงสุด 3 ครั้ง ด้วย exponential backoff
    FOR v_retry IN 1..3 LOOP
        BEGIN
            SELECT public.http_post(v_endpoint, v_payload, v_headers)
            INTO v_response;

            v_http := public.fn_http_post_status(v_response);
            v_push_ok := (v_http >= 200 AND v_http < 400);

            IF v_push_ok THEN
                EXIT;  -- สำเร็จแล้ว ไม่ต้อง retry ต่อ
            END IF;

            -- รอก่อน retry ครั้งถัดไป (exponential backoff)
            IF v_retry < 3 THEN
                PERFORM pg_sleep(2 ^ v_retry);
            END IF;
        EXCEPTION WHEN OTHERS THEN
            PERFORM public.sp_system_notify(
                'SYNC RATE HTTP RETRY',
                format('Trace: %s | Attempt: %s/3 | Error: %s', v_trace_id, v_retry, SQLERRM),
                NULL, NULL
            );
            IF v_retry < 3 THEN
                PERFORM pg_sleep(2 ^ v_retry);
            ELSE
                RAISE;  -- re-raise หลัง retry ครบ 3 ครั้ง
            END IF;
        END;
    END LOOP;

    -- แจ้งเตือนเฉพาะตอน fail/warn
    PERFORM public.sp_notify_channel_response(
        '[WARNING] SYNC RATE PUSH',
        v_conn,
        v_trace_id,
        v_response
    );

    IF v_push_ok THEN
        -- อัพเดท log
        sql_update_channel_response := format($SQL$
            UPDATE public.intf_sync_channel_logs
               SET channel_response = jsonb_build_object('raw', %L)
             WHERE trace_id = %L
        $SQL$, v_response, v_trace_id);

        BEGIN
            PERFORM dblink_exec(v_conn, sql_update_channel_response);
            v_log_ok := true;
        EXCEPTION WHEN OTHERS THEN
            v_log_ok := false;
            PERFORM public.sp_system_notify(
                'SYNC RATE LOG UPDATE FAILED',
                format('Conn: %s | Trace: %s | Error: %s', v_conn, v_trace_id, SQLERRM),
                NULL,
                '⚠️'
            );
        END;
    ELSE
        v_log_ok := false;
        PERFORM public.sp_system_notify(
            'SYNC RATE PUSH FAILED',
            format('HTTP %s | Mode: %s | Conn: %s | Endpoint: %s | Trace: %s',
                   v_http, v_sync_mode, v_conn, v_endpoint, v_trace_id),
            NULL,
            '❌'
        );
    END IF;

    IF v_push_ok AND v_log_ok THEN
        BEGIN
            PERFORM public.sp_commit_rate_state(v_trace_id, v_conn);
            v_commit_ok := true;
        EXCEPTION WHEN OTHERS THEN
            v_commit_ok := false;
            PERFORM public.sp_system_notify(
                'SYNC RATE COMMIT STATE FAILED',
                format('Trace: %s | Error: %s', v_trace_id, SQLERRM),
                NULL,
                '⚠️'
            );
        END;
    END IF;

    -- อัพเดท sync_stamp ถ้าทุกขั้นตอนสำเร็จ
    IF v_push_ok AND v_log_ok AND v_commit_ok THEN
        v_sync_ts := (i_row->'data'->'result_data'->'metadata'->>'sync_times')::timestamptz;
        IF v_sync_ts IS NULL THEN v_sync_ts := now(); END IF;
        v_new_stamp := v_sync_ts;

        INSERT INTO sync_stamp(service_code, property_id, last_sync, updated_at)
        VALUES (v_service_code, v_prop_uuid, v_new_stamp, now())
        ON CONFLICT (service_code, property_id)
        DO UPDATE SET last_sync = EXCLUDED.last_sync, updated_at = now();
    END IF;

    -- Return ผลลัพธ์
    result := (
        i_row
        || jsonb_build_object('sync_mode', v_sync_mode)
        || jsonb_build_object('http', v_http)
        || jsonb_build_object('push_ok', v_push_ok, 'log_ok', v_log_ok, 'commit_ok', v_commit_ok)
        || jsonb_build_object('channel_response', v_response)
    );
    RETURN NEXT;

EXCEPTION WHEN OTHERS THEN
    PERFORM public.sp_system_notify(
        'SYNC RATE PROCESS FAILED',
        format('Mode: %s | Conn: %s | Trace: %s | Error: %s',
               v_sync_mode, v_conn, v_trace_id, SQLERRM),
        NULL,
        '❌'
    );

    result := i_row || jsonb_build_object('sync_mode', v_sync_mode, 'error', SQLERRM);
    RETURN NEXT;
END;

fn_process_sync_result

Parameters

Definition