import java.sql.*;
import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;



public class gui implements ActionListener{

		public static JButton myButton;

		public static void main(String[] args) {
			(new gui()).go();

		}

		public void go() {


			 JFrame myFrame = new JFrame();



			myButton = new JButton("Hit Me");

			myButton.addActionListener(this);


			myFrame.getContentPane().add(myButton);

			myFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

			myFrame.setSize(300,200);
			myFrame.setVisible(true);
		}

		public void actionPerformed(ActionEvent event) {


			String url = "jdbc:mysql://localhost:3306/memes?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
			String username = "root";
			String password = "7777";

			try{
				Class.forName("com.mysql.cj.jdbc.Driver");
				try (Connection connection = DriverManager.getConnection(url, username, password)) {
					myButton.setText("Connesso al Database!");
					}
				if(TextDemo.check.equals("yes")) {
		        JFrame frame = new JFrame("Query");
		        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

		        //Add contents to the window.
		        frame.add(new TextDemo());

		        //Display the window.
		        frame.pack();
		        frame.setVisible(true);
		        TextDemo.check="no";
				}

			}
				catch (SQLException | ClassNotFoundException e) {
					myButton.setText("Ops :(");
					e.printStackTrace();}

			}




		public static class TextDemo extends JPanel implements ActionListener {
		    /**
			 *
			 */
			private static final long serialVersionUID = 1L;
			protected static JTextField textField;
		    protected static JTextArea textArea;
		    private final static String newline = "\n";
		    public static String check = "yes";



public TextDemo() {
    super(new GridBagLayout());

    textField = new JTextField(20);
    textField.addActionListener(this);

    textArea = new JTextArea(10, 60);
    textArea.setEditable(false);
    JScrollPane scrollPane = new JScrollPane(textArea);

    //Add Components to this panel.
    GridBagConstraints c = new GridBagConstraints();
    c.gridwidth = GridBagConstraints.REMAINDER;

    c.fill = GridBagConstraints.HORIZONTAL;
    add(textField, c);

    c.fill = GridBagConstraints.BOTH;
    c.weightx = 1.0;
    c.weighty = 1.0;
    add(scrollPane, c);
}

public void actionPerformed(ActionEvent evt) {

	if (textField.getText().equals("drop")) {
		   gui.droptable();}
		    else
		    if (textField.getText().equals("show")){
		    	gui.printusers();
		    }
    String text = textField.getText();
    textArea.append( newline);
    textField.selectAll();
    //Make sure the new text is visible, even if there
    //was a selection in the text area.
    textArea.setCaretPosition(textArea.getDocument().getLength());




}}











  public static void droptable() {



	// JDBC driver name and database URL
	   final String DB_URL = "jdbc:mysql://localhost:3306/memes?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";

	   //  Database credentials
	    final String USER = "root";
	   final String PASS = "7777";

	   Connection conn = null;
	   Statement stmt = null;
	   try{
	      //STEP 2: Register JDBC driver
	      Class.forName("com.mysql.cj.jdbc.Driver");

	      //STEP 3: Open a connection
	      System.out.println("Connecting to a selected database...");
	      conn = DriverManager.getConnection(DB_URL, USER, PASS);
	      System.out.println("Connected database successfully...");

	      //STEP 4: Execute a query
	      System.out.println("Deleting table in given database...");
	      stmt = conn.createStatement();

	      String sql = "DROP TABLE users ";

	      stmt.executeUpdate(sql);
	      System.out.println("Table  deleted in given database...");
	   }catch(SQLException se){
	      //Handle errors for JDBC
	      se.printStackTrace();
	   }catch(Exception e){
	      //Handle errors for Class.forName
	      e.printStackTrace();
	   }finally{
	      //finally block used to close resources
	      try{
	         if(stmt!=null)
	            conn.close();
	      }catch(SQLException se){
	      }// do nothing
	      try{
	         if(conn!=null)
	            conn.close();
	      }catch(SQLException se){
	         se.printStackTrace();
	      }//end finally try
	   }//end try
	   System.out.println("Goodbye!");
	}//end main












public static void printusers()
{
  try
  {
    // create our mysql database connection
    String myDriver = "com.mysql.cj.jdbc.Driver";
    String myUrl = "jdbc:mysql://localhost:3306/memes?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
    Class.forName(myDriver);
    Connection conn = DriverManager.getConnection(myUrl, "root", "7777");

    // our SQL SELECT query.
    // if you only need a few columns, specify them by name instead of using "*"
    String query = "SELECT * FROM users";

    // create the java statement
    Statement st = conn.createStatement();

    // execute the query, and get a java resultset
    ResultSet rs = st.executeQuery(query);

    // iterate through the java resultset
    while (rs.next())
    {
      int id = rs.getInt("id");
      String firstName = rs.getString("first_name");
      String lastName = rs.getString("last_name");
      Date dateCreated = rs.getDate("date_created");
      boolean isAdmin = rs.getBoolean("is_admin");
      int numPoints = rs.getInt("num_points");

      // print the results
      String output = id +"\t" + firstName+"\t"  + lastName +"\t" + dateCreated +"\t" + isAdmin+"\t"  + numPoints+"\n" ;
      System.out.println(output);
      TextDemo.textArea.append(output);


    }
    st.close();
  }
  catch (Exception e)
  {
    System.err.println("Got an exception! ");
    System.err.println(e.getMessage());
  }
}

}