/*Class Guestbook which connects to a SQL-database that has a TABLE created like this: 
CREATE TABLE guestbook(id INT, timestamp VARCHAR(80), name VARCHAR(80),email VARCHAR(80),comment VARCHAR(100),homepage VARCHAR(80));*/
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import mixer.*;
import java.util.regex.*;
import java.util.*;

public class Guestbook extends HttpServlet {
    
    Calendar cal;
    int connectHour, connectLimit = 2;
    private static final String SELECT_ALL =
    "SELECT id, timestamp, name, email, comment, homepage FROM guestbook"; 
    private static final String CREATE_TABLE =
    "CREATE TABLE guestbook(id INT(11), timestamp VARCHAR(50), name VARCHAR(80), email VARCHAR(80), comment VARCHAR(100), homepage CHAR(60));";
    private static final String INSERT = 
    "INSERT INTO guestbook(id, timestamp, name, email, comment, homepage)" +
    "VALUES (?,?,?,?,?,?)";
    private static String htmlFile = null;   
    private static String htmlPath = "/ip-2/guestbook/";
    private static int count = 1;    
    private String htmlFileName ="guestbook.htm";    
    private Connection con = null;    
  
    private String host = "xxx"; 
    private String dbName = "xxx"; 
    private String uName = "xxx"; 
    private String password = "xxx";
    
    public void init() throws ServletException {
       
        cal = Calendar.getInstance();
        connectHour = cal.get(Calendar.HOUR_OF_DAY);
          if(htmlFile == null) {
            htmlFile = Mixer.getContent(new File(getServletContext().getRealPath(htmlPath + htmlFileName)));
        }
        try {
            if(con == null || con.isClosed()) {
                boolean ok = loadDatabaseDrivers();
                
                if(!ok)
                    System.err.println("Sorry, there is a problem connecting to the database, please try later...");
                else {
                    con.createStatement();
                    PreparedStatement p = con.prepareStatement(CREATE_TABLE);
                    p.executeUpdate();
                }
            }
        }
        catch(SQLException sE) {
        }
        
    }
    private boolean loadDatabaseDrivers() throws ServletException {
        
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection("jdbc:mysql://" + host + "/" + dbName, uName, password);
            if(con != null && !con.isClosed())
                return true;
            else 
                return false;
        }
        catch (ClassNotFoundException e) {
             throw new UnavailableException ("Couldn't load database driver: " + e.getMessage());
        }
        catch(InstantiationException e) {
            throw new UnavailableException("Couldn't instantiate : " + e.getMessage());
        }
        catch(IllegalAccessException e) {
             throw new UnavailableException("Illegal acces:" + e.getMessage());
        }
        catch(SQLException e) {
             throw new UnavailableException("Couldn't get establish database connection!");
             
        }
       
      
    }
  
    public void doGet(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException  {
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        
        int currentHour = Calendar.getInstance().get(Calendar.HOUR_OF_DAY);
        if(currentHour - connectHour >= connectLimit) {
            connectHour = currentHour;
            if(con != null) {
                try {
                con.close();
                con = null;
                }
                catch(SQLException sE) {
                }
            }
        }
        boolean ok = false;
        if(con == null) {
            ok = loadDatabaseDrivers();
                if(!ok)
                    out.println("Sorry, there is a problem connecting to the database, please try later...");
                else
                    printMessages(out);
        }
        else
             printMessages(out);
    }
        
    
    public void doPost(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException  {
        handleForm(req, res);
        doGet(req,res);
    }
    
    
    // Läs rader från databasens table guestbook
    private void printMessages(PrintWriter out) throws ServletException {
        String name, email, comment, homepage,timestamp;
        Mixer m = new Mixer(htmlFile);
        int id = 0;
        Statement s = null;
        ResultSet rS = null;
       
        
        try {
            s = con.createStatement();
            rS = s.executeQuery(SELECT_ALL);
            while(rS.next()) {
                id = rS.getInt(1);
                if(rS.wasNull() || id == 0) 
                    id = 0; 
                timestamp = rS.getString(2);
                if(rS.wasNull() || timestamp.length() == 0) 
                    timestamp = "Unknown time";                 
                name = rS.getString(3);
                if(rS.wasNull() || name.length() == 0) 
                    name = "Unknown user";
                email = rS.getString(4);
                if(rS.wasNull() || email.length() == 0) 
                    email = "Unknown email";
                comment = rS.getString(5);
                if(rS.wasNull() || comment.length() == 0) 
                    comment = "No comment";
                homepage = rS.getString(6);
                if(rS.wasNull() || homepage.length() == 0) 
                    homepage = "No homepage";
                if(rS.isLast())
                    count = rS.getInt(1)+1;
               
                m.add("<!--===xxx===-->","===email===",email);
                m.add("<!--===xxx===-->","===comment===",comment);
                m.add("<!--===xxx===-->","===homepage===",homepage);
                m.add("<!--===xxx===-->","===id===",Integer.toString(id));
                m.add("<!--===xxx===-->","===name===",name);
                m.add("<!--===xxx===-->","===timestamp===",timestamp);
            }
            out.println(m.getMix());
        }
        catch (SQLException e) {
            throw new ServletException(e);
        }
        finally {
            try {
                if(s != null) s.close();
            }
            catch (SQLException ignored) {}
            //pool.returnConnection(con);
        }
    }
    
    
    private void handleForm(HttpServletRequest req, 
                            HttpServletResponse res) throws ServletException {
        String REGEX = "[<>]", at = "[@]";                        
        Pattern p = Pattern.compile(REGEX);
        Pattern p2 = Pattern.compile(at);                                
        Matcher m = null;   
        PreparedStatement pS = null;
                                
        String comment = req.getParameter("comment");                        
        m = p.matcher(comment);  
        if(m.find())
            comment = "CENSUR";
        else if(comment.length() > 100)
            comment = comment.substring(0,100);
        String name = req.getParameter("name");
        m = p.matcher(name);  
        if(m.find())
            name = "CENSUR";
        else if(name.length() > 100)
            name = name.substring(0,100);        
        String email = req.getParameter("email");
        m = p.matcher(email);
        if(m.find())
            email = "CENSUR";
        m = p2.matcher(email);
        if(!m.find())
            email = "Unknown";
        else if(email.length() > 60)
            email = email.substring(0,60);        
        String homepage = req.getParameter("homepage");
        m = p.matcher(homepage);  
        if(m.find())
            homepage = "CENSUR";  
        else if(homepage.length() > 80)
            homepage = homepage.substring(0,80); 
        if(!homepage.startsWith("http") && homepage != null && homepage !="" && homepage.length() != 0) 
            homepage = "http://" + homepage;            
       
        try {
            pS = con.prepareStatement(INSERT);
            Timestamp t = new Timestamp(System.currentTimeMillis());
            synchronized(pS) {
                pS.clearParameters();
                pS.setInt(1,count);
                pS.setString(2,t.toString());
                pS.setString(3, name);
                pS.setString(4, email);
                pS.setString(5, comment);
                pS.setString(6, homepage);
                pS.executeUpdate();
            }
        }
        catch (SQLException e) {
            throw new ServletException(e);
        }
        finally {
            try {
                if(pS != null) pS.close();
            }
            catch(SQLException ignored) {
                }
        }
        
      
    }
    public void destroy() {
         try {
                if(con !=null)
                    con.close();
        }
        catch(SQLException sE) {
            System.err.println("Problem closing database connection when destroying servlet " +sE);
        }
    }
}