import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;

//import com.mysql.jdbc.*;

public class SQLGuestbook extends JFrame {
   private String db_name = "wijkm";
   private String username = "wijkm";
   private String password = "aeBaikae5Gee";
      
   private String computer = "atlas.dsv.su.se";

   //private String url = "jdbc:mysql://" + computer + "/" + db_name;
   private String url = "jdbc:mysql://" + computer + "/" + db_name + "?user=" + username + "&password=" + password;

   private java.sql.Statement statement = null;

   private JTextField textFieldName = new JTextField();
   private JTextField textFieldEmail = new JTextField();
   private JTextField textFieldHomepage = new JTextField();
   private JTextField textFieldComment = new JTextField();
   private JButton buttonAdd = new JButton("Press me!");
   private JTextArea textArea = new JTextArea("Fetching previous comments ...");

   public static void main(String[] args) {
      new SQLGuestbook();
   }

   public SQLGuestbook() {
      buttonAdd.addActionListener(new L1());
      getContentPane().add(BorderLayout.CENTER, new JScrollPane(textArea));
      JPanel panel = new JPanel();
      panel.setLayout(new GridLayout(5,2));
      panel.add(new JLabel("Name:"));
      panel.add(textFieldName);
      panel.add(new JLabel("E-mail:"));
      panel.add(textFieldEmail);
      panel.add(new JLabel("Homepage:"));
      panel.add(textFieldHomepage);
      panel.add(new JLabel("Comment:"));
      panel.add(textFieldComment);
      panel.add(new JLabel("Add:"));
      panel.add(buttonAdd);
      getContentPane().add(BorderLayout.NORTH, panel);
      setLocation(0, 0);
      setSize(640, 480);

      setVisible(true);

      connect();
      showComments();
   }

   private void connect() {
      try {
         // Anslut!
         Class.forName("com.mysql.jdbc.Driver").newInstance();
         java.sql.Connection dbConnection = DriverManager.getConnection(url); //, username, password);

         // Gor tabell!
         statement = dbConnection.createStatement();
         //String str = "DROP TABLE guestbook";
         String str = "CREATE TABLE IF NOT EXISTS guestbook (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, time TINYTEXT, name TINYTEXT, email TINYTEXT, homepage TINYTEXT, comment TINYTEXT)";
         statement.executeUpdate(str);

         setTitle("CONNECTED TO MySQL ON: " + computer);
      } catch(Exception e) {
e.printStackTrace();
         System.out.println("---> COULD NOT CONNECT OR CREATE TABLE!");
      }
   }

   private void addComment(String name, String email, String homepage, String comment) {
      String time = getTime();

      try {
         String str = "INSERT INTO guestbook (time, name, email, homepage, comment) VALUES ('" + time + "', '" + name + "', '" + email + "', '" + homepage + "', '" + comment + "')";
         statement.executeUpdate(str);
      } catch(Exception e) {
         System.out.println("---> COULD NOT QUERY!");
      }
   }

   private void showComments() {
      textArea.setText("");
      try {
         String str = "SELECT * FROM guestbook ORDER BY time";
         java.sql.ResultSet result = statement.executeQuery(str);
         while(result.next()) {
            int idR = result.getInt("id");
            String timeR = result.getString("time");
            String nameR = result.getString("name");
            String emailR = result.getString("email");
            String homepageR = result.getString("homepage");
            String commentR = result.getString("comment");

            textArea.append("NO: " + idR + " TIME: " + timeR + "\n" + "NAME: " + nameR + " EMAIL: " + emailR + " HOMEPAGE: " + homepageR + "\n" + "COMMENT: " + commentR + "\n\n");
         }
      } catch(Exception e) {
         System.out.println("---> COULD NOT FETCH IT!");
      }
   }

   private String getTime() {
      Calendar calendar = new GregorianCalendar();

      int yearI = calendar.get(Calendar.YEAR);
      String year = (new Integer(yearI)).toString();

      int monthI = calendar.get(Calendar.MONTH) + 1;
      String month = (new Integer(monthI)).toString();
      if(monthI < 10) month = "0" + month;

      int dayI = calendar.get(Calendar.DAY_OF_MONTH);
      String day = (new Integer(dayI)).toString();
      if(dayI < 10) day = "0" + day;

      int hourI = calendar.get(Calendar.HOUR_OF_DAY);
      String hour = (new Integer(hourI)).toString();
      if(hourI < 10) hour = "0" + hour;

      int minuteI = calendar.get(Calendar.MINUTE);
      String minute = (new Integer(minuteI)).toString();
      if(minuteI < 10) minute = "0" + minute;

      int secondI = calendar.get(Calendar.SECOND);
      String second = (new Integer(secondI)).toString();
      if(secondI < 10) second = "0" + second;

      return year + "-" + month + "-" + day + " " + hour + ":" + minute + ":" + second;
   }

   private String removeHTML(String str) {
      Pattern p = Pattern.compile("<.*>");
      Matcher m = p.matcher(str);
      return m.replaceAll("CENCUR");
   }

   class L1 implements ActionListener {
      public void actionPerformed(ActionEvent ae) {
         String name = removeHTML(textFieldName.getText());
         String email = removeHTML(textFieldEmail.getText());
         String homepage = removeHTML(textFieldHomepage.getText());    
         String comment = removeHTML(textFieldComment.getText());

         addComment(name, email, homepage, comment);
         showComments();
         textFieldComment.setText("");
      }
   }
}