import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.net.*;


/** 
 * Provides information about a particular literary work from the database.
 */

public class DescribeWork extends LitsearchBase {

    public String drawPage(HttpServletRequest request,
			   HttpServletResponse response) throws Exception {
	int work = -1;
	String workName = "";
	String authorName = "";
	ConnectionWrapper wrapper = null;

	//Try to parse out the work identifier number
	String workStr = request.getParameter("work");
	if (workStr != null) work = Integer.parseInt(workStr);

	//Try to parse out a title
	workName = request.getParameter("title");
	if (workName != null && workName.length() > 1) workName = "%" + workName;
	
	//Couldn't parse any input, show the user the form.
	if (work < 0 && workName == null) {
	    Template t = new Template(TEMPLATE_DIR + "worksearch.html");
	    return t.toString();
	}

	try {
	    //get a connection from the pool
	    wrapper = DatabaseHook.getConnection();
	    Connection c = wrapper.connection();
	    Statement stmt = c.createStatement();
	    String text = "";
	    String query;
	    ResultSet rs;

	    //construct a query based on the title of the work. Join 
	    //with other tables to get author and critical analysis information.
	    //NOTE: This query used to be more interesting prior to cleaning up 
	    //the SQL to preserve MySQL compatibility.
	    //the SELECT COUNT(*)... was a subquery on the main query.
	    if (work < 0) {
		query = "SELECT COUNT(*) FROM Work WHERE title LIKE " + 
		    formatString(workName + "%");
		rs = stmt.executeQuery(query);
		rs.next();
		int count = rs.getInt(1);
		if (count == 0) {
		    text += "Sorry. No works matcing '" + workName.substring(1) + "' were found.";
		    wrapper.checkIn();
		    return text;
		} else  {
		    query = "SELECT w.id id, w.title title, a.name name, a.birth birth, a.death death " +
		        "FROM Work w, WrittenBy r, Author a " +
		        "WHERE w.id = r.work AND a.name = r.name AND w.title LIKE " +		  
			formatString(workName + "%");
		    if (count > 1) {
			if (workName.length() > 1) workName = workName.substring(1);
			text = "<font size=4>Works containing '" + workName + "':<br>";
			rs = stmt.executeQuery(query);
			while(rs.next()) {
			    text += "<br><a href='DescribeWork?work=" + rs.getInt("id") +
				"'>" + rs.getString("title") + "</a>";
			}
			wrapper.checkIn();
			return text;
		    }
		}

		//construct a query based on the work identifier of the work.
	    } else {
		query = 
		    "SELECT w.id id, w.title title, a.name name, a.birth birth, "+
		    "a.death death " +
		    "FROM Work w, WrittenBy r, Author a " +
		    "WHERE w.id = r.work AND a.name = r.name AND w.id =" + work;
	    }

	    //execute the query
	    rs = stmt.executeQuery(query);
	    if (rs.next()) {
		authorName = rs.getString("name");
		workName = rs.getString("title");
		text += "<font size='5'>" + rs.getString("title") + 
		    "</font><br>\n";
		text += "by <a href='DescribeAuthor?name=" +
		    URLEncoder.encode(authorName) + "'>" + authorName + " (" +
		    rs.getString("birth") + "-" + rs.getString("death") + 
		    ")</a><br><br>\n";
	    }

	    text += "<table cellpadding='4' cellspacing='2' width='100%'>";
	    query = "SELECT c.title, c.critic, c.synopsis, c.url, c.publication " +
		    "FROM Criticism c, WorkCriticized w " +
		    "WHERE w.url = c.url AND w.work=" + work;
	    rs = stmt.executeQuery(query);


	    //display the query results in tabular format.
	    boolean firstPass = true;
	    while (rs.next()) {
		if (firstPass) {
		    text += "<tr><td>&nbsp;<br><font size=4>Critical analysis of " + 
			workName + "</font></td></tr>\n";
		    firstPass = false;
		}
		String title = rs.getString("title");
		String critic = rs.getString("critic");
		String synopsis = rs.getString("synopsis");
		String url = rs.getString("url");
		String publication = rs.getString("publication");
		text += "<tr><td bgcolor=#d0d0d0><a href='" + url + "'><b>" + title + 
		    "</b></a></td></tr>\n";
		if (!critic.equals(""))
		    text += "<tr><td bgcolor=#d0d0d0><b>Author:</b> " + critic + "</td></tr>\n";
		text += "<tr><td bgcolor=#d0d0d0><b>Source:</b> " + publication + "</td></tr>\n";
		text += "<tr><td bgcolor=#e8e8e8>" + synopsis + "<br>&nbsp;</td></tr>\n";
		text += "<tr><td bgcolor=#ffffff><img src='spacer.gif' height=1 width=1></td></tr>\n";
	    }
	    text += "</table>";
		

	    wrapper.checkIn();
	    return text;

	} catch (SQLException e) {
	    wrapper.checkIn();
	    throw e;
	}
    }
}