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

public class Turism extends HttpServlet {
	String[] sqlDataNames = { "id", "nimi", "url", "url_pilt", "maakond" };
	StringReader srXSL;

	public void doGet(HttpServletRequest kysimus, HttpServletResponse vastus) throws IOException, ServletException {
		PrintWriter stdOut = vastus.getWriter();
		srXSL = new StringReader(
			"<?xml version=\"1.0\"?>" +
			"<xsl:stylesheet xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" version=\"1.0\">" +
			"<xsl:template match=\"/\">" +
			"<table border='1'>" +
			"<xsl:for-each select=\"Turismitalud/kirje\">" +
			"<tr>" +
			"<td><xsl:value-of select=\"nimi\" /></td>" +
			"<td><xsl:value-of select=\"url\" /></td>" +
			"</tr>" +
			"</xsl:for-each>" +
			"</table>" +
			"</xsl:template>" +
			"</xsl:stylesheet>"
		);

		boolean done = false;
		try {
			String action = kysimus.getParameter("action");
			if (action.compareTo("mobile") != 0)
				header(vastus);
			if (action.compareTo("show") == 0) {
				tShow(stdOut);
			}
			else if (action.compareTo("mobile") == 0) {
				try {
					String show = kysimus.getParameter("show");
					if (show.compareTo("ALL") == 0) {
						ResultSet rs = sqlQuery("select * from turismitalud");
						while (rs.next())
							stdOut.println(rs.getString(sqlDataNames[4])); // maakond
					}
					else {
						ResultSet rs = sqlQuery("select * from turismitalud where maakond = '" + show + "'");
						while (rs.next())
							stdOut.println(rs.getString(sqlDataNames[1])); // nimi
					}
				}
				catch (Exception e) {
					stdOut.println("Parameeter 'show' jäi panemata!");
					e.printStackTrace();
				}
			}
			else if (action.compareTo("xml") == 0) {
				String[] sOut = new String[255];
				int i = 0;
				ResultSet rs = sqlQuery("select * from turismitalud");
				while (rs.next())
					for (int j = 0; j < sqlDataNames.length; j++)
						sOut[i++] = rs.getString(sqlDataNames[j]);
				XML x = new XML();
				StringWriter sw = x.get(sOut, sqlDataNames);
				stdOut.println("<pre>" + sw.toString().replaceAll(">", "&gt;\n").replaceAll("<", "\n&lt;") + "</pre>");
			}
			else if (action.substring(0, 3).compareTo("xsl") == 0) {
				String[] sOut = new String[255];
				int i = 0;
				ResultSet rs = sqlQuery("select * from turismitalud");
				while (rs.next())
					for (int j = 0; j < sqlDataNames.length; j++)
						sOut[i++] = rs.getString(sqlDataNames[j]);
				XML xml = new XML();
				StringWriter swXML = xml.get(sOut, sqlDataNames);
				XSL xsl = new XSL();
				StringWriter swXSL = xsl.get(srXSL, new StringReader(swXML.toString()));
				if (swXSL != null) {
					String sOutput = swXSL.toString();
					if (action.compareTo("xslsrc") == 0)
						sOutput = sOutput.replaceAll(">", "&gt;\n").replaceAll("<", "\n&lt;");
					stdOut.println(sOutput);
				}
				else
					stdOut.println("Tagastud XML oli null");
			}
			if (action.compareTo("mobile") != 0) {
				stdOut.println("<p><a href='Turism'>Tagasi</a>\n");
				footer(vastus);
			}
			done = true;
		}
		catch (Exception e) { e.printStackTrace(); }
		if (!done) {
			stdOut.println("<p><a href='Turism?action=show'>Tabel</a>\n");
			stdOut.println("<p><a href='Turism?action=xml'>XML</a>\n");
			stdOut.println("<p><a href='Turism?action=xsl'>XSL tabel</a>\n");
			stdOut.println("<p><a href='Turism?action=xslsrc'>XSL sorts</a>\n");
			stdOut.println("<p><a href='Turism?action=mobile'>Mobiil</a>\n");
		}
	}

	public Connection sqlConnect () {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection dbC = DriverManager.getConnection("jdbc:mysql://localhost/tpu", "tpu", "pedaraisk"); 
			return dbC;
		}
		catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public ResultSet sqlQuery (String query) {
		try {
			Connection dbC = sqlConnect();
			Statement dbS = dbC.createStatement();
			ResultSet ret = dbS.executeQuery(query);
			dbC.close();
			return ret;
		}
		catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	
	public void tShow (PrintWriter out) throws IOException {
		ResultSet rs = sqlQuery ("select * from turismitalud");
		out.println("<table border='1'><tr style='font-weight: bold;'><td>ID</td><td>Nimi</td><td>URL</td><td>Pildi URL</td><td>Maakond</td></tr>\n");
		try {
			while(rs.next()) {
				out.println("<tr>\n" +
					"<td>" + rs.getString("id") + "</td>\n" +
					"<td>" + rs.getString("nimi") + "</td>\n" +
					"<td>" + rs.getString("url") + "</td>\n" +
					"<td>" + rs.getString("url_pilt") + "</td>\n" +
					"<td>" + rs.getString("maakond") + "</td>\n" +
					"</tr>\n"
				);
			}
		}
		catch (Exception e) { e.printStackTrace(); }
		out.println("</table>\n");
	}

	public int sizeOf (String[] s) {
		int iReturn = 0;
		for (int i = 0; i < s.length; i++) {
			if (s[i] == null) {
				iReturn = i;
				break;
			}
		}
		return iReturn;
	}

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

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

class XML {
	public StringWriter get(String[] data, String[] data_names) throws IOException {
		int cut = data_names.length;
		int data_length = 0;
		for (int i = 0; i < data.length; i++) {
			if (data[i] == null) {
				data_length = i;
				break;
			}
		}
		try {
			Document d = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
			Element root = d.createElement("Turismitalud");
			d.appendChild(root);
			Element[] e = new Element[cut];
			int j = 0;
			for (j = 0; j < data_length / cut; j++) {
				Element el = d.createElement("kirje");
				for (int i = 0; i < cut; i++) {
					e[i] = d.createElement(data_names[i]);
					e[i].appendChild(d.createTextNode(data[i + (j * cut)]));
					el.appendChild(e[i]);
				}
				root.appendChild(el);
			}
			Transformer t = TransformerFactory.newInstance().newTransformer();
			StringWriter swReturn = new StringWriter();
			t.transform(new DOMSource(d), new StreamResult(swReturn));
			return swReturn;
		}
		catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}

class XSL {
	public StringWriter get(StringReader srXSL, StringReader srXML) throws Exception {
		try {
			Transformer t = TransformerFactory.newInstance().newTransformer(new StreamSource(srXSL));
			StringWriter swReturn = new StringWriter();
			t.transform(new StreamSource(srXML), new StreamResult(swReturn));
			return swReturn;
		}
		catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}