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

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.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.util.SortedSet;
import java.util.TreeSet;

import org.apache.commons.lang.StringEscapeUtils;
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 publishing alerts as Joomla! articles by connecting to a Joomla! database via JDBC.
 * The subscribers served by this alerter must have a URI that is a valid JDBC URI (including user and password) with the following extra parameters, which must be URL encoded in UTF-8:
 * <dl>
 * 	<dt>tablePrefix</dt>
 * 	<dd>the Joomla! table prefix to use</dd>
 * 	<dt>section</dt>
 * 	<dd>the Joomla! section ID to use</dd>
 * 	<dt>category</dt>
 * 	<dd>the Joomla! category ID to use</dd>
 * 	<dt>userId</dt>
 * 	<dd>the Joomla! user ID to use</dd>
 * </dl>
 * @author thanos@di.uoa.gr
 * @see Alerter
 * @see eu.dnetlib.domain.functionality.AlertTopic
 * @see eu.dnetlib.domain.functionality.AlertSubscription
 * @see AlerterException
 * 
 */
public class JoomlaAlerter extends Alerter {
	private static final Logger logger = Logger.getLogger(JoomlaAlerter.class);
	private static final String JOOMLA = "Joomla!";
	private static final String JDBC = "jdbc";
	private static final SortedSet<String> SUPPORTED_ALERT_MODES = new TreeSet<String>();
	private static final SortedSet<String> SUPPORTED_URI_SCHEMES = new TreeSet<String>();
	
	static {
		SUPPORTED_ALERT_MODES.add(JOOMLA);
		SUPPORTED_URI_SCHEMES.add(JDBC);
	}
	
	/**
	 * Load the JDBC drivers to use.
	 * @param jdbcDrivers a string array 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("Joomla! 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().getRawSchemeSpecificPart()).getRawQuery().split("&")) {
				String[] nameValue = parameter.split("=");
				parameters.put(URLDecoder.decode(nameValue[0], "UTF-8"), (nameValue.length > 1) ? URLDecoder.decode(nameValue[1], "UTF-8") : null);
			}
			connection = DriverManager.getConnection(subscription.getSubscriber().toString());
			logger.info("Connected to " + subscription.getSubscriber());
			final String tablePrefix = parameters.getProperty("tablePrefix");
			final int section = Integer.parseInt(parameters.getProperty("section"));
			final int category = Integer.parseInt(parameters.getProperty("category"));
			final int userId = Integer.parseInt(parameters.getProperty("userId"));
			final Date now = new Date(System.currentTimeMillis());
			final String quote = connection.getMetaData().getIdentifierQuoteString();
			if (quote.equals(" "))
				throw new AlerterException("error alerting subscription " + subscription + ": can not escape SQL identifiers");			
			final StringBuilder query = new StringBuilder("INSERT INTO ");
			query.append(quote).append(tablePrefix).append("content").append(quote).append(" (").append(quote).append("title").append(quote).append(", ").append(quote).append("alias").append(quote).append(", ").append(quote);
			query.append("title_alias").append(quote).append(", ").append(quote).append("introtext").append(quote).append(", ").append(quote).append("fulltext").append(quote).append(", ").append(quote).append("state");
			query.append(quote).append(", ").append(quote).append("sectionid").append(quote).append(", ").append(quote).append("catid").append(quote).append(", ").append(quote).append("created").append(quote).append(", ");
			query.append(quote).append("created_by").append(quote).append(", ").append(quote).append("modified").append(quote).append(", ").append(quote).append("modified_by").append(quote).append(", ").append(quote);
			query.append("publish_up").append(quote).append(", ").append(quote).append("images").append(quote).append(", ").append(quote).append("urls").append(quote).append(", ").append(quote).append("attribs").append(quote);
			query.append(", ").append(quote).append("metakey").append(quote).append(", ").append(quote).append("metadesc").append(quote).append(", ").append(quote).append("metadata").append(quote);
			query.append(") VALUES (?, ?, ?, ?, '', 1, ?, ?, ?, ?, ?, ?, ?, '', '', '', '', '', '');");
			final PreparedStatement statement = connection.prepareStatement(query.toString());
			statement.setString(1, title); // title
			statement.setString(2, title.toLowerCase().replaceAll("\\s+", "-")); // alias
			statement.setString(3, title.toLowerCase().replaceAll("\\s+", "-")); // title_alias
			final StringBuilder introtext = new StringBuilder();
			if (link != null)
				introtext.append("<a href=\"").append(StringEscapeUtils.escapeXml(link.toString())).append("\">");
			introtext.append(message);
			if (link != null)
				introtext.append("</a>");
			statement.setString(4, introtext.toString()); // introtext
			statement.setInt(5, section); // sectionid
			statement.setInt(6, category); // catid
			statement.setDate(7, now); // created
			statement.setInt(8, userId); // created_by
			statement.setDate(9, now);	// modified
			statement.setInt(10, userId); // modified_by
			statement.setDate(11, now); // publish_up
			statement.executeUpdate();
			logger.info("Posted Joomla! article to " + subscription.getSubscriber());
		} catch (final SQLException e) {
			throw new AlerterException("error posting Joomla! article to " + subscription.getSubscriber(), e);
		} catch (final UnsupportedEncodingException e) {
			throw new AlerterException("error posting Joomla! article to " + subscription.getSubscriber(), e);
		} catch (final URISyntaxException e) {
			throw new AlerterException("error posting Joomla! article 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(), e);
				}
			}
		}
	}
	
	@Override
	protected SortedSet<String> getSupportedUriSchemes() {
		return SUPPORTED_URI_SCHEMES;
	}
}