//	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.util.ArrayList;

import com.j0h1.contour.admin.server.dao.AbstractDAO;
import com.j0h1.contour.admin.server.dao.DAOPlayer;
import com.j0h1.contour.admin.server.utils.DatabaseManager;
import com.j0h1.contour.admin.shared.DAOPlayerException;
import com.j0h1.contour.admin.shared.domain.Player;

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

	@Override
	public ArrayList<Player> findAll() throws DAOPlayerException {
		openConnection();		
		connection = dbm.getConnection();
		
		ArrayList<Player> ret = new ArrayList<Player>();
		
		try {
			prep = connection.prepareStatement("select playerId, playername, phoneId "
					+ "from player");
			ResultSet players = prep.executeQuery();
			
			while(players.next()) {
				Player temp = new Player();
				temp.setPlayerId(players.getInt("playerId"));
				temp.setPlayername(players.getString("playername"));
				temp.setPhoneId(players.getString("phoneId"));
				
				ret.add(temp);
			}
			
			players.close();
			prep.close();
			
			return ret;
		} catch (SQLException ex) {
			throw new DAOPlayerException("Unable to find players.");
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public void delete(int playerId) throws DAOPlayerException {
		openConnection();		
		connection = dbm.getConnection();
		setAutoCommit(false);
		
		try {
			// get sessions from player
			prep = connection.prepareStatement("select sessionId, trackId "
					+ "from session "
					+ "where playerId = ?");
			prep.setInt(1, playerId);
			
			ResultSet sessions = prep.executeQuery();
			
			while (sessions.next()) {
				int sessionId = sessions.getInt("sessionId");
				int trackId = sessions.getInt("trackId");
				
				// get all drawings associated with the session
				prep = connection.prepareStatement("select drawingId "
						+ "from drawing "
						+ "where sessionId = ?");
				prep.setInt(1, sessionId);
				
				ResultSet drawings = prep.executeQuery();
				
				int imageId = 0; // only needed for custom sessions
				
				// delete scoreimages associated with the drawings from this session
				while (drawings.next()) {
					int drawingId = drawings.getInt("drawingId");
					prep = connection.prepareStatement("delete from scoreimage "
							+ "where drawingId = ?");
					prep.setInt(1, drawingId);
					prep.executeUpdate();
					prep.close();
					
					// if it's a custom session, we have to delete all image data
					if (trackId == 0) {						
						prep = connection.prepareStatement("select imageId "
								+ "from drawing "
								+ "where drawingId = ?");
						prep.setInt(1, drawingId);
						
						ResultSet image = prep.executeQuery();
						
						if (image.next()) {
							imageId = image.getInt("imageId");
							
							// delete image translation
							prep = connection.prepareStatement("delete from image_translation "
									+ "where imageId = ?");
							prep.setInt(1, imageId);						
							prep.executeUpdate();
							prep.close();
							
							// delete image_edge / image_orig
							prep = connection.prepareStatement("delete from image_edge "
									+ "where imageId = ?");
							prep.setInt(1, imageId);						
							prep.executeUpdate();
							prep.close();
							
							prep = connection.prepareStatement("delete from image_orig "
									+ "where imageId = ?");
							prep.setInt(1, imageId);						
							prep.executeUpdate();
							prep.close();
						}
						
						image.close();
					}
				}
				
				drawings.close();
				
				// delete drawings associated with the current sessionId
				prep = connection.prepareStatement("delete from drawing "
						+ "where sessionId = ?");
				prep.setInt(1, sessionId);
				prep.executeUpdate();
				prep.close();
				
				// drawings have a reference on the imageId of the image table, so we
				// have to delete the image in custom sessions after the drawing
				if (trackId == 0 && imageId != 0) {
					// delete image
					prep = connection.prepareStatement("delete from image "
							+ "where imageId = ?");
					prep.setInt(1, imageId);						
					prep.executeUpdate();
					prep.close();
				}
				
				// delete current session
				prep = connection.prepareStatement("delete from session "
						+ "where sessionId = ?");
				prep.setInt(1, sessionId);
				prep.executeUpdate();
				prep.close();
			}
			
			sessions.close();
			
			// delete player
			prep = connection.prepareStatement("delete from player "
					+ "where playerId = ?");
			prep.setInt(1, playerId);
			prep.executeUpdate();			
			prep.close();
			
			connection.commit();
		} catch (SQLException ex) {
			throw new DAOPlayerException("Unable to delete player.");
		} finally {
			closeConnection();
		}
	}

}
