/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */
package com.queplix.core.modules.inbox.utils.db;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.modules.inbox.utils.AccountDAO;
import com.queplix.core.modules.inbox.utils.AccountVO;
import com.queplix.core.utils.dao.AbstractDAO;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.SqlWrapperFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * Description.
 *
 * @author Konstantin Mironov
 * @since 8 Dec 2006
 */
public class AccountDAOImpl extends AbstractDAO implements AccountDAO {

    // ===================================================== Fields

    /**
     * SQL wrapper implementation reference.
     */
    protected SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper();

    // ===================================================== Overridden methods

    /*
     * (non-javadoc)
     *
     * @see AccountDAO#loadAccountVO
     */

    public AccountVO loadAccountVO(long accountID) {

        DEBUG("Try to get data for " + accountID + " mail account");

        AccountVO account = null;
        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_account");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            stmt.setLong(1, accountID);

            ResultSet rs = sqlWrapper.executeQuery(stmt);
            if(!rs.next()) {
                throw new NullPointerException(
                        "Account #" + accountID + " cannot be found!");
            }

            account = buildAccountVO(rs);

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, stmt);
        }

        DEBUG("Got account VO for " + accountID + " : " + account);

        return account;
    } // loadAccountVO(long accountID) : AccountVO

    /*
     * (non-javadoc)
     *
     * @see AccountDAO#loadAllAccountVO
     */
    public Collection loadAllAccountVO() {

        List ret = new ArrayList();

        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_all_accounts");
            ps = sqlWrapper.doPreparedStatement(con, sql);

            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                AccountVO account = buildAccountVO(rs);
                ret.add(account);
            }

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        return ret;
    }

    /*
     * (non-javadoc)
     *
     * @see AccountDAO#loadAllActiveAccountVO
     */
    public Collection loadAllActiveAccountVO(Date nextCheckDate) {

        List ret = new ArrayList();

        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_all_active_accounts");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            sqlWrapper.getTimestampParser().setObject(ps, 1, nextCheckDate);

            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                AccountVO account = buildAccountVO(rs);
                ret.add(account);
            }
        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, ps);
        }
        return ret;
    } // loadAllActiveAccountVO(Date) : Collection

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#loadValidFlag
     */
    public Boolean loadValidFlag(AccountVO account) {

        DEBUG("Try to get valid state for account: " + account);

        Connection con = null;
        PreparedStatement ps = null;

        Boolean validFlag;
        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_valid_state");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            ps.setLong(1, account.getAccountID());

            ResultSet rs = sqlWrapper.executeQuery(ps);
            if(!rs.next()) {
                throw new NullPointerException(
                        "Can't find account: " + account);
            }

            Integer num = sqlWrapper.getIntParser().getValue(rs, 1);
            if(num != null) {
                validFlag = new Boolean(num.intValue() > 0);
            } else {
                validFlag = null;
            }

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        DEBUG("Got valid state: " + validFlag);

        return validFlag;
    } // loadValidFlag(AccountVO) : Boolean

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#loadLastReceiveDate
     */
    public Date loadLastReceiveDate(AccountVO account) {

        DEBUG("Try to get last receive date for account: " + account);

        Connection con = null;
        PreparedStatement ps = null;

        Date lastReceiveDate;
        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_last_receive_date");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            ps.setLong(1, account.getAccountID());

            ResultSet rs = sqlWrapper.executeQuery(ps);
            if(!rs.next()) {
                throw new NullPointerException(
                        "Can't find account: " + account);
            }

            lastReceiveDate = sqlWrapper.getTimestampParser().getValue(rs, 1);

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        DEBUG("Got last receive date: " + lastReceiveDate);

        return lastReceiveDate;
    }

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeValidFlag
     */
    public void storeValidFlag(AccountVO account, Boolean b) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql("update_valid_flag");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            sqlWrapper.getIntParser().setObject(stmt, 1, (b == null) ? null:
                    (b.booleanValue() ? new Integer(1):new Integer(0)));
            stmt.setLong(2, account.getAccountID());
            sqlWrapper.executeUpdate(stmt);

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, stmt);
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeLastReceiveDate
     */
    public void storeLastReceiveDate(AccountVO account, Date d,
                                     String messageUID, String messageDigest) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql("update_last_receive_date");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            sqlWrapper.getTimestampParser().setObject(stmt, 1, d);
            stmt.setString(2, messageUID);
            stmt.setString(3, messageDigest);
            stmt.setLong(4, account.getAccountID());
            sqlWrapper.executeUpdate(stmt);

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, stmt);
        } // try
    } // storeLastReceiveDate(AccountVO, Date, String, String)

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeLastMessageID
     */
    public void storeLastMessageID(AccountVO account, String messageUID,
                                   String messageDigest) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql("update_last_message_id");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            stmt.setString(1, messageUID);
            stmt.setString(2, messageDigest);
            stmt.setLong(3, account.getAccountID());
            sqlWrapper.executeUpdate(stmt);

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, stmt);
        } // try
    } // storeLastMessageID(AccountVO, String, String)

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeNextCheckDate
     */
    public void storeNextCheckDate(AccountVO account, Date d) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {

            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql("update_next_check_date");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            sqlWrapper.getTimestampParser().setObject(stmt, 1, d);
            stmt.setLong(2, account.getAccountID());
            sqlWrapper.executeUpdate(stmt);

        } catch (SQLException ex) {
            throw new GenericSystemException(
                    "SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, stmt);
        }
    } // storeNextCheckDate( AccountVO, Date)

    // ===================================================== Private methods

    // Builds AccountVO from ResultSet <code>rs</code>.

    private AccountVO buildAccountVO(ResultSet rs)
            throws SQLException {

        int i = 1;
        long accountID = rs.getLong(i++);
        String providerName = rs.getString(i++);
        String server = rs.getString(i++);
        Integer port = sqlWrapper.getIntParser().getValue(rs, i++);
        String folderName = rs.getString(i++);
        String name = rs.getString(i++);
        String password = rs.getString(i++);
        Integer delayInMinutes = sqlWrapper.getIntParser().getValue(rs, i++);
        Long maxMessageSize = sqlWrapper.getLongParser().getValue(rs, i++);
        Long maxMailboxSize = sqlWrapper.getLongParser().getValue(rs, i++);
        Boolean deleteMailFlag = null;
        Integer num = sqlWrapper.getIntParser().getValue(rs, i++);
        if(num != null) {
            deleteMailFlag = new Boolean(num.intValue() > 0);
        }
        Boolean markEmailAsSeenFlag = null;
        num = sqlWrapper.getIntParser().getValue(rs, i++);
        if(num != null) {
            markEmailAsSeenFlag = new Boolean(num.intValue() > 0);
        }
        Long defaultOwner = sqlWrapper.getLongParser().getValue(rs, i++);
        Long defaultWorkgroup = sqlWrapper.getLongParser().getValue(rs, i++);

        String messageUID = rs.getString(i++);
        String messageDigest = rs.getString(i++);

        AccountVO account = new AccountVO(accountID,
                providerName,
                server,
                port,
                folderName,
                name,
                password,
                delayInMinutes,
                maxMessageSize,
                maxMailboxSize,
                deleteMailFlag,
                markEmailAsSeenFlag,
                defaultOwner,
                defaultWorkgroup,
                messageUID,
                messageDigest
        );
        return account;
    }

} // class AccountDAOImpl extends AbstractDAO implements AccountDAO
