package de.ugoe.cs.swe.exercises.misc;

import java.sql.Connection;
import java.sql.SQLException;

import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import org.eclipse.swt.widgets.Shell;

import de.ugoe.cs.swe.exercises.exercise.Exercise;
import de.ugoe.cs.swe.exercises.settings.*;

public abstract class Model {
	private static SettingsDialog mysqlSettings = new SettingsDialog(new Shell());
	public static int UNDEFINED = -1;
	private int id = Model.UNDEFINED;
	private static Connection connection;

	protected abstract String getTableName();
	
	public static class DoesNotExistException extends RuntimeException {
		private static final long serialVersionUID = 1L;

		public DoesNotExistException() {
			super("The model entry doesn't exist in the database, yet.");
		}

		public DoesNotExistException(String message) {
			super(message);
		}
	}

	public static Connection initialize(boolean test) {
		// connect to DB
		// -Djavax.net.ssl.trustStorePassword=datenbank -Djavax.net.ssl.trustStore=${project_loc}/keystore
		System.setProperty("javax.net.ssl.trustStorePassword", mysqlSettings.getMySQLPassword());
        String path = Model.class.getProtectionDomain().getCodeSource().getLocation().getPath();
        if (path.endsWith("bin/"))
        	path = path.replaceFirst("bin/$", "");
        path += "keystore";
        System.setProperty("javax.net.ssl.trustStore", path);

		if (test == true) {
			try {
				System.out.println("Connecting to test db");
				// Step 1: Load the JDBC driver.
				Class.forName("com.mysql.jdbc.Driver").newInstance();

				// Step 2: Establish the connection to the database.
				//System.out.println("jdbc:mysql://" + mysqlSettings.getMySQLHost() + "/" + mysqlSettings.getMySQLDb() + "test?useSSL="+mysqlSettings.isMySQLcheckSSL());
				String url = "jdbc:mysql://" + mysqlSettings.getMySQLHost() + "/" + mysqlSettings.getMySQLDb() + "test";//?useSSL="+mysqlSettings.isMySQLcheckSSL();

				Connection conn = DriverManager.getConnection(url, mysqlSettings.getMySQLUsername(),
						mysqlSettings.getMySQLPassword());
				System.out.println("Connection successful");
				//url.wait(500);
				return conn;
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			try {
				System.out.println("Connecting to production db");
				// Step 1: Load the JDBC driver.
				Class.forName("com.mysql.jdbc.Driver").newInstance();

				// Step 2: Establish the connection to the database.
				String url = "jdbc:mysql://" + mysqlSettings.getMySQLHost() + "/" + mysqlSettings.getMySQLDb() + "?useSSL="+mysqlSettings.isMySQLcheckSSL();
				Connection conn = DriverManager.getConnection(url, mysqlSettings.getMySQLUsername(),
						mysqlSettings.getMySQLPassword());
				return conn;
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return null;
	}

	public Model(int id) {
		this.id = id;
	}

	public Model() {
		this.id = Model.UNDEFINED;
	}

	public int getId() {
		return this.id;
	}

	public static Connection getConnection(boolean test) {
		if (connection == null)
			connection = initialize(test);
		return connection;
	}

	// run on production server
	public static Connection getConnection() {
		return getConnection(false);
	}

	public void save() {
		try {
			if (this.id == Model.UNDEFINED) {
				ResultSet resultset = this.insert().getGeneratedKeys();
				resultset.next();
				this.id = resultset.getInt(1);
				if (this instanceof Exercise) 
					((Exercise)(this)).saveToDisk();
			} else
				this.update();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	protected abstract PreparedStatement insert() throws SQLException;

	protected abstract void update() throws SQLException;

	public void delete() {
		if (this.id == Model.UNDEFINED)
			throw new DoesNotExistException();

		try {
			this._delete();
			this.id = Model.UNDEFINED;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	protected void _delete() throws SQLException {
		PreparedStatement statement = prepareStatement("DELETE FROM "
				+ this.getTableName() + " WHERE id=?");
		statement.setInt(1, this.getId());
		statement.executeUpdate();
	}

	// Note: Subclasses add "static ArrayList all()" method which returns
	// list of all model instances of <subclass> type.

	public static PreparedStatement prepareStatement(String sql)
			throws SQLException {
		Connection connection = getConnection();
		PreparedStatement statement = connection.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);
		return statement;
	}

	public static void flushTables() {
		try {
			DatabaseMetaData metaData;
			metaData = Model.getConnection().getMetaData();
			ResultSet rs = metaData.getTables(mysqlSettings.getMySQLDb()+"test", null, null, null);
			PreparedStatement statement;
			while (rs.next()) {
				statement = Model.getConnection(true).prepareStatement(
						"DELETE FROM " + rs.getString("TABLE_NAME"));
				statement.execute();
			}
		} catch (SQLException e) {
			System.err.println("Got an exception! ");
			System.err.println(e.getMessage());
		}

	}
}
