package br.com.elotech.saude.scripts;

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

public class VisitaDomiciliarScript {

    private static StringBuilder builder;

    public static String getSqlVisitasDomiciliares(VisitaDomiciliarFilter filter){
        builder = new StringBuilder();

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

        builder.append("SELECT DISTINCT")
               .append(" tcvd.co_seq_cds_visita_domiciliar AS codigo,")
               .append(" uni.uni_desc                     AS unidade,")
               .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 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 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(" usr.usr_nome                                    AS profissional,")
                .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 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 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 uni.uni_codigo =:unidade")
               .append(" AND ate.ate_data BETWEEN :dtInicial AND :dtFinal");
        if(filter.getProfissional()!= null){
            builder.append(" AND usr.usr_codigo =:profissional");
        }
        if(filter.getDesfecho()!= null){
            builder.append(" AND tcvd.co_cds_visita_dom_desfecho =:desfecho");
        }
        if(filter.getPaciente()!=null){
            builder.append(" AND usu.usu_codigo =:paciente");
        }

        if(filter.getTpRel().equals(TipoRelEnum.ANALITICO)){
            builder.append(" ORDER BY uni.uni_desc, 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, usr.usr_nome, des.no_cds_visita_dom_desfecho")
                   .append(" ORDER BY uni.uni_desc, usr.usr_nome, des.no_cds_visita_dom_desfecho");
        }
        return builder.toString();
    }

    public static String getSqlVisitasMotivos(){
        builder = new StringBuilder();
        builder.append("SELECT DISTINCT ")
               .append(" tcvdm.no_cds_visita_dom_motivo AS motivo,")
               .append(" rcvdm.co_cds_visita_domiciliar AS codigo")
               .append(" FROM rl_cds_visita_dom_motivo AS rcvdm")
               .append(" JOIN tb_cds_visita_dom_motivo AS tcvdm ON rcvdm.co_cds_visita_dom_motivo = tcvdm.co_cds_visita_dom_motivo")
               .append(" WHERE rcvdm.co_cds_visita_domiciliar = :codigo");
        return builder.toString();
    }
}