package survey;


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.HashMap;


public class DatabaseHandler {

	static float[][] resultTable = new float[10][5];
	static int pomoc;
	static int test=0;
	static java.sql.Connection connection;

		public DatabaseHandler()
		{
			if (checkDriver("com.mysql.jdbc.Driver"))
				System.out.println(" ... OK");
			else
				System.exit(1);
			connection = connectToDatabase("jdbc:mysql://", "localhost:3306", "sob", "root", "");
			if (connection != null)
				System.out.print(" polaczenie OK\n");
		}


		public static boolean checkDriver(String driver) {
			System.out.print("Sprawdzanie sterownika:");
			try {
				Class.forName(driver).newInstance();
				return true;
			} catch (Exception e) {
				System.out.println("Error when loading driver!");
				return false;
			}
		}

		public static Connection connectToDatabase(String kindOfDatabase, String adress,
				String dataBaseName, String userName, String password) {
			String baza = kindOfDatabase + adress + "/" + dataBaseName;
			java.sql.Connection connection = null;
			try {
				connection = DriverManager.getConnection(baza, userName, password);
			} catch (SQLException e) {
				System.out.println("Error connecting to database!");
				System.exit(1);
			}
			return connection;
		}

		private static Statement createStatement(Connection connection) {
			try {
				return connection.createStatement();
			} catch (SQLException e) {
				System.out.println("Error - > createStatement! " + e.getMessage() + ": " + e.getErrorCode());
				System.exit(3);
			}
			return null;
		}

		private static void closeConnection(Connection connection, Statement s) {
			try {
				s.close();
				connection.close();
			} catch (SQLException e) {
				System.out
						.println("Error closing database! " + e.getMessage() + ": " + e.getErrorCode());;
				System.exit(4);
			}

		}

		private static ResultSet executeQuery(Statement s, String sql) {
			try {
				return s.executeQuery(sql);
			} catch (SQLException e) {
				System.out.println("Query couldn't be executed! " + e.getMessage() + ": " + e.getErrorCode());
			}
			return null;
		}
		private static int executeUpdate(Statement s, String sql) {
			try {
				return s.executeUpdate(sql);
			} catch (SQLException e) {
				System.out.println("Statement couldn't be executed! " + e.getMessage() + ": " + e.getErrorCode());
			}
			return -1;
		}

		public HashMap<Integer, String> getQuestions()
		{
			System.out.println("Getting questions from database");
			HashMap<Integer, String> questionsMap = new HashMap<>();
			Statement sqlStatement = createStatement(connection);
			ResultSet result = executeQuery(sqlStatement, "Select * from Questions");

			try {
				while(result.next())
				{
					Object number = result.getObject(1);
					Object question = result.getObject(2);
					questionsMap.put(Integer.parseInt(number.toString()), question.toString());
				}
			}
			catch (SQLException e) {
				System.out.println("Error getting questions from database! " + e.getMessage() + ": " + e.getErrorCode());
			}

			return questionsMap;
		}

		public HashMap<Integer, ArrayList<String>> getAnswers()
		{
			System.out.println("Getting answers from database");

			HashMap<Integer, ArrayList<String>> answerMap = new HashMap<>();

			Statement sqlStatement = createStatement(connection);
			ResultSet result = executeQuery(sqlStatement, "Select * from Answers");

			try {
				while(result.next())
				{
					ArrayList<String> answers = new ArrayList<>();
					Object number = result.getObject(1);
					for(int i = 2; i < 7; ++i){
						Object question = result.getObject(i);
						answers.add(question.toString());

					}
					answerMap.put(Integer.parseInt(number.toString()), answers);

				}
			}
			catch (SQLException e) {
				System.out.println("Error getting answers from database! " + e.getMessage() + ": " + e.getErrorCode());
			}

			return answerMap;
		}

		public static void sendAnswerToDatabase(String clientID,int questionNumber, String result ){
			Statement s = createStatement(connection);
			executeUpdate(s, "INSERT INTO Results (Client_ID, Question_Number, Answer)"
					+ "VALUES ('" + clientID + "'," + questionNumber + ",'" + result + "');");

		}


		public void getSummary() throws SQLException
	    {
			Statement s = createStatement(connection);
			ResultSet result1 = executeQuery(s, "Select count(Client_ID)/10 as zlicz from results");
			if(result1.next())
			this.pomoc = result1.getInt("zlicz");

			System.out.println(this.pomoc);
			for(int i=1; i<=10; i++) {
				for( int j=1; j<=5; j++) {
					ResultSet result = executeQuery(s, "Select count(*) as total from results where question_number=" + i + " && answer="+ j);

					//System.out.println("aa");
					if(result.next())
						this.resultTable[i-1][j-1] = result.getInt("total");

				}
			}
			for(int i=1; i<=10; i++) {

				System.out.println("Na pytanie nr :" + i);

				for( int j=1; j<=5; j++) {

					System.out.println("Procentowy wybor odpowiedz " + j + " wynosi: ");
					System.out.println((this.resultTable[i-1][j-1]/this.pomoc)*100 + "%");


				}
				System.out.println();
			}
			closeConnection(connection, s);
	    }

}