//	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.database;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.SerializationUtils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;

import com.j0h1.contour.pojo.Image;
import com.j0h1.contour.pojo.ImagePojo;
import com.j0h1.contour.pojo.Player;
import com.j0h1.contour.serialized.CustomPath;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
import com.sun.jersey.core.util.Base64;

/**
 * This class contains all methods to access or modify data
 * of the local SQLite database.
 * @author Johannes Riedmann
 *
 */
public class ContourDatabase extends SQLiteAssetHelper {
	
	private static final String DATABASE_NAME = "contour.db";
	private static final int DATABASE_VERSION = 1;
	
	public ContourDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	/**
	 * Creates a local track session and stores all edge images of a given track.
	 * @param playerId		ID if player that starts the session.
	 * @param trackId		ID of track that is assigned to the session.
	 * @param edgeImages	Edge images of the given track.
	 */
	public void startTrackSession(int playerId, int trackId, List<ImagePojo> edgeImages) {
		// create session
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("playerId", playerId);
		cv.put("trackId", trackId);
		
		db.insert("session", null, cv);
		
		cv = null;
		
		// persist edge images locally
		// when an image is deleted from a track, some positions
		// may not be set; since the server-sided query returns a
		// ordered list of the edge images, the positions can easily
		// be reconstructed here
		int position = 1;
		
		for (ImagePojo i : edgeImages) {			
			cv = new ContentValues();
			cv.put("imageId", i.getImageId());
			cv.put("imagename", i.getImagename());
			cv.put("position", position);
			cv.put("edgedata", i.getData());
			cv.putNull("originaldata");
			
			db.insert("image", null, cv);
			
			cv = null;
			
			position++;
		}
		
		db.close();
	}
	
	/**
	 * Creates a local custom session and stores the edge image, as well as the original
	 * image data locally.
	 * @param playerId		ID of player that starts the session.
	 * @param edgeImage		Generated edge image data.
	 * @param originalData	Original image data of the selected image.
	 */
	public void startCustomSession(int playerId, Image edgeImage, byte[] originalData) {
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("playerId", playerId);
		cv.putNull("trackId");
		
		db.insert("session", null, cv);
		
		// persist image
		cv = new ContentValues();
		cv.put("imageId", edgeImage.getImageId());
		cv.put("imagename", edgeImage.getImagename());
		cv.put("position", edgeImage.getPosition());
		cv.put("edgedata", edgeImage.getData());
		cv.put("originalData", originalData);
			
		db.insert("image", null, cv);
		
		db.close();
	}
	
	/**
	 * This method is meant to check if there is currently an ongoing session.
	 * @return	Amount of sessions currently persisted in the local database.
	 * 			This should be either 0 or 1, because the database is always cleaned
	 * 			up after a session is finished or discarded.
	 */
	public int getSessionCount() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select count(*) from session", null);
		
		int ret = 0;
		
		if (c.moveToFirst()) {
			ret = c.getInt(0);
		}
		
		c.close();			
		db.close();
		
		return ret;
	}
	
	/**
	 * Returns the total amount of edge images stores in the local database.
	 * @return Amount of edge images in local database.
	 */
	public int getImageCount() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select count(*) from image", null);
		
		int ret = 0;
		
		if (c.moveToFirst()) {
			ret = c.getInt(0);
		}
		
		c.close();			
		db.close();
		
		return ret;
	}
	
	/**
	 * Returns the sum of the locally persisted score images. This is equal to the
	 * currently reached score in an ongoing session.
	 * @return Sum of scores assigned to score images.
	 */
	public double getCurrentScore() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select sum(score) from scoreimage", null);
		
		double ret = 0;
		
		if (c.moveToFirst()) {
			ret = c.getDouble(0);
		}
		
		return ret;
		
	}
	
	/**
	 * Returns an edge image at a specific position.
	 * @param position	Position of edge image in the current track (or custom session).
	 * @return Edge image at the given position.
	 */
	public Image getImageAtPosition(int position) {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select imageId, imagename, edgedata "
				+ "from image "
				+ "where position = " + position, null);
		
		Image ret = null;
		
		if (c.moveToFirst()) {
			ret = new Image();
			ret.setImageId(c.getInt(0));
			ret.setImagename(c.getString(1));
			ret.setPosition(position);
			ret.setData(c.getBlob(2));
		}
		
		c.close();			
		db.close();
			
		return ret;
	}
	
	/**
	 * Returns the image data of a custom session.
	 * @return	Image data of current custom session.
	 */
	public ArrayList<ImagePojo> getCurrentCustomImageData() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select imagename, edgedata, originaldata "
				+ "from image", null);
		
		ArrayList<ImagePojo> ret = new ArrayList<ImagePojo>();
		
		if (c.moveToFirst()) {
			if (c.getType(2) == Cursor.FIELD_TYPE_NULL) {
				// when no original data = no custom session or data missing
				return ret;
			}
			
			// add edge image data
			ImagePojo temp = new ImagePojo();
			temp.setImagename(c.getString(0));
			temp.setData(new String(Base64.encode(c.getBlob(1))));
			ret.add(temp);
			
			// add original image data
			temp = new ImagePojo();
			temp.setImagename(c.getString(0));
			temp.setData(new String(Base64.encode(c.getBlob(2))));
			ret.add(temp);
		}
		
		return ret;
	}
	
	/**
	 * Returns the latest image that has no drawing assigned to it, which is the last
	 * image that the user had opened in the game. If the drawing session was already over
	 * as the user closed the application, null is returned.
	 * @return	Last image with no drawing assigned.
	 */
	public Image getLastImage() {
		SQLiteDatabase db = getReadableDatabase();
		
		// select the highest position of an image that has a drawing persisted
		Cursor c = db.rawQuery("select max(position) from "
				+ "image a, drawing b where "
				+ "a.imageId = b.imageId", null);
		
		// if above query returns null no drawing has been persisted and
		// the image with the minimal position value is returned		
		Integer position = 1;
		
		if (c.moveToFirst()) {
			// otherwise position = last position + 1
			if (c.getType(0) != Cursor.FIELD_TYPE_NULL) {
				position = c.getInt(0) + 1;
			}
		}
		
		c.close();
		
		c = db.rawQuery("select imageId, imagename, position, edgedata "
				+ "from image "
				+ "where position = ?", new String[] { "" + position.intValue() });
		
		Image ret = null;
		
		// is c.moveToFirst == false -> session was already finished, because the last
		// image has already a drawing persisted
		if (c.moveToFirst()) {
			ret = new Image();
			ret.setImageId(c.getInt(0));
			ret.setImagename(c.getString(1));
			ret.setPosition(c.getInt(2));
			ret.setData(c.getBlob(3));
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
	/**
	 * Returns a list of CustomPath objects (= serializable Path objects) for
	 * a given image.
	 * @param imageId	ImageId to get paths from.
	 * @return	List of CustomPath object persisted for the image with the given imageId.
	 */
	public ArrayList<CustomPath> getPathListForImage(int imageId) {
		ArrayList<CustomPath> ret = new ArrayList<CustomPath>();
		
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select pathdata, portrait from path where imageId = ?", 
				new String[] { ""+ imageId });
		
		while(c.moveToNext()) {
			CustomPath tempPath = (CustomPath) SerializationUtils.deserialize(c.getBlob(0));
			tempPath.setPortraitPath(c.getInt(1) > 0);
			ret.add(tempPath);
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
	/**
	 * Updates a user drawing, represented by a list of paths, for an image with
	 * the given imageId.
	 * @param imageId	ImageId of the image, whose drawing gets updated.
	 * @param paths		Updated drawing information, stored in a list of serializable
	 * 					Path objects.
	 */
	public void updateDrawing(int imageId, ArrayList<CustomPath> paths) {
		// delete previous Paths for this drawing
		deletePathsForDrawing(imageId);
		
		// store paths in database
		SQLiteDatabase db = getWritableDatabase();
		
		for (CustomPath p : paths) {
			byte[] data = SerializationUtils.serialize(p);
			
			ContentValues cv = new ContentValues();
			cv.put("imageId", imageId);
			cv.put("pathdata", data);
			cv.put("portrait", p.isPortraitPath());
			
			db.insert("path", null, cv);
		}
		
		db.close();
	}
	
	/**
	 * Deletes all paths assigned to a drawing.
	 * @param imageId	ImageId, the drawing is associated with.
	 */
	public void deletePathsForDrawing(int imageId) {
		SQLiteDatabase db = getWritableDatabase();
		
		db.delete("path", "imageId = ?", new String[] { "" + imageId });
		
		db.close();
	}
	
	/**
	 * Stores a drawing in the local database.
	 * @param imageId	ImageId of the image that needs to be persisted.
	 * @param drawing	Drawing data as Bitmap objects that needs to be persisted.
	 * @return	Generated drawingId.
	 */
	public int persistDrawing(int imageId, Bitmap drawing) {
		// convert the Bitmap to a byte array		
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		drawing.compress(Bitmap.CompressFormat.PNG, 100, out);
		byte[] drawingBytes = out.toByteArray();
		
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("imageId", imageId);
		cv.put("drawingdata", drawingBytes);
		
		int key = (int) db.insert("drawing", null, cv);
		
		db.close();
		
		return key;
	}
	
	/**
	 * Stores a score image in the local database.
	 * @param drawingId		ID of the drawing the score image is assigned to.
	 * @param score			Score that was achieved for the drawing.
	 * @param scoreImage	Score image data as Bitmap objects that needs 
	 * 						to be persisted.
	 */
	public void persistScoreImage(int drawingId, double score, Bitmap scoreImage) {
		// convert the Bitmap to a byte array
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		scoreImage.compress(Bitmap.CompressFormat.PNG, 100, out);
		byte[] scoreImageBytes = out.toByteArray();
		
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("drawingId", drawingId);
		cv.put("score", score);
		cv.put("scoreimagedata", scoreImageBytes);
		
		db.insert("scoreimage", null, cv);
		
		db.close();
	}
	
	/**
	 * Wipes all data from the local database, except for the player data.
	 * That means session data, image data that belongs to this session and
	 * path data that is assigned to a specific drawing.
	 */
	public void discardSession() {
		SQLiteDatabase db = getWritableDatabase();
		
		db.delete("scoreimage", null, null);
		db.delete("drawing", null, null);
		db.delete("path", null, null);
		db.delete("image", null, null);
		db.delete("session", null, null);
		
		db.close();
	}
	
	/**
	 * Returns the trackId of the current track session.
	 * @return	TrackId of current track in drawing session.
	 */
	public Integer getCurrentTrackId() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select trackId from session", null);
		
		c.moveToFirst();
		
		Integer ret = 0;
		
		if (c.getType(0) == Cursor.FIELD_TYPE_NULL) {
			return Integer.MAX_VALUE;
		} else {
			ret = c.getInt(0);
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
	/**
	 * Returns all drawings persisted locally. Image data gets base64 encoded.
	 * @return	List of drawings for the current session.
	 */
	public ArrayList<ImagePojo> getDrawings() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select imageId, drawingdata from drawing", null);
		
		ArrayList<ImagePojo> ret = new ArrayList<ImagePojo>();
		
		while(c.moveToNext()) {
			ImagePojo temp = new ImagePojo();
			temp.setImageId(c.getInt(0));
			
			// base64 encode the image data
			String base64String = new String(Base64.encode(c.getBlob(1)));
			
			temp.setData(base64String);
			
			ret.add(temp);
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
	/**
	 * Returns all score images persisted locally. Image data gets base64 encoded.
	 * @return	List of score images for the current session.
	 */
	public ArrayList<ImagePojo> getScoreImages() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select drawingId, score, scoreimagedata from scoreimage", null);
		
		ArrayList<ImagePojo> ret = new ArrayList<ImagePojo>();
		
		while(c.moveToNext()) {
			ImagePojo temp = new ImagePojo();
			temp.setImageId(c.getInt(0));
			temp.setScore(c.getDouble(1));
			
			// base64 encode the image data
			String base64String = new String(Base64.encode(c.getBlob(2)));
			
			temp.setData(base64String);
			
			ret.add(temp);
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
	/**
	 * Retrieve the locally persisted player. Only one player can be persisted locally
	 * at one time.
	 * @return Player objects that represents the locally persisted player.
	 */
	public Player findPlayer() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select playerId, playername from player", null);
		
		Player ret = null;
		
		if (c.moveToFirst()) {
			ret = new Player(c.getInt(0), c.getString(1));
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
	/**
	 * Persists a player locally.
	 * @param player Player to be persisted locally.
	 */
	public void persistPlayer(Player player) {
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("playerId", player.getPlayerId());
		cv.put("playername", player.getPlayername());
		
		db.insert("player", null, cv);
		
		db.close();
	}
	
	/**
	 * Deletes the locally persisted player.
	 */
	public void deletePlayer() {
		SQLiteDatabase db = getWritableDatabase();
		
		db.delete("player", null, null);
		
		db.close();
	}
	
	/**
	 * Updates the recent screen orientation of the session.
	 * @param orientation 1 = portrait mode; 2 = landscape mode
	 */
	public void updateRecentScreenOrientation(int orientation) {
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("recentScreenOrientation", orientation);
		
		db.update("session", cv, null, null);
		
		db.close();
	}
	
	/**
	 * Returns the recent screen orientation of the session.
	 * @return 1 = portrait mode, 2 = landscape mode
	 */
	public int getRecentScreenOrientation() {
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor c = db.rawQuery("select recentScreenOrientation from session", null);
		
		int ret = 0;
		
		if (c.moveToFirst()) {
			ret = c.getInt(0);
		}
		
		c.close();
		db.close();
		
		return ret;
	}
	
}
