package eu.dnetlib.enabling.datasources;

import java.io.StringWriter;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.collect.ImmutableMap;

import eu.dnetlib.enabling.datasources.common.Api;
import eu.dnetlib.enabling.datasources.common.ApiParam;
import eu.dnetlib.enabling.datasources.common.BrowsableField;
import eu.dnetlib.enabling.datasources.common.BrowseTerm;
import eu.dnetlib.enabling.datasources.common.BrowseTermImpl;
import eu.dnetlib.enabling.datasources.common.Datasource;
import eu.dnetlib.enabling.datasources.common.DsmException;
import eu.dnetlib.enabling.datasources.common.DsmForbiddenException;
import eu.dnetlib.enabling.datasources.common.DsmNotFoundException;
import eu.dnetlib.enabling.datasources.common.DsmRuntimeException;
import eu.dnetlib.enabling.datasources.common.Identity;
import eu.dnetlib.enabling.datasources.common.Organization;
import eu.dnetlib.enabling.datasources.common.PidSystem;
import eu.dnetlib.enabling.datasources.common.SearchApisEntry;
import eu.dnetlib.enabling.datasources.common.SimpleDatasource;

public class LocalOpenaireDatasourceManagerImpl implements LocalOpenaireDatasourceManager {

	private DatasourceManagerClients datasourceManagerClients;

	private List<DbBrowsableField> browsableFields;

	public static final String QUERY_BASEDIR = "/eu/dnetlib/enabling/datasources/queries/";

	private static final Resource searchDsByType = new ClassPathResource(QUERY_BASEDIR + "searchDsByType.sql");
	private static final Resource searchApis = new ClassPathResource(QUERY_BASEDIR + "searchApisNormal.sql");
	private static final Resource searchApisUsingField = new ClassPathResource(QUERY_BASEDIR + "searchApisUsingField.sql");
	private static final Resource addDs = new ClassPathResource(QUERY_BASEDIR + "addDatasource.sql");
	private static final Resource addOrg = new ClassPathResource(QUERY_BASEDIR + "addOrganization.sql");
	private static final Resource deleteDs = new ClassPathResource(QUERY_BASEDIR + "deleteDatasource.sql");
	private static final Resource setActive = new ClassPathResource(QUERY_BASEDIR + "setActive.sql");
	private static final Resource setManaged = new ClassPathResource(QUERY_BASEDIR + "setManaged.sql");
	private static final Resource setCompliance = new ClassPathResource(QUERY_BASEDIR + "setCompliance.sql");
	private static final Resource overrideCompliance = new ClassPathResource(QUERY_BASEDIR + "overrideCompliance.sql");
	private static final Resource resetCompliance = new ClassPathResource(QUERY_BASEDIR + "resetCompliance.sql");
	private static final Resource setLastCollectionInfo = new ClassPathResource(QUERY_BASEDIR + "setLastCollectionInfo.sql");
	private static final Resource setLastAggregationInfo = new ClassPathResource(QUERY_BASEDIR + "setLastAggregationInfo.sql");
	private static final Resource setLastDownloadInfo = new ClassPathResource(QUERY_BASEDIR + "setLastDownloadInfo.sql");
	private static final Resource setLastValidationJob = new ClassPathResource(QUERY_BASEDIR + "setLastValidationJob.sql");
	private static final Resource resetLastOperationsInfo = new ClassPathResource(QUERY_BASEDIR + "resetLastOperationsInfo.sql");
	private static final Resource insertApiParam = new ClassPathResource(QUERY_BASEDIR + "insertApiParam.sql");
	private static final Resource insertApi = new ClassPathResource(QUERY_BASEDIR + "insertApi.sql");
	private static final Resource setRemovable = new ClassPathResource(QUERY_BASEDIR + "setRemovable.sql");

	private static final Log log = LogFactory.getLog(LocalOpenaireDatasourceManagerImpl.class);

	@Override
	@Transactional(readOnly = true)
	public Set<String> listManagedDatasourceIds() throws DsmRuntimeException {
		try {
			return datasourceManagerClients.searchSQL("SELECT id FROM dsm_services WHERE managed = true", new HashMap<>())
				.stream()
				.map(m -> (String) m.get("id"))
				.collect(Collectors.toCollection(HashSet::new));
		} catch (final DsmException e) {
			throw new DsmRuntimeException(e);
		}
	}

	@Override
	@Transactional(readOnly = true)
	public List<SimpleDatasource> searchDatasourcesByType(final String type) throws DsmException {

		return datasourceManagerClients.searchSQL(searchDsByType, ImmutableMap.of("type", type))
			.stream()
			.map(DatasourceFunctions::mapToSimpleDs)
			.collect(Collectors.toList());
	}

	@Override
	@Transactional(readOnly = true)
	public List<? extends SearchApisEntry> searchApis(final String field, final Object value) throws DsmException {
		try {
			final StringWriter sql = new StringWriter();

			final Map<String, Object> params = new HashMap<>();
			if (field != null && value != null) {
				if (field.equalsIgnoreCase("__search__")) {
					params.put("value", "%" + value + "%");
					sql.append(IOUtils.toString(searchApis.getInputStream()));
				} else {
					sql.append(IOUtils.toString(searchApisUsingField.getInputStream()));

					if (field.equalsIgnoreCase("collbackend")) {
						sql.append("\"collMdId\" is not null and \"collMdId\" != '' and \"collMdId\" ");
						if (StringUtils.equalsIgnoreCase(value.toString(), MDStoreBackend.HDFS.toString())) {
							sql.append("like 'md-%' and length(\"collMdId\") <= 40");
						} else {
							// MONGO
							sql.append("not like 'md-%' and length(\"collMdId\") > 40");
						}
					} else if (field.equalsIgnoreCase("gateway")) {
						params.put("val1", value);
						params.put("val2", value);
						sql.append("primary_provide_gateway = :val1 or :val2 = ANY(string_to_array(affiliated_provide_gateways, ','))");
					} else {
						params.put("value", value);
						sql.append(field);
						sql.append("::text = ");
						sql.append(":value");
					}
				}

				return datasourceManagerClients.searchSQL(sql.toString(), params)
					.stream()
					.map(DatasourceFunctions::mapToSearchApisEntry)
					.collect(Collectors.toList());
			}
		} catch (final Exception e) {
			log.error("Error searching field " + field + " - value: " + value, e);
		}
		return new ArrayList<>();
	}

	@Override
	@Transactional
	public void saveDs(final Datasource<Organization<?>, Identity, PidSystem> ds) throws DsmException {

		if (StringUtils.isBlank(ds.getAggregator())) {
			ds.setAggregator("OPENAIRE");
		}

		ds.setManaged(true);

		datasourceManagerClients.updateSQL(ds.getId(), addDs, DatasourceFunctions.dsToMap(ds));

		if (ds.getOrganizations() != null) {
			for (final Organization<?> org : ds.getOrganizations()) {
				final Map<String, Object> orgParams = DatasourceFunctions.orgToMap(ds.getId(), org);
				datasourceManagerClients.updateSQL(ds.getId(), addOrg, orgParams);
			}
		}
		final Map<String, Object> params = new HashMap<>();
		params.put("removable", true);
		params.put("dsId", ds.getId());
		datasourceManagerClients.updateSQL(ds.getId(), setRemovable, params);
	}

	@Override
	@Transactional
	public void deleteDs(final String dsId) throws DsmException {
		datasourceManagerClients.updateSQL(dsId, deleteDs, ImmutableMap.of("dsId", dsId));
	}

	@Override
	public Datasource<Organization<?>, Identity, PidSystem> getDs(final String dsId) throws DsmException {
		return datasourceManagerClients.getDatasourceById(dsId);
	}

	@Override
	public Datasource<Organization<?>, Identity, PidSystem> getDsByNsPrefix(final String prefix) throws DsmException {
		return datasourceManagerClients.getDatasourceByPrefix(prefix);
	}

	@Override
	public List<Api<ApiParam>> getApis(final String dsId) throws DsmException {
		return datasourceManagerClients.getApis(dsId);
	}

	@Override
	public void setManaged(final String dsId, final boolean managed) throws DsmException {
		final String id = dsId;

		final Map<String, Object> params = new HashMap<>();
		params.put("managed", managed);
		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setManaged, params);

		params.clear();
		params.put("removable", true);
		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setRemovable, params);

	}

	@Override
	public boolean isManaged(final String dsId) throws DsmException {
		final String q = "SELECT id from dsm_services WHERE id = :dsId AND managed = true";
		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", dsId)).isEmpty();
	}

	@Override
	@Transactional
	public void setActive(final String dsId, final String apiId, final boolean active) throws DsmException {
		final String id = dsId;

		final Map<String, Object> params = new HashMap<>();
		params.put("active", active);
		params.put("apiId", apiId);
		params.put("dsId", id);

		datasourceManagerClients.updateSQL(id, setActive, params);
		if (!active) {
			datasourceManagerClients.updateSQL(dsId, resetLastOperationsInfo, params);
		}

		setManaged(id, true);
	}

	@Override
	@Transactional(readOnly = true)
	public boolean isActive(final String dsId, final String apiId) throws DsmException {
		final String q = "SELECT * from dsm_api WHERE id = :apiId AND service = :dsId AND active = true";
		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", dsId, "apiId", apiId)).isEmpty();
	}

	@Override
	@Transactional(readOnly = true)
	public boolean isRemovable(final String dsId, final String apiId) throws DsmException {
		final String q = "SELECT * from dsm_api WHERE id = :apiId AND service = :dsId AND active != true AND removable = true";
		return !datasourceManagerClients.searchSQL(q, ImmutableMap.of("dsId", dsId, "apiId", apiId)).isEmpty();
	}

	@Override
	@Transactional
	public void updateCompliance(final String dsId, final String apiId, final String level, final boolean override) throws DsmException {
		final String id = dsId;

		if (!override) {
			datasourceManagerClients.updateSQL(id, setCompliance, ImmutableMap.of("level", level, "apiId", apiId, "dsId", id));
		} else if (level != null) {
			datasourceManagerClients.updateSQL(id, overrideCompliance, ImmutableMap.of("level", level, "apiId", apiId, "dsId", id));
		} else {
			datasourceManagerClients.updateSQL(id, resetCompliance, ImmutableMap.of("apiId", apiId, "dsId", id));
		}

		setManaged(id, true);
	}

	@Override
	public void setLastCollectionInfo(final String dsId, final String apiId, final String mdId, final Integer size, final Date date)
		throws DsmException {
		setLastOperationInfo(setLastCollectionInfo, dsId, apiId, mdId, size, date);
	}

	@Override
	public void setLastAggregationInfo(final String dsId, final String apiId, final String mdId, final Integer size, final Date date)
		throws DsmException {
		setLastOperationInfo(setLastAggregationInfo, dsId, apiId, mdId, size, date);
	}

	@Override
	public void setLastDownloadInfo(final String dsId, final String apiId, final String objId, final Integer size, final Date date)
		throws DsmException {
		setLastOperationInfo(setLastDownloadInfo, dsId, apiId, objId, size, date);
	}

	@Override
	public void setLastValidationJob(final String dsId, final String apiId, final String jobId) throws DsmException {
		final Map<String, Object> params = new HashMap<>();
		params.put("dsId", dsId);
		params.put("apiId", apiId);
		params.put("jobId", jobId);

		datasourceManagerClients.updateSQL(dsId, setLastValidationJob, params);
		setManaged(dsId, true);
	}

	@Transactional
	protected void setLastOperationInfo(final Resource sqlResource,
		final String dsId,
		final String apiId,
		final String mdId,
		final Integer size,
		final Date date)
		throws DsmException {
		final Map<String, Object> params = new HashMap<>();
		params.put("dsId", dsId);
		params.put("apiId", apiId);
		params.put("mdId", mdId);
		params.put("total", size);
		if (date != null) {
			params.put("date", new java.sql.Timestamp(date.getTime()));
		}

		datasourceManagerClients.updateSQL(dsId, sqlResource, params);

		setManaged(dsId, true);
	}

	@Override
	@Transactional
	public void updateApiDetails(final String dsId,
		final String apiId,
		final String metadataIdentifierPath,
		final String baseUrl,
		final Map<String, String> params)
		throws DsmException {

		final String id = dsId;

		// Delete old params
		datasourceManagerClients.updateSQL(id, "DELETE FROM dsm_apiparams WHERE api = :api", ImmutableMap.of("api", apiId));

		// Insert new params
		for (final Map.Entry<String, String> e : params.entrySet()) {
			final Map<String, Object> sqlParams = ImmutableMap.of("param", e.getKey(), "value", e.getValue(), "api", apiId);
			datasourceManagerClients.updateSQL(id, insertApiParam, sqlParams);
		}

		// Update the BaseURL
		datasourceManagerClients
			.updateSQL(id, "UPDATE dsm_api SET baseurl = :baseurl WHERE id = :api", ImmutableMap.of("baseurl", baseUrl, "api", apiId));

		// Update the metadata_identifier_path
		datasourceManagerClients.updateSQL(id, "UPDATE dsm_api SET metadata_identifier_path = :path WHERE id = :api", ImmutableMap
			.of("path", metadataIdentifierPath, "api", apiId));

		setManaged(id, true);
	}

	@Override
	public List<? extends BrowsableField> listBrowsableFields() throws DsmException {
		return getBrowsableFields();
	}

	@Override
	public List<BrowseTerm> browseField(final String field) throws DsmException {
		final Optional<DbBrowsableField> bf = getBrowsableFields()
			.stream()
			.filter(f -> f.getId().equals(field))
			.findFirst();

		if (bf.isPresent()) {
			return datasourceManagerClients.searchSQL(bf.get().getSql(), new HashMap<>())
				.stream()
				.filter(m -> m.get("term") != null)
				.filter(m -> m.get("count") != null)
				.filter(m -> StringUtils.isNotBlank(m.get("term").toString()))
				.map(m -> new BrowseTermImpl(m.get("term").toString(), NumberUtils.toInt(m.get("count").toString(), 0)))
				.collect(Collectors.toList());
		} else {
			log.error("Not browsable field:" + field);
			throw new DsmException("Not browsable field:" + field);
		}
	}

	@Override
	@Transactional
	public void addApi(final Api<ApiParam> api) throws DsmException {

		datasourceManagerClients.updateSQL(api.getDatasource(), insertApi, DatasourceFunctions.apiToMap(api));

		if (api.getApiParams() != null) {
			api.getApiParams().forEach(p -> {
				final ImmutableMap<String, Object> sqlParams = ImmutableMap.of("param", p.getParam(), "value", p.getValue(), "api", api.getId());
				try {
					datasourceManagerClients.updateSQL(api.getDatasource(), insertApiParam, sqlParams);
				} catch (final DsmException e) {
					throw new RuntimeException(e);
				}
			});
		}

		setManaged(api.getDatasource(), true);
	}

	@Override
	@Transactional
	public void deleteApi(final String dsId, final String apiId) throws DsmForbiddenException, DsmNotFoundException {
		try {
			if (!isRemovable(dsId, apiId)) { throw new DsmForbiddenException("The api " + apiId + " can't be deleted"); }

			datasourceManagerClients.updateSQL(dsId, "DELETE FROM dsm_apiparams WHERE api = :api", ImmutableMap.of("api", apiId));
			datasourceManagerClients.updateSQL(dsId, "DELETE FROM dsm_api WHERE id = :api", ImmutableMap.of("api", apiId));

			setManaged(dsId, true);
		} catch (final Exception e) {
			throw new DsmForbiddenException("The api " + apiId + " can't be deleted");
		}
	}

	public DatasourceManagerClients getDatasourceManagerClients() {
		return datasourceManagerClients;
	}

	@Required
	public void setDatasourceManagerClients(final DatasourceManagerClients datasourceManagerClients) {
		this.datasourceManagerClients = datasourceManagerClients;
	}

	public List<DbBrowsableField> getBrowsableFields() {
		return browsableFields;
	}

	@Required
	public void setBrowsableFields(final List<DbBrowsableField> browsableFields) {
		this.browsableFields = browsableFields;
	}

	@Override
	@Deprecated
	public void regenerateProfiles() throws DsmException {}

	@Override
	public void updateTickets(final String dsId, final Set<Integer> tickets) throws DsmException {
		datasourceManagerClients.updateSQL(dsId, "DELETE FROM dsm_tickets WHERE service = :service", ImmutableMap.of("service", dsId));

		for (final Integer t : tickets) {
			datasourceManagerClients
				.updateSQL(dsId, "INSERT INTO dsm_tickets (service, ticket) VALUES (:service, :ticket)", ImmutableMap.of("service", dsId, "ticket", t));
		}
	}

	@Override
	public Set<Integer> listTickets(final String dsId) throws DsmException {
		return datasourceManagerClients.searchSQL("select ticket from dsm_tickets where service = :service", ImmutableMap.of("service", dsId))
			.stream()
			.map(r -> r.get("ticket"))
			.filter(n -> n instanceof Integer)
			.map(n -> (Integer) n)
			.collect(Collectors.toSet());
	}

	@Override
	@Transactional
	public void setPrimaryProvideGateway(final String dsId, final String primaryProvideGateway) throws DsmException {
		datasourceManagerClients
			.updateSQL(dsId, "UPDATE dsm_services SET primary_provide_gateway = :gw WHERE id = :id", ImmutableMap.of("id", dsId, "gw", primaryProvideGateway));

		setManaged(dsId, true);
	}

	@Override
	@Transactional
	public void setAffiliatedProvideGateways(final String dsId, final String... primaryProvideGateways) throws DsmException {
		datasourceManagerClients
			.updateSQL(dsId, "UPDATE dsm_services SET affiliated_provide_gateways = string_to_array(:gws, ',') WHERE id = :id", ImmutableMap
				.of("id", dsId, "gws", StringUtils.join(primaryProvideGateways, ",")));

		setManaged(dsId, true);
	}

}