import java.sql.*;

public class DataBase {
    Statement statement;
    int currentIndex = 1;
    Boolean dataBaseExists = false;
    public DataBase() throws SQLException {
        String driver = "com.mysql.jdbc.Driver";
        String URL = "jdbc:mysql://127.0.0.1:3306/";
        String databaseName = "ankieta";
        Connection connection = null;
        try {
            Class.forName(driver).newInstance();
            connection = DriverManager.getConnection(URL, "root", null);
            statement = connection.createStatement();
            ResultSet resultSet = connection.getMetaData().getCatalogs();
            while (resultSet.next()) {
            	  if(databaseName.equals(resultSet.getString(1)))
            		  dataBaseExists=true;
            	}
        } catch (InstantiationException | IllegalAccessException
                | ClassNotFoundException | SQLException e) {
        	System.out.println("Błąd ze sterownikiem bazy danych!");
            e.printStackTrace();
        }
        if(dataBaseExists) {
        	useDatabase();
            DatabaseMetaData dmd = connection.getMetaData();
            ResultSet rs = dmd.getTables(null, null, "odpowiedzi", null);
            if (!rs.next())
                createTables();
        }
        else {
        	createDatabase();
        }
    }

    public void useDatabase() throws SQLException {
    	 try {
    		 statement.executeUpdate("USE ankieta;");
    		 System.out.println("Używamy bazy - ankieta");
         } catch (SQLException e) {
             e.printStackTrace();
         }
    }

    public void createDatabase() throws SQLException {
   	 try {
   		 statement.executeUpdate("CREATE DATABASE ankieta;");
		 System.out.println("Utworzona baza danych - ankieta");
   		 useDatabase();
   		 createTables();
     } catch (SQLException e) {
         e.printStackTrace();
     }
    }

    private void createTables() {
        String answers = "CREATE TABLE odpowiedzi(Id int, odp varchar(255));";
        String questions = "CREATE TABLE bazapytan(pytanie varchar(255), odpA varchar(255), odpb varchar(255), odpC varchar(255), odpD varchar(255));";
        try {
            statement.executeUpdate(answers);
            statement.executeUpdate(questions);
            insertQuestions();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertQuestions() {
        String questions = "INSERT INTO bazapytan values (\"Pytanie jeden\",\"odp1\",\"odp2\",\"3\",\"4\")";
        try {
            statement.executeUpdate(questions);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public String readQuestion(int id) {
        String question = "";
        String sql = ("SELECT pytanie, odpA,odpB,odpC,odpD FROM bazapytan WHERE ID= " + id);
        int number;
        ResultSet resultSet;
        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                for (number = 1; number < 6; number++)
                    question += resultSet.getString(number) + "\n";
            }
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return question;
    }

    public int countRows() {
        int numberOfRows = 0;
        ResultSet resultSet;
        try {
            resultSet = statement.executeQuery("SELECT COUNT(*) FROM bazapytan");
            while (resultSet.next())
                numberOfRows = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return numberOfRows;
    }

    public void saveInDataBase(String answer) {
        int lastIndex = countRows();
        try {
            if (currentIndex <= lastIndex)
                statement.executeUpdate("INSERT INTO odpowiedzi VALUES ('" + currentIndex + "',' " + answer + "')");
            currentIndex++;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public int countValues(int id,int odp) {
        int numberOfValues = 0;
        ResultSet resultSet;

        String sql = "SELECT COUNT(*) FROM odpowiedzi WHERE Id=" + id + " AND odp=" + odp;
        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                numberOfValues = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return numberOfValues;
    }

    public String getQuestion(int id) {
        String message = "", sql;
        ResultSet resultSet;
        sql = "SELECT pytanie FROM `bazapytan` WHERE Id=" + id;
        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next())
                message = resultSet.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return message;
    }

    public String getAnswer(int id, String odp) {
        String message = "", sql;
        ResultSet resultSet;
        sql = "SELECT " + odp + " FROM `bazapytan` WHERE Id=" + id;
        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next())
                message = resultSet.getString(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return message;
    }
}