//	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.admin.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.admin.server.dao.AbstractDAO;
import com.j0h1.contour.admin.server.dao.DAOImage;
import com.j0h1.contour.admin.server.utils.DatabaseManager;
import com.j0h1.contour.admin.shared.DAOImageException;
import com.j0h1.contour.admin.shared.domain.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 void persist(String titleEN, String titleDE, String originalContent, 
			String edgeContent) throws DAOImageException {		
		openConnection();		
		connection = dbm.getConnection();
		setAutoCommit(false);
    	
		try {
			// check if image title is already in use
			try {				
				prep = connection.prepareStatement("select * "
						+ "from image_translation where "
						+ "imagename = ? or imagename = ?");
				prep.setString(1, titleEN);
				prep.setString(2, titleDE);
				
				ResultSet imageExisting = prep.executeQuery();
				
				if (imageExisting.next()) {
					throw new DAOImageException("Image with this title "
							+ "(DE or EN) already existing.");
				}
				
				imageExisting.close();
				prep.close();
			} catch (SQLException ex) {
				throw new DAOImageException("Failed to select image "
						+ "translation from database.");
			}
			
			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 generatedImageId = prep.getGeneratedKeys();
		    	
		    	if (generatedImageId.next()) {
		    		imageId = generatedImageId.getInt(1); // set id
		    	} else {
		    		throw new DAOImageException("Error creating new image.");
		    	}
		    	
		    	generatedImageId.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();
			} 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 void delete(int imageId) {
		openConnection();		
		connection = dbm.getConnection();
		setAutoCommit(false);
		
		try {			
			boolean imageInTrack = false;
			
			// check if the image is in a track
			try {
				prep = connection.prepareStatement("select trackid "
						+ "from image_in_track "
						+ "where imageid = ?");
				prep.setInt(1, imageId);
				ResultSet imageIsInTrack = prep.executeQuery();
				
				if (imageIsInTrack.next()) {
					imageInTrack = true;
				}
				
				imageIsInTrack.close();
				prep.close();
			} catch (SQLException e) {
				throw new DAOImageException("Error finding image in track.");
			}
			
			if (imageInTrack) {
				// update image's deleted flag
				try {
					prep = connection.prepareStatement("update image "
							+ "set deleted = true "
							+ "where imageid = ?");
					prep.setInt(1, imageId);
					prep.executeUpdate();
					prep.close();
				} catch (SQLException ex) {
					throw new DAOImageException("Unable to delete image.");
				}
			} else {
				// delete image from database
				try {
					// delete translations
					prep = connection.prepareStatement("delete from image_translation "
							+ "where imageid = ?");
					prep.setInt(1, imageId);
					prep.executeUpdate();
					prep.close();
					
					// delete image data
					prep = connection.prepareStatement("delete from image_orig "
							+ "where imageid = ?");
					prep.setInt(1, imageId);
					prep.executeUpdate();
					prep.close();
					
					prep = connection.prepareStatement("delete from image_edge "
							+ "where imageid = ?");
					prep.setInt(1, imageId);
					prep.executeUpdate();
					prep.close();
					
					// delete image
					prep = connection.prepareStatement("delete from image "
							+ "where imageid = ?");
					prep.setInt(1, imageId);
					prep.executeUpdate();
					prep.close();
				} catch (SQLException ex) {
					throw new DAOImageException("Unable to delete image.");
				}
			}
			
			// commit changes to database
			try {
				connection.commit();
			} catch (SQLException e) {
				throw new DAOImageException("Unable to commit image deletion.");
			}
		} catch (DAOImageException ex) {
			try {
				connection.rollback();
				throw ex;
			} catch (SQLException e) {
				throw ex;
			}			
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public ArrayList<Image> findAll() {
		openConnection();
		connection = dbm.getConnection();
		
		ArrayList<Image> result = new ArrayList<Image>();
		
		try {
			prep = connection.prepareStatement("select b.imageId, a.imagename "
					+ "from image_translation a, image b "
					+ "where a.imageid = b.imageid and a.langcode = 'en' "
					+ "and b.deleted = false "
					+ "MINUS "
					+ "select DISTINCT b.imageId, c.imagename "
					+ "from session a, drawing b, image_translation c "
					+ "where a.sessionId = b.sessionId and c.imageId = b.imageId "
					+ "and a.trackId is null");
			ResultSet images = prep.executeQuery();
			
			while (images.next()) {
				// create temporary Image object
				Image temp = new Image();
				temp.setImageId(images.getInt("imageid"));
				temp.setTitleEN(images.getString("imagename"));
				
				// check if current image from ResultSet is in a Track
				prep = connection.prepareStatement("select trackid "
						+ "from image_in_track "
						+ "where imageid = ?");
				prep.setInt(1, temp.getImageId());
				
				ResultSet imageInTrack = prep.executeQuery();
				
				if (imageInTrack.next()) {
					temp.setInTrack(true);
				} else {
					temp.setInTrack(false);
				}
				
				imageInTrack.close();
				result.add(temp); // add current image to result list
			}
			
			images.close();
			prep.close();
			
			return result;
		} catch (SQLException e) {
			throw new DAOImageException("Unable to find images.");
		} finally {
			closeConnection();
		}
	}
	
}
