SELECT
p.id AS pessoa_id,

CASE
    WHEN pf.nome IS NOT NULL THEN UPPER(pf.nome)
    WHEN pj.razao_social IS NOT NULL THEN UPPER(pj.razao_social)
ELSE 'interessado' END AS nome,
CASE
WHEN pf.registro_nacional IS NOT NULL THEN pf.registro_nacional
WHEN pj.registro_nacional IS NOT NULL THEN pj.registro_nacional
ELSE 'ND' END AS registro_nacional,
CASE
    WHEN pf.id IS NOT NULL THEN 'profissional'
    WHEN pj.id IS NOT NULL THEN 'empresa'
END AS tipo_pessoa,

LOWER(p.email) AS email,

e.*

FROM tb_pessoa AS p
INNER JOIN tb_enderecos AS e ON e.id = p.enderecocorrespondencia
LEFT JOIN tb_profissional AS pf ON pf.pessoa_id = p.id
LEFT JOIN tb_empresa AS pj ON pj.pessoa_id = p.id
        AND (pj.valor_capital IS NOT NULL OR pj.valor_capital::INT <> 0 OR LOWER(pj.categoria) IN ('c', 's'))
WHERE
-- somente profissionais ou empresas
(EXISTS (SELECT pessoa_id FROM tb_profissional WHERE pessoa_id = p.id)

--or exists (select pessoa_id from tb_empresa where pessoa_id = p.id
--  and valor_capital::float > 0 or lower(pj.categoria) in ('c', 's'))
--  and categoria is not null
)

-- nao quitou o exercicio atual
AND EXTRACT(YEAR FROM CURRENT_DATE) != (
    SELECT MAX(exercicio) FROM (
        SELECT MAX(a.exercicio) AS exercicio FROM tb_anuidade a
        WHERE a.pessoa_id = p.id
        AND a.parcela = 1
        AND a.qtdparcelas = 1
        UNION
        SELECT MAX(b.exercicio) AS exercicio FROM tb_anuidade b
        WHERE b.pessoa_id = p.id
        AND b.qtdparcelas = (SELECT MAX(c.parcela) FROM tb_anuidade c
        WHERE c.pessoa_id = b.pessoa_id AND c.qtdparcelas = b.qtdparcelas
        AND c.exercicio = b.exercicio)) AS ultimo_exercicio
)

-- passou 3 meses desde o ultimo pagamento de anuidade
AND (SELECT MAX(pg.data_pagamento) FROM tb_boleto AS b
INNER JOIN tb_convenio AS c ON c.id = b.convenio_id
        AND c.descricao ilike '%anuidade%'
INNER JOIN tb_pagamento AS pg ON pg.boleto_id = b.id
WHERE b.pessoa_id = p.id) + INTERVAL '3 months' < CURRENT_DATE

-- nao possui guias de anuidade em prazo de pagamento
AND NOT EXISTS (SELECT b.pessoa_id FROM tb_boleto AS b
INNER JOIN tb_convenio AS c ON c.id = b.convenio_id
AND c.descricao ilike '%anuidade%'
WHERE b.pessoa_id = p.id
AND b.disponivel_pessoa = TRUE
AND b.vencimento >= CURRENT_DATE
AND NOT EXISTS (SELECT boleto_id FROM tb_pagamento WHERE boleto_id = b.id))

-- somente registrados permanentes ativos
AND (EXISTS (SELECT pr.id
        FROM tb_profissional AS pro
        INNER JOIN tb_profissional_registro pr ON pr.profissional_id = pro.pessoa_id
        INNER JOIN tb_tiporegistro AS tr ON tr.id = pr.tiporegistro_id AND tr.codigo IN ('PP', 'PE', 'TE')
        INNER JOIN tb_profissional_registro_situacaoregistro AS prs ON prs.registro_id = pr.id
        INNER JOIN tb_situacaoregistro AS sr ON sr.id = prs.situacaoregistro_id
                AND sr.id = (SELECT id FROM tb_situacaoregistro WHERE descricao ilike 'ativo')
        WHERE
        pro.pessoa_id = p.id
        ORDER BY prs.data_inicial ASC
        LIMIT 1)

OR EXISTS

(SELECT pr.id
        FROM tb_empresa AS emp
        INNER JOIN (SELECT id AS registro_id, empresa_id FROM tb_empresa_registro) pr1 ON pr1.empresa_id = emp.pessoa_id
        INNER JOIN tb_empresa_registro pr ON pr.id = pr1.registro_id
        INNER JOIN tb_tiporegistro AS tr ON tr.id = pr.tiporegistro_id
                AND tr.codigo IN ('DD', 'EM')
        INNER JOIN tb_situacaoregistro AS sr ON sr.id = pr.situacaoregistro_id
                AND sr.id = (SELECT id FROM tb_situacaoregistro WHERE descricao ilike 'ativo')
        WHERE
        emp.pessoa_id = p.id
        ORDER BY pr.data_ini_registro ASC
        LIMIT 1)
)

-- tem email
AND p.email IS NOT NULL

-- somente pessoas que possuem endereco no estado
AND UPPER(e.uf) = (SELECT UPPER(enderecouf) FROM tb_filial WHERE enderecouf IS NOT NULL LIMIT 1)

-- tem que possuir um endereco valido
AND e.logradouro IS NOT NULL

--devendo desde 2011
AND (
        SELECT MAX(exercicio) FROM (
        SELECT MAX(a.exercicio) AS exercicio FROM tb_anuidade a
        WHERE a.pessoa_id = p.id
        AND a.parcela = 1
        AND a.qtdparcelas = 1

        UNION

        SELECT MAX(b.exercicio) AS exercicio FROM tb_anuidade b
        WHERE b.pessoa_id = p.id
        AND b.qtdparcelas = (SELECT MAX(c.parcela) FROM tb_anuidade c
        WHERE c.pessoa_id = b.pessoa_id AND c.qtdparcelas = b.qtdparcelas
        AND c.exercicio = b.exercicio)) AS ultimo_exercicio
    ) BETWEEN 2010 AND 2013

--order by tipo_pessoa asc, nome asc