package com.lovo.dao;

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

import com.lovo.dao.MessageDAO;
import com.lovo.po.MessagePO;
import com.lovo.po.UserPO;
import com.lovo.util.DBUtil;

public class MessageDAOImpl implements MessageDAO {
	Connection con;

	ResultSet rs;

	PreparedStatement pre;
	
	// 發送
	public void insert(MessagePO message) throws SQLException {
		String sql = "insert into Message (receiver_id  ,sender_id ,title ,content,sendate) values (?,?,?,?, date_format(now(),'%Y-%m-%d %H:%i:%s'))";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, message.getReceiveUser().getId());
			pre.setInt(2, message.getSendUser().getId());
			pre.setString(3, message.getTitle());
			pre.setString(4, message.getContent());

			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

	}

	// 刪除
	public void delete(int id) throws SQLException {
		String sql = "delete from Message where id = ?";
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(sql);
			pre.setInt(1, id);
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

	}
	

	// 查詢
	public List<MessagePO> queryMsgByReceiverId(int id) throws SQLException {
		List<MessagePO> list = new ArrayList<MessagePO>();
		String msgSql = "select * from message where receiver_id = ? ";
		
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(msgSql);
			pre.setInt(1, id);
			rs = pre.executeQuery();

			while (rs.next()) {
				MessagePO message = new MessagePO();
				
				message.setId(rs.getInt("id"));
				UserPO sendUser = new UserPO();
				sendUser.setId(rs.getInt("sender_id"));
				UserPO receiver = new UserPO();
				receiver.setId(rs.getInt("receiver_id"));
				message.setReceiveUser(receiver);
				message.setSendUser(sendUser);
				message.setTitle(rs.getString("title"));
				message.setContent(rs.getString("content"));
				message.setSendDate(rs.getDate("sendate").toString());
				message.setReadFlag(rs.getInt("readflag"));
				list.add(message);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return list;
	}

	public void update(MessagePO message) throws SQLException {
		String msgSql = "update message set readflag = ? where id = ? ";
		System.out.println("message.getReadFlag() =" + message.getReadFlag());
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(msgSql);
			pre.setInt(1, message.getReadFlag());
			pre.setInt(2, message.getId());
			pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

	}

	public MessagePO queryById(int id) throws SQLException {
		String msgSql = "select * from message where id = ? ";
		MessagePO message = null;
		try {
			con = DBUtil.getDBUtil().getConnection();
			pre = con.prepareStatement(msgSql);
			pre.setInt(1, id);
			rs = pre.executeQuery();

			while (rs.next()) {
				message = new MessagePO();
				
				message.setId(rs.getInt("id"));
				UserPO sendUser = new UserPO();
				sendUser.setId(rs.getInt("sender_id"));
				UserPO receiver = new UserPO();
				receiver.setId(rs.getInt("receiver_id"));
				message.setReceiveUser(receiver);
				message.setSendUser(sendUser);
				message.setTitle(rs.getString("title"));
				message.setContent(rs.getString("content"));
				message.setReadFlag(rs.getInt("readflag"));
				message.setSendDate(rs.getDate("sendate").toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.getDBUtil().close(rs);
			DBUtil.getDBUtil().close(pre);
			DBUtil.getDBUtil().close(con);
		}

		return message;
	}

	


}