package br.com.centralit.citcorpore.integracao;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import br.com.centralit.citcorpore.bean.GerenciamentoPortfolioServicoContratoANSDTO;
import br.com.centralit.citcorpore.bean.ServicoContratoANSDTO;
import br.com.centralit.citcorpore.bean.ServicoContratoCalendarioDTO;
import br.com.citframework.dto.IDto;
import br.com.citframework.excecao.PersistenceException;
import br.com.citframework.integracao.Condition;
import br.com.citframework.integracao.CrudDaoDefaultImpl;
import br.com.citframework.integracao.Field;
import br.com.citframework.integracao.core.DataBase;
import br.com.citframework.integracao.core.Page;
import br.com.citframework.integracao.core.PageImpl;
import br.com.citframework.integracao.core.Pageable;
import br.com.citframework.integracao.core.PagingQueryUtil;
import br.com.citframework.util.Constantes;
import br.com.citframework.util.UtilStrings;

public class ServicoContratoANSDao extends CrudDaoDefaultImpl {

	public ServicoContratoANSDao() {
		super(Constantes.getValue("DATABASE_ALIAS"), null);
	}

	@Override
	public Collection<Field> getFields() {
		Collection<Field> listFields = new ArrayList<>();

		listFields.add(new Field("idServicoContratoANS", "idServicoContratoANS", true, true, false, false));
		listFields.add(new Field("idServicoContrato", "idServicoContrato", false, false, false, false));
		listFields.add(new Field("idAcordoNivelServico", "idAcordoNivelServico", false, false, false, false));
		listFields.add(new Field("dataInicio", "dataInicio", false, false, false, false));
		listFields.add(new Field("dataFim", "dataFim", false, false, false, false));

		return listFields;
	}


	@Override
	public String getTableName() {
		return this.getOwner() + "servicocontratoans";
	}

	@Override
	public Class<ServicoContratoANSDTO> getBean() {
		return ServicoContratoANSDTO.class;
	}

	@Override
	public void updateNotNull(IDto obj) throws PersistenceException {
		super.updateNotNull(obj);
	}

	@SuppressWarnings("unchecked")
	public Page<GerenciamentoPortfolioServicoContratoANSDTO> findByIdServicoContrato(Integer idServicoContrato, String filtro, final Pageable pageable, Boolean isTotalizacao) throws Exception {
		Page<GerenciamentoPortfolioServicoContratoANSDTO> taskPage;

		StringBuilder selectQueryPiece = new StringBuilder("SELECT ");
		selectQueryPiece.append(getNamesFieldsStr("scans")).append(", tituloSLA, disponibilidade ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append(getTableName()).append(" scans ");
		fromWhereQueryPiece.append("	INNER JOIN acordonivelservico ans on scans.idAcordoNivelServico = ans.idAcordoNivelServico ");
		fromWhereQueryPiece.append("WHERE ");
		fromWhereQueryPiece.append("	scans.idServicoContrato = ").append(idServicoContrato).append(" ");

		StringBuilder orderBy = new StringBuilder("ORDER BY scans.idServicoContratoANS desc ");

		if (UtilStrings.isNotVazio(filtro)) {
			try {
				Integer id = Integer.parseInt(filtro);
				fromWhereQueryPiece.append(" and (scans.idServicoContratoANS = " + id + " ");
				fromWhereQueryPiece.append(" or upper(tituloSLA) like upper('%" + filtro + "%')) ");
			} catch (Exception e) {
				fromWhereQueryPiece.append(" and upper(tituloSLA) like upper('%" + filtro + "%') ");
			}
		}

		String sql = "";
		if (isTotalizacao) {
			final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
			final Long totalElements = this.countElements(sqlCount.toString(), null);
			final List<GerenciamentoPortfolioServicoContratoANSDTO> result = new ArrayList<GerenciamentoPortfolioServicoContratoANSDTO>();
			taskPage = this.makePage(result, pageable, totalElements);

		} else {
			if (pageable != null) {
				if (MAIN_SGBD.equals(DataBase.MSSQLSERVER)) {
					sql = PagingQueryUtil.constructsSQLServerPagingPiece(pageable, selectQueryPiece.toString(), orderBy.toString(), fromWhereQueryPiece.toString());
				} else {
					selectQueryPiece.append(fromWhereQueryPiece);
					selectQueryPiece.append(orderBy);
					sql = PagingQueryUtil.concatPagingPieceOnQuery(pageable, selectQueryPiece.toString(), MAIN_SGBD);
				}
			} else {
				sql = selectQueryPiece.toString() + fromWhereQueryPiece.toString() + orderBy.toString();
			}

			List<?> lista = this.execSQL(sql, null);

			List<String> camposRetorno = getListNamesFieldClass();
			camposRetorno.add("tituloSla");
			camposRetorno.add("disponibilidade");

			List<GerenciamentoPortfolioServicoContratoANSDTO> result = this.engine.listConvertion(GerenciamentoPortfolioServicoContratoANSDTO.class, lista, camposRetorno);
			taskPage = new PageImpl<GerenciamentoPortfolioServicoContratoANSDTO>(result, pageable, 1L);
		}

		return taskPage;
	}

	public Boolean existeAnsAtivoByServicoContrato(Integer idServicoContrato) throws Exception {
		StringBuilder sb = new StringBuilder();

		sb.append("SELECT ");
		sb.append(getNamesFieldsStr()).append(" ");
		sb.append("FROM ");
		sb.append(getTableName()).append(" ");
		sb.append("WHERE ");
		sb.append("	idServicoContrato = ").append(idServicoContrato).append(" ");
		sb.append("	and (dataFim is null) ");
		sb.append("ORDER BY ");
		sb.append("	idServicoContratoANS desc");

		List<?> list = this.execSQL(sb.toString(), null);

		if (list != null && !list.isEmpty()) {
			return true;
		}

		return false;
	}
	
	public Collection<ServicoContratoANSDTO> getServicoContratoANSDTOAtivoByIdServicoContrato(Integer idServicoContrato) throws PersistenceException {
		List<Condition> condicao = new ArrayList<>();
		condicao.add(new Condition("idServicoContrato", Condition.EQUALS, idServicoContrato));
		condicao.add(new Condition("dataFim", Condition.IS, null));
		return this.findByCondition(condicao, new ArrayList<>());
	}
}