package org.openswing.swing.table.permissions.database.server;

import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import java.util.Date;

import org.openswing.swing.message.send.java.*;
import org.openswing.swing.table.permissions.java.*;
import org.openswing.swing.util.java.Consts;


/**
 * <p>Title: OpenSwing Framework</p>
 * <p>Description: Grid permissions manager: it manages the fetching of grid permissions.
 * This implementation is based on database tables: it stores and retrieves user roles from a table and after that
 * the permissions from a second table filtered by user roles and grid identifier.
 * </p>
 * <p>Copyright: Copyright (C) 2006 Mauro Carniel</p>
 *
 * <p> This file is part of OpenSwing Framework.
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the (LGPL) Lesser General Public
 * License as published by the Free Software Foundation;
 *
 *                GNU LESSER GENERAL PUBLIC LICENSE
 *                 Version 2.1, February 1999
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Library General Public License for more details.
 *
 * You should have received a copy of the GNU Library General Public
 * License along with this library; if not, write to the Free
 * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *
 *       The author may be contacted at:
 *           maurocarniel@tin.it</p>
 *
 * @author Mauro Carniel
 * @version 1.0
 */
public class DbGridPermissionsManager extends GridPermissionsManager {

  /** connection manager */
  private DbConnectionSource dbConnectionSource = null;
  private DbDigestDescriptor dbDigestDescriptor = null;
  private DbPermissionsDescriptor dbPermissionsDescriptor = null;


  public DbGridPermissionsManager(
      DbConnectionSource dbConnectionSource,
      DbDigestDescriptor dbDigestDescriptor,
      DbPermissionsDescriptor dbPermissionsDescriptor) {
    this.dbConnectionSource = dbConnectionSource;
    this.dbDigestDescriptor = dbDigestDescriptor;
    this.dbPermissionsDescriptor = dbPermissionsDescriptor;
  }


  /**
   * @param username username to use to fetch associated roles
   * @return list of role identifiers associated to the specified user (Object[] values)
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   */
  public final ArrayList getUserRoles() throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      String roleIdFields = "";
      for(int i=0;i<dbPermissionsDescriptor.getRoleIdFieldNamesInUserRolesTable().length;i++)
        roleIdFields += dbPermissionsDescriptor.getRoleIdFieldNamesInUserRolesTable()[i]+",";
      roleIdFields = roleIdFields.substring(0,roleIdFields.length()-1);
      String sql =
        "select "+
        roleIdFields+" "+
        "from "+dbPermissionsDescriptor.getUserRolesTableName()+" "+
        "where "+
        dbPermissionsDescriptor.getUsernameFieldNameInUserRolesTable()+"=? ";
      for(int i=0;i<dbPermissionsDescriptor.getRolesWhereFieldNames().length;i++)
        sql += " and "+dbPermissionsDescriptor.getRolesWhereFieldNames()[i]+"=? ";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1,getUsername());
      for(int i=0;i<dbPermissionsDescriptor.getRolesWhereValues().length;i++)
        pstmt.setObject(i+2,dbPermissionsDescriptor.getRolesWhereValues()[i]);
      rset = pstmt.executeQuery();

      ArrayList roleIds = new ArrayList();
      Object[] roleId = null;
      while(rset.next()) {
        roleId = new Object[rset.getMetaData().getColumnCount()];
        for(int i=0;i<roleId.length;i++)
          roleId[i] = rset.getObject(i+1);
        roleIds.add(roleId);
      }
      return roleIds;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * @param functionId identifier (functionId) associated to the grid
   * @param userRoles list of role identifiers associated to the specified user
   * @param columnAttributes list of attribute names, that identify columns
   * @param columnsVisibility define which columns are visible
   * @param columnEditableInInsert define which columns are editable on insert; used to correctly define GridPermissions content: a column will be marked as NOT editable if currently editable but NOT the inverse
   * @param columnEditableInEdit define which columns are editable on edit; used to correctly define GridPermissions content: a column will be marked as NOT editable if currently editable but NOT the inverse
   * @param columnsMandatory define which columns are required on insert/edit mode; used to correctly define GridPermissions content: a column will be marked as required if currently not required but NOT the inverse
   * @return GridPermissions object, built starting from user roles for the specified grid identifier
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   */
  public final GridPermissions getUserGridPermissions(String functionId,ArrayList userRoles,String[] columnAttributes,boolean[] columnsVisibility,boolean[] columnEditableInInsert,boolean[] columnsEditableInEdit,boolean[] columnsMandatory) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      String sql =
        "select "+
        dbPermissionsDescriptor.getColumnsAttributeFieldNameInGridPermissionsTable()+","+
        dbPermissionsDescriptor.getEditableColumnsInInsertFieldNameInGridPermissionsTable()+","+
        dbPermissionsDescriptor.getEditableColumnsInEditFieldNameInGridPermissionsTable()+","+
        dbPermissionsDescriptor.getColumnsMandatoryFieldNameInGridPermissionsTable()+","+
        dbPermissionsDescriptor.getColumnsVisibilityFieldNameInGridPermissionsTable()+" "+
        "from "+dbPermissionsDescriptor.getGridPermissionsTableName()+" "+
        "where "+dbPermissionsDescriptor.getFunctionIdFieldNameInGridPermissionsTable()+"=? ";

      for(int j=0;j<userRoles.size();j++) {
        sql += " and (";
        for(int i=0;i<dbPermissionsDescriptor.getRoleIdFieldNamesInGridPermissionsTable().length;i++)
          sql += dbPermissionsDescriptor.getRoleIdFieldNamesInGridPermissionsTable()[i]+"=? or ";
        sql = sql.substring(0,sql.length()-3);
        sql += ") ";
      }

      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1,functionId);
      Object[] roleId =null;
      int count = 2;
      for(int j=0;j<userRoles.size();j++) {
        roleId = (Object[])userRoles.get(j);
        for(int i=0;i<roleId.length;i++) {
          pstmt.setObject(count, roleId[i]);
          count++;
        }
      }
      rset = pstmt.executeQuery();

      GridPermissions permissions = new GridPermissions(
        functionId,
        getUsername(),
        (String[])columnAttributes.clone(),
        (boolean[])columnsVisibility.clone(),
        (boolean[])columnEditableInInsert.clone(),
        (boolean[])columnsEditableInEdit.clone(),
        (boolean[])columnsMandatory.clone()
      );

      String[] aux = null;
      while(rset.next()) {
        aux = rset.getString(1).split(","); // columnAttributes
        for(int i=0;i<aux.length;i++)
          permissions.getColumnsAttribute()[i] = aux[i];

        aux = rset.getString(2).split(","); // editableColumnsInInsertFieldName
        for(int i=0;i<aux.length;i++)
          permissions.getColumnsEditabilityInInsert()[i] = permissions.getColumnsEditabilityInInsert()[i] && aux[i].equals("true");

        aux = rset.getString(3).split(","); // editableColumnsInEdit
        for(int i=0;i<aux.length;i++)
          permissions.getColumnsEditabilityInEdit()[i] = permissions.getColumnsEditabilityInEdit()[i] && aux[i].equals("true");

        aux = rset.getString(4).split(","); // columnsMandatory
        for(int i=0;i<aux.length;i++)
          permissions.getColumnsMandatory()[i] = permissions.getColumnsMandatory()[i] || aux[i].equals("true");

        aux = rset.getString(5).split(","); // columnsVisibility
        for(int i=0;i<aux.length;i++)
          permissions.getColumnsVisibility()[i] = aux[i].equals("true");

      }

      return permissions;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * Store in grid permissions defaults table.
   * @param functionId identifier (functionId) associated to the grid
   * @param columnAttributes list of attribute names, that identify columns
   * @param columnsVisibility define which columns are visible
   * @param columnEditableInInsert define which columns are editable on insert; used to correctly define GridPermissions content: a column will be marked as NOT editable if currently editable but NOT the inverse
   * @param columnEditableInEdit define which columns are editable on edit; used to correctly define GridPermissions content: a column will be marked as NOT editable if currently editable but NOT the inverse
   * @param columnsMandatory define which columns are required on insert/edit mode; used to correctly define GridPermissions content: a column will be marked as required if currently not required but NOT the inverse
   * @throws Throwable throwed if storing operation does not correctly accomplished
   */
  public void storeGridPermissionsDefaults(String functionId,String[] columnAttributes,boolean[] columnsVisibility,boolean[] columnEditableInInsert,boolean[] columnsEditableInEdit,boolean[] columnsMandatory) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();

      String sql =
          "update "+dbPermissionsDescriptor.getGridPermissionsDefaultsTableName()+" set "+
          dbPermissionsDescriptor.getColumnsAttributeFieldNameInDefaultsTableName()+"=? and "+
          dbPermissionsDescriptor.getColumnsMandatoryFieldNameInDefaultsTableName()+"=? and "+
          dbPermissionsDescriptor.getColumnsVisibilityFieldNameInDefaultsTableName()+"=? and "+
          dbPermissionsDescriptor.getEditableColumnsInEditFieldNameInDefaultsTableName()+"=? and "+
          dbPermissionsDescriptor.getEditableColumnsInInsertFieldNameInDefaultsTableName()+"=? where "+
          dbPermissionsDescriptor.getFunctionIdFieldNameInDefaultsTableName()+"=? ";
      for(int i=0;i<dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName().length;i++)
        sql += " and "+dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName()[i]+"=? ";

      pstmt = conn.prepareStatement(sql);

      String aux = "";
      for(int i=0;i<columnAttributes.length;i++)
        aux += columnAttributes[i]+",";
      aux = aux.substring(0,aux.length()-1);
      pstmt.setString(1,aux);

      aux = "";
      for(int i=0;i<columnAttributes.length;i++)
        aux += (columnsMandatory[i]?"true":"false")+",";
      aux = aux.substring(0,aux.length()-1);
      pstmt.setString(2,aux);

      aux = "";
      for(int i=0;i<columnAttributes.length;i++)
        aux += (columnsVisibility[i]?"true":"false")+",";
      aux = aux.substring(0,aux.length()-1);
      pstmt.setString(3,aux);

      aux = "";
      for(int i=0;i<columnAttributes.length;i++)
        aux += (columnsEditableInEdit[i]?"true":"false")+",";
      aux = aux.substring(0,aux.length()-1);
      pstmt.setString(4,aux);

      aux = "";
      for(int i=0;i<columnAttributes.length;i++)
        aux += (columnEditableInInsert[i]?"true":"false")+",";
      aux = aux.substring(0,aux.length()-1);
      pstmt.setString(5,aux);

      pstmt.setString(6,functionId);
      for(int i=0;i<dbPermissionsDescriptor.getOtherFieldValuesInDefaultsTableName().length;i++)
        pstmt.setObject(i+7,dbPermissionsDescriptor.getOtherFieldValuesInDefaultsTableName()[i]);

      int num = pstmt.executeUpdate();
      if (num==0) {
        sql =
          "insert into "+dbPermissionsDescriptor.getGridPermissionsDefaultsTableName()+"("+
          dbPermissionsDescriptor.getColumnsAttributeFieldNameInDefaultsTableName()+","+
          dbPermissionsDescriptor.getColumnsMandatoryFieldNameInDefaultsTableName()+","+
          dbPermissionsDescriptor.getColumnsVisibilityFieldNameInDefaultsTableName()+","+
          dbPermissionsDescriptor.getEditableColumnsInEditFieldNameInDefaultsTableName()+","+
          dbPermissionsDescriptor.getEditableColumnsInInsertFieldNameInDefaultsTableName()+","+
          dbPermissionsDescriptor.getFunctionIdFieldNameInDefaultsTableName();
        for(int i=0;i<dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName().length;i++)
          sql += ","+dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName()[i];
        sql += ") values(?,?,?,?,?,?";
        for(int i=0;i<dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName().length;i++)
          sql += ",?";
        sql += ")";

        pstmt = conn.prepareStatement(sql);

        aux = "";
        for(int i=0;i<columnAttributes.length;i++)
          aux += columnAttributes[i]+",";
        aux = aux.substring(0,aux.length()-1);
        pstmt.setString(1,aux);

        aux = "";
        for(int i=0;i<columnAttributes.length;i++)
          aux += (columnsMandatory[i]?"true":"false")+",";
        aux = aux.substring(0,aux.length()-1);
        pstmt.setString(2,aux);

        aux = "";
        for(int i=0;i<columnAttributes.length;i++)
          aux += (columnsVisibility[i]?"true":"false")+",";
        aux = aux.substring(0,aux.length()-1);
        pstmt.setString(3,aux);

        aux = "";
        for(int i=0;i<columnAttributes.length;i++)
          aux += (columnsEditableInEdit[i]?"true":"false")+",";
        aux = aux.substring(0,aux.length()-1);
        pstmt.setString(4,aux);

        aux = "";
        for(int i=0;i<columnAttributes.length;i++)
          aux += (columnEditableInInsert[i]?"true":"false")+",";
        aux = aux.substring(0,aux.length()-1);
        pstmt.setString(5,aux);

        pstmt.setString(6,functionId);
        for(int i=0;i<dbPermissionsDescriptor.getOtherFieldValuesInDefaultsTableName().length;i++)
          pstmt.setObject(i+7,dbPermissionsDescriptor.getOtherFieldValuesInDefaultsTableName()[i]);

        pstmt.execute();
      }

      conn.commit();

    }
    catch (Throwable t) {
      throw new IOException(t.getMessage());
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }

  }


  /**
   * Delete all grid permissions for the specified grid identifier, independently from the current user.
   * Grid permissions defaults are removed too.
   * This method is automatically invoked if "grid digest" comparison lead to discover a grid change: in this case all grid permissions must be removed.
   * @param functionId identifier (functionId) associated to the grid
   * @throws Throwable throwed if deleting operation does not correctly accomplished
   */
  public void deleteAllGridPermissionsPerFunctionId(String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();

      // remove grid permissions...
      Hashtable h = dbPermissionsDescriptor.deleteAllGridPermissions(functionId);
      String where = " and ";
      Enumeration en = h.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      pstmt = conn.prepareStatement(
        "delete from "+dbPermissionsDescriptor.getGridPermissionsTableName()+" "+
        "where "+dbPermissionsDescriptor.getFunctionIdFieldNameInGridPermissionsTable()+"=? "+where
      );
      pstmt.setString(1,functionId);

      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+2,whereValues.get(i));

      pstmt.execute();

      // remove grid permissions defaults...
      String sql =
        "delete from "+dbPermissionsDescriptor.getGridPermissionsDefaultsTableName()+" "+
        "where "+dbPermissionsDescriptor.getFunctionIdFieldNameInDefaultsTableName()+"=? ";
      for(int i=0;i<dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName().length;i++)
        sql += " and "+dbPermissionsDescriptor.getOtherFieldNamesInDefaultsTableName()[i]+"=? ";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1,functionId);
      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+2,dbPermissionsDescriptor.getOtherFieldValuesInDefaultsTableName()[i]);
      pstmt.execute();

      conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * Store the "grid digest", i.e. a value that globally identify the current grid configuration.
   * @throws Throwable throwed if storing operation does not correctly accomplished
   */
  public void storeGridDigest(String functionId,String gridDigest) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = dbConnectionSource.getConnection();

      Hashtable h1 = dbDigestDescriptor.storeGridDigestOnSetUpdate();
      Hashtable h2 = dbDigestDescriptor.storeGridDigestOnWhereUpdate();
      String where = " and ";
      Enumeration en = h2.keys();
      String field = null;
      ArrayList whereValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        where += field+"=? and ";
        whereValues.add(h2.get(field));
      }
      if (where.length()>5)
        where = where.substring(0,where.length()-4);
      else
        where = "";

      String set = ",";
      en = h1.keys();
      field = null;
      ArrayList setValues = new ArrayList();
      while(en.hasMoreElements()) {
        field = en.nextElement().toString();
        set += field+"=?,";
        setValues.add(h1.get(field));
      }
      if (set.length()>1)
        set = set.substring(0,set.length()-1);
      else
        set = "";

      pstmt = conn.prepareStatement(
        "update "+dbDigestDescriptor.getDigestTableName()+" "+
        "set "+dbDigestDescriptor.getDigestFieldName()+"=? "+set+" "+
        "where "+dbDigestDescriptor.getFunctionIdFieldName()+"=? "+where
      );
      pstmt.setString(1,gridDigest);
      pstmt.setString(2+setValues.size(),functionId);

      for(int i=0;i<setValues.size();i++)
        pstmt.setObject(i+2,setValues.get(i));
      for(int i=0;i<whereValues.size();i++)
        pstmt.setObject(i+setValues.size()+3,whereValues.get(i));

      int num = pstmt.executeUpdate();

      if (num==0) {

        Hashtable h = dbDigestDescriptor.storeGridDigestOnInsert();
        String ins = "";
        en = h.keys();
        field = null;
        String pkQuestions = "";
        ArrayList insValues = new ArrayList();
        while(en.hasMoreElements()) {
          field = en.nextElement().toString();
          pkQuestions += "?,";
          ins += ","+field;
          insValues.add(h.get(field));
        }

        pstmt = conn.prepareStatement(
          "insert into "+dbDigestDescriptor.getDigestTableName()+"("+
          dbDigestDescriptor.getDigestFieldName()+","+
          dbDigestDescriptor.getFunctionIdFieldName()+ins+") "+
          "values("+pkQuestions+"?,?)"
        );
        pstmt.setString(1,gridDigest);
        pstmt.setString(2,functionId);

        for(int i=0;i<insValues.size();i++)
          pstmt.setObject(i+3,insValues.get(i));
        pstmt.execute();
      }
      conn.commit();
    }
    finally {
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }


  /**
   * @return retrieve the "grid digest", i.e. a value that globally identify the current grid configuration; this digest is used to check if grid columns have been changed from last grid execution: in this case all grid permissions will be deleted
   * @throws Throwable throwed if fetching operation does not correctly accomplished
   * Note: this method returns null if no digest has been yet stored (i.e. this is the first time the grid is being viewed)
   */
  public String getLastGridDigest(String functionId) throws Throwable {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    try {
      conn = dbConnectionSource.getConnection();
      pstmt = conn.prepareStatement(
        "select "+
        dbDigestDescriptor.getDigestFieldName()+" "+
        "from "+dbDigestDescriptor.getDigestTableName()+" "+
        "where "+dbDigestDescriptor.getFunctionIdFieldName()+"=? "
      );
      pstmt.setString(1,functionId);
      rset = pstmt.executeQuery();

      if(rset.next()) {
        return rset.getString(1);
      }
      return null;
    }
    finally {
      try {
        if (rset!=null)
          rset.close();
      }
      catch (Exception ex) {
      }
      try {
        if (pstmt!=null)
          pstmt.close();
      }
      catch (Exception ex) {
      }
      try {
        if (conn != null) {
          dbConnectionSource.releaseConnection(conn);
        }
      }
      catch (Exception ex1) {
      }
    }
  }



}
