package eu.dnetlib.data.statsmanager;


import eu.dnetlib.clients.data.search.ws.SearchWebService;
import eu.dnetlib.common.rmi.UnimplementedException;
import eu.dnetlib.domain.data.SearchResult;
import org.apache.cxf.jaxws.JaxWsProxyFactoryBean;
import org.apache.log4j.Logger;
import org.springframework.core.io.Resource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;


public class Validator {

	private ValidationReport validationReport;
	private Resource validationQueriesFile;
	private DataSource dataSource;
	private String historySchema;
	private String shadowSearchURL;
	private static SearchWebService searchWebService = null;
	private Logger log = Logger.getLogger(this.getClass());

	public Validator() {
	}

	private void initSearch() {
		if (searchWebService == null) {
			JaxWsProxyFactoryBean factory = new JaxWsProxyFactoryBean();
			factory.setServiceClass(SearchWebService.class);
			factory.setAddress(shadowSearchURL);
			searchWebService = (SearchWebService) factory.create();
		}
	}

	public ValidationReport validateDatabase() throws Exception {
		ValidationReport report = new ValidationReport();
		Properties p = new Properties();

		this.validationReport = report;

		try { //TODO initiate search
			initSearch();
			   log.debug("Search Client  initiated at " + this.shadowSearchURL);
		} catch (Exception e) {
			log.error(e.toString());
			throw new Exception(e);

		}

		try {

			p.loadFromXML(validationQueriesFile.getInputStream());

		} catch (Exception e) {
			log.error("Error while loading queries file :" + e.toString());
			throw new Exception(e);

		}


		int queryCount = Integer.parseInt(p.getProperty("queries.count"));
		 log.info("Query  Count: " + queryCount + "\n");

		for (int i = 1; i <= queryCount; i++) {
			String schema = p.getProperty("queries.schema");

			String queryType = p.getProperty("queries." + i + ".type");
			String sql = p.getProperty("queries." + i + ".sql").replaceAll("schema", schema);
			String cql = p.getProperty("queries." + i + ".cql.query");
			String name = p.getProperty("queries." + i + ".name");
			String id = p.getProperty("queries." + i + ".id");

		    log.debug("Query: id  " + id + "  " + name + "\n" + " SQL " + sql + "\n" + "CQL" + cql + "\n");
			if (queryType.equals("search")) {


				int sqlResult = executeSQLCountQuery(sql);
				int cqlResult = executeCQLCountQuery(cql);

				boolean status = validate(sqlResult, cqlResult);
				saveResult(id, name, sql, String.valueOf(sqlResult), validationReport.getValidationDate().toString());
				validationReport.addQuery(id, name, sqlResult, cqlResult, status);


			} else if (queryType.equals("browse")) {

				//   Map<String, Integer> sqlResults = executeSQLBrowseQuery(sql);
				//  Map<String, Integer> cqlResults = executeCQLBrowseQuery(cql, p.getProperty("queries." + i + ".cql.groupBy"));

				// TODO compare and update report
			}
		}

		log.info("Generated report : " + report.toString());
		return report;
	}

	//@Transactional
	public void saveResult(String id, String desc, String qString, String result, String date) throws Exception {
		Connection con = dataSource.getConnection();

		log.info("Writing report to DB " + con.getMetaData().getURL() + " and historySchema " + this.historySchema + "...");

		String q = "UPDATE  " + this.historySchema + ".query SET query_id=?,query=?, description=? WHERE query_id=? ;" +
				"INSERT INTO " + this.historySchema + ".query (query_id, query, description) SELECT ? ,?,?  WHERE NOT EXISTS" +
				"(SELECT 1 FROM " + this.historySchema + ".query WHERE query_id=?)";

		// log.info("Executing query update " + q);

		PreparedStatement stm = con.prepareStatement(q);


		stm.setString(1, id);
		stm.setString(2, qString);
		stm.setString(3, desc);
		stm.setString(4, id);
		stm.setString(5, id);
		stm.setString(6, qString);
		stm.setString(7, desc);
		stm.setString(8, id);

		stm.executeUpdate();

		// q = "INSERT INTO value(query_id, num, date) values ('" + id + "','" + result + "','" + date + "');";

		q = "INSERT INTO " + this.historySchema + ".value(query_id, num, date) values (?,?,?);";
		stm.clearParameters();


		stm = con.prepareStatement(q);
		stm.setString(1, id);
		stm.setString(2, result);
		stm.setString(3, date);

		//log.info("Executing query update " + q);
		stm.executeUpdate();

		stm.close();
		con.close();
	}


	private boolean validate(int sqlResult, int cqlResult) {

		  log.debug(" Validation input  ->  sql : " + sqlResult + "  cql : " + cqlResult);
		if (sqlResult == cqlResult) {

			return true;
		}

		return false;
	}

	private Map<String, Integer> executeCQLBrowseQuery(String cql, String groupBy) {
		// TODO implement me please

		//oaftype=result and deletedbyinferece=false and type=publication , groupby = access_mode
		// searchServiceServiceLocator.getService().refine();

		//public SearchResult refine(String queryText, String transformer,
		//String locale, Collection<String> fields) throws SearchServiceException;

		//    public SearchResult refine(String queryText, String transformer,
		//          String locale, Collection<String> fields) throws SearchServiceException;
		//[2:52:39 PM] Antonis Lempesis:


		//query=   oaftype=result and deletedbyinferece=false and type=publication

		//transformer = results_openaire

//        locale=UTF-8

		//      fields = access_mode


//        set to 1 page and results
		//       public SearchResult search(String queryText, String transformer,
		//             String locale, int page, int size) throws SearchServiceException;

		throw new UnimplementedException();
	}

	private Map<String, Integer> executeSQLBrowseQuery(String sql) {
		// TODO implement me please
		throw new UnimplementedException();
	}

	private int executeCQLCountQuery(String cql) throws Exception {
		try {
			if (cql == null || cql.isEmpty() || cql.equalsIgnoreCase("not available")) {
				return 0;
			}
			SearchResult result = searchWebService.search(cql, "results_openaire", "en_GB", 1, 1);

			return result.getTotal();


		} catch (Exception e) {
			log.error("Could not execute CQL query. Reason: " + e);
			throw new Exception("Could not execute CQL query. Reason: ", e);
		}
	}


	private int executeSQLCountQuery(String sql) throws Exception {

		Connection con = null;
		try {

			con = dataSource.getConnection();


			Statement st = con.createStatement();
			if (st.execute(sql)) {

				ResultSet rs = st.getResultSet();
				int res = getResult(rs);
				st.close();
				return res;
			} else {
				log.error("Fail to execute command  " + sql + "  " + st.getWarnings());
				throw new Exception("Fail to execute command " + sql + "  " + st.getWarnings());
			}


		} catch (Exception e) {
			log.error("Could not execute sql query  " + sql + " : " + e);
			throw new Exception("Could not execute sql query  " + sql + " : ", e);
		} finally {
			if (con != null) {
				con.close();


			}
		}

	}

	private int getResult(ResultSet rs) throws Exception {
		HashMap<String, Integer> data = new HashMap<String, Integer>();
		int res = -1;
		try {

			ResultSetMetaData rsmd = rs.getMetaData();

			if (rs.next()) {

				res = (Integer.valueOf(rs.getString(1)));
				rs.close();
			}

			return res;
		} catch (Exception e) {
			log.error("Could not process results :" + e);
			throw new Exception("Could not process results :", e);
		}

	}

	private HashMap<String, Integer> getResults(ResultSet rs) throws Exception {
		HashMap<String, Integer> data = new HashMap<String, Integer>();
		try {

			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				for (int i = 1; i < rsmd.getColumnCount() - 1; i++) {
					data.put(rsmd.getColumnName(i), rs.getInt(i));
				}

			}

			return data;

		} catch (Exception e) {
			log.error("Could not process results :" + e);
			throw new Exception("Could not process results :", e);
		}

	}


	public ValidationReport getValidationReport() {
		return validationReport;
	}

	public void setValidationReport(ValidationReport validationReport) {
		this.validationReport = validationReport;
	}

	public Resource getValidationQueriesFile() {
		return validationQueriesFile;
	}

	public void setValidationQueriesFile(Resource validationQueriesFile) {
		this.validationQueriesFile = validationQueriesFile;
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public String getShadowSearchURL() {
		return shadowSearchURL;
	}

	public void setShadowSearchURL(String shadowSearchURL) {
		this.shadowSearchURL = shadowSearchURL;
	}

	public static SearchWebService getSearchWebService() {
		return searchWebService;
	}

	public static void setSearchWebService(SearchWebService searchWebService) {
		Validator.searchWebService = searchWebService;
	}

	public String getHistorySchema() {
		return historySchema;
	}

	public void setHistorySchema(String historySchema) {
		this.historySchema = historySchema;
	}


}