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

/**
 * This class implements all of the administrative functions
 * such as adding and editing author definitions and adding
 * literary criticism (reviews).
 */

public class Admin extends LitsearchBase {

    public String drawPage(HttpServletRequest request,
			   HttpServletResponse response) throws Exception {
	String func = request.getParameter("function");
	if (func == null) func = "";
	String text = "";

	//Dispatch based on value of the "function" parameter
	if (func.equals("addreview")) {
	    text += addReview(request);
	} else if (func.equals("addauthor")) {
	    text += addAuthor(request);
	} else if (func.equals("editauthor")) {
	    text += editAuthor(request);
	} else if (func.equals("delwb")) {
	    delWrittenBy(request);
	    text += editAuthor(request);
	} else if (func.equals("addwb")) {
	    addWrittenBy(request);
	    text += editAuthor(request);
	} else {
	    //Can't figure out what they want to do. Show the welcome screen.
	    text += "<font size=4>Welcome to the administrator control panel.</font><br><br>";
	    text += "<b>Please select a function:</b><br>\n";
	    text += "<a href='Admin?function=addreview'>Add a critical analysis</a><br>\n";
	    text += "<a href='Admin?function=addauthor'>Add an author record</a><br>\n";
	    text += "<a href='Admin?function=editauthor'>Edit an author record</a><br>\n";
	    text += "<a href='Front'>Exit</a><br>\n";
	}
	return text;
    }


    /**
     * Adds a piece of criticism to the database -- it gets input from
     * the user, and stores it in a review record.
     */
    private String addReview(HttpServletRequest request) throws Exception {
	String title =       request.getParameter("title");
	String critic =      request.getParameter("critic");
	String url =         request.getParameter("url");
	String synopsis =    request.getParameter("synopsis");
	String publication = request.getParameter("publication");
	String work =        request.getParameter("work");
	
	//If there's not enough data to do anything, present the user with 
	//an editing form
	if (title == null || critic == null || url == null || synopsis == null ||
	    publication == null || work == null || title.length() < 3) {

	    if (title == null) title = "";
	    if (critic== null) critic = "";
	    if (url == null) url = "";
	    if (synopsis == null) synopsis = "";
	    if (publication == null) publication = "";
	    if (work == null) work = "";
	    if (title == null) title = "";

	    Template t = new Template(TEMPLATE_DIR + "editreview.html");
	    t.substitute("MESSAGE", "Please fill out all of the fields below:");
	    t.substitute("TITLE", title);
	    t.substitute("CRITIC", critic);
	    t.substitute("URL", url);
	    t.substitute("SYNOPSIS", synopsis);
	    t.substitute("PUBLICATION", publication);
	    t.substitute("WORK", work);
	    return t.toString();
	}

	//Otherwise insert the criticism.
	ConnectionWrapper wrapper = null;
	try {
	    wrapper = DatabaseHook.getConnection();
	    Connection c = wrapper.connection();
	    Statement stmt = c.createStatement();
	    String update = "INSERT INTO Criticism VALUES (" +
		formatString(title) + ", " +
		formatString(critic) + ", " + 
		formatString(url) + ", " + 
		formatString(synopsis) + ", " +
		formatString(publication) + ", '')";
	    stmt.executeUpdate(update);

	    ArrayList idsToAdd = new ArrayList();
	    ResultSet rs = stmt.executeQuery
		("SELECT id FROM Work WHERE title LIKE '" + work + "%'");
	    while (rs.next()) {
		idsToAdd.add(rs.getString(1));
	    }
	    for (int i = 0; i < idsToAdd.size(); i++) {
		update = "INSERT INTO WorkCriticized VALUES (" +
		    formatString(url) + ", " + 
		    (String) idsToAdd.get(i) + ")";
		stmt.executeUpdate(update);
	    }
	    wrapper.checkIn();
	    return "Successfully added the analysis entitled: " + title + "<br>\n" +
		"Attached analysis to " + idsToAdd.size() + " works.<br>\n";


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

    /**
     * Adds an author record to the database.
     */
    private String addAuthor(HttpServletRequest request) throws Exception {
	String name =   request.getParameter("name");
	String birth =  request.getParameter("birth");
	String death =  request.getParameter("death");
	String desc =   request.getParameter("description");
	
	if (name == null || birth == null || death == null || desc == null ||
	    name.length() < 3) {
	    if (name == null) name = "";
	    if (birth == null) birth = "";
	    if (death == null) death= "";
	    if (desc == null) desc = "";

	    Template t = new Template(TEMPLATE_DIR + "editauthor.html");
	    t.substitute("MESSAGE", "Please fill out the following information about the author:");
	    t.substitute("NAME", name);
	    t.substitute("BIRTH", birth);
	    t.substitute("DEATH", death);
	    t.substitute("DESCRIPTION", desc);
	    t.substitute("WORKS", "");
	    return t.toString();
	}

	ConnectionWrapper wrapper = null;
	try {
	    wrapper = DatabaseHook.getConnection();
	    Connection c = wrapper.connection();
	    Statement stmt = c.createStatement();
	    if (birth.equals("")) birth = "0";
	    if (death.equals("")) death = "0";

	    String update = "INSERT INTO Author VALUES (" +
		formatString(name) + ", " + birth + ", " + death + ", " +
		formatString(desc) + ")";
	    stmt.executeUpdate(update);
	    wrapper.checkIn();
	    return "Successfully added the author: " + name + " to the database.<br>\n";

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

    /**
     * Edits an author record in the database
     */
    private String editAuthor(HttpServletRequest request) throws Exception {
	String name =   request.getParameter("name");
	String birth =  request.getParameter("birth");
	String death =  request.getParameter("death");
	String desc =   request.getParameter("description");
	String text = "";

	if (name == null || name.length() < 3) {
	    text += "<FORM METHOD='Post'>\n";
	    text += "Enter the name of the author you want to edit (last name first):<br>\n";
	    text += "<INPUT TYPE='text' SIZE='30' NAME='name'>";
	    text += "<INPUT TYPE='submit' VALUE='Edit'>\n</FORM>\n";
	    return text;
	}

	ConnectionWrapper wrapper = null;
	try {
	    wrapper = DatabaseHook.getConnection();
	    Connection c = wrapper.connection();
	    Statement stmt = c.createStatement();
	    String update;
	    Template t = new Template(TEMPLATE_DIR + "editauthor.html");

	    if (name != null && birth != null && death != null && desc != null &&
		name.length() >= 3) {
		update = "UPDATE Author SET birth=" + birth + ", death=" +
		    death + ", description=" + formatString(desc) +
		    " WHERE name=" + formatString(name);
		stmt.executeUpdate(update);
		t.substitute("MESSAGE", "Your changes were successfully saved. You may make additional edits, if you would like.");
		t.substitute("NAME", name, true);
		t.substitute("BIRTH", birth);
		t.substitute("DEATH", death);
		t.substitute("DESCRIPTION", desc);

	    } else {
		ResultSet rs = stmt.executeQuery("SELECT * FROM Author WHERE name like " +
						 formatString(name + "%"));
		if (rs.next()) {
		    name = rs.getString("name");
		    birth = rs.getString("birth");
		    death = rs.getString("death");
		    desc = rs.getString("description");
		}
		t.substitute("MESSAGE", "Please make any changes to the fields below:");
		t.substitute("NAME", name, true);
		t.substitute("BIRTH", birth);
		t.substitute("DEATH", death);
		t.substitute("DESCRIPTION", desc);
	    }

	    ResultSet rs = stmt.executeQuery
		("SELECT Work.id, Work.title FROM Work, WrittenBy " +
		 "WHERE Work.id = WrittenBy.work " +
		 "AND WrittenBy.name = " + formatString(name));
	    String works = "";
	    while (rs.next()) {
		works += "<a href='Admin?function=delwb&work=" + rs.getString("id") +
		    "&name=" + URLEncoder.encode(name) + "'>[remove]</a> " +
		    rs.getString("title") + "<br>\n";
	    }
	    t.substitute("WORKS", works);
	    wrapper.checkIn();
	    return t.toString();
	    
	} catch (Exception e) {
	    wrapper.checkIn();
	    throw e;
	}
    }


    /**
     * Deletes a WrittenBy tuple from the databse, i.e. says that author X
     * is no longer credited with writing book Y.
     */
    private void delWrittenBy(HttpServletRequest request) throws Exception {
	String work = request.getParameter("work");
	String name = request.getParameter("name");
	if (work != null && name != null) {
	    ConnectionWrapper wrapper = null;
	    try {
		wrapper = DatabaseHook.getConnection();
		Connection c = wrapper.connection();
		Statement stmt = c.createStatement();
		stmt.executeUpdate("DELETE FROM WrittenBy WHERE name=" + 
				   formatString(name) + " AND work=" + work);
		wrapper.checkIn();
	    } catch (Exception e) {
		wrapper.checkIn();
		throw e;
	    }
	}
    }


    /**
     * Adds a WrittenBy tuple to the database, i.e. says that author X
     * is credited with writing book Y.
     */
    private void addWrittenBy(HttpServletRequest request) throws Exception {
	String workName = request.getParameter("work");
	String name = request.getParameter("name");
	if (workName != null && name != null) {
	    ConnectionWrapper wrapper = null;
	    try {
		wrapper = DatabaseHook.getConnection();
		Connection c = wrapper.connection();
		Statement stmt = c.createStatement();

		ResultSet rs = stmt.executeQuery("SELECT id FROM Work WHERE title LIKE " +
						 formatString(workName + "%"));
		ArrayList idsToAdd = new ArrayList();
		while (rs.next()) {
		    idsToAdd.add(rs.getString(1));
		}
		for (int i = 0; i < idsToAdd.size(); i++) {
		    String work = (String) idsToAdd.get(i);
		    stmt.executeUpdate("INSERT INTO WrittenBy VALUES (" + work + ", " +
				       formatString(name) + ")");
		}
		wrapper.checkIn();

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