/*
 * DBUtilControllerServlet.java
 *
 * Created on June 19, 2003 San Diego, CA
 *
 * Author: Lawrence Truett
 *
 * Copyright Lawrence Truett and FluffyCat.com
 */

package servlet;

import cart.dbUtils.DBConnection;
import cart.dbUtils.CategoryDBUtil;
import cart.dbUtils.ItemDBUtil;
import cart.dbUtils.ItemCategoryDBUtil;
import cart.dbUtils.ItemGroupDBUtil;
import cart.dbUtils.ItemInventoryDBUtil;
import cart.dbUtils.ItemSizeColorDBUtil;
import java.io.*;

import java.sql.*;

//import java.sql.Connection;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import javax.servlet.*;
import javax.servlet.http.*;

/* DBUtilCOntrollerServlet - runs direct processing of the db
 *    for admin use, not for regular transaction processing
 * author - Lawrence Truett - FluffyCat.com
 * date - June 19, 2003 - San Diego, CA
 **/
public class DBUtilControllerServlet extends HttpServlet {
    
    /** Initializes the servlet.
     */
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        
    }
    
    /** Destroys the servlet.
     */
    public void destroy() {
        
    }
    
    /** Processes requests for both HTTP <code>GET</code> and 
     *     <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     */
    protected void processRequest(HttpServletRequest request, 
                                  HttpServletResponse response)
    throws ServletException, IOException {
        
       /*        
        * Creating a database and user in MySQL 
        *     (change hostname, username, and password):
        *
        *   create database "hostname_-_content";
        *   create user "hostname_username"
        *   grant all on 'hostname_content' to 
        *       'hostname_username'@'localhost' identified by 'password';
        */
        
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        out.println("<html"+">");
        out.println("<head"+"><title"+">DBUtilControllerServlet</title"+"></head"+">");
        out.println("<body"+"><h1"+">DBUtilControllerServlet</h1"+">");
        
        out.println("New DBConnection<br>");
        DBConnection dbc = new DBConnection();
        
        out.println("getConnection<br>");
        Connection connection = dbc.getConnection();

        out.println("after getConnection<br>");
                
        buildAndPopulateCategoryTable(out, connection);
        buildAndPopulateItemSizeColorTable(out, connection);
        buildAndPopulateItemTable(out, connection);
        buildAndPopulateItemCategoryTable(out, connection);
        buildAndPopulateItemInventoryTable(out, connection);
        buildAndPopulateItemGroupTable(out, connection);        

        out.println("<br"+"><b"+">All done!</b"+">");
        out.println("</body"+">");
        out.println("</html"+">");
        
    }
    
    /** Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doGet(HttpServletRequest request, 
                         HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doPost(HttpServletRequest request, 
                          HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Returns a short description of the servlet.
     */
    public String getServletInfo() {
        return "Controller Servlet for DBUtil - " +
               "creates all tables and loads test data, " +
               "copyright Lawrence Truett 2003-2004, all rights reserved";
    }
    
    void buildAndPopulateCategoryTable(PrintWriter out, 
                                       Connection connection) {
        
        out.println("<br><b>category table</b><br>");
        
        out.println("drop table category<br>");
        try {
            PreparedStatement preparedStatement = 
                connection.prepareStatement("DROP TABLE " + 
                                            DBConnection.getDBPrefix() +
                                            "cart.category;");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("create table category<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("CREATE TABLE " + 
                                        DBConnection.getDBPrefix() + 
                                        "cart.category " +
                                        "(category INT NOT NULL, " +
                                        "parent INT NOT NULL, " +
                                        "description VARCHAR(100), "+
                                        "primary key(category));");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("insert into table category<br>");
        
        {
            CategoryDBUtil.insert(
                (long)0,(long)0, "root", connection);
            CategoryDBUtil.insert(
                (long)1,(long)0, "Mollusks", connection);
            CategoryDBUtil.insert(
                (long)2,(long)0, "Cats", connection);
            CategoryDBUtil.insert(
                (long)3,(long)0, "Clothing", connection);
            CategoryDBUtil.insert(
                (long)4,(long)1, "BiValves", connection);
            CategoryDBUtil.insert(
                (long)5,(long)1, "UniValves", connection);
            CategoryDBUtil.insert(
                (long)6,(long)2, "Long Haired", connection);
            CategoryDBUtil.insert(
                (long)7,(long)3, "Hats", connection);
            CategoryDBUtil.insert(
                (long)8,(long)5, "Cowries", connection);
            CategoryDBUtil.insert(
                (long)100,(long)0, "Top Sellers", connection);
        }
        
        out.println("select table category<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from category");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                out.println("Key: " + resultSet.getString(1) +
                            "Parent: " + resultSet.getString(2) + 
                            "Description: " + resultSet.getString(3) + 
                            "<br>");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
    }
    
    void buildAndPopulateItemTable(PrintWriter out, 
                                   Connection connection) {
        
        out.println("<br><b>item table</b><br>");
        
        out.println("drop table item<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("DROP TABLE " + 
                DBConnection.getDBPrefix() + "cart.item;");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("create table item<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "CREATE TABLE " + 
                 DBConnection.getDBPrefix() + 
                 "cart.item (" +
                     "item INT NOT NULL, " +
                     "price DOUBLE NOT NULL, " +
                     "description CHAR(50), " +
                     "type CHAR(5), " +
                     "smallGraphic VARCHAR(25), " +
                     "largeGraphic VARCHAR(25), " +
                     "primary key(item));");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("insert into table item<br>"); {
            ItemDBUtil.insert((long)101,1.01,"button shell",     
                "S1234","115-1572_IMG.JPG", "115-1572_IMG.JPG",connection);
            ItemDBUtil.insert((long)102,1.02,"big cowrie",       
                "S1234","115-1568_IMG.JPG","115-1565_IMG.JPG", connection);
            ItemDBUtil.insert((long)103,1.03,"orange cat pic",   
                "M1234","115-1574_IMG.JPG","115-1574_IMG.JPG", connection);
            ItemDBUtil.insert((long)104,1.04,"himalayan cat pic",
                "M1234","115-1564_IMG.JPG","115-1555_IMG.JPG", connection);
            ItemDBUtil.insert((long)105,1.05,"hat"              ,
                "SC234","115-1564_IMG.JPG","115-1555_IMG.JPG", connection);
            ItemDBUtil.insert((long)106,1.06,"hat"              ,
                "SC234","115-1564_IMG.JPG","115-1555_IMG.JPG", connection);
            ItemDBUtil.insert((long)107,0.00,"hat"              ,
                "G1234","115-1564_IMG.JPG","115-1555_IMG.JPG", connection);
        }
        
        out.println("select table item<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from item");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                out.println("Item No: " + resultSet.getString(1) + 
                            "Price: " + resultSet.getString(2) + 
                            "Description: " + resultSet.getString(3)+ 
                            "<br>");
                out.println("    type: " + resultSet.getString(4) + 
                            "shortImage: " + resultSet.getString(5) + 
                            "longImage: " + resultSet.getString(6) +
                            "<br>");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
    }
    
    void buildAndPopulateItemSizeColorTable(PrintWriter out, 
                                            Connection connection) {
        
        out.println("<br><b>itemSizeColor table</b><br>");
        
        out.println("drop table itemSizeColor<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("DROP TABLE " + 
                                         DBConnection.getDBPrefix() + 
                                        "cart.itemSizeColor;");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("create table itemSizeColorType<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "CREATE TABLE " + DBConnection.getDBPrefix() + 
                    "cart.itemSizeColor (itemKey INT NOT NULL, " +
                                        "size VARCHAR(25) NOT NULL, " + 
                                        "color VARCHAR(25), " +
                                        "primary key(itemKey));");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("insert into table itemSizeColor<br>"); {
            ItemSizeColorDBUtil.insert(
                (long)105,"LARGE","GREEN",connection);
            ItemSizeColorDBUtil.insert(
                (long)106,"LARGE","BLUE",connection);
        }
        
        out.println("select table itemSizeColor<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from itemSizeColor");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                out.println("ItemKey: " + resultSet.getString(1) + 
                            "Color: " + resultSet.getString(2) + 
                            "Size: " + resultSet.getString(3)+ "<br>");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
    }
    
    
    void buildAndPopulateItemCategoryTable(PrintWriter out, 
                                           Connection connection) {
        
        out.println("<br><b>itemCategory table</b><br>");
        
        out.println("drop table itemCategory<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("DROP TABLE " + 
                                        DBConnection.getDBPrefix() + 
                                        "cart.itemCategory;");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("create table itemCategory<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "CREATE TABLE " + 
                    DBConnection.getDBPrefix() + 
                   "cart.itemCategory (item INT NOT NULL, " +
                   "category INT NOT NULL, " +
                   "primary key(item,category));");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("insert into table itemCategory<br>"); {
            ItemCategoryDBUtil.insert((long)101, (long)8, connection);
            ItemCategoryDBUtil.insert((long)101, (long)100, connection);
            ItemCategoryDBUtil.insert((long)102, (long)8, connection);
            ItemCategoryDBUtil.insert((long)103, (long)6, connection);
            ItemCategoryDBUtil.insert((long)104, (long)6, connection);
            ItemCategoryDBUtil.insert((long)104, (long)100, connection);
            ItemCategoryDBUtil.insert((long)107, (long)7, connection);
        }
        
        out.println("select table itemCategory<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from itemCategory");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                out.println("Item No: " + resultSet.getString(1) + 
                            "category: " + resultSet.getString(2) + 
                            "<br>");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
    }
    
    void buildAndPopulateItemGroupTable(PrintWriter out, 
                                        Connection connection) {
        
        out.println("<br><b>itemGroup table</b><br>");
        
        out.println("drop table itemGroup<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("DROP TABLE " + 
                                        DBConnection.getDBPrefix() + 
                                        "cart.itemGroup;");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("create table itemGroup<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "CREATE TABLE " + DBConnection.getDBPrefix() + 
                    "cart.itemGroup (groupKey INT NOT NULL, " +
                    "itemKey INT NOT NULL, " +
                    "primary key(groupKey, itemKey));");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("insert into table itemGroup<br>"); {
            ItemGroupDBUtil.insert((long)107, (long)105, connection);
            ItemGroupDBUtil.insert((long)107, (long)106, connection);
        }
        
        out.println("select table itemGroup<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from itemGroup");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                out.println("Group No: " + resultSet.getString(1) + 
                            "item: " + resultSet.getString(2) + 
                            "<br>");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
    }    
    
    void buildAndPopulateItemInventoryTable(PrintWriter out, 
                                            Connection connection) {
        
        out.println("<br><b>itemInventory table</b><br>");
        
        out.println("drop table itemInventory<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("DROP TABLE " + 
                                        DBConnection.getDBPrefix() + 
                                        "cart.itemInventory;");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("create table itemInventory<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "CREATE TABLE " + DBConnection.getDBPrefix() + 
                    "cart.itemInventory (item INT NOT NULL, " +
                        "inventory INT NOT NULL, " +
                        "reserved INT NOT NULL, " +
                        "primary key(item));");
            preparedStatement.execute();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
        out.println("insert into table itemInventory<br>"); {
            ItemInventoryDBUtil.insert(
                (long)101, (long)2, (long)0, connection);
            ItemInventoryDBUtil.insert(
                (long)102, (long)1, (long)0, connection);
            ItemInventoryDBUtil.insert(
                (long)103, (long)17, (long)0, connection);
            ItemInventoryDBUtil.insert(
                (long)104, (long)5, (long)0, connection);
            ItemInventoryDBUtil.insert(
                (long)105, (long)6, (long)0, connection);
            ItemInventoryDBUtil.insert(
                (long)106, (long)5, (long)0, connection);
        }
        
        out.println("select table itemInventory<br>");
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from itemInventory");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                out.println("Item No: " + resultSet.getString(1) + 
                            "inventory: " + resultSet.getString(2) + 
                            "reserved: " + resultSet.getString(3) + 
                            "<br>");
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            out.println("Exception: " + sqlException + "<br>");
        }
        
    }
    
}