/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/ThreadDAOImplJDBC.java,v 1.14 2004/06/03 19:14:23 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.14 $
 * $Date: 2004/06/03 19:14:23 $
 *
 * ====================================================================
 *
 * 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 com.mvnforum.db.*;
import net.myvietnam.mvncore.db.DBUtils;
import net.myvietnam.mvncore.exception.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ThreadDAOImplJDBC implements ThreadDAO {

    private static Log log = LogFactory.getLog(ThreadDAOImplJDBC.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 ThreadDAOImplJDBC() {
    }

    protected static boolean isDirty() {
        return m_dirty;
    }

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

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

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

    /*
     * Included columns: ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody,
     *                   ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType,
     *                   ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
     *                   ThreadIcon, ThreadDuration
     * Excluded columns: ThreadID
     */
    private void create(int forumID, String memberName, String lastPostMemberName,
                        String threadTopic, String threadBody, int threadVoteCount,
                        int threadVoteTotalStars, Timestamp threadCreationDate, Timestamp threadLastPostDate,
                        int threadType, int threadOption, int threadStatus,
                        int threadHasPoll, int threadViewCount, int threadReplyCount,
                        String threadIcon, int threadDuration)
        throws CreateException, DatabaseException, ForeignKeyNotFoundException {

        ThreadBean.validateThreadStatus(threadStatus);

        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 Thread.");
        }

        /* Here we allow memberName to be empty or null, which means
           unknown user or guest created the thread. */
        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 '' */

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (ForumID, MemberName, LastPostMemberName, ThreadTopic, ThreadBody, ThreadVoteCount, ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption, ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount, ThreadIcon, ThreadDuration)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, forumID);
            statement.setString(2, memberName);
            statement.setString(3, lastPostMemberName);
            statement.setString(4, threadTopic);
            if (DBUtils.getDatabaseType() == DBUtils.DATABASE_ORACLE) {
                statement.setCharacterStream(5, new StringReader(threadBody), threadBody.length());
            } else {
                statement.setString(5, threadBody);
            }
            statement.setInt(6, threadVoteCount);
            statement.setInt(7, threadVoteTotalStars);
            statement.setTimestamp(8, threadCreationDate);
            statement.setTimestamp(9, threadLastPostDate);
            statement.setInt(10, threadType);
            statement.setInt(11, threadOption);
            statement.setInt(12, threadStatus);
            statement.setInt(13, threadHasPoll);
            statement.setInt(14, threadViewCount);
            statement.setInt(15, threadReplyCount);
            statement.setString(16, threadIcon);
            statement.setInt(17, threadDuration);

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

    public int createThread(int forumID, String memberName, String lastPostMemberName,
                        String threadTopic, String threadBody, int threadVoteCount,
                        int threadVoteTotalStars, Timestamp threadCreationDate, Timestamp threadLastPostDate,
                        int threadType, int threadOption, int threadStatus,
                        int threadHasPoll, int threadViewCount, int threadReplyCount,
                        String threadIcon, int threadDuration)
                        throws ObjectNotFoundException, CreateException, DatabaseException, ForeignKeyNotFoundException {

        create(forumID, memberName, lastPostMemberName, threadTopic, threadBody, threadVoteCount, threadVoteTotalStars, threadCreationDate, threadLastPostDate, threadType, threadOption, threadStatus, threadHasPoll, threadViewCount, threadReplyCount, threadIcon, threadDuration);
        int threadID = 0;
        try {
            threadID = findThreadID(forumID, memberName, threadCreationDate);
        } catch (ObjectNotFoundException ex) {
            // Hack the Oracle 9i problem
            Timestamp roundTimestamp = new Timestamp((threadCreationDate.getTime()/1000)*1000);
            threadID = findThreadID(forumID, memberName, roundTimestamp);
        }
        return threadID;
    }

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

        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);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot delete a row in table Thread where primary key = (" + threadID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in ThreadDAOImplJDBC.delete.");
        } 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 ThreadDAOImplJDBC.delete_inForum.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: ThreadTopic, ThreadBody, ThreadIcon
     * Excluded columns: ThreadID, ForumID, MemberName, LastPostMemberName, ThreadVoteCount,
     *                   ThreadVoteTotalStars, ThreadCreationDate, ThreadLastPostDate, ThreadType, ThreadOption,
     *                   ThreadStatus, ThreadHasPoll, ThreadViewCount, ThreadReplyCount,
     *                   ThreadDuration