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.factory.DAOFactory;
import com.lovo.po.PublishPO;
import com.lovo.po.ReplyPO;
import com.lovo.po.UserPO;
import com.lovo.util.DBUtil;
import com.lovo.util.Page;

public class ReplyDAOImpl implements ReplyDAO {
	
	private Connection con = null;

	private PreparedStatement st = null;

	private ResultSet rs = null;

	/**
	 * 添加回復貼
	 * @param replyPO
	 * @throws SQLException
	 */
	public void insert(ReplyPO replyPO) throws SQLException {
		String sql = "insert into reply(publish_id,user_id,content,replyDate)" +
				" values(?, ?, ?, date_format(now(),'%Y-%m-%d %H:%i:%s'))";
		try {
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, replyPO.getPublish().getId());
			st.setInt(2, replyPO.getReplyMan().getId());
			st.setString(3, replyPO.getContent());
			
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			DBUtil.getDBUtil().close(st);
			DBUtil.getDBUtil().close(con);
		}
		
	}
	
	/**
	 * 刪除回復貼
	 * @param id
	 * @throws SQLException
	 */
	public void delete(int id) throws SQLException {
		String sql = "delete from reply where id = ?"; 
		try {
			con = DBUtil.getDBUtil().getConnection();
			//得到語句對象
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally{
			DBUtil.getDBUtil().close(st);
			DBUtil.getDBUtil().close(con);
		}
	}
	
	/**
	 * 查詢某個用戶的所有帖子
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<ReplyPO> queryByUserId(int id) throws SQLException {
		List<ReplyPO> list = new ArrayList<ReplyPO> ();
		String sql = "select * from reply where user_id = ?";
		try{
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			while(rs.next()){
				ReplyPO replyPO = new ReplyPO();
				this.initReply(rs, replyPO);
				list.add(replyPO);
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw e;
		}
		return list;
	}
	
	/**
	 * 查詢某個主題的所有恢復帖子
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<ReplyPO> queryByPublish(int id, Page page) throws SQLException {  
		List<ReplyPO> list = new ArrayList<ReplyPO> ();
		String sql = "select * from reply where publish_id = ? limit ?, ?";
		try{
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			st.setInt(2, (page.getCurrPage() - 1) * page.getPageRows());
			st.setInt(3, page.getPageRows());
			rs = st.executeQuery();
			while(rs.next()){
				ReplyPO replyPO = new ReplyPO();
				
				this.initReply(rs, replyPO);
				
				list.add(replyPO);
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw e;
		}
		return list;
	}
	
	public List<ReplyPO> queryByContent(String content, Page page) throws SQLException {  
		List<ReplyPO> list = new ArrayList<ReplyPO> ();
		String sql = "select * from reply where content like ? limit ?, ?";
		try{
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setString(1, content);
			st.setInt(2, (page.getCurrPage() - 1) * page.getPageRows());
			st.setInt(3, page.getPageRows());
			rs = st.executeQuery();
			while(rs.next()){
				ReplyPO replyPO = new ReplyPO();
				
				this.initReply(rs, replyPO);
				
				list.add(replyPO);
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw e;
		}
		return list;
	}
	/**
	 * 根據ID具體查詢某個回復貼
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public ReplyPO queryById(int id) throws SQLException {
		String sql = "select * from reply where id = ?";
		ReplyPO replyPO = null;
		try{
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			while(rs.next()){
				replyPO = new ReplyPO();
				this.initReply(rs, replyPO);
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw e;
		}
		return replyPO;
	}
	
	public int queryReplyNumByPublish(int id) throws SQLException {
		String sql = "select count(*) maxrows from reply where publish_id = ?";
		int maxRows = 0;
		try{
			con = DBUtil.getDBUtil().getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, id);
			rs = st.executeQuery();
			while(rs.next()){
				maxRows = rs.getInt("maxrows");
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw e;
		}
		return maxRows;
	}
	
	public void initReply(ResultSet rs, ReplyPO replyPO) throws SQLException {
		PublishPO publish = new PublishPO();
		UserPO replyMan = new UserPO();
		replyMan.setId(rs.getInt("user_id"));
		publish.setId(rs.getInt("publish_id"));
		replyPO.setId(rs.getInt("id"));
		replyPO.setContent(rs.getString("content"));
		replyPO.setPublish(publish);
		replyPO.setReplyDate(rs.getString("replydate"));
		replyPO.setReplyMan(replyMan);
	}

	
}