/*
 * The contents of this file are subject to the Mozilla Public License Version 1.1
 * (the "License"); you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at <http://www.mozilla.org/MPL/>.
 * 
 * Software distributed under the License is distributed on an "AS IS" basis, WITHOUT
 * WARRANTY OF ANY KIND, either express or implied. See the License for the specific
 * language governing rights and limitations under the License.
 * 
 * The Original Code is the Venice Web Communities System.
 * 
 * The Initial Developer of the Original Code is Eric J. Bowersox <erbo@silcom.com>,
 * for Silverwrist Design Studios.  Portions created by Eric J. Bowersox are
 * Copyright (C) 2002 Eric J. Bowersox/Silverwrist Design Studios.  All Rights Reserved.
 * 
 * Contributor(s): 
 */
package com.silverwrist.dynamo.db;

import java.sql.*;
import java.util.*;
import com.silverwrist.util.*;
import com.silverwrist.dynamo.except.*;
import com.silverwrist.dynamo.iface.*;

public class UserManagerOps_mysql extends UserManagerOps
{
  /*--------------------------------------------------------------------------------
   * Static data members
   *--------------------------------------------------------------------------------
   */

  private static final Integer GACLID_NONE = new Integer(-1);

  /*--------------------------------------------------------------------------------
   * Attributes
   *--------------------------------------------------------------------------------
   */

  private DBUtilities m_utils;                // reference to utilities object
  private UserObjectOps m_obj_ops = null;     // object operations object
  private GroupObjectOps m_group_ops = null;  // group operations object

  /*--------------------------------------------------------------------------------
   * Constructor
   *--------------------------------------------------------------------------------
   */

  public UserManagerOps_mysql(DBConnectionPool pool)
  {
    super(pool);
    m_utils = (DBUtilities)(pool.queryService(DBUtilities.class));

  } // end constructor

  /*--------------------------------------------------------------------------------
   * Abstract implementations from class UserManagerOps
   *--------------------------------------------------------------------------------
   */

  synchronized UserObjectOps getObjectOps()
  {
    if (m_obj_ops==null)
      m_obj_ops = new UserObjectOps_mysql(getPool(),m_utils);
    return m_obj_ops;

  } // end getObjectOps

  synchronized GroupObjectOps getGroupOps()
  {
    if (m_group_ops==null)
      m_group_ops = new GroupObjectOps_mysql(getPool(),m_utils);
    return m_group_ops;

  } // end getGroupOps

  Map getAnonymousUserData() throws DatabaseException
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection and statement
      conn = getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT uid, username, email, created, last_accessed FROM users "
			     + "WHERE is_anon = 1;");
      if (!(rs.next()))
	return null;  // no anonymous user info (will throw exception later)

      // Create and return the Map containing the user data.
      HashMap rc = new HashMap();
      rc.put(HMKEY_UID,new Integer(rs.getInt(1)));
      rc.put(HMKEY_USERNAME,rs.getString(2));
      rc.put(HMKEY_EMAIL,rs.getString(3));
      rc.put(HMKEY_LOCKED,Boolean.FALSE);
      rc.put(HMKEY_NOSPAM,Boolean.TRUE);
      rc.put(HMKEY_CREATED,m_utils.getDateTime(rs,4));
      java.util.Date lacc = m_utils.getDateTime(rs,5);
      if (lacc!=null)
	rc.put(HMKEY_LAST_ACCESS,lacc);
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getAnonymousUserData

  Map getUserData(int id) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // execute the statement to get the user data
      stmt = conn.prepareStatement("SELECT username, email, locked, nospam, created, last_accessed FROM users "
				   + "WHERE uid = ? AND is_anon = 0;");
      stmt.setInt(1,id);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;  // no user info

      // Create and return the Map containing the user data.
      HashMap rc = new HashMap();
      rc.put(HMKEY_UID,new Integer(id));
      rc.put(HMKEY_USERNAME,rs.getString(1));
      rc.put(HMKEY_EMAIL,rs.getString(2));
      rc.put(HMKEY_LOCKED,Boolean.valueOf(rs.getBoolean(3)));
      rc.put(HMKEY_NOSPAM,Boolean.valueOf(rs.getBoolean(4)));
      rc.put(HMKEY_CREATED,m_utils.getDateTime(rs,5));
      java.util.Date lacc = m_utils.getDateTime(rs,6);
      if (lacc!=null)
	rc.put(HMKEY_LAST_ACCESS,lacc);
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getUserData

  Map getUserData(String username) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // execute the statement to get the user data
      stmt = conn.prepareStatement("SELECT uid, email, locked, nospam, created, last_accessed FROM users "
				   + "WHERE username = ? AND is_anon = 0;");
      stmt.setString(1,username);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;  // no user info

      // Create and return the Map containing the user data.
      HashMap rc = new HashMap();
      rc.put(HMKEY_UID,new Integer(rs.getInt(1)));
      rc.put(HMKEY_USERNAME,username);
      rc.put(HMKEY_EMAIL,rs.getString(2));
      rc.put(HMKEY_LOCKED,Boolean.valueOf(rs.getBoolean(3)));
      rc.put(HMKEY_NOSPAM,Boolean.valueOf(rs.getBoolean(4)));
      rc.put(HMKEY_CREATED,m_utils.getDateTime(rs,5));
      java.util.Date lacc = m_utils.getDateTime(rs,6);
      if (lacc!=null)
	rc.put(HMKEY_LAST_ACCESS,lacc);
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getUserData

  Map createNewUser(String username, String email) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the users table
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES users WRITE;");

      // see if the user already exists
      stmt = conn.prepareStatement("SELECT uid FROM users WHERE username = ?;");
      stmt.setString(1,username);
      rs = stmt.executeQuery();
      if (rs.next())
      { // user exists - our operation is blown
	DatabaseException de = new DatabaseException(UserManagerOps_mysql.class,"UserMessages","user.exists");
	de.setParameter(0,username);
	throw de;

      } // end if

      // close down previous statement in prep for next one
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);

      // insert the new user record!
      stmt = conn.prepareStatement("INSERT INTO users (username, email, created) VALUES (?, ?, ?);");
      stmt.setString(1,username);
      stmt.setString(2,email);
      java.util.Date created = new java.util.Date();
      m_utils.setDateTime(stmt,3,created);
      stmt.executeUpdate();

      // Generate the return Map, suitable for creating a new object.
      HashMap rc = new HashMap();
      rc.put(HMKEY_UID,new Integer(MySQLUtils.getLastInsertInt(conn)));
      rc.put(HMKEY_USERNAME,username);
      rc.put(HMKEY_EMAIL,email);
      rc.put(HMKEY_LOCKED,Boolean.FALSE);
      rc.put(HMKEY_NOSPAM,Boolean.FALSE);
      rc.put(HMKEY_CREATED,created);
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end createNewUser

  Map getGroupData(int id) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // execute the statement to get the group data
      stmt = conn.prepareStatement("SELECT groupname, gaclid FROM groups WHERE gid = ?;");
      stmt.setInt(1,id);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;  // no group info

      // Create and return the Map containing the group data.
      HashMap rc = new HashMap();
      rc.put(HMKEY_GID,new Integer(id));
      rc.put(HMKEY_GROUPNAME,rs.getString(1));
      rc.put(HMKEY_GACLID,new Integer(rs.getInt(2)));
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getGroupData

  Map getGroupData(String groupname) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // execute the statement to get the group data
      stmt = conn.prepareStatement("SELECT gid, gaclid FROM groups WHERE groupname = ?;");
      stmt.setString(1,groupname);
      rs = stmt.executeQuery();
      if (!(rs.next()))
	return null;  // no group info

      // Create and return the Map containing the group data.
      HashMap rc = new HashMap();
      rc.put(HMKEY_GID,new Integer(rs.getInt(1)));
      rc.put(HMKEY_GROUPNAME,groupname);
      rc.put(HMKEY_GACLID,new Integer(rs.getInt(2)));
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end getGroupData

  Map createNewGroup(String groupname) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt = null;
    Statement stmt2 = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the groups table
      stmt2 = conn.createStatement();
      stmt2.executeUpdate("LOCK TABLES groups WRITE;");

      // see if the group already exists
      stmt = conn.prepareStatement("SELECT gid FROM groups WHERE groupname = ?;");
      stmt.setString(1,groupname);
      rs = stmt.executeQuery();
      if (rs.next())
      { // group exists - our operation is blown
	DatabaseException de = new DatabaseException(UserManagerOps_mysql.class,"UserMessages","group.exists");
	de.setParameter(0,groupname);
	throw de;

      } // end if

      // close down previous statement in prep for next one
      SQLUtils.shutdown(rs);
      rs = null;
      SQLUtils.shutdown(stmt);

      // insert the new group record!
      stmt = conn.prepareStatement("INSERT INTO groups (groupname) VALUES (?);");
      stmt.setString(1,groupname);
      stmt.executeUpdate();

      // Generate the return Map, suitable for creating a new object.
      HashMap rc = new HashMap();
      rc.put(HMKEY_GID,new Integer(MySQLUtils.getLastInsertInt(conn)));
      rc.put(HMKEY_GROUPNAME,groupname);
      rc.put(HMKEY_GACLID,GACLID_NONE);
      return rc;

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt2);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end createNewGroup

  void loadUserDefaults(int uid, int[] nsids) throws DatabaseException
  {
    Connection conn = null;
    PreparedStatement stmt_insert = null;
    Statement stmt = null;
    ResultSet rs = null;
    try
    { // get a connection
      conn = getConnection();

      // lock the global and user properties tables
      stmt = conn.createStatement();
      stmt.executeUpdate("LOCK TABLES userprop WRITE, globalprop READ;");

      // prepare the fancy insert statement
      stmt_insert = conn.prepareStatement("INSERT INTO userprop (uid, nsid, prop_name, prop_value) "
					  + "SELECT ?, globalprop.nsid, globalprop.prop_name, "
					  + "globalprop.prop_value FROM globalprop "
					  + "WHERE globalprop.nsid = ?;");
      for (int i=0; i<nsids.length; i++)
      { // execute each statement in turn to insert each namespace's default values
	stmt_insert.setInt(1,uid);
	stmt_insert.setInt(2,nsids[i]);
	stmt_insert.executeUpdate();

      } // end for

    } // end try
    catch (SQLException e)
    { // translate to a general DatabaseException
      throw generalException(e);

    } // end catch
    finally
    { // shut everything down
      MySQLUtils.unlockTables(conn);
      SQLUtils.shutdown(rs);
      SQLUtils.shutdown(stmt);
      SQLUtils.shutdown(stmt_insert);
      SQLUtils.shutdown(conn);

    } // end finally

  } // end loadUserDefaults

} // end class UserManagerOps_mysql
