/*
 * $Id: VKCUserProjectHistorySerializer.java,v 1.1 2006/07/05 20:54:29 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCUserProjectHistorySerializer.java,v $
 * Revision 1.1  2006/07/05 20:54:29  marbobo
 * + Initiale Version
 *
 */
package vkc.db.serializer;

import java.sql.Connection;

import vkc.db.entities.VKCUserProjectHistory;
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 marbobo
 */
public class VKCUserProjectHistorySerializer {
	public VKCUserProjectHistorySerializer() {
    }

    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, projectId, lastAccessed FROM userProjectHistories WHERE userid = ? ORDER BY lastAccessed DESC");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();

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

                VKCUserProjectHistory uph = new VKCUserProjectHistory();
				
                uph.setUserId(new Integer(rs.getInt("userId")));
                uph.setProjectId(new Integer(rs.getInt("projectId")));
                uph.setLastAccessed(rs.getTimestamp("lastAccessed"));
				
				historyList.add(uph);
			}
		} catch (Exception e) {
			throw new DBException(e);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(pStmt);
		}

		return historyList;
	}

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

		Connection conn = null;

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

	private final static ArrayList retrieveHistoryProjectIds(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 projectId FROM userProjectHistories WHERE userid = ? ORDER BY lastAccessed DESC");
			pStmt.setInt(1, userId.intValue());
			rs = pStmt.executeQuery();

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

		return historyList;
	}

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

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

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

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

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

			// insert new entry
			sqlStatement = "INSERT INTO userProjectHistories ( userId, projectId, lastAccessed ) VALUES ( ?, ?, ? )";
			DBHelper.close(pStmt);
			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, userId.intValue());
			pStmt.setInt(2, projectId.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 getCountProjects(Integer userId) throws DBException {
        Connection conn = null;
	
		try {
			conn = WebShare.database.getConnection();
            
            return getCountProjects(userId, conn);
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(conn);
		}
	}
	
	public final static int getCountProjects(Integer userId, Connection conn) throws DBException {
		PreparedStatement pStmt = null;
		ResultSet rs = null;

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

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

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

		Connection conn = null;

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