path: root/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbImage.java

package org.openslx.bwlp.sat.database.mappers;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import org.apache.log4j.Logger;
import org.openslx.bwlp.sat.database.Database;
import org.openslx.bwlp.sat.database.MysqlConnection;
import org.openslx.bwlp.sat.database.MysqlStatement;
import org.openslx.bwlp.sat.database.Paginator;
import org.openslx.bwlp.thrift.iface.ImageBaseWrite;
import org.openslx.bwlp.thrift.iface.ImageDetailsRead;
import org.openslx.bwlp.thrift.iface.ImagePermissions;
import org.openslx.bwlp.thrift.iface.ImageSummaryRead;
import org.openslx.bwlp.thrift.iface.ImageVersionDetails;
import org.openslx.bwlp.thrift.iface.ImageVersionWrite;
import org.openslx.bwlp.thrift.iface.ShareMode;
import org.openslx.bwlp.thrift.iface.TNotFoundException;
import org.openslx.bwlp.thrift.iface.UserInfo;
import org.openslx.filetransfer.util.ChunkList;

public class DbImage {

	private static final Logger LOGGER = Logger.getLogger(DbImage.class);

	/**
	 * Get list of all images visible to the given user, optionally filtered by
	 * the given list of tags.
	 * 
	 * @param user Instance of {@link UserInfo} representing the user in
	 *            question
	 * @param tagSearch list of tags an image must have to be included in the
	 *            list.
	 * @param page page to return
	 * @return {@link List} of {@link ImageSummaryRead}
	 * @throws SQLException
	 */
	public static List<ImageSummaryRead> getAllVisible(UserInfo user, List<String> tagSearch, int page)
			throws SQLException {
		// TODO: Implement tag search functionality
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("SELECT"
					+ " i.imagebaseid, i.currentversionid, i.latestversionid, i.displayname,"
					+ " i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid,"
					+ " i.sharemode, i.istemplate, i.canlinkdefault, i.candownloaddefault,"
					+ " i.caneditdefault, i.canadmindefault,"
					+ " cur.expiretime, cur.filesize, cur.isenabled, cur.isrestricted, cur.isvalid,"
					+ " lat.uploaderid, lat.isprocessed,"
					+ " perm.canlink, perm.candownload, perm.canedit, perm.canadmin"
					+ " FROM imagebase i"
					+ " LEFT JOIN imageversion cur ON (cur.imageversionid = i.currentversionid)"
					+ " LEFT JOIN imageversion lat ON (lat.imageversionid = i.latestversionid)"
					+ " LEFT JOIN imagepermission perm ON (i.imagebaseid = perm.imagebaseid AND perm.userid = :userid)"
					+ Paginator.limitStatement(page));
			stmt.setString("userid", user.userId);
			ResultSet rs = stmt.executeQuery();
			List<ImageSummaryRead> list = new ArrayList<>(100);
			while (rs.next()) {
				list.add(resultSetToSummary(rs));
			}
			return list;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.getAllVisible()", e);
			throw e;
		}
	}

	public static ImageDetailsRead getImageDetails(UserInfo user, String imageBaseId)
			throws TNotFoundException, SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("SELECT i.imagebaseid, i.currentversionid, i.latestversionid,"
					+ " i.displayname, i.description, i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid, i.updaterid,"
					+ " i.sharemode, i.istemplate,"
					+ " i.canlinkdefault, i.candownloaddefault, i.caneditdefault, i.canadmindefault,"
					+ " perm.canlink, perm.candownload, perm.canedit, perm.canadmin"
					+ " FROM imagebase i"
					+ " LEFT JOIN imagepermission perm ON (i.imagebaseid = perm.imagebaseid AND perm.userid = :userid)"
					+ " WHERE i.imagebaseid = :imagebaseid");
			stmt.setString("userid", user.userId);
			stmt.setString("imagebaseid", imageBaseId);
			ResultSet rs = stmt.executeQuery();
			if (!rs.next())
				throw new TNotFoundException();
			// Exists:
			List<String> tags = DbSoftwareTag.getImageTags(connection, imageBaseId);
			List<ImageVersionDetails> versions = getImageVersions(connection, imageBaseId);
			ImagePermissions defaultPermissions = DbImagePermissions.fromResultSetDefault(rs);
			ImageDetailsRead image = new ImageDetailsRead(rs.getString("imagebaseid"),
					rs.getString("currentversionid"), rs.getString("latestversionid"), versions,
					rs.getString("displayname"), rs.getString("description"), tags, rs.getInt("osid"),
					rs.getString("virtid"), rs.getLong("createtime"), rs.getLong("updatetime"),
					rs.getString("ownerid"), rs.getString("updaterid"),
					toShareMode(rs.getString("sharemode")), rs.getByte("istemplate") != 0, defaultPermissions);
			return image;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.getImageDetails()", e);
			throw e;
		}
	}

	/**
	 * Private helper to create an {@link ImageSummaryRead} instance from a
	 * {@link ResultSet}
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private static ImageSummaryRead resultSetToSummary(ResultSet rs) throws SQLException {
		ImagePermissions defaultPermissions = DbImagePermissions.fromResultSetDefault(rs);
		ImageSummaryRead entry = new ImageSummaryRead(rs.getString("imagebaseid"),
				rs.getString("currentversionid"), rs.getString("latestversionid"),
				rs.getString("displayname"), rs.getInt("osid"), rs.getString("virtid"),
				rs.getLong("createtime"), rs.getLong("updatetime"), rs.getLong("expiretime"),
				rs.getString("ownerid"), rs.getString("uploaderid"), toShareMode(rs.getString("sharemode")),
				rs.getLong("filesize"), rs.getByte("isrestricted") != 0, rs.getByte("isvalid") != 0,
				rs.getByte("isprocessed") != 0, rs.getByte("istemplate") != 0, defaultPermissions);
		entry.userPermissions = DbImagePermissions.fromResultSetUser(rs);
		return entry;
	}

	/**
	 * Get summary about an image by its base id.
	 * 
	 * @param user
	 * @param imageBaseId
	 * @return
	 * @throws SQLException
	 * @throws TNotFoundException
	 */
	public static ImageSummaryRead getImageSummary(UserInfo user, String imageBaseId) throws SQLException,
			TNotFoundException {
		try (MysqlConnection connection = Database.getConnection()) {
			return getImageSummary(connection, user, imageBaseId);
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.getImageSummary()", e);
			throw e;
		}
	}

	protected static ImageSummaryRead getImageSummary(MysqlConnection connection, UserInfo user,
			String imageBaseId) throws SQLException, TNotFoundException {
		MysqlStatement stmt = connection.prepareStatement("SELECT"
				+ " i.imagebaseid, i.currentversionid, i.latestversionid, i.displayname,"
				+ " i.osid, i.virtid, i.createtime, i.updatetime, i.ownerid,"
				+ " i.sharemode, i.istemplate, i.canlinkdefault, i.candownloaddefault,"
				+ " i.caneditdefault, i.canadmindefault,"
				+ " cur.expiretime, cur.filesize, cur.isenabled, cur.isrestricted, cur.isvalid,"
				+ " lat.uploaderid, lat.isprocessed,"
				+ " perm.canlink, perm.candownload, perm.canedit, perm.canadmin"
				+ " FROM imagebase i"
				+ " LEFT JOIN imageversion cur ON (cur.imageversionid = i.currentversionid)"
				+ " LEFT JOIN imageversion lat ON (lat.imageversionid = i.latestversionid)"
				+ " LEFT JOIN imagepermission perm ON (i.imagebaseid = perm.imagebaseid AND perm.userid = :userid)"
				+ " WHERE i.imagebaseid = :imagebaseid");
		stmt.setString("userid", user.userId);
		stmt.setString("imagebaseid", imageBaseId);
		ResultSet rs = stmt.executeQuery();
		if (!rs.next()) {
			LOGGER.info("Image " + imageBaseId + " not found");
			throw new TNotFoundException();
		} else {
			LOGGER.info("Image " + imageBaseId + " found");
		}
		return resultSetToSummary(rs);
	}

	private static List<ImageVersionDetails> getImageVersions(MysqlConnection connection, String imageBaseId)
			throws SQLException {
		List<ImageVersionDetails> versionList = new ArrayList<>();
		MysqlStatement stmt = connection.prepareStatement("SELECT"
				+ " imageversionid, createtime, expiretime, filesize, uploaderid, isenabled,"
				+ " isrestricted, isvalid, isprocessed" + " FROM imageversion"
				+ " WHERE imagebaseid = :imagebaseid");
		stmt.setString("imagebaseid", imageBaseId);
		ResultSet rs = stmt.executeQuery();
		while (rs.next()) {
			String imageVersionId = rs.getString("imageversionid");
			versionList.add(new ImageVersionDetails(imageVersionId, rs.getLong("createtime"),
					rs.getLong("expiretime"), rs.getLong("filesize"), rs.getString("uploaderid"),
					rs.getByte("isenabled") != 0, rs.getByte("isrestricted") != 0,
					rs.getByte("isvalid") != 0, rs.getByte("isprocessed") != 0,
					DbSoftwareTag.getImageVersionSoftwareList(connection, imageVersionId)));
		}
		stmt.close();
		return versionList;
	}

	/**
	 * Create new row in the imagebase table.
	 * 
	 * @param user the user the image will belong to
	 * @param imageName name of the image to be created
	 * @return UUID of the newly created image
	 * @throws SQLException
	 */
	public static String createImage(UserInfo user, String imageName) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("INSERT INTO imagebase"
					+ " (imagebaseid, displayname, createtime, updatetime, ownerid, updaterid, sharemode,"
					+ " istemplate, canlinkdefault, candownloaddefault, caneditdefault, canadmindefault)"
					+ " VALUES"
					+ " (:baseid, :name, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), :userid, :userid, 'LOCAL',"
					+ " 0, 0, 0, 0, 0)");
			String imageUuid = UUID.randomUUID().toString();
			stmt.setString("baseid", imageUuid);
			stmt.setString("name", imageName);
			stmt.setString("userid", user.userId);
			stmt.executeUpdate();
			connection.commit();
			LOGGER.info("Created image '" + imageUuid + "'");
			return imageUuid;
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.createImage()", e);
			throw e;
		}
	}

	public static void updateImageMetadata(UserInfo user, String imageBaseId, ImageBaseWrite image)
			throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			/*
			1: string imageName,
			2: string description,
			3: i32 osId,
			4: bool isTemplate,
			5: ImagePermissions defaultPermissions,
			6: ShareMode shareMode,
			7: optional UUID ownerId,
			*/
			MysqlStatement stmt = connection.prepareStatement("UPDATE imagebase"
					+ " SET displayname = :imagename, description = :description,"
					+ " osid = :osid, virtid = :virtid, istemplate = :istemplate, canlinkdefault = :canlink,"
					+ " candownloaddefault = :candownload, caneditdefault = :canedit,"
					+ " updaterid = :updaterid, updatetime = UNIX_TIMESTAMP(),"
					+ " canadmindefault = :canadmin" + " WHERE imagebaseid = :baseid");
			stmt.setString("baseid", imageBaseId);
			stmt.setString("imagename", image.imageName);
			stmt.setString("description", image.description);
			stmt.setInt("osid", image.osId);
			stmt.setString("virtid", image.virtId);
			stmt.setBoolean("istemplate", image.isTemplate);
			stmt.setBoolean("canlink", image.defaultPermissions.link);
			stmt.setBoolean("candownload", image.defaultPermissions.download);
			stmt.setBoolean("canedit", image.defaultPermissions.edit);
			stmt.setBoolean("canadmin", image.defaultPermissions.admin);
			stmt.setString("updaterid", user.userId);
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.updateImageMetadata()", e);
			throw e;
		}
	}

	public static void setImageOwner(String imageBaseId, String newOwnerId) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("UPDATE imagebase"
					+ " SET ownerid = :ownerid WHERE imagebaseid = :baseid");
			stmt.setString("ownerid", newOwnerId);
			stmt.setString("baseid", imageBaseId);
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.setImageOwner()", e);
			throw e;
		}
	}

	/**
	 * Get the UUID of the image base belonging to the given image version UUID.
	 * Returns <code>null</code> if the UUID does not exist.
	 * 
	 * @param imageVersionId
	 * @return
	 * @throws SQLException
	 */
	public static String getBaseIdForVersionId(String imageVersionId) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			return getBaseIdForVersionId(connection, imageVersionId);
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.getBaseIdForVersionId()", e);
			throw e;
		}
	}

	/**
	 * Get the UUID of the image base belonging to the given image version UUID.
	 * Returns <code>null</code> if the UUID does not exist.
	 * 
	 * @param imageVersionId
	 * @return
	 * @throws SQLException
	 */
	protected static String getBaseIdForVersionId(MysqlConnection connection, String imageVersionId)
			throws SQLException {
		MysqlStatement stmt = connection.prepareStatement("SELECT imagebaseid FROM imageversion"
				+ " WHERE imageversionid = :imageversionid LIMIT 1");
		stmt.setString("imageversionid", imageVersionId);
		ResultSet rs = stmt.executeQuery();
		if (!rs.next())
			return null;
		return rs.getString("imagebaseid");
	}

	private static ShareMode toShareMode(String string) {
		return ShareMode.valueOf(string);
	}

	/**
	 * Update meta data of a specific image version.
	 * 
	 * @param user user doing the edit
	 * @param imageVersionId UUID of image version
	 * @param image meta data to set
	 * @throws SQLException
	 */
	public static void updateImageVersion(UserInfo user, String imageVersionId, ImageVersionWrite image)
			throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("UPDATE imageversion v, imagebase b SET"
					+ " v.isenabled = :isenabled, v.isrestricted = :isrestricted,"
					+ " b.updaterid = :userid, b.updatetime = UNIX_TIMESTAMP()"
					+ " WHERE v.imageversionid = :versionid AND v.imagebaseid = b.imagebaseid");
			stmt.setString("versionid", imageVersionId);
			stmt.setString("userid", user.userId);
			stmt.setBoolean("isenabled", image.isEnabled);
			stmt.setBoolean("isrestricted", image.isRestricted);
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.updateImageVersion()", e);
			throw e;
		}
	}

	/**
	 * Mark given image for deletion. The image is marked for deletion by
	 * setting the expire timestamp to the current date, and by setting the
	 * image disabled and invalid. Next time the cleanup task runs, the image
	 * will be deleted.
	 * 
	 * @param imageVersionId UUID of image version to delete
	 * @throws SQLException
	 */
	public static void markForDeletion(String imageVersionId) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("UPDATE imageversion SET"
					+ " expiretime = UNIX_TIMESTAMP() - 1, isenabled = 0, isvalid = 0"
					+ " WHERE imageversionid = :versionid");
			stmt.setString("versionid", imageVersionId);
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.markForDeletion()", e);
			throw e;
		}
	}

	public static void setShareMode(String imageBaseId, ImageBaseWrite newData) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement stmt = connection.prepareStatement("UPDATE imagebase"
					+ " SET sharemode = :sharemode WHERE imagebaseid = :baseid LIMIT 1");
			stmt.setString("baseid", imageBaseId);
			stmt.setString("sharemode", newData.shareMode.toString());
			stmt.executeUpdate();
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.setShareMode()", e);
			throw e;
		}
	}

	public static void createImageVersion(String imageBaseId, String imageVersionId, UserInfo owner, long fileSize, String filePath, ImageVersionWrite versionSettings, ChunkList chunks) throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			final long nowSecs = System.currentTimeMillis() / 1000;
			MysqlStatement stmt = connection.prepareStatement("INSERT INTO imageversion"
					+ " (imageversionid, imagebaseid, createtime, expiretime, filesize, filepath, uploaderid,"
					+ "  isenabled, isrestricted, isvalid, isprocessed, mastersha1, virtualizerconfig)"
					+ "         VALUES                     "
					+ " (:imageversionid, :imagebaseid, :createtime, :expiretime, :filesize, :filepath,"
					+ "  :uploaderid, :isenabled, :isrestricted, :isvalid, :isprocessed, :mastersha1, :virtualizerconfig)");
			stmt.setString("imageversionid", imageVersionId);
			stmt.setString("imagebaseid", imageBaseId);
			stmt.setLong("createtime", nowSecs);
			stmt.setLong("expiretime", nowSecs + 86400 * 365); // TODO: Config!
			stmt.setLong("filesize", fileSize);
			stmt.setString("filepath", filePath);
			stmt.setString("uploaderid", owner.userId);
			stmt.setBoolean("isenabled", versionSettings == null ? false : versionSettings.isEnabled);
			stmt.setBoolean("isrestricted", versionSettings == null ? false : versionSettings.isRestricted);
			stmt.setBoolean("isvalid", true); // TODO
			stmt.setBoolean("isprocessed", false);
			stmt.setBinary("mastersha1", null); // TODO
			stmt.setString("virtualizerconfig", null); // TODO
			stmt.executeUpdate();
			// TODO: Write chunk hashes to DB
			connection.commit();
		} catch (SQLException e) {
			LOGGER.error("Query failed in DbImage.createImageVersion()", e);
			throw e;
		}
	}

}