package connector;

import items.*;
import java.sql.*;
import java.util.ArrayList;

import proxy.*;

import beans.*;

class DBConnector {

	private static DBConnector dbconnect = null;

	private static final String url = "jdbc:mysql://localhost:3306/";

	private static final String driver = "com.mysql.jdbc.Driver";

	private static Connection getConnection() {
		Connection con = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, MysqlServer.client,
					MysqlServer.clientPassword);
		} catch (ClassNotFoundException ce) {
			System.err.println("ClassNotFoundException from getConnection():  "
					+ ce.getMessage());
		} catch (SQLException ex) {
			System.err.println("SQLException from getting Connection:");
			System.err.println("Message:  " + ex.getMessage());
		}
		return con;
	}

	private static Connection getAdminConnection() {
		Connection con = null;
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, MysqlServer.admin,
					MysqlServer.adminPassword);
		} catch (ClassNotFoundException ce) {
			System.err
					.println("ClassNotFoundException from getAdminConnection():  "
							+ ce.getMessage());
		} catch (SQLException ex) {
			System.err.println("SQLException from getAdminConnection():");
			System.err.println("Message:  " + ex.getMessage());
		}
		return con;
	}

	private DBConnector() {
		try {
			Connection con = getAdminConnection();
			Statement stmt = con.createStatement();

			TablesManager.createTables(stmt);
			stmt.executeUpdate("GRANT select ON bookstore.* "
					+ "TO 'clientID'@'localhost' IDENTIFIED BY 'clientPW' ;");
			stmt.executeUpdate("use bookstore;");
			stmt.executeUpdate("grant update (cpassword,realname,mailingaddr,"
					+ "creditcardnum,expirationdate,emailaddr) on customer "
					+ "TO 'clientID'@'localhost';");
			stmt.executeUpdate("grant insert on customer "
					+ "TO 'clientID'@'localhost';");
			stmt.executeUpdate("grant insert on invoice "
					+ "TO 'clientID'@'localhost';");
			stmt.executeUpdate("grant insert on orders "
					+ "TO 'clientID'@'localhost';");

			stmt.close();
			con.close();
		} catch (SQLException ex) {
			System.err.println("SQLException from constructing DBConnector:");
			System.err.println("Message:  " + ex.getMessage());
			System.err.println("Vendor:  " + ex.getErrorCode());
		}
	}

	private static ArrayList<Book> searchBooks(String query) {
		ArrayList<Book> books = new ArrayList<Book>();
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				books.add(new Book(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from searchBooks: "
					+ se.getMessage());
		}
		if (books.size() == 0)
			System.err.println("Query Fail: No Such Book in the database.");
		return books;
	}

	static DBConnector getSingletonInstance() {

		if (dbconnect == null) {
			dbconnect = new DBConnector();
		}
		return dbconnect;
	}

	static String getLastID(String table) {

		if (Checker.checkNull(table) == Checker.nullStr) {
			System.err.println(" get Last ID error: table name is null");
			return null;
		}

		String query = "select * from " + table + ";", last = null;

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			boolean r = result.next();
			while (r) {
				last = result.getString(1);
				r = result.next();
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getLastID(): "
					+ se.getMessage());
		}
		return last;
	}

	static Book getBookByID(String BID) {
		if (Checker.checkNull(BID) == Checker.nullStr) {
			System.err.println(" get Book By ID error: book id is null");
			return null;
		}

		String query = "select * from book where BID = '" + BID.trim() + "';";
		ArrayList<Book> books = searchBooks(query);
		if (books.size() == 0) {
			System.err
					.println("No such book according to the specified ID in the datebase");
			return null;
		}
		return books.get(0);
	}

	static int execute(String query) {
		int result = -1;
		try {
			Connection con = getAdminConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			result = stmt.executeUpdate(query);
			stmt.close();
			con.close();
		} catch (SQLException ex) {
			System.err.println("-----SQLException-----");
			System.err.println("From DBConnector.execute(): "
					+ ex.getLocalizedMessage());
		}
		return result;
	}

	static ArrayList<Book> searchBookByTitle(String title) {

		if (Checker.checkNull(title) == Checker.nullStr) {
			System.err
					.println(" search Book By Title error: book title is null");
			return new ArrayList<Book>();
		}

		String query = "select * from book where title like '%" + title.trim()
				+ "%';";
		return searchBooks(query);
	}

	static ArrayList<Book> searchBookByAuthor(String author) {

		if (Checker.checkNull(author) == Checker.nullStr) {
			System.err
					.println(" search Book By Author error: book author is null");
			return new ArrayList<Book>();
		}

		String query = "select * from book where author like '%"
				+ author.trim() + "%';";
		return searchBooks(query);

	}

	static ArrayList<Book> top10BySale() {
		String query = "select b.* from invoice i,book b,publisher p "
				+ "where i.BID = b.BID and b.PID = p.PID "
				+ " group by b.BID order by sum(i.qty) desc;";

		ArrayList<Book> books = new ArrayList<Book>();
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			int j = 0, i = 0;
			while (result.next() && j < 10) {
				for (i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				books.add(new Book(args));
				j++;
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from top10BySale():\n "
					+ se.getMessage());
		}
		if (books.size() == 0)
			System.err.println("Query Fail: No Book in the database.");
		return books;
	}

	static int handleReg(String Cname, String Cpassword, String EMailAddr) {
		// /////////////////////////////////////////////////////////////////
		if (Checker.checkNull(Cname) == Checker.nullStr) {
			System.err.println("Customer register fail: Customer name is null");
			return 1;
		}

		String query = "SELECT Cname FROM customer where Cname = '"
				+ Cname.trim() + "';";
		String tmp = null;

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			while (result.next()) {
				tmp = result.getString(Cname);
				if (tmp != null)
					break;
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from handle Register: "
					+ se.getMessage());
		}
		if (tmp != null) {
			System.err
					.println("Customer register fail: Customer name is already in the database.");
			return 1;
		}

		if (Checker.checkNull(Cpassword) == Checker.nullStr) {
			System.err
					.println("Customer register fail: Customer password is null");
			return 1;
		}

		if (Checker.checkNull(EMailAddr) == Checker.nullStr) {
			System.err
					.println("Customer register fail: Customer email address is null");
			return 1;
		}

		String CID = CustomerManager.getNewCustomerID();
		int intResult = CustomerManager.insert(CID, Cname, Cpassword, null,
				null, null, null, EMailAddr);
		if (intResult == 1)
			return 0;
		return 2;
	}

	static ArrayList<Category> listAllCategories() {
		String query = "select * from category;";
		ArrayList<Category> categories = new ArrayList<Category>();
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);

			while (result.next()) {
				categories.add(new Category(result.getString("Cno"), result
						.getString("Cname")));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from listAllCategories: "
					+ se.getMessage());
		}
		if (categories.size() == 0)
			System.err
					.println("Query Fail: There is no category in the database.");
		return categories;
	}

	static ArrayList<SubCategory> listSubCategories(String categoryName) {
		String query = "SELECT s.* FROM subcategory s,category c "
				+ "where s.Cno = c.Cno and c.Cname = '" + categoryName + "';";
		ArrayList<SubCategory> subCategories = new ArrayList<SubCategory>();
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);

			while (result.next()) {
				subCategories.add(new SubCategory(result.getString("SCno"),
						result.getString("SCname"), result.getString("Cno")));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from listSubCategories: "
					+ se.getMessage());
		}

		if (subCategories.size() == 0)
			System.err.println("Query Fail: There is no subCategory "
					+ "of the specified category in the database.");
		return subCategories;
	}

	static boolean buyBooks(String Cname, String RealName, String MailAddr,
			String shippment, ArrayList<Book> books, ArrayList<Integer> qty) {
		// //////////////////////////////////////////////////
		if (books.size() != qty.size()) {
			System.out.println(books.size());
			System.err.println("books number error");
			return false;
		}
		if (Cname == null || Cname.trim().length() == 0) {
			System.err.println("Cname can not be null");
			return false;
		}

		if (RealName == null || RealName.trim().length() == 0) {
			System.err.println("RealName can not be null");
			return false;
		}

		if (MailAddr == null || MailAddr.trim().length() == 0) {
			System.err.println("MailAddr can not be null");
			return false;
		}

		if (shippment == null || shippment.trim().length() == 0) {
			System.err.println("shippment can not be null");
			return false;
		}

		String query = "";
		String Ono = OrdersManager.getNewOrderID();
		int length = books.size();
		String cid = null;
		String bid = null;
		int Qty;
		double billedPrice;

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			query = "select cid from customer where Cname='" + Cname + "';";
			ResultSet result = stmt.executeQuery(query);
			if (!result.next()) {
				System.err.println("No such customer name in the datebase");
				return false;
			} else {
				cid = result.getString(1);
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from searchBooks: "
					+ se.getMessage());
		}

		query = "insert into orders values ('" + Ono + "','" + cid + "','"
				+ shippment + "'," + "null," + "current_date);";
		DBConnector.execute(query);

		for (int i = 0; i < length; i++) {

			bid = books.get(i).getBID();
			Qty = qty.get(i).intValue();
			billedPrice = (double) books.get(i).getPrice() * Qty;
			query = "insert into invoice values ('" + Ono + "','" + bid + "','"
					+ Qty + "','" + billedPrice + "');";
			DBConnector.execute(query);

		}
		return true;

	}

	static ArrayList<Book> top10ByDate() {
		String query = "select b.* " + " from book b , publisher p "
				+ " where b.PID = p.PID "
				+ " order by b.publicationDate desc ;";

		ArrayList<Book> books = new ArrayList<Book>();
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			int j = 0, i = 0;
			while (result.next() && j < 10) {
				for (i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				books.add(new Book(args));
				j++;
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from top10ByDate():\n "
					+ se.getMessage());
		}
		return books;
	}