/*
 * $Id: VKCAclSerializer.java,v 1.23 2006/03/27 22:08:11 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: VKCAclSerializer.java,v $
 * Revision 1.23  2006/03/27 22:08:11  marbobo
 * + Bugfixes und Refactoring
 *
 * Revision 1.22  2005/05/30 16:10:09  jonyp
 * added some utility function to retrieve vectors of aclentry-types
 *
 * Revision 1.21  2005/05/30 06:12:56  jonyp
 * *** empty log message ***
 *
 * Revision 1.20  2005/01/19 16:09:12  jonyp
 * create only a new timestamp, when none is given for each aclentry in method saveAcl!
 *
 * Revision 1.19  2004/12/17 01:18:33  marbobo
 * + Weitere Umstellungen und Optimierungen bzgl. PreparedStatements
 *
 * Revision 1.18  2004/12/01 21:44:14  marbobo
 * + Statement auf PreparedStatement umgestellt
 *
 * Revision 1.17  2004/11/24 13:01:10  lawern
 * Global "conn.setAutoCommit( false );" enfernt, weil überflüssig.
 *
 * Revision 1.16  2004/11/24 09:25:55  lawern
 * Hibernate ist VKC-Geschichte!
 *
 * Revision 1.15  2004/11/18 19:38:22  marbobo
 * + changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.14  2004/11/18 15:52:43  jonyp
 * changed order of close-stmt to resultset/connect/session  in finally-blocks
 *
 * Revision 1.13  2004/11/18 10:21:43  jonyp
 * + set/getCreated: changed from date to Timestamp
 * + use preparedStatement for savingAcl instead of DBHelper
 *
 * Revision 1.12  2004/11/17 16:57:45  jonyp
 * take prepared statement to save acl (preliminary!)
 *
 * Revision 1.11  2004/11/10 15:02:44  koppy
 * Projektweite Überarbeitung von Error-Logs, Stacktraces,..
 *
 * Revision 1.10  2004/10/21 23:49:16  marbobo
 * + CURRENT_TIMESTAMP ersetzt mit java-Anweisung
 *
 * Revision 1.9  2004/08/09 12:52:09  jonyp
 * defensive programming: create 2 statements for getDefaultCatAcls method
 *
 * Revision 1.8  2004/07/20 08:23:20  jonyp
 * added copyright info + cvs log
 *
 *
 */
package vkc.db.serializer;

import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.Vector;
import java.util.List;
import java.util.ArrayList;

import java.sql.Connection;
import vkc.db.entities.VKCAclEntry;
import vkc.db.entities.VKCAclType;
import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCPermission;
import vkc.db.entities.VKCUser;
import vkc.db.helper.DBHelper;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.security.VKCAcl;
import vkc.security.AccessorPermPair;
import vkc.view.WebShare;

public class VKCAclSerializer {
    private VKCAclSerializer() {
    }

    /**
     * Speichern einer ACL
     *
     * @param acl das zu speichernde VKCAcl Objekt
     */
    public static final void saveAcl(VKCAcl acl) throws DBException {
    	Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            saveAcl(acl, conn);
            conn.commit();
        } catch (Exception e) {
            DBHelper.rollBack(conn);
            VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error while saving ACL: \n" + acl, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final void saveAcl(VKCAcl acl, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        
        try {   
            if (acl.getAclId() == null) {
                acl.setAclId(DBHelper.getNewId(conn, "ACLIDS", "aclId"));
            } else {
                // delete all objects corresponding to aclId                
                deleteAcl(acl, conn);
            }
    
            String stmt = " INSERT INTO CategoryAcls ( aclEntryId, aclId, aclTypeId, accessorId, permissionId, created, creatorId ) " +
                          " VALUES ( ?, ?, ?, ?, ?, ?, ? )";
            pStmt = conn.prepareStatement(stmt);
           
            // save all new/updated aclentries
            for (Enumeration e = acl.getAclEntries().elements(); e.hasMoreElements();) {
				VKCAclEntry catAcl = (VKCAclEntry) e.nextElement();
                catAcl.setAclEntryId(DBHelper.getNewId(conn, "CategoryAcls", "aclEntryId"));
                
                if (catAcl.getCreated() == null) {
                    // create only a new timestamp, when none is given !!!
                    catAcl.setCreated(new Timestamp(System.currentTimeMillis()));    
                }

    			pStmt.setInt(1, catAcl.getAclEntryId().intValue());
    			pStmt.setInt(2, catAcl.getAclId().intValue());
    			pStmt.setInt(3, catAcl.getAclType().getAclTypeId().intValue());                
                pStmt.setInt(4, catAcl.getAccessorId().intValue());
                pStmt.setInt(5, catAcl.getPermission().getPermissionId().intValue());
                pStmt.setTimestamp(6, catAcl.getCreated());
                pStmt.setInt(7, catAcl.getCreatorId().intValue());

                pStmt.execute();
            }
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Löschen einer ACL
     *
     * @param acl das zu löschende VKCAcl Objekt
     */
    public static final void deleteAcl(VKCAcl acl) throws DBException {
        Connection conn = null;

        try {
        	conn = WebShare.database.getConnection();
            deleteAcl(acl, conn);
            acl.setAclId( null );
            conn.commit();
        } catch ( Exception e ) {
            DBHelper.rollBack( conn );
            VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error while saving ACL: \n" + acl, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final void deleteAcl(VKCAcl acl, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        
        try {
            pStmt = conn.prepareStatement("DELETE FROM CategoryAcls WHERE aclId = ?");
            pStmt.setInt(1, acl.getAclId().intValue());
            pStmt.execute();
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Laden einer ACL
     *
     * @param aclId Id der zu ladenden ACL
     *
     * @return VKCAcl: das gewünschte VCKAcl Object
     *
     * @throws DBException DOCUMENT ME!
     */
    public static final VKCAcl retrieveAcl(Integer aclId) throws DBException {
        if (aclId == null) {
            return null;
        }

        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveAcl(aclId, conn);
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error retrieving Acl ID=" + aclId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public final static VKCAcl retrieveAcl(Integer aclId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            String sqlStatement =
                "SELECT categoryacls.aclEntryId, categoryacls.aclId," +
                "       acltypes.aclTypeId, acltypes.name, acltypes.rank, acltypes.description," +
                "       categoryacls.accessorId," +
                "       permissions.permissionId, permissions.context, permissions.token, permissions.numvalue, permissions.printstring, permissions.description as perm_descr," +
                "       categoryacls.created, categoryacls.creatorId" +
                "  FROM categoryacls, permissions, acltypes" +
                " WHERE categoryacls.aclid = ?" +
                "   AND categoryacls.permissionId = permissions.permissionId" +
                "   AND categoryacls.aclTypeId = acltypes.aclTypeId";
            pStmt = conn.prepareStatement(sqlStatement);
            pStmt.setInt(1, aclId.intValue());

            rs = pStmt.executeQuery();

			VKCAcl acl = new VKCAcl();
			acl.setAclId(aclId);

            while (rs.next()) {
                // construct a new VKCCategoryAcl
                VKCAclEntry catAcl = new VKCAclEntry();
                VKCAclType aclType = new VKCAclType();
                VKCPermission perm = new VKCPermission();

                catAcl.setAclEntryId(new Integer(rs.getInt("aclEntryId")));
                catAcl.setAclId(new Integer(rs.getInt("aclId")));

                aclType.setAclTypeId(new Integer(rs.getInt("aclTypeId")));
                aclType.setName(rs.getString("name"));
                aclType.setRank(new Integer(rs.getInt("rank")));
                aclType.setDescription(rs.getString("description"));
                catAcl.setAclType(aclType);

                catAcl.setAccessorId(new Integer(rs.getInt("accessorId")));

                perm.setPermissionId(new Integer(rs.getInt("permissionId")));
                perm.setPrintstring(rs.getString("printstring"));
                perm.setNumvalue(new Integer(rs.getInt("numvalue")));
                perm.setToken((rs.getString("token")).charAt(0));
                perm.setContext(rs.getString("context"));
                perm.setDescription(rs.getString("perm_descr"));
                catAcl.setPermission(perm);

                catAcl.setCreated(rs.getTimestamp("created"));
                catAcl.setCreatorId(new Integer(rs.getInt("creatorId")));

                acl.addAclEntry(catAcl);
            }

            return acl;
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);           
        }
    }

    public static final List retrieveAccessorPermList(Integer aclId) throws DBException {
        if (aclId == null) {
            return null;
        }

        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveAccessorPermList(aclId, null, conn);
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error retrieving Acl ID=" + aclId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }

    public static final List retrieveAccessorPermList(Integer aclId, Integer aclTypeId) throws DBException {
        if (aclId == null) {
            return null;
        }

        Connection conn = null;

        try {
            conn = WebShare.database.getConnection();
            
            return retrieveAccessorPermList(aclId, aclTypeId, conn);
        } catch (Exception e) {
            VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error retrieving Acl ID=" + aclId, e);
            throw new DBException(e);
        } finally {
            DBHelper.close(conn);
        }
    }
    
    public final static List retrieveAccessorPermList(Integer aclId, Integer aclTypeId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        PreparedStatement pStmt2 = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        List accessorPermList = new ArrayList();

        try {
            String sqlStatement =
                "SELECT DISTINCT categoryAcls.accessorId, categoryAcls.aclTypeId" +
                "           FROM categoryAcls" +
                "          WHERE categoryAcls.aclid = ?";
                
            if (aclTypeId != null) {
                    sqlStatement += " AND aclTypeId = ?"; 
            }

			pStmt = conn.prepareStatement( sqlStatement );
            pStmt.setInt(1, aclId.intValue());

            if (aclTypeId != null) {
                pStmt.setInt(2, aclTypeId.intValue());
            }
            
            rs = pStmt.executeQuery();

            while (rs.next()) {
                AccessorPermPair app = new AccessorPermPair();
                app.setAclTypeId(new Integer(rs.getInt("aclTypeId")));
                app.setAccessorId(new Integer(rs.getInt("accessorId")));
            	accessorPermList.add(app);
            }

            for (Iterator iter = accessorPermList.iterator(); iter.hasNext();) {
            	AccessorPermPair app = (AccessorPermPair) iter.next();
            	
            	sqlStatement = "SELECT token FROM categoryAcls WHERE aclId = ? AND accessorId = ? AND aclTypeId = ? ";
            	
            	DBHelper.close(rs2);
                DBHelper.close(pStmt2);
            	pStmt2 = conn.prepareStatement(sqlStatement);
            	pStmt2.setInt(1, aclId.intValue());
            	pStmt2.setInt(2, app.getAccessorId().intValue());
            	pStmt2.setInt(3, app.getAclTypeId().intValue());
            	
            	rs2 = pStmt.executeQuery();
            	
            	String tokenString = "";

            	while (rs2.next()) {
            	    tokenString += rs.getString("token").charAt(0);	
            	}
            	
            	app.setPermTokenString(tokenString);
            	app.setPermTokenList(tokenString.toCharArray());
            }
            
            return accessorPermList;
        } catch (Exception ex) {
            throw new DBException(ex);
        } finally {
        	DBHelper.close(rs);
        	DBHelper.close(rs2);
            DBHelper.close(pStmt);
            DBHelper.close(pStmt2);
        }
    }
    
    /**
     * Gibt die Acl zurück, welche mit der übergebenen Kategorie zusammenhängt
     *
     * @param categoryId
     *
     * @return acl
     */
    public final static VKCAcl getAclByCategoryId(Integer categoryId) throws DBException {
        Connection conn = null;

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

    public final static VKCAcl getAclByCategoryId(Integer categoryId, Connection conn) throws DBException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            pStmt = conn.prepareStatement("SELECT aclId FROM Categories WHERE categoryId = ?");
            pStmt.setInt(1, categoryId.intValue());
            rs = pStmt.executeQuery();
            
            if (rs.next()) {
                return retrieveAcl(new Integer(rs.getInt("aclId")), conn);
            } 

            return null;
        } catch (Exception e) {
            throw new DBException(e);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }
    
	/**
	 * Speichert eine DefaultAcl
	 *
	 * @param acl - Die zu speichernde ACL
	 */

	public final static void saveDefaultAcl(VKCAcl acl, Connection conn) throws DBException {
        PreparedStatement pStmt = null;

        try {		
			Vector aclEntries = acl.getAclEntries();
			VKCAclEntry catAcl;
	
			if (acl.getAclId() == null) {
				acl.setAclId(DBHelper.getNewId(conn, "ACLIDS", "aclId"));
			} else {
	            pStmt = conn.prepareStatement("DELETE FROM DefaultCatAcls WHERE StartCatId = ?");
	            pStmt.setInt(1, acl.getAclId().intValue());
	            pStmt.execute();
			}
	
            String stmt = " INSERT INTO DefaultCatAcls ( aclEntryId, startCatId, aclTypeId, accessorId, permissionId, created, creatorId ) " +
            			  " VALUES ( ?, ?, ?, ?, ?, ?, ? )";
			DBHelper.close(pStmt);
            pStmt = conn.prepareStatement(stmt);
			
			// save all new/updated aclentries
			for (Iterator iter = aclEntries.iterator(); iter.hasNext();) {
				catAcl = (VKCAclEntry)iter.next();
				catAcl.setAclEntryId(DBHelper.getNewId(conn, "DefaultCatAcls", "aclEntryId"));
	
				pStmt.setInt(1, catAcl.getAclEntryId().intValue());
				pStmt.setInt(2, catAcl.getAclId().intValue());
				pStmt.setInt(3, catAcl.getAclType().getAclTypeId().intValue());
				pStmt.setInt(4, catAcl.getAccessorId().intValue());
				pStmt.setInt(5, catAcl.getPermission().getPermissionId().intValue());
				pStmt.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
				pStmt.setInt(7, catAcl.getCreatorId().intValue());
				pStmt.execute();
			}
		} catch (Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(pStmt);
		}
	}

	/**
	 * Speichert eine DefaultAcl
	 *
	 * @param acl - Die zu speichernde ACL
	 */

	public static final void saveDefaultAcl(VKCAcl acl) throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			saveDefaultAcl(acl, conn);
			conn.commit();
		} catch (Exception e) {
			DBHelper.rollBack(conn);
			VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error while saving Default-ACL: \n" + acl, e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Liefert eine Default ACL für die Kategorie, deren ID übergeben wurde,
	 * bzw. die Default ACL der nächsten Kategorie oberhalb der übergebenen,
	 * für die eine Default ACL hinterlegt wurde.
	 *
	 * @param parentCategoy Die übergeordnete Kategorie
	 * @param user Der User, der ggf. für OWNER-Einträge eingesetzt werden soll
	 *        wenn 'null' übergeben wird, werden die OWNER-Einträge nicht
	 *        ersetzt
	 *
	 * @return Default-CategoryAcl
	 *
	 * @throws DBException
	 */
	public static final VKCAcl getDefaultCatAcl(VKCUser user, 
												VKCCategory parentCategory,
												boolean searchParentCategories) 
	throws DBException {
		Connection conn = null;

		try {
			conn = WebShare.database.getConnection();
			
			return getDefaultCatAcl(conn, user, parentCategory, searchParentCategories);
		} catch (Exception e) {
			VKCLogManager.errorLog(VKCAclSerializer.class, "1", "Error retrieving DefaultACL for Category-ID: " + parentCategory.getCategoryId(), e);
			throw new DBException(e);
		} finally {
			DBHelper.close(conn);
		}
	}

	/**
	 * Private Methode, die durch die public Methode getDefaultAcl aufgerufen
	 * wird.
	 *
	 * @param conn Die Connection
	 * @param parentCategory Die übergeordnete Kategorie
	 * @param user Der User, der ggf. für OWNER-Einträge eingesetzt werden soll
	 *
	 * @return Default-CategoryAcl
	 *
	 * @throws HibernateException
	 * @throws SQLException
	 * @throws AclException
	 */
	private static final VKCAcl getDefaultCatAcl(Connection conn, 
									 			 VKCUser user,
									 			 VKCCategory parentCategory, 
									 			 boolean searchParentCategories)
	throws DBException {
		PreparedStatement pStmt = null;
		PreparedStatement pStmt2 = null;
		PreparedStatement pStmt3 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;

		int categoryId = parentCategory.getCategoryId().intValue();
		boolean hasDefaultCatAcl;

		try {
			// Handelt es sich bei der ParentCategoryId um die Root-Kategorie?
			if (categoryId != VKCCategory.ROOT_CATEGORY_ID) {
				// Prüfen, ob es für die übergebene Kategorie-ID eine Default-ACL gibt.
				pStmt3 = conn.prepareStatement("SELECT Count(*) AS HasDefaultCatAcl FROM defaultcatacls WHERE defaultcatacls.startcatid = ?");
				pStmt3.setInt(1, categoryId);
				rs3 = pStmt3.executeQuery();
				
				if (rs3.next()) {
					if (rs3.getInt("HasDefaultCatAcl") == 0) {
						hasDefaultCatAcl = false;
					} else {
						hasDefaultCatAcl = true;
					}
				} else {
					hasDefaultCatAcl = false;
				}


				if (!hasDefaultCatAcl) {
					if (!searchParentCategories) {
						return null;
					} 

					try {
						VKCCategory parentParentCategory = VKCCategorySerializer.retrieveCategory(parentCategory.getParentCategoryId());
						
						return getDefaultCatAcl(conn, user, parentParentCategory, true);
					} catch (DBException e) {
						VKCLogManager.errorLog(VKCAclSerializer.class, "", "getDefaultAcl: Error retrieving parent category of category " + categoryId, e);
						categoryId = VKCCategory.ROOT_CATEGORY_ID; // Im Fehlerfall wird die DefaultCatAcl der Rootkategorie geholt
					}
				}
			}

			String sqlStatement =
				"SELECT defaultcatacls.aclTypeId, defaultcatacls.accessorId, defaultcatacls.permissionId, defaultcatacls.creatorid," +
				"       acltypes.aclTypeId, acltypes.name, acltypes.rank, acltypes.description," +
				"       permissions.permissionId, permissions.context, permissions.token, permissions.numvalue, permissions.printstring, permissions.description as perm_descr " +
				"  FROM defaultcatacls, permissions, acltypes" +
				" WHERE defaultcatacls.startcatid = ?" +
				"   AND defaultcatacls.permissionId = permissions.permissionId" +
				"   AND defaultcatacls.aclTypeId = acltypes.aclTypeId";

			String sqlStatement2 = "SELECT name, rank, description FROM acltypes WHERE acltypeid = ?";

			boolean replaceOwner = (user != null);

			pStmt = conn.prepareStatement(sqlStatement);
			pStmt.setInt(1, categoryId);
			
			pStmt2 = conn.prepareStatement(sqlStatement2);
			pStmt2.setInt(1, VKCAclType.USER_TYPE);

			rs = pStmt.executeQuery();

			rs2 = pStmt2.executeQuery();
			rs2.next();

			VKCAcl acl = new VKCAcl();
			acl.setAclId(parentCategory.getCategoryId());

			while (rs.next()) {
				// construct a new VKCCategoryAcl
				VKCAclEntry catAcl = new VKCAclEntry();
				VKCAclType aclType = new VKCAclType();
				VKCPermission perm = new VKCPermission();

				int aclTypeId = rs.getInt("aclTypeId");
				
				aclType.setAclTypeId(new Integer(((aclTypeId == VKCAclType.OWNER_TYPE) && replaceOwner) 
									? VKCAclType.USER_TYPE 
									: aclTypeId));
				
				aclType.setName(((aclTypeId == VKCAclType.OWNER_TYPE) && replaceOwner)
							   ? rs2.getString("name")
							   : rs.getString("name"));
				
				aclType.setRank(new Integer(((aclTypeId == VKCAclType.OWNER_TYPE) && replaceOwner)
							   ? rs2.getInt("rank")
							   : rs.getInt("rank")));
				
				aclType.setDescription(((aclTypeId == VKCAclType.OWNER_TYPE) &&	replaceOwner) 
									  ? rs2.getString("description")
								  	  : rs.getString("description"));
				
				catAcl.setAclType(aclType);

				catAcl.setAccessorId(new Integer(((aclTypeId == VKCAclType.OWNER_TYPE) && replaceOwner) 
									? user.getUserId().intValue()
									: rs.getInt("accessorId")));

				perm.setPermissionId(new Integer(rs.getInt("permissionId")));
				perm.setPrintstring(rs.getString("printstring"));
				perm.setNumvalue(new Integer(rs.getInt("numvalue")));
				perm.setToken((rs.getString("token")).charAt(0));
				perm.setContext(rs.getString("context"));
				perm.setDescription(rs.getString("perm_descr"));
				catAcl.setPermission(perm);

				if (user != null) {
					catAcl.setCreatorId(user.getUserId());
				} else {
					catAcl.setCreatorId(new Integer(rs.getInt("creatorid")));
				}

				if (!acl.contains(catAcl)) {
					acl.addAclEntry(catAcl);
				}
			}

			return acl;
		} catch(Exception ex) {
			throw new DBException(ex);
		} finally {
			DBHelper.close(rs);
			DBHelper.close(rs2);
			DBHelper.close(rs3);
			DBHelper.close(pStmt);
			DBHelper.close(pStmt2);
			DBHelper.close(pStmt3);
		}
	}
}