package eu.stork.documentservice.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import eu.stork.documentservice.exceptions.DatabaseException;
import eu.stork.documentservice.exceptions.ModelException;
import eu.stork.documentservice.model.DocumentModel;
import eu.stork.documentservice.model.RequestModel;
import eu.stork.documentservice.model.TempDocumentModel;

public class DatabaseConnectorMySQLImpl implements DatabaseConnector {
	
	private String user;
	private String password;
	private String server;
	private String database;
	
	/**
	 * The MYSQL connector constructor
	 * @author sveinbjorno
	 * @param _user the database user
	 * @param _password the users password
	 * @param _server the mysql server
	 * @param _database the mysql database
	 */
	public DatabaseConnectorMySQLImpl(String _user, String _password, String _server, String _database)
	{
		user = _user;
		password = _password;
		server = _server;
		database = _database;
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#addDocument(eu.stork.documentservice.model.DocumentModel)
	 */
	@Override
	public boolean addDocument(DocumentModel document) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{	
			document.insertValidate();
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);			
			
			pst = con.prepareStatement("INSERT INTO document(DOCID, DOCUMENT, FILENAME, MIMETYPE, CREATED, RECEIVERCERT, "
					+ "ENCKEY, ENCIV) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            pst.setString(1, document.getDocid());
            pst.setBlob(2, document.getDataStream());
            pst.setString(3, document.getFilename());
            pst.setString(4, document.getMimetype());
			pst.setTimestamp(5, DatabaseHelper.getSqlCurrentDate());
			pst.setString(6, document.getReicevercert());
			pst.setString(7, document.getEnckey());
			pst.setString(8, document.getEnciv());
            pst.executeUpdate();
            successful = true;
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return successful;
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#updateDocument(eu.stork.documentservice.model.DocumentModel)
	 */
	@Override
	public boolean updateDocument(DocumentModel document) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{	
			document.updateValidate();
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);			
			
			pst = con.prepareStatement("update document set DOCUMENT = ?, FILENAME = ?, MIMETYPE = ?, UPDATED = ?, "
					+ "RECEIVERCERT = ?, ENCKEY = ?, ENCIV = ? where DOCID = ?");            
            pst.setBlob(1, document.getDataStream());
            pst.setString(2, document.getFilename());
            pst.setString(3, document.getMimetype());
			pst.setTimestamp(4, DatabaseHelper.getSqlCurrentDate());
			pst.setString(5, document.getReicevercert());
			pst.setString(6, document.getEnckey());
			pst.setString(7, document.getEnciv());
			pst.setString(8, document.getDocid());
            pst.executeUpdate();
            successful = true;
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return successful;
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#getDocument(java.lang.String)
	 */
	@Override
	public DocumentModel getDocument(String docId) throws DatabaseException
	{
		DocumentModel document = null;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			if (docId == null || docId.isEmpty())
				throw new DatabaseException("Document ID is null or empty.");
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);
			String commandString = "select DOCUMENT, FILENAME, MIMETYPE, CREATED, UPDATED, DELETED, RECEIVERCERT, ENCKEY, ENCIV "
				+ "from document where DOCID = ?";
							
			pst = con.prepareStatement(commandString);
			pst.setString(1, docId);
            
			ResultSet set = pst.executeQuery();
            if (set.next())
            {
            	document = new DocumentModel();
            	document.setDocid(docId);
            	document.setDataStream(set.getBinaryStream(1));
            	document.setFilename(set.getString(2));
            	document.setMimetype(set.getString(3));
            	document.setCreated(DatabaseHelper.getUtilDate(set.getTimestamp(4)));
            	document.setUpdated(DatabaseHelper.getUtilDate(set.getTimestamp(5)));
            	document.setDeleted(DatabaseHelper.getUtilDate(set.getTimestamp(6)));
            	document.setReicevercert(set.getString(7));
            	document.setEnckey(set.getString(8));
            	document.setEnciv(set.getString(9));
            }
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		
		return document;		
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#deleteDocument(java.lang.String)
	 */
	@Override
	public boolean deleteDocument(String docId) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			if (docId == null || docId.isEmpty())
				throw new DatabaseException("Document ID is null or empty.");
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);
			String commandString = "update document set document = null, deleted = ? where DOCID = ?";
							
			pst = con.prepareStatement(commandString);
			pst.setTimestamp(1, DatabaseHelper.getSqlCurrentDate());
			pst.setString(2, docId);
            
			pst.executeUpdate();
            successful = true;
            
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		
		return successful;		
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#addRequest(eu.stork.documentservice.model.RequestModel)
	 */
	@Override
	public boolean addRequest(RequestModel request) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			request.insertValidate();
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);			
			
			pst = con.prepareStatement("INSERT INTO request(REQUESTID, DOCID, DESTCOUNTRY, SPCOUNTRY, "
					+ "SPID, XMLREQUEST, REQTIMESTAMP) VALUES(?, ?, ?, ?, ?, ?, ?)");
            pst.setString(1, request.getRequestid());
            pst.setString(2, request.getDocid());
            pst.setString(3, request.getDestcountry());
            pst.setString(4, request.getSpcountry());
            pst.setString(5, request.getSpid());
            pst.setString(6, request.getXmlrequest());
			pst.setTimestamp(7, DatabaseHelper.getSqlCurrentDate());
            pst.executeUpdate();
            successful = true;
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return successful;
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#getRequest(java.lang.String)
	 */
	@Override
	public RequestModel getRequest(String requestId) throws DatabaseException
	{
		RequestModel request = null;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			if (requestId == null || requestId.isEmpty())
				throw new DatabaseException("Request ID is null or empty.");
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);
			String commandString = "select DOCID, DESTCOUNTRY, SPCOUNTRY, SPID, XMLREQUEST, XMLRESPONSE, REQTIMESTAMP, "
				+ "RESTIMESTAMP from request where REQUESTID = ?";
							
			pst = con.prepareStatement(commandString);
			pst.setString(1, requestId);
            
			ResultSet set = pst.executeQuery();
            if (set.next())
            {
            	request = new RequestModel();
            	request.setRequestid(requestId);
            	request.setDocid(set.getString(1));
            	request.setDestcountry(set.getString(2));
            	request.setSpcountry(set.getString(3));
            	request.setSpid(set.getString(4));
            	request.setXmlrequest(set.getString(5));
            	request.setXmlresponse(set.getString(6));
            	request.setReqtimestamp(DatabaseHelper.getUtilDate(set.getTimestamp(7)));
            	request.setRestimestamp(DatabaseHelper.getSqlDate(set.getTimestamp(8)));
            }
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		
		return request;
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#getRequestByDocId(java.lang.String)
	 */
	@Override
	public RequestModel getRequestByDocId(String docId) throws DatabaseException
	{
		RequestModel request = null;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			if (docId == null || docId.isEmpty())
				throw new DatabaseException("Document ID is null or empty.");
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);
			String commandString = "select REQUESTID, DESTCOUNTRY, SPCOUNTRY, SPID, XMLREQUEST, XMLRESPONSE, REQTIMESTAMP, "
				+ "RESTIMESTAMP from request where DOCID = ?";
							
			pst = con.prepareStatement(commandString);
			pst.setString(1, docId);
            
			ResultSet set = pst.executeQuery();
            if (set.next())
            {
            	request = new RequestModel();
            	request.setDocid(docId);
            	request.setRequestid(set.getString(1));
            	request.setDestcountry(set.getString(2));
            	request.setSpcountry(set.getString(3));
            	request.setSpid(set.getString(4));
            	request.setXmlrequest(set.getString(5));
            	request.setXmlresponse(set.getString(6));
            	request.setReqtimestamp(DatabaseHelper.getUtilDate(set.getTimestamp(7)));
            	request.setRestimestamp(DatabaseHelper.getSqlDate(set.getTimestamp(8)));
            }
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		
		return request;
	}
	
	/* (non-Javadoc)
	 * @see eu.stork.documentservice.data.DatabaseConnector#updateRequest(eu.stork.documentservice.model.RequestModel)
	 */
	@Override
	public boolean updateRequest(RequestModel request) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			request.updateValidate();
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);			
			
			pst = con.prepareStatement("update request set DOCID = ?, DESTCOUNTRY = ?, SPCOUNTRY = ?, SPID = ?, "
					+ "XMLREQUEST = ?, XMLRESPONSE = ?, RESTIMESTAMP = ? where REQUESTID = ?");            
			pst.setString(1, request.getDocid());
			pst.setString(2, request.getDestcountry());
            pst.setString(3, request.getSpcountry());
            pst.setString(4, request.getSpid());
            pst.setString(5, request.getXmlrequest());
            pst.setString(6, request.getXmlresponse());
			pst.setTimestamp(7, DatabaseHelper.getSqlDate(request.getRestimestamp()));
			pst.setString(8, request.getRequestid());
            pst.executeUpdate();
            successful = true;
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return successful;
	}
	
	@Override
	public boolean addTempDocument(TempDocumentModel document) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{	
			document.insertValidate();
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);			
			
			pst = con.prepareStatement("INSERT INTO temp_document(DOCID, DOCUMENT, MIMETYPE, CREATED, RECEIVERCERT, "
					+ "ENCKEY, ENCIV, SPID) VALUES(?, ?, ?, ?, ?, ?, ?, (SELECT ID FROM spid where spid = ?))");
            pst.setString(1, document.getDocid());
            pst.setBlob(2, document.getDataStream());
            pst.setString(3, document.getMimetype());
			pst.setTimestamp(4, DatabaseHelper.getSqlCurrentDate());
			pst.setString(5, document.getReicevercert());
			pst.setString(6, document.getEnckey());
			pst.setString(7, document.getEnciv());
			pst.setString(8, document.getSpid());
            pst.executeUpdate();
            successful = true;
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return successful;
	}
	
	@Override
	public TempDocumentModel getTempDocument(String docId) throws DatabaseException
	{
		TempDocumentModel document = null;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			if (docId == null || docId.isEmpty())
				throw new DatabaseException("Document ID is null or empty.");
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);
			String commandString = "select temp.DOCUMENT, temp.MIMETYPE, temp.CREATED, temp.FETCHED, temp.DELETED, "
				+ "temp.RECEIVERCERT, temp.ENCKEY, temp.ENCIV, sp.SPID "
				+ "from temp_document temp inner join spid sp on sp.ID = temp.SPID where DOCID = ?";
							
			pst = con.prepareStatement(commandString);
			pst.setString(1, docId);
            
			ResultSet set = pst.executeQuery();
            if (set.next())
            {
            	document = new TempDocumentModel();
            	document.setDocid(docId);
            	document.setDataStream(set.getBinaryStream(1));
            	document.setMimetype(set.getString(2));
            	document.setCreated(DatabaseHelper.getUtilDate(set.getTimestamp(3)));
            	document.setFetched(DatabaseHelper.getUtilDate(set.getTimestamp(4)));
            	document.setDeleted(DatabaseHelper.getUtilDate(set.getTimestamp(5)));
            	document.setReicevercert(set.getString(6));
            	document.setEnckey(set.getString(7));
            	document.setEnciv(set.getString(8));
            	document.setSpid(set.getString(9));
            }
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return document;
	}
	
	@Override
	public boolean updateTempDocument(TempDocumentModel document) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{	
			document.updateValidate();
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);			
			
			pst = con.prepareStatement("update temp_document set DOCUMENT = ?, MIMETYPE = ?, FETCHED = ?, "
					+ "RECEIVERCERT = ?, ENCKEY = ?, ENCIV = ? where DOCID = ?");            
            pst.setBlob(1, document.getDataStream());
            pst.setString(2, document.getMimetype());
			pst.setTimestamp(3, DatabaseHelper.getSqlCurrentDate());
			pst.setString(4, document.getReicevercert());
			pst.setString(5, document.getEnckey());
			pst.setString(6, document.getEnciv());
			pst.setString(7, document.getDocid());
            pst.executeUpdate();
            successful = true;
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		catch (ModelException mex)
		{
			throw new DatabaseException(mex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		return successful;
	}
	
	@Override
	public boolean deleteTempDocument(String docId) throws DatabaseException
	{
		boolean successful = false;
		Connection con = null;
		PreparedStatement pst = null;
		
		try
		{			
			if (docId == null || docId.isEmpty())
				throw new DatabaseException("Document ID is null or empty.");
			String url = "jdbc:mysql://" + server + "/" + database;
			con = DriverManager.getConnection(url, user, password);
			String commandString = "update temp_document set document = null, deleted = ? where DOCID = ?";
							
			pst = con.prepareStatement(commandString);
			pst.setTimestamp(1, DatabaseHelper.getSqlCurrentDate());
			pst.setString(2, docId);
            
			pst.executeUpdate();
            successful = true;
            
		}
		catch (SQLException ex)
		{
			throw new DatabaseException(ex);
		}
		finally
		{
			try
			{
				if (pst != null) {
	                pst.close();
	            }
	            if (con != null) {
	                con.close();
	            }
			}
			catch (SQLException ex)
			{
				throw new DatabaseException(ex);
			}
		}
		
		return successful;		
	}
}