-- ============ CONSULTAS ORACLE TUDOPROD =============

-- Diferentes status presentes no campo registration status ordenados por quantidade
SELECT DISTINCT REGISTRATIONSTATUS, COUNT(*) AS QTD
FROM TUDOPROD.PERSON
GROUP BY REGISTRATIONSTATUS ORDER BY QTD DESC;

-- Cadastros COMPLETOS no aplicativo:
SELECT DOCUMENT, REGISTRATIONSTATUS
FROM TUDOPROD.PERSON
WHERE REGISTRATIONSTATUS IN ('COMPLETE','APPROVED','BACKOFFICE_PENDING');
-- OBS: status BACKOFFICE_PENDING e considerado completo, mas nao se sabe o significado

-- Cadastros INCOMPLETOS no aplicativo:
SELECT DOCUMENT,
REGISTRATIONSTATUS,
CASE WHEN REGISTRATIONSTATUS = 'EMAIL' THEN 'EMAIL PREENCHIDO'
WHEN REGISTRATIONSTATUS = 'PHONENUMBER' THEN 'TELEFONE PREENCHIDO'
WHEN REGISTRATIONSTATUS = 'SMSTOKEN' THEN 'TELEFONE VALIDADO'
WHEN REGISTRATIONSTATUS = 'PASSWORD' THEN 'SENHA CRIADA'
WHEN REGISTRATIONSTATUS IN ('DOCUMENTSPICTURES_BACK','DOCUMENTSPICTURES_FRONT') THEN 'FOTO DOS DOCUMENTOS'
WHEN REGISTRATIONSTATUS = 'FACEPICTURE' THEN 'SELFIE'
WHEN REGISTRATIONSTATUS = 'INITALDATA' THEN 'CPF E CONVENIO PREENCHIDOS' END AS ULTIMOPASSO
FROM TUDOPROD.PERSON
WHERE REGISTRATIONSTATUS IS NOT NULL
AND REGISTRATIONSTATUS != 'OPEN'
AND REGISTRATIONSTATUS NOT IN ('COMPLETE', 'APPROVED','BACKOFFICE_PENDING');

-- OBS: status ADRESS não se sabe o significado, provavelmente legado
-- OBS: status REFUSED não se sabe o significado, antes era considerado como cadastro completo segundo o Daniel

-- Não cadastrados NO aplicativo(prospects):
SELECT DOCUMENT, REGISTRATIONSTATUS
FROM TUDOPROD.PERSON
WHERE REGISTRATIONSTATUS IS NULL;

-- Numero de simulações no aplicativo:
SELECT DISTINCT P.DOCUMENT,P.ID, COUNT(*) AS QTD_SIMULACOES
FROM TUDOPROD.PERSON P
INNER JOIN TUDOPROD.CONTRACTSIMULATION C ON C.PERSONID = P.ID
GROUP BY P.DOCUMENT, P.ID;

-- OBS: Pessoas que NÃO estao neste join NÃO fizeram simulacoes no aplicativo

-- =========== CONSULTAS POSTGRES SDR ===========

-- Query de Teste substring cpf:
SELECT cpf, SUBSTRING(cpf,0,4) || SUBSTRING(cpf, 5, 3) ||  SUBSTRING(cpf, 9, 3) || SUBSTRING(cpf, 13,3) AS cpf_alterado
, COUNT(*) AS qtd_simulacoes_site
FROM sdr.simulacaosite
WHERE cpf IS NOT NULL
GROUP BY cpf;

-- Simulacões feitas no site por CPF:
WITH simul_por_cpf AS (

SELECT SUBSTRING(cpf,0,4) || SUBSTRING(cpf, 5, 3) ||  SUBSTRING(cpf, 9, 3) || SUBSTRING(cpf, 13,3) AS cpf
, COUNT(*) AS qtd_simulacoes_site
FROM sdr.simulacaosite
WHERE cpf IS NOT NULL
GROUP BY cpf

) SELECT * FROM simul_por_cpf;