//	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.DAOSession;
import com.j0h1.contour.admin.server.utils.DatabaseManager;
import com.j0h1.contour.admin.shared.DAOSessionException;
import com.j0h1.contour.admin.shared.domain.Player;
import com.j0h1.contour.admin.shared.domain.Session;
import com.j0h1.contour.admin.shared.domain.Track;

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

	@Override
	public ArrayList<Session> findAll() throws DAOSessionException {
		openConnection();		
		connection = dbm.getConnection();
		
		ArrayList<Session> ret = new ArrayList<Session>();
		
		try {
			prep = connection.prepareStatement("select sessionId, playerId, "
					+ "trackId, score, date "
					+ "from session");
			ResultSet sessions = prep.executeQuery();
			
			while(sessions.next()) {
				Player player = new Player();
				player.setPlayerId(sessions.getInt("playerId"));
				
				Track track = new Track();
				track.setTrackId(sessions.getInt("trackId"));
				
				// query for player
				prep = connection.prepareStatement("select playername, phoneId "
						+ "from player "
						+ "where playerId = ?");
				prep.setInt(1, player.getPlayerId());
				
				ResultSet currentPlayer = prep.executeQuery();
				
				if (currentPlayer.next()) {
					player.setPlayername(currentPlayer.getString("playername"));
					player.setPhoneId(currentPlayer.getString("phoneId"));
				}
				
				currentPlayer.close();
				
				if (track.getTrackId() != 0) {
					// query for track
					prep = connection.prepareStatement("select trackname "
							+ "from track_translation "
							+ "where trackId = ? and langcode = 'en'");
					prep.setInt(1, track.getTrackId());
					
					ResultSet currentTrack = prep.executeQuery();
					
					if (currentTrack.next()) {
						track.setTitleEN(currentTrack.getString("trackname"));
					}
					
					currentTrack.close();
				}				
				
				// create session and return it
				ret.add(new Session(sessions.getInt("sessionId"), player, track, 
						sessions.getFloat("score"), sessions.getLong("date")));
			}
			
			sessions.close();
			prep.close();
			
			return ret;			
		} catch (SQLException ex) {
			throw new DAOSessionException("Unable to list sessions.");
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public void delete(int sessionId) throws DAOSessionException {
		openConnection();		
		connection = dbm.getConnection();
		setAutoCommit(false);
		
		try {
			prep = connection.prepareStatement("select trackId "
					+ "from session "
					+ "where sessionId = ?");
			prep.setInt(1, sessionId);
			
			ResultSet session = prep.executeQuery();
			
			int trackId = 0;
			
			if (session.next()) {
				trackId = session.getInt("trackId");
			}
			
			session.close();
			
			// 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();
				
				// 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();
			
			connection.commit();
		} catch (SQLException ex) {
			throw new DAOSessionException("Unable to delete "
					+ "session with id " + sessionId);
		} finally {
			closeConnection();
		}
	}

}
