package de.ugoe.cs.swe.memos.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import de.ugoe.cs.swe.memos.SettingsManager;
import de.ugoe.cs.swe.memos.Utils;

public class HSQLConnector {

	private static HSQLConnector instance;
	private Connection connection;

	public Connection getConnection() {
		try {
			if (!this.isConnected())

				this.connect();
		} catch (Exception e) {

			Utils.showError("Konnte Datenbankverbindung nicht aufbauen.");
			return null;
		}

		return connection;
	}

	private HSQLConnector() {
	}

	public static HSQLConnector getInstance() {
		if (HSQLConnector.instance == null) {
			HSQLConnector.instance = new HSQLConnector();
		}

		return HSQLConnector.instance;
	}

	public boolean isConnected() throws SQLException {
		return (this.connection != null && !this.connection.isClosed());
	}

	public void connect() throws DatabaseException, SQLException {
		if (this.isConnected())
			throw new DatabaseException("Already connected. Disconnect first.");

		try {
			try {
				Class.forName("org.hsqldb.jdbcDriver");
			} catch (Exception e) {
				Utils.showError("Laden des HSQL JDBC Treibers fehlgeschlagen");
				return;
			}
			connection = DriverManager
					.getConnection("jdbc:hsqldb:file:"
							+ SettingsManager.getInstance().getHSQLFilename(),
							"sa", "");
			connection.setAutoCommit(true);
			createDatabase();
		} catch (Exception e) {
			Utils.showError("Verbindung zur HSQL Datenbank fehlgeschlagen");
			throw new DatabaseException(e.getMessage());
		}
	}

	public void close() throws DatabaseException, SQLException {
		if (!this.isConnected())
			throw new DatabaseException("Not connected. No need to disconnect.");

		try {
			Statement statement = connection.createStatement();
			statement.execute("SHUTDOWN;");
			connection.close();
			connection = null;
			// ignored in code coverage. not injectable sql error :))
		} catch (Exception e) {
			throw new DatabaseException(e.getMessage());
		}
	}

	public void createDatabase() {
		String createQuery = // "DROP TABLE IF EXISTS Categories; " +
		"CREATE TABLE Categories (ID int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL, Parent int default NULL, Name varchar(255) NOT NULL);"
				+
				// "DROP TABLE IF EXISTS Memos; " +
				"CREATE TABLE Memos (  ID int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL,  Title varchar(255) default NULL,  Content longvarchar,  Timestamp timestamp default NULL,  Category int default NULL,  LockedBy varchar(255) default NULL,  Author varchar(255) NOT NULL,  Draft boolean NOT NULL,  LockedAt timestamp default NULL);"
				+
				// "DROP TABLE IF EXISTS Tagging; " +
				"CREATE TABLE Tagging (ID int GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL,  Tag varchar(255) NOT NULL,  Memo int NOT NULL);"
				+
				// "DROP TABLE IF EXISTS" +
				"CREATE TABLE Files (ID int IDENTITY NOT NULL, Name varchar(700) NOT NULL, Memo int NOT NULL, Content longvarbinary NOT NULL);";

		try {
			Statement createStatement = getConnection().createStatement();
			createStatement.execute(createQuery);
		} catch (Exception e) {
			if (!e.getMessage().contains("already exists")) {
				Utils
						.showError("Es gibt ein Problem mit der HSQL Datenbank. Bitte kontaktieren sie ihren Administrator");
			}
		}

	}

}
