package org.usi2011;

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

/**
 * @author XKHM6852
 * 
 */
public final class Database {

	private static Connection connection;

	/** */
	public static Connection getConnection() {
		if (connection == null) {
			createDatabase();
		}
		return connection;
	}

	private static void createDatabase() {
		try {
			connection = DriverManager.getConnection("jdbc:hsqldb:mem:aname",
					"sa", "");
			String query = "DROP TABLE game; CREATE TABLE game ( "
					+ "  id INT NOT NULL PRIMARY KEY, "
					+ "  longpollingduration INT NOT NULL, "
					+ "  nbusersthreshold INT NOT NULL,"
					+ "  questiontimeframe INT NOT NULL,"
					+ "  nbquestions INT NOT NULL" + ");";

			Statement statement = connection.createStatement();
			ResultSet resultset = statement.executeQuery(query);
			connection.commit();
			createUser();

		} catch (SQLException e) {

			System.out.println("SQL Exception " + e.getMessage());
		}
	}

	private static void createUser() {
		/*
		 `id` int(11) NOT NULL AUTO_INCREMENT,
		  `firstname` varchar(255) COLLATE utf8_bin NOT NULL,
		  `lastname` varchar(255) COLLATE utf8_bin NOT NULL,
		  `mail` varchar(255) COLLATE utf8_bin NOT NULL,
		  `password` varchar(255) COLLATE utf8_bin NOT NULL,
		  */
		try {
			String query = "DROP TABLE user; CREATE TABLE user ( "
					+ "  id INT GENERATED BY DEFAULT AS IDENTITY, "
					+ "  firstname VARCHAR NOT NULL, "
					+ "  lastname VARCHAR NOT NULL,"
					+ "  mail VARCHAR NOT NULL,"
					+ "  password VARCHAR NOT NULL" + ");";

			Statement statement = connection.createStatement();
			ResultSet resultset = statement.executeQuery(query);
			connection.commit();

		} catch (SQLException e) {

			System.out.println("SQL Exception " + e.getMessage());
		}
	}

}