package br.com.elotech.social.scripts;

import br.com.elotech.social.model.filters.AtendimentoRealizadoAosMunicipesFilter;
import org.apache.commons.lang3.StringUtils;

public class AtendimentoRealizadoAosMunicipesScript {

    public static String getSqlAtendimentoRealizadoAosMunicipes(AtendimentoRealizadoAosMunicipesFilter filter){
        StringBuilder compl = new StringBuilder(" WHERE 1=1");
        compl.append(" AND asab_data BETWEEN :dt_inicial AND :dt_final");
        if(filter.getUniCodigo() != null){
            compl.append(" AND ate.uni_codigo = :unidade");
        }
       if(filter.getFaixa_etaria()!= null) {
            compl.append(" AND (extract(year from age(u.usu_datanasc)) > 99999 ");
           String[] array = StringUtils.split(filter.getFaixa_etaria(), "|");
           for (String s: array) {
               switch(s){
                   case "1":
                       compl.append(" OR extract(year from age(u.usu_datanasc)) BETWEEN 0 AND 5");
                       break;
                   case "6":
                       compl.append(" OR extract(year from age(u.usu_datanasc)) BETWEEN 6 AND 14");
                       break;
                   case "15":
                       compl.append(" OR extract(year from age(u.usu_datanasc)) BETWEEN 15 AND 17");
                       break;
                   case "18":
                       compl.append(" OR extract(year from age(u.usu_datanasc)) BETWEEN 18 AND 59");
                       break;
                   case "60":
                       compl.append(" OR extract(year from age(u.usu_datanasc)) > 60");
                       break;
               }

           }
           compl.append(")");
       }
        compl.append(" AND u.usu_datanasc <= current_date");

      StringBuilder sb = new StringBuilder("SELECT");
        sb.append(" uni.uni_desc,")
          .append(" CASE WHEN (extract(year from age(u.usu_datanasc)) BETWEEN 0  AND 5)   THEN '00 e 05 ANOS'")
          .append("      WHEN (extract(year from age(u.usu_datanasc)) BETWEEN 6  AND 14)  THEN '06 e 14 ANOS'")
          .append("      WHEN (extract(year from age(u.usu_datanasc)) BETWEEN 15 AND 17)  THEN '15 e 17 ANOS'")
          .append("      WHEN (extract(year from age(u.usu_datanasc)) BETWEEN 18 AND 59)  THEN '18 e 59 ANOS'")
          .append("      WHEN (extract(year from age(u.usu_datanasc)) >= 60) THEN '60 ANOS OU MAIS' END as faixa_etaria,")
          .append(" count(*) as qtdeAtendimento")
          .append(" FROM as_atendimento_basicocras ate")
          .append(" INNER JOIN usuario u on u.usu_codigo = ate.usu_codigo")
          .append(" INNER JOIN unidade uni on uni.uni_codigo = ate.uni_codigo");
        sb.append(compl);
        sb.append(" GROUP BY uni.uni_desc,faixa_etaria ORDER BY uni.uni_desc,faixa_etaria");

        return sb.toString();

    }

}