package org.microblog.dao;

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

import org.microblog.po.Friend;
import org.microblog.util.DBConn;

public class FriendDao {
	
	public boolean addFriend(final Friend friend){
		//f_status
		//1为u1关注u2
		//2为u1与u2互相关注
		//获得关注的人数量
		int u_id = friend.getU_id();
		int u_id2 = friend.getU_id2();
		DBConn dbConn = new DBConn();
		
			//int id = 0;
			long amount = 0;
			int addAffectedRows = 0;
			int changeAffectedRows = 0;
			String sql_u2_u1="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
			ResultSet rs = dbConn.execQuery(sql_u2_u1, new Object[]{u_id2,u_id});
			try {
		while(rs.next()){
			//id=rs.getInt("f_id"); 
		    amount   =   rs.getLong("count(*)"); 
		}
		
		if(amount==0){
			String sql_addfriend = "insert into friend values(null,?,?,?)";
			addAffectedRows = dbConn.execOther(sql_addfriend, new Object[]{u_id,u_id2,1});
		}else if(amount==1){
			String sqlChangeStatus = "update friend set f_status = 2 where u_id = ? and u_id2 = ?";
			changeAffectedRows = dbConn.execOther(sqlChangeStatus, new Object[]{u_id2,u_id});
		}
		return ((addAffectedRows>0)||(changeAffectedRows>0))?true:false;
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
			}
		return false;
		}
		
	
	public boolean removeFriendById(final int u_id,final int u_id2){	
		//删除关注
		String sql_removeFriendById="SELECT f_id,f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
		String sql_removeFriendById2="SELECT f_id,f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
		DBConn dbConn = new DBConn();
		ResultSet rs1 = dbConn.execQuery(sql_removeFriendById, new Object[]{u_id,u_id2});
		ResultSet rs2 = dbConn.execQuery(sql_removeFriendById2, new Object[]{u_id2,u_id});
		int status1 = 0;
		int status2 = 0;
		int id1 = 0;
		int id2 = 0;
		int affectedRows1 = 0;
		int affectedRows2 = 0;
		try {
			while(rs1.next()){
				if(!rs1.wasNull()){
					id1=rs1.getInt("f_id"); 
				status1=rs1.getInt("f_status");
				}
			}
			while(rs2.next()){
				if(!rs2.wasNull())
					id2=rs2.getInt("f_id"); 
				status2=rs2.getInt("f_status");
				
			}
			if(status1==1){
				String sql_sql_removeFriend = "delete from friend where f_id = ?";
				affectedRows1 = dbConn.execOther(sql_sql_removeFriend, new Object[]{id1});
			}else if(status2==1){
				String sql_sql_removeFriend = "delete from friend where f_id = ?";
				affectedRows1 = dbConn.execOther(sql_sql_removeFriend, new Object[]{id2});
			}else if(status1==2){
				String sql_sql_removeFriend2 = "update friend set u_id=?,u_id2=?,f_status=? where f_id = ?";
				affectedRows2 = dbConn.execOther(sql_sql_removeFriend2, new Object[]{u_id2,u_id,1,id1});
			}else if(status2==2){
				String sql_sql_removeFriend2 = "update friend set f_status=? where f_id = ?";
				affectedRows2 = dbConn.execOther(sql_sql_removeFriend2, new Object[]{1,id2});
			}
			
			if((affectedRows1>0)||(affectedRows2>0)){
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs1.close();
				rs2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		
		return false;

	}
	
	public int isFriend(final int u_id,final int u_id2){
		//判断二人关系
		/*
		 * u_id1关注u_id2返回	1
		 * u_id2关注u_id1返回	2
		 * 互为好友				3
		 */
		
		String sql_getStatusById="SELECT f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
		String sql_getStatusById2="SELECT f_status FROM `microblog`.`friend` where u_id = ? and u_id2 = ?";
		DBConn dbConn = new DBConn();
		ResultSet rs1 = dbConn.execQuery(sql_getStatusById, new Object[]{u_id,u_id2});
		ResultSet rs2 = dbConn.execQuery(sql_getStatusById2, new Object[]{u_id2,u_id});
		int status1 = 0;
		int status2 = 0;
		try {
			while(rs1.next()){
				if(!rs1.wasNull()){
				status1=rs1.getInt("f_status");
				}
			}
			while(rs2.next()){
				if(!rs2.wasNull())
				status2=rs2.getInt("f_status");
				
			}
			if(status1==1){
				return 1;
				//u_id1关注u_id2
			}else if(status2==1){
				return 2;
				//u_id2关注u_id1
			}else if((status1==2)||(status2==2)){
				return 3;
				//互为好友
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs1.close();
				rs2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		
		return 0;
	}

	public boolean existRelationship(final int u_id, final int u_id2) {
		String strSql = "select f_status from friend where u_id = ? and u_id2 = ?";
		DBConn dbConn = new DBConn();
		ResultSet rs = dbConn.execQuery(strSql, new Object[] { u_id, u_id2 });
		ResultSet rs2 = dbConn.execQuery(strSql, new Object[] { u_id2, u_id });

		int f_status = 0;
		int f_status2 = 0;
		try {
			if (rs.next()) {
				f_status = rs.getInt(1);
			}
			if (rs2.next()) {
				f_status2 = rs2.getInt(1);
			}
			
			return (f_status > 0 || f_status2>0)? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				rs.close();
				rs2.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
			dbConn.closeConn();
		}

	}
	
	public long getFansAmountById(final int u_id){
		//f_status
		//1为u1关注u2
		//2为u1与u2互相关注
		//获得关注的人数量
		String sql_getFansAmountById="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 1";
		String sql_getFansAmountById2="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 2";
		String sql_getFansAmountById3="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 2";
		DBConn dbConn = new DBConn();
		ResultSet rs = dbConn.execQuery(sql_getFansAmountById, new Object[]{u_id});
		ResultSet rs2 = dbConn.execQuery(sql_getFansAmountById2, new Object[]{u_id});
		ResultSet rs3 = dbConn.execQuery(sql_getFansAmountById3, new Object[]{u_id});
		long amount = 0;
		long amount2 = 0;
		long amount3 = 0;
		try {
			while(rs.next()){
				amount=rs.getLong("count(*)");
				
			}
			
			while(rs2.next()){
				amount2=rs2.getLong("count(*)");
				
			}
			
			while(rs3.next()){
				amount3=rs3.getLong("count(*)");
				
			}
			
			return (amount+amount2+amount3);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				rs2.close();
				rs3.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		return amount;
	}
	
	public long getFansedAmountById(final int u_id){
		//f_status
		//1为u1关注u2
		//2为u1与u2互相关注
		//获得被关注的数量
		String sql_getFansedAmountById="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 1";
		String sql_getFansedAmountById2="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 2";
		String sql_getFansedAmountById3="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 2";
		DBConn dbConn = new DBConn();
		ResultSet rs = dbConn.execQuery(sql_getFansedAmountById, new Object[]{u_id});
		ResultSet rs2 = dbConn.execQuery(sql_getFansedAmountById2, new Object[]{u_id});
		ResultSet rs3 = dbConn.execQuery(sql_getFansedAmountById3, new Object[]{u_id});
		long amount = 0;
		long amount2 = 0;
		long amount3 = 0;
		try {
			while(rs.next()){
				amount=rs.getLong("count(*)");
				
			}
			
			while(rs2.next()){
				amount2=rs2.getLong("count(*)");
				
			}
			
			while(rs3.next()){
				amount3=rs3.getLong("count(*)");
				
			}
			
			return (amount+amount2+amount3);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				rs2.close();
				rs3.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		return amount;
	}
	
	public long getFriendAmountById(final int u_id){
		//获得互相关注的好友的数量
		//f_status
		//1为u1关注u2
		//2为u1与u2互相关注
		String sql_getFriendAmount="SELECT count(*) FROM `microblog`.`friend` where u_id = ? and f_status = 2";
		String sql_getFriendAmount2="SELECT count(*) FROM `microblog`.`friend` where u_id2 = ? and f_status = 2";
		DBConn dbConn = new DBConn();
		ResultSet rs = dbConn.execQuery(sql_getFriendAmount, new Object[]{u_id});
		ResultSet rs2 = dbConn.execQuery(sql_getFriendAmount2, new Object[]{u_id});
		long amount = 0;
		long amount2 = 0;
		try {
			while(rs.next()){
				
				amount=rs.getLong("count(*)");	
			}
			while(rs2.next()){
				
				amount2=rs2.getLong("count(*)");	
			}
			return amount+amount2;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				rs2.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		return 0;
	}
	
	public List<Integer> getFansIdListbyUserId(final int u_id){
		List<Integer> fansIdList = new ArrayList<Integer>();
		String sql_getFansListbyUserId = "select u_id2 from friend where u_id = ? and f_status = 1";
		String sql_getFansListbyUserId2 = "select u_id2 from friend where u_id = ? and f_status = 2";
		String sql_getFansListbyUserId3 = "select u_id from friend where u_id2 = ? and f_status = 2";
		
		DBConn dbConn = new DBConn();
		ResultSet rs = dbConn.execQuery(sql_getFansListbyUserId, new Object[]{u_id});
		ResultSet rs2 = dbConn.execQuery(sql_getFansListbyUserId2, new Object[]{u_id});
		ResultSet rs3 = dbConn.execQuery(sql_getFansListbyUserId3, new Object[]{u_id});
		try {
			while(rs.next()){
				fansIdList.add(rs.getInt("u_id2"));
			}
			while(rs2.next()){
				fansIdList.add(rs2.getInt("u_id2"));
			}
			while(rs3.next()){
				fansIdList.add(rs3.getInt("u_id"));
			}
			
			return fansIdList;
		} catch (SQLException e) {		
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				rs2.close();
				rs3.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		return null;
	}
	
	public List<Integer> getFansedIdListbyUserId(final int u_id){
		List<Integer> fansIdList = new ArrayList<Integer>();
		String sql_getFansedListbyUserId = "select u_id from friend where u_id2 = ? and f_status = 2";
		String sql_getFansedListbyUserId3 = "select u_id2 from friend where u_id = ? and f_status = 2";
		String sql_getFansedListbyUserId2 = "select u_id from friend where u_id2 = ? and f_status = 1";
		DBConn dbConn = new DBConn();
		ResultSet rs = dbConn.execQuery(sql_getFansedListbyUserId, new Object[]{u_id});
		ResultSet rs2 = dbConn.execQuery(sql_getFansedListbyUserId2, new Object[]{u_id});
		ResultSet rs3 = dbConn.execQuery(sql_getFansedListbyUserId3, new Object[]{u_id});
		try {
			while(rs.next()){
				fansIdList.add(rs.getInt("u_id"));
			}
			
			while(rs2.next()){
				fansIdList.add(rs2.getInt("u_id"));
			}
			
			while(rs3.next()){
				fansIdList.add(rs3.getInt("u_id2"));
			}
			return fansIdList;
		} catch (SQLException e) {		
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				rs2.close();
				rs3.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbConn.closeConn();
		}
		return null;
	}
	
	/*
	public static void main(String[] args) {
		Friend friend = new Friend();
		friend.setU_id(1);
		friend.setU_id2(6);
		
		FriendDao friendDao = new FriendDao();
		System.out.println(friendDao.addFriend(friend));
		System.out.println(friendDao.getFansAmountById(1));
		System.out.println(friendDao.getFansedAmountById(1));
		System.out.println(friendDao.getFriendAmountById(1));
		System.out.println(friendDao.isFriend(2, 5));
	}
	*/
}