/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/PostDAOImplJDBC.java,v 1.8 2004/03/20 19:15:04 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.8 $
 * $Date: 2004/03/20 19:15:04 $
 *
 * ====================================================================
 *
 * Copyright (C) 2002-2004 by MyVietnam.net
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * All copyright notices regarding mvnForum MUST remain intact
 * in the scripts and in the outputted HTML.
 * The "powered by" text/logo with a link back to
 * http://www.mvnForum.com and http://www.MyVietnam.net in the
 * footer of the pages MUST remain visible when the pages
 * are viewed on the internet or intranet.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * Support can be obtained from support forums at:
 * http://www.mvnForum.com/mvnforum/index
 *
 * Correspondence and Marketing Questions can be sent to:
 * info@MyVietnam.net
 *
 * @author: Minh Nguyen  minhnn@MyVietnam.net
 * @author: Mai  Nguyen  mai.nh@MyVietnam.net
 */
package com.mvnforum.db.jdbc;

import java.io.StringReader;
import java.sql.*;
import java.util.*;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.mvnforum.db.*;
import net.myvietnam.mvncore.db.DBUtils;
import net.myvietnam.mvncore.exception.*;

public class PostDAOImplJDBC implements PostDAO {

    private static Log log = LogFactory.getLog(PostDAOImplJDBC.class);

    // this variable will support caching if cache for this class is needed
    private static boolean m_dirty = true;

    // Prevent instantiation from classes other than derived classes
    public PostDAOImplJDBC() {
    }

    protected static boolean isDirty() {
        return m_dirty;
    }

    protected static void setDirty(boolean dirty) {
        m_dirty = dirty;
    }

    public void findByPrimaryKey(int postID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT PostID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE PostID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the primary key (" + postID + ") in table 'Post'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.findByPrimaryKey.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ParentPostID, ForumID, ThreadID, MemberID, MemberName,
     *                   LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate,
     *                   PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption,
     *                   PostStatus, PostIcon, PostAttachCount
     * Excluded columns: PostID
     */
    public void create(int parentPostID, int forumID, int threadID,
                        int memberID, String memberName, String lastEditMemberName,
                        String postTopic, String postBody, Timestamp postCreationDate,
                        Timestamp postLastEditDate, String postCreationIP, String postLastEditIP,
                        int postEditCount, int postFormatOption, int postOption,
                        int postStatus, String postIcon, int postAttachCount)
        throws CreateException, DatabaseException, ForeignKeyNotFoundException {

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getForumDAO().findByPrimaryKey(forumID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Forum' does not exist. Cannot create new Post.");
        }

        //allow anonymous/guests to post
        if (memberID!=0) {
            try {
                // @todo: modify the parameter list as needed
                // You may have to regenerate this method if the needed columns dont have attribute 'include'
                DAOFactory.getMemberDAO().findByPrimaryKey(memberID);
            } catch(ObjectNotFoundException e) {
                throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
            }
        }

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getThreadDAO().findByPrimaryKey(threadID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Thread' does not exist. Cannot create new Post.");
        }

        //We allow anonymous/guests to send posts too (if admin allows them to).
        if ((memberName!=null) && (memberName.length()>0)) {
            try {
                // @todo: modify the parameter list as needed
                // You may have to regenerate this method if the needed columns dont have attribute 'include'
                DAOFactory.getMemberDAO().findByAlternateKey_MemberName(memberName);
            } catch(ObjectNotFoundException e) {
                throw new ForeignKeyNotFoundException("Foreign key refers to table 'Member' does not exist. Cannot create new Post.");
            }
        } else memberName=""; /* This is needed, otherwise we will get 'null' in the
                                 sql query, instead of '' */

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            if (parentPostID != 0) {
                findByPrimaryKey(parentPostID);
            }
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Post' does not exist. Cannot create new Post.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (ParentPostID, ForumID, ThreadID, MemberID, MemberName, LastEditMemberName, PostTopic, PostBody, PostCreationDate, PostLastEditDate, PostCreationIP, PostLastEditIP, PostEditCount, PostFormatOption, PostOption, PostStatus, PostIcon, PostAttachCount)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, parentPostID);
            statement.setInt(2, forumID);
            statement.setInt(3, threadID);
            statement.setInt(4, memberID);
            statement.setString(5, memberName);
            statement.setString(6, lastEditMemberName);
            statement.setString(7, postTopic);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(8, new StringReader(postBody), postBody.length());
            } else {
                statement.setString(8, postBody);
            }
            statement.setTimestamp(9, postCreationDate);
            statement.setTimestamp(10, postLastEditDate);
            statement.setString(11, postCreationIP);
            statement.setString(12, postLastEditIP);
            statement.setInt(13, postEditCount);
            statement.setInt(14, postFormatOption);
            statement.setInt(15, postOption);
            statement.setInt(16, postStatus);
            statement.setString(17, postIcon);
            statement.setInt(18, postAttachCount);

            if (statement.executeUpdate() != 1) {
                throw new CreateException("Error adding a row into table 'Post'.");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.create.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int createPost(int parentPostID, int forumID, int threadID,
                       int memberID, String memberName, String lastEditMemberName,
                       String postTopic, String postBody, Timestamp postCreationDate,
                       Timestamp postLastEditDate, String postCreationIP, String postLastEditIP,
                       int postEditCount, int postFormatOption, int postOption,
                       int postStatus, String postIcon, int postAttachCount)
        throws CreateException, DatabaseException, ForeignKeyNotFoundException {

        create(
            parentPostID, forumID, threadID, memberID, memberName, lastEditMemberName,
            postTopic, postBody, postCreationDate, postLastEditDate,
            postCreationIP, postLastEditIP, postEditCount, postFormatOption,
            postOption, postStatus, postIcon, postAttachCount);

        int postID = 0;
        try {
            postID = findPostID(forumID, memberName, postCreationDate);
        } catch (ObjectNotFoundException ex) {
            // Hack the Oracle 9i problem
            Timestamp roundTimestamp = new Timestamp((postCreationDate.getTime()/1000)*1000);
            try {
               postID = findPostID(forumID, memberName, roundTimestamp);
            } catch (ObjectNotFoundException e) {
               throw new CreateException("Cannot find the PostID in table Post.");
            }
        }
        return postID;
    }

    public void delete(int postID)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE PostID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, postID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot delete a row in table Post where PostID = (" + postID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.delete.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void delete_inThread(int threadID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE ThreadID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, threadID);

            statement.executeUpdate();

            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.delete_inThread.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void delete_inForum(int forumID)
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE ForumID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, forumID);
            statement.executeUpdate();
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in PostDAOImplJDBC.delete_inForum.");
        } finally {