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 BancoCadastroDomiciliar {
  protected static String sql = "";
  protected static PreparedStatement pstmt;

  public List<HashMap> getDadosCadastroDomiciliar() throws SQLException {
    sql = "SELECT DISTINCT co_cds_cad_domiciliar, usr.cnes_cod_cns AS cns_profissional,usr.usr_codigo, "
      + "uni.uni_cnes AS cnes_unidade,uni.uni_codigo_ibge AS codigo_ibge_municipio,tbe.nu_ine, "
      + "CASE WHEN tdcdr.co_data_atendimento is not null THEN tdcdr.co_data_atendimento ELSE dom.dom_data_cadastro END AS data_atendimento,"
      + "bai.bai_nome AS bairro,REPLACE(rua.rua_cep,'-','') AS cep,"
      + "cid.cid_codigo_ibge,dom.dom_complemento AS complemento,rua.rua_nome AS nome_logradouro, "
      + "UPPER(dom.dom_ponto_referencia) AS dom_ponto_referencia, dom.dom_telefone AS dom_telefone, "
      + "tpl.co_tipo_logradouro AS tipo_logradouro_numero_dne,dom.dom_numero AS numero,dom.tipo_imovel, "
      + "uf.uf_cod_esus AS estado,"
      + "mic.mic_descricao AS dom_microarea, dom.dom_microarea_fa AS dom_microarea_fa, "
      + "upper(ipe_nome) AS ipe_nome, ipe_st_outros AS ipe_st_outros, ipe_resp_nome AS ipe_resp_nome, "
      + "ipe_resp_cns AS ipe_resp_cns, upper(ipe_resp_cargo) AS ipe_resp_cargo, ipe_resp_telefone AS ipe_resp_telefone, "
      + "dom.usu_recusa as usu_recusa "
      + "FROM  tb_cds_domicilio_resposta tdcdr "
      + "INNER JOIN domicilio dom ON tdcdr.co_cds_cad_domiciliar=dom.dom_codigo "
      + "INNER JOIN usuarios usr ON dom.usr_codigo=usr.usr_codigo "
      + "INNER JOIN unidade uni ON dom.uni_codigo=uni.uni_codigo "
      + "INNER JOIN rua ON dom.rua_codigo=rua.rua_codigo "
      + "INNER JOIN bairro bai ON rua.bai_codigo=bai.bai_codigo "
      + "LEFT JOIN distrito dis ON bai.dis_codigo=dis.dis_codigo "
      + "INNER JOIN cidade cid ON (bai.cid_codigo=cid.cid_codigo OR dis.cid_codigo=cid.cid_codigo) "
      + "INNER JOIN estado uf ON cid.uf_codigo=uf.uf_codigo "
      + "LEFT JOIN tb_equipe tbe ON dom.cod_equipe=tbe.co_seq_equipe "
      + "INNER JOIN tb_ms_tipo_logradouro tpl ON tpl.co_tipo_logradouro = rua.co_tipo_logradouro "
      + "LEFT JOIN microarea mic ON mic.mic_codigo = dom.dom_microarea "
      + "LEFT JOIN instituicao_permanencia ipe ON ipe.dom_codigo = dom.dom_codigo "
      + "WHERE (uuid_ficha IS NULL OR uuid_ficha='') AND dom.tipo_imovel IS NOT NULL AND dom.usu_codigo_responsavel IS NOT NULL ";
    pstmt = CONN.prepareStatement(sql);
    ResultSet rs = pstmt.executeQuery();
    List<HashMap> listAux = Util.resultSetToArrayList(rs);
    List<HashMap> list = new ArrayList<>();
    for (HashMap hash : listAux) {
      list.add(getSubDadosCadastroDomiciliar(hash));
      Integer atual = (listAux.indexOf(hash) + 1);
      Integer total = listAux.size();
      Util.showLoading("Cadastro Domiciliar", atual, total);
    }
    rs.close();
    return list;
  }

  public ResultSet getAnimaisNoDomicilio(int codDom) throws SQLException {
    sql = "SELECT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta tbdr "
      + "INNER JOIN tb_pergunta_detalhe tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar = ? "
      + "AND tbdr.co_pergunta = 71 ";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setInt(1, codDom);
    return pstmt.executeQuery();
  }

  public ResultSet getDadosFamilia(int domCodigo) throws SQLException {
    sql = "SELECT usu_codigo_responsavel,"
      + " usu.usu_datanasc,"
      + " usu.usu_cartao_sus,"
      + " usu.usu_prontuario,"
      + " usu.usu_cpf,"
      + " dom.dom_codigo,"
      + " dom.dom_data_cadastro"
      + " FROM domicilio dom "
      + "INNER JOIN usuario usu ON usu.usu_codigo=dom.usu_codigo_responsavel "
      + "WHERE dom.dom_codigo = ? AND (usu.usu_cartao_sus IS NOT NULL OR usu.usu_cpf IS NOT NULL)";
    pstmt = CONN.prepareStatement(sql);

    pstmt.setInt(1, domCodigo);
    return pstmt.executeQuery();
  }

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

  public ResultSet getCbo(int usrCodigo) throws SQLException {
    sql = "select esp.cod_cbo from usuarios usr "
      + "join medico_especialidade me on me.med_codigo = usr.usr_codigo "
      + "join especialidade esp on esp.esp_codigo = me.esp_codigo "
      + "where me.med_codigo = ? limit 1";
    pstmt = CONN.prepareStatement(sql);
    pstmt.setInt(1, usrCodigo);
    return  pstmt.executeQuery();
  }

  private HashMap getSubDadosCadastroDomiciliar(HashMap hash) throws SQLException {
    sql = "SELECT (SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 57 LIMIT 1) AS situacao_moradia, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 58 LIMIT 1)  AS localizacao, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 59 LIMIT 1)  AS tipo_domicilio, "
      + "(SELECT  DISTINCT tbdr.ds_resposta FROM tb_cds_domicilio_resposta AS tbdr "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 60 LIMIT 1)  AS numero_moradores, "
      + "(SELECT  DISTINCT tbdr.ds_resposta FROM tb_cds_domicilio_resposta AS tbdr "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 61 LIMIT 1)  AS numero_comodos, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 62 LIMIT 1)  AS tipo_acesso, "
      + "(SELECT DISTINCT tbdr.ds_resposta FROM tb_cds_domicilio_resposta AS tbdr "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 63 LIMIT 1)  AS energia_eletrica, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 64 LIMIT 1)  AS destino_lixo, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 65 LIMIT 1)  AS tratamento_agua, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 66 LIMIT 1)  AS posse_terra, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 67 LIMIT 1)  AS material_domicilio, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 68 LIMIT 1)  AS abastecimento_agua, "
      + "(SELECT DISTINCT tbpd.co_pergunta_detalhe FROM tb_cds_domicilio_resposta AS tbdr "
      + "INNER JOIN tb_pergunta_detalhe AS tbpd ON tbdr.co_pergunta_detalhe=tbpd.co_pergunta_detalhe "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 69 LIMIT 1)  AS escoamento_sanitario, "
      + "(SELECT DISTINCT tbdr.co_resposta FROM tb_cds_domicilio_resposta AS tbdr "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 70 LIMIT 1)  AS animais_domicilio, "
      + "(SELECT DISTINCT tbdr.ds_resposta FROM tb_cds_domicilio_resposta AS tbdr "
      + "WHERE tbdr.co_cds_cad_domiciliar= ? AND tbdr.co_pergunta = 72 LIMIT 1)  AS quantidade_animais_domicilio";
    pstmt = CONN.prepareStatement(sql);
    for (int i = 1; i <= pstmt.getParameterMetaData().getParameterCount(); i++) {
      pstmt.setInt(i, (Integer) hash.get("co_cds_cad_domiciliar"));
    }
    ResultSet rs = pstmt.executeQuery();
    return Util.addToHashMap(hash, rs);
  }

}