/*******************************************************************************
 * 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.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Table;

import com.ffii.core.User;
import com.ffii.core.dao.JdbcDao;
import com.ffii.core.utils.DataBindUtils;
import com.ffii.core.utils.MapUtils;
import com.ffii.core.utils.Params;
import com.ffii.core.web.AbstractService;
import com.ffii.tbms.user.dao.UserDao;

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

@Service
public class UserService extends AbstractService {

	@Autowired
	private JdbcDao jdbcDao;

	@Autowired
	private UserDao userDao;



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

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public Integer saveOrUpdate(User instance) {
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName + " WHERE id = :id");

		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> map = jdbcDao.queryForMap(sql.toString(), MapUtils.toHashMap("id", instance.getId()));
			map.remove("password");
		}

		Integer id = userDao.saveOrUpdate(instance);

		userDao.flush();

		Map<String, Object> map = jdbcDao.queryForMap(sql.toString(), MapUtils.toHashMap("id", id));
		map.remove("password");

		return id;
	}

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

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> findUserMap(Map<String, Object> args) {
		//@formatter:off
		StringBuilder sql = new StringBuilder("SELECT"
				+ " u.id,"
				+ " u.versionId,"
				+ " u.username,"
				+ " u.password,"
				+ " u.expired,"
				+ " u.locked,"
				+ " u.locale,"
				+ " u.fullname,"
				+ " u.firstname,"
				+ " u.lastname,"
				+ " u.deptId,"
				+ " u.title,"
				+ " u.email,"
				+ " u.phone1,"
				+ " u.phone2,"
				+ " u.remarks,"
				+ " u.companyId,"
				+ " u.customerId"
				+ " FROM users u"
				+ " WHERE u.deleted = 0");
		//@formatter:on

		if (args.containsKey("id"))
			sql.append(" AND u.id = :id");

		if (args.containsKey("username"))
			sql.append(" AND u.username = :username");

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

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public List<Map<String, Object>> searchForCombo(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder(
				"SELECT"
						+ " u.id,"
						+ " u.companyId,"
						+ " u.department,"
						+ " u.username,"
						+ " u.fullname"
						+ " FROM users u"
						+ " WHERE u.deleted = 0");
		//@formatter:on
		if (args.containsKey(Params.QUERY))
			sql.append(" AND (u.username LIKE :query OR u.fullname LIKE :query)");

		if (args.containsKey("senderId"))
			sql.append(" AND u.id <> :senderId");

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

		if (args.containsKey("companyIds"))
			sql.append(" AND u.companyId IN (:companyIds)");

		if (args.containsKey("authority"))
			sql.append(
					" AND (EXISTS(SELECT 1 FROM users_authorities WHERE userId = id AND authority = :authority) OR EXISTS(SELECT 1 FROM groups_users gu INNER JOIN groups_authorities ga ON ga.groupId = gu.groupId AND ga.authority = :authority WHERE gu.userId = id))");

		sql.append(" ORDER BY u.fullname");

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

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

		StringBuilder sql = new StringBuilder("SELECT" 
			+ " u.id," 
			+ " u.modified," 
			+ " u.username," 
			+ " u.fullname,"
			+ " u.firstname," 
			+ " u.lastname," 
			+ " u.department," 
			+ " u.deptId," 
			+ " u.title," 
			+ " u.email,"
			+ " u.phone1," 
			+ " u.phone2," 
			+ " u.remarks," 
			+ " u.companyId," 
			+ " gu.groupId" 
			+ " FROM users u"
			+ " LEFT JOIN groups_users gu on u.id = gu.userId" 
			+ " WHERE u.deleted = 0");

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

		if(args.containsKey("sysGroupId")){
			sql.append(" AND u.sysGroupId = :sysGroupId ");
		}
		if (args.containsKey("groupId"))
			sql.append(" AND gu.groupId = :groupId");
		if (args.containsKey("username"))
			sql.append(" AND u.username LIKE :username");
		if (args.containsKey("fullname"))
			sql.append(" AND u.fullname LIKE :fullname");

		sql.append(" ORDER BY u.fullname");

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

	@Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> queryForMap(String username) {
		String sql = "SELECT" + " *" + " FROM users" + " WHERE username = :username";

		return jdbcDao.queryForMap(sql, MapUtils.toHashMap("username", username));
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public boolean lockUser(String username, boolean lock) {
		return jdbcDao.executeUpdate("UPDATE users SET locked = :lock WHERE username = :username",
				MapUtils.toHashMap("username", username, "lock", lock)) > 0;
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public boolean changeLocale(int id, String locale) {
		return jdbcDao.executeUpdate("UPDATE users SET locale = :locale WHERE id = :id",
				MapUtils.toHashMap("locale", locale, "id", id)) > 0;
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = false)
	public boolean clone(User instance, String username, String password) {
		User newUser = new User();
		BeanUtils.copyProperties(instance, newUser, DataBindUtils.coreFields);
		newUser.setUsername(username);
		newUser.setPassword(password);
		int newId = saveOrUpdate(newUser);
		Map<String, Object> args = MapUtils.toHashMap("sourceId", instance.getId(), "targetId", newId);
		jdbcDao.executeUpdate("INSERT INTO users_authorities (userId, authority)" + " SELECT :targetId , authority"
				+ " FROM users_authorities" + " WHERE userId = :sourceId", args);

		jdbcDao.executeUpdate("INSERT INTO groups_users (userId, groupId)" + " SELECT :targetId , groupId"
				+ " FROM groups_users" + " WHERE userId = :sourceId", args);
		return true;
	}

	@Transactional(isolation = Isolation.SERIALIZABLE, rollbackFor = Exception.class, readOnly = true)
	public Map<String, Object> getSysGroup(int sysGroupId) {
		Map<String, Object> args = new HashMap<>();
		args.put("id", sysGroupId);
		//@formatter:off
		StringBuilder sql = new StringBuilder(" SELECT "
				+ " * "
				+ " FROM sys_groups sg"
				+ " WHERE sg.deleted = 0");
		//@formatter:on

		if (args.containsKey("id"))
			sql.append(" AND sg.id = :id");

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


}