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

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

/**
 * @author elotech
 */
public class BancoConsumoAlimentar {
  protected static String sql = "";
  protected static PreparedStatement pstmt;

  public ResultSet getDadosConsumoAlimentar() throws SQLException {
    sql = "SELECT eca.*, usu.usu_cpf "
      + "FROM esus_consumo_alimentar eca "
      + "  INNER JOIN tb_cds_consumo_alimentar tcca ON tcca.co_seq_cds_consumo_alimentar = eca.co_cds_consumo_alimentar "
      + "  INNER JOIN atendimento ate ON tcca.ate_codigo = ate.ate_codigo "
      + "  INNER JOIN usuario usu ON usu.usu_codigo = ate.usu_codigo "
      + "WHERE uuid_ficha IS NULL "
      + "OR    uuid_ficha = ''";
    return CONN.prepareStatement(sql).executeQuery();
  }

  public ResultSet getPerguntasQuestionarioCriancasMenoresSeisMeses(int codAtiv) throws SQLException {
    sql = "select tqq.no_qst_identificador as no_qst_pergunta, tqr.no_qst_identificador as no_qst_resposta "
      + "from tb_cds_consumo_alimentar_resposta tca "
      + "inner join tb_qst_questao tqq on tqq.co_qst_questao = tca.co_qst_questao "
      + "inner join tb_qst_resposta tqr on tqr.co_qst_resposta = tca.co_qst_resposta "
      + "where tca.co_cds_consumo_alimentar = ? "
      + "and tca.co_qst_questao in (1,3,4,5,6,7,8,9,10)";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setInt(1, codAtiv);
    ResultSet rs = pstmt.executeQuery();
    return rs;
  }

  public ResultSet getPerguntasQuestionarioCriancasDeSeisVinteTresMeses(int codAtiv) throws SQLException {
    sql = "select tqq.no_qst_identificador as no_qst_pergunta, tqr.no_qst_identificador as no_qst_resposta "
      + "from tb_cds_consumo_alimentar_resposta tca "
      + "inner join tb_qst_questao tqq on tqq.co_qst_questao = tca.co_qst_questao "
      + "inner join tb_qst_resposta tqr on tqr.co_qst_resposta = tca.co_qst_resposta "
      + "where tca.co_cds_consumo_alimentar = ? "
      + "and tca.co_qst_questao in (21,22,23,24,25,26,28,29,30,31,32,33,34,35,36,37,38,39,40,41)";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setInt(1, codAtiv);
    ResultSet rs = pstmt.executeQuery();
    return rs;
  }

  public ResultSet getPerguntasQuestionarioCriancasComMaisDoisAnos(int codAtiv) throws SQLException {
    sql =
      "select tca.co_qst_questao as co_qst_pergunta, tqq.no_qst_identificador as no_qst_pergunta, tqr.no_qst_identificador as no_qst_resposta "
        + "from tb_cds_consumo_alimentar_resposta tca "
        + "inner join tb_qst_questao tqq on tqq.co_qst_questao = tca.co_qst_questao "
        + "inner join tb_qst_resposta tqr on tqr.co_qst_resposta = tca.co_qst_resposta "
        + "where tca.co_cds_consumo_alimentar = ? "
        + "and tca.co_qst_questao in (11,12,14,15,16,17,18,19,20) "
        + "order by tca.co_qst_questao asc, tca.co_qst_resposta asc";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setInt(1, codAtiv);
    ResultSet rs = pstmt.executeQuery();
    return rs;
  }

  public int getQtdRegistros() throws SQLException {
    sql = "SELECT count(*) FROM esus_consumo_alimentar WHERE uuid_ficha IS NULL OR uuid_ficha = ''";
    pstmt = CONN.prepareStatement(sql);
    ResultSet rs = pstmt.executeQuery();
    int numRegistro = 0;
    if (rs.next()) {
      numRegistro = rs.getInt(1);
    }

    return numRegistro;
  }

  public ResultSet getDadosOriginadoraRemetente() throws SQLException {
    sql = "SELECT * FROM esus_remente_originadora WHERE ero_status = 't'";
    ResultSet rs = CONN.prepareStatement(sql).executeQuery();
    return rs;
  }

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

}