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 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 expirtDate) throws Exception {
		SimpleDateFormat sdf1 = new SimpleDateFormat("dd-MM-yyyy");
		java.util.Date date = sdf1.parse(expirtDate);

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

		for (String bookID : shoppingCart) {
			stmt.executeUpdate("udpate stock set quantity = quantity - 1 where isbn = " + bookID);
			stmt.executeUpdate("insert into sales values ("  +  bookID + " , " + 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 {
//		TimeZone timeZone = TimeZone.getTimeZone("Europe/Rome");
//		TimeZone.setDefault(timeZone);

		Class.forName("com.mysql.cj.jdbc.Driver");
		con = DriverManager.getConnection(url, user, password);

		// ("jdbc:mysql://localhost:3306/Library?serverTimezone=UTC","SAMPLE","admin");

		stmt = con.createStatement();
		shoppingCart = new ArrayList<String>();
	}

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

	// todo creditcard check

	@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) + " , fistName = "
				+ addQuotes(firstName) + " , lastName = " + addQuotes(lastName) + " , email = " + addQuotes(email)
				+ " , phone = " + addQuotes(phone) + " , shippingAddress = " + addQuotes(shippingAddress)
				+ " where username = " + "\"" + addQuotes(user) + "\"");
	}

	@Override
	public ResultSet getAllBooks() throws SQLException {
		return stmt.executeQuery("select * from book;");
	}

	@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 {
		return stmt.executeQuery("select * from book where isbn in" + getSearchSpaceFromShoppingCart());
	}

	@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 {
		return stmt.executeQuery("select * from user where username = " + addQuotes(mail));
	}

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

	@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.getFetchSize() != 1)
			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.getFetchSize() != 1)
			throw new RuntimeException("Failed to login.");
		user = username;
	}

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

	@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 index, String val) throws SQLException {
		if (index.equalsIgnoreCase("author"))
			return stmt
					.executeQuery("select * from book natural join book_authors where author_name = " + addQuotes(val));

		if (index.equalsIgnoreCase("isbn") || index.equalsIgnoreCase("price") || index.equalsIgnoreCase("threshold"))
			return stmt.executeQuery("select * from book where " + index + " = " + val);

		return stmt.executeQuery("select * from book where " + index + " = " + addQuotes(val));
	}

	@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 {
		// TODO Auto-generated method stub
		ResultSet r = stmt.executeQuery("select *, sum(price) purchase" + "from user u natural join sales s "
				+ "where month(s.checkout)+3 >= month(now()) " + "group by u.username " + "ordered by purchase DESC "
				+ "LIMIT 5;");
		return r;
	}

	@Override
	public ResultSet topTenSellingBooks() throws SQLException {
		// TODO Auto-generated method stub
		ResultSet r = stmt.executeQuery(
				"select *, count(*) selling " + "from book b, sales s " + "where month(s.checkout)+3 >= month(now()) "
						+ "group by b.isbn " + "ordered 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(s.checkout)+1 >= month(now())" + "group by b.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) + " );");
		}

	}

}