/*
 * $Id: VKCUserDocHistorySerializer.java,v 1.13 2006/03/27 22:08:15 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCUserDocHistorySerializer.java,v $
 * Revision 1.13  2006/03/27 22:08:15  marbobo
 * + Bugfixes und Refactoring
 *
 * Revision 1.12  2006/03/23 11:39:18  marbobo
 * + Mögliche NPE abgefangen
 *
 * Revision 1.11  2004/12/09 21:07:40  marbobo
 * + kleine Optimierungen und Bugfixes
 * +an einigen Stellen Rreihenfolge der close-Anweisungen vereinheitlicht
 *
 * Revision 1.10  2004/12/09 12:39:28  lawern
 * vergessene PreparedStatements geschlossen
 *
 * Revision 1.9  2004/12/05 22:57:47  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.8  2004/11/24 13:01:13  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.7  2004/11/24 09:25:55  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.6  2004/11/18 16:05:24  lawern
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.5  2004/10/21 23:49:17  marbobo
 * + CURRENT_TIMESTAMP ersetzt mit java-Anweisung
 *
 * Revision 1.4  2004/05/11 15:52:36  jonyp
 * fixed bug: sql exception due to wrong sql statement during deletion of favorite
 *
 * Revision 1.3  2004/05/07 10:15:55  jonyp
 * minor bug fixes
 *
 * Revision 1.2  2004/05/06 15:09:37  jonyp
 * small bug fix
 *
 * Revision 1.1  2004/05/05 13:06:43  jonyp
 * first strike
 *
 *
 * 
 */
package vkc.db.serializer;

import java.sql.Connection;

import vkc.db.entities.VKCUserDocHistory;
import vkc.db.serializer.VKCSysParameterSerializer;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;

import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.view.WebShare;

/** 
 *
 * @author jonyP
 */
public class VKCUserDocHistorySerializer {
	public VKCUserDocHistorySerializer() {
    }

    public final static ArrayList retrieveHistoryInfos(Integer userId, int maxCount) throws DBException {
		if (userId == null) {
			return null;
		}

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveHistoryInfos(userId, maxCount, conn);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	public final static ArrayList retrieveHistoryInfos(Integer userId, int maxCount, Connection conn) throws DBException {
		if (userId == null || userId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		ArrayList historyList = new ArrayList();
		int currentCount = 0; 

		try {
			pStmt = conn.prepareStatement("SELECT userId, documentId, lastAccessed FROM userDocHistories WHERE userid = ? ORDER BY lastAccessed DESC");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();

			while (rs.next()) {
                if (currentCount++ >= maxCount) {
                	break;
                }

                VKCUserDocHistory udh = new VKCUserDocHistory();
				
				udh.setUserId(new Integer(rs.getInt("userId")));
				udh.setDocumentId(new Integer(rs.getInt("documentId")));
				udh.setLastAccessed(rs.getTimestamp("lastAccessed"));
	            udh.setDocument(VKCDocumentSerializer.retrieveDocument(udh.getDocumentId(), conn));
				
				historyList.add(udh);
			}
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}

		return historyList;
	}

	public final static ArrayList retrieveHistoryDocIds(Integer userId) throws DBException {
		if (userId == null) {
			return null;
		}

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return retrieveHistoryDocIds(userId, conn);
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	private final static ArrayList retrieveHistoryDocIds(Integer userId, Connection conn) throws DBException {
		if (userId == null || userId.intValue() <= 0) {
			return null;
		}

		PreparedStatement pStmt = null;
		ResultSet rs = null;
		ArrayList historyList = new ArrayList();

		try {
			pStmt = conn.prepareStatement("SELECT documentId FROM userDocHistories WHERE userid = ? ORDER BY lastAccessed DESC");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				int docId = rs.getInt("documentId");
				historyList.add(new Integer(docId));
			}
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}

		return historyList;
	}

	public final static void saveDocumentHistory(Integer userId, Integer documentId) throws DBException {
		if (userId == null || documentId == null) {
			return;
		}
		
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveDocumentHistory(userId, documentId, conn);
			conn.commit();
		} catch (Exception ex) {
			DBHelper.rollBack(conn);
			throw new DBException(ex);
		} finally {
    		DBHelper.close(conn);
		}
	}

	public final static void saveDocumentHistory(Integer userId, Integer documentId, Connection conn) throws DBException {
		if (userId == null || documentId == null) {
			return;
		}

		PreparedStatement pStmt = null;
	    
	    try {
			String sqlStatement = null;
			int historyCount = VKCSysParameterSerializer.retrieveIntValue("DOCUMENT", "MaxHistoryCount", 10);
			
			// delete the oldest entry, if count greater equal than "historyCount"
			if (VKCUserDocHistorySerializer.getCountDocuments(userId, conn) >= historyCount) {
				sqlStatement = "DELETE FROM userDocHistories " +
				               " WHERE lastAccessed <= (SELECT min(lastAccessed) " +
					           "                         FROM userDocHistories  " +
					           "                        WHERE userId = ?)";
				pStmt = conn.prepareStatement(sqlStatement);
				pStmt.setInt(1, userId.intValue());
				pStmt.execute();
            }

            // delete equivalent entry in order to insert lastAccessed
            sqlStatement = "DELETE FROM userDocHistories " +
                           " WHERE userId = ?" +
                           "   AND documentId = ?";
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, userId.intValue());
			pStmt.setInt(2, documentId.intValue());
			pStmt.execute();

			// insert new entry
			sqlStatement = "INSERT INTO userDocHistories ( userId, documentId, lastAccessed ) VALUES ( ?, ?, ? )";
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, userId.intValue());
			pStmt.setInt(2, documentId.intValue());
			pStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
			pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
	}
	
	public final static int getCountDocuments(Integer userId) throws DBException {
        Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
            
            return getCountDocuments(userId, conn);
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	public final static int getCountDocuments(Integer userId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		try {
			pStmt = conn.prepareStatement("SELECT count(*) AS countDocs FROM userDocHistories WHERE userId = ?");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();			

			if (rs.next()) {
				return rs.getInt("countDocs");
			} 

			// should never happen
			return 0;            
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}
	}
	
	public final static void deleteDocumentHistory(Integer userId, Integer documentId) throws DBException {
		if (userId == null && documentId == null) {
			return;
		}

		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			deleteDocumentHistory(userId, documentId, conn);
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	public final static void deleteDocumentHistory(Integer userId, Integer documentId, Connection conn) throws DBException {
		if (userId == null && documentId == null) {
			return;	
		}
	
		PreparedStatement pStmt = null;
		
		try {
            if (userId != null && documentId == null) {
            	pStmt = conn.prepareStatement("DELETE FROM userDocHistories WHERE userId = ?");
            	pStmt.setInt(1, userId.intValue());
                pStmt.execute(); 
            } else if (userId == null && documentId != null) {
            	pStmt = conn.prepareStatement("DELETE FROM userDocHistories WHERE documentId = ?");
            	pStmt.setInt(1, documentId.intValue());
                pStmt.execute(); 
			} else if (userId != null && documentId != null) {
            	pStmt = conn.prepareStatement("DELETE FROM userDocHistories WHERE userId = ? AND documentId = ?");
            	pStmt.setInt(1, userId.intValue());
            	pStmt.setInt(2, documentId.intValue());
                pStmt.execute(); 
			}
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
		    DBHelper.close(pStmt);
		}
    }
}