package eu.dnetlib.functionality.alert.alerter.jdbc;

import java.io.UnsupportedEncodingException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.SortedSet;
import java.util.TreeSet;

import org.apache.log4j.Logger;

import eu.dnetlib.domain.functionality.AlertSubscription;
import eu.dnetlib.functionality.alert.alerter.Alerter;
import eu.dnetlib.functionality.alert.alerter.AlerterException;

/**
 * This class implements an alerter that is capable of storing alerts in a JDBC database.
 * The subscribers served by this alerter must have a URI that is a valid JDBC URI (including user and password), but which may also contain arbitrary extra parameters.
 * All parameters should be URL encoded in UTF-8.
 * Among these parameters there should be at least one with name "table" and value the name of the table to insert data into.
 * For each parameter specified, it is checked whether a column with the same name exists in that table, and if yes, the corresponding parameter value is inserted as a string.
 * Moreover the value of a parameter may be one of the following special vars, in which case it is replaced accordingly:
 * <dl>
 * 	<dt>$time</dt>
 * 	<dd>the time the alert was received</dd>
 * 	<dt>$title</dt>
 * 	<dd>the title of the alert</dd>
 * 	<dt>$message</dt>
 * 	<dd>the message of the alert</dd>
 * 	<dt>$link</dt>
 * 	<dd>the link of the alert</dd>
 * </dl>
 * Thus the JDBC URI "jdbc:mysql://host.domain.net:3306/database?user=user&password=password&table=table&username=thanos&time=$time&title=$title&message=$message&link=$link" will result in connecting to a MySQL database
 * on host.domain.net on port 3306 and executing the SQL query "INSERT INTO table (username, time, title, message, link) VALUES ('thanos', ?, ?, ?, ?);" after binding the corresponding values.
 * @author thanos@di.uoa.gr
 * @see Alerter
 * @see eu.dnetlib.domain.functionality.AlertTopic
 * @see eu.dnetlib.domain.functionality.AlertSubscription
 * @see AlerterException
 * 
 */
public class JDBCAlerter extends Alerter {
	private static final Logger logger = Logger.getLogger(JDBCAlerter.class);
	private static final String JDBC_ALERT_MODE = "JDBC";
	private static final String JDBC_URI_SCHEME = "jdbc";
	private static final SortedSet<String> SUPPORTED_ALERT_MODES = new TreeSet<String>();
	private static final SortedSet<String> SUPPORTED_URI_SCHEMES = new TreeSet<String>();
	private static final String TIME_VAR = "$time";
	private static final String TITLE_VAR = "$title";
	private static final String MESSAGE_VAR = "$message";
	private static final String LINK_VAR = "$link";
	
	static {
		SUPPORTED_ALERT_MODES.add(JDBC_ALERT_MODE);
		SUPPORTED_URI_SCHEMES.add(JDBC_URI_SCHEME);
	}

	/**
	 * Load the JDBC drivers to use.
	 * @param jdbcDrivers an array of strings containing the class names of the JDBC drivers to load
	 * @throws ClassNotFoundException if a JDBC driver class is not found
	 * @throws IllegalAccessException if the constructor of a JDBC driver is not accessible
	 * @throws InstantiationException if a JDBC driver can not be instantiated
	 */
	public void setJdbcDrivers(final String[] jdbcDrivers) throws ClassNotFoundException, IllegalAccessException, InstantiationException {
		for (String jdbcDriver : jdbcDrivers) {
			Class.forName(jdbcDriver.trim()).newInstance();
			logger.info("Loaded JDBC driver " + jdbcDriver.trim());
		}
	}

	@Override
	public void init() {
		logger.info("JDBC alerter initialization complete");
	}
	
	@Override
	public SortedSet<String> getSupportedAlertModes() {
		return SUPPORTED_ALERT_MODES;
	}

	@Override
	public void alert(final AlertSubscription subscription, final String title, final String message, final URL link) throws AlerterException {
		validateSubscription(subscription);
		Connection connection = null;
		try {
			final Properties parameters = new Properties();
			for (String parameter : new URI(subscription.getSubscriber().getSchemeSpecificPart()).getRawQuery().split("&")) {
				String[] nameValue = parameter.split("=");
				parameters.setProperty(URLDecoder.decode(nameValue[0], "UTF-8"), (nameValue.length > 1) ? URLDecoder.decode(nameValue[1], "UTF-8") : null);
			}
			if (parameters.getProperty("table") == null)
				throw new AlerterException("error alerting subscription " + subscription + ": no table specified");
			connection = DriverManager.getConnection(subscription.getSubscriber().toString());
			logger.info("Connected to " + subscription.getSubscriber());
			final String quote = connection.getMetaData().getIdentifierQuoteString();
			if (quote.equals(" "))
				throw new AlerterException("error alerting subscription " + subscription + ": can not escape SQL identifiers");			
			final List<String> columns = new ArrayList<String>();
			for (String name : parameters.stringPropertyNames()) {
				final ResultSet resultSet = connection.getMetaData().getColumns(connection.getCatalog(), null, parameters.getProperty("table"), name);
				if (resultSet.next())
					columns.add(name);
			}
			if (columns.size() == 0) // no columns matched
				throw new AlerterException("error alerting subscrption " + subscription + ": no columns matched");
			final StringBuilder query = new StringBuilder("INSERT INTO ");
			query.append(quote).append(parameters.getProperty("table")).append(quote).append(" (");
			for (String column : columns)
				query.append(quote).append(column).append(quote).append(", ");
			query.setLength(query.length() - ", ".length()); // ommit last ", "
			query.append(") VALUES (");
			for (String column : columns) {
				final String value = parameters.getProperty(column);
				if (value.equals(TIME_VAR) || value.equals(TITLE_VAR) || value.equals(MESSAGE_VAR) || value.equals(LINK_VAR))
					query.append("?, ");
				else
					query.append("'").append(value).append("', ");
			}
			query.setLength(query.length() - ", ".length()); // ommit last ", "
			query.append(");");
			final PreparedStatement statement = connection.prepareStatement(query.toString());
			int i = 1;
			for (String column : columns) {
				final String value = parameters.getProperty(column);
				if (value.equals(TIME_VAR)) {
					statement.setTimestamp(i, new Timestamp(new Date().getTime()));
					i++;
				} else if (value.equals(TITLE_VAR)) {
					statement.setString(i, title);
					i++;
				} else if (value.equals(MESSAGE_VAR)) {
					statement.setString(i, message);
					i++;
				} else if (value.equals(LINK_VAR)) {
					if (link == null)
						statement.setNull(i, Types.VARCHAR);
					else
						statement.setString(i, link.toString());
					i++;
				}
			}
			statement.executeUpdate();
			logger.info("Inserted alert to " + subscription.getSubscriber());
		} catch (final SQLException e) {
			throw new AlerterException("error inserting alert to " + subscription.getSubscriber(), e);
		} catch (final UnsupportedEncodingException e) {
			throw new AlerterException("error inserting alert to " + subscription.getSubscriber(), e);
		} catch (final URISyntaxException e) {
			throw new AlerterException("error inserting alert to " + subscription.getSubscriber(), e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
					logger.info("Closed connection to " + subscription.getSubscriber());
				} catch (final SQLException e) {
					logger.warn("Error closing connection to " + subscription.getSubscriber());
				}
			}
		}
	}
	
	@Override
	protected SortedSet<String> getSupportedUriSchemes() {
		return SUPPORTED_URI_SCHEMES;
	}
}