
//http://www.vogella.de/articles/MySQL/article.html

package edu.unl.consystlab.sudokuSolver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;


import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.JLabel;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
/**
 *
 * @author jasongaare
 */
public class dbmysql {

    private Connection connect = null;
	private Statement statement = null;
	private ResultSet resultSet = null;

    private String type;
    private String source;
    private String lower;
    private String level;
    private String upper;
    private String name;
    private String original;
    private String description;
    private String [] puzzleArray;
    private int puzzlesindb;

	public dbmysql() throws Exception {





	}

     public int numOfPuzzInDB() throws Exception {
        try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
			PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            PreparedStatement statement = connect
					.prepareStatement("SELECT COUNT(*) FROM puzzles");

			resultSet = statement.executeQuery();

			while (resultSet.next()) {

            puzzlesindb = resultSet.getInt("COUNT(*)");

            //System.out.println("check check  " + puzzlesindb);
			}

            } catch (Exception e) {
			throw e;
		} finally {

			close();

            return puzzlesindb;
		}

     }
     
 public void showAllHighScores() throws Exception{

			Class.forName("com.mysql.jdbc.Driver").newInstance();



			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","aswearng","cse486");




			PreparedStatement pstatement = connect
					.prepareStatement("use aswearng");

			resultSet = pstatement.executeQuery();

                        PreparedStatement statement = connect
					.prepareStatement("SELECT * FROM SudokuHighScores ORDER BY time");

			resultSet = statement.executeQuery();


                        String list = "";
                        int count = 1;
                        while(resultSet.next()){
                            list = list + "\n" + Integer.toString(count) + ". " + resultSet.getString("name") + " " + resultSet.getTime("time");
                        count++;
                        }

                        if(list==""){
                            list = "There are currently no high scores.";
                        }
                        JOptionPane.showMessageDialog(null,list,"High Scores",JOptionPane.INFORMATION_MESSAGE);
      }

     public boolean is_high_score(int hours, int minutes, int seconds) throws Exception {

         try {

                String time = "";
                String h = "";
                String m = "";
                String s = "";

                if(hours<10){
                    h = "0" + Integer.toString(hours);
                }
                else{
                    h = Integer.toString(hours);
                }


                 if(minutes<10){
                    m = "0" + Integer.toString(minutes);
                }
                else{
                    m = Integer.toString(minutes);
                }

               if(seconds<10){
                    s = "0" + Integer.toString(seconds);
                }
                else{
                    s = Integer.toString(seconds);
                }

                time = h + ":" +  m  +  ":" + s;




                    DateFormat sdf = new SimpleDateFormat("hh:mm:ss");


                        Date date = sdf.parse(time);

                        System.out.println("Date and Time: " + date);


			Class.forName("com.mysql.jdbc.Driver").newInstance();



			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","aswearng","cse486");




			PreparedStatement pstatement = connect
					.prepareStatement("use aswearng");

			resultSet = pstatement.executeQuery();

            PreparedStatement statement = connect
					.prepareStatement("SELECT * FROM SudokuHighScores ORDER BY time");

			resultSet = statement.executeQuery();



                       int count = 0;
                       Date lowest = new Date();
                       while (resultSet.next()) {


                          if(count==9){
                              lowest = resultSet.getTime("time");
                          }

                            count++;

			}


                       resultSet = statement.executeQuery();
                       boolean hs = false;


                       if(count>=10){
                           while(resultSet.next()){

                                int results = date.compareTo(resultSet.getTime("time"));
                                if(results<0){
                                        hs = true;


                               }


                           }
                           if(hs==true){

                               try{

                                                     String message = "Congratulations!\n\n\n You have solved the puzzle with a  high score of " + time + "\n\nPlease enter your name below:\n\n";
                                                      Object result = JOptionPane.showInputDialog(null, message);

                                                      if(result!=null){
                                                              //insert high score into db
                                                              String myquery = "INSERT INTO SudokuHighScores(name,time) VALUES('" + result + "','" + time + "')";
                                                              Statement statement_new = connect
                                                                    .createStatement();
                                                              int result1 = statement_new.executeUpdate(myquery);

                                                              //Delete the lowest time in the db
                                                              String myquery2 = "DELETE FROM SudokuHighScores WHERE time='" + lowest + "'";
                                                               statement_new = connect
                                                                    .createStatement();
                                                              int result2 = statement_new.executeUpdate(myquery2);
                                                      }
                               }
                               catch(Exception e){
                                   System.out.println(e);
                               }

                           }
                           else{


                                    String message = "Congratulations!\nYou solved the puzzle.\n Sorry, You did not recieve a high score.\n Better luck next time!\n\n";
                                    JOptionPane.showMessageDialog(null,message);
                           }
                       }
                       else {

                                                     String message = "Congratulations!\n You have solved the puzzle with a high score of " + time + "\n\nPlease enter your name below:\n\n";
                                                      Object result = JOptionPane.showInputDialog(null, message);

                           //insert high score into db
                                                      if(result!=null){
                                                          String myquery = "INSERT INTO SudokuHighScores(name,time) VALUES('" + result + "','" + time + "')";

                                                      Statement updatestatement = connect
                                                            .createStatement();

                                                          try{
                                                          int res = updatestatement.executeUpdate(myquery);
                                                          }
                                                          catch(Exception e){
                                                              System.out.println(e);
                                                          }
                                                      }
                       }



            } catch (Exception e) {
			throw e;

		} finally {

			close();

            return true;
		}


     }



    public void upload(int puzznum) throws Exception {

        String curString;
        

        try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
            PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            PreparedStatement statement = connect
					.prepareStatement("SELECT * FROM puzzles WHERE PuzzleID = " + puzznum);

			resultSet = statement.executeQuery();

			while (resultSet.next()) {

            curString = resultSet.getString("PuzzleValues");
			puzzleArray = curString.split("");
            type = resultSet.getString("SudokuType");
            source = resultSet.getString("Source");
            lower = resultSet.getString("MinDiffLvl");
            level = resultSet.getString("DiffLvl");
            upper = resultSet.getString("MaxDiffLvl");
            name = resultSet.getString("PuzzleName");
            original = resultSet.getString("OrigName");
            description = resultSet.getString("Description");

			}
            } catch (Exception e) {
                System.out.print("Error Accessing Database");

			throw e;
		} finally {
			close();
		}
    }

    public String [] getArray(){
        return puzzleArray;
    }

    int filter(String type, String maxDiff, String diff, int solutions, String consis) {
        int matches = 0;
        try {

            String[][] results = null;

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
			PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            if(maxDiff.equalsIgnoreCase("ALL"))
                maxDiff = "%";
            if(diff.equalsIgnoreCase("ALL"))
                diff = "%";
            if(consis.equalsIgnoreCase("ALL"))
                consis = "%";

            String query = "SELECT COUNT(*) FROM puzzles WHERE SudokuType ='" + type
                    + "' AND MaxDiffLvl LIKE '" + maxDiff + "' AND DiffLvl " +
                    "LIKE '" + diff + "' AND Consistency LIKE '" + consis +"'";

            if(solutions == 1) {
                query += " AND Solutions = 1";
            }
            else if(solutions ==2) {
                query += " AND Solutions > 1";
            }

            PreparedStatement statement = connect
					.prepareStatement(query);

			resultSet = statement.executeQuery();

            while(resultSet.next()){
                matches = Integer.parseInt(resultSet.getString("COUNT(*)"));
            }
        } catch (Exception e) {
			throw e;
		} finally {

			close();

            return matches;
		}

    }
    
    String getDesc() {
        return description;
    }

    String getLevel() {
        return level;
    }

    String getLower() {
        return lower;
    }

    String[] getMaxDiffs() {
        String[] MaxDiffLvls = null;
        try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
			PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            PreparedStatement statement = connect
					.prepareStatement("SELECT DISTINCT MaxDiffLvl FROM puzzles ORDER BY MaxDiffLvl");

			resultSet = statement.executeQuery();

            String[] temp = new String[11];
            temp[0] = "ALL";
            int y = 1;
			while (resultSet.next()) {

                temp[y] = resultSet.getString("MaxDiffLvl");
                y++;

			}

            MaxDiffLvls = new String[y];
            for(int i = 0; i < y; i++)
            {
                MaxDiffLvls[i] = temp[i];
            }

            } catch (Exception e) {
			throw e;
		} finally {

			close();

            return MaxDiffLvls;
		}
    }

    String getName() {
        return name;
    }

    String getOrigName() {
        return original;
    }

    String getSource() {
        return source;
    }

    String getType() {
        return type;
    }

    String getUpper() {
        return upper;
    }

    String[][] populateList(String type, String maxDiff, String diff, int solutions,
            String consis, String orderBy) throws Exception {
         try {

            String[][] results = null;

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
			PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            if(maxDiff.equalsIgnoreCase("ALL"))
                maxDiff = "%";
            if(diff.equalsIgnoreCase("ALL"))
                diff = "%";
            if(consis.equalsIgnoreCase("ALL"))
                consis = "%";

            String query = "SELECT * FROM puzzles WHERE SudokuType ='" + type
                    + "' AND MaxDiffLvl LIKE '" + maxDiff + "' AND DiffLvl" +
                    " LIKE '" + diff + "' AND Consistency LIKE '" + consis + "'";

            if(solutions == 1) {
                query += " AND Solutions = 1";
            }
            else if(solutions == 2) {
                query += " AND Solutions > 1";
            }

            query +=  " ORDER BY " + orderBy;

            //System.out.println(query);

            PreparedStatement statement = connect
					.prepareStatement(query);

			resultSet = statement.executeQuery();

            int index = 0;
            int size =0;
            if (resultSet != null)
            {
              resultSet.beforeFirst();
              resultSet.last();
              size = resultSet.getRow();
              resultSet.first();
            }

            //System.out.println("Results size: " + size);

            results = new String[size][7];

            if(size > 0)
            {
                do
                {
                    results[index][0] = resultSet.getString("PuzzleName");
                    results[index][1] = resultSet.getString("MaxDiffLvl");
                    results[index][2] = resultSet.getString("DiffLvl");
                    results[index][3] = resultSet.getString("Source");
                    results[index][4] = resultSet.getString("Solutions");
                    results[index][5] = resultSet.getString("Consistency");
                    results[index][6] = resultSet.getString("PuzzleID");
                    index++;
                }
                while(resultSet.next());
            }
            return results;

            } catch (Exception e) {
			throw e;
		} finally {
			close();
		}
    }
    
    void uploadPuzzle(String type, String source, String lower, String level,
            String upper, String name, String original, String description,
            String array) throws Exception {
        try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
			PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            String query = "INSERT INTO puzzles (SudokuType, Source, MinDiffLvl, "+
                    "DiffLvl, MaxDiffLvl, PuzzleName, OrigName, Description, PuzzleValues) VALUES" +
                    "('"+type+"','"+source+"', "+lower+","+level+","+upper +
                    ",'"+name+"','"+original+"','"+description+"','"+array+"')";


            //System.out.println(query);

            PreparedStatement statement = connect
					.prepareStatement(query);

			statement.execute();

            } catch (Exception e) {
			throw e;
		} finally {
			close();
		}
    }

       int uploadPuzzle(String db, String type, String source, String lower, String level,
            String upper, String name, String original, String description, String array,
            int sols, String levelNeeded) throws Exception {

        int count = 0;

        try {

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connect = DriverManager
					.getConnection("jdbc:mysql://cse.unl.edu/","sudoku","ukodus");
			PreparedStatement pstatement = connect
					.prepareStatement("use sudoku");

			resultSet = pstatement.executeQuery();

            String query = "SELECT COUNT(*) FROM " + db + " WHERE PuzzleValues = '" + array + "'";

            PreparedStatement statement = connect
					.prepareStatement(query);

			resultSet = statement.executeQuery();


            //System.out.println(query);
            if(resultSet.next())
            {
                resultSet.first();
                count = resultSet.getInt("COUNT(*)");
            }

            //System.out.println(count);
            if(count == 0)
            {


            query = "INSERT INTO " + db + " (SudokuType, Source, MinDiffLvl, "+
                    "DiffLvl, MaxDiffLvl, PuzzleName, OrigName, Description, " +
                    "PuzzleValues, Solutions, Consistency) VALUES" +
                    "('"+type+"','"+source+"', "+lower+","+level+","+upper +
                    ",'"+name+"','"+original+"','"+description+"','"+array+
                    "',"+sols+", '"+levelNeeded+"')";


            //System.out.println(query);

            statement = connect
					.prepareStatement(query);

			statement.execute();
            }


            } catch (Exception e) {
			throw e;
		} finally {
			close();
            if(count == 0)
                return 0;
            else if (count > 0)
                return 1;
		}

        return 2;
    }
       
	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

}
