/**
 * $RCSfile$
 * $Revision: 3117 $
 * $Date: 2005-11-25 22:57:29 -0300 (Fri, 25 Nov 2005) $
 *
 * Copyright (C) 2004-2008 Jive Software. All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (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.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.jivesoftware.openfire.group;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.XMPPServer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;

/**
 * Database implementation of the GroupManager interface.
 *
 * @author Matt Tucker
 */
public class DefaultGroupProvider implements GroupProvider {

	private static final Logger Log = LoggerFactory.getLogger(DefaultGroupProvider.class);

    private static final String INSERT_GROUP =
        "INSERT INTO ofGroup (groupName, description) VALUES (?, ?)";
    private static final String SAVE_GROUP =
        "UPDATE ofGroup SET description=? WHERE groupName=?";
    private static final String SET_GROUP_NAME_1 =
        "UPDATE ofGroup SET groupName=? WHERE groupName=?";
    private static final String SET_GROUP_NAME_2 =
        "UPDATE ofGroupProp SET groupName=? WHERE groupName=?";
    private static final String SET_GROUP_NAME_3 =
        "UPDATE ofGroupUser SET groupName=? WHERE groupName=?";
    private static final String DELETE_GROUP_USERS =
        "DELETE FROM ofGroupUser WHERE groupName=?";
    private static final String DELETE_PROPERTIES =
        "DELETE FROM ofGroupProp WHERE groupName=?";
    private static final String DELETE_GROUP =
        "DELETE FROM ofGroup WHERE groupName=?";
    private static final String GROUP_COUNT = "SELECT count(*) FROM ofGroup";
     private static final String LOAD_ADMINS =
        "SELECT username FROM ofGroupUser WHERE administrator=1 AND groupName=? ORDER BY username";
    private static final String LOAD_MEMBERS =
        "SELECT username FROM ofGroupUser WHERE administrator=0 AND groupName=? ORDER BY username";
    private static final String LOAD_GROUP =
        "SELECT description FROM ofGroup WHERE groupName=?";
    private static final String REMOVE_USER =
        "DELETE FROM ofGroupUser WHERE groupName=? AND username=?";
    private static final String ADD_USER =
        "INSERT INTO ofGroupUser (groupName, username, administrator) VALUES (?, ?, ?)";
    private static final String UPDATE_USER =
        "UPDATE ofGroupUser SET administrator=? WHERE groupName=? AND username=?";
    private static final String USER_GROUPS =
        "SELECT groupName FROM ofGroupUser WHERE username=?";
    private static final String GROUPLIST_CONTAINERS =
            "SELECT groupName from ofGroupProp " +
            "where name='sharedRoster.groupList' " +
            "AND propValue LIKE ?";
    private static final String PUBLIC_GROUPS = 
    		"SELECT groupName from ofGroupProp " +
    		"WHERE name='sharedRoster.showInRoster' " +
    		"AND propValue='everybody'";
    private static final String GROUPS_FOR_PROP = 
    		"SELECT groupName from ofGroupProp " +
    		"WHERE name=? " +
    		"AND propValue=?";
    private static final String ALL_GROUPS = "SELECT groupName FROM ofGroup ORDER BY groupName";
    private static final String SEARCH_GROUP_NAME = "SELECT groupName FROM ofGroup WHERE groupName LIKE ? ORDER BY groupName";
    private static final String LOAD_SHARED_GROUPS =
            "SELECT groupName FROM ofGroupProp WHERE name='sharedRoster.showInRoster' " +
            "AND propValue IS NOT NULL AND propValue <> 'nobody'";
    private static final String LOAD_PROPERTIES =
            "SELECT name, propValue FROM ofGroupProp WHERE groupName=?";    	

    private XMPPServer server = XMPPServer.getInstance();

    public Group createGroup(String name) throws GroupAlreadyExistsException {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(INSERT_GROUP);
            pstmt.setString(1, name);
            pstmt.setString(2, "");
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
        Collection<JID> members = getMembers(name, false);
        Collection<JID> administrators = getMembers(name, true);
        return new Group(name, "", members, administrators);
    }

    public Group getGroup(String name) throws GroupNotFoundException {
        String description = null;

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_GROUP);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            if (!rs.next()) {
                throw new GroupNotFoundException("Group with name "
                    + name + " not found.");
            }
            description = rs.getString(1);
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        Collection<JID> members = getMembers(name, false);
        Collection<JID> administrators = getMembers(name, true);
        return new Group(name, description, members, administrators);
    }

    public void setDescription(String name, String description)
            throws GroupNotFoundException
    {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(SAVE_GROUP);
            pstmt.setString(1, description);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
            throw new GroupNotFoundException();
        }
        finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    public void setName(String oldName, String newName) throws UnsupportedOperationException,
            GroupAlreadyExistsException
    {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean abortTransaction = false;
        try {
            con = DbConnectionManager.getTransactionConnection();
            pstmt = con.prepareStatement(SET_GROUP_NAME_1);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
            DbConnectionManager.fastcloseStmt(pstmt);
            
            pstmt = con.prepareStatement(SET_GROUP_NAME_2);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
            DbConnectionManager.fastcloseStmt(pstmt);
            
            pstmt = con.prepareStatement(SET_GROUP_NAME_3);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
            abortTransaction = true;
        }
        finally {
            DbConnectionManager.closeStatement(pstmt);
            DbConnectionManager.closeTransactionConnection(con, abortTransaction);
        }
    }

    public void deleteGroup(String groupName) {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean abortTransaction = false;
        try {
            con = DbConnectionManager.getTransactionConnection();
            // Remove all users in the group.
            pstmt = con.prepareStatement(DELETE_GROUP_USERS);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
            DbConnectionManager.fastcloseStmt(pstmt);
            
            // Remove all properties of the group.
            pstmt = con.prepareStatement(DELETE_PROPERTIES);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
            DbConnectionManager.fastcloseStmt(pstmt);
            
            // Remove the group entry.
            pstmt = con.prepareStatement(DELETE_GROUP);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
            abortTransaction = true;
        }
        finally {
            DbConnectionManager.closeStatement(pstmt);
            DbConnectionManager.closeTransactionConnection(con, abortTransaction);
        }
    }

    public int getGroupCount() {
        int count = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(GROUP_COUNT);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return count;
    }

    /**
     * Returns the name of the groups that are shared groups.
     *
     * @return the name of the groups that are shared groups.
     */
    public Collection<String> getSharedGroupNames() {
        Collection<String> groupNames = new HashSet<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_SHARED_GROUPS);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException sqle) {
            Log.error(sqle.getMessage(), sqle);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return groupNames;
    }

    public Collection<String> getSharedGroupNames(JID user) {
    	Set<String> answer = new HashSet<String>();
    	Collection<String> userGroups = getGroupNames(user);
    	answer.addAll(userGroups);
    	for (String userGroup : userGroups) {
    		answer.addAll(getVisibleGroupNames(userGroup));
    	}
        answer.addAll(getPublicSharedGroupNames());
        return answer;
    }

	public Collection<String> getVisibleGroupNames(String userGroup) {
		Set<String> groupNames = new HashSet<String>();
        Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
		    con = DbConnectionManager.getConnection();
		    pstmt = con.prepareStatement(GROUPLIST_CONTAINERS);
		    pstmt.setString(1, "%" + userGroup + "%");
		    rs = pstmt.executeQuery();
		    while (rs.next()) {
		        groupNames.add(rs.getString(1));
		    }
		}
		catch (SQLException sqle) {
		    Log.error(sqle.getMessage(), sqle);
		}
		finally {
		    DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return groupNames;
	}
    
	public Collection<String> search(String key, String value) {
		Set<String> groupNames = new HashSet<String>();
        Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
		    con = DbConnectionManager.getConnection();
		    pstmt = con.prepareStatement(GROUPS_FOR_PROP);
		    pstmt.setString(1, key);
		    pstmt.setString(2, value);
		    rs = pstmt.executeQuery();
		    while (rs.next()) {
		        groupNames.add(rs.getString(1));
		    }
		}
		catch (SQLException sqle) {
		    Log.error(sqle.getMessage(), sqle);
		}
		finally {
		    DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return groupNames;
	}

	public Collection<String> getPublicSharedGroupNames() {
		Set<String> groupNames = new HashSet<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(PUBLIC_GROUPS);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException sqle) {
            Log.error(sqle.getMessage(), sqle);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return groupNames;
	}

    public Collection<String> getGroupNames() {
        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ALL_GROUPS);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);       }
        return groupNames;
    }

    public Collection<String> getGroupNames(int startIndex, int numResults) {
        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_GROUPS);
            rs = pstmt.executeQuery();
            DbConnectionManager.scrollResultSet(rs, startIndex);
            int count = 0;
            while (rs.next() && count < numResults) {
                groupNames.add(rs.getString(1));
                count++;
            }
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return groupNames;
    }

    public Collection<String> getGroupNames(JID user) {
        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(USER_GROUPS);
            pstmt.setString(1, server.isLocal(user) ? user.getNode() : user.toString());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return groupNames;
    }

    public void addMember(String groupName, JID user, boolean administrator) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ADD_USER);
            pstmt.setString(1, groupName);
            pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
            pstmt.setInt(3, administrator ? 1 : 0);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    public void updateMember(String groupName, JID user, boolean administrator) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(UPDATE_USER);
            pstmt.setInt(1, administrator ? 1 : 0);
            pstmt.setString(2, groupName);
            pstmt.setString(3, server.isLocal(user) ? user.getNode() : user.toString());
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    public void deleteMember(String groupName, JID user) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(REMOVE_USER);
            pstmt.setString(1, groupName);
            pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(pstmt, con);
        }
    }

    public boolean isReadOnly() {
        return false;
    }

    public Collection<String> search(String query) {
        return search(query, 0, Integer.MAX_VALUE);
    }

    public Collection<String> search(String query, int startIndex, int numResults) {
        if (query == null || "".equals(query)) {
            return Collections.emptyList();
        }
        // SQL LIKE queries don't map directly into a keyword/wildcard search like we want.
        // Therefore, we do a best approximiation by replacing '*' with '%' and then
        // surrounding the whole query with two '%'. This will return more data than desired,
        // but is better than returning less data than desired.
        query = "%" + query.replace('*', '%') + "%";
        if (query.endsWith("%%")) {
            query = query.substring(0, query.length()-1);
        }

        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            if ((startIndex==0) && (numResults==Integer.MAX_VALUE))
            {
               pstmt = con.prepareStatement(SEARCH_GROUP_NAME);
               pstmt.setString(1, query);
               rs = pstmt.executeQuery();
               while (rs.next()) {
                   groupNames.add(rs.getString(1));
               }
            } else {
               pstmt = DbConnectionManager.createScrollablePreparedStatement(con, SEARCH_GROUP_NAME);
               DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
               pstmt.setString(1, query);
               rs = pstmt.executeQuery();
               // Scroll to the start index.
               DbConnectionManager.scrollResultSet(rs, startIndex);
               int count = 0;
               while (rs.next() && count < numResults) {
                   groupNames.add(rs.getString(1));
                   count++;
               }	
            }
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return groupNames;
    }

    public boolean isSearchSupported() {
        return true;
    }

    public boolean isSharingSupported() {
        return true;
    }

    private Collection<JID> getMembers(String groupName, boolean adminsOnly) {
        List<JID> members = new ArrayList<JID>();
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            if (adminsOnly) {
                pstmt = con.prepareStatement(LOAD_ADMINS);
            }
            else {
                pstmt = con.prepareStatement(LOAD_MEMBERS);
            }
            pstmt.setString(1, groupName);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String user = rs.getString(1);
                JID userJID = null;
                if (user.indexOf('@') == -1) {
                    // Create JID of local user if JID does not match a component's JID
                    if (!server.matchesComponent(userJID)) {
                        userJID = server.createJID(user, null);
                    }
                }
                else {
                    userJID = new JID(user);
                }
                members.add(userJID);
            }
        }
        catch (SQLException e) {
            Log.error(e.getMessage(), e);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return members;
    }
    
    /**
     * Returns a custom {@link Map} that updates the database whenever
     * a property value is added, changed, or deleted.
     * 
     * @param name The target group
     * @return The properties for the given group
     */
    public Map<String,String> loadProperties(Group group) {
    	// custom map implementation persists group property changes
    	// whenever one of the standard mutator methods are called
    	String name = group.getName();
    	DefaultGroupPropertyMap<String,String> result = new DefaultGroupPropertyMap<String,String>(group);
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_PROPERTIES);
            pstmt.setString(1, name);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String key = rs.getString(1);
                String value = rs.getString(2);
                if (key != null) {
                    if (value == null) {
                        result.remove(key);
                        Log.warn("Deleted null property " + key + " for group: " + name);
                    } else {
                    	result.put(key, value, false); // skip persistence during load
                    }
                }
                else { // should not happen, but ...
                    Log.warn("Ignoring null property key for group: " + name);
                }
            }
        }
        catch (SQLException sqle) {
            Log.error(sqle.getMessage(), sqle);
        }
        finally {
            DbConnectionManager.closeConnection(rs, pstmt, con);
        }
        return result;
    }
	
}