prompt --application/deployment/install/install_chatbot_procedures
begin
--   Manifest
--     INSTALL: INSTALL-Chatbot Procedures
--   Manifest End
wwv_flow_imp.component_begin (
 p_version_yyyy_mm_dd=>'2024.05.31'
,p_release=>'24.1.2'
,p_default_workspace_id=>5893639279394007
,p_default_application_id=>20011
,p_default_id_offset=>0
,p_default_owner=>'DEMO'
);
wwv_flow_imp_shared.create_install_script(
 p_id=>wwv_flow_imp.id(16013830446391244)
,p_install_id=>wwv_flow_imp.id(16009640173256284)
,p_name=>'Chatbot Procedures'
,p_sequence=>20
,p_script_type=>'INSTALL'
,p_script_clob=>wwv_flow_string.join(wwv_flow_t_varchar2(
'create or replace procedure "SEND_MESSAGE_TO_GEN_AI" (',
'    ov_retcode      OUT VARCHAR2,',
'    ov_errmsg       OUT VARCHAR2,',
'    in_chat_id      IN  NUMBER,',
'    in_chat_model   IN  VARCHAR2,',
'    iv_app_user     IN  VARCHAR2)',
'AS',
'    lc_json_payload     CLOB;',
'    lv_method           VARCHAR2(30)  := ''POST'';',
'    lc_req_body         CLOB;',
'    lv_url              VARCHAR(4000) := ''http://localhost:8000/send_message_to_gen_ai/tongyi/'';',
'    lc_res_body         CLOB;',
'    lj_object           JSON_OBJECT_T;',
'    lv_result           VARCHAR2(4000);',
'    lv_result_message   VARCHAR2(20000);',
'    lv_message_res      CLOB;',
'BEGIN',
'    ov_retcode := ''0'';',
'    ov_errmsg := NULL;',
'--',
'    SELECT',
'        JSON_OBJECT(',
'            KEY ''model'' VALUE in_chat_model,',
'            KEY ''messages'' VALUE(',
'                JSON_ARRAYAGG(',
'                    JSON_OBJECT(',
'                        KEY',
'                            ''role''',
'                        VALUE',
'                            CASE',
'                                ccm.message_type',
'                            WHEN ''U'' THEN ''user''',
'                            WHEN ''M'' THEN ''assistant''',
'                            END,',
'                        KEY ''text'' VALUE ccm.message_text',
'                    )',
'                    ORDER BY ccm.message_id',
'                )',
'            )',
'        )',
'    INTO',
'        lc_req_body',
'    FROM',
'        cb_chat_messages ccm',
'    WHERE',
'        ccm.chat_id = in_chat_id',
'    ORDER BY',
'        ccm.message_id;',
'    ',
'    -- Call Web Service',
'    apex_web_service.g_request_headers(1).name := ''Content-Type'';',
'    apex_web_service.g_request_headers(1).Value := ''application/json; charset=utf-8'';',
'',
'    lc_res_body := APEX_WEB_SERVICE.make_rest_request(',
'        p_url         => lv_url,',
'        p_http_method => lv_method,',
'        p_body        => lc_req_body',
'    );',
'    -- Parse Service Result',
'    lj_object := JSON_OBJECT_T.parse(lc_res_body);',
'    lv_result := lj_object.get_string(''result'');',
'    lv_result_message := lj_object.get_string(''result_message'');',
'    lv_message_res := lj_object.get_string(''message_res'');',
'',
'    -- Write AI answer to table',
'    INSERT INTO cb_chat_messages(',
'        chat_id,',
'        message_type,',
'        message_text,',
'        user_name,',
'        created_by,',
'        last_updated_by',
'    )',
'    VALUES(',
'        in_chat_id,         -- chat_id',
'        ''M'',                -- message_type, Model',
'        lv_message_res,     -- message_text,',
'        ''Model'',            -- user_name,',
'        iv_app_user,        -- created_by,',
'        iv_app_user         -- last_updated_by',
'    );',
'',
'EXCEPTION',
'    WHEN OTHERS THEN',
'        -- Exception',
'        ov_retcode := ''1'';',
'        ov_errmsg := SQLERRM;',
'',
'END SEND_MESSAGE_TO_GEN_AI;',
'/ '))
);
wwv_flow_imp_shared.create_install_object(
 p_id=>wwv_flow_imp.id(16013946038391261)
,p_script_id=>wwv_flow_imp.id(16013830446391244)
,p_object_owner=>'#OWNER#'
,p_object_type=>'PROCEDURE'
,p_object_name=>'SEND_MESSAGE_TO_GEN_AI'
);
wwv_flow_imp.component_end;
end;
/
