package industries.doublehelix.furrynotemanager;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.TreeSet;

public class User implements Comparable<User> {
	private int userId = -1;
	private Site site = null;
	private String name = "";
	private String password = "";
	
	public User() {
	}
	
	public User(int site, String name, String password) throws SQLException {
		this(Site.getSite(site), name, password);
	}
	
	public User(Site site, String name, String password) {
		setSite(site);
		setName(name);
		setPassword(password);
	}
	
	public static User getUser(int userId) throws SQLException {
		TreeSet<User> users = getUsersByExtendedStatement("USERID=" + userId);
		return users.first();
	}

	public static TreeSet<User> getAllUsers() throws SQLException {
		return getUsersByExtendedStatement(null);
	}
	
	public static TreeSet<User> getAllUsersFromSite(Site site) throws SQLException {
		return getUsersByExtendedStatement("WHERE SITEID=" + site.getSiteId());
	}
	
	public static User getUser(Site site, String name) throws SQLException {
		if (name == null || site == null) return null;
		
		// use prepared statement for escaping
		try (PreparedStatement ps = DBTools.getInstance().getConnection().prepareStatement("SELECT * FROM USERS "
				+ "WHERE SITEID=? AND NAME=?")) {
			ps.setInt(1, site.getSiteId());
			ps.setString(2, name.trim());
			
			try(ResultSet rs = ps.executeQuery()) {
				if(rs.next()) {
					User user = new User(Site.getSite(rs.getInt("SITEID")), rs.getString("NAME"), rs.getString("PASSWORD"));
					user.userId = rs.getInt("USERID");
					return user;
				}
			}
		}
		return null; //not found
	}
	
	public static TreeSet<User> getUsersByExtendedStatement(String where) throws SQLException {
		String wherePart = "";
		if (where != null && !where.isEmpty()) wherePart = " WHERE " + where;
		
		TreeSet<User> users = new TreeSet<>();
		try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
			try(ResultSet rs = s.executeQuery("SELECT * FROM USERS" + wherePart)) {
				while(rs.next()) {
					User user = new User(Site.getSite(rs.getInt("SITEID")), rs.getString("NAME"), rs.getString("PASSWORD"));
					user.userId = rs.getInt("USERID");
					users.add(user);
				}
			}
		}
		return users;
	}
	
	/**
	 * remove this user from DB. Requires user to have no notes
	 * @throws SQLException probably the user had notes
	 */
	public void removeFromDB() throws SQLException {
		// sql constraints should throw an error when trying to remove a user that still has notes
		try (Statement s = DBTools.getInstance().getConnection().createStatement()) {
			s.executeUpdate("DELETE FROM USERS WHERE USERID=" + this.userId);
		}
	}
	
	/**
	 * saves to db, ensures userId is set
	 * @return true if the User is in the DB at the end (not necessarily added anew)
	 * @throws SQLException
	 */
	public boolean saveToDb() throws SQLException {
		if (site == null || site.getSiteId() == -1) {
			System.err.println("Attempt to save user without site set");
			return false;
		}		
		if (name == null || name.trim().isEmpty()) {
			System.err.println("Attempt to save user without name");
			return false;
		}
		if (password == null || password.trim().isEmpty()) {
			System.err.println("Attempt to save user without name");
			return false;
		}
		
		// check if there is already a user in the db (same name, same pass, same site)
		// use prepared statement for escaping
		User oldUser = getUser(site, name);
		if (oldUser != null) {
			// user already exists
			if (!oldUser.getPassword().equals(password)) {
				// user has different password
				String sql = "UPDATE USERS SET PASSWORD=? WHERE NAME=? AND SITEID=?"; //name/siteid are primary key
				
				try (PreparedStatement ps = DBTools.getInstance().getConnection().prepareStatement(sql.toString())) {
					ps.setString(1, password); // can handle if sender is null
					ps.setString(2, name); // can handle if sender is null
					ps.setInt(3, site.getSiteId());
					
					ps.executeUpdate();
				}
			}
			userId = oldUser.userId;
			return true;
		}
		
		// new user
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO USERS (");
		sql.append("SITEID, NAME, PASSWORD");
		sql.append(") VALUES (");
		sql.append("?,      ?,    ?");
		sql.append(")");
		
		try (PreparedStatement ps = DBTools.getInstance().getConnection().prepareStatement(sql.toString())) {
			ps.setInt(1, site.getSiteId());
			ps.setString(2, name);
			ps.setString(3, password);
			
			ps.executeUpdate();
		}

		// re-select the now inserted note to get the noteid
		User u = getUser(site, name);
		this.userId = u.userId;
		return true;
	}
	
	public TreeSet<Note> getNotes() throws SQLException {
		return Note.getNotesByExtendedStatement("USERID=" + this.userId);
	}
	
	public TreeSet<Folder> getFolders() throws SQLException {
		return Folder.getFoldersFromUser(this);
	}
	
	public int getUserId() {
		return userId;
	}
	public void setUserId(int userId) {
		this.userId = userId;
	}
	public Site getSite() {
		return site;
	}
	public void setSite(Site site) {
		this.site = site;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name == null ? name : name.trim();
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password == null ? password : password.trim();
	}

	@Override
	public int compareTo(User o) {
		if (o.name.equals(this.name)) {
			return Integer.compare(site.getSiteId(), o.site.getSiteId());
		}
		return this.name.compareTo(o.name);
	}
	
	@Override
	public boolean equals(Object obj) {
		if (obj == null) return false;
		if (!(obj instanceof User)) return false;
		
		User user = (User)obj;
		
		// not comparing userid in case one side was not put into the DB
		return (site.equals(user.site) && name.equals(user.name) && password.equals(user.password));
	}
}
