package br.com.elotech.websaude.integracao.esus.model;

import br.com.elotech.websaude.integracao.esus.util.Util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import static br.com.elotech.websaude.integracao.esus.model.dao.Conexao.CONN;

public class BancoProcedimentos {

  protected static String sql = "";
  protected static PreparedStatement pstmt;
  private StringBuilder builder;

  public List<HashMap> getDadosProcedimentos() throws SQLException {
    builder = new StringBuilder("SELECT DISTINCT ");
    builder.append("   efp.age_codigo,")
      .append("   efp.co_local_atend,")
      .append("   efp.efp_profissional_cns,")
      .append("   efp.efp_cbo_codigo_2002,")
      .append("   efp.efp_cnes,")
      .append("   efp.efp_ine,")
      .append("   efp_dtatendimento,")
      .append("   efp.efp_codigo_ibge_mun,")
      .append("   efp.efp_num_cartao_sus,")
      .append("   efp.efp_dtnascimento,")
      .append("   efp.efp_sexo,")
      .append("   efp.efp_tipo_dado_serializado,")
      .append("   efp.efp_dtcadastro,")
      .append("   efp.efp_codigo,")
      .append("   efp_numprontuario,")
      .append("   ate.turno,")
      .append("   efp.efp_cpf_cidadao,")
      .append("   efp.efp_data_hora_inicial,")
      .append("   efp.efp_data_hora_final")
      .append(" FROM esus_ficha_procedimento AS efp")
      .append(" LEFT JOIN atendimento ate ON ate.age_codigo = efp.age_codigo")
      .append(" WHERE uuid_ficha IS NULL OR uuid_ficha = ''")
      .append(" GROUP BY efp.age_codigo, efp.co_local_atend, efp_profissional_cns, efp_cbo_codigo_2002,")
      .append("   efp_cnes, efp_ine, efp_dtatendimento, efp_codigo_ibge_mun, efp_num_cartao_sus,")
      .append("   efp_dtnascimento, efp_sexo, efp_tipo_dado_serializado, efp_dtcadastro, efp.efp_codigo, ate.turno,")
      .append("   efp.efp_cpf_cidadao,efp.efp_data_hora_inicial,efp.efp_data_hora_final");
    pstmt = CONN.prepareStatement(builder.toString());
    ResultSet rs = pstmt.executeQuery();
    List<HashMap> listAux = Util.resultSetToArrayList(rs);
    List<HashMap> list = new ArrayList<>();
    for (HashMap hash : listAux) {
      list.add(getSubDadosProcedimentos(hash));
      Integer atual = (listAux.indexOf(hash) + 1);
      Integer total = listAux.size();
      Util.showLoading("Procedimentos", atual, total);
    }
    rs.close();
    return list;
  }

  public ResultSet getProcedimentosSigtap(int ageCodigo) throws SQLException {
    sql = "SELECT DISTINCT  proc.proc_codigo_sus FROM esus_ficha_procedimento esf "
      + "INNER JOIN procedimento proc ON esf.proc_codigo=proc.proc_codigo AND proc.proc_codigo_sus IS NOT NULL "
      + "WHERE esf.age_codigo = ?";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setInt(1, ageCodigo);
    return pstmt.executeQuery();
  }

  public boolean atualizaStatus(String uuid, int codigo) throws SQLException {
    sql = "UPDATE esus_ficha_procedimento SET uuid_ficha = ? WHERE age_codigo = ?";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setString(1, uuid);
    pstmt.setInt(2, codigo);
    pstmt.executeUpdate();
    return true;
  }

  private HashMap getSubDadosProcedimentos(HashMap hash) throws SQLException {
    builder = new StringBuilder("SELECT");
    builder.append("  (SELECT COUNT(*) ")
      .append("   FROM procedimento_atendimento AS pat ")
      .append("     INNER JOIN atendimento AS ate ON pat.ate_codigo = ate.ate_codigo ")
      .append("     INNER JOIN agendamento AS age ON ate.age_codigo = age.age_codigo ")
      .append("     INNER JOIN procedimento AS proc ON pat.proc_codigo = proc.proc_codigo ")
      .append("   WHERE age.age_codigo = ? AND proc.proc_codigo_sus = '0301100039') ")
      .append("    AS numTotalAfericaoPa, ")
      .append("  (SELECT COUNT(*) ")
      .append("   FROM procedimento_atendimento AS pat ")
      .append("     INNER JOIN atendimento AS ate ON pat.ate_codigo = ate.ate_codigo ")
      .append("     INNER JOIN agendamento AS age ON ate.age_codigo = age.age_codigo ")
      .append("     INNER JOIN procedimento AS proc ON pat.proc_codigo = proc.proc_codigo ")
      .append("   WHERE age.age_codigo = ? AND proc.proc_codigo_sus = '0214010015') ")
      .append("    AS numTotalGlicemiaCapilar, ")
      .append("  (SELECT COUNT(*) ")
      .append("   FROM procedimento_atendimento AS pat ")
      .append("     INNER JOIN atendimento AS ate ON pat.ate_codigo = ate.ate_codigo ")
      .append("     INNER JOIN agendamento AS age ON ate.age_codigo = age.age_codigo ")
      .append("     INNER JOIN procedimento AS proc ON pat.proc_codigo = proc.proc_codigo ")
      .append("   WHERE age.age_codigo = ? AND proc.proc_codigo_sus = '0101040024') ")
      .append("    AS numTotalMedicaoAlturaPeso, ")
      .append("  (SELECT COUNT(*) ")
      .append("   FROM procedimento_atendimento AS pat ")
      .append("     INNER JOIN atendimento AS ate ON pat.ate_codigo = ate.ate_codigo ")
      .append("     INNER JOIN agendamento AS age ON ate.age_codigo = age.age_codigo ")
      .append("     INNER JOIN procedimento AS proc ON pat.proc_codigo = proc.proc_codigo ")
      .append("   WHERE age.age_codigo = ? AND proc.proc_codigo_sus = '0201020041') ")
      .append("    AS numTotalExame, ")
      .append("  (SELECT COUNT(*) ")
      .append("   FROM procedimento_atendimento AS pat ")
      .append("     INNER JOIN atendimento AS ate ON pat.ate_codigo = ate.ate_codigo ")
      .append("     INNER JOIN agendamento AS age ON ate.age_codigo = age.age_codigo ")
      .append("     INNER JOIN procedimento AS proc ON pat.proc_codigo = proc.proc_codigo ")
      .append("   WHERE age.age_codigo = ? AND proc.proc_codigo_sus = '0401010023') ")
      .append("    AS numTotalCurativo, ")
      .append("  (SELECT COUNT(*) ")
      .append("   FROM procedimento_atendimento AS pat ")
      .append("     INNER JOIN atendimento AS ate ON pat.ate_codigo = ate.ate_codigo ")
      .append("     INNER JOIN agendamento AS age ON ate.age_codigo = age.age_codigo ")
      .append("     INNER JOIN procedimento AS proc ON pat.proc_codigo = proc.proc_codigo ")
      .append("   WHERE age.age_codigo = ? AND proc.proc_codigo_sus = 'ABPG034') ")
      .append("    AS numTotalAfericaoTemperatura");
    pstmt = CONN.prepareStatement(builder.toString());
    for (int i = 1; i <= pstmt.getParameterMetaData().getParameterCount(); i++) {
      pstmt.setInt(i, ((Long) hash.get("age_codigo")).intValue());
    }
    ResultSet rs = pstmt.executeQuery();
    return Util.addToHashMap(hash, rs);
  }

}