create or replace procedure prc_26ai_tech (p_rowid in varchar default null,
                                                      p_oci_cred  IN VARCHAR2 default 'OCI_CRED',
                                                      p_comp in varchar2 )
/*


Para testes de carga, ou associacao a jobs:

declare
v_resp clob;
v_json clob;
begin
for r1 in (select rowid cid from TB_26AI_TECH where insight_report is null ) loop
 PRC_26AI_TECH (p_rowid => r1.cid ,
                           p_oci_cred => 'OCI_CRED',
                           p_comp => 'ocid1.compartment.oc1..aaaaaaaayv4jpgnurtdpxwfy3t2tak6akpyh6jgunlc6pdmilqv5f2s5nevq',
                           p_response => v_resp,
                           p_json => v_json );
end loop;                           
end;

*/
as
  v_pre_prompt clob;
  v_customer_data clob; 
  v_pre_prompt_json clob;
  v_response clob;
  v_json clob;
  v_error clob;
begin

    v_pre_prompt :='Você é um analista sênior de dados e operações. 
Sua tarefa é interpretar os dados, comparar os resultados e produzir um relatório executivo e técnico acionável.

Objetivo:
Gerar insights em 4 blocos:
1) Análise
2) Diagnóstico
3) Recomendações
4) Plano de ação

Regras de qualidade:
- Não invente dados ausentes. Quando faltar contexto, declare explicitamente “Informação não fornecida”.
- Seja específico, com linguagem executiva + técnica.
- Priorize risco real de negócio e explorabilidade.
- Sempre indicar severidade e prioridade.
- Sempre incluir ações de curto, médio e longo prazo.
- Quando possível, referenciar boas práticas (OWASP, NIST, CIS, CVE/CWE).

Formato obrigatório da resposta:
## 1. Análise
- Resumo executivo (5 a 8 linhas)
- Principais métricas observadas
- Pontos fora do padrão (outliers/anomalias)

## 2. Diagnóstico
- Causas prováveis por ordem de impacto (Alta/Média/Baixa)
- Evidências que sustentam cada causa (referenciando campos do JSON)
- Riscos de curto e médio prazo se nada for feito

## 3. Recomendações
Para cada recomendação, informe:
- Ação recomendada
- Problema que resolve
- Impacto esperado
- Esforço estimado (Baixo/Médio/Alto)
- Prioridade (P1/P2/P3)

## 4. Plano de ação (30-60-90 dias)
- 30 dias: ações imediatas e “quick wins”
- 60 dias: ajustes estruturais
- 90 dias: consolidação e otimização
Para cada etapa, incluir:
- Responsável sugerido (perfil/função)
- Dependências
- Métrica de sucesso (KPI)
- Critério de conclusão';


v_pre_prompt_json := '## FORMATO DE SAÍDA JSON
Quando solicitado no modo JSON, responda EXCLUSIVAMENTE com o objeto JSON 
válido conforme o schema abaixo, sem texto antes ou depois, sem markdown, 
sem blocos de código. O JSON deve ser parseable diretamente.
Campos não avaliáveis por qualidade de imagem devem receber null.
Não inclua explicações, texto introdutório, saudações ou blocos de Markdown como "```json" no comeco nem no final como "```".

## Exemplo JSON:
Retorne um JSON válido (sem comentários), espelhando o relatório:
{
  "id": "",
  "titulo": "",
  "analise": {
    "resumo_tecnico": "",
    "vetor_ataque": "",
    "pre_condicoes": [],
    "impacto_cia": {
      "confidencialidade": "",
      "integridade": "",
      "disponibilidade": ""
    }
  },
  "diagnostico": {
    "causa_raiz": "",
    "classificacao": {
      "cwe": "",
      "owasp": ""
    },
    "severidade": "",
    "probabilidade_exploracao": "",
    "impacto_negocio": "",
    "prioridade_final": "",
    "justificativa": ""
  },
  "recomendacoes": [
    {
      "acao": "",
      "tipo": "corretiva|preventiva|detectiva",
      "esforco": "baixo|medio|alto",
      "dependencias": []
    }
  ],
  "plano_acao": [
    {
      "prazo": "curto|medio|longo",
      "etapa": "",
      "responsavel_sugerido": "",
      "deadline_sugerido": "",
      "resultado_esperado": "",
      "criterio_aceite": ""
    }
  ],
  "lacunas_informacao": []
}';

   select json_object(
        'AGE_IN_DAYS' VALUE AGE_IN_DAYS,
        'ASSET_DISPLAY_IPV4_ADDRESS' VALUE ASSET_DISPLAY_IPV4_ADDRESS,
        'ASSET_ID' VALUE ASSET_ID,
        'ASSET_NAME' VALUE ASSET_NAME,
        'ASSET_TAGS' VALUE ASSET_TAGS,
        'DEFINITION_EXPLOITABILITY_EASE' VALUE DEFINITION_EXPLOITABILITY_EASE,
        'DEFINITION_FAMILY' VALUE DEFINITION_FAMILY,
        'DEFINITION_ID' VALUE DEFINITION_ID,
        'DEFINITION_NAME' VALUE DEFINITION_NAME,
        'DEFINITION_VPR_SCORE' VALUE DEFINITION_VPR_SCORE,
        'FIRST_OBSERVED' VALUE FIRST_OBSERVED,
        'ID' VALUE ID,
        'LAST_SEEN' VALUE LAST_SEEN,
        'OUTPUT' VALUE OUTPUT,
        'PROTOCOL' VALUE PROTOCOL,
        'RESURFACED_DATE' VALUE RESURFACED_DATE,
        'SEVERITY' VALUE SEVERITY,
        'STATE' VALUE STATE,
        'INSIGHT_REPORT' VALUE INSIGHT_REPORT,
        'ID_INSIGHT' VALUE ID_INSIGHT,
        'NOME' VALUE NOME,
        'EMPRESA' VALUE EMPRESA,
        'CLASSE' VALUE CLASSE,
        'AMBIENTE' VALUE AMBIENTE,
        'STATUS_OPERACIONAL' VALUE STATUS_OPERACIONAL,
        'DESCRICAO' VALUE DESCRICAO,
        'SOX' VALUE SOX,
        'COFRE' VALUE COFRE,
        'ENDERECO_IP' VALUE ENDERECO_IP,
        'SISTEMA_OPERACIONAL' VALUE SISTEMA_OPERACIONAL,
        'TIPO_DE_REDE' VALUE TIPO_DE_REDE,
        'IS_VIRTUAL' VALUE IS_VIRTUAL,
        'CRITICIDADE' VALUE CRITICIDADE,
        'INSTANCIAS_BANCOS' VALUE INSTANCIAS_BANCOS,
        'APLICACOES_RELACIONADAS' VALUE APLICACOES_RELACIONADAS,
        'JANELAS_DE_MANUTENCAO' VALUE JANELAS_DE_MANUTENCAO,
        'ANALISTA_RESPONSAVEL' VALUE ANALISTA_RESPONSAVEL,
        'ANALISTAS_RESPONSAVEIS' VALUE ANALISTAS_RESPONSAVEIS,
        'GERENCIADO_PELO_GRUPO' VALUE GERENCIADO_PELO_GRUPO,
        'GRUPOS_RESPONSAVEIS' VALUE GRUPOS_RESPONSAVEIS,
        'GRUPO_RESPONSAVEL' VALUE GRUPO_RESPONSAVEL,
        'EDR' VALUE EDR,
        'TENABLE' VALUE TENABLE,
        'SIEM' VALUE SIEM,
        'APPLIANCE' VALUE APPLIANCE
    ) AS my_json
    into v_customer_data
    from v_26ai_tech a
    where cid = p_rowid;
   -- and insight_report is null ;

        v_response := FNC_26ai_rag( p_ai_prompt => v_pre_prompt || '. Dados: ' || v_customer_data,
                      p_oci_cred => p_oci_cred,
                      p_comp => p_comp
                    );

        v_json :=  replace( replace( FNC_26ai_rag( p_ai_prompt => 'MODO JSON: ' || v_pre_prompt || ' ' || v_pre_prompt_json || '. Dados: ' || v_customer_data,
                      p_oci_cred => p_oci_cred,
                      p_comp => p_comp
                    ) ,'```json','' ),'```','' );

    update TB_26AI_TECH_v2
       set insight_report = v_response,
           insight_json =  v_json    
       where rowid = p_rowid;

     
    if length(v_response) > 0 then

       -- vetorizacao do relatorio gerado  
       insert into TB_26AI_TECH_VECTOR
            SELECT  et.embed_id,
                    v_response,
                    et.embed_vector,
                    null,
                    p_rowid
                FROM dual
                CROSS JOIN TABLE(
                    DBMS_VECTOR_CHAIN.UTL_TO_EMBEDDINGS(
                       -- DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
                          --  dbms_vector_chain.utl_to_text(  v_response ) , 
                           (  v_response ) ,  
                     --      JSON('{"by":"words","max":"1000","split":"sentence","normalize":"all","overlap":"0"}')     ),
                        json('{ "provider": "ocigenai",
                                "credential_name": "' || p_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   VARCHAR2(4000) PATH '$.embed_data',
                        embed_vector CLOB           PATH '$.embed_vector'
                    )
                ) AS et;

    end if; 

    commit;          

exception
  when others then
    v_error := sqlerrm;
    update TB_26AI_TECH_v2
       set insight_report = v_error,
           insight_json =  null    
       where rowid = p_rowid;  
    commit;                     
end;
/