package com.hongshee.ejforum.data;

/**
 * <p>Title: ForumBoardDAO.java</p>
 * <p>Description: Forum board data access object</p>
 * <p>Copyright: Copyright (c) 2007</p>
 * <p>Company: hongshee</p>
 * @author jackie
 * @version 1.0
 */

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;

import com.hongshee.ejforum.common.CacheManager;
import com.hongshee.ejforum.common.IConstants;
import com.hongshee.ejforum.data.SectionDAO.SectionVO;
import com.hongshee.ejforum.util.AppUtils;
import com.hongshee.ejforum.util.PageUtils;

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

    protected BoardDAO()
    {}

    public static BoardDAO getInstance()
    {
        if (_dao == null)
        {
            _dao = new BoardDAO();
        }
        return _dao;
    } 
        
    /**
     * Add a forum board
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void addBoard(HttpServletRequest request) throws SQLException
    {
        String sectionID = PageUtils.getParam(request,"sectionID");
        CacheManager cache = CacheManager.getInstance();
        SectionVO aSection = cache.getSection(sectionID);
            
        int seqno = 1;
        if (aSection != null 
                && aSection.boardList != null && aSection.boardList.size() > 0)
        {
            BoardVO aBoard = aSection.boardList.get(aSection.boardList.size()-1);
            seqno = aBoard.seqno + 1; 
        }

        ArrayList<Object> paramValues = new ArrayList<Object>();
        paramValues.add(sectionID);
        paramValues.add(PageUtils.getParam(request,"boardName"));
        paramValues.add(seqno);
        this.execUpdateSql(adapter.Board_Insert, paramValues);
    }

    /**
     * Update forum board info
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void updateBoard(HttpServletRequest request) throws SQLException
    {
        Connection conn = null;
        PreparedStatement pstmtUpdate = null;
        try
        {
            String[] groups = request.getParameterValues("allowGroups");
            StringBuilder allowGroups = new StringBuilder();
            if (groups != null)
            {
                for (int i=0; i<groups.length; i++)
                {
                    allowGroups.append(groups[i].trim());
                }
            }

            StringBuilder aclGroups = new StringBuilder();
            
            String[] visits = request.getParameterValues("visitGroups");
            if (visits != null)
            {
                aclGroups.append(IConstants.PERMIT_VISIT_FORUM).append("_");
                for (int i=0; i<visits.length; i++)
                {
                    aclGroups.append(visits[i].trim());
                }
                aclGroups.append(",");
            }
            String[] topics = request.getParameterValues("topicGroups");
            if (topics != null)
            {
                aclGroups.append(IConstants.PERMIT_NEW_TOPIC).append("_");
                for (int i=0; i<topics.length; i++)
                {
                    aclGroups.append(topics[i].trim());
                }
                aclGroups.append(",");
            }
            String[] replies = request.getParameterValues("replyGroups");
            if (replies != null)
            {
                aclGroups.append(IConstants.PERMIT_NEW_REPLY).append("_");
                for (int i=0; i<replies.length; i++)
                {
                    aclGroups.append(replies[i].trim());
                }
                aclGroups.append(",");
            }
            String[] downloads = request.getParameterValues("downloadGroups");
            if (downloads != null)
            {
                aclGroups.append(IConstants.PERMIT_DOWNLOAD).append("_");
                for (int i=0; i<downloads.length; i++)
                {
                    aclGroups.append(downloads[i].trim());
                }
                aclGroups.append(",");
            }
            String[] uploads = request.getParameterValues("uploadGroups");
            if (uploads != null)
            {
                aclGroups.append(IConstants.PERMIT_UPLOAD).append("_");
                for (int i=0; i<uploads.length; i++)
                {
                    aclGroups.append(uploads[i].trim());
                }
                aclGroups.append(",");
            }
            
            String oldSectionID = PageUtils.getParam(request,"oldSectionID");
            String sectionID = PageUtils.getParam(request,"sectionID");
            String boardID = PageUtils.getParam(request,"boardID");
            
            conn = dbManager.getConnection();
            conn.setAutoCommit(false);
            
            pstmtUpdate = conn.prepareStatement(adapter.Board_Update);
            pstmtUpdate.setString(1, sectionID);
            pstmtUpdate.setString(2, PageUtils.getParam(request,"boardName"));
            pstmtUpdate.setString(3, PageUtils.getParam(request,"highColor"));
            pstmtUpdate.setString(4, PageUtils.getParam(request,"brief"));
            pstmtUpdate.setString(5, PageUtils.getParam(request,"keywords"));
            pstmtUpdate.setString(6, PageUtils.getParam(request,"viewStyle"));
            pstmtUpdate.setString(7, PageUtils.getParam(request,"sortField"));
            pstmtUpdate.setString(8, PageUtils.getParam(request,"isImageOK"));
            pstmtUpdate.setString(9, PageUtils.getParam(request,"isMediaOK"));
            pstmtUpdate.setString(10, PageUtils.getParam(request,"isGuestPostOK"));
            pstmtUpdate.setString(11, allowGroups.toString());
            pstmtUpdate.setString(12, aclGroups.toString());
            pstmtUpdate.setString(13, PageUtils.getParam(request,"headAdCode"));
            pstmtUpdate.setString(14, PageUtils.getParam(request,"footAdCode"));
            pstmtUpdate.setString(15, PageUtils.getParam(request,"state"));
            pstmtUpdate.setString(16, boardID);
            pstmtUpdate.executeUpdate();
            
            if (!sectionID.equals(oldSectionID))
            {
                ArrayList<Object> paramValues = new ArrayList<Object>();
                paramValues.add(sectionID);
                paramValues.add(boardID);
                this.execUpdateSql(adapter.Topic_ModSection, paramValues, conn);
            }
            
            conn.commit();
        }
        catch(SQLException se)
        {
            conn.rollback();
            throw se;
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
            dbManager.closeConnection(conn);
        }
    }

    /**
     * Copy board setting info to other boards
     * @param 
     *      request - HttpServletRequest
     * @return none
     * @throws SQLException
     * @since 1.0
     */
    public void copyBoardInfo(HttpServletRequest request) throws SQLException
    {
        Connection conn = null;
        PreparedStatement pstmtUpdate = null;
        try
        {
            String sectionID = request.getParameter("sectionID");
            String boardID = request.getParameter("boardID");
            String[] targetIDs = request.getParameterValues("targetBoards");

            CacheManager cache = CacheManager.getInstance();
            BoardVO aBoard = cache.getBoard(sectionID, boardID);
            
            conn = dbManager.getConnection();
            pstmtUpdate = conn.prepareStatement(adapter.Board_CopyInfo);
            int p = -1;
            for (int i=0; i<targetIDs.length; i++)
            {
                p = targetIDs[i].indexOf('_');  // sectionID_boardID
                pstmtUpdate.setString(1, aBoard.viewStyle);
                pstmtUpdate.setString(2, aBoard.sortField);
                pstmtUpdate.setString(3, String.valueOf(aBoard.isImageOK));
                pstmtUpdate.setString(4, String.valueOf(aBoard.isMediaOK));
                pstmtUpdate.setString(5, String.valueOf(aBoard.isGuestPostOK));
                pstmtUpdate.setString(6, aBoard.allowGroups);
                pstmtUpdate.setString(7, aBoard.acl);
                pstmtUpdate.setString(8, aBoard.headAdCode);
                pstmtUpdate.setString(9, aBoard.footAdCode);
                pstmtUpdate.setString(10, targetIDs[i].substring(p+1));
                pstmtUpdate.addBatch();
            }
            pstmtUpdate.executeBatch();
        }
        finally
        {
            dbManager.closePStatement(pstmtUpdate);
            dbManager.closeConnection(conn);
        }
    }
    
    /**
     * Modify board moderator
     * @param 
     *      sectionID - Section ID
     *      boardID - Board ID
     *      moderator - Board moderator
     * @return string message 
     * @throws SQLException
     * @since 1.0
     */
    public String modifyModerator(String sectionID, String boardID, String moderator) 
                                                                throws SQLException
    {
        Connection conn = null;
        try
        {
            String[] users = moderator.split(",");
            conn = dbManager.getConnection();
            
            for (int i=0; i<users.length; i++)
            {
                if (users[i].trim().length() > 0)
                {
                    if (!UserDAO.getInstance().isExistedID(users[i], conn))
                    {
                        return "用戶名不存在：" + users[i];
                    }
                }
            }