/cs108finalproject/src/web/DBMessages.java

package web;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBMessages {
	static String table = "messages";
	private Statement stmt;
	
	public static final int NUM_FIELDS = 9;
	
	public static final int SUCCESS = 1;
	public static final int FAILURE = -1;
	
	/**
	 * Constructor for DBMessages class that sets the instance variable Statement stmt
	 * @param stmt - stmt for the database quering
	 */
	public DBMessages(Statement stmt) {
		this.stmt = stmt;
	}
	
	
	//------------------------------------------ Start of Get Methods --------------------------------------------
	/**
	 * Grabs an ArrayList of MessagesRow's for the receiverID
	 * @param receiverID
	 * @return ArrayList<MessagesRow>
	 */
	public ArrayList<MessagesRow> getRecievedMessages(int receiverID) {
		
		String query = "SELECT * FROM " + table;
		
		if(receiverID > 0) {
			query += " WHERE receiverID = \"" + receiverID + "\"";
		}
		
		ResultSet rs = QueryDatabase.getQuery(query, stmt);
		if(rs == null) {
			System.out.println("No messages for receiverID: " + receiverID + " exist");
			return null;
		}
		
		ArrayList<MessagesRow> messages = new ArrayList<MessagesRow>();
		
		try {
			while(rs.next()) {
				messages.add(new MessagesRow(rs.getInt("messageID"),
											rs.getInt("receiverID"),
											rs.getInt("senderID"),
											rs.getString("messageTitle"),
											rs.getString("message"), 
											rs.getLong("dateSent"), 
											rs.getInt("dateRead"),
											rs.getString("messageType"),
											rs.getInt("quizChallengeID"),
											rs.getInt("friendRequest")));
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		
		return messages;
	}
	
	/**
	 * Get unread messages for a user
	 * @param receiverID userID of user to get messages for
	 * @return ArrayList of unread messages
	 */
	public ArrayList<MessagesRow> getUnreadMessages(int receiverID) {
		
		String query = String.format("SELECT * FROM %s WHERE receiverID = '%d' AND dateRead = '0'", table, receiverID);
		
		ResultSet rs = QueryDatabase.getQuery(query, stmt);
		if(rs == null) {
			System.out.println("No messages for receiverID: " + receiverID + " exist");
			return null;
		}
		
		ArrayList<MessagesRow> messages = new ArrayList<MessagesRow>();
		
		try {
			while(rs.next()) {
				messages.add(new MessagesRow(rs.getInt("messageID"),
											rs.getInt("receiverID"),
											rs.getInt("senderID"),
											rs.getString("messageTitle"),
											rs.getString("message"), 
											rs.getLong("dateSent"), 
											rs.getInt("dateRead"),
											rs.getString("messageType"),
											rs.getInt("quizChallengeID"),
											rs.getInt("friendRequest")));
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		
		return messages;
	}
	
	
	/**
	 * Grabs an ArrayList of MessagesRow's that the userID has sent
	 * @param senderID
	 * @return ArrayList<MessagesRow>
	 */
	public ArrayList<MessagesRow> getSentMessages(int senderID) {
		
		String query = "SELECT * FROM " + table;
		
		if(senderID > 0) {
			query += " WHERE senderID = \"" + senderID + "\"";
		}
		
		ResultSet rs = QueryDatabase.getQuery(query, stmt);
		if(rs == null) {
			System.out.println("No quizes for senderID: " + senderID + " exist");
			return null;
		}
		
		ArrayList<MessagesRow> messages = new ArrayList<MessagesRow>();
		
		try {
			while(rs.next()) {
				messages.add(new MessagesRow(rs.getInt("messageID"),
											rs.getInt("receiverID"),
											rs.getInt("senderID"),
											rs.getString("messageTitle"),
											rs.getString("message"), 
											rs.getLong("dateSent"), 
											rs.getInt("dateRead"),
											rs.getString("messageType"),
											rs.getInt("quizChallengeID"),
											rs.getInt("friendRequest")));
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		
		return messages;
	}
	
	/**
	 * Grabs an ArrayList of MessagesRow's that the userID has sent
	 * @param senderID
	 * @return ArrayList<MessagesRow>
	 */
	public MessagesRow getMessage(int messageID) {
		
		String query = "SELECT * FROM " + table;
		
		if(messageID > 0) {
			query += " WHERE messageID = \"" + messageID + "\"";
		}
		
		ResultSet rs = QueryDatabase.getQuery(query, stmt);
		if(rs == null) {
			System.out.println("No quizes for messageID: " + messageID + " exist");
			return null;
		}
		
		MessagesRow row = null;
		
		try {
			while(rs.next()) {
				row = new MessagesRow(rs.getInt("messageID"),
										rs.getInt("receiverID"),
										rs.getInt("senderID"),
										rs.getString("messageTitle"),
										rs.getString("message"), 
										rs.getLong("dateSent"), 
										rs.getInt("dateRead"),
										rs.getString("messageType"),
										rs.getInt("quizChallengeID"),
										rs.getInt("friendRequest"));
			}
			
		} catch (SQLException e) {
				e.printStackTrace();
		}
		
		return row;
	}
	//------------------------------------------- End of Get Messages Methods ----------------------------------
	
	//----------------------------------------------------------------------------------------------------------
	
	//-------------------------------------------- Start of Send Messages --------------------------------------
	public int sendMessage(MessagesRow row) {
		return sendMessage(row.receiverID, row.senderID, row.messageTitle, row.message, row.dateSent.getTimeInMillis(),
							row.dateRead, row.messageType, row.quizChallengeID, row.friendRequest);
	}
	
	public int sendMessage(int receiverID, int senderID, String messageTitle, String message,
							Long dateSent, int dateRead, String messageType, int quizChallengeID, int friendRequest) {
		
		String dSent;
		
		if(dateSent == null)
			dSent = "";
		else
			dSent = dateSent.toString();
		
		if(messageType == null)
			messageType = "";
		
		return sendMessage(receiverID+"", senderID+"", messageTitle, message,
							dSent, dateRead+"", messageType, quizChallengeID+"", friendRequest+"");
	}
	
	/**
	 * Adds a new MessageRow to the database
	 * @param receiverID - ID receiving the message
	 * @param senderID - ID sending the message
	 * @param message - The message being sent
	 * @param dateSent - The date this message was sent
	 * @param dateRead - The time this message gets read
	 * @param quizChallengeID - If sending a quiz, this ID is the quizID of the quiz
	 * @return messageID on success, -1 on failure
	 */
	public int sendMessage(String receiverID, String senderID, String messageTitle, String message, String dateSent,
			String dateRead, String messageType, String quizChallengeID, String friendRequest) {
		ArrayList<Object> row = new ArrayList<Object>();
		
		row.add(receiverID);
		row.add(senderID);
		row.add(messageTitle);
		row.add(message);
		row.add(dateSent);
		row.add(dateRead);
		row.add(messageType);
		row.add(quizChallengeID);
		row.add(friendRequest);
		
		return sendMessage(row);
	}
	
	/**
	 * Adds a new MessageRow to the database
	 * @param row - ArrayList<Object> with all the info for adding a MessagesRow
	 * @return messageID on success, -1 on failure
	 */
	public int sendMessage(ArrayList<Object> row) {
		
		if(row.size() < (NUM_FIELDS-1)) {
			//System.err.println("This isn't a proper entry");
			return FAILURE;
		}
		
		String receiverID = (String)row.get(0);
		String senderID = (String)row.get(1);
		String messageTitle = (String)row.get(2);
		String message = (String)row.get(3);
		String dateSent = (String)row.get(4);
		String dateRead = (String)row.get(5);
		String messageType = (String)row.get(6);
		String quizChallengeID = (String)row.get(7);
		String friendRequest = (String)row.get(8);
		
		String query = "INSERT INTO " + table;
		
		query += "(receiverID";
		query += ", senderID";
		query += ", messageTitle";
		query += ", message";
		query += ", dateSent";
		query += ", dateRead";
		query += ", messageType";
		query += ", quizChallengeID";
		query += ", friendRequest";
		
		query += ") VALUES (";
		query += "\"" + receiverID + "\"";
		query += ",";
		query += "\"" + senderID + "\"";
		query += ",";
		query += "\"" + DatabaseCompliance.makeStringDatabaseCompatible(messageTitle,
															DatabaseCompliance.MESSAGE_TITLE_MAX_LENGTH) + "\"";
		query += ",";
		query += "\"" + DatabaseCompliance.makeStringDatabaseCompatible(message,
															DatabaseCompliance.MESSAGE_MAX_LENGTH) + "\"";
		query += ",";
		query += "\"" + dateSent + "\"";
		query += ",";
		query += "\"" + dateRead + "\"";
		query += ",";
		query += "\"" + DatabaseCompliance.makeStringDatabaseCompatible(messageType,
															DatabaseCompliance.MESSAGE_TYPE_MAX_LENGTH) + "\"";
		query += ",";
		query += "\"" + quizChallengeID + "\"";
		query += ",";
		query += "\"" + friendRequest + "\"";
		query += ")";
		return QueryDatabase.setQueryWithAutoID(query, stmt);
	}
	
	//------------------------------------------- End of Send Messages Methods -----------------------------------
	
	//------------------------------------------------------------------------------------------------------------

	//------------------------------------------------ Update Methods --------------------------------------------
	/**
	 * Updates dateRead to a non-zero number (In most cases, 1)
	 * @param messageID
	 */
	public void updateDateReadToTrue(int messageID) {
		String query = "UPDATE " + table + " SET dateRead = 1 WHERE messageID = " + messageID;
		
		QueryDatabase.setQuery(query, stmt);
	}
	
	//-------------------------------------------- End of Update Methods --------------------------------------------
	
	//---------------------------------------------------------------------------------------------------------------
	
	//----------------------------------------------- Delete Methods ------------------------------------------------
	/**
	 * Deletes a message from the database
	 * @param messageID
	 */
	public void deleteMessage(int messageID) {
		String query = "DELETE FROM " + table + " WHERE messageID = " + messageID;
		
		QueryDatabase.setQuery(query, stmt);
	}
	
	/**
	 * Remove friend request messages sent between userA and userB
	 * in the database.
	 * @param userA userID
	 * @param userB userID
	 */
	public void deleteFriendRequests(int userA, int userB)
	{
		String query1 = String.format("DELETE FROM %s WHERE receiverID = %d AND senderID = %d AND friendRequest = 1", table, userA, userB);
		String query2 = String.format("DELETE FROM %s WHERE receiverID = %d AND senderID = %d AND friendRequest = 1", table, userB, userA);
		QueryDatabase.setQuery(query1, stmt);
		QueryDatabase.setQuery(query2, stmt);
	}
	
	//--------------------------------------------- End of Delete Methods -------------------------------------------
	/*
	 * Removes messages sent or received by authored by the user with the given ID
	 */
	public void clearMessages(int userID) {
		String query = "DELETE FROM " + table + " WHERE receiverID = " + userID;
		String query2 = "DELETE FROM " + table + " WHERE senderID = " + userID;
		QueryDatabase.setQuery(query, stmt);
		QueryDatabase.setQuery(query2, stmt);
	}
}