
-- Log técnico

CREATE TABLE log_tecnico (
  id serial NOT NULL,
  data_hora timestamp without time zone,
  nivel text,
  mensagem text,
  metadados jsonb,
  CONSTRAINT pk_log_tecnico PRIMARY KEY (id)
);

-- Log do usuário

CREATE TABLE log_usuario (
  id serial NOT NULL,
  data_hora timestamp without time zone,
  nivel text,
  mensagem text,
  metadados jsonb,
  CONSTRAINT pk_log_usuario PRIMARY KEY (id)
);


-- View para listar os logs do usuário de uma forma mais conveniente


create or replace view log_usuario_simples as
select 
	id, data_hora, nivel, mensagem,
	metadados::json->>'tipo' as tipo,
	metadados::json->>'conversa_interno_id' as conversa_interno_id,
	metadados::json#>>'{payload,intents,0,intent}' as intent,
	metadados::json->>'mensagem_texto' as mensagem_texto
from log_usuario;


-- View para listar as conversas do usuário


create or replace view conversa_usuario as
select 
	min(id) as id, 
	min(data_hora)::date as data,
	min(data_hora) as data_hora_inicio,
	max(data_hora) as data_hora_final,
	metadados::json->>'conversa_interno_id' as conversa_interno_id,
	max(metadados::json#>>'{payload,context,nome_usuario}') as nome_usuario,
	trim(leading '0' from coalesce(max(log_usuario.metadados::json #>> '{payload,context,codigo_empresa}'::text[]), '-1')) AS codigo_empresa,
	sum(case when mensagem like '%<falar_com_atendente>%' then 1 else 0 end) > 0 as foi_para_atendente
from log_usuario
group by metadados::json->>'conversa_interno_id';


-- View para listar os dados referentes à validação das respostas do usuário


create or replace view validacao_resposta_usuario as
select 
	cnv.id as conversa_id,
	cnv.nome_usuario,

	dado.*,	
	
	lop.mensagem_texto as pergunta_original,
	lor.mensagem_texto as resposta_original,
	lur.mensagem_texto as resposta_validacao,

	case lur.mensagem
		when 'Sim' then true
		when 'Não' then false
		else null
	end as foi_util,

	cnv.codigo_empresa
from (
	select 
		dado.*,		
		min(lor.id) as resposta_original_id
	from (
		select
			lup.conversa_interno_id,	
			lup.data_hora,
			max(lop.id) as pergunta_original_id,
			lup.id as pergunta_validacao_id,
			min(lur.id) as resposta_validacao_id
		from log_usuario_simples lup
		left join log_usuario_simples lur on  lur.conversa_interno_id = lup.conversa_interno_id
						  and lur.id > lup.id
						  and lur.tipo = 'envio_conv'
		left join log_usuario_simples lop on  lop.conversa_interno_id = lup.conversa_interno_id
						  and lop.id < lup.id
						  and lop.tipo in ('envio_conv', 'pesquisa')
						  and lop.mensagem <> ''
		where lup.tipo = 'retorno_conv'
		and   (lup.intent = 'validacao_resposta' or lup.mensagem like '%Essa informação foi útil para você%')
		group by lup.conversa_interno_id, lup.data_hora, lup.id
	) dado

	left join log_usuario_simples lor on  lor.conversa_interno_id = dado.conversa_interno_id
					  and lor.id > dado.pergunta_original_id 
					  and lor.id <= dado.pergunta_validacao_id
					  and lor.tipo in ('retorno_conv', 'ret_pesquisa')
	group by dado.conversa_interno_id, dado.data_hora, dado.pergunta_original_id, dado.pergunta_validacao_id, dado.resposta_validacao_id
) dado

left join log_usuario_simples lop on lop.id = dado.pergunta_original_id
left join log_usuario_simples lor on lor.id = dado.resposta_original_id
left join log_usuario_simples lur on lur.id = dado.resposta_validacao_id

left join conversa_usuario cnv on cnv.conversa_interno_id = dado.conversa_interno_id;


-- View para listar os intents mais comuns do mês


create or replace view intents_mais_comuns_mes as

select
	dado.intent,
	count(dado.conversa_interno_id)

from (
	select distinct
		lus.conversa_interno_id, 
		lus.intent
	from log_usuario_simples lus
	where lus.data_hora > CURRENT_TIMESTAMP - interval '1 month'
	and   lus.tipo = 'retorno_conv'
	and   lus.intent is not Null

	and   lus.intent not in ('saudacao_sim', 'saudacao_nao', 'tempo_chat_esgotado', 
		'duvida', 'outros_assuntos', 'nota_fiscal', 'mensagem_erro_alerta', 'validacao_resposta',
		'problema', 'mensagens_de_erro', 'ecf')
) dado

group by dado.intent

order by count(dado.conversa_interno_id) desc;


-- Índices


create index fk_log_usuario_conversa_interno_id on log_usuario((metadados::json->>'conversa_interno_id'), id);
create index fk_log_usuario_intent on log_usuario((metadados::json#>>'{payload,intents,0,intent}'), (metadados::json->>'tipo'), (metadados::json->>'conversa_interno_id'));
create index fk_log_usuario_data_hora on log_usuario(data_hora, id);