import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

public class Database_class {

	String driver;
	Connection conn;
	private Vector dataVector;

	/*--------------- SQL-METHODS FOR DB-CONNECTION -----------*/

	public Database_class() throws SQLException {
		driver = "org.apache.derby.jdbc.EmbeddedDriver";
		conn = DriverManager.getConnection("jdbc:derby:testdb;create=true");
	}

	void connect2db() throws SQLException {
		try {
			Class.forName(driver); // load Driver
			System.out.println(driver + " loaded. ");
		} catch (java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}
	}

	void closedb() throws SQLException {
		conn.close();
		if (driver.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
			boolean gotSQLExc = false;
			try {
				DriverManager.getConnection("jdbc:derby:;shutdown=true");
			} catch (SQLException se) {
				if (se.getSQLState().equals("XJ015")) {
					gotSQLExc = true;
				}
			}
			if (!gotSQLExc) {
				System.out.println("Database did not shut down normally");
			} else {
				System.out.println("Database shut down normally");
			}
		}
	}

	/*--------------- SQL-METHODS FOR VOCAB -------------------*/

	void queryVocab_debug() throws SQLException {
		System.out.println("Debug message: getVocab_debug()");
		Statement s = conn.createStatement();
		ResultSet result = s.executeQuery("select * from Vocable");

		System.out.println("ID:\tEnglisch:\t\t\tDeutsch:\t\t\tKategorie:");
		System.out
				.println("-------------------------------------------------------------\n");
		while (result.next()) { // Prints all results
			System.out.println(result.getString(1) + "\t" + result.getString(2)
					+ "\t" + result.getString(3) + "\t" + result.getString(4));
		}
		System.out.println("\n");
	}

	ResultSet queryVocab() throws SQLException {
		System.out.println("Debug message: queryVocab()");
		Statement s = conn.createStatement();
		ResultSet result = s
				.executeQuery("select Vocable.VID, Vocable.Word, Vocable.Transl, Category.Name from Vocable inner join Category on Vocable.Cat = Category.CID");
		return result;

	}

	void insertVocab(String value, String transl, int cat) throws SQLException {
		System.out.println("Debug message: insertVocab('" + value + "', '"
				+ transl + "', '" + cat + "')");
		Statement s = conn.createStatement();
		s.execute("insert into Vocable(Word, Transl, Cat) values " + "('"
				+ value + "','" + transl + "'," + cat + ")");
	}

	void deleteVocab(int value) throws SQLException {
		System.out.println("Debug message: deleteVocab(" + value + ")");
		Statement s = conn.createStatement();
		s.execute("Delete from Vocable where VID = " + value);
		System.out.println(value + " deleted from table Vocable");
	}

	void deleteVocab_Word(String value) throws SQLException {
		System.out.println("Debug message: deleteVocab_Word(" + value + ")");
		Statement s = conn.createStatement();
		s.execute("Delete from Vocable where Word = '" + value + "'");
		System.out.println(value + " deleted from table Vocable");
	}

	int getVocab_ID(String value, boolean choice) throws SQLException {
		System.out.println("Debug message: getVocab_ID(" + value + ", "
				+ choice + ")");
		int vID = 0;
		Statement s = conn.createStatement();

		if (choice)
			s.execute("select VID from Vocable where Transl = '" + value + "'");
		else
			s.execute("select VID from Vocable where Word = '" + value + "'");

		return vID;
	}

	/*--------------- SQL-METHODS FOR TRAINING ----------------*/

	String getRandomVocab() throws SQLException {
		String returnval = "";

		System.out.println("Debug message: getRandomVocab()");
		Statement s = conn.createStatement();

		ResultSet result = s
				.executeQuery("Select Word from Vocable order by RANDOM()");
		result.next();
		returnval = result.getString(1);
		System.out.println(returnval);
		return returnval;
	}

	String checkAnswer(String value) throws SQLException {
		String returnvalue = "";
		System.out.print("Debug message: checkAnswer(): ");
		Statement s = conn.createStatement();
		ResultSet result = s.executeQuery("Select Word from Vocable where Transl = '"+value+"'");

		if(result.next()){
			returnvalue = result.getString(1);
			return returnvalue;
		} else {
			return returnvalue;
		}
	}

	/*--------------- SQL-METHODS FOR CATEGORY ----------------*/

	ResultSet queryCategory() throws SQLException {
		System.out.println("Debug message: queryCategory()");
		Statement s = conn.createStatement();
		ResultSet result = s.executeQuery("select * from Category");
		return result;
	}

	void insertCategory(String value) throws SQLException {
		System.out.println("Debug message: insertCategory(" + value + ")");
		Statement s = conn.createStatement();
		s.execute("insert into Category(Name) values " + "('" + value + "')");
	}

	void deleteCategory(int value) throws SQLException {
		if (alterCategoryifNotEmpty(value)) {
			System.out.println("Debug message: deleteCategory(" + value + ")");
			Statement s = conn.createStatement();
			s.execute("Delete from Category where CID = " + value);
		}
	}

	int getCategoryID(String value) throws SQLException {
		int returnval = 0;
		Statement s = conn.createStatement();
		ResultSet result = s
				.executeQuery("select CID from Category where Name = '" + value
						+ "'");
		while (result.next()) {

			returnval = result.getInt(1);
		}
		return returnval;
	}

	String getCategoryName(int id) throws SQLException {
		System.out.println("Debug message: getCategoryName(" + id + ")");
		String value = "";
		Statement s = conn.createStatement();
		ResultSet result = s
				.executeQuery("Select Name from Category where CID = " + id);
		while (result.next())
			value = result.getString(1);
		return value;
	}

	/**
	 * Alter Category of Vocabs in Preparation to delete a category from the
	 * table.
	 *
	 * @param id
	 * @throws SQLException
	 */
	boolean alterCategoryifNotEmpty(int id) throws SQLException {
		System.out.println("Debug message: checkifCategoryEmpty()");
		Statement s = conn.createStatement();
		Statement s1 = conn.createStatement();
		if (id != 1) {
			ResultSet result = s
					.executeQuery("Select VID, Cat from Vocable where Cat = "
							+ id);

			while (result.next()) {
				s1.executeUpdate("update Vocable set Cat = 1 where Cat = "
						+ result.getString(2));
			}
			return true;

		} else {
			System.out
					.println("Debug message: Nice try! I ain't gonna delete myself!");
			return false;
		}
	}

	int checkCategory(String value) throws SQLException {
		int returnval = 0;
		Statement s = conn.createStatement();
		ResultSet result = s
				.executeQuery("select CID from Category where Name = '" + value
						+ "'");
		while (result.next()) {

			returnval = result.getInt(1);
		}
		return returnval;
	}

	/*--------------- SQL-METHODS FOR DBUSER ------------------*/

	/**
	 * Debug-method that returns a full list of all Users to the console.
	 *
	 * @throws SQLEception
	 */
	void queryUser_debug() throws SQLException {
		System.out.println("Debug message: queryUser_debug()");
		Statement s = conn.createStatement();
		ResultSet result = s.executeQuery("select * from DBUser");

		System.out.println("UserID:\tName:");
		System.out
				.println("-------------------------------------------------------------\n");
		while (result.next()) { // prints all results
			System.out
					.println(result.getString(1) + "\t" + result.getString(2));
		}
		System.out.println("\n");
	}

	/**
	 * Queries the Userdatabase and returns a full list of all Users.
	 *
	 * @return list of all DBUsers
	 * @throws SQLException
	 */
	ResultSet queryUser() throws SQLException {
		System.out.println("Debug message: queryUser()");
		Statement s = conn.createStatement();
		ResultSet result = s.executeQuery("select * from DBUser");
		return result;
	}

	/**
	 * Inserts a User {@value} into the database.
	 *
	 * @param value
	 * @throws SQLException
	 */
	void insertUser(String value) throws SQLException {
		System.out.println("Debug message: insertUser(" + value + ")");
		Statement s = conn.createStatement();
		s.execute("insert into DBUser(Name) values " + "('" + value + "')");
	}

	/**
	 * Deletes a User {@value} from the database.
	 *
	 * @param value
	 * @throws SQLException
	 */
	void deleteUser(int value) throws SQLException {
		Statement s = conn.createStatement();
		s.execute("Delete from DBUser where UID = " + value);
		System.out.println("Debug message: deleteUser(" + value + ")");
	}

	/* -------------- END OF SQL-METHODS ---------------------- */

	ArrayList<String> getCategory() throws SQLException {
		ArrayList<String> result_list = new ArrayList(1);
		ArrayList<Integer> result_list_nr = new ArrayList(1);
		Statement s = conn.createStatement();
		ResultSet result = s.executeQuery("select Name from Category");

		while (result.next()) {
			result_list.add(result.getString("Name"));
		}
		return result_list;
	}

	/* begin for User-SQL-Statements */
}