package org.mynonglam.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.sql.DataSource;

import org.mynonglam.entitys.SearchingFriend;

public class FriendSearchingMod {
	
	private DataSource dataSource;
	 
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public ArrayList<SearchingFriend> seach(String keyword, int offset, int limit) throws SQLException{

		ArrayList<SearchingFriend> list = new ArrayList<SearchingFriend>();
		Connection con = dataSource.getConnection();
   
		PreparedStatement ps = con.prepareStatement("SELECT email, fullname, country, avatar FROM account WHERE email like ? OR fullname like ? limit ? offset ?");
		ps.setString(1, "%" + keyword + "%");
		ps.setString(2, "%" + keyword + "%");
		ps.setInt(3, limit);
		ps.setInt(4, offset);
		ResultSet rs = ps.executeQuery();
		SearchingFriend fl = null;
		
		while (rs.next()) {
			String email = rs.getString(1);
			String fullname = rs.getString(2);
			String country = rs.getString(3);
			String avatar = rs.getString(4);
			fl = new SearchingFriend(email, fullname, country, avatar);
			list.add(fl);
		}
		
		ps.close();
		con.close();
		return list;
	}
	
    public int getRecordNumber(String keyword) throws SQLException {
    	int record = 0;
		Connection con = dataSource.getConnection();	
		PreparedStatement ps  = con.prepareStatement("SELECT COUNT(email) FROM account WHERE email like ? OR fullname like ?");
		ps.setString(1, "%" + keyword + "%");
		ps.setString(2, "%" + keyword + "%");
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) { 	        	
			record = rs.getInt(1);
		}
		ps.close();
		con.close();
		return record;
	}
    
	 public boolean addFriend(int type, String from, String to, String request, String response, int accept, int viewed, int share) throws SQLException {
		 boolean result = false;
		 Connection con = dataSource.getConnection();
		 try {
		
			 PreparedStatement ps = con.prepareStatement("INSERT INTO relationship values(?, ?, ?, ?, ?, ?, ?, ?)");
			 ps.setInt(1, type);
			 ps.setString(2, from);
			 ps.setString(3, to);
			 ps.setString(4, request);
			 ps.setString(5, response);
			 ps.setInt(6, accept);
			 ps.setInt(7, viewed);
			 ps.setInt(8, share);
			 int number = ps.executeUpdate();
			 if (number > 0) {
				result = true;
			 }
			 ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	 
 	public void deleteRelationship(String from, String to) throws SQLException {
		Connection con = dataSource.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement("DELETE FROM relationship WHERE (emailfrom = ? AND emailto = ?) OR (emailfrom = ? AND emailto = ?)");
			ps.setString(1, from);
			ps.setString(2, to);
			ps.setString(3, to);
			ps.setString(4, from);
			ps.executeUpdate();	
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 	
 	public boolean acceptFriend(String from, String to, String date) throws SQLException {
		boolean result = false;
		Connection con = dataSource.getConnection();
		try {
			
			PreparedStatement ps = con.prepareStatement("UPDATE relationship SET responsedate = ?, isaccept = 1 WHERE emailfrom = ? AND emailto = ?");
			ps.setString(1, date);
			ps.setString(2, from);
			ps.setString(3, to);
			int number = ps.executeUpdate();
			if (number > 0) {
				result = true;
			}
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
    
 	public void setRequestViewed(String from) throws SQLException {
		Connection con = dataSource.getConnection();
		try {
			PreparedStatement ps = con.prepareStatement("UPDATE relationship SET isviewed = 1 WHERE emailto = ?");
			ps.setString(1, from);
			ps.executeUpdate();
			ps.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
 	
 	public ArrayList<SearchingFriend> getFriendRequest(String id) throws SQLException{
 		ArrayList<SearchingFriend> list = new ArrayList<SearchingFriend>();
		Connection con = dataSource.getConnection();
   
		PreparedStatement ps = con.prepareStatement("SELECT acc.email, acc.fullname, acc.country, acc.avatar FROM account as acc, relationship as rel WHERE acc.email = rel.emailfrom AND rel.emailto = ? AND rel.isaccept = 0");
		ps.setString(1, id);
		ResultSet rs = ps.executeQuery();
		SearchingFriend fl = null;
		
		while (rs.next()) {
			String email = rs.getString(1);
			String fullname = rs.getString(2);
			String country = rs.getString(3);
			String avatar = rs.getString(4);
			fl = new SearchingFriend(email, fullname, country, avatar);
			list.add(fl);
		}
		
		ps.close();
		con.close();
		return list;
 	}
 	
 	public int countUnviewedFriendRequest(String id) throws SQLException{
 		int result = 0;
		Connection con = dataSource.getConnection();
		PreparedStatement ps = con.prepareStatement("SELECT count(emailto) FROM relationship WHERE emailto = ? AND isviewed = 0");
		ps.setString(1, id);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			result = rs.getInt(1);
		}
		ps.close();
		con.close();
		return result;
 	}
 	
 	public ArrayList<SearchingFriend> getMyRequest(String id) throws SQLException{
 		ArrayList<SearchingFriend> list = new ArrayList<SearchingFriend>();
		Connection con = dataSource.getConnection();
   
		PreparedStatement ps = con.prepareStatement("SELECT emailto FROM relationship WHERE emailfrom = ? AND isaccept = 0");
		ps.setString(1, id);
		ResultSet rs = ps.executeQuery();
		SearchingFriend fl = null;
		
		while (rs.next()) {
			String email = rs.getString(1);
			fl = new SearchingFriend(email, "", "", "");
			list.add(fl);
		}
		
		ps.close();
		con.close();
		return list;
 	}
    
 	public boolean checkAlreadyFriendRequest(String me, String another) throws SQLException{
 		boolean result = false;
		Connection con = dataSource.getConnection();
		PreparedStatement ps = con.prepareStatement("SELECT emailto FROM relationship WHERE emailfrom = ? AND emailto = ?");
		ps.setString(1, me);
		ps.setString(2, another);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			result = true;
		}
		ps.close();
		con.close();
		return result;
 	}
 	
 	public boolean checkOtherSendFriendRequest(String me, String another) throws SQLException{
 		boolean result = false;
		Connection con = dataSource.getConnection();
		PreparedStatement ps = con.prepareStatement("SELECT emailto FROM relationship WHERE emailfrom = ? AND emailto = ?");
		ps.setString(1, another);
		ps.setString(2, me);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			result = true;
		}
		ps.close();
		con.close();
		return result;
 	}
 	
 	public boolean checkIsFriends(String me, String another) throws SQLException{
 		boolean result = false;
		Connection con = dataSource.getConnection();
		PreparedStatement ps = con.prepareStatement("SELECT emailto FROM relationship WHERE ((emailfrom = ? AND emailto = ?) OR (emailto = ? AND emailfrom = ?)) AND isaccept = 1");
		ps.setString(1, another);
		ps.setString(2, me);
		ps.setString(3, another);
		ps.setString(4, me);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			result = true;
		}
		ps.close();
		con.close();
		return result;
 	}
 	
	public ArrayList<SearchingFriend> getHintFriends(String myEmail) throws SQLException{

		ArrayList<SearchingFriend> list = new ArrayList<SearchingFriend>();
		Connection con = dataSource.getConnection();
   
		PreparedStatement ps = con.prepareStatement("SELECT email, fullname, country, avatar FROM account WHERE email != ? AND (email NOT IN (SELECT DISTINCT emailfrom FROM relationship WHERE emailto = ?)) AND (email NOT IN (SELECT DISTINCT emailto FROM relationship WHERE emailfrom = ?)) ORDER BY RAND() LIMIT 6");
		ps.setString(1, myEmail);
		ps.setString(2, myEmail);
		ps.setString(3, myEmail);
		ResultSet rs = ps.executeQuery();
		SearchingFriend fl = null;
		
		while (rs.next()) {
			String email = rs.getString(1);
			String fullname = rs.getString(2);
			String country = rs.getString(3);
			String avatar = rs.getString(4);
			fl = new SearchingFriend(email, fullname, country, avatar);
			list.add(fl);
		}
		
		ps.close();
		con.close();
		return list;
	}
 	
	public static void main(String[] args) throws SQLException {
//		ArrayList<SearchingFriend> list = new FriendSearchingMod().seach("l", 0, 2);
//		System.out.println(new Gson().toJson(list));
//		System.out.println(new FriendSearchingMod().getRecordNumber("l"));
//		System.out.println(new FriendSearchingMod().addFriend(1, "luanxt@zing.vn", "1", "", "", 0, 0, 1));
//		new FriendSearchingMod().deleteRelationship("luanxt@zing.vn", "1");
//		System.out.println(new FriendSearchingMod().acceptFriend("luanxt@zing.vn", "1", "11/11"));
//		new FriendSearchingMod().setRequestViewed("luanxt@zing.vn");
//		ArrayList<SearchingFriend> list = new FriendSearchingMod().getMyRequest("dlduynam@gmail.com");
//		for (SearchingFriend searchingFriend : list) {
//			System.out.println(searchingFriend.getId() + " - " + searchingFriend.getFullname() + " - " + searchingFriend.getCountry());
//		}
//		System.out.println(new FriendSearchingMod().countUnviewedFriendRequest("luanxt@zing.vn"));
//		System.out.println(new FriendSearchingMod().checkIsFriends("dlduynam@gmail.com", "luanxt@zing.vn"));
		for (SearchingFriend f : new FriendSearchingMod().getHintFriends("luanxt@zing.vn")) {
			System.out.println(f.getFullname());
		}
	}
}
