//	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 com.j0h1.contour.admin.server.dao.AbstractDAO;
import com.j0h1.contour.admin.server.dao.DAOTrack;
import com.j0h1.contour.admin.server.utils.DatabaseManager;
import com.j0h1.contour.admin.shared.DAOTrackException;
import com.j0h1.contour.admin.shared.domain.Track;

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

	@Override
	public void persist(Track track) throws DAOTrackException {
		openConnection();		
		connection = dbm.getConnection();
		setAutoCommit(false);
		
		// create new track entry and generate trackId
		int trackId = 0;
		
		try {
			try {
				prep = connection.prepareStatement("insert into track "
						+ "(trackid) "
						+ "values (null)", 
						Statement.RETURN_GENERATED_KEYS);
				prep.executeUpdate();
				
				ResultSet generatedTrackId = prep.getGeneratedKeys();
				
				if (generatedTrackId.next()) {
					trackId = generatedTrackId.getInt(1);
				} else {
					throw new DAOTrackException("Unable to create new track.");
				}
				
				generatedTrackId.close();
				prep.close();
			} catch (SQLException e) {
				throw new DAOTrackException("Unable to create new track.");
			}
			
			// insert track translations
			try {
				prep = connection.prepareStatement("insert into track_translation "
						+ "(trackid, langcode, trackname) "
						+ "values(?, ?, ?)");
				prep.setInt(1, trackId);
				prep.setString(2, "en");
				prep.setString(3, track.getTitleEN());
				prep.executeUpdate();
				prep.close();
				
				prep = connection.prepareStatement("insert into track_translation "
						+ "(trackid, langcode, trackname) "
						+ "values(?, ?, ?)");
				prep.setInt(1, trackId);
				prep.setString(2, "de");
				prep.setString(3, track.getTitleDE());
				prep.executeUpdate();
				prep.close();
			} catch (SQLException e) {
				throw new DAOTrackException("Unable to create track translations.");
			}
			
			// add entries into images_in_track to associate images with track
			try {
				for (int i = 0; i < track.getImages().size(); i++) {
					prep = connection.prepareStatement("insert into image_in_track "
							+ "(trackid, imageid, position) "
							+ "values(?, ?, ?)");
					prep.setInt(1, trackId);
					prep.setInt(2, track.getImages().get(i).getImageId());
					prep.setInt(3, track.getImages().get(i).getPosition());
					prep.executeUpdate();
					prep.close();
				}
			} catch (SQLException e) {
				throw new DAOTrackException("Unable to create track translations.");
			}
			
			// commit changes
			try {
				connection.commit();
			} catch (SQLException ex) {
				throw new DAOTrackException("Error committing changes to database.");
			}
		} catch (DAOTrackException ex) {
			try {
				connection.rollback();
				throw ex;
			} catch (SQLException e) {
				throw ex;
			}			
		} finally {
			closeConnection();
		}
	}

	@Override
	public void delete(int trackId) throws DAOTrackException {
		openConnection();		
		connection = dbm.getConnection();		
		setAutoCommit(false);
		
		try {
			// check if track has images assigned
			boolean trackHasImages = false;
			
			try {
				prep = connection.prepareStatement("select imageid "
						+ "from image_in_track "
						+ "where trackid = ?");
				prep.setInt(1, trackId);
				
				ResultSet imagesInTrack = prep.executeQuery();
				
				if (imagesInTrack.next()) {
					trackHasImages = true;
				}
				
				imagesInTrack.close();
				prep.close();
			} catch (SQLException e) {
				throw new DAOTrackException("Unable to delete Track. "
						+ "Can't check if track has images.");
			}
			
			// if track has images, set deleted flag true
			if (trackHasImages) {
				try {
					prep = connection.prepareStatement("update track "
							+ "set deleted = true "
							+ "where trackid = ?");
					prep.setInt(1, trackId);
					prep.executeUpdate();
					prep.close();
				} catch (SQLException ex) {
					throw new DAOTrackException("Unable to delete track.");
				}
			} else {
				// otherwise delete track from database
				try {
					// delete translations
					prep = connection.prepareStatement("delete from "
							+ "track_translation "
							+ "where trackid = ?");
					prep.setInt(1, trackId);
					prep.executeUpdate();
					prep.close();
					
					// delete track
					prep = connection.prepareStatement("delete from track "
							+ "where trackid = ?");
					prep.setInt(1, trackId);
					prep.executeUpdate();
					prep.close();
				} catch (SQLException ex) {
					throw new DAOTrackException("Unable to delete track.");
				}
			}
			
			try {
				connection.commit();
			} catch (SQLException e) {
				throw new DAOTrackException("Unable to commit "
						+ "track deletion.");
			}
		} catch (DAOTrackException ex) {
			try {
				connection.rollback();
				throw ex;
			} catch (SQLException e) {
				throw ex;
			}
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public ArrayList<Track> findAll() throws DAOTrackException {
		openConnection();		
		connection = dbm.getConnection();
		
		ArrayList<Track> ret = new ArrayList<Track>();
		
		try {
			prep = connection.prepareStatement("select a.trackid, trackname "
					+ "from track a, track_translation b "
					+ "where a.trackid = b.trackid "
					+ "and langcode = 'en' "
					+ "and deleted = false");
			
			ResultSet tracks = prep.executeQuery();
			
			while(tracks.next()) {
				Track tempTrack = new Track();
				tempTrack.setTrackId(tracks.getInt("trackid"));
				tempTrack.setTitleEN(tracks.getString("trackname"));
				
				ret.add(tempTrack);
			}
			
			tracks.close();
			prep.close();
			
			return ret;
		} catch (SQLException e) {
			throw new DAOTrackException("Unable to find tracks.");
		} finally {
			closeConnection();
		}
	}	
}
