/*******************************************************************************
 * Copyright (c), 2001, 2002 N2 Broadband, Inc.  All Rights Reserved.
 *
 * This module contains unpublished, confidential, proprietary
 * material.  The use and dissemination of this material are
 * governed by a license.  The above copyright notice does not
 * evidence any actual or intended publication of this material.
 *
 * Author:  Drake H. Henderson
 * Created:  11-12-01
 *
 ******************************************************************************/

package com.n2bb.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.n2bb.security.SecurityManager;
import com.n2bb.security.UserSecurityData;
import com.n2bb.util.DigestUtil;
import com.n2bb.util.N2bbException;
import com.tandbergtv.workflow.core.licensing.InvalidLicenseException;
import com.tandbergtv.workflow.core.licensing.LicenseManager;
import com.tandbergtv.workflow.resourcemanager.ResourceManagement;
import com.tandbergtv.workflow.resourcemanager.ResourceManager;
import com.tandbergtv.workflow.resourcemanager.entities.Resource;
import com.tandbergtv.workflow.resourcemanager.entities.ResourceState;


/**
 * Managers CRUD for users.
 *
 * @version $Id: UserManager.java,v 1.5 2007/06/22 21:44:50 vjakobac Exp $
 */
public class UserManager {
    /*
     Implementation note:
     This class manages CRUD for application-level users.
     It defers management of container-level users
     (i.e. users as seen by container-managed persistence)
     to SecurityManager.
     */

	private static final Logger n2bbLog = Logger
	.getLogger(UserManager.class);
	
	private static final String SEAT_LICENSE = "seatLicense";
	
    private static UserManager instance;

    /**
     * Constructor.
     */
    private UserManager() {
    }

    /** Gets the singleton instance of this class. */
    public static synchronized UserManager getInstance() {
        if (instance == null) {
            instance = new UserManager();
        }
        return instance;
    }

    /**
     * Gets the list of users.
     *
     * @return list of users
     */
    public Vector listUsers() throws N2bbException {
        n2bbLog.debug("enter");

        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        Vector usersVector = new Vector();

        try {
            con = getDataSource().getConnection();
            if (con == null) {
                throw new N2bbException("error.userManager.databaseNoConnection");
            }

            stmt = con.createStatement();
            String query = "Select * from user_data";
            rs = stmt.executeQuery(query);

            while (rs.next()) {
                UserBean bean = new UserBean();
                bean.setUserName(rs.getString(1));
                bean.setFirstName(rs.getString(2));
                bean.setLastName(rs.getString(3));
                bean.setEmail(rs.getString(4));
                bean.setPhone(rs.getString(5));

                try {
                    UserSecurityData securityData =
                            SecurityManager.getInstance().getUserSecurityData(bean.getUserName());
                    bean.setStatus(securityData.getStatus());
                    bean.setRoleName(securityData.getRole());
                }
                catch (N2bbException e) {
                    n2bbLog.error("failed to retrieve user framework data... " + bean.getUserName(), e);
                    bean.setStatus("undetermined");
                    bean.setRoleName("undetermined");
                }
                usersVector.add(bean);
            }
            n2bbLog.debug("users found... " + usersVector.size());
        }
        catch (N2bbException e) {
            throw e;
        }
        catch (SQLException e) {
            n2bbLog.error("SQLException - message... " + e.getMessage(), e);
            throw new N2bbException("error.userManager.databaseAccessError");
        }
        catch (Exception e) {
            n2bbLog.error(e.getMessage(), e);
            throw new N2bbException("error.other");
        }
        finally {
            closeStatement(stmt);
            closeConnection(con);
        }

        return usersVector;
    }

    /**
     * Gets UI data source.
     * @return UI data source.
     */
    private DataSource getDataSource() {
        return com.n2bb.util.PropertyManager.getUIDataSource();
    }

    /**
     * Creates a user.
     *
     * @param bean  user info
     */
    public void saveUser(UserBean bean) throws N2bbException {
        n2bbLog.debug("enter user... " + bean.getUserName());

        if (!seatLicenseAvailable())
        	throw new N2bbException("error.userManager.noAvailableLicense");
        
        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            // check framework for duplicate user name
            if (SecurityManager.getInstance().userExists(bean.getUserName())) {
                n2bbLog.warn("user already exists... " + bean.getUserName());
                throw new N2bbException("error.userManager.duplicateUserName");
            }

            con = getDataSource().getConnection();
            if (con == null) {
                throw new N2bbException("error.userManager.databaseNoConnection");
            }

            try {
                con.setAutoCommit(false);

                n2bbLog.debug("getPassword... " + bean.getPassword());
                // create user
                SecurityManager.getInstance().createUser(bean.getUserName(), bean.getPassword(),
                        bean.getStatus(), bean.getRoleName(), con);

                // insert user data into app specific table
                pstmt = con.prepareStatement("insert into USER_DATA (USER_NAME,FIRSTNAME,LASTNAME,EMAIL,PHONE,EMPLOYEEID,DEPARTMENT,LOCATION,EXTENSION) values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                pstmt.setString(1, bean.getUserName());
                pstmt.setString(2, bean.getFirstName());
                pstmt.setString(3, bean.getLastName());
                pstmt.setString(4, bean.getEmail());
                pstmt.setString(5, bean.getPhone());
                pstmt.setString(6,bean.getEmployeeId());
                pstmt.setString(7,bean.getDepartment());
                pstmt.setString(8,bean.getLocation());
                pstmt.setString(9,bean.getExtn());
                pstmt.executeUpdate();
                pstmt.close();

                con.commit();
                con.setAutoCommit(true);

            }
            catch (N2bbException e) {
                con.rollback();
                con.setAutoCommit(true);
                throw e;
            }
            catch (Exception e) {
                n2bbLog.error(e.getMessage(), e);
                con.rollback();
                con.setAutoCommit(true);
                throw new N2bbException("error.other");
            }

        }
        catch (N2bbException e) {
            throw e;
        }
        catch (SQLException e) {
            n2bbLog.error("SQLException - message... " + e.getMessage(), e);
            throw new N2bbException("error.userManager.databaseAccessError");
        }
        catch (Exception e) {
            n2bbLog.error(e.getMessage(), e);
            throw new N2bbException("error.other");
        }
        finally {
            closeStatement(pstmt);
            closeConnection(con);
        }
    }

	/**
	 * @throws N2bbException
	 */
	private boolean seatLicenseAvailable() throws N2bbException {
		int numOfActiveUsers = this.getNumOfActiveUsers();
        int numOfSeatLicenses = 0;
        try {
			numOfSeatLicenses = this.getNumOfSeatLicenses();
		} catch (InvalidLicenseException e1) {
			throw new N2bbException("error.userManager.cannotReadLicense");
		}
		if (numOfActiveUsers >= numOfSeatLicenses){
			return false;
		}
		return true;
	}    

	/**
     * Deletes a user.
     *
     * @param name  username
     */
    public void deleteUser(String name) throws N2bbException {
        n2bbLog.debug("enter user... " + name);

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            con = getDataSource().getConnection();
            if (con == null) {
                throw new N2bbException("error.userManager.databaseNoConnection");
            }

            try {
                con.setAutoCommit(false);

                // delete user data from app specific table
                pstmt = con.prepareStatement("delete from user_data where user_name = ?");
                pstmt.setString(1, name);
                pstmt.executeUpdate();
                pstmt.close();

                // delete user
                SecurityManager.getInstance().deleteUser(name, con);

                con.commit();
                con.setAutoCommit(true);
            }
            catch (N2bbException e) {
                con.rollback();
                con.setAutoCommit(true);
                throw e;
            }
            catch (Exception e) {
                n2bbLog.error(e.getMessage(), e);
                con.rollback();
                con.setAutoCommit(true);
                throw new N2bbException("error.other");
            }
        }
        catch (N2bbException e) {
            throw e;
        }
        catch (SQLException e) {
            n2bbLog.error("SQLException - message... " + e.getMessage(), e);
            throw new N2bbException("error.userManager.databaseAccessError");
        }
        catch (Exception e) {
            n2bbLog.error(e.getMessage(), e);
            throw new N2bbException("error.other");
        }
        finally {
            closeStatement(pstmt);
            closeConnection(con);
        }
    }

    /**
     * Gets a user.
     *
     * @param name username
     */
    public UserBean getUser(String name) throws N2bbException {
        n2bbLog.debug("enter user... " + name);

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        UserBean bean = new UserBean();

        try {
            con = getDataSource().getConnection();
            if (con == null) {
                throw new N2bbException("error.userManager.databaseNoConnection");
            }

            String query = "select USER_NAME,FIRSTNAME,LASTNAME,EMAIL,PHONE,EMPLOYEEID,DEPARTMENT,LOCATION,EXTENSION from user_data where user_name=?";
            pstmt = con.prepareStatement(query);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                bean.setUserName(rs.getString("USER_NAME"));
                bean.setFirstName(rs.getString("FIRSTNAME"));
                bean.setLastName(rs.getString("LASTNAME"));
                bean.setEmail(rs.getString("EMAIL"));
                bean.setPhone(rs.getString("PHONE"));
                bean.setEmployeeId(rs.getString("EMPLOYEEID"));
                bean.setDepartment(rs.getString("DEPARTMENT"));
                bean.setLocation(rs.getString("LOCATION"));
                bean.setExtn(rs.getString("EXTENSION"));

                UserSecurityData securityData =
                        SecurityManager.getInstance().getUserSecurityData(name);
                bean.setStatus(securityData.getStatus());
                bean.setRoleName(securityData.getRole());
            }
            else {
                n2bbLog.debug("user not found");
            }
            return bean;
        }
        catch (N2bbException e) {
            throw e;
        }
        catch (SQLException e) {
            n2bbLog.error("SQLException - message... " + e.getMessage(), e);
            throw new N2bbException("error.userManager.databaseAccessError");
        }
        catch (Exception e) {
            n2bbLog.error(e.getMessage(), e);
            throw new N2bbException("error.other");
        }
        finally {
            closeStatement(pstmt);
            closeConnection(con);
        }
    }

    /**
     * Updates a user.
     *
     * @param bean              user info
     * @param changePassword    true to update password
     */
    public void updateUser(UserBean bean, boolean changePassword) throws N2bbException {
        n2bbLog.debug("enter user... " + bean.getUserName());
        n2bbLog.debug("change password... " + changePassword);

        Connection con = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
        
        try {
            con = getDataSource().getConnection();
            if (con == null) {
                throw new N2bbException("error.userManager.databaseNoConnection");
            }
            
            // check if the user is inactive
            pstmt = con.prepareStatement("select status from users_realm where USER_NAME=?");
            pstmt.setString(1, bean.getUserName());
            rs = pstmt.executeQuery();
			if (rs.next()) {
				int status = rs.getInt(1);
				if (status == 0 && 
						(bean.getStatus().equalsIgnoreCase("Active") || bean.getStatus().equalsIgnoreCase("A"))){
					//inactive user, changing status to Active, so check if there's available seat license
					if (!seatLicenseAvailable()) {
						throw new N2bbException("error.userManager.noAvailableLicense");
					}
				} else if (status == 1 && bean.getStatus().equalsIgnoreCase("I")) {
					//active user, changing status to Inactive, so check if the corresponding resource is not online
					ResourceManagement resourceManagement = ResourceManager.getInstance();
	            	Resource resource = resourceManagement.getResourceByUser(bean.getUserName());
	            	if (resource != null && 
	            			(resource.getAdministrationState() != ResourceState.OFFLINE ||
	            					(resource.getOperationalState() != ResourceState.INVALID &&
	            					resource.getOperationalState() != ResourceState.INACTIVE &&
	            					resource.getOperationalState() != ResourceState.OFFLINE))){
	            		throw new N2bbException("error.userManager.unChangedUser");
	            	}
				}
			}
          
			try {
                con.setAutoCommit(false);

                int iStatus = 0;
                if (bean.getStatus().equalsIgnoreCase("Active") || bean.getStatus().equalsIgnoreCase("A")) {
                    iStatus = 1;
                }
                if (changePassword) {
                	pstmt = con.prepareStatement("update USERS_REALM set USER_PASS=?,PASSWORD_MODIFIED_DATE=sysdate,STATUS=?,UPDATEDATE=sysdate where USER_NAME=?");
                	pstmt.setString(1, DigestUtil.Digest(bean.getPassword(), "SHA"));
                   	pstmt.setInt(2,iStatus);
                	pstmt.setString(3,bean.getUserName());
                } else {
                	pstmt = con.prepareStatement("UPDATE USERS_REALM SET STATUS=?,UPDATEDATE=SYSDATE WHERE USER_NAME=?");
                   	pstmt.setInt(1,iStatus);
                	pstmt.setString(2,bean.getUserName());
                }
                pstmt.executeUpdate();
                pstmt.close();
                
                // Update the USER_BIGROLE Table
                pstmt = con.prepareStatement("UPDATE USER_BIGROLE SET BIGROLE=?,UPDATEDATE=SYSDATE WHERE USER_NAME=?");
               	pstmt.setString(1,bean.getRoleName());
            	pstmt.setString(2,bean.getUserName());
                pstmt.executeUpdate();
                pstmt.close();
                
                // Update the USER_DATA table
                pstmt = con.prepareStatement("UPDATE USER_DATA SET FIRSTNAME=?,LASTNAME=?,EMAIL=?,PHONE=?,UPDATEDATE=SYSDATE,EMPLOYEEID=?,DEPARTMENT=?,LOCATION=?,EXTENSION=? WHERE USER_NAME=?");
               	pstmt.setString(1,bean.getFirstName());
            	pstmt.setString(2,bean.getLastName());
            	pstmt.setString(3,bean.getEmail());
            	pstmt.setString(4,bean.getPhone());
            	pstmt.setString(5,bean.getEmployeeId());
            	pstmt.setString(6,bean.getDepartment());
            	pstmt.setString(7,bean.getLocation());
            	pstmt.setString(8,bean.getExtn());
            	pstmt.setString(9,bean.getUserName());
                pstmt.executeUpdate();
                pstmt.close();

                con.commit();
                con.setAutoCommit(true);
            }
            catch (Exception e) {
                n2bbLog.error(e.getMessage(), e);
                con.rollback();
                con.setAutoCommit(true);
                if (e instanceof N2bbException) {
                    n2bbLog.error("n2bb exception - message... " + e.getMessage(), e);
                    throw (N2bbException) e;
                }
                else if (e instanceof SQLException) {
                    n2bbLog.error("SQLException - message... " + e.getMessage(), e);
                    throw new N2bbException("error.userManager.databaseAccessError");
                }
            }

        }
        catch (N2bbException e) {
            throw e;
        }
        catch (SQLException e) {
            n2bbLog.error("SQLException - message... " + e.getMessage(), e);
            throw new N2bbException("error.userManager.databaseAccessError");
        }
        catch (Exception e) {
            n2bbLog.error(e.getMessage(), e);
            throw new N2bbException("error.other");
        }
        finally {
            closeStatement(pstmt);
            closeConnection(con);
        }
    }

    /**
     * Closes a statement.  Note that this automatically closes
     * the statement's current result set, if one exists.
     *
     * @param stmt  statement
     */
    private void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            }
            catch (SQLException e) {
            }
        }
    }

    private void closeConnection(Connection con) {
        if (con != null) {
            try {
                con.close();
            }
            catch (SQLException e) {
            }
        }
    }

    int getNumOfActiveUsers() throws N2bbException {
		int result = 0;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			con = getDataSource().getConnection();
			if (con == null) {
				throw new N2bbException(
						"error.userManager.databaseNoConnection");
			}

			stmt = con.createStatement();
			String query = "select count(*) from USERS_REALM where STATUS=1";
			rs = stmt.executeQuery(query);

			if (rs.next()) {
				result = rs.getInt(1);
				n2bbLog.debug("number of active users = "+result);
			} else {
				n2bbLog.debug("users not found");
				result = 0;
			}
			return result;
		} catch (N2bbException e) {
			throw e;
		} catch (SQLException e) {
			n2bbLog.error("SQLException - message... " + e.getMessage(), e);
			throw new N2bbException("error.userManager.databaseAccessError");
		} catch (Exception e) {
			n2bbLog.error(e.getMessage(), e);
			throw new N2bbException("error.other");
		} finally {
			closeStatement(stmt);
			closeConnection(con);
		}
	}

    /**
	 * Method used to obtain license information
	 * 
	 * @return the number of available seat licenses
	 * @throws InvalidLicenseException
	 */
	private int getNumOfSeatLicenses() throws InvalidLicenseException {
		int result = -1;
		LicenseManager licManager = LicenseManager.getInstance();
		String seatLicense = licManager.getObject(SEAT_LICENSE);
		n2bbLog.debug("String value for seatLicense: "+seatLicense);						
		if (seatLicense == null || seatLicense.trim().length() == 0)
			return Integer.MAX_VALUE;
		else {
			result = Integer.parseInt(seatLicense.trim());
		}
		
		if (result < 0)
			throw new InvalidLicenseException("Invalid License for "+SEAT_LICENSE+" - non-negative value must be specified");
		
		return result;		
	}

	/**
	 * A method that checks the seat license info and in case there are no available licenses 
	 * for all active users, it makes inactive most recently added users 
	 * @throws InvalidLicenseException 
	 * @throws N2bbException 
	 *
	 */
	public void init() throws InvalidLicenseException, N2bbException {
		int numOfSeatLicenses = this.getNumOfSeatLicenses();
		int numOfActiveUsers = this.getNumOfActiveUsers();
		n2bbLog.debug("numOfSeatLicenses="+numOfSeatLicenses);
		n2bbLog.debug("numOfActiveUsers="+numOfActiveUsers);
		
		if (numOfActiveUsers <= numOfSeatLicenses)
			return;
		
		makeSomeActiveUsersInactive(numOfSeatLicenses);
	}
	/**
	 * A method that deactivate users in case there are no available seat licenses
	 * for all active users
	 * 
	 * @param numOfSeatLicenses
	 * @throws N2bbException
	 */
	private void makeSomeActiveUsersInactive(int numOfSeatLicenses) throws N2bbException {

		Connection con = null;
		Statement stmt = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		List<String> inactiveUserNames = new ArrayList<String>();

		try {
			con = getDataSource().getConnection();
			if (con == null) {
				throw new N2bbException(
						"error.userManager.databaseNoConnection");
			}
			stmt = con.createStatement();
			String query = "Select user_name from users_realm where STATUS=1 order by CREATEDATE";
			rs = stmt.executeQuery(query);

			int activeUserCount = 0;
			while (rs.next()) {
				String userName = rs.getString(1);
				if (activeUserCount >= numOfSeatLicenses){
					inactiveUserNames.add(userName);
					n2bbLog.debug("added to inactiveList: "+userName);
				} 
				activeUserCount++;				
			}
			
			con.setAutoCommit(false);
			
			int iStatus = 0;
			for (String userName : inactiveUserNames){
				prepStmt = con.prepareStatement("UPDATE USERS_REALM SET STATUS=?,UPDATEDATE=SYSDATE WHERE USER_NAME=?");
				prepStmt.setInt(1,iStatus);
				prepStmt.setString(2,userName);
	        
				prepStmt.executeUpdate();
				prepStmt.close();
			}			
			
			con.commit();
            con.setAutoCommit(true);
			
		} catch (N2bbException e) {
			throw e;
		} catch (SQLException e) {
			n2bbLog.error("SQLException - message... " + e.getMessage(), e);
			throw new N2bbException("error.userManager.databaseAccessError");
		} catch (Exception e) {
			n2bbLog.error(e.getMessage(), e);
			throw new N2bbException("error.other");
		} finally {
			closeStatement(stmt);
			closeConnection(con);
		}
	}

	public void toggleUserStatus(String userName) throws N2bbException {
		n2bbLog.debug("enter user... " + userName);
        
        Connection con = null;
        PreparedStatement pstmt = null;
		ResultSet rs = null;
        
        try {
            con = getDataSource().getConnection();
            if (con == null) {
                throw new N2bbException("error.userManager.databaseNoConnection");
            }
            
            // check if the user is inactive
            pstmt = con.prepareStatement("select status from users_realm where USER_NAME=?");
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
			if (rs.next()) {
				int status = rs.getInt(1);
				if (status == 0){
					//inactive user, changing status to Active, so check if there's available seat license
					if (!seatLicenseAvailable()) {
						n2bbLog.info("throwing n2bbexception - no avail license");
						throw new N2bbException("error.userManager.noAvailableLicense");
					} 
				}				
			} 
			//if all checks passed, toggle the status
	        SecurityManager.getInstance().toggleUserStatus(userName);
		
        }
        catch (N2bbException e) {
            throw e;
        }
        catch (SQLException e) {
            n2bbLog.error("SQLException - message... " + e.getMessage(), e);
            throw new N2bbException("error.userManager.databaseAccessError");
        }
        catch (Exception e) {
            n2bbLog.error(e.getMessage(), e);
            throw new N2bbException("error.other");
        }
        finally {
            closeStatement(pstmt);
            closeConnection(con);
        }
        
		
	}

	/**
	 * @return Returns the list of users who are inactive
	 * due to licensing limit.
	 * @throws N2bbException 
	 */
	public List<String> getInactiveUserNames() throws N2bbException {
		
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<String> inactiveUserNames = new ArrayList<String>();

		try {
			con = getDataSource().getConnection();
			if (con == null) {
				throw new N2bbException(
						"error.userManager.databaseNoConnection");
			}
			stmt = con.createStatement();
			String query = "Select user_name from users_realm where STATUS=0";
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				String userName = rs.getString(1);
				inactiveUserNames.add(userName);
			}
		} catch (N2bbException e) {
			throw e;
		} catch (SQLException e) {
			n2bbLog.error("SQLException - message... " + e.getMessage(), e);
			throw new N2bbException("error.userManager.databaseAccessError");
		} catch (Exception e) {
			n2bbLog.error(e.getMessage(), e);
			throw new N2bbException("error.other");
		} finally {
			closeStatement(stmt);
			closeConnection(con);
		}
		return inactiveUserNames;
	}
    
}