package br.com.centralit.citcorpore.integracao;

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

import org.apache.commons.lang.StringUtils;

import br.com.centralit.citcorpore.bean.GrupoDTO;
import br.com.centralit.citcorpore.bean.SmartReportDTO;
import br.com.centralit.citcorpore.bean.UsuarioDTO;
import br.com.centralit.citcorpore.util.Enumerados.ReportCategories;
import br.com.centralit.citcorpore.util.Enumerados.TipoExibicaoRelatorio;
import br.com.centralit.citgerencial.bean.SmartReportParameterOptionDTO;
import br.com.centralit.citgerencial.bean.SmartReportParameterOptionsDTO;
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.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.SQLConfig;

public class SmartReportDAO extends CrudDaoDefaultImpl {

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

	@Override
	public Collection<SmartReportDTO> find(final IDto obj) {
		return null;
	}

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

		listFields.add(new Field("idSmartReport", "idSmartReport", true, true, false, false));
		listFields.add(new Field("identificacao", "identificacao", false, false, false, false));
		listFields.add(new Field("descricao", "descricao", false, false, false, false));
		listFields.add(new Field("idCategoria", "idCategoria", false, false, false, false));
		listFields.add(new Field("tipoConsulta", "tipoConsulta", false, false, false, false));
		listFields.add(new Field("tipoRelatorio", "tipoRelatorio", false, false, false, false));
		listFields.add(new Field("parametros", "parametros", false, false, false, false));
		listFields.add(new Field("sql", "sql", false, false, false, false));
		listFields.add(new Field("script", "script", false, false, false, false));
		listFields.add(new Field("jsp", "jsp", false, false, false, false));
		listFields.add(new Field("template", "template", false, false, false, false));
		listFields.add(new Field("tipoExibicao", "tipoExibicao", false, false, false, false));
		listFields.add(new Field("moduloExibicao", "moduloExibicao", false, false, false, false));
		listFields.add(new Field("dataFim", "dataFim", false, false, false, false));

		return listFields;
	}

	@Override
	public String getTableName() {
		return "smartreport";
	}

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

	public List execSQL(SmartReportDTO smartReportDTO) throws Exception {
		return this.execSQL(smartReportDTO.getSql(), null);
	}

	public List<Map<String, Object>> executeQuery(SmartReportDTO smartReportDTO, Object[] parametros) throws Exception {
		return this.executeQuery(smartReportDTO.getSql(), parametros);
	}

	public List<String> getResultColumns(SmartReportDTO smartReportDTO, Object[] parametros) throws Exception {
		return this.getResultColumns(smartReportDTO.getSql(), parametros);
	}

	public List<SmartReportDTO> findAtivos() throws Exception {
		List<Condition> condition = new ArrayList<Condition>();
		condition.add(new Condition("dataFim", Condition.IS, null));

		return (List<SmartReportDTO>) this.findByCondition(condition, null);
	}

	public List<SmartReportDTO> findByIdsGrupoUsuarioAndIdCategoria(Collection<GrupoDTO> gruposUsuario, Integer idCategoria) throws Exception {
		StringBuilder sql = new StringBuilder();

		sql.append("select distinct \n");
		sql.append("	relatorio.idSmartReport, \n");
		sql.append("	relatorio.identificacao \n");
		sql.append("from \n");
		sql.append("	smartreport relatorio \n");
		sql.append("	left join smartreportgrupo relatorioGrupo on relatorioGrupo.idSmartReport = relatorio.idSmartReport \n");
		sql.append("where \n");
		if (gruposUsuario != null && !gruposUsuario.isEmpty()) {
			sql.append("	(relatorioGrupo.idgrupo in (" + getIdsGrupoUsuario(gruposUsuario) + ") or relatorioGrupo.idgrupo is null) and \n");
		}
		sql.append("	datafim is null \n");
		sql.append("	and idCategoria = ?");

		List dados = this.execSQL(sql.toString(), new Object[]{ idCategoria });

		List<String> fields = new ArrayList<String>();

		fields.add("idSmartReport");
		fields.add("identificacao");

		return this.listConvertion(this.getBean(), dados, fields);
	}

	public Integer getTotalByIdsGrupoUsuarioAndIdCategoria(Collection<GrupoDTO> gruposUsuario, Integer idCategoria) throws Exception {
		StringBuilder sql = new StringBuilder();

		Object[] params = null;

		sql.append("select \n");
		sql.append("	count(1) \n");
		sql.append("from \n");
		sql.append("	smartreport relatorio \n");
		sql.append("	left join smartreportgrupo relatorioGrupo on relatorioGrupo.idSmartReport = relatorio.idSmartReport \n");
		sql.append("	where \n");
		if (gruposUsuario != null && !gruposUsuario.isEmpty()) {
			sql.append("	(relatorioGrupo.idgrupo in (" + getIdsGrupoUsuario(gruposUsuario) + ") or relatorioGrupo.idgrupo is null) and \n");
		}
		params = new Object[]{ idCategoria };
		sql.append("	idCategoria = ? \n");
		sql.append("	and datafim is null ");

		List dados = this.execSQL(sql.toString(), params);
		Object[] totalLinha = (Object[]) dados.get(0);

		return Integer.valueOf(totalLinha[0] + "");
	}

	public Collection<SmartReportParameterOptionDTO> findParametroOptions(SmartReportParameterOptionsDTO options) throws Exception {
		String sqlParm = options.getSql();

		Collection<SmartReportParameterOptionDTO> smartReportParametroOptionDTOs = new ArrayList<SmartReportParameterOptionDTO>();
		List listReturn = null;

		try {
			listReturn = execSQL(sqlParm, new Object[]{});
		} catch (Exception e) {
			listReturn = null;
		}

		if (listReturn != null && listReturn.size() > 0) {
			for (int i = 0; i < listReturn.size(); i++) {
				Object[] row = (Object[]) listReturn.get(i);

				SmartReportParameterOptionDTO option = new SmartReportParameterOptionDTO();
				for (int z = 0; z < row.length; z++) {
					Object obj = row[z];
					if(obj != null){
						if (z == 0) {
							option.setValue(obj.toString());
						}
						if (z == 1) {
							option.setText(obj.toString());
						}
					}
				}
				smartReportParametroOptionDTOs.add(option);
			}
		}

		return smartReportParametroOptionDTOs;
	}

	public Collection<SmartReportDTO> findByIdentificacao(String consulta) throws Exception {
		if (consulta == null) {
			consulta = "";
        }
		String sql = "select idSmartReport, identificacao, idCategoria from " + this.getTableName() + " where upper(identificacao) like upper(?)";

		consulta = "%" + consulta.toUpperCase() + "%";
        final Object[] params = new Object[] {consulta};
        final List list = this.execSQL(sql, params);

        final List<String> listRetorno = new ArrayList<>();
		listRetorno.add("idSmartReport");
		listRetorno.add("identificacao");
        listRetorno.add("idCategoria");

        return this.listConvertion(this.getBean(), list, listRetorno);
	}

	public SmartReportDTO getByIdentificacao(String identificacao) throws Exception {

		String sql =  "select * from " + this.getTableName() + " where identificacao = ?";
		List result = super.execSQL(sql, new Object[]{ identificacao });
		List smartReportDTOs = this.listConvertion(this.getBean(), result, (List) this.getFields());

		if(smartReportDTOs != null && smartReportDTOs.size() > 0) {
			return (SmartReportDTO) smartReportDTOs.get(0);
		}

		return null;
	}

	public Page<SmartReportDTO> findPaged(String filter, Pageable pageable, boolean isTotalizacao) throws Exception {

		Page<SmartReportDTO> taskPage;

		StringBuilder selectQueryPiece = new StringBuilder("SELECT ");
		selectQueryPiece.append("	* ");

		StringBuilder fromWhereQueryPiece = new StringBuilder("FROM ");
		fromWhereQueryPiece.append("	" + this.getTableName());

		fromWhereQueryPiece.append(" WHERE ");
		fromWhereQueryPiece.append("	datafim is null");
		if(filter != null) {
			fromWhereQueryPiece.append("	and lower(identificacao) LIKE lower('%" + filter + "%')");
		}

		String sql = "";
		List lista = new ArrayList();
		if(isTotalizacao) {
			final StringBuilder sqlCount = this.countQueryPiece(fromWhereQueryPiece);
			final Long totalElements = this.countElements(sqlCount.toString(), null);
			final List<SmartReportDTO> result = new ArrayList<SmartReportDTO>();
			taskPage = this.makePage(result, pageable, totalElements);
		}else{
			if (SQLConfig.isSqlServerSGBDPrincipal()) {
				sql = PagingQueryUtil.constructsSQLServerPagingPiece(pageable, selectQueryPiece.toString(), "ORDER BY idsmartreport", fromWhereQueryPiece.toString());
			} else {
				selectQueryPiece.append(fromWhereQueryPiece);
				sql = PagingQueryUtil.concatPagingPieceOnQuery(pageable, selectQueryPiece.toString(), MAIN_SGBD);
			}

			lista = this.execSQL(sql, null);

			List<SmartReportDTO> result = this.engine.listConvertion(getBean(), lista, (List<Field>) this.getFields());
			taskPage = new PageImpl<SmartReportDTO>(result, pageable, 1L);
		}

		return taskPage;
	}

	public List<SmartReportDTO> findByIdsGrupoUsuarioAndTipoModuloExibicao(Collection<GrupoDTO> gruposUsuario, String tipoExibicao, String moduloExibicao) throws Exception {
		StringBuilder sql = new StringBuilder();

		sql.append("select distinct \n");
		sql.append("	relatorio.idSmartReport, \n");
		sql.append("	relatorio.identificacao \n");
		sql.append("from \n");
		sql.append("	smartreport relatorio \n");
		sql.append("	left join  smartreportgrupo relatorioGrupo on relatorioGrupo.idSmartReport = relatorio.idSmartReport \n");
		sql.append("where ");
		if (gruposUsuario != null && !gruposUsuario.isEmpty()) {
			sql.append("		(relatorioGrupo.idgrupo in (" + getIdsGrupoUsuario(gruposUsuario) + ") or relatorioGrupo.idgrupo is null) and \n");
		}
		sql.append("tipoExibicao = '").append(tipoExibicao).append("' \n");
		if (TipoExibicaoRelatorio.ESPECIFICO.getIdentificador().equalsIgnoreCase(tipoExibicao)) {
			sql.append("and moduloExibicao = '").append(moduloExibicao).append("'");
		} else {
			sql.append("and moduloExibicao like '%").append(moduloExibicao).append("%'");
		}

		List dados = this.execSQL(sql.toString(), null);

		List<String> fieldsRetorno = new ArrayList<String>();
		fieldsRetorno.add("idSmartReport");
		fieldsRetorno.add("identificacao");

		return this.listConvertion(this.getBean(), dados, fieldsRetorno);
	}

	public List<SmartReportDTO> findAvailablesByUsuario(UsuarioDTO usuarioDTO) throws PersistenceException {
		StringBuilder sql = new StringBuilder();
		List params = new ArrayList<>();

		sql.append("select distinct \n");
		sql.append("	relatorio.idSmartReport, \n");
		sql.append("	relatorio.descricao, \n");
		sql.append("	relatorio.tipoRelatorio, \n");
		sql.append("	cast(relatorio.identificacao as varchar(254)), \n");
		sql.append("	cast(relatorio.parametros as varchar(254)) \n");
		sql.append("from \n");
		sql.append("	smartreport relatorio \n");
		sql.append("	left join  smartreportgrupo relatorioGrupo on relatorioGrupo.idSmartReport = relatorio.idSmartReport \n");
		sql.append("where \n");
		sql.append("	upper(relatorio.tipoExibicao) != upper('").append(TipoExibicaoRelatorio.ESPECIFICO.getIdentificador()).append("') \n");

		if (usuarioDTO.getColGrupos() != null && !usuarioDTO.getColGrupos().isEmpty()) {
			sql.append("	and (relatorioGrupo.idgrupo in (" + getIdsGrupoUsuario(usuarioDTO.getColGrupos()) + ") or relatorioGrupo.idgrupo is null) \n");
		}

		List dados = this.execSQL(sql.toString(), null);

		List<String> fieldsRetorno = new ArrayList<String>();
		fieldsRetorno.add("idSmartReport");
		fieldsRetorno.add("descricao");
		fieldsRetorno.add("tipoRelatorio");
		fieldsRetorno.add("identificacao");
		fieldsRetorno.add("parametros");

		return this.listConvertion(this.getBean(), dados, fieldsRetorno);
	}

	private String getIdsCategoria(List<ReportCategories> reportCategories) {
		Integer[] idsCategoria = new Integer[reportCategories.size()];

		for (int i = 0; i < reportCategories.size(); i++) {
			idsCategoria[i] = reportCategories.get(i).getId();
		}

		return StringUtils.join(idsCategoria, ",");
	}

	private String getIdsGrupoUsuario(Collection<GrupoDTO> gruposUsuario) {
		Integer[] idsGrupoUsuario = new Integer[gruposUsuario.size()];

		int i = 0;
		for (GrupoDTO grupoDto : gruposUsuario) {
			idsGrupoUsuario[i] = grupoDto.getIdGrupo();
			i++;
		}

		return StringUtils.join(idsGrupoUsuario, ",");
	}
}
