WITH setores_resposta AS (
    SELECT
            ROW_NUMBER() over (partition by th001.id_notificacao_solicitacao ORDER BY th001.id_notificacao_solicitacao_status) as linha
            ,setores.setor
            ,max(th001.dt_alteracao) as dt_resposta
            ,th001.id_notificacao_solicitacao_status
            ,th001.id_notificacao_solicitacao as id_solicitacao
    FROM th001_notificacao_solicitacao_status AS th001
    JOIN pentaho.tb_setores AS setores
            ON 1=1
            AND setores.emails ILIKE '%'||fn_retorna_email(th001.de_resposta)||'%'
    WHERE --th001.id_notificacao_solicitacao = 1186
     th001.id_status_notificacao_solicitacao_atual = 35

    GROUP BY 2,4,5
    order by 5,1

), data_encaminhamento as (
        SELECT th001.id_notificacao_solicitacao as id_solicitacao
                  ,min(th001.dt_alteracao) as dt_encaminhamento
        FROM th001_notificacao_solicitacao_status AS th001
        WHERE --th001.id_notificacao_solicitacao = 1186
             th001.id_status_notificacao_solicitacao_atual = 41
        GROUP BY 1
)


SELECT distinct

             sr.id_solicitacao
            ,(select setor from setores_resposta where linha = 1 and id_solicitacao = tb017.id_notificacao_solicitacao) as setor_1
            ,(select setor from setores_resposta where linha = 2 and id_solicitacao = tb017.id_notificacao_solicitacao) as setor_2
            ,(select setor from setores_resposta where linha = 3 and id_solicitacao = tb017.id_notificacao_solicitacao) as setor_3
            ,(select setor from setores_resposta where linha = 4 and id_solicitacao = tb017.id_notificacao_solicitacao) as setor_4
            ,(select setor from setores_resposta where linha = 5 and id_solicitacao = tb017.id_notificacao_solicitacao) as setor_5
          ,dte.dt_encaminhamento as dt_encaminhamento
            ,(select dt_resposta from setores_resposta where linha = 1 and id_solicitacao = tb017.id_notificacao_solicitacao) as dt_resposta_1
            ,(select dt_resposta from setores_resposta where linha = 2 and id_solicitacao = tb017.id_notificacao_solicitacao) as dt_resposta_2
            ,(select dt_resposta from setores_resposta where linha = 3 and id_solicitacao = tb017.id_notificacao_solicitacao) as dt_resposta_3
            ,(select dt_resposta from setores_resposta where linha = 4 and id_solicitacao = tb017.id_notificacao_solicitacao) as dt_resposta_4
            ,(select dt_resposta from setores_resposta where linha = 5 and id_solicitacao = tb017.id_notificacao_solicitacao) as dt_resposta_5
          ,REPLACE(to_char(((select dt_resposta from setores_resposta where linha = 1 and id_solicitacao = tb017.id_notificacao_solicitacao) - dte.dt_encaminhamento )::interval,'dd HH24:MI:SS'), ' ', ' dia(s) ') AS tempo_resposta_setor_1
            ,REPLACE(to_char(((select dt_resposta from setores_resposta where linha = 2 and id_solicitacao = tb017.id_notificacao_solicitacao) - dte.dt_encaminhamento )::interval,'dd HH24:MI:SS'), ' ', ' dia(s) ') AS tempo_resposta_setor_2
            ,REPLACE(to_char(((select dt_resposta from setores_resposta where linha = 3 and id_solicitacao = tb017.id_notificacao_solicitacao) - dte.dt_encaminhamento )::interval,'dd HH24:MI:SS'), ' ', ' dia(s) ') AS tempo_resposta_setor_3
            ,REPLACE(to_char(((select dt_resposta from setores_resposta where linha = 4 and id_solicitacao = tb017.id_notificacao_solicitacao) - dte.dt_encaminhamento )::interval,'dd HH24:MI:SS'), ' ', ' dia(s) ') AS tempo_resposta_setor_4
            ,REPLACE(to_char(((select dt_resposta from setores_resposta where linha = 5 and id_solicitacao = tb017.id_notificacao_solicitacao) - dte.dt_encaminhamento )::interval,'dd HH24:MI:SS'), ' ', ' dia(s) ') AS tempo_resposta_setor_5
            ,frm.de_tema as tema
            ,frm.de_assunto as assunto
            ,frm.de_sub_assunto as sub_assunto
            ,frm.de_tipo as tipo
            ,frm.de_reincidente as prioridade
            ,TB008.de_meio_comunicacao as CANAL_ATENDIMENTO
            ,TB017.ID_NOTIFICACAO_SOLICITACAO AS NU_PROTOCOLO
            ,(CASE WHEN TB009.DE_PESSOA IS NULL THEN
                    'Anônimo'
                  ELSE
                    fnnormalizatexto(UPPER(TB009.DE_PESSOA),'')
              END) AS DE_SOLICITANTE
            ,fnnormalizatexto(fnnormalizacpf(TB009.DE_CPF),'') AS DE_SOLICITANTE_CPF
            ,fnnormalizatexto(fnretornatelefone(TB009.ID_PESSOA::INTEGER),'') AS DE_SOLICITANTE_TELEFONE
            ,TB009.DE_EMAIL AS DE_SOLICITANTE_EMAIL
            ,fntabela_secaoarvore(TB056.ID_SECAO::INTEGER)::TEXT as ASSUNTO
            ,to_char(TB017.DT_CADASTRO at time zone  'Brazil/East', 'DD/MM/YYYY HH24:MI:SS') AS DT_ABERTURA
            ,date_part('month', TB017.DT_CADASTRO::timestamp)::INTEGER AS MES_ABERTURA
            ,date_part('year', TB017.DT_CADASTRO::timestamp)::INTEGER AS ANO_ABERTURA
            ,fnnormalizatexto(removetaghtml(fnnormalizahtml(TB017.DE_NOTIFICACAO)), '') AS DE_DESCRICAO_SOLICITACAO
            ,(CASE
                    WHEN TB017.ST_STATUS = 2 THEN
                    'FINALIZADO'
                    ELSE
                    fnnormalizatexto(TB016.DE_STATUS_NOTIFICACAO, 'RECEBIDO')
            END) AS DE_STATUS_NOTIFICACAO
            ,to_char(TH001_ENCAMINHAMENTO.DT_ALTERACAO at time zone  'Brazil/East', 'DD/MM/YYYY HH24:MI:SS') AS DT_ENCAMINHAMENTO
            ,date_part('month', TH001_ENCAMINHAMENTO.DT_ALTERACAO::timestamp)::INTEGER AS MES_ENCAMINHAMENTO
            ,date_part('year', TH001_ENCAMINHAMENTO.DT_ALTERACAO::timestamp)::INTEGER AS ANO_ENCAMINHAMENTO
            ,fnnormalizatexto(UPPER(TB005.DE_USUARIO), '') AS DE_USUARIO
            ,TB041.DE_ORGAO AS DE_ORGAO
            ,fnnormalizatexto(removetaghtml(fnnormalizahtml(TH001.DE_RESPOSTA)), '') AS DE_RESPOSTA
            ,to_char(TH001.DT_ALTERACAO at time zone 'Brazil/East', 'DD/MM/YYYY HH24:MI:SS') as DT_RESPOSTA
            ,REPLACE(to_char((CURRENT_TIMESTAMP::TIMESTAMP - TB017.DT_CADASTRO::TIMESTAMP)::interval,'dd HH24:MI:SS'), ' ', ' dia(s) ') AS MASCARA_INTERVALO



FROM TB017_NOTIFICACAO_SOLICITACAO AS TB017
LEFT JOIN
    TB009_PESSOA AS TB009
        ON
            TB009.ID_PESSOA = TB017.ID_PESSOA
LEFT JOIN
    TB056_DADO_SOLICITACAO AS TB056
        ON
            TB056.ID_DADO_SOLICITACAO = TB017.ID_DADO_SOLICITACAO
LEFT JOIN
    TB041_ORGAO AS TB041
        ON
            TB041.ID_ORGAO = TB017.ID_ORGAO
LEFT JOIN
    TB057_DADO_SOLICITACAO_INTERNACIONALIZA AS TB057
        ON
            TB057.ID_DADO_SOLICITACAO = TB056.ID_DADO_SOLICITACAO
            AND
            TB057.DE_LOCALE = 'PORTUGUES'
LEFT OUTER JOIN
    TH001_NOTIFICACAO_SOLICITACAO_STATUS AS TH001
        ON
            TH001.ID_NOTIFICACAO_SOLICITACAO_STATUS = TB017.ID_HISTORICO_ATUAL
LEFT JOIN
    th001_notificacao_solicitacao_status AS TH001_ENCAMINHAMENTO
        ON
            TB017.ID_NOTIFICACAO_SOLICITACAO = TH001_ENCAMINHAMENTO.ID_NOTIFICACAO_SOLICITACAO
            AND TH001_ENCAMINHAMENTO.id_notificacao_solicitacao_status = (select MAX(id_notificacao_solicitacao_status)
                                                                                                                                        from th001_notificacao_solicitacao_status
                                                                                                                                        where ID_STATUS_NOTIFICACAO_SOLICITACAO_ATUAL = 16
                                                                                                                                            AND id_notificacao_solicitacao = TB017.ID_NOTIFICACAO_SOLICITACAO)
LEFT JOIN TB005_USUARIO AS TB005
  ON TB005.ID_USUARIO = TH001.ID_USUARIO_ALTERACAO
LEFT OUTER JOIN
    TB005_USUARIO AS TB005_ATRIBUIDO
        ON
            TB005_ATRIBUIDO.ID_USUARIO = TB017.ID_USUARIO_TRATAMENTO
LEFT OUTER JOIN
    TB016_STATUS_NOTIFICACAO AS TB016
        ON
            TB016.ID_STATUS_NOTIFICACAO = TH001.ID_STATUS_NOTIFICACAO_SOLICITACAO_ATUAL
LEFT OUTER JOIN
    TB008_MEIO_COMUNICACAO AS TB008
        ON
            TB008.ID_MEIO_COMUNICACAO = TB017.ID_MEIO_COMUNICACAO


 JOIN frm_formulario_check_list_v2 AS frm
        ON frm.id = tb017.id_registro_check_list

left JOIN setores_resposta as sr
    ON sr.id_solicitacao = tb017.id_notificacao_solicitacao
left join data_encaminhamento as dte
    on sr.id_solicitacao = dte.id_solicitacao


WHERE
--tb017.id_notificacao_solicitacao = 18
1=1
and
    (
        (
            0 <> 0
            AND
            TB017.ID_NOTIFICACAO_SOLICITACAO = 0
        )
        OR
        (
            0 = 0
            AND
            (
                '--TODOS--' = '--TODOS--'
                OR
                fnNormalizaTexto(TB016.DE_STATUS_NOTIFICACAO, 'RECEBIDO') = '--TODOS--'
            )
            AND
            (TB017.DT_CADASTRO at time zone 'Brazil/East')::DATE BETWEEN '2019-07-02'::DATE AND '2019-07-02'::DATE
            AND
            (
              '--TODOS--' = '--TODOS--'
              OR
              (
                '--TODOS--' <> '--TODOS--'
                AND
                TB041.DE_ORGAO = '--TODOS--'
              )
            )
        )
    )
ORDER BY TB017.ID_NOTIFICACAO_SOLICITACAO ASC

--limit 10;