//	Contour - Android drawing teacher application
//	Copyright (C) 2014  Johannes Riedmann
//	
//	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 com.j0h1.contour.server.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.commons.codec.binary.Base64;

import com.j0h1.contour.server.DatabaseManager;
import com.j0h1.contour.server.dao.AbstractDAO;
import com.j0h1.contour.server.dao.DAOImage;
import com.j0h1.contour.server.dao.DAOImageException;
import com.j0h1.contour.server.rest.pojo.Image;

/**
 * Implementations for DAOImage.
 * @author Johannes Riedmann
 *
 */
public class DAOImageImpl extends AbstractDAO implements DAOImage {
	
	private Connection connection;
	private PreparedStatement prep;
	private DatabaseManager dbm;
	
	public DAOImageImpl() {
		connection = null;
		prep = null;
		dbm = DatabaseManager.getInstance();
	}

	@Override
	public ArrayList<Image> findEdgeImagesByTrackId(int trackId, String language) throws DAOImageException {
		openConnection();
		connection = dbm.getConnection();
		
		ArrayList<Image> ret = new ArrayList<Image>();
		
		try {
			prep = connection.prepareStatement("select a.imageid, d.imagename, c.position, b.data " +
					"from image a, image_edge b, image_in_track c, image_translation d " + 
					"where b.imageid = c.imageid and a.imageid = c.imageid and c.imageid = d.imageid " +
					"and c.trackid = ? and d.langcode = ? and a.deleted = false " +
					"order by c.position ASC");
			prep.setInt(1, trackId);
			prep.setString(2, language);
			
			ResultSet edgeImages = prep.executeQuery();
			
			while(edgeImages.next()) {
				Image tempImage = new Image();
				tempImage.setImageId(edgeImages.getInt("imageid"));
				tempImage.setImagename(edgeImages.getString("imagename"));
				tempImage.setPosition(edgeImages.getInt("position"));
				tempImage.setData(edgeImages.getBytes("data"));
				
				ret.add(tempImage);
			}
			
			edgeImages.close();
			prep.close();
			
			return ret;
		} catch (SQLException e) {
			throw new DAOImageException("Unable to find images.");
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public ArrayList<Image> getSessionDetails(int sessionId, String language)
			throws DAOImageException {
		openConnection();		
		connection = dbm.getConnection();
		
		ArrayList<Image> ret = new ArrayList<Image>();
		
		try {
			prep = connection.prepareStatement("select trackId "
					+ "from session "
					+ "where sessionId = ?");
			prep.setInt(1, sessionId);
			
			ResultSet customOrTrackSession = prep.executeQuery();
			
			if (customOrTrackSession.next()) {
				if (customOrTrackSession.getInt("trackid") != 0) {
					// track session
					prep = connection.prepareStatement("select d.imageId, c.imagename, e.score "
							+ "from session a, track b, image_translation c, drawing d, scoreimage e "
							+ "where a.sessionId = ? "
							+ "and a.trackId = b.trackId "
							+ "and a.sessionId = d.sessionId "
							+ "and d.drawingId = e.drawingId "
							+ "and d.imageId = c.imageId "
							+ "and c.langcode = ?");
					prep.setInt(1, sessionId);
					prep.setString(2, language);
				} else {
					// custom session
					prep = connection.prepareStatement("select d.imageId, c.imagename, e.score "
							+ "from session a, image_translation c, drawing d, scoreimage e "
							+ "where a.sessionId = ? "
							+ "and a.sessionId = d.sessionId "
							+ "and d.drawingId = e.drawingId "
							+ "and d.imageId = c.imageId "
							+ "and c.langcode = ?");
					prep.setInt(1, sessionId);
					prep.setString(2, language);
				}
			}
			
			customOrTrackSession.close();			
			
			ResultSet sessionDetails = prep.executeQuery();
			
			while(sessionDetails.next()) {
				Image temp = new Image();
				temp.setImageId(sessionDetails.getInt("imageId"));
				temp.setImagename(sessionDetails.getString("imagename"));
				temp.setScore(sessionDetails.getDouble("score"));
				
				ret.add(temp);
			}
			
			sessionDetails.close();
			prep.close();
			
			return ret;
		} catch (SQLException ex) {
			throw new DAOImageException("Unable to find session details.");
		} finally {
			closeConnection();
		}
	}
	
	public int persist(String titleEN, String titleDE, String originalContent, 
			String edgeContent) throws DAOImageException {		
		openConnection();		
		connection = dbm.getConnection();
		setAutoCommit(false);
    	
		try {			
			int imageId = 0;
			
			// insert null entry to image to create an ID
			try {
		    	prep = connection.
		    			prepareStatement("insert into "
		    					+ "image(imageid) "
		    					+ "values(null)",
		    					Statement.RETURN_GENERATED_KEYS);
		    	prep.executeUpdate();
		    	
		    	// get generated ID from image entry
		    	ResultSet insertedImage = prep.getGeneratedKeys();
		    	
		    	if (insertedImage.next()) {
		    		imageId = insertedImage.getInt(1);
		    	} else {
		    		throw new DAOImageException("Error creating new image.");
		    	}
		    	
		    	insertedImage.close();
		    	prep.close();
			} catch (SQLException ex) {
				throw new DAOImageException("Error creating new image.");
			}
	    	
	    	// insert data into image_orig table
			try {
				// decode base64 encoded String and persist binary data
				byte[] decodedContent = Base64.decodeBase64(originalContent.getBytes());
				
		    	prep = connection.
		    			prepareStatement("insert into "
		    					+ "image_orig(imageid, data) "
		    					+ "values(?, ?)");
		    	prep.setInt(1, imageId);
		    	prep.setBytes(2, decodedContent);
		    	prep.executeUpdate();
		    	prep.close();
			} catch (SQLException ex) {
				throw new DAOImageException("Error persisting original image to database.");
			}
			
			// insert data into image_edge table
			try {
				byte[] decodedContent = Base64.decodeBase64(edgeContent.getBytes());
				
		    	prep = connection.
		    			prepareStatement("insert into "
		    					+ "image_edge(imageid, data) "
		    					+ "values(?, ?)");
		    	prep.setInt(1, imageId);
		    	prep.setBytes(2, decodedContent);
		    	prep.executeUpdate();
		    	prep.close();
			} catch (SQLException ex) {
				throw new DAOImageException("Error persisting edge image to database.");
			}
	    	
	    	// insert titles into image_translation table
			try {
		    	prep = connection.prepareStatement("insert into "
		    			+ "image_translation(imageId, langcode, imagename) "
		    			+ "values(?, ?, ?)");
		    	prep.setInt(1, imageId);
		    	prep.setString(2, "en");
		    	prep.setString(3, titleEN);
		    	prep.executeUpdate();
		    	prep.close();
			} catch (SQLException ex) {
				throw new DAOImageException("Error persisting english title to database.");
			}
	    	
			try {
		    	prep = connection.prepareStatement("insert into "
		    			+ "image_translation(imageId, langcode, imagename) "
		    			+ "values(?, ?, ?)");
		    	prep.setInt(1, imageId);
		    	prep.setString(2, "de");
		    	prep.setString(3, titleDE);
		    	prep.executeUpdate();
		    	prep.close();
			} catch (SQLException ex) {
				throw new DAOImageException("Error persisting german title to database.");
			}
	    	
			// commit changes cause auto-commit is disabled
			// auto commit is disabled, cause if one statement fails, everything has to be rolled back
			try {
				connection.commit();
				return imageId;
			} catch (SQLException ex) {
				throw new DAOImageException("Error committing changes to database.");
			}
		} catch (DAOImageException ex) {
			// if anything fails, perform rollback
			try {
				connection.rollback();
				throw ex; // actually throw it to client
			} catch (SQLException e) {
				throw ex;
			}
		} finally {
			closeConnection();			
		}		
	}

	@Override
	public Image getOriginalImageByImageId(int imageId)
			throws DAOImageException {
		openConnection();		
		connection = dbm.getConnection();
		
		Image ret = new Image();
		
		try {
			prep = connection.prepareStatement("select data from image_orig where imageId = ?");
			prep.setInt(1, imageId);
			
			ResultSet originalImage = prep.executeQuery();
			
			if (originalImage.next()) {
				ret.setImageId(imageId);
				ret.setData(originalImage.getBytes("data"));
			}
			
			return ret;
		} catch(SQLException ex) {
			throw new DAOImageException("Unable to find original image.");
		} finally {
			closeConnection();
		}
	}

	@Override
	public Image getEdgeImageByImageId(int imageId)
			throws DAOImageException {
		openConnection();		
		connection = dbm.getConnection();
		
		Image ret = new Image();
		
		try {
			prep = connection.prepareStatement("select data from image_edge where imageId = ?");
			prep.setInt(1, imageId);
			
			ResultSet edgeImage = prep.executeQuery();
			
			if (edgeImage.next()) {
				ret.setImageId(imageId);
				ret.setData(edgeImage.getBytes("data"));
			}
			
			edgeImage.close();
			prep.close();
			
			return ret;
		} catch(SQLException ex) {
			throw new DAOImageException("Unable to find original image.");
		} finally {
			closeConnection();
		}
	}

	@Override
	public Image getDrawingByImageIdAndSessionId(int imageId, int sessionId)
			throws DAOImageException {
		openConnection();		
		connection = dbm.getConnection();
		
		Image ret = new Image();
		
		try {
			prep = connection.prepareStatement("select data "
					+ "from drawing "
					+ "where imageId = ? and sessionId = ?");
			prep.setInt(1, imageId);
			prep.setInt(2, sessionId);
			
			ResultSet userDrawing = prep.executeQuery();
			
			if (userDrawing.next()) {
				ret.setImageId(imageId);
				ret.setData(userDrawing.getBytes("data"));
			}
			
			userDrawing.close();
			prep.close();
			
			return ret;
		} catch(SQLException ex) {
			throw new DAOImageException("Unable to find original image.");
		} finally {
			closeConnection();
		}
	}

	@Override
	public Image getScoreImageByImageIdAndSessionId(int imageId,
			int sessionId) throws DAOImageException {
		openConnection();		
		connection = dbm.getConnection();
		
		Image ret = new Image();
		
		try {
			prep = connection.prepareStatement("select a.data "
					+ "from scoreimage a, drawing b, session c "
					+ "where b.sessionId = ? and "
					+ "b.imageId = ? and "
					+ "b.sessionId = c.sessionId and "
					+ "b.drawingId = a.drawingId");
			prep.setInt(1, sessionId);
			prep.setInt(2, imageId);
			
			ResultSet scoreImage = prep.executeQuery();
			
			if (scoreImage.next()) {
				ret.setImageId(imageId);
				ret.setData(scoreImage.getBytes("data"));
			}
			
			scoreImage.close();
			prep.close();
			
			return ret;
		} catch(SQLException ex) {
			throw new DAOImageException("Unable to find original image.");
		} finally {
			closeConnection();
		}
	}

}
