package org.openslx.bwlp.sat.database;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

public class Updater {

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

	public static void updateDatabase() throws SQLException {
		addLocationPrivateField();
		addLectureLocationMapTable();
		addHasUsbAccessField();
		addLogTable();
		fixEmailFieldLength();
	}

	private static void addLectureLocationMapTable() throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement tablesStmt = connection.prepareStatement("SHOW TABLES");
			ResultSet tables = tablesStmt.executeQuery();
			while (tables.next()) {
				if (tables.getString(1).equals("lecture_x_location")) {
					return; // Table exists, don't do anything
				}
			}
			// Add table
			MysqlStatement tableAddStmt = connection.prepareStatement("CREATE TABLE `lecture_x_location` ("
					+ " `lectureid` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,"
					+ " `locationid` int(11) NOT NULL,"
					+ " PRIMARY KEY (`lectureid`,`locationid`), KEY locationid (locationid)"
					+ " ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
			tableAddStmt.executeUpdate();
			// Add constraint
			MysqlStatement constraintStmt = connection.prepareStatement("ALTER TABLE `lecture_x_location`"
					+ " ADD FOREIGN KEY ( `lectureid` ) REFERENCES `sat`.`lecture` (`lectureid`)"
					+ " ON DELETE CASCADE ON UPDATE CASCADE");
			constraintStmt.executeUpdate();
			connection.commit();
			LOGGER.info("Updated database: Added lecture-location mapping table");
		} catch (SQLException e) {
			LOGGER.error("Query failed in Updater.addLectureLocationMapTable()", e);
			throw e;
		}
	}

	private static void addLocationPrivateField() throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement checkStmt = connection.prepareStatement("DESCRIBE lecture");
			ResultSet cols = checkStmt.executeQuery();
			while (cols.next()) {
				if (cols.getString("Field").equals("islocationprivate")) {
					return; // Field exists, don't do anything
				}
			}
			// Add field to table
			MysqlStatement columnAddStmt = connection.prepareStatement("ALTER TABLE lecture"
					+ " ADD islocationprivate TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER isprivate");
			columnAddStmt.executeUpdate();
			connection.commit();
			LOGGER.info("Updated database: Added is location private field in lecture table");
		} catch (SQLException e) {
			LOGGER.error("Query failed in Updater.addLocationPrivateField()", e);
			throw e;
		}
	}

	private static void addHasUsbAccessField() throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement checkStmt = connection.prepareStatement("DESCRIBE lecture");
			ResultSet cols = checkStmt.executeQuery();
			while (cols.next()) {
				if (cols.getString("Field").equals("hasusbaccess")) {
					return; // Field exists, don't do anything
				}
			}
			// Add field to table
			MysqlStatement columnAddStmt = connection.prepareStatement("ALTER TABLE lecture"
					+ " ADD hasusbaccess TINYINT(1) UNSIGNED NOT NULL DEFAULT 1 AFTER hasinternetaccess");
			columnAddStmt.executeUpdate();
			connection.commit();
			LOGGER.info("Updated database: Added 'hasusbaccess' private field in lecture table");
		} catch (SQLException e) {
			LOGGER.error("Query failed in Updater.addHasUsbAccessField()", e);
			throw e;
		}
	}

	private static void addLogTable() throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement tablesStmt = connection.prepareStatement("SHOW TABLES");
			ResultSet tables = tablesStmt.executeQuery();
			while (tables.next()) {
				if (tables.getString(1).equals("actionlog")) {
					return; // Table exists, don't do anything
				}
			}
			// Add table
			MysqlStatement tableAddStmt = connection.prepareStatement("CREATE TABLE `actionlog` ("
					+ " `actionid` int(11) NOT NULL AUTO_INCREMENT,"
					+ " `dateline` bigint(20) NOT NULL,"
					+ " `userid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,"
					+ " `targetid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,"
					+ " `description` varchar(500) NOT NULL,"
					+ " PRIMARY KEY (`actionid`),"
					+ " KEY userid (userid, dateline),"
					+ " KEY targetid (targetid, dateline),"
					+ " KEY dateline (dateline)"
					+ " ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
			tableAddStmt.executeUpdate();
			// Add constraint
			MysqlStatement constraintStmt = connection.prepareStatement("ALTER TABLE `actionlog`"
					+ " ADD FOREIGN KEY ( `userid` ) REFERENCES `sat`.`user` (`userid`)"
					+ " ON DELETE SET NULL ON UPDATE CASCADE");
			constraintStmt.executeUpdate();
			connection.commit();
			LOGGER.info("Updated database: Added actionlog table");
		} catch (SQLException e) {
			LOGGER.error("Query failed in Updater.addLogTable()", e);
			throw e;
		}
	}

	/**
	 * Make email field longer. Was 50 chars, which is not enough in rare cases
	 * :)
	 */
	private static void fixEmailFieldLength() throws SQLException {
		try (MysqlConnection connection = Database.getConnection()) {
			MysqlStatement checkStmt = connection.prepareStatement("DESCRIBE user");
			ResultSet cols = checkStmt.executeQuery();
			while (cols.next()) {
				if (cols.getString("Field").equals("email")
						&& "varchar(100)".equalsIgnoreCase(cols.getString("Type"))) {
					return; // Field exists and has proper type, don't do anything
				}
			}
			MysqlStatement upStmt = connection.prepareStatement("ALTER TABLE user CHANGE email"
					+ " email VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL");
			upStmt.executeUpdate();
			connection.commit();
			LOGGER.info("Updated database: Made email field longer");
		} catch (SQLException e) {
			LOGGER.error("Query failed in Updater.fixEmailFieldLength()", e);
			throw e;
		}
	}
}