authen.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_trace_id         uuid := gen_random_uuid();

v_conns            text[];
    v_conn             text;

v_row              jsonb;
    v_success          boolean := true;

r_enabled          boolean;
    r_prop_uuid        uuid;
    r_max_sync_days    integer;
    r_last_sync        timestamptz;
    r_check_overall    boolean;
    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_roomavail  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.';
        RETURN;
    END IF;

FOREACH v_conn IN ARRAY v_conns LOOP
        BEGIN
            -- อ่าน NBIS params จาก DB ปลายทางผ่าน dblink
            sql_call_get_params := $SQL$
                SELECT jsonb_build_object(
                    'enabled',        COALESCE(fn_intf_param('NBIS','INTERFACE_ROOMAVAIL_ENABLED','false')::boolean, false),
                    'max_days',       COALESCE(fn_intf_param('NBIS','MAX_SYNC_DAYS','400')::int, 400),
                    'check_overall',  COALESCE(fn_intf_param('NBIS','CHECK_OVERALL_AVAIL','false')::boolean, false),
                    'interface_code', COALESCE(NULLIF(fn_intf_param('NBIS','INTERFACE_CODE','')::text,''), 'CHANNEX')
                )::jsonb
            $SQL$;

BEGIN
                SELECT 
                    (j->>'enabled')::boolean,
                    (j->>'max_days')::int,
                    (j->>'check_overall')::boolean,
                    (j->>'interface_code')::text
                INTO r_enabled, r_max_sync_days, r_check_overall, 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
                v_success := false;
                RAISE WARNING 'Failed to read NBIS params on % - %', v_conn, SQLERRM;
                CONTINUE;
            END;

IF NOT r_enabled THEN
                RAISE NOTICE 'Remote interface disabled on %; skip.', v_conn;
                CONTINUE;
            END IF;

-- อ่าน Channel params (PROPERTY_ID, API-KEY, ENDPOINT) จาก DB ปลายทางผ่าน 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
                v_success := false;
                RAISE WARNING 'Failed to read channel params (% on %) - %', r_channel_code, v_conn, SQLERRM;
                CONTINUE;
            END;

IF r_prop_uuid IS NULL THEN
                RAISE WARNING 'No PROPERTY_ID for channel % on %; skip.', r_channel_code, v_conn;
                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;
                CONTINUE;
            END IF;

-- อ่าน last_sync ของ property นี้
            SELECT last_sync
              INTO r_last_sync
              FROM sync_stamp
             WHERE service_code = 'NBIS' AND property_id = r_prop_uuid;

-- เรียก sp_get_sync_roomavail ใน DB ปลายทางผ่าน dblink
            sql_call_sp_get_sync_roomavail := format($Q$
                SELECT public.sp_get_sync_roomavail(
                    jsonb_build_object(
                        'trace_id', gen_random_uuid(),
                        'db_name', '%s',
                        'source', 'cron.sync_room_avail_interface',
                        'sync_times', NOW()
                    ),
                    jsonb_build_object(
                        'last_sync', %L::timestamptz,
                        'roomtypeno', NULL,
                        'agents', NULL,
                        'max_days', %s,
                        'check_overall', %L::boolean
                    )
                )::jsonb
            $Q$, current_database(), r_last_sync, COALESCE(r_max_sync_days, 400), COALESCE(r_check_overall, false));

FOR v_row IN
                SELECT jsonb_build_object(
                    'connection', v_conn,
                    'data', value
                )
                FROM dblink(v_conn, sql_call_sp_get_sync_roomavail) AS t(value jsonb)
            LOOP
                DECLARE
                    v_payload   jsonb;
                    v_headers   jsonb;
                    v_response  text;
                    v_result    jsonb;
                BEGIN
                    v_result := (v_row->'data'->'result_data'->'result');

-- ไม่มี data ให้ sync -> อัปเดต stamp แล้วข้าม
                    IF v_result IS NULL OR v_result = 'null'::jsonb
                       OR (jsonb_typeof(v_result) = 'array' AND jsonb_array_length(v_result) = 0) THEN
                        RAISE NOTICE 'No availability changes on %; skipping.', v_conn;

v_new_stamp := (v_row->'data'->'result_data'->'metadata'->>'sync_times')::timestamptz;
                        IF v_new_stamp IS NULL THEN v_new_stamp := now(); END IF;

INSERT INTO sync_stamp(service_code, property_id, last_sync, updated_at)
                        VALUES ('NBIS', 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;
                        CONTINUE;
                    END IF;

-- เตรียม payload + header แล้วยิง API
                    v_payload := jsonb_build_object('values', v_result);
                    v_headers := jsonb_build_object('user-api-key', COALESCE(r_api_key,''));

SELECT public.http_post(r_endpoint, v_payload, v_headers)
                      INTO v_response;

RAISE NOTICE 'API Response (%): %', v_conn, v_response;

-- อัปเดต stamp ตาม metadata.sync_times (fallback = now())
                    v_new_stamp := (v_row->'data'->'result_data'->'metadata'->>'sync_times')::timestamptz;
                    IF v_new_stamp IS NULL THEN v_new_stamp := now(); END IF;

INSERT INTO sync_stamp(service_code, property_id, last_sync, updated_at)
                    VALUES ('NBIS', 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;

EXCEPTION WHEN OTHERS THEN
                    v_success := false;
                    RAISE WARNING 'API POST failed on % - %', v_conn, SQLERRM;
                    CONTINUE;
                END;
            END LOOP;

EXCEPTION WHEN OTHERS THEN
            v_success := false;
            RAISE WARNING 'Connection failed: % - %', v_conn, SQLERRM;
            CONTINUE;
        END;
    END LOOP;

RETURN;
END;

sp_sync_room_avail_interface

Parameters

Definition