create or replace function fnc_26ai_food( p_image_id in number, 
                                          p_comp_id in varchar2,
                                          p_credential in varchar2 default 'OCI_CRED')
return clob
as
/*

  Criado por: fernando.leal@oracle.com
  Data: Oct/2025
  Objetivo: demonstrar casos de uso de Food & Nutrition

  v1 - Food & Nutrition leal

-- PDF

    BEGIN
       DBMS_CLOUD.GET_OBJECT(
       credential_name => 'OCI_CRED',
       object_uri => 'https://objectstorage.sa-saopaulo-1.oraclecloud.com/n/idi1o0a010nx/b/bucket-database-26ai/o/food02.pdf',
       directory_name => 'DATA_PUMP_DIR');
    END;

-- Embedding: 

  CREATE TABLE "TB_26AI_FOOD_VECTOR" 
   (	"EMBED_ID" NUMBER, 
	"EMBED_DATA" VARCHAR2(4000), 
	"EMBED_VECTOR" VECTOR, 
	"DOC_ORIGEM" VARCHAR2(200)
   ) ;

     CREATE TABLE "TB_26AI_FOOD" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 66 CACHE 20 ORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"FILE_NAME" VARCHAR2(200), 
	"FILE_MIMETYPE" VARCHAR2(200), 
	"FILE_CHARSET" VARCHAR2(200), 
	"FILE_BLOB" BLOB, 
	"UPDATED_DATE" DATE, 
    CREATED_BY varchar2(200) default 'ADMIN'
   ) ;


INSERT INTO "TB_26AI_FOOD_VECTOR"
select  embed_id, 
        text_chunk, 
        embed_vector,
        'guia_alimentar_populacao_brasileira_2ed.pdf' 
 from dual dt
             CROSS JOIN TABLE(
                 dbms_vector_chain.utl_to_embeddings(
                     dbms_vector_chain.utl_to_chunks(
                          -- PDF Embrapa - Doencas no cultivo da Soja
                         dbms_vector_chain.utl_to_text(   to_blob(bfilename('DATA_PUMP_DIR', 'guia_alimentar_populacao_brasileira_2ed.pdf')) ), 
                         json('{"by":"words","max":"100","split":"sentence","normalize":"none", "overlap":0}')
                     ),
                     json('{
						"provider": "ocigenai",
						"credential_name": "OCI_CRED",
						"url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
						"model": "cohere.embed-v4.0"  }')
                 )
             )  t
             CROSS JOIN JSON_TABLE(
                 t.column_value, 
                 '$[*]' COLUMNS (
                     embed_id NUMBER PATH '$.embed_id',
                     text_chunk CLOB PATH '$.embed_data',
                     embed_vector CLOB PATH '$.embed_vector'
                 )
         ) AS et;


INSERT INTO "TB_26AI_FOOD_VECTOR"
select  embed_id, 
        text_chunk, 
        embed_vector,
        'food02.pdf' 
 from dual dt
             CROSS JOIN TABLE(
                 dbms_vector_chain.utl_to_embeddings(
                     dbms_vector_chain.utl_to_chunks(
                         dbms_vector_chain.utl_to_text(   to_blob(bfilename('DATA_PUMP_DIR', 'food02.pdf')) ), 
                         json('{"by":"words","max":"150","split":"newline","sentence":"none", "overlap":0}')
                     ),
                     json('{
						"provider": "ocigenai",
						"credential_name": "OCI_CRED",
						"url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
						"model": "cohere.embed-v4.0"  }')
                 )
             )  t
             CROSS JOIN JSON_TABLE(
                 t.column_value, 
                 '$[*]' COLUMNS (
                     embed_id NUMBER PATH '$.embed_id',
                     text_chunk CLOB PATH '$.embed_data',
                     embed_vector CLOB PATH '$.embed_vector'
                 )
         ) AS et;



INSERT INTO "TB_26AI_FOOD_VECTOR"
select  embed_id, 
        text_chunk, 
        embed_vector,
        'guia_alimentar_cardapios.pdf' 
 from dual dt
             CROSS JOIN TABLE(
                 dbms_vector_chain.utl_to_embeddings(
                     dbms_vector_chain.utl_to_chunks(
                         dbms_vector_chain.utl_to_text(   to_blob(bfilename('DATA_PUMP_DIR', 'guia_alimentar_cardapios.pdf')) ), 
                         json('{"by":"words","max":"120","split":"sentence","normalize":"none", "overlap":0}')
                     ),
                     json('{
						"provider": "ocigenai",
						"credential_name": "OCI_CRED",
						"url": "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/embedText",
						"model": "cohere.embed-v4.0"  }')
                 )
             )  t
             CROSS JOIN JSON_TABLE(
                 t.column_value, 
                 '$[*]' COLUMNS (
                     embed_id NUMBER PATH '$.embed_id',
                     text_chunk CLOB PATH '$.embed_data',
                     embed_vector CLOB PATH '$.embed_vector'
                 )
         ) AS et;


*/
   messages CLOB;
   v_vector clob; 
   p_prompt clob;
   p_prompt2 clob;
   v_total_calorias varchar(200);
begin

  SELECT json_value( fnc_26ai_embed_image_cohere(file_name, file_blob, p_credential, p_comp_id) , '$.embeddings[*].vector()')        
    INTO v_vector
    from TB_26AI_FOOD
    where id = p_image_id;   

  for message_cursor in (

           SELECT embed_data, doc_origem
			FROM TB_26AI_FOOD_VECTOR
			ORDER BY VECTOR_DISTANCE(EMBED_VECTOR, v_vector , COSINE    )
			FETCH EXACT FIRST 60 ROWS ONLY    
       
       ) loop

      messages := messages || '"' || replace(replace(replace(replace(message_cursor.embed_data || '- Fonte:' || message_cursor.doc_origem ,chr(10),null),chr(13),null),'"',''),'''','') || '",' ;

  END LOOP;

   p_prompt := 'Você é um analista de nutrição profissional especializado em avaliar refeições usando entradas de imagem e em recuperar informações nutricionais utilizando RAG a partir do Oracle 26ai Vector Database.
Sua tarefa:
Analisar a imagem de entrada.
Identificar cada item alimentar no prato.
Estimar tamanhos de porções.
Inferir ingredientes ocultos (óleo, manteiga, molhos, açúcar, sal).
Gerar embeddings.
Converter cada item alimentar detectado em uma descrição textual normalizada (adequada para embeddings).
Exemplo: “peito de frango grelhado 120g”, “arroz branco 150g”, “salada verde 80g com molho”.
Executar a Vector Similarity Search.
Recuperar os fatos nutricionais mais próximos do repositório vetorial.
Dados típicos retornados:
calorias;
proteínas;
carboidratos;
gorduras;
fibras;
densidade de micronutrientes;
impacto glicêmico;
Consulte a base da TBCA para obter a composição nutricional (macronutrientes, micronutrientes, fibras, etc.) - Tabela Brasileira de Composição de Alimentos, versão 7.3. São Paulo: USP; 2025.
Combine os resultados do RAG com sua própria estimativa visual.
Quando dados estiverem faltando, estime valores de forma conservadora.
Sua resposta deve sempre estar totalmente fundamentada nos dados retornados pelo RAG quando disponíveis.
Se o RAG não retornar nenhuma correspondência, declare explicitamente "fonte": "estimativa visual". Responda em formato Markdown e portugues Brasil.';

    -- cuidado com temperatura usando "." ou ","
   --execute immediate('alter session set nls_numeric_characters=''.,'' '); 

    -- sem re-rank (opcao de uso para refinar resultado)
    p_prompt2 := p_prompt || '. Dados privados encontrados: ' || messages || '. Cite o documento de origem dos dados.';   

    return fnc_26ai_rag_food(p_prompt2 , p_credential , p_image_id, p_comp_id );

end;
/