package br.com.elotech.saude.scripts;

import br.com.elotech.enuns.TipoRelEnum;
import br.com.elotech.saude.model.filters.psf.BuscaAtivaFilter;

public class BuscaAtivaScript {

    public static String getSqlBuscaAtiva(BuscaAtivaFilter filter){
      StringBuilder builder = new StringBuilder();

        if(filter.getTpRel().equals(TipoRelEnum.ANALITICO)){

        builder.append("SELECT DISTINCT")
               .append(" tcvd.co_seq_cds_visita_domiciliar AS codigo,")
               .append(" tcvdm.no_cds_visita_dom_motivo    AS motivo,")
               .append(" uni.uni_desc                      AS unidade,")
               .append(" teq.no_equipe                     AS nomeEquipe,")
               .append(" teq.nu_ine                        AS ineEquipe,")
               .append(" ate.ate_data                      AS data,")
               .append(" ate.ate_hora                      AS hora,")
               .append(" ate.ate_reclamacao                AS observacao,")
               .append(" des.no_cds_visita_dom_desfecho    AS desfecho,")
               .append(" usr.usr_nome                      AS profissional,")
               .append(" usu.usu_nome                      AS paciente,")
               .append(" tplog.ds_tipo_logradouro          AS tipoLogradouro,")
               .append(" rua.rua_nome                      AS rua,")
               .append(" dom.dom_numero                    AS numero,")
               .append(" bai.bai_nome                      AS bairro,")
               .append(" cid.cid_nome                      AS cidade,")
               .append(" cid.uf_sigla                      AS sigla")
               .append(" FROM tb_cds_visita_domiciliar       AS tcvd")
               .append(" JOIN rl_cds_visita_dom_motivo AS rcvdm ON tcvd.co_seq_cds_visita_domiciliar = co_cds_visita_domiciliar")
               .append(" JOIN tb_cds_visita_dom_motivo AS tcvdm ON rcvdm.co_cds_visita_dom_motivo = tcvdm.co_cds_visita_dom_motivo")
               .append(" JOIN atendimento                    AS ate   ON ate.ate_codigo = tcvd.ate_codigo ")
               .append(" JOIN unidade                        AS uni   ON ate.uni_codigo = uni.uni_codigo ")
               .append(" JOIN usuarios                       AS usr   ON usr.usr_codigo = ate.med_codigo ")
               .append(" JOIN usuarios_equipe                AS use   ON usr.usr_codigo = use.usr_codigo")
               .append(" JOIN tb_equipe                      AS teq   ON teq.co_seq_equipe = use.co_equipe")
               .append(" JOIN tb_cds_visita_dom_desfecho     AS des   ON tcvd.co_cds_visita_dom_desfecho = des.co_cds_visita_dom_desfecho ")
               .append(" JOIN usuario                        AS usu   ON usu.usu_codigo = ate.usu_codigo ")
               .append(" LEFT JOIN domicilio                 AS dom   ON usu.dom_codigo = dom.dom_codigo OR usu.usu_codigo = dom.usu_codigo_responsavel ")
               .append(" LEFT JOIN rua                       AS rua   ON dom.rua_codigo = rua.rua_codigo ")
               .append(" LEFT JOIN tb_ms_tipo_logradouro     AS tplog ON rua.co_tipo_logradouro = tplog.co_tipo_logradouro ")
               .append(" LEFT JOIN bairro                    AS bai   ON rua.bai_codigo = bai.bai_codigo ")
               .append(" LEFT JOIN cidade                    AS cid   ON bai.cid_codigo = cid.cid_codigo ");
        } else{
         builder.append(" SELECT")
                .append(" uni.uni_desc                       AS unidade,")
                .append(" teq.no_equipe                      AS nomeEquipe,")
                .append(" teq.nu_ine                         AS ineEquipe,")
                .append(" usr.usr_nome                       AS profissional,")
                .append(" tcvdm.no_cds_visita_dom_motivo     AS motivo,")
                .append(" des.no_cds_visita_dom_desfecho     AS desfecho,")
                .append(" count (tcvd.ate_codigo)            AS qtde")
                .append(" FROM tb_cds_visita_domiciliar       AS tcvd")
                .append(" JOIN rl_cds_visita_dom_motivo AS rcvdm ON tcvd.co_seq_cds_visita_domiciliar = co_cds_visita_domiciliar")
                .append(" JOIN tb_cds_visita_dom_motivo AS tcvdm ON rcvdm.co_cds_visita_dom_motivo = tcvdm.co_cds_visita_dom_motivo")
                .append(" JOIN atendimento                    AS ate   ON ate.ate_codigo = tcvd.ate_codigo")
                .append(" JOIN unidade                        AS uni   ON ate.uni_codigo = uni.uni_codigo")
                .append(" JOIN usuarios                       AS usr   ON usr.usr_codigo = ate.med_codigo")
                .append(" JOIN usuarios_equipe                AS use   ON usr.usr_codigo = use.usr_codigo")
                .append(" JOIN tb_equipe                      AS teq   ON teq.co_seq_equipe = use.co_equipe")
                .append(" JOIN tb_cds_visita_dom_desfecho     AS des   ON tcvd.co_cds_visita_dom_desfecho = des.co_cds_visita_dom_desfecho")
                .append(" JOIN usuario                   AS usu   ON usu.usu_codigo = ate.usu_codigo");
        }
        builder.append(" WHERE ate.ate_data BETWEEN :dtInicial AND :dtFinal")
               .append(" AND tcvdm.co_cds_visita_dom_motivo IN (2,3,4,30)");
        if(filter.getUnidade()!=null){
          builder.append(" AND uni.uni_codigo = :unidade");
        }
         if(filter.getEquipe()!=null){
           builder.append(" AND teq.co_seq_equipe = :equipe");
         }
        if(filter.getProfissional()!= null){
            builder.append(" AND usr.usr_codigo =:profissional");
        }
        if(filter.getTpRel().equals(TipoRelEnum.ANALITICO)){
            builder.append(" ORDER BY uni.uni_desc, teq.no_equipe, tcvdm.no_cds_visita_dom_motivo, usr.usr_nome, des.no_cds_visita_dom_desfecho, usu.usu_nome, ate.ate_data, ate.ate_hora");
        }else{
            builder.append(" GROUP BY uni.uni_desc, teq.no_equipe, teq.nu_ine, tcvdm.no_cds_visita_dom_motivo, usr.usr_nome, des.no_cds_visita_dom_desfecho")
                   .append(" ORDER BY uni.uni_desc,teq.no_equipe, tcvdm.no_cds_visita_dom_motivo, usr.usr_nome, des.no_cds_visita_dom_desfecho");
        }
        return builder.toString();
    }

}