/*
 * 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.alert.utils.db;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.modules.alert.AlertBlock;
import com.queplix.core.modules.alert.AlertData;
import com.queplix.core.modules.alert.utils.AlertDAO;
import com.queplix.core.modules.alert.utils.AlertVO;
import com.queplix.core.modules.eql.CompoundKey;
import com.queplix.core.utils.StringHelper;
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.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Project-specific implementation of the AlertDAO
 *
 * @author [ALB] Baranov Andrey
 * @version $Revision: 1.1.1.1 $ $Date: 2005/09/12 15:30:10 $
 */

public class AlertDAOImpl
        extends AbstractDAO implements AlertDAO {

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

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

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

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#deleteAlertVO
     */

    public int deleteAlertVO(AlertVO alert) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();

            // Delete blocks.
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "delete_block_alerts"));
            ps.setLong(1, alert.getAlertID());
            sqlWrapper.executeUpdate(ps);
            ps.close();

            // Delete main record.
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "delete_alert"));
            ps.setLong(1, alert.getAlertID());

            return sqlWrapper.executeUpdate(ps);

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

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

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#blockAlertVO
     */
    public int blockAlertVO(AlertVO alert, AlertBlock block) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            long id = sqlWrapper.getNextKey(con, DBRealmManager.getSql(
                    "alert_block_table"));
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "insert_block_alert"));
            ps.setLong(1, id);
            ps.setLong(2, alert.getAlertID());
            ps.setLong(3, block.getUserID());
            ps.setInt(4, block.getUserType());

            return sqlWrapper.executeUpdate(ps);

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

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

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#getNextAlertID
     */
    public long getNextAlertID() {
        Connection con = null;

        try {
            con = sqlWrapper.doConnection();
            return sqlWrapper.getNextKey(con, DBRealmManager.getSql(
                    "alert_table"));

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

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

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#storeAlertVO
     */
    public int storeAlertVO(AlertVO alert) {

        Connection con = null;
        PreparedStatement ps = null;

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

            // Store mandatory data.
            int i = 1;
            ps.setLong(i++, alert.getAlertID());
            ps.setLong(i++, alert.getCreatorID());
            ps.setInt(i++, alert.getCreatorType());
            ps.setLong(i++, alert.getSenderID());
            ps.setInt(i++, alert.getSenderType());
            ps.setString(i++, alert.getMessage());
            ps.setInt(i++, alert.getSeverity());
            sqlWrapper.getTimestampParser().setValue(ps, i++,
                    alert.getDateposted());

            // Store optional data.
            sqlWrapper.getLongParser().setValue(ps, i++,
                    alert.getRecipientID());
            sqlWrapper.getIntParser().setValue(ps, i++,
                    alert.getRecipientType());
            sqlWrapper.getLongParser().setValue(ps, i++,
                    alert.getWorkgroupID());
            sqlWrapper.getIntParser().setValue(ps, i++, alert.getTier());
            ps.setInt(i++, alert.isToAll() ? 1:0);

            // Store add-on data.
            AlertData data = alert.getData();
            if(data != null) {
                // .. put values
                CompoundKey recordKey = data.getRecordKey();
                int keys = recordKey.size();

                ps.setString(i++, data.getFocusID());
                ps.setString(i++, data.getTabID());
                ps.setString(i++, data.getFormID());
                for(int j = 0; j < 4; j++) {
                    if(j < keys) {
                        ps.setString(i++, (String) recordKey.getKey(j));
                    } else {
                        // .. put empty string
                        ps.setString(i++, "");
                    }
                }

            } else {
                // .. put nulls
                while(i <= 20) {
                    ps.setNull(i++, Types.VARCHAR);
                }
            }

            return sqlWrapper.executeUpdate(ps);

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

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

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#updateAlertVO
     */
    public int updateAlertVO(AlertVO alert) {
        Connection con = null;
        PreparedStatement ps = null;

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

            // Store mandatory data.
            int i = 1;
            ps.setString(i++, alert.getMessage());
            ps.setInt(i++, alert.getSeverity());
            ps.setLong(i++, alert.getAlertID());

            return sqlWrapper.executeUpdate(ps);

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

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

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#loadAlertVO
     */
    public AlertVO loadAlertVO(long alertID) {

        if(getLogger().isDebugEnabled()) {
            DEBUG("Try to get data for " + alertID + " alert");
        }

        AlertVO alert = null;
        Connection con = null;
        PreparedStatement ps = null;

        try {
            // Open connection.
            con = sqlWrapper.doConnection();

            // Execute main SQL query.
            String sql = DBRealmManager.getSql("get_alert");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            ps.setLong(1, alertID);

            // Read main data.
            ResultSet rs = sqlWrapper.executeQuery(ps);
            if(!rs.next()) {
                return null;
            }
            alert = createAlert(rs);
            rs.close();
            ps.close();

            // Execute block SQL query.
            sql = DBRealmManager.getSql("select_block_alerts");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            ps.setLong(1, alertID);

            // Read blocks.
            rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                AlertBlock block = createBlock(rs, 1);
                alert.addBlock(block);
            }

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

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

        if(getLogger().isDebugEnabled()) {
            DEBUG("Got alert VO for " + alertID + " : " + alert);
        }

        return alert;
    }

    /*
     * (non-javadoc)
     *
     * @see AlertDAO#loadAlertVO
     */
    public Collection loadAllAlertVO() {

        Connection con = null;
        PreparedStatement ps = null;

        Collection alertList = new ArrayList();
        Map blockMap = new HashMap();

        try {
            // Open connection.
            con = sqlWrapper.doConnection();

            // Execute block SQL query.
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_all_block_alerts"));

            // Read all blocks and put to the Map <code>blockMap</code>.
            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                Long alertID = new Long(rs.getLong(1));
                AlertBlock block = createBlock(rs, 2);

                List l = (List) blockMap.get(alertID);
                if(l == null) {
                    l = new ArrayList();
                    blockMap.put(alertID, l);
                }
                l.add(block);
            }
            rs.close();
            ps.close();

            // Execute main SQL query.
            ps = sqlWrapper.doPreparedStatement(con, DBRealmManager.getSql(
                    "select_all_alert"));

            // Read main data.
            rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                AlertVO alert = createAlert(rs);
                alertList.add(alert);

                // Add blocks if any exist.
                Long alertID = new Long(alert.getAlertID());
                List l = (List) blockMap.get(alertID);
                if(l != null) {
                    for(int i = 0; i < l.size(); i++) {
                        alert.addBlock((AlertBlock) l.get(i));
                    }
                }
            }

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

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

        return alertList;
    }

    // ===================================================== Protected methods

    //
    // Creates AlertVO from ResultSet
    //

    protected AlertVO createAlert(ResultSet rs)
            throws SQLException {

        // Read mandatory data.
        int i = 1;
        long alertID = rs.getLong(i++);
        long creatorID = rs.getLong(i++);
        int creatorType = rs.getInt(i++);
        long senderID = rs.getLong(i++);
        int senderType = rs.getInt(i++);
        String message = rs.getString(i++);
        int severity = rs.getInt(i++);
        Date dateposted = sqlWrapper.getTimestampParser().getValue(rs, i++);

        AlertVO alertVO = new AlertVO(alertID,
                creatorID,
                creatorType,
                senderID,
                senderType,
                message,
                severity,
                dateposted);

        // Read optional data.
        Long recipientID = sqlWrapper.getLongParser().getValue(rs, i++);
        Integer recipientType = sqlWrapper.getIntParser().getValue(rs, i++);
        if(recipientID != null) {
            if(recipientType == null) {
                throw new NullPointerException(
                        "Recipeint Type for Alert #" + alertID + " is NULL");
            }
            alertVO.setUserRecipient(recipientID.longValue(),
                    recipientType.intValue());
        }

        Long workgroupID = sqlWrapper.getLongParser().getValue(rs, i++);
        Integer tier = sqlWrapper.getIntParser().getValue(rs, i++);
        if(workgroupID != null) {
            alertVO.setWorkgroupRecipient(workgroupID.longValue(), tier);
        }

        boolean toAll = (rs.getInt(i++) == 1);
        alertVO.setToAllRecipient(toAll);

        // Read add-on data.
        String focusID = rs.getString(i++);
        if(focusID != null) {
            String tabID = rs.getString(i++);
            String formID = rs.getString(i++);

            CompoundKey recordKey = new CompoundKey();
            recordKey.addKey(rs.getString(i++));

            String recordId2 = rs.getString(i++);
            if(!StringHelper.isEmpty(recordId2)) {
                recordKey.addKey(recordId2);
            }
            String recordId3 = rs.getString(i++);
            if(!StringHelper.isEmpty(recordId3)) {
                recordKey.addKey(recordId3);
            }
            String recordId4 = rs.getString(i++);
            if(!StringHelper.isEmpty(recordId4)) {
                recordKey.addKey(recordId4);
            }

            alertVO.setData(new AlertData(focusID, tabID, formID, recordKey));
        }

        return alertVO;
    }

    //
    // Creates AlertBlock from ResultSet
    //
    protected AlertBlock createBlock(ResultSet rs, int i)
            throws SQLException {

        long recipientID = rs.getLong(i++);
        int recipientType = rs.getInt(i++);

        return new AlertBlock(recipientID, recipientType);
    }
}
