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


/**
 * This implements the "Custom Search" feature on the site.
 * It reads in the parameters, and then constructs an
 * SQL query based on how the user selects the drop-down
 * menus and the values the user enters for the query.
 * After that, it uses a fairly simple join and projection
 * algorithm
 */

public class AdvancedSearch extends LitsearchBase {

    public String drawPage(HttpServletRequest request,
			   HttpServletResponse response) throws Exception {
	String text = "";
	String[] stripes = new String[2];
	stripes[0] = "<tr bgcolor='#e0e0e0'>";
	stripes[1] = "<tr bgcolor='#ffffff'>";
	int curColor = 0;
	text += "<table cellpadding='4' cellspacing='1' width='100%'>";
	text += stripes[curColor % 2] + "<td colspan=2>";
	text += "Found the following results:</td></tr>\n";
	text += stripes[curColor++ %2] + 
	    "<td>Title</td><td>Author</td></tr>\n";

	//Get the parameters from the user
	String field1 = request.getParameter("f1");
	String field2 = request.getParameter("f2");
	String field3 = request.getParameter("f3");
	String mod1 = request.getParameter("m1");
	String mod2 = request.getParameter("m2");
	String mod3 = request.getParameter("m3");
	String val1 = request.getParameter("v1");
	String val2 = request.getParameter("v2");
	String val3 = request.getParameter("v3");

	String con1 = request.getParameter("c1");
	String con2 = request.getParameter("c2");

	if (val1 == null || field1.equals("--")) {
	    Template t = new Template(TEMPLATE_DIR + "advancedsearch.html");
	    return t.toString();
	}


	//Construct 2 possible queries -- one if they are searching 
	//criticism, the other if they are not.
	String queryFront1 = 
	    "SELECT w.title, w.id, b.name " +
	    "FROM Work w, WrittenBy b, Criticism c, WorkCriticized z " +
	    "WHERE w.id = b.work AND w.id = z.work AND c.url = z.url ";

	String queryFront2 = 
	    "SELECT w.title, w.id, b.name " +
	    "FROM Work w, WrittenBy b " +
	    "WHERE w.id = b.work ";


	//Construct the "meat" of the query based on user responses
	String query = "";
	if (!(field1.equals("--"))) {
	    query += " AND (";
	    if (mod1.equals("contains")) {
		query += field1 + " LIKE " + formatString("%" + val1 + "%");
	    } else if (mod1.equals("starts")) {
		query += field1 + " LIKE " + formatString(val1 + "%");
	    } else {
		query += field1 + " = " + formatString(val1);
	    }

	if (!(field2.equals("--"))) {
	    query += " " + con1 + " ";
	    if (mod1.equals("contains")) {
		query += field2 + " LIKE " + formatString("%" + val2 + "%");
	    } else if (mod1.equals("starts")) {
		query += field2 + " LIKE " + formatString(val2 + "%");
	    } else {
		query += field2 + " = " + formatString(val2);
	    }
	}

	if (!(field3.equals("--"))) {
	    query += " " + con2 + " ";
	    if (mod1.equals("contains")) {
		query += field3 + " LIKE " + formatString("%" + val3 + "%");
	    } else if (mod1.equals("starts")) {
		query += field3 + " LIKE " + formatString(val3 + "%");
	    } else 
		query += field3 + " = " + formatString(val3);
	    }
	}
	query += ")";

	ConnectionWrapper wrapper = null;
	try {
	    //Get a connection from the pool
	    wrapper = DatabaseHook.getConnection();
	    Connection c = wrapper.connection();
	    Statement stmt = c.createStatement();
	    ResultSet rs;	
	    
	    //Execute the query
	    if (!field1.equals("c.title") && !field2.equals("c.title") && !field3.equals("c.title")) {
		rs = stmt.executeQuery(queryFront2 + query);
	    } else {
		rs = stmt.executeQuery(queryFront1 + query);
	    }

	    //Print out the result in tabular format
	    while (rs.next()) {
		text += stripes[curColor++ % 2];
		text += "<td><a href='DescribeWork?work=" + rs.getInt("id") + "'>" + 
		    rs.getString("title") + "</a></td>";
		text += "<td>by <a href='DescribeAuthor?name=" + 
		    URLEncoder.encode(rs.getString("name")) + "'>" + rs.getString("name") + "</a></td>";
	    }

	    text += stripes[curColor++ % 2];
	    text += "<td colspan=2><a href='AdvancedSearch'>Search Again</a>\n";
	    text += "</td></tr></table>";
	    
	    wrapper.checkIn();
	    return text;

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

}