select_ai/README.md

# Crie um Chat com Dashboard utilizando **Oracle Autonomous Database SELECT AI** 

## 1. Introdução

Este tutorial cria uma **interface web** simples (chat + cards com **gráficos** e **tabelas**) transformando **perguntas em linguagem natural** (pt-BR) em **SQL** automaticamente, usando o **SELECT AI** do Autonomous Database. É ideal para cenários como **Pronto-Socorro (ER)**, vendas, logística etc., quando usuários de negócio querem **insights imediatos** sem escrever SQL.

### Como funciona:

- O **Flask** serve uma página com um campo de pergunta e um histórico de respostas (cada resposta pode ter **SQL gerado**, **tabela** e **gráfico Chart.js**).
- Ao enviar a pergunta, o backend chama `SELECT AI 'sua pergunta' FROM <tabela/view>` — quem gera e executa o SQL é o **SELECT AI** dentro do banco.
- O app formata os resultados, tenta inferir o **tipo de gráfico** e permite **exportar tudo** para **PDF** (html2canvas + jsPDF, no navegador) e **Excel** (duas opções: no navegador com XlsxPopulate ou **no servidor** com `openpyxl`).

### Tecnologias principais:
- **Oracle Autonomous Database 23ai** com **SELECT AI** e **DBMS_CLOUD_AI** (perfis de LLM)
- **Python** + **Flask** + **python-oracledb (Thin)** com **mTLS** (wallet)
- **Front-end**: Chart.js, html2canvas + jsPDF e XlsxPopulate (via CDN)

---

## 2. Pré-requisitos

**Oracle Cloud**
- Um **Autonomous Database (Serverless)** em **23ai** (ou com SELECT AI disponível).
- **Saída de rede do ADB** para acessar o serviço OCI Generative AI.
- Permissões para usar **DBMS_CLOUD** / **DBMS_CLOUD_AI** e criar **perfis de IA**.
- **Modelo/Região** do OCI Generative AI habilitados (ex.: `cohere.command-r-08-2024` em `us-chicago-1`).

**Uma VM Linux para hospedagem da página Web (app Flask)**
- **Python 3.10+** (recomendado).
- Pacotes: `flask`, `oracledb`, `openpyxl`, `pillow`.
- **Wallet** do ADB (ZIP baixado do console).

---

## 3. Entendendo o código (trecho a trecho)

O código-fonte pode ser baixado aqui: [app_select_ai.py](./files/app_select_ai.py)

### Conexão e sessão com perfil de IA
- Lê **config** (wallet, alias TNS, usuário/senha).
- Define **`TNS_ADMIN`**.
- `session_callback` ativa o **perfil de IA** (garante SELECT AI pronto em cada sessão).

![img_1.png](images/img_1.png)

### run_select_ai
- Monta a sentença **`SELECT AI 'pergunta' FROM <tabela>`**.
- Executa e retorna **SQL gerado**, **headers/rows** e usuário/perfil ativos.

![img_2.png](images/img_2.png)

### build_chart e format_table
- `build_chart`: heurísticas para inferir gráfico (pizza, barras, linha).
- `format_table`: limita registros e devolve `{headers, rows}`.

![img_3.png](images/img_3.png)


### Front-end (PAGE)
- **Chart.js** desenha gráficos; toolbar alterna tipos.
- **Exportar PDF**: usa html2canvas + jsPDF.
- **Exportar Excel**: cliente (XlsxPopulate) ou servidor (openpyxl).

### Histórico (session)
- Mantém últimos 10 cards em `timeline`.

---

## 4. SELECT AI no Autonomous Database

- Permite **NL→SQL** direto no banco.
- Configuração feita via **Perfis de IA** (`DBMS_CLOUD_AI.CREATE_PROFILE`).
- Perfis incluem provider, modelo, region, credential e lista de objetos (tables/views).
- Comandos auxiliares: `showsql`, `explain`, `narrate` etc.

---

## 5. Instalação e implantação

### 5.1. Criar usuário e permissionamento como ADMIN

Execute estes comandos como ADMIN no Autonomous Database:

```sql
CREATE USER MEU_USUARIO IDENTIFIED BY "SenhaForte123";
ALTER USER MEU_USUARIO QUOTA UNLIMITED ON DATA;
GRANT CREATE SESSION TO MEU_USUARIO;
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO MEU_USUARIO;
GRANT SELECT ON ADMIN.DATASET_ED_ADMISSION TO MEU_USUARIO;
GRANT EXECUTE ON DBMS_CLOUD TO MEU_USUARIO;
GRANT EXECUTE ON DBMS_CLOUD_AI TO MEU_USUARIO;
```

### 5.2 Escolher o método de autenticação do SELECT AI

Há duas opções. Escolha uma opção (**A ou B**) conforme necessidade:

**Opção A** — Resource Principal (RP) (recomendada no ADB)

Sem armazenar chave no banco; usa a identidade do serviço ADB.

```sql
BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');  -- habilita provedor OCI no ADB
END;
/

BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();                -- ativa RP no banco
END;
/

BEGIN
  DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(                      -- permite o schema usar RP
    provider => 'OCI',
    username => 'MEU_USUARIO'
  );
END;
/
```

**Opção B** — Credencial com API Key

Armazena uma credencial (OCID do usuário IAM + chave privada).

Substitua username e password pelos seus valores (OCID e chave/PEM).

```sql
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_GENAI_CRED',
    username        => 'ocid1.user.oc1..aaaa...vx',     -- OCID do usuário IAM
    password        => 'SUA_CHAVE_PRIVADA_OU_CONTEUDO'  -- chave/PEM
  );
END;
/
```

### 5.3 Criar o(s) Perfil(is) de IA (SELECT AI)

O perfil define provedor, região, credencial/RP, quais objetos o LLM pode usar e (opcionalmente) o modelo.

Exemplo 1 — Perfil com Resource Principal

```sql
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    'OCI_GENAI',                               
    '{
      "provider": "OCI",
      "credential_name": "OCI$RESOURCE_PRINCIPAL",
      "object_list": [ { "owner": "ADMIN" } ],
      "model": "cohere.command-r-08-2024",
      "oci_runtimetype": "COHERE",
      "temperature": "0.4"
    }'
  );
END;
/
```

Exemplo 2 — Perfil principal da aplicação (com Credencial)

Inclui explicitamente os objetos que o SELECT AI pode usar:

```sql

BEGIN
    
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'OCI_GENERATIVE_AI_PROFILE',
    attributes   =>
      '{
        "provider":"OCI",
        "region":"us-chicago-1",
        "credential_name":"OCI$RESOURCE_PRINCIPAL",
        "object_list":[
          {"owner":"ADMIN","name":"DATASET_ED_ADMISSION"},
          {"owner":"MEU_USUARIO","name":"NLU_ED_ADMISSION"}
        ],
        "model":"cohere.command-r-08-2024"
      }'
  );
END;
/
```

>**Nota:** 💡 Dica: se preferir, você pode omitir "model" e deixar o ADB usar o modelo padrão da região/provedor.

>**Nota:** 🔒  Segurança: liste somente os objetos necessários em object_list.

### 5.4 Ativar o perfil e torná-lo ativo na sessão

```sql
BEGIN
   DBMS_CLOUD_AI.ENABLE_PROFILE('OCI_GENERATIVE_AI_PROFILE');
END;
/

EXEC  DBMS_CLOUD_AI.SET_PROFILE('OCI_GENERATIVE_AI_PROFILE');
SELECT DBMS_CLOUD_AI.GET_PROFILE() AS active_after FROM dual;
```

>**IMPORTANTE:** É necessário executar este comando abaixo antes de cada consulta SELECT AI:

```sql
CALL DBMS_CLOUD_AI.SET_PROFILE('OCI_GENERATIVE_AI_PROFILE');
```

### 5.5 Enriquecer o vocabulário

Enriquecer o vocabulário com COMMENT ON na tabela gerada no tutorial: [Hospital Risk Admission Prediction with Machine Learning](https://github.com/hoshikawa2/hospital_risk_admission)

Comentários ajudam o LLM a entender o domínio (descrições de tabelas/colunas).

```sql
COMMENT ON TABLE DATASET_ED_ADMISSION IS
    'Tabela de pacientes que deram entrada no pronto-socorro / ER patients admission table';

COMMENT ON COLUMN DATASET_ED_ADMISSION.subject_id IS
    'Patient ID / ID do paciente (unique identifier)';

COMMENT ON COLUMN DATASET_ED_ADMISSION.hadm_id IS
    'Hospital admission ID / ID da internação hospitalar (NULL if not admitted)';

COMMENT ON COLUMN DATASET_ED_ADMISSION.stay_id IS
    'ER stay ID / ID da estadia no pronto-socorro, ou o mesmo que o ID de internação';

COMMENT ON COLUMN DATASET_ED_ADMISSION.intime IS
    'ER entry timestamp / Data-hora de entrada no pronto-socorro (use EXTRACT(MONTH) for month filter)';

COMMENT ON COLUMN DATASET_ED_ADMISSION.outtime IS
    'ER discharge timestamp / Data-hora de saída do pronto-socorro';

COMMENT ON COLUMN DATASET_ED_ADMISSION.gender IS
    'Gender (Male or M/Female or F) / Sexo (Masculino ou M/Feminino ou F)';

COMMENT ON COLUMN DATASET_ED_ADMISSION.race IS
    'Race/Ethnicity / Raça ou etnia do paciente';

COMMENT ON COLUMN DATASET_ED_ADMISSION.arrival_transport IS
    'Arrival transport mode (ambulance, walk) / Forma de chegada (ambulância, caminhada)';

COMMENT ON COLUMN DATASET_ED_ADMISSION.disposition IS
    'Disposition after ER (ADMITTED, HOME, etc.) / Destino após atendimento';

COMMENT ON COLUMN DATASET_ED_ADMISSION.admitted_from_ed IS
    'Hospitalized from ER (1=yes, 0=no) / Internado a partir do pronto-socorro (1=internação/0=Sem internação)';

COMMENT ON COLUMN DATASET_ED_ADMISSION.temperature IS
    'Body temperature (Celsius) / Temperatura corporal';

COMMENT ON COLUMN DATASET_ED_ADMISSION.heartrate IS
    'Heart rate (bpm) / Frequência cardíaca';

COMMENT ON COLUMN DATASET_ED_ADMISSION.resprate IS
    'Respiratory rate (breaths/min) / Frequência respiratória';

COMMENT ON COLUMN DATASET_ED_ADMISSION.o2sat IS
    'Oxygen saturation (SpO2) / Saturação de oxigênio';

COMMENT ON COLUMN DATASET_ED_ADMISSION.sbp IS
    'Systolic blood pressure / Pressão arterial sistólica';

COMMENT ON COLUMN DATASET_ED_ADMISSION.dbp IS
    'Diastolic blood pressure / Pressão arterial diastólica';

COMMENT ON COLUMN DATASET_ED_ADMISSION.n_diagnosis IS
    'Number of diagnoses / Número de diagnósticos registrados';

COMMENT ON COLUMN DATASET_ED_ADMISSION.split IS
    'Data split flag (train, val, test) / Particionamento dos dados';
```

### 5.6 Criar uma view para NL com nomes claros

Esta view, mais amigável, facilitará a LLM do banco de dados a entender mais facilmente os campos.

Criar uma view no schema do usuário (recomendado para consumo pelo app):

```sql
-- execute no MESMO schema usado pelo SELECT AI (ex.: MEU_USUARIO)
CREATE OR REPLACE VIEW MEU_USUARIO.NLU_ED_ADMISSION AS
SELECT
    subject_id              AS patient_id,        -- ID do paciente
    hadm_id                 AS admission_id,      -- ID da internação (NULL se não internado)
    stay_id                 AS er_stay_id,        -- ID da estadia no PS
    intime                  AS er_entry_time,     -- data/hora de entrada no PS
    outtime                 AS er_exit_time,      -- data/hora de saída do PS
    gender,
    race,
    arrival_transport,
    disposition,                                   -- ADMITTED, HOME, etc.
    admitted_from_ed        AS admitted,           -- 1=internado, 0=não
    temperature,
    heartrate,
    resprate,
    o2sat,
    sbp,
    dbp,
    n_diagnosis,
    split,
    EXTRACT(MONTH FROM intime) AS month_num,       -- 1..12
    TO_CHAR(intime, 'fmMonth', 'NLS_DATE_LANGUAGE=PORTUGUESE') as month_name,
    EXTRACT(YEAR  FROM intime) AS year_num         -- ano numérico
FROM ADMIN.DATASET_ED_ADMISSION;

COMMENT ON TABLE MEU_USUARIO.NLU_ED_ADMISSION IS 'Tabela de Internações de pacientes que deram entrada no pronto-socorro com nomes de campos amigaveis para consultas em linguagem natural';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.gender IS
    'Genero (Masculino ou M/Feminino ou F)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.race IS
    'Raça ou etnia do paciente';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.arrival_transport IS
    'Forma de chegada (ambulância, caminhada)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.disposition IS
    'Destino após atendimento (ADMITTED=Internados/Internado, HOME=Dispensados/Dispensado/Sem Internação/Sem Internações, TRANSFER=Transferido/Transferidos)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.temperature IS
    'Temperatura corporal (celsius)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.heartrate IS
    'Frequência cardíaca em BPM';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.resprate IS
    'Frequência respiratória (respiração/minuto)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.o2sat IS
    'Saturação de oxigênio (SpO2)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.sbp IS
    'Pressão arterial sistólica';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.dbp IS
    'Pressão arterial diastólica';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.n_diagnosis IS
    'Número de diagnósticos registrados';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.split IS
    'Particionamento dos dados (train, val, test)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.patient_id   IS 'ID do paciente';
COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.admitted     IS 'Admitted indica se Paciente foi Internado (1=Internado/0=Não Internado)';
COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.er_entry_time IS 'Data de Entrada no pronto-socorro';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.month_num IS
    'Número do mês da entrada no PS / Mês (1=Janeiro, 2=Fevereiro, 3=Março, 4=Abril, 5=Maio, 6=Junho, 7=Julho, 8=Agosto, 9=Setembro, 10=Outubro, 11=Novembro, 12=Dezembro)';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.month_name IS
    'Nome do mês de entrada do paciente no PS (ex.: Janeiro, Fevereiro, Março, ...). Use para exibição; para filtros, prefira month_num e year_num.';

COMMENT ON COLUMN MEU_USUARIO.NLU_ED_ADMISSION.year_num     IS 'Ano de Entrada no pronto-socorro';
```

>**Por que?** Nomes como patient_id, admitted, month_num facilitam a tradução NL→SQL e evitam ambiguidade.

Valide o SELECT AI, executando os comandos abaixo numa mesma sessão:

### Testar direto no SQL
```sql
BEGIN
   EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENERATIVE_AI_PROFILE');
   SELECT AI 'quantos pacientes no hospital' FROM MEU_USUARIO.NLU_ED_ADMISSION;
END;
```


### 5.7 Preparar o app Flask

Configure o arquivo [config](./files/config) com os dados da Wallet do Autonomous Database:

```json
{
  "WALLET_PATH": "/caminho/Wallet_ADB",
  "DB_ALIAS": "oradb_high",
  "USERNAME": "MEU_USUARIO",
  "PASSWORD": "SenhaForte123"
}
```

Baixar o arquivo [requirements.txt](./files/requirements.txt). Instalar dependências:

```bash
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```


---

## 6. Testar a aplicação

Rodar o app [app_select_ai.py](./files/app_select_ai.py):

```bash
python app_select_ai.py
# abre http://localhost:5001
```

### Acessando a aplicação
Abra `http://localhost:5001` e faça perguntas como:
- quantos pacientes chegaram no hospital
- mostrar pacientes e suas pressoes arteriais acima de 120 80
- quantos pacientes deram entrada no hospital no mes "Junho"
- quantos pacientes do genero F
- quantos pacientes do genero M que tenham pressao acima de 120 80
- listar pacientes do genero M com todos os seus dados de mediçao que tenham pressao acima de 120 80
- quantos pacientes internados no hospital por genero

### Exportar resultados
- **PDF**: botão “Exportar PDF”.
- **Excel**: botão “Exportar Excel” (via servidor).

---

![img.png](images/img.png)


## Reference

- [Install OCI-CLI](https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm)
- [Download Wallet Database Connection File](https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/connect-download-wallet.html#GUID-DED75E69-C303-409D-9128-5E10ADD47A35)
- [About SELECT AI](https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/select-ai.html)
- [Hospital Risk Admission Prediction with Machine Learning](https://github.com/hoshikawa2/hospital_risk_admission)

## Acknowledgments

- **Author** - Cristiano Hoshikawa (Oracle LAD A-Team Solution Engineer)