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;

public class BancoFichaComplementar {

  protected static String sql = "";
  protected static PreparedStatement pstmt;

  public ResultSet getDados() throws SQLException {
    StringBuilder builder = new StringBuilder();
    builder.append(" SELECT ")
      .append("  efc.efc_codigo              AS codigo,")
      .append("  efc.efc_turno               AS turno,")
      .append("  usu.usu_cartao_sus          AS cnsCidadao,")
      .append("  usu.usu_cpf                 AS cpfCidadao,")
      .append("  resp.usu_cartao_sus         AS cnsResponsavelFamiliar,")
      .append("  resp.usu_cartao_sus         AS cpfResponsavelFamiliar,")
      .append("  efc.efc_data_olhinho        AS dataRealizacaoTesteOlhinho,")
      .append("  efc.efc_res_olhinho         AS coResultadoTesteOlhinho,")
      .append("  efc.efc_data_fundo          AS dataRealizacaoExameFundoOlho,")
      .append("  efc.efc_res_fundo           AS coResultadoExameFundoOlho,")
      .append("  efc.efc_data_orelhinha      AS dataRealizacaoTesteOrelhinha,")
      .append("  efc.efc_res_orelhinha       AS coResultadoTesteOrelhinha,")
      .append("  efc.efc_data_transfontanela AS dataRealizacaoUSTransfontanela,")
      .append("  efc.efc_res_transfontanela  AS coResultadoUsTransfontanela,")
      .append("  efc.efc_data_tomografia     AS dataRealizacaoTomografiaComputadorizada,")
      .append("  efc.efc_res_tomografia      AS coResultadoTomografiaComputadorizada,")
      .append("  efc.efc_data_ressonancia    AS dataRealizacaoRessonanciaMagnetica,")
      .append("  efc.efc_res_ressonancia     AS coResultadoRessonanciaMagnetica,")
      .append("  esp.cod_cbo                 AS cboCodigo_2002,")
      .append("  usr.cnes_cod_cns            AS profissionalCNS,")
      .append("  uni.uni_cnes                AS cnes,")
      .append("  teq.nu_ine                  AS ine,")
      .append("  efc.efc_data                AS dataAtendimento,")
      .append("  efc.efc_cod_igbe_mun        AS codigoIbgeMunicipio")
      .append(" FROM esus_ficha_complementar AS efc")
      .append(" INNER JOIN especialidade AS esp ON efc.esp_codigo = esp.esp_codigo")
      .append(" INNER JOIN usuarios AS usr ON efc.usr_codigo = usr.usr_codigo")
      .append(" INNER JOIN unidade AS uni ON efc.uni_codigo = uni.uni_codigo")
      .append(" INNER JOIN usuario AS usu ON efc.usu_codigo = usu.usu_codigo")
      .append(" LEFT JOIN usuario AS resp ON efc.efc_usu_responsavel = resp.usu_codigo")
      .append(" LEFT JOIN tb_equipe teq ON efc.usr_equipe_codigo = teq.co_seq_equipe")
      .append(" WHERE uuid_ficha IS NULL OR uuid_ficha = ''");

    pstmt = CONN.prepareStatement(builder.toString());
    return pstmt.executeQuery();
  }

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

}