package jeopardy;
import java.sql.*;
import java.util.ArrayList;



public class DBModel implements Model {
	private Connection con;
	private PreparedStatement createUserStmnt;
	private PreparedStatement checkUserStmnt;
	private PreparedStatement getValidCategoriesStmnt;
	
	public DBModel(String url) {
		try {
			con = DriverManager.getConnection(url);
			createUserStmnt = con.prepareStatement("INSERT INTO `users` (`name`) VALUES (?)");
			checkUserStmnt = con.prepareStatement("SELECT COUNT(*) FROM `users` WHERE `name` = ?");
			getValidCategoriesStmnt = con.prepareStatement( // TODO: this has to support more than 6 players
					"SELECT categories.name FROM aqpairs, categories WHERE aqpairs.used = 0 AND aqpairs.category = categories.id AND aqpairs.id NOT IN "
					+ "(SELECT DISTINCT aqpairs.id FROM aqpairs, authors, users WHERE aqpairs.used = 0 AND users.name IN (?, ?, ?, ?, ?, ?) AND users.id = authors.author AND authors.aqpair = aqpairs.id )"
					+ " GROUP BY category HAVING COUNT(aqpairs.id) >= 2");
		}
		catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		}
	}

	

	public void createUser(String name) {
		try {
			createUserStmnt.setString(1, name);
			createUserStmnt.execute();
		}
		catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		}
	}

	public boolean userExists(String name) {
		try {
			checkUserStmnt.setString(1, name);
			ResultSet r = checkUserStmnt.executeQuery();
			r.next();
			return (r.getInt(1) == 1);
		}
		catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
			return false;
		}
	}
	
	public ArrayList<String> validCategoriesForUsers(ArrayList<String> users)
	{
		try {
			for(int i = 0; i < 6; i++) {
				getValidCategoriesStmnt.setString(i + 1, users.size() <= i ? "" : users.get(i));
			}
			ResultSet r = getValidCategoriesStmnt.executeQuery();
			ArrayList<String> result = new ArrayList<String>();
			while(r.next()) {
				result.add(r.getString(1));
			}
			return result;
		}
		catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
			return null;
		}
	}
	
	
	
}