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

import HTML.*;
import ConnectionPool.*;

/*
    This servlet displays a list of titles either from a
    category or title search.  Each of the titles in stock
    present the user with the option to order them.  This
    servlet inherits its doGet() method from CatalogServlet.
*/

public class TitleListServlet extends CatalogServlet {

  // Method Name: init()
  // Purpose: This is the default init() method.
  public void init(ServletConfig config)
    throws ServletException {

    super.init(config);
  }

  // Method Name: selectByCategory()
  // Purpose: This method takes the passed in category_id
  // and performs a database lookup returning the ResultSet
  // from the query.
  private ResultSet selectByCategory(int category_id) {

    Connection con = null;
    ConnectionPool pool = null;
    ResultSet rs = null;
    
    try {

      // Get a reference to the ConnectionPool from the Global
      // ServletContext
      ServletContext context = getServletContext();
      // Get a connection from the ConnectionPool
      if ( context == null ) {

        throw new ServletException("Could not get a " +
          "reference to the ServletContext");
      }
      pool =(ConnectionPool)
        context.getAttribute("CONNECTION_POOL");

      // Get a connection from the ConnectionPool
      if ( pool == null ) {

        throw new ServletException("Could not get reference" +
          " to the CONNECTION_POOL");
      }
      con = pool.getConnection();
      // Get a connection from the ConnectionPool
      if ( con == null ) {

        throw new ServletException("Could not get reference" +
          " Connection.");
      }
      if ( con != null ) {

        // Create the statement
        Statement statement = con.createStatement();

        // Use the created statement to SELECT the DATA
        // FROM the Titles Table.
        rs = statement.executeQuery("SELECT * " +
         "FROM Titles Where category_id = " + category_id);
      }
    }
    catch (SQLException sqle) {

      System.err.println(sqle.getMessage());
    }
    catch (Exception e) {

      System.err.println(e.getMessage());
    }
    finally {

      // Release the connection
      pool.releaseConnection(con);
    }
    return rs;
  }

  // Method Name: selectBySearchString()
  // Purpose: This method takes the passed in search_string
  // and performs a database lookup returning the ResultSet
  // from the query.
  private ResultSet
    selectBySearchString(String search_string) {

    Connection con = null;
    ConnectionPool pool = null;
    ResultSet rs = null;

    try {

      // Get a reference to the ConnectionPool from the Global
      // ServletContext
      ServletContext context = getServletContext();
      // Get a connection from the ConnectionPool
      if ( context == null ) {

        throw new ServletException("Could not get a " +
          "reference to the ServletContext");
      }
      pool =(ConnectionPool)
        context.getAttribute("CONNECTION_POOL");

      // Get a connection from the ConnectionPool
      if ( pool == null ) {

        throw new ServletException("Could not get reference" +
          " to the CONNECTION_POOL");
      }
      con = pool.getConnection();
      // Get a connection from the ConnectionPool
      if ( con == null ) {

        throw new ServletException("Could not get reference" +
          " Connection.");
      }
      if ( con != null ) {

        // Create the statement
        Statement statement = con.createStatement();

        // Use the created statement to SELECT the DATA
        // FROM the Titles Table.
        rs = statement.executeQuery("SELECT * " +
         "FROM Titles Where title_name = \'" +
         search_string + "\'");
      }
    }
    catch (SQLException sqle) {

      System.err.println(sqle.getMessage());
    }
    catch (Exception e) {

      System.err.println(e.getMessage());
    }
    finally {

      // Release the connection
      pool.releaseConnection(con);
    }
    return rs;
  }

  // Method Name: buildClientArea()
  // Purpose: This method implements its parents abstract
  // method.  It represents the client area of the browser
  // window.
  public HTMLTable
    buildClientArea(HttpServletRequest request)
    throws Exception {

    // Create the Table to return as the client area.
    HTMLTable table = new HTMLTable();
    ResultSet rs = null;
    table.setHorizontalAlign(HTMLObject.LEFT);
    table.setVerticalAlign(HTMLObject.TOP);
    table.setWidthByPixel(650);

    // Check for the parameter "category_id"
    String category_id = request.getParameter("category_id");
    if ( category_id != null ) {

      // If it is found perform the category_id select
      rs = selectByCategory(
        new Integer(category_id).intValue());
    }
    else {

      String search_string =
        request.getParameter("search_string");
      // If it is not found perform the title select
      rs = selectBySearchString(search_string);
    }
    // Iterate over the ResultSet
    try {

      if ( rs != null ) {

        // This value is only used to switch the background
        // of the Title List.
        boolean flag = true;

        while ( rs.next() ) {

          HTMLTableRow row = new HTMLTableRow();

          // Switch every other row to lightgrey
          if ( flag ) {

            row.setBackgroundColor("lightgrey");
            flag = false;
          }
          else {

            flag = true;
          }

          // Get the values from the ResultSet
          String id = new Integer(
            rs.getInt("title_id")).toString();

          String name = rs.getString("title_name");

          String price = new Double(
            rs.getDouble("price")).toString();

          int quantity = rs.getInt("quantity");

          // Add the Name value to the table cell
          HTMLTableCell cell =
            new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(new HTMLText(name));
          row.addObject(cell);

          // Add the price value to the table cell
          cell =  new HTMLTableCell(HTMLTableCell.DATA);
          cell.setWidth(55);
          cell.addObject(new HTMLText(price));
          row.addObject(cell);

          if ( quantity == 0 ) {

            // If the quantity is 0, display the text
            // "Out of Stock"
            cell =  new HTMLTableCell(HTMLTableCell.DATA);
            cell.setWidth(145);
            cell.addObject(new HTMLText("Out of Stock"));
            row.addObject(cell);
          }
          else {

            // Otherwise create a link to the AddMovieServlet
            // with the id of the selected movie.
            // We are adding the extra parameter info in order
            // to redirect the browser to the List of Titles
            // before the selection.
            cell =  new HTMLTableCell(HTMLTableCell.DATA);
            cell.setWidth(145);
            
            String current_url =
              request.getServletPath() + "?" +
              request.getQueryString();

            cell.addObject(
              new HTMLLink("/servlet/AddMovieServlet?id=" +
              id + "&price=" + price + "&trans=" + current_url,
              new HTMLText("Buy")));
            row.addObject(cell);
          }
          table.addObject(row);
        }
      }
      else {

        HTMLTableRow row = new HTMLTableRow();
        HTMLTableCell cell =
          new HTMLTableCell(HTMLTableCell.DATA);

        // The result was empty, probably an error.
        cell.addObject(new HTMLText("The ResultSet was null," +
          " please contact technical support."));
        row.addObject(cell);
        table.addObject(row);
      }
    }
    finally {

      // Close the ResultSet
      if ( rs != null ) {

        rs.close();
      }
    }
    return table;
  }

  //Get Servlet information
  public String getServletInfo() {

    return "TitleListServlet Information";
  }
}