package com.hongshee.ejforum.data;

/**
 * <p>Title: TrashBoxDAO.java</p>
 * <p>Description: Trash box data access object</p>
 * <p>Copyright: Hongshee Software (c) 2008</p>
 * @author jackie du
 * @version 1.0
 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Calendar;

import javax.servlet.http.HttpServletRequest;

import com.hongshee.ejforum.util.PageUtils;
import com.hongshee.ejforum.common.ForumSetting;

public class TrashBoxDAO extends EntityDAO 
{
    private static TrashBoxDAO _dao = null;

    protected TrashBoxDAO()
    {}

    public static TrashBoxDAO getInstance()
    {
        if (_dao == null)
        {
            _dao = new TrashBoxDAO();
        }
        return _dao;
    } 
        
    /**
     * Add trashed records of topic or reply
     * @param 
     *      conn - DB Connection
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void addTrashes(String[] boardIDs, String[] boardNames, 
                           String[] topicIDs, String[] topicTitles, 
                           String[] replyIDs, String[] userIDs,
                           String deleteUser, Connection conn) throws SQLException
    {
        PreparedStatement pstmtInsert = null;
        try
        {
            pstmtInsert = conn.prepareStatement(adapter.TrashBox_Insert);
            for (int i=0; i<topicIDs.length; i++)
            {
                pstmtInsert.setString(1, topicIDs[i]);
                if (replyIDs == null)
                    pstmtInsert.setString(2, "0");
                else
                    pstmtInsert.setString(2, replyIDs[i]);
                pstmtInsert.setString(3, boardIDs[i]);
                pstmtInsert.setString(4, boardNames[i]);
                pstmtInsert.setString(5, topicTitles[i]);
                pstmtInsert.setString(6, userIDs[i]);
                pstmtInsert.setString(7, deleteUser);
                pstmtInsert.addBatch();
            }
            pstmtInsert.executeBatch();
        }
        finally
        {
            dbManager.closePStatement(pstmtInsert);
        }
    }

    /**
     * Delete trashes by query conditions
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void deleteTrashes(HttpServletRequest request) throws Exception
    {
        ArrayList<Object> paramValues = new ArrayList<Object>();
        String whereSql = this.buildSearchWhereSql(request, paramValues);
        Connection conn = dbManager.getConnection();
        try
        {
            conn.setAutoCommit(false);

            String sql = "select topicID,replyID from ejf_trash_box" + whereSql;                
            ArrayList<HashMap> postList = this.execSelectSql(sql, paramValues, conn);
            
            sql = "delete from ejf_trash_box" + whereSql;
            this.execUpdateSql(sql, paramValues, conn);
            
            if (postList != null && postList.size() > 0)
                removePosts(postList, conn);
            
            // Add admin log
            ActionLogDAO.getInstance().addAdminLog(request, "刪除回收站帖子", "", conn);
            conn.commit();
        }
        catch(SQLException se)
        {
            conn.rollback();
            throw se;
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }

    /**
     * Delete a trash record
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void deleteTrash(HttpServletRequest request) throws Exception
    {
        String topicID = request.getParameter("tid");
        String replyID = request.getParameter("rid");
        
        ArrayList<Object> paramValues = new ArrayList<Object>();
        String whereSql = " where topicID=? and replyID=?";
        paramValues.add(topicID);
        paramValues.add(replyID);

        ArrayList<HashMap> postList = new ArrayList<HashMap>();
        HashMap<String, String> record = new HashMap<String, String>();
        record.put("TOPICID", topicID);
        record.put("REPLYID", replyID);
        postList.add(record);
        
        Connection conn = dbManager.getConnection();
        try
        {
            conn.setAutoCommit(false);

            String sql = "delete from ejf_trash_box" + whereSql;
            this.execUpdateSql(sql, paramValues, conn);
            
            removePosts(postList, conn);
            
            // Add admin log
            ActionLogDAO.getInstance().addAdminLog(request, "刪除回收站帖子", "", conn);
            conn.commit();
        }
        catch(SQLException se)
        {
            conn.rollback();
            throw se;
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }

    public void cleanExpiredTrashes() throws Exception
    {
        int keepMonths = 
            ForumSetting.getInstance().getInt(ForumSetting.FUNCTIONS, "trashKeepMonths");
        if (keepMonths <= 0) return;
        
        Connection conn = dbManager.getConnection();
        try
        {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.MONTH, (-1)*keepMonths);

            SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
            String expireDate = dateFormatter.format(cal.getTime());
            ArrayList<Object> paramValues = new ArrayList<Object>();
            paramValues.add(expireDate);

            this.execUpdateSql(adapter.TrashBox_CleanExpired, paramValues, conn);
            this.execUpdateSql(adapter.TrashBox_RemoveTopic, null, conn);
            this.execUpdateSql(adapter.TrashBox_RemoveReply, null, conn);
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }
    
    /**
     * Restore trashed topics or replies by query conditions
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void restoreTrashes(HttpServletRequest request) throws Exception
    {
        ArrayList<Object> paramValues = new ArrayList<Object>();
        String whereSql = this.buildSearchWhereSql(request, paramValues);
        Connection conn = dbManager.getConnection();
        try
        {
            conn.setAutoCommit(false);

            String sql = "select topicID, replyID from ejf_trash_box" + whereSql;                
            ArrayList<HashMap> postList = this.execSelectSql(sql, paramValues, conn);
            
            sql = "delete from ejf_trash_box" + whereSql;
            this.execUpdateSql(sql, paramValues, conn);
            
            if (postList != null && postList.size() > 0)
                restorePosts(postList, conn);
            
            // Add admin log
            ActionLogDAO.getInstance().addAdminLog(request, "還原回收站帖子", "", conn);
            conn.commit();
        }
        catch(SQLException se)
        {
            conn.rollback();
            throw se;
        }
        finally
        {
            dbManager.closeConnection(conn);
        }
    }
    
    /**
     * Restore a trash record
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void restoreTrash(HttpServletRequest request) throws Exception
    {
        String topicID = request.getParameter("tid");
        String replyID = request.getParameter("rid");
        
        ArrayList<Object> paramValues = new ArrayList<Object>();
        String whereSql = " where topicID=? and replyID=?";
        paramValues.add(topicID);
        paramValues.add(replyID);

        ArrayList<HashMap> postList = new ArrayList<HashMap>();
        HashMap<String, String> record = new HashMap<String, String>();
        record.put("TOPICID", topicID);
        record.put("REPLYID", replyID);
        postList.add(record);