/*

  *   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.io.UnsupportedEncodingException;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.sql.Statement;

 import java.text.SimpleDateFormat;

 import java.util.ArrayList;

 import java.util.Collections;

 import java.util.Date;

 import java.util.List;

 import java.util.Locale;

 import java.util.logging.Level;

 import java.util.logging.Logger;

 import javax.mail.internet.MailDateFormat;

 import javax.mail.internet.MimeUtility;

 import org.apache.commons.codec.net.BCodec;

 import org.apache.commons.codec.net.QuotedPrintableCodec;

 import org.sonews.config.Config;

 import org.sonews.feed.Subscription;

 import org.sonews.storage.Article;

 import org.sonews.storage.ArticleHead;

 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";

 	public static final int MAX_RESTARTS = 2;

 	/** How many times the database connection was reinitialized */

 	protected int restarts = 0;

 	protected Connection conn = null;

 	private QuotedPrintableCodec qpc = new QuotedPrintableCodec(CHARSET);

 	private SimpleDateFormat RFC822_DATE = new SimpleDateFormat("EEE', 'dd' 'MMM' 'yyyy' 'HH:mm:ss' 'Z", Locale.US);

 	// TODO: správná doména

 	private String myDomain = "kinderporno.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);

 			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) {

 			}

 		}

 	}

 	/**

 	 * 

 	 * @param messageID <{0}-{1}-{2}@domain.tld> where {0} is nntp_id and {1} is group_id and {2} is group_name

 	 * @return array where [0] = nntp_id and [1] = group_id and [2] = group_name or returns null if messageID is invalid

 	 */

 	private static String[] parseMessageID(String messageID) {

 		if (messageID.matches("<[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+>")) {

 			return messageID.substring(1).split("@")[0].split("\\-");

 		} else {

 			return null;

 		}

 	}

 	private static Long parseArticleID(String messageID) {

 		String[] localPart = parseMessageID(messageID);

 		if (localPart == null) {

 			return null;

 		} else {

 			return Long.parseLong(localPart[0]);

 		}

 	}

 	private static Long parseGroupID(String messageID) {

 		String[] localPart = parseMessageID(messageID);

 		if (localPart == null) {

 			return null;

 		} else {

 			return Long.parseLong(localPart[1]);

 		}

 	}

 	private static String parseGroupName(String messageID) {

 		String[] localPart = parseMessageID(messageID);

 		if (localPart == null) {

 			return null;

 		} else {

 			return localPart[2];

 		}

 	}

 	private static String constructMessageId(int articleID, int groupID, String groupName, String domainName) {

 		StringBuilder sb = new StringBuilder();

 		sb.append("<");

 		sb.append(articleID);

 		sb.append("-");

 		sb.append(groupID);

 		sb.append("-");

 		sb.append(groupName);

 		sb.append("@");

 		sb.append(domainName);

 		sb.append(">");

 		return sb.toString();

 	}

 	/**

 	 * 

 	 * @param sb header list to be appended with new header. List must be terminated by line end.

 	 * @param key header name (without : and space)

 	 * @param value header value

 	 * @param encode true if value should be encoded/escaped before appending

 	 * @throws UnsupportedEncodingException 

 	 */

 	private static void addHeader(StringBuilder sb, String key, String value, boolean encode) throws UnsupportedEncodingException {

 		sb.append(key);

 		sb.append(": ");

 		if (encode) {

 			sb.append(MimeUtility.encodeWord(value));

 		} else {

 			sb.append(value);

 		}

 		sb.append(CRLF);

 	}

 	private String constructHeaders(ResultSet rs) throws SQLException, UnsupportedEncodingException {

 		StringBuilder sb = new StringBuilder();

 		addHeader(sb, "Message-id", constructMessageId(rs.getInt("id"), rs.getInt("group_id"), rs.getString("group_name"), myDomain), false);

 		addHeader(sb, "From", MimeUtility.encodeWord(rs.getString("sender_name")) + " <>", false);

 		addHeader(sb, "Subject", rs.getString("subject"), true);

 		/** TODO: správný formát data: */

 		addHeader(sb, "Date", RFC822_DATE.format(new Date(rs.getLong("created"))), false);

 		addHeader(sb, "Content-Type", "text/html; charset=" + CHARSET, false);

 		addHeader(sb, "Content-Transfer-Encoding", "quoted-printable", false);

 		//addHeader(sb, "Content-Transfer-Encoding", "base64", false);

 		Integer parentID = rs.getInt("parent_id");

 		if (parentID != null && parentID > 0) {

 			String parentMessageID = constructMessageId(parentID, rs.getInt("group_id"), rs.getString("group_name"), myDomain);

 			addHeader(sb, "In-Reply-To", parentMessageID, false);

 			addHeader(sb, "References", parentMessageID, false);

 		}

 		return sb.toString();

 	}

 	@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()) {

 				String headers = constructHeaders(rs);

 				// TODO: fold?

 				BCodec bc = new BCodec(CHARSET);

 				byte[] body = qpc.encode(rs.getString("text")).getBytes();

 				//byte[] body = bc.encode(rs.getString("text")).getBytes();

 				return new Article(headers, body);

 			} 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 {

 			// TODO: je nutné řazení?

 			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()) {

 				String headers = constructHeaders(rs);

 				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 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();

 	}

 	@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);

 			}

 		}

 	}

 	//

 	// --- zatím neimplementovat ---

 	//

 	@Override

 	public void addArticle(Article art) throws StorageBackendException {

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

 	}

 	@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 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 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 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 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<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 void setConfigValue(String key, String value) throws StorageBackendException {

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

 	}

 	@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.");

 	}

 }