package database;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
//import org.json.simple.parser.JSONParser;
//import org.json.simple.parser.ParseException;
import java.time.format.DateTimeFormatter;
import java.time.LocalDateTime;


import com.mysql.jdbc.Statement;

public class ChatData {
	private Connection con = null;
	public ChatData(String url, String username, String password) {
		try
		{
			con = DriverManager.getConnection(url, username, password);
			System.out.println("Connected to database!");
        }
		catch (Exception e)
		{
			System.out.println("Cannot connect to database!");
			System.out.println(e);
		}
	}

	public ResultSet query(String sql) {
		Statement stmt;
		try
		{
			stmt = (Statement) con.createStatement();
		}
		catch (SQLException e1)
		{
			return null;
		}

		try {
			return stmt.executeQuery(sql);
		}
		catch (SQLException e) {
			return null;
		}
	}

	public void update(String sql) {
		Statement stmt;
		try {
			stmt = (Statement) con.createStatement();
			stmt.executeUpdate(sql);
		}
		catch (SQLException e1)
		{
			System.out.println("UPDATE DATABASE ERROR");
		}
	}

	@SuppressWarnings("unchecked")
	public String getInbox(String userName) {
		String sql = "select * from chatapp.inbox where sender = '" + userName + "'";
		ResultSet res = this.query(sql);
		JSONArray inboxes = new JSONArray();
		try
		{
			while(res.next())
			{
				String idLastMess = res.getString(1);
				String lastTime = res.getString(2);
				String receiverId = res.getString(4);
				String sqlLastMessage = "select content, timeSeen from chatapp.messages where idMess = '" +  idLastMess + "';";
				ResultSet resLastMessage = this.query(sqlLastMessage);
				resLastMessage.next();
				//content of last message
				String contentLastMessage = resLastMessage.getString(1);
				String timeSeen = resLastMessage.getString(2);
				boolean seen = (timeSeen == null) ? false : true;

				String sqlReceiver = "select * from chatapp.receiver where idRec = '" + receiverId + "';";
				ResultSet resReceiver = this.query(sqlReceiver);
				resReceiver.next();
				String receiverName = "";
				boolean isGroup = false;
				String groupId = resReceiver.getString(3);
				isGroup = (groupId == null) ? false : true;
				if (isGroup)
				{
					String sqlGroupName = "select group_name from chatapp.group where group_id = " + groupId + ";";
					ResultSet resGroupName = this.query(sqlGroupName);
					resGroupName.next();
					receiverName = resGroupName.getString(1);
				}
				else
					receiverName = resReceiver.getString(2);

				JSONObject inbox = new JSONObject();
				inbox.put("idReceiver", Integer.parseInt(receiverId));
				inbox.put("Receiver", receiverName);
				inbox.put("LastMess", contentLastMessage);
				inbox.put("TimeOfLastMess", lastTime);
				inbox.put("Seen", seen);
				inboxes.add(inbox);
			}
		}
		catch (SQLException e)
		{
			System.out.println(e.getMessage());
			JSONObject obj = new JSONObject();
			obj.put("type", "RES_GET_INBOX");
			JSONObject obj2 = new JSONObject();
			obj2.put("user_name", userName);
			obj2.put("code", 444);
			obj.put("output", obj2);
			return "[" + obj.toString() + "]";
		}
		catch (NullPointerException e)
		{
			System.out.println(e.getMessage());
			return "No row has been returned";
		}

		JSONObject obj = new JSONObject();
		obj.put("type", "RESPONSE_INBOX");
		JSONObject subObj = new JSONObject();
		subObj.put("user_name", userName);
		subObj.put("inbox", inboxes);
		obj.put("output", subObj);
		return obj.toString();
	}

	@SuppressWarnings("unchecked")
	public String checkLogin(String user_name, String pass, String IpAddr) {
		String sql = "Select * from user where user_name = '"  + user_name + "' and password = '" + pass + "'";
		ResultSet res = this.query(sql);
		boolean check  = false;
		try
		{
			while(res.next())
				check = true;
		}
		catch (SQLException e)
		{
			JSONObject obj = new JSONObject();
			obj.put("type", "RES_CHECK_LOGIN");
			JSONObject obj2 = new JSONObject();
			obj2.put("user_name", user_name);
			obj2.put("code", 444);
			obj.put("output", obj2);
			return obj.toString();
		}

		if (check == true)
		{
			sql = "UPDATE user SET IpAddr = '"+ IpAddr + "' where user_name = '" + user_name + "'";
			this.update(sql);
			JSONObject obj = new JSONObject();
			obj.put("type", "RES_CHECK_LOGIN");
			JSONObject obj2 = new JSONObject();
			obj2.put("user_name", user_name);
			obj2.put("code", 777);
			obj.put("output", obj2);
			return obj.toString();
		}
		else
		{
			JSONObject obj = new JSONObject();
			obj.put("type", "RES_CHECK_LOGIN");
			JSONObject obj2 = new JSONObject();
			obj2.put("user_name", user_name);
			obj2.put("code", 444);
			obj.put("output", obj2);
			return obj.toString();
		}
	}

	@SuppressWarnings("unchecked")
	public String getMessages(String user_name, long receiver) {
		String sql = "Select * from receiver where idRec = "  + receiver;
		ResultSet res = this.query(sql);
		int isGroup = 0;
		String usernameOfRec = null;
		try
		{
			while(res.next())
				usernameOfRec = res.getString(2);

			if (usernameOfRec == null)
				isGroup = 1;
			if (isGroup == 1)
			{
				sql = "Select * from messages where receiver = "  + receiver;
				res = this.query(sql);
				JSONObject obj = new JSONObject();
				obj.put("type", "RESPONSE_OF_GET_MESSAGE");
				obj.put("user_name", user_name);
				obj.put("idReceiver", receiver);
				JSONArray arr = new JSONArray();
				while(res.next())
				{
					JSONObject obj2 = new JSONObject();
					obj2.put("sender", res.getString(4));
					obj2.put("content", res.getString(2));
					obj2.put("timeSent", res.getString(6));
					obj2.put("timeSeen", res.getString(7));
					arr.add(obj2);
				}
				obj.put("messages", arr);
				return obj.toString();
			}
			else
			{
				sql = "Select * from receiver where user_name = \'"  + user_name + "\'";
				res = this.query(sql);
				int idRecOfUsername = 0;
				while(res.next())
					idRecOfUsername = res.getInt(1);
				sql = "Select * from messages where (sender = \'"  + usernameOfRec + "\' and receiver = " + idRecOfUsername + ") or (sender = \'"  + user_name + " \' and receiver = \'" + receiver + " \') order by timeSent" ;
				res = this.query(sql);
				JSONObject obj = new JSONObject();
				obj.put("type", "RESPONSE_OF_GET_MESSAGE");
				obj.put("user_name", user_name);
				obj.put("idReceiver", receiver);
				JSONArray arr = new JSONArray();
				while(res.next())
				{
					JSONObject obj1 = new JSONObject();
					obj1.put("type", res.getString(3));
					obj1.put("content", res.getString(2));
					obj1.put("timeSent", res.getString(6));
					obj1.put("timeSeen", res.getString(7));
					arr.add(obj1);
				}
				obj.put("messages", arr);
				return obj.toString();
			}
		}
		catch (SQLException e)
		{
			return null;
		}
	}


	public void sendTextMessages(String sender, long receiver, String content)
	{
		DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");
		LocalDateTime now = LocalDateTime.now();
		String lastTime = dtf.format(now);
		String sql = "Select * from inbox where receiver = " + receiver + " and sender = \'" +  sender +  "\'";
		ResultSet res = this.query(sql);
		int numMess = 0;
		try
		{
			while(res.next())
					numMess = res.getInt(5);
			sql = "UPDATE inbox SET lastMess = \'" + content + "\', numMess = " + (numMess + 1) + ", lastTime = \'" + lastTime + "\' where receiver = " + receiver + " and sender = \'"+sender +"\'";
			System.out.println("Insert to inbox database successfully!");
			this.update(sql);
			sql = "INSERT INTO messages(content,type,sender,receiver,timeSent) values (\'" + content + "\', \'text\',\'" +sender + "\',"+ receiver + ",\'"+lastTime + "\')";
			System.out.println("Insert to messages database successfully!");
			this.update(sql);
		}
		catch (SQLException e)
		{
			System.out.println(" SEND TEXT MESSAGE ERROR ");
		}
	}

	public void requestMessages(String user_name, int idRecerver) {

	}

	public static void main(String args[]) {
		ChatData data = new ChatData("jdbc:mysql://localhost:3306/ChatApp?autoReconnect=true&useSSL=false","root", "Luan161198");
		String sender = "user_1";
		//String receiver = "2";
		//data.sendTextMessages("user_1", 2,"HAHAHA");

		//String res = data.getMessages("user_1",2);
		//String res = data.checkLogin("user_1","1234","192.168.100.14");
		String res = data.getInbox(sender);
		System.out.println(res);
		//System.out.println(res);
//        JSONParser parser = new JSONParser();
//		try {
//			JSONObject obj = (JSONObject)parser.parse(res);
//			String type = (String) obj.get("type");
//			JSONArray arr = (JSONArray) obj.get("messages");
//			for (int i = 0; i< arr.size(); i++) {
//				System.out.println(((JSONObject)arr.get(i)).get("content"));
//				System.out.println(((JSONObject)arr.get(i)).get("type"));
//				System.out.println(((JSONObject)arr.get(i)).get("timeSeen"));
//				System.out.println(((JSONObject)arr.get(i)).get("timeSent"));
//			}
//		} catch (ParseException e) {
//			e.printStackTrace();
//		}
	}
}