package dbms;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import utiles.User;

public class DBMS implements IDBMS {

	private Connection con;
	private ArrayList<String> shoppingCart;
	private Statement stmt;
	private String user;

	@Override
	public void addAuthor(String author) throws SQLException {
		stmt.executeUpdate("insert into author values (" + addQuotes(author) + ");");
	}

	@Override
	public void addBook(String bookID, String title, String publisher, String publicationDate, String price,
			String category, String threshold, String quantity, ArrayList<String> book_authors) throws SQLException {

		stmt.executeUpdate("insert into book values (" + bookID + ", \"" + title + "\", \"" + publisher + "\", \""
				+ publicationDate + "\", " + price + ", \"" + category + "\", " + threshold + ");");

		stmt.executeUpdate("insert into stock values (" + bookID + ", " + quantity + ");");

		for (String authorName : book_authors) {
			if (!isAuthorExists(authorName))
				addAuthor(authorName);
			stmt.executeUpdate("insert into book_authors values (" + bookID + ", " + addQuotes(authorName) + ");");
		}
	}

	@Override
	public void addBookToCart(String bookID) {
		shoppingCart.add(bookID);
	}

	@Override
	public void addPublisher(String publisherName, String address, String telephone) throws SQLException {
		stmt.executeUpdate("insert into publisher values (" + addQuotes(publisherName) + "," + addQuotes(address) + ","
				+ addQuotes(telephone) + ");");
	}

	private String addQuotes(String str) {
		return "\"" + str + "\"";
	}

	@Override
	public void checkoutCart(String creditCardNum, String expiryDate) throws Exception {
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
		java.util.Date date = sdf1.parse(expiryDate);

		if (date.getTime() < new Date().getTime())
			throw new RuntimeException("Expired credit card.");

		for (String bookID : shoppingCart){
			stmt.executeUpdate("update stock set quantity = quantity - 1 where isbn = " + bookID);
			stmt.executeUpdate("insert into sales (ISBN, username, checkout) values ("  +  bookID + " , " + addQuotes(user) + " , " + "now()" + ")");
		}
		shoppingCart.clear();
	}

	@Override
	public void confirmOrder(String id) throws SQLException {
		stmt.executeUpdate("delete from orders where id = " + id);
	}

	@Override
	public void connect(String url, String user, String password) throws Exception {
		Class.forName("com.mysql.cj.jdbc.Driver");
		con = DriverManager.getConnection(url, user, password);
		stmt = con.createStatement();
		shoppingCart = new ArrayList<String>();
	}

	@Override
	public void deleteBook(String bookID) throws SQLException {
		stmt.executeUpdate("delete from book where isbn = " + bookID + ";");
	}

	@Override
	public void disconnect() throws SQLException {
		con.close();
		shoppingCart = null;
		user = null;
	}

	@Override
	public void editUserInfo(String password, String lastName, String firstName, String email, String phone,
			String shippingAddress) throws SQLException {
		stmt.executeUpdate("update user set password = " + addQuotes(password) + " , firstName = "
				+ addQuotes(firstName) + " , lastName = " + addQuotes(lastName) + " , email = " + addQuotes(email)
				+ " , phone = " + addQuotes(phone) + " , shippingAddress = " + addQuotes(shippingAddress)
				+ " where username = " + addQuotes(user));
	}


	private void addAuthors(ResultSet r, HashMap<String, StringBuilder> authors) throws SQLException {
		ResultSet a = stmt.executeQuery("select * from book_authors;");
		while(r.next()) authors.put(r.getString("isbn"), new StringBuilder());
		while(a.next()) {
			String isbn = a.getString("isbn");
			if(authors.containsKey(isbn)) {
				if(!authors.get(isbn).toString().isEmpty())
					authors.get(isbn).append(", ");
				authors.get(isbn).append(a.getString("author_name"));
			}
		}
	}

	@Override
	public ResultSet getAllBooks(HashMap<String, StringBuilder> authors) throws SQLException {
		ResultSet ret = stmt.executeQuery("select * from book natural join stock;");
		ResultSet temp = stmt.executeQuery("select * from book natural join stock;");

		addAuthors(temp, authors);
		return ret;
	}

	@Override
	public ResultSet getOrders() throws SQLException {
		ResultSet r = stmt.executeQuery("Select * from orders");
		return r;
	}

	private String getSearchSpaceFromShoppingCart() {
		StringBuilder ret = new StringBuilder("( ");
		for (int i = 0; i < shoppingCart.size(); ++i) {
			ret.append(shoppingCart.get(i));
			if (i + 1 < shoppingCart.size())
				ret.append(", ");
		}

		ret.append(")");
		return ret.toString();
	}

	@Override
	public ResultSet getShoppingCartContent() throws SQLException {
		if(shoppingCart.isEmpty()) return null;
		ResultSet ret = stmt.executeQuery("select * from book where isbn in" + getSearchSpaceFromShoppingCart());
		return ret;
	}

	@Override
	public int getTotalPriceOfCart() throws SQLException {
		int ret = 0;
		for (String bookID : shoppingCart) {
			ResultSet r = stmt.executeQuery("select price from book where isbn =" + bookID);
			ret += r.getInt("price");
		}
		return ret;
	}

	@Override
	public ResultSet getUserInfoWithMail(String mail) throws SQLException {
		ResultSet ret = stmt.executeQuery("select * from user where email = " + addQuotes(mail));
		ret.next();
		return ret;
	}

	@Override
	public ResultSet getUserInfoWithName(String username) throws SQLException {
		ResultSet ret = stmt.executeQuery("select * from user where username = " + addQuotes(username));
		ret.next();
		return ret;
	}

	@Override
	public ResultSet getUsers() throws SQLException {
		ResultSet r = stmt.executeQuery("select * from user where usertype <> \"Manager\"");
		return r;
	}

	@Override
	public String getUserType() throws SQLException {
		return stmt.executeQuery("select usertype from user where username = " + addQuotes(user)).getString("usertype");
	}

	private boolean isAuthorExists(String author) throws SQLException {
		ResultSet r = stmt.executeQuery("select * from author where author_name = " + addQuotes(author));
		return r.next();
	}

	@Override
	public void loginWithMail(String mail, String password) throws Exception {
		ResultSet rs = stmt.executeQuery(
				"select * from user where email = " + addQuotes(mail) + " and password = " + addQuotes(password) + ";");
		if (!rs.next())
			throw new RuntimeException("Failed to login.");

		user = rs.getString("username");
	}

	@Override
	public void loginWithUsername(String username, String password) throws Exception {
		ResultSet rs = stmt.executeQuery("select * from user where username = " + addQuotes(username)
				+ " and password = " + addQuotes(password) + ";");
		if (!rs.next())
			throw new RuntimeException("Failed to login.");
		user = username;
	}

	@Override
	public void placeOrder(String bookID, String quantity) throws SQLException {
		stmt.executeUpdate("insert into orders (isbn, quantity, checkout) values(" + bookID + ","
				+ quantity + ", now());");
	}

	@Override
	public void promoteUser(String username) throws SQLException {
		stmt.executeUpdate(
				"update user " + "set usertype = " + " \"manager\" " + " where username = " + "\"" + username + "\"");
	}

	@Override
	public void removeBookFromCart(String bookID) {
		shoppingCart.remove(bookID);
	}

	@Override
	public ResultSet searchBookByIndex(String val, String index, HashMap<String, StringBuilder> authors) throws SQLException {
		ResultSet ret, temp;
		if (index.equalsIgnoreCase("author")) {
			ret = stmt
					.executeQuery("select * from book natural join book_authors natural join stock where author_name = " + addQuotes(val));
			temp = stmt
					.executeQuery("select * from book natural join book_authors natural join stock where author_name = " + addQuotes(val));
		}
		else if (index.equalsIgnoreCase("isbn") || index.equalsIgnoreCase("price") || index.equalsIgnoreCase("threshold")) {
			ret = stmt.executeQuery("select * from book natural join stock where " + index + " = " + val);
			temp = stmt.executeQuery("select * from book natural join stock where " + index + " = " + val);
		}
		else{
			ret = stmt.executeQuery("select * from book natural join stock where " + index + " = " + addQuotes(val));
			temp = stmt.executeQuery("select * from book natural join stock where " + index + " = " + addQuotes(val));
		}

		addAuthors(temp, authors);
		return ret;
	}

	@Override
	public void signup(User user) throws SQLException {
		stmt.executeUpdate("insert into user values (" + addQuotes(user.getUsername()) + ","
				+ addQuotes(user.getPassward()) + "," + addQuotes(user.getLname()) + "," + addQuotes(user.getFname())
				+ "," + addQuotes(user.getMail()) + "," + addQuotes(user.getPhone()) + ","
				+ addQuotes(user.getAddress()) + "," + addQuotes(user.getType()) + ");");
	}

	@Override
	public ResultSet topFiveCustomers() throws SQLException {
		ResultSet r = stmt.executeQuery("select *, sum(price) purchase from user u natural join sales s natural join book b "
				+ "where month(checkout)+3 >= month(now()) group by username order by purchase DESC "
				+ "LIMIT 5;");
		return r;
	}

	@Override
	public ResultSet topTenSellingBooks() throws SQLException {
		ResultSet r = stmt.executeQuery(
				"select *, count(*) selling from book b natural join sales s where month(checkout)+3 >= month(now()) "
						+ "group by isbn order by selling DESC LIMIT 10;");
		return r;
	}

	@Override
	public ResultSet totalSalesForPrevoisMonth() throws SQLException {
		ResultSet r = stmt.executeQuery("select *, count(*) from book b natural join sales s "
				+ "where month(checkout)+1 >= month(now()) group by isbn;");
		return r;
	}

	@Override
	public void updateBook(String bookID, String title, String publisher, String publicationDate, String price,
			String category, String threshold, ArrayList<String> authors) throws SQLException {
		stmt.executeUpdate("update book " + "set title = " + addQuotes(title) + " , publisher = " + addQuotes(publisher)
				+ " , publication_date = " + addQuotes(publicationDate) + " , price = " + price + " , category = "
				+ addQuotes(category) + " , threshold = " + threshold + " where isbn = " + bookID + ";");

		stmt.executeUpdate("delete from book_authors where isbn = " + bookID);
		for (String author : authors) {
			if (!isAuthorExists(author))
				addAuthor(author);
			stmt.executeUpdate("insert into book_authors values (" + bookID + " , " + addQuotes(author) + " );");
		}

	}

}