package org.foodup.persistence.dao;

/**
 * @author Meritxell Martí Martínez <m.marblu@gmail.com>
 * @author Cristóbal Cabezas Mateos <ccabezasmateos@gmail.com>
 * @author Escola del treball <correu@escoladeltreball.org>
 *
 * This is free software, licensed under the GNU General Public License v3.
 * See http://www.gnu.org/licenses/gpl.html for more information.
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.foodup.persistence.DBUtils;
import org.foodup.persistence.entity.Aliment;
import org.foodup.persistence.entity.User;

class UserDao extends BaseDao {

	// SELECTS

	public int getNextSequence(Connection con) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT nextval('users_seq')");

			rs = ps.executeQuery();

			return rs.next() ? rs.getInt(1) : -1;

		} finally {
			DBUtils.closeResources(rs, ps);
		}

	}
	

	/**
	 * Get user info
	 * 
	 * @param id
	 *            user
	 * @return User or null if something went wrong
	 * @throws SQLException 
	 */
	public User getUser(Connection con, int id) throws SQLException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT age, name_user, email, last_update, intolerance FROM users WHERE id=?");
			ps.setInt(1, id);
			rs = ps.executeQuery();
			
			User user = null;

			if (rs.next()) {
				user = new User();
				user.setQuantity(getNullableInteger(rs, "age"));
				user.setName(rs.getString("name_user"));
				user.setMail(rs.getString("email"));
				user.setLastUpdate(rs.getTimestamp("last_update").getTime());
				user.setIntolerance(getNullableInteger(rs, "intolerance"));
				user.setId(id);
			}

			return user;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	
	
	public String getPasswordById(Connection con, int id) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT password FROM users WHERE id = ?");

			ps.setInt(1, id);
			
			rs = ps.executeQuery();

			return rs.next() ? rs.getString(1) : null;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	
	
	public String getPasswordByMail(Connection con, String email) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT password FROM users WHERE email = ?");

			ps.setString(1, email);
			
			rs = ps.executeQuery();

			return rs.next() ? rs.getString(1) : null;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	
	
	public int getUserIdByMail(Connection con, String email) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT id FROM users WHERE email = ?");
			
			ps.setString(1, email);

			rs = ps.executeQuery();

			return rs.next() ? rs.getInt(1) : -1;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	
	
	public int getUserId(Connection con, String email, String password) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT id FROM users WHERE email = ? AND password = ?");
			
			ps.setString(1, email);
			ps.setString(2, password);

			rs = ps.executeQuery();

			return rs.next() ? rs.getInt(1) : -1;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}

	
	public int getUserId(Connection con, String email) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT id FROM users WHERE email = ?");
			
			ps.setString(1, email);

			rs = ps.executeQuery();

			return rs.next() ? rs.getInt(1) : -1;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}

	
	public byte[] getUserPhoto(Connection con, int id) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT picture_file FROM user_pictures WHERE id_user = ?");
			
			ps.setInt(1, id);

			rs = ps.executeQuery();

			return rs.next() ? rs.getBytes("picture_file") : null;

		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	
	/**
	 * Get friends of user
	 * 
	 * @param idUser
	 * @return List<User> or null if something went wrong
	 * @throws SQLException 
	 */
	public List<User> getColeguis(Connection con, int idUser) throws SQLException {
		
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement(
					"SELECT id, age, name_user, email, last_update, intolerance FROM users INNER JOIN my_coleguis ON id = id_colegui WHERE id_user = ?");
			ps.setInt(1, idUser);
			rs = ps.executeQuery();
			
			List<User> coleguis = new ArrayList<>();
			User user = null;

			while (rs.next()) {
				user = new User();
				// basic info
				user.setId(rs.getInt("id"));
				user.setQuantity(getNullableInteger(rs, "age"));
				user.setName(rs.getString("name_user"));
				user.setMail(rs.getString("email"));
				user.setLastUpdate(rs.getTimestamp("last_update").getTime());
				user.setIntolerance(getNullableInteger(rs, "intolerance"));
				coleguis.add(user);
			}

			return coleguis;
			
		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	

	/**
	 * Get allergies from user
	 * 
	 * @param idUser
	 * @return List<Aliments> or null if something went wrong
	 * @throws SQLException 
	 */
	public List<Aliment> getUserAllergies(Connection con, int idUser) throws SQLException {

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {

			ps = con.prepareStatement("SELECT id, name_aliment, food_group FROM aliments INNER JOIN users_restriction ON id = id_aliment WHERE id_user = ?");
			
			ps.setInt(1, idUser);
			rs = ps.executeQuery();
			
			List<Aliment> allergies = new ArrayList<>();
			Aliment aliment;
			
			if (rs.next()) {
				aliment = new Aliment();
				aliment.setId(rs.getInt("id"));
				aliment.setName(rs.getString("name_aliment"));
				aliment.setFoodGroup(rs.getInt("food_group"));
				allergies.add(aliment);
			}

			return allergies;
			
		} finally {
			DBUtils.closeResources(rs, ps);
		}
	}
	
	// INSERTS

	public int insertImageUser(Connection con, int idUser, byte[] file) throws SQLException {

		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement("INSERT INTO user_pictures VALUES (?, ?)");
			ps.setInt(1, idUser);
			ps.setBytes(2, file);

			return ps.executeUpdate();
		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	public int insertUser(Connection con, User user, String password) throws SQLException {
		
		PreparedStatement ps = null;

		try {
			
			ps = con.prepareStatement("INSERT INTO users (id, age, name_user, email, password, last_update, intolerance) VALUES (?, ?, ?, ?, ?, ?, ?)");
			
			ps.setInt(1, user.getId());
			setNullableInteger(ps, 2, user.getQuantity());
			ps.setString(3, user.getName());
			ps.setString(4, user.getMail());
			ps.setString(5, password);
			ps.setTimestamp(6, new Timestamp(user.getLastUpdate()));
			setNullableInteger(ps, 7, user.getIntolerance());
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	/**
	 * Insert user's FoodUp
	 * @param idUser
	 * @param idFoodUp
	 * @throws SQLException 
	 */
	public int insertFoodUpOfUser(Connection con, int idUser, int idFoodUp) throws SQLException {

		PreparedStatement ps = null;

		try {

			ps = con.prepareStatement("INSERT INTO relation_foodups_users VALUES (?, ?)");
			ps.setInt(1, idFoodUp);
			ps.setInt(2, idUser);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	

	/**
	 * Insert allergies
	 * 
	 * @param idUser
	 * @param idNewAliment
	 * @throws SQLException 
	 */
	public int[] insertAllergies(Connection con, int idUser, List<Aliment> aliments) throws SQLException {

		PreparedStatement ps = null;

		try {

			ps = con.prepareStatement("INSERT INTO users_restriction VALUES (?,?)");
			
			for (Aliment aliment: aliments) {
				ps.setInt(1, idUser);
				ps.setInt(2, aliment.getId());
				ps.addBatch();
			}
			
			return ps.executeBatch();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	
	/**
	 * Insert user's friend
	 * @param idUser
	 * @param idColegui
	 * @throws SQLException 
	 */
	public int insertColegui(Connection con, int idUser, int idColegui) throws SQLException {

		PreparedStatement ps = null;

		try {
			
			ps = con.prepareStatement("INSERT INTO my_coleguis VALUES (?, ?) WHERE id_user != id_colegui");
			ps.setInt(1, idUser);
			ps.setInt(2, idColegui);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	// UPDATEs

	public int updateUserInfo(Connection con, User user) throws SQLException {

		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement(
					"UPDATE users SET name_user = ?, age = ?, last_update = ?, intolerance = ? WHERE id = ?");
			
			ps.setString(1, user.getName());
			ps.setInt(2, user.getQuantity());
			ps.setTimestamp(3, new Timestamp(user.getLastUpdate()));
			setNullableInteger(ps, 4, user.getIntolerance());
			ps.setInt(5, user.getId());

			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}

	/**
	 * @param con a database connection
	 * @param id
	 * @param email
	 * @return row count have been updated, 0 otherwise
	 * @throws SQLException if a database access error occurs or if a restriction is violated
	 */
	public int updateUserMail(Connection con, int id, String email) throws SQLException {

		PreparedStatement ps = null;

		try {
			ps = con.prepareStatement("UPDATE users SET email = ? WHERE id = ?");
			ps.setString(1, email);
			ps.setInt(2, id);

			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	// DELETES

	/**
	 * Delete image profile of user
	 * @param con a database connection
	 * @param idUser
	 * @return row count have been removed, 0 otherwise
	 * @throws SQLException if a database access error occurs or if a restriction is violated
	 */
	public int deleteUserImage(Connection con, int idUser) throws SQLException {
		
		PreparedStatement ps = null;

		try {
			
			ps = con.prepareStatement("DELETE FROM user_pictures WHERE id_user= ?");
			ps.setInt(1, idUser);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	/**
	 * Delete colegui of user
	 * @param con a database connection
	 * @param idUser
	 * @param idColeguiToRemove
	 * @return row count have been removed, 0 otherwise
	 * @throws SQLException if a database access error occurs or if a restriction is violated
	 */
	public int deleteColegui(Connection con, int idUser, int idColeguiToRemove) throws SQLException {

		PreparedStatement ps = null;

		try {
			
			ps = con.prepareStatement("DELETE FROM my_coleguis WHERE id_user= ? AND id_colegui= ?");
			ps.setInt(1, idUser);
			ps.setInt(2, idColeguiToRemove);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	/**
	 * Delete a user 
	 * @param con a database connection
	 * @param idUser
	 * @return row count have been removed, 0 otherwise
	 * @throws SQLException if a database access error occurs or if a restriction is violated
	 */
	public int deleteUser(Connection con, int idUser) throws SQLException {

		PreparedStatement ps = null;

		try {
			
			ps = con.prepareStatement("DELETE FROM users WHERE id_user= ?");
			ps.setInt(1, idUser);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	/**
	 * @param con a database connection
	 * @param idUser
	 * @param idFoodUp
	 * @return row count have been removed, 0 otherwise
	 * @throws SQLException if a database access error occurs or if a restriction is violated
	 */
	public int deleteUserFoodUp(Connection con, int idUser, int idFoodUp) throws SQLException {

		PreparedStatement ps = null;

		try {

			ps = con.prepareStatement("DELETE FROM relation_foodups_users WHERE id_user=? AND id_foodup=?");
			ps.setInt(1, idUser);
			ps.setInt(2, idFoodUp);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}
	
	/**
	 * Delete all allergies of user
	 * @param con a database connection
	 * @param idUser
	 * @param idFoodUp
	 * @return row count have been removed, 0 otherwise
	 * @throws SQLException if a database access error occurs or if a restriction is violated
	 */
	public int deleteAllergies(Connection con, int idUser) throws SQLException {

		PreparedStatement ps = null;

		try {
			
			ps = con.prepareStatement("DELETE FROM users_restriction WHERE id_user=?");
			ps.setInt(1, idUser);
			
			return ps.executeUpdate();

		} finally {
			DBUtils.closeResources(ps);
		}
	}

}