/*******************************************************************************
 * Copyright 2Fi Business Solutions Ltd.
 *
 * This code is copyrighted. Under no circumstances should any party, people,
 * or organization should redistribute any portions of this code in any form,
 * either verbatim or through electronic media, to any third parties, unless
 * under explicit written permission by 2Fi Business Solutions Ltd.
 ******************************************************************************/
package com.ffii.tbms.user.service;

import java.util.List;
import java.util.Map;

import com.ffii.core.dao.JdbcDao;
import com.ffii.core.utils.MapUtils;
import com.ffii.core.utils.Params;
import com.ffii.core.web.AbstractService;
import com.ffii.tbms.user.Group;
import com.ffii.tbms.user.dao.GroupDao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author Patrick
 */
@Service
public class GroupService extends AbstractService {

	@Autowired
	private JdbcDao jdbcDao;

	@Autowired
	private GroupDao groupDao;

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdate(Group instance) {
		return groupDao.saveOrUpdate(instance);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Group find(Integer id) {
		return groupDao.find(id);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer delete(Group instance) {
		if (instance.getId() != null) {
			StringBuilder delGUSQL = new StringBuilder("DELETE FROM groups_users WHERE groupId = :groupId ;");
			StringBuilder delGASQL = new StringBuilder("DELETE FROM groups_authorities WHERE groupId = :groupId ;");
			Map<String, Object> args = MapUtils.toHashMap("groupId", instance.getId());

			jdbcDao.executeUpdate(delGUSQL.toString(), args);
			jdbcDao.executeUpdate(delGASQL.toString(), args);
		}
		instance.setDeleted(true);
		return saveOrUpdate(instance);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> search(Map<String, Object> args) {
		
		StringBuilder sql = new StringBuilder("SELECT"
				+ " g.*");

		if (args.containsKey("userId")) sql.append(", EXISTS(SELECT 1 FROM groups_users WHERE groupId = g.id AND userId = :userId) AS v");

		sql.append(" FROM groups g");

		if (args.containsKey(Params.QUERY)) sql.append(" AND (g.name LIKE :query)");

		if (args.containsKey(Params.ID)) sql.append(" AND g.id = :id");

		if (args.containsKey(Params.NAME)) sql.append(" AND g.name LIKE :name");
		sql.append(" WHERE g.deleted = 0");


		sql.append(" ORDER BY g.name");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public Map<String, Object> queryForMap(Integer id) {
		String sql = "SELECT * FROM groups WHERE id = :id";

		return jdbcDao.queryForMap(sql, MapUtils.toHashMap(Params.ID, id));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchForCombo(Map<String, Object> args) {

		StringBuilder sql = new StringBuilder("SELECT"
				+ " g.id,"
				+ " g.name AS groupName"
				+ " FROM groups g"
				+ " WHERE g.deleted = 0");

		if (args.containsKey(Params.QUERY)) sql.append(" AND (g.name LIKE :query)");

		if (args.containsKey(Params.ID)) sql.append(" AND g.id = :id");

		sql.append(" ORDER BY g.name");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	/* group's user */
	@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> listGroupUser(Integer groupId) {

		StringBuilder sql = new StringBuilder("SELECT"
				+ " u.id,"
				+ " u.username,"
				+ " u.fullname"
				+ " FROM groups_users gu"
				+ " INNER JOIN users u ON gu.userId = u.id"
				+ " WHERE u.deleted = 0"
				+ " AND gu.groupId = :groupId"
				+ " ORDER BY u.username");

		return jdbcDao.queryForList(sql.toString(), MapUtils.toHashMap("groupId", groupId));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public int[] addGroupUser(Map<String, Object>[] batchValues) {

		String sql = "INSERT IGNORE INTO groups_users"
				+ " (groupId,userId)"
				+ " VALUES"
				+ " (:groupId, :userId)";

		return jdbcDao.executeBatchUpdate(sql, batchValues);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public int[] removeGroupUser(Map<String, Object>[] batchValues) {

		String sql = "DELETE FROM groups_users"
				+ " WHERE groupId = :groupId AND userId = :userId";

		return jdbcDao.executeBatchUpdate(sql, batchValues);
	}

	/* group's auth */
	@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> listGroupAuth(int groupId) {

		Map<String, Object> args = MapUtils.toHashMap("groupId", groupId);
		
		StringBuilder sql = new StringBuilder("SELECT"
				+ " a.module,"
				+ " a.authority,"
				+ " a.name, "
				+ " a.description, "
				+ " EXISTS(SELECT 1 FROM groups_authorities WHERE authority = a.authority AND groupId = :groupId) AS v"
				+ " FROM authorities a"
				+ " ORDER BY a.authority");
		

		return jdbcDao.queryForList(sql.toString(), args);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public int[] addGroupAuth(Map<String, Object>[] batchValues) {

		String sql = "INSERT IGNORE INTO groups_authorities"
				+ " (groupId, authority)"
				+ " VALUES"
				+ " (:groupId, :authority)";

		return jdbcDao.executeBatchUpdate(sql, batchValues);
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public int[] removeGroupAuth(Map<String, Object>[] batchValues) {

		String sql = "DELETE FROM groups_authorities"
				+ " WHERE groupId = :groupId AND authority = :authority";

		return jdbcDao.executeBatchUpdate(sql, batchValues);
	}

}
