import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Text;
import org.eclipse.swt.SWT;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.widgets.Combo;
import org.eclipse.swt.widgets.List;

import java.util.*;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import java.net.*;
import java.text.*;
import java.lang.*;
import java.io.*;
import java.sql.*;
import java.awt.*;


public class ProjectPart2 {

	public int custID;
	public String selectedBook;
	public String bookTitle4String;
	public String bookYear;
	public String bookLanguage;
	public String bookWeight;
	public String custClub;
	public double minimumPrice;
	protected Shell shell;
	private Text loginText;
	private Text bookTitle;
	private Text lowestPrice;
	private Text quantity;
	private Text totalText;
	private static Connection myConnection;
	private static String url;
	private Text promptText;
	private Label customerInfo;
	private Combo catCombo;
	private List bookList;
	private static Pattern bookInfo = Pattern.compile("Title: (.+) Year: (.+) Language: (.+) Weight: (.+)");
	public DateFormat df = new SimpleDateFormat("yyyy-mm-dd-HH.mm.ss");
	public Calendar calobj = Calendar.getInstance();
	public int quantityI;


	/**
	 * Launch the application.
	 * @param args
	 */
	public static void main(String[] args) {
		try
		{
			//Register the driver with the driver manager.
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
		}
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (InstantiationException e)
		{
			e.printStackTrace();
		}
		catch (IllegalAccessException e)
		{
			e.printStackTrace();
		}

		//Give the url for the db
		url = "jdbc:db2:c3421m";

		try
		{
			myConnection = DriverManager.getConnection(url);
		}
		catch (SQLException e)
		{
			System.out.println("SQL: database connection error.\n");
		}


		try {
			ProjectPart2 window = new ProjectPart2();
			window.open();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * Open the window.
	 */
	public void open() {
		Display display = Display.getDefault();
		createContents();
		shell.open();
		shell.layout();
		while (!shell.isDisposed()) {
			if (!display.readAndDispatch()) {
				display.sleep();
			}
		}
	}

	/**
	 * Create contents of the window.
	 */
	protected void createContents() {
		shell = new Shell();
		shell.setSize(700, 550);
		shell.setText("SWT Application");

		loginText = new Text(shell, SWT.BORDER);
		loginText.setBounds(28, 53, 150, 24);

		Label lblUserId = new Label(shell, SWT.NONE);
		lblUserId.setBounds(28, 27, 69, 21);
		lblUserId.setText("Customer ID");

		Button loginButton = new Button(shell, SWT.NONE);
		loginButton.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				find_customer(Integer.parseInt(loginText.getText()));
			}
		});
		loginButton.setBounds(184, 53, 85, 24);
		loginButton.setText("Login");


		catCombo = new Combo(shell, SWT.NONE);
		catCombo.setBounds(28, 128, 150, 24);

		Label lblCategory = new Label(shell, SWT.NONE);
		lblCategory.setBounds(28, 101, 69, 21);
		lblCategory.setText("Category");

		bookTitle = new Text(shell, SWT.BORDER);
		bookTitle.setBounds(226, 128, 273, 24);

		Label lblBookTitle = new Label(shell, SWT.NONE);
		lblBookTitle.setBounds(226, 101, 69, 21);
		lblBookTitle.setText("Book Title");

		bookList = new List(shell, SWT.BORDER);
		bookList.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				selectedBook = bookList.getItem(bookList.getFocusIndex());
				Matcher m = bookInfo.matcher(selectedBook);
				bookTitle4String = m.group(0);
				bookYear = m.group(1);
				bookLanguage = m.group(2);
				bookWeight = m.group(3);
				min_price();
			}
		});
		bookList.setBounds(28, 188, 555, 125);

		Button btnSearch = new Button(shell, SWT.NONE);
		btnSearch.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				find_book();
			}
		});
		btnSearch.setBounds(518, 127, 85, 26);
		btnSearch.setText("Search");

		Label lblListOfBooks = new Label(shell, SWT.NONE);
		lblListOfBooks.setBounds(28, 161, 85, 21);
		lblListOfBooks.setText("List of Books");

		lowestPrice = new Text(shell, SWT.BORDER);
		lowestPrice.setBounds(589, 232, 85, 24);
		lowestPrice.setEditable(false);

		Label lblLowestPrice = new Label(shell, SWT.NONE);
		lblLowestPrice.setBounds(586, 205, 88, 21);
		lblLowestPrice.setText("Lowest Price");

		quantity = new Text(shell, SWT.BORDER);
		quantity.setBounds(28, 376, 86, 24);


		Label lblWhatIsThe = new Label(shell, SWT.NONE);
		lblWhatIsThe.setBounds(27, 349, 356, 21);
		lblWhatIsThe.setText("Quantity");

		totalText = new Text(shell, SWT.BORDER);
		totalText.setBounds(28, 443, 85, 24);
		totalText.setEditable(false);

		Label lblTotalPrice = new Label(shell, SWT.NONE);
		lblTotalPrice.setBounds(28, 416, 69, 21);
		lblTotalPrice.setText("Total Price");

		Button purchaseBtn = new Button(shell, SWT.NONE);
		purchaseBtn.setBounds(159, 442, 85, 26);
		purchaseBtn.setText("Purchase!");
		purchaseBtn.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				insert_purchase();
			}
		});

		Label lblUserPrompt = new Label(shell, SWT.NONE);
		lblUserPrompt.setBounds(315, 10, 85, 21);
		lblUserPrompt.setText("User Prompt");

		promptText = new Text(shell, SWT.BORDER);
		promptText.setBounds(315, 37, 359, 40);
		promptText.setEditable(false);
		promptText.setText("Enter your customer id and press login to login");

		customerInfo = new Label(shell, SWT.NONE);
		customerInfo.setBounds(381, 402, 293, 94);
		customerInfo.setText("");

		Label lblCustomerInfo = new Label(shell, SWT.NONE);
		lblCustomerInfo.setBounds(381, 376, 118, 21);
		lblCustomerInfo.setText("Customer Info");

		Button quantityButton = new Button(shell, SWT.NONE);
		quantityButton.setBounds(127, 376, 85, 26);
		quantityButton.setText("Select");
		quantityButton.addSelectionListener(new SelectionAdapter() {
			@Override
			public void widgetSelected(SelectionEvent e) {
				quantityI = Integer.parseInt(quantity.getText());
				double price = Double.parseDouble(lowestPrice.getText());
				String totalSPrice = Double.toString(quantityI * price);
				totalText.setText(totalSPrice);
				promptText.setText("Review the price and press the purchase button to purchase your books");

			}
		});


	}

	private void setText(String string) {
		// TODO Auto-generated method stub

	}

	public boolean find_customer(int input) {
		         String            queryText = "";     // The SQL text.
		         PreparedStatement querySt   = null;   // The query handle.
		         ResultSet         answers   = null;   // A cursor.
		         String 		   output;

		         boolean           inDB      = false;  // Return.

		         queryText =
		             "SELECT cid, name, city       "
		           + "FROM yrb_customer "
		           + "WHERE cid = ?     ";

		         // Prepare the query.
		         try {
		             querySt = myConnection.prepareStatement(queryText);
		         } catch(SQLException e) {
		             System.out.println("SQL#1 failed in prepare");
		             System.out.println(e.toString());
		             System.exit(0);
		         }

		         // Execute the query.
		         try {
		             querySt.setInt(1, input);
		             answers = querySt.executeQuery();
		         } catch(SQLException e) {
		             System.out.println("SQL#1 failed in execute");
		             System.out.println(e.toString());
		             System.exit(0);
		         }

		         // Any answer?
		         try {
		             if (answers.next()) {
		                 inDB = true;
		                 output = "cid: " + answers.getString("cid") + "\nName: " + answers.getString("name") + "\nCity: " + answers.getString("city");
		                 customerInfo.setText(output);
		             } else {
		                 inDB = false;
		                 promptText.setText("There is no customer with that id \n Please enter your customer id and try again");;
		             }
		         } catch(SQLException e) {
		             System.out.println("SQL#1 failed in cursor.");
		             System.out.println(e.toString());
		             System.exit(0);
		         }

		         // Close the cursor.
		         try {
		             answers.close();
		         } catch(SQLException e) {
		             System.out.print("SQL#1 failed closing cursor.\n");
		             System.out.println(e.toString());
		             System.exit(0);
		         }

		         // We're done with the handle.
		         try {
		             querySt.close();
		         } catch(SQLException e) {
		             System.out.print("SQL#1 failed closing the handle.\n");
		             System.out.println(e.toString());
		             System.exit(0);
		         }

		         return inDB; }


	public boolean fetch_categories() {
        String            queryText = "";     // The SQL text.
        PreparedStatement querySt   = null;   // The query handle.
        ResultSet         answers   = null;   // A cursor.
        String 		   output;

        boolean           inDB      = false;  // Return.

        queryText =
            "SELECT cat       "
          + "FROM yrb_category ";

        // Prepare the query.
        try {
            querySt = myConnection.prepareStatement(queryText);
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in prepare");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Execute the query.
        try {
            answers = querySt.executeQuery();
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in execute");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Any answer?
        try {
            if (answers.next()) {
                inDB = true;
                catCombo.add(answers.getString("cat"));
                while(answers.next())
                {
                	catCombo.add(answers.getString("cat"));
                }
                promptText.setText("Select a category and enter the title of a book to search for");
            } else {
                inDB = false;
                promptText.setText("There are no categories to display.");
            }
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in cursor.");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Close the cursor.
        try {
            answers.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing cursor.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        // We're done with the handle.
        try {
            querySt.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing the handle.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        return inDB; }


	public boolean find_book() {
        String            queryText = "";     // The SQL text.
        PreparedStatement querySt   = null;   // The query handle.
        ResultSet         answers   = null;   // A cursor.
        String 		   output;

        boolean           inDB      = false;  // Return.

        queryText =
            "SELECT title, year, language, weight     "
          + "FROM yrb_book "
          + "WHERE cat = ? AND title = ?     ";

        // Prepare the query.
        try {
            querySt = myConnection.prepareStatement(queryText);
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in prepare");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Execute the query.
        try {
            querySt.setString(1, catCombo.getText());
            querySt.setString(2, bookTitle.getText());
            answers = querySt.executeQuery();
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in execute");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Any answer?
        try {
            if (answers.next()) {
                inDB = true;
                output = "Title: " + answers.getString("title") + " Year: " + answers.getString("year") + " Language: " + answers.getString("language") + " Weight: " + answers.getString("weight");
                bookList.add(output);
                while(answers.next())
                {
                	output = "Title: " + answers.getString("title") + " Year: " + answers.getString("year") + " Language: " + answers.getString("language") + " Weight: " + answers.getString("weight");
                    bookList.add(output);
                }
            } else {
                inDB = false;
                promptText.setText("There are no books in that category with that title, Please enter a new query and search again.");;
            }
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in cursor.");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Close the cursor.
        try {
            answers.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing cursor.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        // We're done with the handle.
        try {
            querySt.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing the handle.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        return inDB; }


	public boolean min_price() {

        String            queryText = "";     // The SQL text.
        PreparedStatement querySt   = null;   // The query handle.
        ResultSet         answers   = null;   // A cursor.
        String 		   output;

        boolean           inDB      = false;  // Return.

        queryText =
            "SELECT min(price),        "
          + "FROM (SELECT * FROM yrb_offer AS O, (SELECT club FROM yrb_member m WHERE m.cid = ?) AS c "
          + "WHERE O.title = ? AND O.year = ? AND O.club = c.club  )   ";

        // Prepare the query.
        try {
            querySt = myConnection.prepareStatement(queryText);
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in prepare");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Execute the query.
        try {
            querySt.setInt(1, custID);
            querySt.setString(2, bookTitle4String);
            querySt.setString(3, bookYear);
            answers = querySt.executeQuery();
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in execute");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Any answer?
        try {
            if (answers.next()) {
                inDB = true;
                output = answers.getString("1");
                lowestPrice.setText(output);
                minimumPrice = Double.parseDouble(output);
                promptText.setText("Enter the amount of books you want to buy in the quantity text field\n"
                		+ "and then press the select button to calculate total");
            } else {
                inDB = false;
                promptText.setText("Something has gone terribly wrong");
            }
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in cursor.");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Close the cursor.
        try {
            answers.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing cursor.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        // We're done with the handle.
        try {
            querySt.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing the handle.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        return inDB; }


	public boolean insert_purchase() {
        String            queryText = "";     // The SQL text.
        PreparedStatement querySt   = null;   // The query handle.
        int         answers = 0;   // A int.
        String 		   output;

        boolean           inDB      = false;  // Return.

        queryText =
            "INSERT INTO yrb_purchase (cid, club, title, year, when, quantity)       "
          + "VALUES (?, ?, ?, ?, ?, ?)";

        // Prepare the query.
        try {
            querySt = myConnection.prepareStatement(queryText);
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in prepare");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Execute the query.
        try {
            querySt.setInt(1, custID);
            querySt.setString(2, custClub);
            querySt.setString(3, bookTitle4String);
            querySt.setString(4, bookYear);
            querySt.setString(5, df.format(calobj.getTime()));
            querySt.setInt(6, quantityI);
            answers = querySt.executeUpdate();
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in execute");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Any answer?
            if (answers == 1) {
                inDB = true;
                promptText.setText("YOU HAVE SUCCESSFULLY PURCHASED THE BOOKS");
            } else {
                inDB = false;
                promptText.setText("SOMETHING WENT WRONG PURCHASING THE BOOKS");;
            }
        // We're done with the handle.
        try {
            querySt.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing the handle.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        return inDB; }


	public boolean find_club() {
        String            queryText = "";     // The SQL text.
        PreparedStatement querySt   = null;   // The query handle.
        ResultSet         answers   = null;   // A cursor.
        String 		   output;

        boolean           inDB      = false;  // Return.

        queryText =
        		"SELECT min(price),        "
        		          + "FROM (SELECT club FROM yrb_offer AS O, (SELECT club AS cub FROM yrb_member m WHERE m.cid = ?) AS c "
        		          + "WHERE O.title = ? AND O.year = ? AND O.club = c.cub AND price = ?  )   ";

        // Prepare the query.
        try {
            querySt = myConnection.prepareStatement(queryText);
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in prepare");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Execute the query.
        try {
        	querySt.setInt(1, custID);
            querySt.setString(2, bookTitle4String);
            querySt.setString(3, bookYear);
            querySt.setDouble(4, minimumPrice);
            answers = querySt.executeQuery();
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in execute");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Any answer?
        try {
            if (answers.next()) {
                inDB = true;
                output = "cid: " + answers.getString("cid") + "\nName: " + answers.getString("name") + "\nCity: " + answers.getString("city");
                customerInfo.setText(output);
            } else {
                inDB = false;
                promptText.setText("There is no customer with that id \n Please enter your customer id and try again");;
            }
        } catch(SQLException e) {
            System.out.println("SQL#1 failed in cursor.");
            System.out.println(e.toString());
            System.exit(0);
        }

        // Close the cursor.
        try {
            answers.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing cursor.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        // We're done with the handle.
        try {
            querySt.close();
        } catch(SQLException e) {
            System.out.print("SQL#1 failed closing the handle.\n");
            System.out.println(e.toString());
            System.exit(0);
        }

        return inDB; }


	public void run()
	{

		//Everything is set up so we lay out the main procedure here

		promptText.setText("Hello!");

		//*******"Login" take in the customer id, if it does not exist prompt the user and try again
		//*******If the customer exists login and display customer id, name and city

		//*******Also if the customer exists populate drop down with all categories

		//*******After the customer chooses a category the customer can enter a title of the book
		//*******Here we return the title, year language and wieght
		//*******There may be more than one book here so we put these in a list and let the user select
		//*******Which book they want

		//*******If no books appear the user can search again

		//*******After the user selects a book we output the minimum price
		//*******The user might belong to different clubs with different prices

		//*******The user then enters the number of books they want to buy and we calculate
		//*******The total price

		//We ask the user if they want to buy the books and then they purchase
		//When they purchase the books we need to insert a tuple into the purchase table
	}
}