/**
 * Copyright © 2001 The JA-SIG Collaborative.  All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *u
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. Redistributions of any form whatsoever must retain the following
 *    acknowledgment:
 *    "This product includes software developed by the JA-SIG Collaborative
 *    (http://www.jasig.org/)."
 *
 * THIS SOFTWARE IS PROVIDED BY THE JA-SIG COLLABORATIVE "AS IS" AND ANY
 * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE JA-SIG COLLABORATIVE OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
 * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 */

package org.jasig.portal;

import org.jasig.portal.security.IPerson;
import org.jasig.portal.ChannelStatistics;
import org.jasig.portal.ChannelUser;
import org.jasig.portal.RDBMStatisticsStore;
import org.jasig.portal.services.EntityCachingService;
import java.io.BufferedReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Enumeration;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


import org.jasig.portal.RDBMServices;

/**
 * <p>A class that saves and retrieves usage statistics to/from the portal database.</p>
 *
 * @author rtwigg@uccs.edu
 */
public class RDBMChannelStatisticsStore implements IChannelStatisticsStore{

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

  /**
   * Get channel statistics.
   * @param channelPublishId a channel publish ID
   * @return channelStatistics, statistics for the channel or <code>null</code>
   *   if no matching channel statistics are found
   * @throws java.sql.SQLException
   */

  public ChannelStatistics getChannelStatistics(int channelPublishId) throws SQLException {
    ChannelStatistics channelStats = null;
    
    // Check the cache
    try {
      channelStats = (ChannelStatistics)EntityCachingService.instance().get(ChannelStatistics.class, String.valueOf(channelPublishId));
    } catch (Exception e) {
      log.error("Error checking cache for channel statistics with publish id " 
              + channelPublishId, e);
    }
    
    // If not found in cache, get it from the store and cache it, otherwise return it
    if (channelStats == null) {

      Connection conn = null;
      RDBMServices.PreparedStatement pstmtChannelStats = null;
      RDBMServices.PreparedStatement pstmtChannelName = null;
      ResultSet rs = null;
      ResultSet rst = null;
  
      try {
        conn = RDBMServices.getConnection();
        pstmtChannelStats = getChannelStatsPstmt(conn);
        pstmtChannelName = getChannelNamePstmt(conn);
        pstmtChannelStats.clearParameters();
        pstmtChannelStats.setInt(1, channelPublishId);
        log.debug("RDBMChannelStatisticsStore.getChannelStatistics(): " + pstmtChannelStats);
        rs = pstmtChannelStats.executeQuery();
        if (rs.next()) {
          do {
            if (channelStats==null) {
              channelStats = new ChannelStatistics(channelPublishId);
            }
            String userName = rs.getString(1);
            ChannelUser channelUser = new ChannelUser(channelPublishId, userName);
            channelUser.setDateAdded(rs.getDate(2));
            channelUser.setDateInstantiated(rs.getDate(3));
            int totInst = rs.getInt(4);
            channelUser.setDateTargeted(rs.getDate(5));
            int totTarg = rs.getInt(6);
            channelUser.setTotInstantiated(totInst);
            channelUser.setTotTargeted(totTarg);
            channelStats.addUser(channelUser);
          } while (rs.next());

          int dbOffset = 0;
          rs.close();
          pstmtChannelName.clearParameters();
          pstmtChannelName.setInt(1, channelPublishId);
          log.debug("RDBMChannelStatisticsStore.getChannelStatistics(): " + pstmtChannelStats);
          rs = pstmtChannelName.executeQuery();

          if (rs.next()) {
            String name = rs.getString(dbOffset + 1);
            if (name != null) {
              channelStats.setName(name);
            } 
          }
          log.debug("RDBMChannelStatisticsStore.getChannelStatistics(): Read channel " + channelPublishId + " from the store");

          // Add the channel definition to the cache
          try {
            EntityCachingService.instance().add(channelStats);
          } catch (Exception e) {
            log.error("Error caching channel statistics " + channelStats, e);
          } 
        } // end if (rs.next())
      } finally {
          try { rs.close(); } catch (Exception e) {}
          try { pstmtChannelStats.close(); } catch (Exception e) {}
          try { pstmtChannelName.close(); } catch (Exception e) {}
          try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
      } // end try/finally
    } // end if channelStats==null
    return channelStats;
  }


  public void putChannelStatistics(IPerson person, Hashtable channelSet) throws SQLException {

    RDBMServices.PreparedStatement pstmtChannelUser = null;
    RDBMServices.PreparedStatement pstmtChannelUserInsert = null;
    RDBMServices.PreparedStatement pstmtChannelUserUpdate = null;
    ResultSet rs = null;
    String sqlUserName;
    java.sql.Date sqlDateAdded;
    java.sql.Date sqlDateInstantiated;
    java.sql.Date sqlDateTargeted;
    int sqlTotInstantiated=0; 
    int sqlTotTargeted=0;

    RDBMServices rdbm = new RDBMServices();
    Connection conn = RDBMServices.getConnection();
    pstmtChannelUser = getChannelUserPstmt(conn);

    try 
    {
       // Set autocommit false for the connection
       RDBMServices.setAutoCommit(conn, false);

       Enumeration en = channelSet.keys();
       while (en.hasMoreElements())
       {
          String channelId = (String)en.nextElement();
          int channelIdInt = Integer.parseInt(channelId);
          ChannelUser cUser = (ChannelUser)channelSet.get(channelId);
          String userName = cUser.getUserName();
          try 
          {
             pstmtChannelUser.clearParameters();
             pstmtChannelUser.setInt(1, Integer.parseInt(channelId));
             sqlUserName = RDBMServices.sqlEscape(userName);
             pstmtChannelUser.setString(2, sqlUserName);
             rs = pstmtChannelUser.executeQuery();  
             log.info("RDBMChannelStatisticsStore.putChannelStatistics(): " + pstmtChannelUser);
             // If channel stats is already there, do an update, otherwise do an insert
             if (rs.next()) 
             {
               java.sql.Date oldDateAdded = rs.getDate(3);
               java.sql.Date oldDateInstantiated = rs.getDate(4);
               int oldTotInstantiated = rs.getInt(5);
               java.sql.Date oldDateTargeted = rs.getDate(6);
               int oldTotTargeted = rs.getInt(7);

               if (oldDateAdded.after(cUser.getDateAdded())) 
                 sqlDateAdded = oldDateAdded;
               else
                 sqlDateAdded = cUser.getDateAdded();
               if (oldDateInstantiated.after(cUser.getDateInstantiated()))
                 sqlDateInstantiated = oldDateInstantiated;
               else
                 sqlDateInstantiated = cUser.getDateInstantiated(); 
               if (oldDateTargeted.after(cUser.getDateTargeted())) 
                  sqlDateTargeted = oldDateTargeted;
               else
                  sqlDateTargeted = cUser.getDateTargeted();

               sqlTotInstantiated = oldTotInstantiated + cUser.getTotInstantiated();
               sqlTotTargeted = oldTotTargeted + cUser.getTotTargeted();

               pstmtChannelUserUpdate = getChannelUserUpdatePstmt(conn);
               pstmtChannelUserUpdate.clearParameters();
               pstmtChannelUserUpdate.setDate(1, sqlDateAdded);
               pstmtChannelUserUpdate.setDate(2, sqlDateInstantiated);
               pstmtChannelUserUpdate.setInt(3, sqlTotInstantiated);
               pstmtChannelUserUpdate.setDate(4, sqlDateTargeted);
               pstmtChannelUserUpdate.setInt(5, sqlTotTargeted);
               pstmtChannelUserUpdate.setInt(6, channelIdInt);
               pstmtChannelUserUpdate.setString(7, sqlUserName);
               pstmtChannelUserUpdate.executeUpdate();
             } 
             else 
             {
               pstmtChannelUserInsert = getChannelUserInsertPstmt(conn);
               pstmtChannelUserInsert.clearParameters();

               pstmtChannelUserInsert.setInt(1, Integer.parseInt(channelId));
               pstmtChannelUserInsert.setString(2, sqlUserName);
               pstmtChannelUserInsert.setDate(3, cUser.getDateAdded());
               pstmtChannelUserInsert.setDate(4, cUser.getDateInstantiated());
               pstmtChannelUserInsert.setInt(5, sqlTotInstantiated);
               pstmtChannelUserInsert.setDate(6, cUser.getDateTargeted());
               pstmtChannelUserInsert.setInt(7, sqlTotTargeted);

               log.info("RDBMChannelStatisticsStore.putChannelStatistics(): " + pstmtChannelUserInsert);
               pstmtChannelUserInsert.executeUpdate();

             }
            // Commit the transaction
            RDBMServices.commit(conn);
          } // end try    
          catch (SQLException sqle) 
          {
             log.error("Exception saving channel statistics for user" + userName, sqle);
             RDBMServices.rollback(conn);
             throw sqle;
          } 
       } // end while
     } finally {
          try { rs.close(); } catch (Exception e) {}
          try { pstmtChannelUser.close(); } catch (Exception e) {}
          try { pstmtChannelUserUpdate.close(); } catch (Exception e) {}
          try { pstmtChannelUserInsert.close(); } catch (Exception e) {}
          try { RDBMServices.releaseConnection(conn); } catch (Exception e) {}
     } // end try/finally
   }

  protected static final RDBMServices.PreparedStatement getChannelStatsPstmt(Connection conn) throws SQLException {

    String sql = "SELECT USER_NAME, CHAN_ADDED, CHAN_LAST_INSTANTIATED, CHAN_TOTAL_INSTANTIATED, " +
                 "CHAN_LAST_TARGETED, CHAN_TOTAL_TARGETED FROM UP_CHANNEL_USER WHERE CHAN_ID=?";

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getChannelNamePstmt(Connection conn) throws SQLException {
      return new RDBMServices.PreparedStatement(conn, "SELECT CHAN_NAME FROM UP_CHANNEL WHERE CHAN_ID=?");
  }

  protected static final RDBMServices.PreparedStatement getChannelUserPstmt(Connection conn) throws SQLException {

    String sql = "SELECT * FROM UP_CHANNEL_USER WHERE CHAN_ID=? AND USER_NAME=?";    

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getChannelUserUpdatePstmt(Connection conn) throws SQLException {

    String sql = "UPDATE UP_CHANNEL_USER SET CHAN_ADDED = ?, CHAN_LAST_INSTANTIATED = ?, " + 
                 "CHAN_TOTAL_INSTANTIATED = ?, CHAN_LAST_TARGETED = ?, CHAN_TOTAL_TARGETED = ? " +
                 "WHERE CHAN_ID = ? AND USER_NAME = ?"; 

    return new RDBMServices.PreparedStatement(conn, sql);
  }

  protected static final RDBMServices.PreparedStatement getChannelUserInsertPstmt(Connection conn) throws SQLException {

    String sql = "INSERT INTO UP_CHANNEL_USER (CHAN_ID, USER_NAME, CHAN_ADDED, " +
    "CHAN_LAST_INSTANTIATED, CHAN_TOTAL_INSTANTIATED, CHAN_LAST_TARGETED, " +
    "CHAN_TOTAL_TARGETED) VALUES (?, ?, ?, ?, ?, ?, ?)";    

    return new RDBMServices.PreparedStatement(conn, sql);
  }

}