import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.util.Date;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;

public class Foorum extends HttpServlet {
	String[] sqlBases = { "servlet_foorum", "servlet_foorum_users", "servlet_foorum_messages" };
	String[] cells = { "id", "user", "message", "name", "date", "time", "text" };

	public void doGet(HttpServletRequest kysimus, HttpServletResponse vastus) throws IOException, ServletException {
		boolean done = false, header = false, mobile = false;
		String action = "false";
		HTTP h = new HTTP();
		PrintWriter stdOut = vastus.getWriter();
		try {
			ResultSet rsAll = sqlGetAll(stdOut);
			action = kysimus.getParameter("action");
			if (action.compareTo("mobile") != 0) {
				h.header(vastus);
				header = true;
			}
			if (action.compareTo("show") == 0)
				h.tShow(rsAll, stdOut, stdOut, false);
			else if (action.compareTo("mobile") == 0) {
				mobile = true;
				h.tShow(rsAll, stdOut, stdOut, true);
			}
			else if (action.compareTo("showxml") == 0) {
				XML x = new XML();
				ResultSet rs = sqlGetAll(stdOut);
				x.writeXML(rs, stdOut, stdOut);
			}
			done = true;
		}
		catch (Exception e) {}

		if (!header && !mobile)
			h.header(vastus);
		if (!done) {
			h.fInsert(stdOut);
			stdOut.println("<p><a href='Foorum?action=show'>N&auml;ita: tabel</a>");
			stdOut.println("<p><a href='Foorum?action=showxml'>N&auml;ita: XML</a>");
			stdOut.println("<p><a href='Foorum?action=mobile'>N&auml;ita: mobiil</a>");
		}
		else if (!mobile)
			stdOut.println("<p><a href='Foorum'>Tagasi</a>\n");
		if (!mobile)
			h.footer(vastus);
	}

	public void doPost(HttpServletRequest kysimus, HttpServletResponse vastus) throws IOException, ServletException {
		SQL s = new SQL();
		HTTP h = new HTTP();
		h.header(vastus);
		PrintWriter stdOut = vastus.getWriter();
		String name = kysimus.getParameter("name");
		String message = kysimus.getParameter("message");
		if (name != null && message != null) {
			Date date = new Date();
			java.sql.Date sqlDate = new java.sql.Date(date.getTime());
			Time sqlTime = new Time(date.getTime());
			String sqlDateTime = sqlDate.toString() + " " + sqlTime.toString();

			// kui kasutajat veel andmebaasis pole, siis ta lisatakse sinna
			String userID = s.get(sqlBases[1], "id", "where name = '" + name + "'", stdOut);
			if (userID == null)
				s.say("insert into " + sqlBases[1] + " (name) values ('" + name + "');", stdOut);
			userID = s.get(sqlBases[1], "id", "where name = '" + name + "'", stdOut);

			// lisatakse uus teade
			s.say("insert into " + sqlBases[2] + " (date, text) values ('" + sqlDateTime + "', '" + message + "');", stdOut);
			String messageID = s.get(sqlBases[2], "id", "order by id desc", stdOut);

			// lisatakse andmed yldisesse andmetabelisse
			if (messageID != null && userID != null)
				s.say("insert into " + sqlBases[0] + " (user, message) values ('" + userID + "', '" + messageID + "');", stdOut);
		}
		stdOut.println("<p><a href='Foorum'>Tagasi</a>");
		ResultSet rs = sqlGetAll(stdOut);
		h.tShow(rs, stdOut, stdOut, false);
		stdOut.println("</body></html>");
	}

	public ResultSet sqlGetAll (PrintWriter err) {
		SQL s = new SQL();
		ResultSet rs = s.query("select * from " + sqlBases[0] + " " +
				"left join " + sqlBases[1] + " on (" + sqlBases[0] + ".user = " + sqlBases[1] + ".id) " +
				"left join " + sqlBases[2] + " on (" + sqlBases[0] + ".message = " + sqlBases[2] + ".id)",
			err
		);
		return rs;
	}
}

class SQL {
	String sqlHost = "localhost", sqlBase = "tpu", sqlUser = "tpu", sqlPswd = "pedaraisk";

	public String get(String where, String what, String params, PrintWriter err) {
		String sReturn = null;
		if (params == null)
			params = "where 1";
		ResultSet rs = query("select " + what + " from " + where + " " + params + " limit 1;", err);
		try {
			rs.next();
			sReturn = rs.getString(what);
		}
		catch (Exception e) {}
		return sReturn;
	}

	public Connection connect (PrintWriter err) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection dbC = DriverManager.getConnection("jdbc:mysql://" + sqlHost + "/" + sqlBase, sqlUser, sqlPswd); 
			return dbC;
		}
		catch (Exception e) {
			err.println("<p><b>connect exception:<br><pre>");
			e.printStackTrace(err);
			err.println("</pre></b>");
			return null;
		}
	}

	public ResultSet query (String query, PrintWriter err) {
		try {
			Connection dbC = connect(err);
			//Statement dbS = dbC.createStatement();
			//ResultSet ret = dbS.executeQuery(query);
			PreparedStatement dbPs = dbC.prepareStatement(query);
			ResultSet ret = dbPs.executeQuery();
			dbC.close();
			return ret;
		}
		catch (Exception e) {
			err.println("<p><b>query exception:<br><pre>");
			e.printStackTrace(err);
			err.println("</pre></b>");
			return null;
		}
	}

	
	public void say (String expression, PrintWriter err) {
		try {
			Connection dbC = connect(err);
			Statement dbS = dbC.createStatement();
			dbS.executeUpdate(expression);
			dbC.close();
		}
		catch (Exception e) {
			err.println("<p><b>say exception:<br><pre>");
			e.printStackTrace(err);
			err.println("</pre></b>");
		}
	}
}

class HTTP {
	public void fInsert (PrintWriter out) {
		out.println(
			"<form method='post'>\n" +
			"<table><tr>\n" +
			"<td>Kes:</td><td><input type='text' name='name' /></td>\n" +
			"</tr><tr>\n" +
			"<td>Tekst:</td><td><textarea name='message' cols='60' rows='25'></textarea></td>\n" +
			"</tr><tr>\n" +
			"<td></td><td><input type='submit' value='Sisesta' />\n" +
			"</tr></table>\n" +
			"</form>"
		);
	}

	public void tShow (ResultSet rs, PrintWriter out, PrintWriter err, boolean mobile) throws IOException {
		if (!mobile)
			out.println("<table border='1'><tr style='font-weight: bold;'><td>Nimi</td><td>Kuup&auml;ev</td><td>Tekst</td></tr>");
		try {
			while(rs.next()) {
				String[] s = rs2str (rs, out);
				if (mobile) {
					out.println(s[3] + ":::");
					out.println(s[4] + ", " + s[5] + ":::");
					out.println(s[6] + ":::");
				}
				else {
					out.println("<tr>\n" +
						"<td>" + s[3] + "</td>\n" +
						"<td align='center'>" + s[4] + "<br>" + s[5] + "</td>\n" +
						"<td>" + s[6] + "</td>\n" +
						"</tr>"
					);
				}
			}
		}
		catch (Exception e) {
			if (!mobile) {
				err.println("<p><b>tShow exception:<br><pre>");
				e.printStackTrace(err);
				err.println("</pre></b>");
			}
		}
		if (!mobile)
			out.println("</table>");
	}

	public String[] rs2str (ResultSet rs, PrintWriter err) throws IOException {
		String[] sReturn = new String[7];
		try {
			String[] sqlDate = rs.getString("date").split(" ");
			sReturn[0] = rs.getString("id"); // user's id
			sReturn[1] = rs.getString("user"); // user's id
			sReturn[2] = rs.getString("message"); // message's id
			sReturn[3] = rs.getString("name");
			sReturn[4] = sqlDate[0];
			sReturn[5] = sqlDate[1];
			sReturn[6] = rs.getString("text");
		}
		catch (Exception e) {
			err.println("<p><b>rs2str exception:<br><pre>");
			e.printStackTrace(err);
			err.println("</pre></b>");
		}
		return sReturn;
	}

	public void header (HttpServletResponse vastus) throws IOException {
		vastus.setContentType("text/html");
		PrintWriter out = vastus.getWriter();
		out.println("<html>\n<head><title>Foorum</title></head>\n<body>");
	}

	public void footer (HttpServletResponse vastus) throws IOException {
		PrintWriter out = vastus.getWriter();
		out.println("</body></html>");
	}
}

class XML {
	public void writeXML (ResultSet rs, PrintWriter out, PrintWriter err) throws IOException {
		HTTP h = new HTTP();
		try {
			String[] maps = { "id", "user", "message", "name", "date", "time", "text" };
			Document d = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
			Element root = d.createElement("foorum");
			d.appendChild(root);
			while (rs.next()) {
				String[] s = h.rs2str(rs, err);
				Element[] e = new Element[7];
				Element el = d.createElement("kirje");
				for (int i = 0; i < 7; i++) {
					e[i] = d.createElement(maps[i]);
					e[i].appendChild(d.createTextNode(s[i]));
					el.appendChild(e[i]);
				}
				root.appendChild(el);
			}
			Transformer t=TransformerFactory.newInstance().newTransformer();
			StringWriter sw = new StringWriter();
			t.transform(new DOMSource(d), new StreamResult(sw));
			out.println("<pre>" + sw.toString().replaceAll(">", "&gt;\n").replaceAll("<", "\n&lt;") + "</pre>");
		}
		catch (Exception e) {
			err.println("<p><b>writeXML exception:<br><pre>");
			e.printStackTrace(out);
			err.println("</pre></b>");
		}
	}
}