/*

 *   SONEWS News Server

 *   see AUTHORS for the list of contributors

 *

 *   This program is free software: you can redistribute it and/or modify

 *   it under the terms of the GNU General Public License as published by

 *   the Free Software Foundation, either version 3 of the License, or

 *   (at your option) any later version.

 *

 *   This program is distributed in the hope that it will be useful,

 *   but WITHOUT ANY WARRANTY; without even the implied warranty of

 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 *   GNU General Public License for more details.

 *

 *   You should have received a copy of the GNU General Public License

 *   along with this program.  If not, see <http://www.gnu.org/licenses/>.

 */

package org.sonews.storage.impl;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.Collections;

import java.util.List;

import java.util.logging.Level;

import java.util.logging.Logger;

import org.sonews.config.Config;

import org.sonews.feed.Subscription;

import org.sonews.storage.Article;

import org.sonews.storage.ArticleHead;

import org.sonews.storage.DrupalArticle;

import org.sonews.storage.DrupalMessage;

import static org.sonews.storage.DrupalMessage.parseArticleID;

import static org.sonews.storage.DrupalMessage.parseGroupID;

import org.sonews.storage.Group;

import org.sonews.storage.Storage;

import org.sonews.storage.StorageBackendException;

import org.sonews.util.Pair;

/**

 *

 * @author František Kučera (frantovo.cz)

 */

public class DrupalDatabase implements Storage {

	private static final Logger log = Logger.getLogger(DrupalDatabase.class.getName());

	public static final String CHARSET = "UTF-8";

	public static final String CRLF = "\r\n";

	protected Connection conn = null;

	// TODO: správná doména

	private String myDomain = "nntp.i1984.cz";

	public DrupalDatabase() throws StorageBackendException {

		connectDatabase();

	}

	private void connectDatabase() throws StorageBackendException {

		try {

			// Load database driver

			String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");

			Class.forName(driverClass);

			// Establish database connection

			String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>");

			String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");

			String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");

			conn = DriverManager.getConnection(url, username, password);

			/**

			 * Kódování češtiny:

			 * SET NAMES utf8 → dobrá čeština

			 *		Client characterset:    utf8

			 *		Conn.  characterset:    utf8

			 * SET CHARACTER SET utf8; → dobrá čeština jen pro SLECT, ale při volání funkce se zmrší

			 *		Client characterset:    utf8

			 *		Conn.  characterset:    latin1

			 * 

			 * Správné řešení:

			 *		V JDBC URL musí být: ?useUnicode=true&characterEncoding=UTF-8

			 */

			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

			if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {

				log.warning("Database is NOT fully serializable!");

			}

		} catch (Exception e) {

			throw new StorageBackendException(e);

		}

	}

	protected static void close(Connection connection, Statement statement, ResultSet resultSet) {

		if (resultSet != null) {

			try {

				resultSet.close();

			} catch (Exception e) {

			}

		}

		if (statement != null) {

			try {

				statement.close();

			} catch (Exception e) {

			}

		}

		if (connection != null) {

			try {

				connection.close();

			} catch (Exception e) {

			}

		}

	}

	@Override

	public List<Group> getGroups() throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT * FROM nntp_group");

			rs = ps.executeQuery();

			List<Group> skupiny = new ArrayList<Group>();

			while (rs.next()) {

				skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));

			}

			return skupiny;

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public Group getGroup(String name) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");

			ps.setString(1, name);

			rs = ps.executeQuery();

			while (rs.next()) {

				return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);

			}

			return null;

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public boolean isGroupExisting(String groupname) throws StorageBackendException {

		return getGroup(groupname) != null;

	}

	@Override

	public Article getArticle(String messageID) throws StorageBackendException {

		Long articleID = parseArticleID(messageID);

		Long groupID = parseGroupID(messageID);

		if (articleID == null || groupID == null) {

			log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});

			return null;

		} else {

			return getArticle(articleID, groupID);

		}

	}

	@Override

	public Article getArticle(long articleID, long groupID) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");

			ps.setLong(1, articleID);

			ps.setLong(2, groupID);

			rs = ps.executeQuery();

			if (rs.next()) {

				DrupalMessage m = new DrupalMessage(rs, myDomain, true);

				return new DrupalArticle(m);

			} else {

				return null;

			}

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ? ORDER BY id");

			ps.setLong(1, group.getInternalID());

			ps.setLong(2, first);

			ps.setLong(3, last);

			rs = ps.executeQuery();

			List<Pair<Long, ArticleHead>> heads = new ArrayList<Pair<Long, ArticleHead>>();

			while (rs.next()) {

				DrupalMessage m = new DrupalMessage(rs, myDomain, false);

				String headers = m.getHeaders();

				heads.add(new Pair<Long, ArticleHead>(rs.getLong("id"), new ArticleHead(headers)));

			}

			return heads;

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public long getArticleIndex(Article article, Group group) throws StorageBackendException {

		Long id = parseArticleID(article.getMessageID());

		if (id == null) {

			throw new StorageBackendException("Invalid messageID: " + article.getMessageID());

		} else {

			return id;

		}

	}

	@Override

	public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");

			ps.setLong(1, groupID);

			rs = ps.executeQuery();

			List<Long> articleNumbers = new ArrayList<Long>();

			while (rs.next()) {

				articleNumbers.add(rs.getLong(1));

			}

			return articleNumbers;

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public int getFirstArticleNumber(Group group) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");

			ps.setLong(1, group.getInternalID());

			rs = ps.executeQuery();

			rs.next();

			return rs.getInt(1);

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public int getLastArticleNumber(Group group) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");

			ps.setLong(1, group.getInternalID());

			rs = ps.executeQuery();

			rs.next();

			return rs.getInt(1);

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public boolean isArticleExisting(String messageID) throws StorageBackendException {

		Long articleID = parseArticleID(messageID);

		Long groupID = parseGroupID(messageID);

		if (articleID == null || groupID == null) {

			return false;

		} else {

			PreparedStatement ps = null;

			ResultSet rs = null;

			try {

				ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");

				ps.setLong(1, articleID);

				ps.setLong(2, groupID);

				rs = ps.executeQuery();

				rs.next();

				return rs.getInt(1) == 1;

			} catch (Exception e) {

				throw new StorageBackendException(e);

			} finally {

				close(null, ps, rs);

			}

		}

	}

	@Override

	public int countArticles() throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article");

			rs = ps.executeQuery();

			rs.next();

			return rs.getInt(1);

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public int countGroups() throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT count(*) FROM nntp_group");

			rs = ps.executeQuery();

			rs.next();

			return rs.getInt(1);

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public int getPostingsCount(String groupname) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE group_name = ?");

			ps.setString(1, groupname);

			rs = ps.executeQuery();

			rs.next();

			return rs.getInt(1);

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public List<Pair<Long, String>> getArticleHeaders(Group group, long start, long end, String header, String pattern) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getArticleHeaders {0} / {1} / {2} / {3} / {4}", new Object[]{group, start, end, header, pattern});

		/** TODO: */

		return Collections.emptyList();

	}

	/**

	 * Checks username and password.

	 * @param username

	 * @param password

	 * @return true if credentials are valid | false otherwise

	 * @throws StorageBackendException it there is any error during authentication process 

	 * (but should not be thrown if only bad thing is wrong username or password)

	 */

	@Override

	public boolean authenticateUser(String username,

			char[] password) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT nntp_login(?, ?)");

			ps.setString(1, username);

			ps.setString(2, String.copyValueOf(password));

			rs = ps.executeQuery();

			rs.next();

			return rs.getInt(1) == 1;

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	/**

	 * Validates article and if OK, calls {@link #insertArticle(java.lang.String, java.lang.String, java.lang.String, java.lang.Long, java.lang.Long) }

	 * @param article

	 * @throws StorageBackendException 

	 */

	@Override

	public void addArticle(Article article) throws StorageBackendException {

		if (article.getAuthenticatedUser() == null) {

			log.log(Level.SEVERE, "User was not authenticated, so his article was rejected.");

			throw new StorageBackendException("User must be authenticated to post articles");

		} else {

			try {

				DrupalMessage m = new DrupalMessage(article);

				Long parentID = m.getParentID();

				Long groupID = m.getGroupID();

				if (parentID == null || groupID == null) {

					throw new StorageBackendException("No valid In-Reply-To header was found → rejecting posted message.");

				} else {

					String subject = m.getSubject();

					String text = m.getBodyXhtmlFragment();

					if (subject == null || subject.length() < 1) {

						String plainText = m.getBodyPlainText();

						subject = plainText.substring(0, Math.min(32, plainText.length()));

						if (subject.length() < plainText.length()) {

							subject = subject + "…";

						}

					}

					insertArticle(article.getAuthenticatedUser(), subject, text, parentID, groupID);

					log.log(Level.INFO, "User ''{0}'' has posted an article", article.getAuthenticatedUser());

				}

			} catch (Exception e) {

				throw new StorageBackendException(e);

			}

		}

	}

	/**

	 * Physically stores article in database.

	 * @param subject

	 * @param text

	 * @param parentID

	 * @param groupID 

	 */

	private void insertArticle(String sender, String subject, String text, Long parentID, Long groupID) throws StorageBackendException {

		PreparedStatement ps = null;

		ResultSet rs = null;

		try {

			ps = conn.prepareStatement("SELECT nntp_post_article(?, ?, ?, ?, ?)");

			ps.setString(1, sender);

			ps.setString(2, subject);

			ps.setString(3, text);

			ps.setLong(4, parentID);

			ps.setLong(5, groupID);

			rs = ps.executeQuery();

			rs.next();

			Long articleID = rs.getLong(1);

			log.log(Level.INFO, "Article was succesfully stored as {0}", articleID);

		} catch (Exception e) {

			throw new StorageBackendException(e);

		} finally {

			close(null, ps, rs);

		}

	}

	@Override

	public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: addEvent {0} / {1} / {2}", new Object[]{timestamp, type, groupID});

	}

	@Override

	public void addGroup(String groupname, int flags) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: addGroup {0} / {1}", new Object[]{groupname, flags});

	}

	@Override

	public void delete(String messageID) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: delete {0}", new Object[]{messageID});

	}

	@Override

	public String getConfigValue(String key) throws StorageBackendException {

		//log.log(Level.SEVERE, "TODO: getConfigValue {0}", new Object[]{key});

		return null;

	}

	@Override

	public void setConfigValue(String key, String value) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: setConfigValue {0} = {1}", new Object[]{key, value});

	}

	@Override

	public int getEventsCount(int eventType, long startTimestamp, long endTimestamp, Group group) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getEventsCount {0} / {1} / {2} / {3}", new Object[]{eventType, startTimestamp, endTimestamp, group});

		return 0;

	}

	@Override

	public double getEventsPerHour(int key, long gid) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getEventsPerHour {0} / {1}", new Object[]{key, gid});

		return 0;

	}

	@Override

	public List<String> getGroupsForList(String listAddress) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getGroupsForList {0}", new Object[]{listAddress});

		return Collections.emptyList();

	}

	@Override

	public List<String> getListsForGroup(String groupname) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getListsForGroup {0}", new Object[]{groupname});

		return Collections.emptyList();

	}

	@Override

	public String getOldestArticle() throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getOldestArticle");

		return null;

	}

	@Override

	public List<Subscription> getSubscriptions(int type) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: getSubscriptions {0}", new Object[]{type});

		return Collections.emptyList();

	}

	@Override

	public void purgeGroup(Group group) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: purgeGroup {0}", new Object[]{group});

	}

	@Override

	public boolean update(Article article) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{article});

		throw new StorageBackendException("Not implemented yet.");

	}

	@Override

	public boolean update(Group group) throws StorageBackendException {

		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{group});

		throw new StorageBackendException("Not implemented yet.");

	}

}