//	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 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.DAOSession;
import com.j0h1.contour.server.dao.DAOSessionException;
import com.j0h1.contour.server.rest.pojo.Image;
import com.j0h1.contour.server.rest.pojo.Player;
import com.j0h1.contour.server.rest.pojo.Session;
import com.j0h1.contour.server.rest.pojo.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> getTrackSessions(int playerId, int trackId) throws DAOSessionException {
		openConnection();
		connection = dbm.getConnection();
		
		try {
			if (playerId == Integer.MAX_VALUE) {
				// if the playerId equals Integer.MAX_VALUE, query for global top10
				prep = connection.prepareStatement("select a.sessionId, a.score, a.date, b.playername "
						+ "from session a, player b "
						+ "where a.playerId = b.playerId and trackId = ? "
						+ "order by score DESC limit 10");
				prep.setInt(1, trackId);
			} else {
				// otherwise, query for local top10 (top10 track sessions of a specific player)
				prep = connection.prepareStatement("select a.sessionId, a.score, a.date, b.playername "
						+ "from session a, player b "
						+ "where a.playerId = b.playerId and a.playerId = ? and trackId = ? "
						+ "order by score DESC limit 10");
				prep.setInt(1, playerId);
				prep.setInt(2, trackId);
			}			
			
			ResultSet trackSessions = prep.executeQuery();
			
			ArrayList<Session> sessions = new ArrayList<Session>();
			
			while(trackSessions.next()) {
				Session temp = new Session();
				temp.setSessionId(trackSessions.getInt("sessionId"));
				temp.setScore(trackSessions.getDouble("score"));				
				temp.setDate(trackSessions.getLong("date"));
				
				Player player = new Player();
				player.setPlayerId(playerId);
				player.setPlayername(trackSessions.getString("playername"));
				temp.setPlayer(player);
				
				Track track = new Track();
				track.setTrackId(trackId);
				temp.setTrack(track);
				
				sessions.add(temp);
			}
			
			trackSessions.close();
			prep.close();
			
			return sessions;
		} catch (SQLException ex) {
			throw new DAOSessionException("Unable to recieve sessions.");
		} finally {
			closeConnection();
		}
	}

	@Override
	public ArrayList<Session> getCustomSessions(int playerId) throws DAOSessionException {
		openConnection();
		connection = dbm.getConnection();
		
		try {
			prep = connection.prepareStatement("select a.sessionId, a.score, a.date, b.playername "
					+ "from session a, player b "
					+ "where a.playerId = ? and trackId IS NULL "
					+ "and a.playerId = b.playerId "
					+ "order by score DESC limit 10");
			prep.setInt(1, playerId);
			
			ResultSet customSessions = prep.executeQuery();
			
			ArrayList<Session> sessions = new ArrayList<Session>();
			
			while (customSessions.next()) {
				Session temp = new Session();
				temp.setSessionId(customSessions.getInt("sessionId"));
				temp.setScore(customSessions.getDouble("score"));				
				temp.setDate(customSessions.getLong("date"));
				
				Player player = new Player();
				player.setPlayerId(playerId);
				player.setPlayername(customSessions.getString("playername"));
				temp.setPlayer(player);
				
				Track track = new Track();
				track.setTrackId(Integer.MAX_VALUE);
				temp.setTrack(track);
				
				sessions.add(temp);
			}
			
			customSessions.close();
			prep.close();
			
			return sessions;
		} catch (SQLException ex) {
			throw new DAOSessionException("Unable to receive sessions.");
		} finally {
			closeConnection();
		}
	}

	@Override
	public void persistTrackSession(Session session, String playername, 
			ArrayList<Image> drawings, ArrayList<Image> scoreImages) throws DAOSessionException {
		openConnection();
		connection = dbm.getConnection();
		setAutoCommit(false);
		
		try {
			// query for player	
			Player p = new Player();
			prep = connection.prepareStatement("select playerId "
					+ "from player "
					+ "where playername = ?");
			prep.setString(1, playername);
			
			ResultSet player = prep.executeQuery();
			
			if (player.next()) {
				p.setPlayerId(player.getInt(1));
				p.setPlayername(playername);
			}
			
			// persist session
			prep = connection.prepareStatement("insert into "
					+ "session(trackId, playerId, score, date) "
					+ "values(?, ?, ?, ?)", 
					Statement.RETURN_GENERATED_KEYS);
			prep.setInt(1, session.getTrack().getTrackId());
			prep.setInt(2, p.getPlayerId());
			prep.setDouble(3, session.getScore());
			prep.setLong(4, session.getDate());
			prep.executeUpdate();
			
			ResultSet persistedSession = prep.getGeneratedKeys();
			
			if (persistedSession.next()) {
				session.setSessionId(persistedSession.getInt(1));
			}
			
			persistedSession.close();
			
			// persist drawings and score images
			for (int i = 0; i < drawings.size(); i++) {
				prep = connection.prepareStatement("insert into "
						+ "drawing(sessionId, imageId, data) "
						+ "values(?, ?, ?)", 
						Statement.RETURN_GENERATED_KEYS);
				prep.setInt(1, session.getSessionId());
				prep.setInt(2, drawings.get(i).getImageId());
				prep.setBytes(3, drawings.get(i).getData());
				prep.executeUpdate();
				
				ResultSet persistedDrawing = prep.getGeneratedKeys();
				
				int drawingId = 0;
				
				if (persistedDrawing.next()) {
					drawingId = persistedDrawing.getInt(1);
				}
				
				persistedDrawing.close();
				
				prep = connection.prepareStatement("insert into "
						+ "scoreimage(drawingId, score, data) "
						+ "values(?, ?, ?)");
				prep.setInt(1, drawingId);
				prep.setDouble(2, scoreImages.get(i).getScore());
				prep.setBytes(3, scoreImages.get(i).getData());
				prep.executeUpdate();
			}
			
			prep.close();
			
			connection.commit();
		} catch (SQLException ex) {
			try {
				connection.rollback();
				throw new DAOSessionException("Unable to persist session.");
			} catch (SQLException e) {
				throw new DAOSessionException("Unable to persist session.");
			}
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public void persistCustomSession(Session session, String playername,
			String originalData, String edgeData, String imagename,
			Image drawing, Image scoreImage) throws DAOSessionException {		
		openConnection();
		connection = dbm.getConnection();
		setAutoCommit(false);
		
		try {
			// query for player	
			Player p = new Player();
			prep = connection.prepareStatement("select playerId "
					+ "from player "
					+ "where playername = ?");
			prep.setString(1, playername);
			
			ResultSet player = prep.executeQuery();
			
			if (player.next()) {
				p.setPlayerId(player.getInt(1));
				p.setPlayername(playername);
			}
			
			// persist session
			prep = connection.prepareStatement("insert into "
					+ "session(trackId, playerId, score, date) "
					+ "values(null, ?, ?, ?)", 
					Statement.RETURN_GENERATED_KEYS);
			prep.setInt(1, p.getPlayerId());
			prep.setDouble(2, session.getScore());
			prep.setLong(3, session.getDate());
			prep.executeUpdate();
			
			ResultSet persistedSession = prep.getGeneratedKeys();
			
			if (persistedSession.next()) {
				session.setSessionId(persistedSession.getInt(1));
			}
			
			persistedSession.close();
			
			// persist original image and edge image data and retrieve id
			DAOImage imageDao = new DAOImageImpl();
			int imageId = imageDao.persist(imagename, imagename, originalData, edgeData);
			
			// persist drawing and score image
			prep = connection.prepareStatement("insert into "
					+ "drawing(sessionId, imageId, data) "
					+ "values(?, ?, ?)", 
					Statement.RETURN_GENERATED_KEYS);
			prep.setInt(1, session.getSessionId());
			prep.setInt(2, imageId);
			prep.setBytes(3, drawing.getData());
			prep.executeUpdate();
			
			ResultSet persistedDrawing = prep.getGeneratedKeys();
			
			int drawingId = 0;
			
			if (persistedDrawing.next()) {
				drawingId = persistedDrawing.getInt(1);
			}
			
			persistedDrawing.close();
			
			prep = connection.prepareStatement("insert into "
					+ "scoreimage(drawingId, score, data) "
					+ "values(?, ?, ?)");
			prep.setInt(1, drawingId);
			prep.setDouble(2, scoreImage.getScore());
			prep.setBytes(3, scoreImage.getData());
			prep.executeUpdate();
			
			prep.close();
			
			connection.commit();
		} catch (SQLException ex) {
			try {
				connection.rollback();
				throw new DAOSessionException("Unable to persist session.");
			} catch (SQLException e) {
				throw new DAOSessionException("Unable to persist session.");
			}
		} finally {
			closeConnection();
		}
		
	}

}
