authen.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_service_code text;

v_conns  text[];
    v_conn   text;
    v_row    jsonb;

r_enabled       boolean;
    r_prop_uuid     uuid;
    r_max_sync_days integer;
    r_last_sync     timestamptz;
    r_channel_code  text;
    r_api_key       text;
    r_endpoint      text;

sql_call_get_params           text;
    sql_call_get_channel_params   text;
    sql_call_sp_get_sync_roomrate text;
    sql_update_channel_response   text;
    sql_commit_state              text;

v_new_stamp timestamptz;
BEGIN
    SELECT array_agg(fn_prop_connection(id))
      INTO v_conns
      FROM property
     WHERE enabled = true;

IF v_conns IS NULL OR array_length(v_conns,1) IS NULL THEN
        --RAISE NOTICE 'No property connections found for sync.';
        PERFORM public.sp_system_notify(
            'SYNC RATE SKIP',
            'No property connections found for sync.',
            NULL,
            '⚠️'
        );
        RETURN;
    END IF;

FOREACH v_conn IN ARRAY v_conns LOOP
        DECLARE
            v_trace_id uuid := gen_random_uuid(); -- trace ต่อ connection/prop
        BEGIN

sql_call_get_params := $SQL$
                SELECT jsonb_build_object(
                    'enabled',        COALESCE(fn_intf_param('NBIS','INTERFACE_RATE_ENABLED','false')::boolean, false),
                    'max_days',       COALESCE(fn_intf_param('NBIS','MAX_SYNC_DAYS','400')::int, 400),
                    'interface_code', COALESCE(NULLIF(fn_intf_param('NBIS','INTERFACE_CODE','')::text,''), 'CHANNEX')
                )::jsonb
            $SQL$;

BEGIN
                SELECT
                    (j->>'enabled')::boolean,
                    (j->>'max_days')::int,
                    (j->>'interface_code')::text
                INTO r_enabled, r_max_sync_days, r_channel_code
                FROM (
                    SELECT value AS j
                    FROM dblink(v_conn, sql_call_get_params) AS t(value jsonb)
                ) s;
            EXCEPTION WHEN OTHERS THEN
                --RAISE WARNING 'Failed to read NBIS params on % - %', v_conn, SQLERRM;
                PERFORM public.sp_system_notify(
                    'SYNC RATE FAILED',
                    format('Failed to read NBIS params | Conn: %s | Error: %s', v_conn, SQLERRM),
                    NULL,
                    '❌'
                );
                CONTINUE;
            END;

IF NOT r_enabled THEN
                CONTINUE;
            END IF;

v_service_code := 'SYNC-RATE-INTERFACE-' || r_channel_code;

-- 2) อ่าน channel params (ฝั่ง PMS ผ่าน dblink)
            sql_call_get_channel_params := format($SQL$
                SELECT jsonb_build_object(
                    'prop_uuid', fn_intf_param('%1$s','PROPERTY_ID','')::uuid,
                    'api_key',   fn_intf_param('%1$s','API-KEY',''),
                    'endpoint',  fn_intf_param('%1$s','ENDPOINT','')
                )::jsonb
            $SQL$, r_channel_code);

BEGIN
                SELECT
                    (j->>'prop_uuid')::uuid,
                    (j->>'api_key')::text,
                    (j->>'endpoint')::text
                INTO r_prop_uuid, r_api_key, r_endpoint
                FROM (
                    SELECT value AS j
                    FROM dblink(v_conn, sql_call_get_channel_params) AS t(value jsonb)
                ) s;
            EXCEPTION WHEN OTHERS THEN
                --RAISE WARNING 'Failed to read channel params (% on %) - %', r_channel_code, v_conn, SQLERRM;
                PERFORM public.sp_system_notify(
                    'SYNC RATE FAILED',
                    format('Failed to read channel params | Channel: %s | Conn: %s | Error: %s',
                           r_channel_code, v_conn, SQLERRM),
                    NULL,
                    '❌'
                );
                CONTINUE;
            END;

IF r_prop_uuid IS NULL THEN
                --RAISE WARNING 'No PROPERTY_ID for channel % on %; skip.', r_channel_code, v_conn;
                PERFORM public.sp_system_notify(
                    'SYNC RATE FAILED',
                    format('Missing PROPERTY_ID | Channel: %s | Conn: %s', r_channel_code, v_conn),
                    NULL,
                    '❌'
                );
                CONTINUE;
            END IF;

IF r_endpoint IS NULL OR r_endpoint = '' THEN
                --RAISE WARNING 'No ENDPOINT for channel % on %; skip.', r_channel_code, v_conn;
                PERFORM public.sp_system_notify(
                    'SYNC RATE FAILED',
                    format('Missing ENDPOINT | Channel: %s | Conn: %s', r_channel_code, v_conn),
                    NULL,
                    '❌'
                );
                CONTINUE;
            END IF;

SELECT last_sync
              INTO r_last_sync
              FROM sync_stamp
             WHERE service_code = v_service_code
               AND property_id = r_prop_uuid;

sql_call_sp_get_sync_roomrate := format($Q$
                SELECT public.sp_get_sync_roomrate(
                    jsonb_build_object(
                        'trace_id', %L,
                        'db_name', '%s',
                        'source', 'cron.sp_sync_rate_interface',
                        'sync_times', NOW()
                    )::jsonb,
                    jsonb_build_object(
                        'last_sync', %L::timestamptz,
                        'sync_all', %s,
                        'roomtypeno', NULL,
                        'rate_id', NULL,
                        'rateplan_id', NULL,
                        'max_days', %s,
                        'property_id', %L
                    )::jsonb
                )::jsonb
            $Q$,
                v_trace_id,
                current_database(),
                r_last_sync,
                CASE WHEN r_last_sync IS NULL THEN 'true' ELSE 'false' END,
                COALESCE(r_max_sync_days, 400),
                r_prop_uuid::text
            );

FOR v_row IN
                SELECT jsonb_build_object('connection', v_conn, 'data', value)
                FROM dblink(v_conn, sql_call_sp_get_sync_roomrate) AS t(value jsonb)
            LOOP
                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_m text[];
                BEGIN
                    v_result := (v_row->'data'->'result_data'->'result');
                    v_sync_mode := CASE WHEN r_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 := (v_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, r_prop_uuid, v_new_stamp, now())
                        ON CONFLICT (service_code, property_id)
                        DO UPDATE SET last_sync = EXCLUDED.last_sync, updated_at = now();

RETURN NEXT (v_row || jsonb_build_object('sync_mode', v_sync_mode, 'note', 'no_changes'));
                        CONTINUE;
                    END IF;

v_payload := jsonb_build_object('values', v_result);
                    v_headers := jsonb_build_object('user-api-key', COALESCE(r_api_key,''));

r_endpoint := 'https://app.channex.io/api/v1/restrictions';

SELECT public.http_post(r_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);

-- แจ้งเฉพาะตอน fail/warn เท่านั้น
                    PERFORM public.sp_notify_channel_response(
                    'SYNC RATE PUSH [Warn/Fail]',
                    v_conn,
                    v_trace_id,
                    v_response
                    );

IF v_push_ok THEN
                        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;
                            --RAISE WARNING 'Failed to update PMS log on % - %', v_conn, SQLERRM;
                            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;
                        --RAISE WARNING 'API POST failed on % - %', v_conn, SQLERRM;
                        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, r_endpoint, v_trace_id),
                            NULL,
                            '❌'
                        );
                    END IF;

IF v_push_ok AND v_log_ok THEN
                            sql_commit_state := format($SQL$
                                SELECT public.sp_commit_rate_state(%L)::text
                            $SQL$, v_trace_id);

BEGIN
                                PERFORM 1
                                FROM dblink(v_conn, sql_commit_state) AS t(value text);

v_commit_ok := true;
                            EXCEPTION WHEN OTHERS THEN
                                v_commit_ok := false;
                                PERFORM public.sp_system_notify(
                                    'SYNC RATE COMMIT STATE FAILED',
                                    format('Conn: %s | Trace: %s | Error: %s', 'AUTHENTICATED_DB', v_trace_id, SQLERRM),
                                    NULL,
                                    '⚠️'
                                );
                            END;
                        END IF;

IF v_push_ok AND v_log_ok AND v_commit_ok THEN
                        v_sync_ts := (v_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;

RETURN NEXT (
                        v_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)
                    );

EXCEPTION WHEN OTHERS THEN
                    --RAISE WARNING 'Process failed on % - %', v_conn, SQLERRM;
                    PERFORM public.sp_system_notify(
                        'SYNC RATE PROCESS FAILED',
                        format('Mode: %s | Conn: %s | Trace: %s | Error: %s',
                               v_sync_mode, 'AUTHENTICATED_DB', v_trace_id, SQLERRM),
                        NULL,
                        '❌'
                    );

RETURN NEXT (v_row || jsonb_build_object('sync_mode', v_sync_mode, 'error', SQLERRM));
                    CONTINUE;
                END;
            END LOOP;

EXCEPTION WHEN OTHERS THEN
            --RAISE WARNING 'Connection failed: % - %', v_conn, SQLERRM;
            PERFORM public.sp_system_notify(
                'SYNC RATE CONNECTION FAILED',
                format('Conn: %s | Trace: %s | Error: %s', v_conn, v_trace_id, SQLERRM),
                NULL,
                '❌'
            );

RETURN NEXT jsonb_build_object('connection', v_conn, 'trace_id', v_trace_id, 'error', SQLERRM);
            CONTINUE;
        END;
    END LOOP;

RETURN;
END;