package azkaban.project;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.io.IOUtils;
import org.apache.log4j.Logger;

import azkaban.flow.Flow;
import azkaban.project.ProjectLogEvent.EventType;
import azkaban.user.Permission;
import azkaban.user.User;
import azkaban.utils.DataSourceUtils;
import azkaban.utils.GZIPUtils;
import azkaban.utils.JSONUtils;
import azkaban.utils.Md5Hasher;
import azkaban.utils.Pair;
import azkaban.utils.Props;
import azkaban.utils.PropsUtils;
import azkaban.utils.Triple;

public class JdbcProjectLoader implements ProjectLoader {
	
	/**
	 * Used for when we store text data. Plain uses UTF8 encoding.
	 */
	public static enum EncodingType {
		PLAIN(1), GZIP(2);

		private int numVal;

		EncodingType(int numVal) {
			this.numVal = numVal;
		}

		public int getNumVal() {
			return numVal;
		}

		public static EncodingType fromInteger(int x) {
			switch (x) {
			case 1:
				return PLAIN;
			case 2:
				return GZIP;
			default:
				return PLAIN;
			}
		}
	}
	
	private static final Logger logger = Logger.getLogger(JdbcProjectLoader.class);
	private static final int CHUCK_SIZE = 1024*1024*10;
	private File tempDir;
	
	private DataSource dataSource;
	private EncodingType defaultEncodingType = EncodingType.GZIP;
	
	public JdbcProjectLoader(Props props) {
		tempDir = new File(props.getString("project.temp.dir", "temp"));
		if (!tempDir.exists()) {
			tempDir.mkdirs();
		}
		String databaseType = props.getString("database.type");
	
		if (databaseType.equals("mysql")) {
			int port = props.getInt("mysql.port");
			String host = props.getString("mysql.host");
			String database = props.getString("mysql.database");
			String user = props.getString("mysql.user");
			String password = props.getString("mysql.password");
			int numConnections = props.getInt("mysql.numconnections");
			
			dataSource = DataSourceUtils.getMySQLDataSource(host, port, database, user, password, numConnections);
		}
	}
	
	@Override
	public List<Project> fetchAllActiveProjects() throws ProjectManagerException {
		Connection connection = getConnection();

		List<Project> projects = null;
		try {
			projects = fetchAllActiveProjects(connection);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
		
		return projects;
	}
	
	private List<Project> fetchAllActiveProjects(Connection connection) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner();
		
		ProjectResultHandler handler = new ProjectResultHandler();
		List<Project> projects = null;
		try {
			projects = runner.query(connection, ProjectResultHandler.SELECT_ALL_ACTIVE_PROJECTS, handler);
			
			for (Project project: projects) {
				List<Triple<String, Boolean, Permission>> permissions = fetchPermissionsForProject(connection, project);
				for (Triple<String, Boolean, Permission> entry: permissions) {
					if(entry.getSecond()) {
						project.setGroupPermission(entry.getFirst(), entry.getThird());
					}
					else {
						project.setUserPermission(entry.getFirst(), entry.getThird());
					}
				}
			}
		} catch (SQLException e) {
			throw new ProjectManagerException("Error retrieving all projects", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
		
		return projects;
	}
	
	@Override
	public Project fetchProjectById(int id) throws ProjectManagerException {
		Connection connection = getConnection();
		
		Project project = null;
		try {
			project = fetchProjectById(connection, id);
		} finally {
			DbUtils.closeQuietly(connection);
		}

		return project;
	}
	
	private Project fetchProjectById(Connection connection, int id) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner();
		// Fetch the project
		Project project = null;
		ProjectResultHandler handler = new ProjectResultHandler();
		try {
			List<Project> projects = runner.query(connection, ProjectResultHandler.SELECT_PROJECT_BY_ID, handler, id);
			if (projects.isEmpty()) {
				throw new ProjectManagerException("No active project with id " + id + " exists in db.");
			}
			
			project = projects.get(0);
		} catch (SQLException e) {
			logger.error(ProjectResultHandler.SELECT_PROJECT_BY_ID + " failed.");
			throw new ProjectManagerException("Query for existing project failed. Project " + id, e);
		}
		
		// Fetch the user permissions
		List<Triple<String, Boolean,Permission>> permissions = fetchPermissionsForProject(connection, project);
		
		for (Triple<String, Boolean, Permission> perm: permissions) {
			if (perm.getThird().toFlags() != 0) {
				if (perm.getSecond()) {
					project.setGroupPermission(perm.getFirst(), perm.getThird());
				}
				else {
					project.setUserPermission(perm.getFirst(), perm.getThird());
				}
			}
		}
		
		return project;
	}
	
	private List<Triple<String, Boolean, Permission>> fetchPermissionsForProject(Connection connection, Project project) throws ProjectManagerException {
		ProjectPermissionsResultHandler permHander = new ProjectPermissionsResultHandler();
		
		QueryRunner runner = new QueryRunner();
		List<Triple<String, Boolean,Permission>> permissions = null;
		try {
			permissions = runner.query(connection, ProjectPermissionsResultHandler.SELECT_PROJECT_PERMISSION, permHander, project.getId());
		} catch (SQLException e) {
			throw new ProjectManagerException("Query for permissions for " + project.getName() + " failed.", e);
		}
		
		return permissions;
	}
	
	/**
	 * Creates a Project in the db.
	 * 
	 * It will throw an exception if it finds an active project of the same name, or the SQL fails
	 */
	@Override
	public Project createNewProject(String name, String description, User creator) throws ProjectManagerException {
		Connection connection = getConnection();
		
		Project project;
		try {
			// No need to commit, since createNewProject should commit.
			project = createNewProject(connection, name, description, creator);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
		
		return project;
	}
	
	private synchronized Project createNewProject(Connection connection, String name, String description, User creator) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner();
		ProjectResultHandler handler = new ProjectResultHandler();
		
		// See if it exists first.
		try {
			List<Project> project = runner.query(connection, ProjectResultHandler.SELECT_ACTIVE_PROJECT_BY_NAME, handler, name);
			if (!project.isEmpty()) {
				throw new ProjectManagerException("Active project with name " + name + " already exists in db.");
			}
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Checking for existing project failed. " + name, e);
		}
		
		final String INSERT_PROJECT = "INSERT INTO projects ( name, active, modified_time, create_time, version, last_modified_by, description) values (?,?,?,?,?,?,?)";
		// Insert project
		try {
			long time = System.currentTimeMillis();
			int i = runner.update(connection, INSERT_PROJECT, name, true, time, time, null, creator.getUserId(), description);
			if (i == 0) {
				throw new ProjectManagerException("No projects have been inserted.");
			}
			connection.commit();

		} catch (SQLException e) {
			logger.error(INSERT_PROJECT + " failed.");
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw new ProjectManagerException("Insert project for existing project failed. " + name, e);
		}

		// Do another query to grab and return the project.
		Project project = null;
		try {
			List<Project> projects = runner.query(connection, ProjectResultHandler.SELECT_ACTIVE_PROJECT_BY_NAME, handler, name);
			if (projects.isEmpty()) {
				throw new ProjectManagerException("No active project with name " + name + " exists in db.");
			}
			else if (projects.size() > 1) {
				throw new ProjectManagerException("More than one active project " + name);
			}
			
			project = projects.get(0);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Checking for existing project failed. " + name, e);
		}

		return project;
	}
	
	@Override
	public void uploadProjectFile(Project project, int version, String filetype, String filename, File localFile, String uploader) throws ProjectManagerException {
		logger.info("Uploading to " + project.getName() + " version:" + version + " file:" + filename);
		Connection connection = getConnection();

		try {
			uploadProjectFile(connection, project, version, filetype, filename, localFile, uploader);
			connection.commit();
			logger.info("Commiting upload " + localFile.getName());
		}
		catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error getting DB connection.", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}

	private void uploadProjectFile(Connection connection, Project project, int version, String filetype, String filename, File localFile, String uploader) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner();
		long updateTime = System.currentTimeMillis();
		
		logger.info("Creating message digest for upload " + localFile.getName());
		byte[] md5 = null;
		try {
			md5 = Md5Hasher.md5Hash(localFile);
		} catch (IOException e) {
			throw new ProjectManagerException("Error getting md5 hash.", e);
		}
		
		logger.info("Md5 hash created");
		// Really... I doubt we'll get a > 2gig file. So int casting it is!
		byte[] buffer = new byte[CHUCK_SIZE];
		final String INSERT_PROJECT_FILES = "INSERT INTO project_files (project_id, version, chunk, size, file) values (?,?,?,?,?)";

		//int numChunks = (localFileSize / buffer.length) + 1;
		BufferedInputStream bufferedStream = null;
		int chunk = 0;
		try {
			bufferedStream = new BufferedInputStream(new FileInputStream(localFile));
			int size = bufferedStream.read(buffer);
			while (size >= 0) {
				logger.info("Read bytes for " + filename + " size:" + size);
				byte[] buf = buffer;
				if (size < buffer.length) {
					buf = Arrays.copyOfRange(buffer, 0, size);
				}
				try {
					logger.info("Running update for " + filename + " chunk " + chunk);
					runner.update(connection, INSERT_PROJECT_FILES, project.getId(), version, chunk, size, buf);
					logger.info("Finished update for " + filename + " chunk " + chunk);
				} catch (SQLException e) {
					IOUtils.closeQuietly(bufferedStream);
					throw new ProjectManagerException("Error chunking", e);
				}
				++chunk;
				
				size = bufferedStream.read(buffer);
			}
			
			bufferedStream.close();
		} catch (IOException e) {
			IOUtils.closeQuietly(bufferedStream);
			throw new ProjectManagerException("Error chunking file " + filename);
		}

		final String INSERT_PROJECT_VERSION =
				"INSERT INTO project_versions (project_id, version, upload_time, uploader, file_type, file_name, md5, num_chunks) values (?,?,?,?,?,?,?,?)";

		try {
			runner.update(connection, INSERT_PROJECT_VERSION, project.getId(), version, updateTime, uploader, filetype, filename, md5, chunk);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error updating project version " + project.getName(), e);
		}
	}

	@Override
	public ProjectFileHandler getUploadedFile(Project project, int version) throws ProjectManagerException {
		logger.info("Retrieving to " + project.getName() + " version:" + version);
		Connection connection = getConnection();
		ProjectFileHandler handler = null;
		try {
			handler = getUploadedFile(connection, project.getId(), version);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
		
		return handler;
	}
	
	@Override
	public ProjectFileHandler getUploadedFile(int projectId, int version) throws ProjectManagerException {
		logger.info("Retrieving to " + projectId + " version:" + version);
		Connection connection = getConnection();
		ProjectFileHandler handler = null;
		try {
			handler = getUploadedFile(connection, projectId, version);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
		
		return handler;
	}
	
	private ProjectFileHandler getUploadedFile(Connection connection, int projectId, int version) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner();
		ProjectVersionResultHandler pfHandler = new ProjectVersionResultHandler();
		
		List<ProjectFileHandler> projectFiles = null;
		try {
			projectFiles = runner.query(connection, ProjectVersionResultHandler.SELECT_PROJECT_VERSION, pfHandler, projectId, version);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Query for uploaded file for project id " + projectId + " failed.", e);
		}
		if (projectFiles == null || projectFiles.isEmpty()) {
			return null;
		}
		
		ProjectFileHandler projHandler = projectFiles.get(0);
		int numChunks = projHandler.getNumChunks();
		BufferedOutputStream bStream = null;
		File file = null;
		try {
			file = File.createTempFile(projHandler.getFileName(), String.valueOf(version), tempDir);
	
			bStream = new BufferedOutputStream(new FileOutputStream(file));
		}
		catch (IOException e) {
			IOUtils.closeQuietly(bStream);
			throw new ProjectManagerException("Error creating temp file for stream.");
		}
		
		int collect = 5;
		int fromChunk = 0;
		int toChunk = collect;
		do {
			ProjectFileChunkResultHandler chunkHandler = new ProjectFileChunkResultHandler();
			List<byte[]> data = null;
			try {
				data = runner.query(connection, ProjectFileChunkResultHandler.SELECT_PROJECT_CHUNKS_FILE, chunkHandler, projectId, version, fromChunk, toChunk);
			}
			catch(SQLException e) {
				logger.error(e);
				IOUtils.closeQuietly(bStream);
				throw new ProjectManagerException("Query for uploaded file for " + projectId + " failed.", e);
			}
			
			try {
				for (byte[] d : data) {
					bStream.write(d);
				}
			}
			catch (IOException e) {
				IOUtils.closeQuietly(bStream);
				throw new ProjectManagerException("Error writing file", e);
			}
			
			// Add all the bytes to the stream.
			fromChunk += collect;
			toChunk += collect;
		} while (fromChunk <= numChunks);
		IOUtils.closeQuietly(bStream);
		
		// Check md5.
		byte[] md5 = null;
		try {
			md5 = Md5Hasher.md5Hash(file);
		} catch (IOException e) {
			throw new ProjectManagerException("Error getting md5 hash.", e);
		}
		
		if (Arrays.equals(projHandler.getMd5Hash(), md5)) {
			logger.info("Md5 Hash is valid");
		}
		else {
			throw new ProjectManagerException("Md5 Hash failed on retrieval of file");
		}
		
		projHandler.setLocalFile(file);
		return projHandler;
	}
	
	@Override
	public void changeProjectVersion(Project project, int version, String user) throws ProjectManagerException {
		long timestamp = System.currentTimeMillis();
		QueryRunner runner = new QueryRunner(dataSource);
		try {
			final String UPDATE_PROJECT_VERSION = "UPDATE projects SET version=?,modified_time=?,last_modified_by=? WHERE id=?";
			
			runner.update(UPDATE_PROJECT_VERSION, version, timestamp, user, project.getId());
			project.setVersion(version);
			project.setLastModifiedTimestamp(timestamp);
			project.setLastModifiedUser(user);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error updating switching project version " + project.getName(), e);
		}
	}
	
	@Override
	public void updatePermission(Project project, String name, Permission perm, boolean isGroup) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		long updateTime = System.currentTimeMillis();
		final String INSERT_PROJECT_PERMISSION = 
				"INSERT INTO project_permissions (project_id, modified_time, name, permissions, isGroup) values (?,?,?,?,?)" +
				"ON DUPLICATE KEY UPDATE modified_time = VALUES(modified_time), permissions = VALUES(permissions)";

		try {
			runner.update(INSERT_PROJECT_PERMISSION, project.getId(), updateTime, name, perm.toFlags(), isGroup);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error updating project " + project.getName() + " permissions for " + name, e);
		}
		
		if (isGroup) {
			project.setGroupPermission(name, perm);
		}
		else {
			project.setUserPermission(name, perm);
		}
	}
	
	@Override
	public void removePermission(Project project, String name, boolean isGroup) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		final String DELETE_PROJECT_PERMISSION = "DELETE FROM project_permissions WHERE project_id=? AND name=? AND isGroup=?";
		
		try {
			runner.update(DELETE_PROJECT_PERMISSION, project.getId(), name, isGroup);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error deleting project " + project.getName() + " permissions for " + name, e);
		}
		
		if (isGroup) {
			project.removeGroupPermission(name);
		}
		else {
			project.removeUserPermission(name);
		}
	}
	
	@Override
	public void removeProject(Project project, String user) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		long updateTime = System.currentTimeMillis();
		final String UPDATE_INACTIVE_PROJECT = "UPDATE projects SET active=false,modified_time=?,last_modified_by=? WHERE id=?";
		try {
			runner.update(UPDATE_INACTIVE_PROJECT, updateTime, user, project.getId());
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error marking project " + project.getName() + " as inactive", e);
		}
	}

	@Override
	public boolean postEvent(Project project, EventType type, String user, String message) {
		QueryRunner runner = new QueryRunner(dataSource);
		
		final String INSERT_PROJECT_EVENTS = 
				"INSERT INTO project_events (project_id, event_type, event_time, username, message) values (?,?,?,?,?)";
		long updateTime = System.currentTimeMillis();
		try {
			runner.update(INSERT_PROJECT_EVENTS, project.getId(), type.getNumVal(), updateTime, user, message);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		
		return true;
	}

	/**
	 * Get all the logs for a given project
	 * 
	 * @param project
	 * @return
	 * @throws ProjectManagerException
	 */
	public List<ProjectLogEvent> getProjectEvents(Project project, int num, int skip) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		ProjectLogsResultHandler logHandler = new ProjectLogsResultHandler();
		List<ProjectLogEvent> events = null;
		try {
			events = runner.query(ProjectLogsResultHandler.SELECT_PROJECT_EVENTS_ORDER, logHandler, project.getId(), num, skip);
		} catch (SQLException e) {
			logger.error(e);
		}
		
		return events;
	}

	@Override
	public void updateDescription(Project project, String description, String user) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		final String UPDATE_PROJECT_DESCRIPTION =
				"UPDATE projects SET description=?,modified_time=?,last_modified_by=? WHERE id=?";
		long updateTime = System.currentTimeMillis();
		try {
			runner.update(UPDATE_PROJECT_DESCRIPTION, description, updateTime, user, project.getId());
			project.setDescription(description);
			project.setLastModifiedTimestamp(updateTime);
			project.setLastModifiedUser(user);
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error marking project " + project.getName() + " as inactive", e);
		}
	}

	@Override
	public int getLatestProjectVersion(Project project) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		IntHander handler = new IntHander();
		try {
			return runner.query(IntHander.SELECT_LATEST_VERSION, handler, project.getId());
		} catch (SQLException e) {
			logger.error(e);
			throw new ProjectManagerException("Error marking project " + project.getName() + " as inactive", e);
		}
	}
	
	@Override
	public void uploadFlows(Project project, int version, Collection<Flow> flows) throws ProjectManagerException {
		// We do one at a time instead of batch... because well, the batch could be large.
		logger.info("Uploading flows");
		Connection connection = getConnection();
		
		try {
			for (Flow flow: flows) {
				uploadFlow(connection, project, version, flow, defaultEncodingType);
			}
			connection.commit();
		}
		catch (IOException e) {
			throw new ProjectManagerException("Flow Upload failed.", e);
		}
		catch (SQLException e) {
			throw new ProjectManagerException("Flow Upload failed.", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}
	
	@Override
	public void uploadFlow(Project project, int version, Flow flow) throws ProjectManagerException {
		logger.info("Uploading flows");
		Connection connection = getConnection();
		
		try {
			uploadFlow(connection, project, version, flow, defaultEncodingType);
			connection.commit();
		}
		catch (IOException e) {
			throw new ProjectManagerException("Flow Upload failed.", e);
		} catch (SQLException e) {
			throw new ProjectManagerException("Flow Upload failed commit.", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}

	public EncodingType getDefaultEncodingType() {
		return defaultEncodingType;
	}

	public void setDefaultEncodingType(EncodingType defaultEncodingType) {
		this.defaultEncodingType = defaultEncodingType;
	}

	private void uploadFlow(Connection connection, Project project, int version, Flow flow, EncodingType encType) throws ProjectManagerException, IOException {
		QueryRunner runner = new QueryRunner();
		String json = JSONUtils.toJSON(flow.toObject());
		byte[] stringData = json.getBytes("UTF-8");
		byte[] data = stringData;

		logger.info("UTF-8 size:" + data.length);
		if (encType == EncodingType.GZIP) {
			data = GZIPUtils.gzipBytes(stringData);
		}
		
		logger.info("Flow upload " + flow.getId() + " is byte size " + data.length);
		final String INSERT_FLOW = "INSERT INTO project_flows (project_id, version, flow_id, modified_time, encoding_type, json) values (?,?,?,?,?,?)";
		try {
			runner.update(connection, INSERT_FLOW, project.getId(), version, flow.getId(), System.currentTimeMillis(), encType.getNumVal(), data );
		} catch (SQLException e) {
			throw new ProjectManagerException("Error inserting flow " + flow.getId(), e);
		}
	}
	
	@Override
	public Flow fetchFlow(Project project, String flowId) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		ProjectFlowsResultHandler handler = new ProjectFlowsResultHandler();
		
		try {
			List<Flow> flows = runner.query(ProjectFlowsResultHandler.SELECT_PROJECT_FLOW, handler, project.getId(), project.getVersion(), flowId);
			if (flows.isEmpty()) {
				return null;
			}
			else {
				return flows.get(0);
			}
		} catch (SQLException e) {
			throw new ProjectManagerException("Error fetching flow " + flowId, e);
		}
	}
	
	@Override
	public List<Flow> fetchAllProjectFlows(Project project) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		ProjectFlowsResultHandler handler = new ProjectFlowsResultHandler();
		
		List<Flow> flows = null;
		try {
			flows = runner.query(ProjectFlowsResultHandler.SELECT_ALL_PROJECT_FLOWS, handler, project.getId(), project.getVersion());
		} catch (SQLException e) {
			throw new ProjectManagerException("Error fetching flows from project " + project.getName() + " version " + project.getVersion(), e);
		}
		
		return flows;
	}

	@Override
	public void uploadProjectProperties(Project project, List<Props> properties) throws ProjectManagerException {
		Connection connection = getConnection();
		
		try {
			for (Props props: properties) {
				uploadProjectProperty(connection, project, props.getSource(), props);
			}
			connection.commit();
		} 
		catch (SQLException e) {
			throw new ProjectManagerException("Error uploading project property files", e);
		}
		catch (IOException e) {
			throw new ProjectManagerException("Error uploading project property files", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}

	@Override
	public void uploadProjectProperty(Project project, Props props) throws ProjectManagerException {
		Connection connection = getConnection();
		try {
			uploadProjectProperty(connection, project, props.getSource(), props);
			connection.commit();
		}
		catch (SQLException e) {
			throw new ProjectManagerException("Error uploading project property files", e);
		} 
		catch (IOException e) {
			throw new ProjectManagerException("Error uploading project property file", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}
	
	@Override
	public void updateProjectProperty(Project project, Props props) throws ProjectManagerException {
		Connection connection = getConnection();
		try {
			updateProjectProperty(connection, project, props.getSource(), props);
			connection.commit();
		}
		catch (SQLException e) {
			throw new ProjectManagerException("Error uploading project property files", e);
		} 
		catch (IOException e) {
			throw new ProjectManagerException("Error uploading project property file", e);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}
	
	private void updateProjectProperty(Connection connection, Project project, String name, Props props) throws ProjectManagerException, IOException {
		QueryRunner runner = new QueryRunner();
		final String UPDATE_PROPERTIES = "UPDATE project_properties SET property=? WHERE project_id=? AND version=? AND name=?";
		
		String propertyJSON = PropsUtils.toJSONString(props, true);
		byte[] data = propertyJSON.getBytes("UTF-8");
		logger.info("UTF-8 size:" + data.length);
		if (defaultEncodingType == EncodingType.GZIP) {
			data = GZIPUtils.gzipBytes(data);
		}

		try {
			runner.update(connection, UPDATE_PROPERTIES, data, project.getId(), project.getVersion(), name);
			connection.commit();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error updating property " + project.getName() + " version " + project.getVersion(), e);
		}
	}
	
	private void uploadProjectProperty(Connection connection, Project project, String name, Props props) throws ProjectManagerException, IOException {
		QueryRunner runner = new QueryRunner();
		final String INSERT_PROPERTIES = "INSERT INTO project_properties (project_id, version, name, modified_time, encoding_type, property) values (?,?,?,?,?,?)";
		
		String propertyJSON = PropsUtils.toJSONString(props, true);
		byte[] data = propertyJSON.getBytes("UTF-8");
		logger.info("UTF-8 size:" + data.length);
		if (defaultEncodingType == EncodingType.GZIP) {
			data = GZIPUtils.gzipBytes(data);
		}

		try {
			runner.update(connection, INSERT_PROPERTIES, project.getId(), project.getVersion(), name, System.currentTimeMillis(), defaultEncodingType.getNumVal(), data);
			connection.commit();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error uploading project properties " + name + " into " + project.getName() + " version " + project.getVersion(), e);
		}
	}

	@Override
	public Props fetchProjectProperty(int projectId, int projectVer, String propsName) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		ProjectPropertiesResultsHandler handler = new ProjectPropertiesResultsHandler();
		try {
			List<Pair<String, Props>> properties = 
					runner.query(ProjectPropertiesResultsHandler.SELECT_PROJECT_PROPERTY, handler,  projectId, projectVer, propsName);

			if (properties == null || properties.isEmpty()) {
				return null;
			}
			
			return properties.get(0).getSecond();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error fetching property " + propsName, e);
		}
	}
	
	@Override
	public Props fetchProjectProperty(Project project, String propsName) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		ProjectPropertiesResultsHandler handler = new ProjectPropertiesResultsHandler();
		try {
			List<Pair<String, Props>> properties = 
					runner.query(ProjectPropertiesResultsHandler.SELECT_PROJECT_PROPERTY, handler,  project.getId(), project.getVersion(), propsName);

			if (properties == null || properties.isEmpty()) {
				return null;
			}
			
			return properties.get(0).getSecond();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error fetching property " + propsName, e);
		}
	}
	
	@Override
	public void cleanOlderProjectVersion(int projectId, int version) throws ProjectManagerException {
		Connection connection = getConnection();
		
		try {
			cleanOlderProjectVersionFlows(connection, projectId, version);
			cleanOlderProjectVersionProperties(connection, projectId, version);
			cleanOlderProjectFiles(connection, projectId, version);
			cleanOlderProjectVersion(connection, projectId, version);
		}
		finally {
			DbUtils.closeQuietly(connection);
		}
	}
	
	private void cleanOlderProjectVersionFlows(Connection connection, int projectId, int version) throws ProjectManagerException {
		final String DELETE_FLOW = "DELETE FROM project_flows WHERE project_id=? AND version<?";
		QueryRunner runner = new QueryRunner();
		try {
			runner.update(connection, DELETE_FLOW, projectId, version);
			connection.commit();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error deleting project version flows " + projectId + ":" + version, e);
		}
	}
	
	private void cleanOlderProjectVersionProperties(Connection connection, int projectId, int version) throws ProjectManagerException {
		final String DELETE_PROPERTIES = "DELETE FROM project_properties WHERE project_id=? AND version<?";
		QueryRunner runner = new QueryRunner();
		try {
			runner.update(connection, DELETE_PROPERTIES,  projectId, version);
			connection.commit();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error deleting project version properties " + projectId + ":" + version, e);
		}
	}
	
	private void cleanOlderProjectFiles(Connection connection, int projectId, int version) throws ProjectManagerException {
		final String DELETE_PROJECT_FILES = "DELETE FROM project_files WHERE project_id=? AND version<?";
		QueryRunner runner = new QueryRunner();
		try {
			runner.update(connection, DELETE_PROJECT_FILES,  projectId, version);
			connection.commit();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error deleting project version files " + projectId + ":" + version, e);
		}
	}
	
	private void cleanOlderProjectVersion(Connection connection, int projectId, int version) throws ProjectManagerException {
		final String UPDATE_PROJECT_VERSIONS = "UPDATE project_versions SET num_chunks=0 WHERE project_id=? AND version<?";
		QueryRunner runner = new QueryRunner();
		try {
			runner.update(connection, UPDATE_PROJECT_VERSIONS,  projectId, version);
			connection.commit();
		} catch (SQLException e) {
			throw new ProjectManagerException("Error updating project version chunksize " + projectId + ":" + version, e);
		}
	}
	
	@Override
	public Map<String,Props> fetchProjectProperties(int projectId, int version) throws ProjectManagerException {
		QueryRunner runner = new QueryRunner(dataSource);
		
		ProjectPropertiesResultsHandler handler = new ProjectPropertiesResultsHandler();
		try {
			List<Pair<String, Props>> properties = 
					runner.query(ProjectPropertiesResultsHandler.SELECT_PROJECT_PROPERTIES, handler, projectId, version);

			if (properties == null || properties.isEmpty()) {
				return null;
			}
			
			HashMap<String, Props> props = new HashMap<String, Props>();
			for (Pair<String, Props> pair: properties) {
				props.put(pair.getFirst(), pair.getSecond());
			}
			return props;
		} catch (SQLException e) {
			throw new ProjectManagerException("Error fetching properties", e);
		}
	}
	
	private static class ProjectResultHandler implements ResultSetHandler<List<Project>> {
		private static String SELECT_PROJECT_BY_ID = 
				"SELECT id, name, active, modified_time, create_time, version, last_modified_by, description FROM projects WHERE id=?";
		
		private static String SELECT_ALL_ACTIVE_PROJECTS = 
				"SELECT id, name, active, modified_time, create_time, version, last_modified_by, description FROM projects WHERE active=true";
		
		private static String SELECT_ACTIVE_PROJECT_BY_NAME = 
				"SELECT id, name, active, modified_time, create_time, version, last_modified_by, description FROM projects WHERE name=? AND active=true";
		
		@Override
		public List<Project> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return Collections.<Project>emptyList();
			}
			
			ArrayList<Project> projects = new ArrayList<Project>();
			do {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				boolean active = rs.getBoolean(3);
				long modifiedTime = rs.getLong(4);
				long createTime = rs.getLong(5);
				int version = rs.getInt(6);
				String lastModifiedBy = rs.getString(7);
				String description = rs.getString(8);
				
				Project project = new Project(id, name);
				project.setActive(active);
				project.setLastModifiedTimestamp(modifiedTime);
				project.setCreateTimestamp(createTime);
				project.setVersion(version);
				project.setLastModifiedUser(lastModifiedBy);
				project.setDescription(description);
				
				projects.add(project);
			} while (rs.next());
			
			return projects;
		}
	}
	
	private static class ProjectPermissionsResultHandler implements ResultSetHandler<List<Triple<String, Boolean, Permission>>> {
		private static String SELECT_PROJECT_PERMISSION = 
				"SELECT project_id, modified_time, name, permissions, isGroup FROM project_permissions WHERE project_id=?";
		
		@Override
		public List<Triple<String, Boolean, Permission>> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return Collections.<Triple<String, Boolean, Permission>>emptyList();
			}
			
			ArrayList<Triple<String, Boolean, Permission>> permissions = new ArrayList<Triple<String, Boolean, Permission>>();
			do {
				//int project_id = rs.getInt(1);
				//long modifiedTime = rs.getLong(2);
				String username = rs.getString(3);
				int permissionFlag = rs.getInt(4);
				boolean val = rs.getBoolean(5);
				
				Permission perm = new Permission(permissionFlag);
				permissions.add(new Triple<String, Boolean, Permission>(username, val, perm));
			} while (rs.next());
			
			return permissions;
		}
	}

	private static class ProjectFlowsResultHandler implements ResultSetHandler<List<Flow>> {
		private static String SELECT_PROJECT_FLOW = 
				"SELECT project_id, version, flow_id, modified_time, encoding_type, json FROM project_flows WHERE project_id=? AND version=? AND flow_id=?";
		
		private static String SELECT_ALL_PROJECT_FLOWS = 
				"SELECT project_id, version, flow_id, modified_time, encoding_type, json FROM project_flows WHERE project_id=? AND version=?";
		
		@Override
		public List<Flow> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return Collections.<Flow>emptyList();
			}
			
			ArrayList<Flow> flows = new ArrayList<Flow>();
			do {
				//int projectId = rs.getInt(1);
				//int version = rs.getInt(2);
				String flowId = rs.getString(3);
				//long modifiedTime = rs.getLong(4);
				int encodingType = rs.getInt(5);
				byte[] dataBytes = rs.getBytes(6);
				
				if (dataBytes == null) {
					continue;
				}
				
				EncodingType encType = EncodingType.fromInteger(encodingType);
				
				Object flowObj = null;
				try {
					// Convoluted way to inflate strings. Should find common package or helper function.
					if (encType == EncodingType.GZIP) {
						// Decompress the sucker.
						String jsonString = GZIPUtils.unGzipString(dataBytes, "UTF-8");
						flowObj = JSONUtils.parseJSONFromString(jsonString);
					}
					else {
						String jsonString = new String(dataBytes, "UTF-8");
						flowObj = JSONUtils.parseJSONFromString(jsonString);
					}
					
					Flow flow = Flow.flowFromObject(flowObj);
					flows.add(flow);
				} catch (IOException e) {
					throw new SQLException("Error retrieving flow data " + flowId, e);
				}
				
			} while (rs.next());
			
			return flows;
		}
	}
	
	private static class ProjectPropertiesResultsHandler implements ResultSetHandler<List<Pair<String, Props>>> {
		private static String SELECT_PROJECT_PROPERTY =
				"SELECT project_id, version, name, modified_time, encoding_type, property FROM project_properties WHERE project_id=? AND version=? AND name=?";

		private static String SELECT_PROJECT_PROPERTIES =
				"SELECT project_id, version, name, modified_time, encoding_type, property FROM project_properties WHERE project_id=? AND version=?";
		
		@Override
		public List<Pair<String, Props>> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return Collections.<Pair<String, Props>>emptyList();
			}
			
			List<Pair<String, Props>> properties = new ArrayList<Pair<String, Props>>();
			do {
				//int projectId = rs.getInt(1);
				//int version = rs.getInt(2);
				String name = rs.getString(3);
				//long modifiedTime = rs.getLong(4);
				int eventType = rs.getInt(5);
				byte[] dataBytes = rs.getBytes(6);
				
				EncodingType encType = EncodingType.fromInteger(eventType);
				String propertyString = null;
				
				try {
					if (encType == EncodingType.GZIP) {
						// Decompress the sucker.
						propertyString = GZIPUtils.unGzipString(dataBytes, "UTF-8");
					}
					else {
						propertyString = new String(dataBytes, "UTF-8");
					}
					
					Props props = PropsUtils.fromJSONString(propertyString);
					props.setSource(name);
					properties.add(new Pair<String, Props>(name, props));
				} catch (IOException e) {
					throw new SQLException(e);
				}
			} while (rs.next());
			
			return properties;
		}
	}
	
	private static class ProjectLogsResultHandler implements ResultSetHandler<List<ProjectLogEvent>> {
		private static String SELECT_PROJECT_EVENTS_ORDER =
				"SELECT project_id, event_type, event_time, username, message FROM project_events WHERE project_id=? ORDER BY event_time DESC LIMIT ? OFFSET ?";
		
		@Override
		public List<ProjectLogEvent> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return Collections.<ProjectLogEvent>emptyList();
			}
			
			ArrayList<ProjectLogEvent> events = new ArrayList<ProjectLogEvent>();
			do {
				int projectId = rs.getInt(1);
				int eventType = rs.getInt(2);
				long eventTime = rs.getLong(3);
				String username = rs.getString(4);
				String message = rs.getString(5);
				
				ProjectLogEvent event = new ProjectLogEvent(projectId, EventType.fromInteger(eventType), eventTime, username, message);
				events.add(event);
			} while (rs.next());
			
			return events;
		}
	}
	
	private static class ProjectFileChunkResultHandler implements ResultSetHandler<List<byte[]>> {
		private static String SELECT_PROJECT_CHUNKS_FILE = "SELECT project_id, version, chunk, size, file FROM project_files WHERE project_id=? AND version=? AND chunk >= ? AND chunk < ? ORDER BY chunk ASC";
		
		@Override
		public List<byte[]> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return Collections.<byte[]>emptyList();
			}

			ArrayList<byte[]> data = new ArrayList<byte[]>();
			do {
//				int project_id = rs.getInt(1);
//				int version = rs.getInt(2);
//				int chunk = rs.getInt(3);
//				int size = rs.getInt(4);
				byte[] bytes = rs.getBytes(5);
				
				data.add(bytes);
			} while (rs.next());
			
			return data;
		}
		
	}
	
	private static class ProjectVersionResultHandler implements ResultSetHandler<List<ProjectFileHandler>> {
		private static String SELECT_PROJECT_VERSION = "SELECT project_id, version, upload_time, uploader, file_type, file_name, md5, num_chunks FROM project_versions WHERE project_id=? AND version=?";
		//private static String SELECT_ALL_PER_PROJECT = "SELECT project_id, version, upload_time, uploader, file_type, file_name, md5, num_chunks FROM project_versions WHERE project_id=?";
		
		@Override
		public List<ProjectFileHandler> handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return null;
			}
			
			List<ProjectFileHandler> handlers = new ArrayList<ProjectFileHandler>();
			do {
				int projectId = rs.getInt(1);
				int version = rs.getInt(2);
				long uploadTime = rs.getLong(3);
				String uploader = rs.getString(4);
				String fileType = rs.getString(5);
				String fileName = rs.getString(6);
				byte[] md5 = rs.getBytes(7);
				int numChunks = rs.getInt(8);
				
				ProjectFileHandler handler = new ProjectFileHandler(
					projectId, version, uploadTime, uploader, fileType, fileName, numChunks, md5
				);
				
				handlers.add(handler);
			} while (rs.next());
			
			return handlers;
		}
	}

	private static class IntHander implements ResultSetHandler<Integer> {
		private static String SELECT_LATEST_VERSION = "SELECT MAX(version) FROM project_versions WHERE project_id=?";
		
		@Override
		public Integer handle(ResultSet rs) throws SQLException {
			if (!rs.next()) {
				return 0;
			}
			
			return rs.getInt(1);
		}
	}
	
	private Connection getConnection() throws ProjectManagerException {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
		} catch (Exception e) {
			DbUtils.closeQuietly(connection);
			throw new ProjectManagerException("Error getting DB connection.", e);
		}
		
		return connection;
	}

}
	