package com.ffii.lioner.modules.user.service;

import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.stream.Collectors;

import org.apache.commons.collections4.Put;
import org.apache.commons.lang3.StringUtils;
import com.ffii.core.utils.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.MessageSource;
import org.springframework.context.NoSuchMessageException;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.client.RestTemplate;
import org.springframework.web.util.UriComponentsBuilder;

import com.ffii.lioner.modules.common.ErrorCodes;
import com.ffii.lioner.modules.common.PasswordRule;
import com.ffii.lioner.modules.common.SecurityUtils;
import com.ffii.lioner.modules.common.service.AuditLogService;
import com.ffii.lioner.modules.settings.service.SettingsService;
import com.ffii.lioner.modules.user.entity.User;
import com.ffii.lioner.modules.user.entity.UserPasswordHistory;
import com.ffii.lioner.modules.user.entity.UserRepository;
import com.ffii.lioner.modules.user.req.NewLionerUserReq;
import com.ffii.lioner.modules.user.req.NewPublicUserReq;
import com.ffii.lioner.modules.user.req.NewUserReq;
import com.ffii.lioner.modules.user.req.SearchUserReq;
import com.ffii.lioner.modules.user.req.UpdateUserReq;
import com.ffii.lioner.modules.user.service.pojo.UserRecord;
import com.ffii.core.exception.NotFoundException;
import com.ffii.core.exception.UnprocessableEntityException;
import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.JsonUtils;
import com.ffii.core.utils.Params;
import com.ffii.core.utils.PasswordUtils;

import jakarta.persistence.Table;

import org.springframework.security.core.Authentication;

@Service
public class UserService extends AbstractBaseEntityService<User, Long, UserRepository> {
	private static final String USER_AUTH_SQL = "SELECT a.authority"
			+ " FROM `user` u"
			+ " JOIN user_authority ua ON ua.userId = u.id"
			+ " JOIN authority a ON a.id = ua.authId"
			+ " WHERE u.deleted = 0"
			+ " AND u.id = :userId";
	private static final String UNION_SQL = " UNION ";
	private static final String GROUP_AUTH_SQL = "SELECT a.authority"
			+ " FROM `user` u"
			+ " JOIN user_group ug ON ug.userId = u.id"
			+ " JOIN `group` g ON g.deleted = 0 AND g.id = ug.groupId"
			+ " JOIN group_authority ga ON ga.groupId = g.id"
			+ " JOIN authority a ON a.id = ga.authId"
			+ " WHERE u.deleted = 0"
			+ " AND u.id = :userId";

	@Autowired
	private SettingsService settingsService;
	@Autowired
	private PasswordEncoder passwordEncoder;
	private AuditLogService auditLogService;
	private UserPasswordHistoryService userPasswordHistoryService;
	private MessageSource messageSource;
	private final RestTemplate restTemplate;

	//@Value("${emsd.webservice.urls}")
	//private String ldapWebServiceUrls;

	@Autowired
	UserRepository userRepository;

	public UserService(JdbcDao jdbcDao, UserRepository userRepository, AuditLogService auditLogService,
			UserPasswordHistoryService userPasswordHistoryService,
			MessageSource messageSource, RestTemplate restTemplate) {
		super(jdbcDao, userRepository);
		this.auditLogService = auditLogService;
		this.userPasswordHistoryService = userPasswordHistoryService;
		this.messageSource = messageSource;
		this.restTemplate = restTemplate;
	}

	public Optional<User> loadUserOptByUsername(String username) {
		return findByUsername(username)
				.map(user -> {
					Set<GrantedAuthority> auths = new LinkedHashSet<GrantedAuthority>();
					auths.add(new SimpleGrantedAuthority("ROLE_USER"));
					jdbcDao.queryForList(USER_AUTH_SQL + UNION_SQL + GROUP_AUTH_SQL, Map.of("userId", user.getId()))
							.forEach(item -> auths.add(new SimpleGrantedAuthority((String) item.get("authority"))));

					user.setAuthorities(auths);
					return user;
				});
	}

	public Optional<User> findByUsername(String username) {
		return userRepository.findByUsernameAndDeletedFalse(username);
	}

	public Map<String,Object> getAuditLogObject(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " u.id,    "
			+ " u.created,    "
			+ " u.createdBy AS createdById,    "
			+ " u1.name AS createdBy,    "
			+ " u.version,    "
			+ " u.modified,    "
			+ " u.modifiedBy AS modifiedById,    "
			+ " u2.name AS modifiedBy, "
			+ " u.deleted, "
			+ " u.username, "
			+ " u.locked, "
			+ " u.name , "
			+ " u.fullname, "
			+ " u.email, "
			+ " u.phone1 "
			+ " FROM `user` u   "
			+ " LEFT JOIN `user` u1 ON u1.id = u.createdBy     "
			+ " LEFT JOIN `user` u2 ON u2.id = u.modifiedBy      "
			+ " WHERE u.id = :id    "
		);

		return jdbcDao.queryForMap(sql.toString(), req).get();
	}

	public List<Integer> getUserAuthIds(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " ua.authId  "
			+ " FROM user_authority ua "
			+ " WHERE userId = :id; "
		);

		return jdbcDao.queryForInts(sql.toString(), req);
	}

	public List<String> getUserAuthNames(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " a.name "
			+ " FROM user_authority ua "
			+ " LEFT JOIN authority a ON ua.authId = a.id "
			+ " WHERE userId = :id; "
		);

		return jdbcDao.queryForStrings(sql.toString(), req);
	}

	public List<Integer> getUserGroupIds(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " ug.groupId  "
			+ " FROM user_group ug "
			+ " WHERE userId = :id; "
		);

		return jdbcDao.queryForInts(sql.toString(), req);
	}

	public List<String> getUserGroupNames(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " g.name  "
			+ " FROM user_group ug "
			+ " LEFT JOIN `group` g ON g.id = ug.groupId  "
			+ " WHERE userId = :id; "
		);
		return jdbcDao.queryForStrings(sql.toString(), req);
	}


	// @Transactional(rollbackFor = Exception.class)
	public List<UserRecord> search(SearchUserReq req) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " DISTINCT u.id,"
				+ " u.created,"
				+ " u.createdBy,"
				+ " u.version,"
				+ " u.modified,"
				+ " u.modifiedBy,"
				+ " u.username,"
				+ " u.locked,"
				+ " u.twoFactorEnabled,"
				+ " u.name,"
				+ " u.locale,"
				+ " u.firstname,"
				+ " u.lastname,"
				+ " u.title,"
				+ " u.department,"
				+ " u.email,"
				+ " u.phone1,"
				+ " u.phone2,"
				+ " u.remarks, "
				+ " u.fullname, "
				+ " u.post, "
				+ " ug.groupId "
				// + " sd.name as subDivision"
				+ " FROM `user` u"
				+ " left join user_group ug on u.id = ug.userId"
				// + " left join sub_division sd on u.subDivisionId = sd.id "
				+ " where u.deleted = false");

		if (req != null) {
			if (req.getId() != null)
				sql.append(" AND u.id = :id");

			if (req.getGroupId() != null) {
				sql.append(" AND ug.groupId = :groupId");
			}

			if (StringUtils.isNotBlank(req.getUsername())) {
				req.setUsername("%" + req.getUsername() + "%");
				sql.append(" AND u.username LIKE :username");
			}

			if (StringUtils.isNotBlank(req.getFullname())) {
				req.setFullname("%" + req.getFullname() + "%");
				sql.append(" AND u.fullname LIKE :fullname");
			}

			if (StringUtils.isNotBlank(req.getPost())) {
				req.setPost("%" + req.getPost() + "%");
				sql.append(" AND u.post LIKE :post");
			}

			if (req.getSubDivisionId() != null) {
				sql.append(" AND u.subDivisionId = :subDivisionId");
			}

			if (StringUtils.isNotBlank(req.getEmail())) {
				req.setEmail("%" + req.getEmail() + "%");
				sql.append(" AND u.email LIKE :email");
			}

			if (StringUtils.isNotBlank(req.getPhone())) {
				req.setPhone("%" + req.getPhone() + "%");
				sql.append(" AND u.phone1 LIKE :phone");
			}

			if (req.getLocked() != null) {
				sql.append(" AND u.locked = :locked");
			}

		}
		sql.append(" ORDER BY u.username asc");

		if (req != null) {
			if (req.getStart() != null && req.getLimit() != null)
				sql.append(" LIMIT :start, :limit");
		}

		return jdbcDao.queryForList(sql.toString(), req, UserRecord.class);
	}

	public Integer getUserGroupId(long id) {
		return jdbcDao.queryForInt(
				"SELECT"
						+ " MAX(ug.groupId) AS groupId"
						+ " FROM user_group ug"
						+ " WHERE ug.userId = :id"
						+ " GROUP BY ug.userId",
				Map.of(Params.ID, id));
	}

	public List<Integer> listUserAuthId(long id) {
		return jdbcDao.queryForInts(
				"SELECT"
						+ " ua.authId"
						+ " FROM user_authority ua"
						+ " WHERE ua.userId = :id",
				Map.of(Params.ID, id));
	}

	public List<Integer> listUserGroupId(long id) {
		return jdbcDao.queryForInts(
				"SELECT"
						+ " gu.groupId"
						+ " FROM user_group gu"
						+ " INNER JOIN `group` g ON g.deleted = FALSE AND g.id = gu.groupId"
						+ " WHERE gu.userId = :id",
				Map.of(Params.ID, id));
	}

	private User saveOrUpdate(User instance, UpdateUserReq req) {

		if (instance.getId() == null) {
			req.setLocked(false);
		}
		BeanUtils.copyProperties(req, instance);
		instance = save(instance);
		// long id = instance.getId();

		// List<Map<String, Integer>> groupBatchInsertValues =
		// req.getAddGroupIds().stream()
		// .map(groupId -> Map.of("userId", (int) id, "groupId", groupId))
		// .collect(Collectors.toList());
		// List<Map<String, Integer>> groupBatchDeleteValues =
		// req.getRemoveGroupIds().stream()
		// .map(groupId -> Map.of("userId", (int) id, "groupId", groupId))
		// .collect(Collectors.toList());

		// if (!groupBatchInsertValues.isEmpty()) {
		// jdbcDao.batchUpdate(
		// "INSERT IGNORE INTO user_group (groupId,userId)"
		// + " VALUES (:groupId, :userId)",
		// groupBatchInsertValues);
		// }
		// if (!groupBatchDeleteValues.isEmpty()) {
		// jdbcDao.batchUpdate(
		// "DELETE FROM user_group"
		// + " WHERE groupId = :groupId AND userId = :userId",
		// groupBatchDeleteValues);
		// }

		// List<Map<String, Integer>> authBatchInsertValues =
		// req.getAddAuthIds().stream()
		// .map(authId -> Map.of("userId", (int)id, "authId", authId))
		// .collect(Collectors.toList());
		// List<Map<String, Integer>> authBatchDeleteValues =
		// req.getRemoveAuthIds().stream()
		// .map(authId -> Map.of("userId", (int)id, "authId", authId))
		// .collect(Collectors.toList());
		// if (!authBatchInsertValues.isEmpty()) {
		// jdbcDao.batchUpdate(
		// "INSERT IGNORE INTO user_authority (userId, authId)"
		// + " VALUES (:userId, :authId)",
		// authBatchInsertValues);
		// }

		// if (!authBatchDeleteValues.isEmpty()) {
		// jdbcDao.batchUpdate(
		// "DELETE FROM user_authority"
		// + " WHERE userId = :userId AND authId = :authId",
		// authBatchDeleteValues);
		// }
		return instance;
	}

	@Transactional(rollbackFor = Exception.class)
	public void updateRecord(Integer id, UpdateUserReq req) {
		saveOrUpdate(
				find(id.longValue()).orElseThrow(NotFoundException::new),
				req);
	}

	private User saveOrUpdateLIONER(User instance, NewLionerUserReq req) {

		if (instance.getId() == null) {
			req.setLocked(false);
		}
		BeanUtils.copyProperties(req, instance);
		instance = saveAndFlush(instance);
		logger.info("##### updating updateRecord " + req.getConsultantIds());

		updateConsultantAssignments(instance.getId(), req.getConsultantIds());
		return instance;
	}

	@Transactional(rollbackFor = Exception.class)
	public User newRecord(NewUserReq req) throws UnsupportedEncodingException {
		if (findByUsername(req.getUsername()).isPresent()) {
			throw new UnprocessableEntityException(ErrorCodes.USERNAME_NOT_AVAILABLE);
		}

		// String randomPassword = PasswordUtils.genPwd(new
		// PasswordRule(settingsService));
		// String pwdHash = passwordEncoder.encode(randomPassword);

		User instance = new User();
		// instance.setPassword(pwdHash);
		instance = saveOrUpdate(instance, req);
		return instance;
	}

	@Transactional(rollbackFor = Exception.class)
	public User newPublicUserRecord(NewPublicUserReq req) throws UnsupportedEncodingException {
		if (findByUsername(req.getUsername()).isPresent()) {
			throw new UnprocessableEntityException(ErrorCodes.USERNAME_NOT_AVAILABLE);
		}

		String submittedPassword = req.getPassword();
		String pwdHash = passwordEncoder.encode(submittedPassword);
		req.setPassword(pwdHash);
		User instance = new User();

		instance = saveOrUpdate(instance, req);
		return instance;
	}

	@Transactional(rollbackFor = Exception.class)
	public User newUserRecord(NewLionerUserReq req)
			throws UnsupportedEncodingException, NoSuchMessageException, ParseException, IllegalAccessException {
		User instance = new User();

		if (req.getId() > 0) {
			instance = find(req.getId()).get();
		} else {
			if (findByUsername(req.getUsername()).isPresent()) {
				throw new UnprocessableEntityException(ErrorCodes.USERNAME_NOT_AVAILABLE);
			}
			instance = new User();
		}

		if (req.getPassword() != null && req.getPassword() != "" && req.getPassword().length() > 1) {
			String submittedPassword = req.getPassword();

			PasswordRule rule = new PasswordRule(settingsService);
			if (!PasswordUtils.checkPwd(req.getPassword(), rule, req.getUsername())) {
				throw new UnprocessableEntityException(rule.getWrongMsg());
			}
			if (req.getId() > 0) {
				List<String> passHists = userPasswordHistoryService.getPasswordHistory(req.getId());
				passHists.stream().forEach(oldPass -> {
					if (passwordEncoder.matches(req.getPassword(), oldPass)) {
						throw new UnprocessableEntityException("This password is already in the password history.");
					}
				});
			}

			String pwdHash = passwordEncoder.encode(submittedPassword);
			req.setPassword(pwdHash);

		} else {
			req.setPassword(instance.getPassword());
		}

		// =====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName + " WHERE id = :id");
		Map<String,Object> oldUserObject = new HashMap<String,Object>();
		Map<String,Object> newUserObject = new HashMap<String,Object>();

		StringBuilder sql2 = new StringBuilder("SELECT authId FROM user_authority WHERE userId = :id");

		StringBuilder sql3 = new StringBuilder("SELECT groupId FROM user_group WHERE userId = :id");

		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("user_auth", getUserAuthNames(input));
			logData.put("user_auth_id", getUserAuthIds(input));
			logData.put("user_group", getUserGroupNames(input));
			logData.put("user_group_id", getUserGroupIds(input));
			oldUserObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		instance = saveOrUpdateLIONER(instance, req);
		Long id = instance.getId();

		if (req.getPassword() != null && req.getPassword() != "" && req.getPassword().length() > 1) {
			// if password updated
			UserPasswordHistory record = new UserPasswordHistory();
			record.setUserId(id);
			record.setPassword(req.getPassword());
			record.setDate(LocalDateTime.now());
			record = userPasswordHistoryService.save(record);
		}

		Map<String, Long> userGroupMap = new HashMap<>();
		userGroupMap.put("userId", instance.getId());
		userGroupMap.put("groupId", req.getUserGroupId());
		if (req.getUserGroupId() > 0) {	// The group id is updated
			jdbcDao.executeUpdate(
				"DELETE FROM user_group"
				+ " WHERE userId = :userId;",
			userGroupMap
			);

			jdbcDao.executeUpdate(
				"INSERT IGNORE INTO user_group (groupId,userId)"
				+ " VALUES (:groupId, :userId);",
			userGroupMap
			);
		}

		// List<Map<String, Long>> authBatchInsertValues = req.getAddAuthIds().stream()
		// 		.map(authId -> Map.of("userId", id, "authId", authId))
		// 		.collect(Collectors.toList());
		// List<Map<String, Long>> authBatchDeleteValues = req.getRemoveAuthIds().stream()
		// 		.map(authId -> Map.of("userId", id, "authId", authId))
		// 		.collect(Collectors.toList());

		// if (!authBatchInsertValues.isEmpty()) {
		// 	jdbcDao.batchUpdate(
		// 			"INSERT IGNORE INTO user_authority (userId, authId)"
		// 					+ " VALUES (:userId, :authId)",
		// 			authBatchInsertValues);
		// }
		// if (!authBatchDeleteValues.isEmpty()) {
		// 	jdbcDao.batchUpdate(
		// 			"DELETE FROM user_authority"
		// 					+ " WHERE userId = :userId AND authId = :authId",
		// 			authBatchDeleteValues);
		// }

		// List<Map<String, Long>> userBatchInsertValues = req.getAddGroupIds().stream()
		// 		.map(groupId -> Map.of("userId", id, "groupId", groupId))
		// 		.collect(Collectors.toList());
		// List<Map<String, Long>> userBatchDeleteValues = req.getRemoveGroupIds().stream()
		// 		.map(groupId -> Map.of("userId", id, "groupId", groupId))
		// 		.collect(Collectors.toList());

		// if (!userBatchInsertValues.isEmpty()) {
		// 	jdbcDao.batchUpdate(
		// 			"INSERT IGNORE INTO user_group (groupId,userId)"
		// 					+ " VALUES (:groupId, :userId)",
		// 			userBatchInsertValues);
		// }
		// if (!userBatchDeleteValues.isEmpty()) {
		// 	jdbcDao.batchUpdate(
		// 			"DELETE FROM user_group"
		// 					+ " WHERE groupId = :groupId AND userId = :userId",
		// 			userBatchDeleteValues);
		// }

		// =====GET NEW AUDIT LOG=====//
		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("user_auth", getUserAuthNames(input));
			logData.put("user_auth_id", getUserAuthIds(input));
			logData.put("user_group", getUserGroupNames(input));
			logData.put("user_group_id", getUserGroupIds(input));
			newUserObject = logData;
		}


		if(auditLogService.compareMaps(newUserObject,oldUserObject).size() != 0 ||
			auditLogService.compareMaps(oldUserObject,newUserObject).size() != 0
		){
			auditLogService.save(
				tableName,
				instance.getId(),
				instance.getName(),
				SecurityUtils.getUser() != null ? SecurityUtils.getUser().get().getId() : null,
				new Date(),
				JsonUtils.toJsonString(auditLogService.compareMaps(newUserObject,oldUserObject)),
				JsonUtils.toJsonString(auditLogService.compareMaps(oldUserObject,newUserObject)));
		}
		// =====GET NEW AUDIT LOG=====//
		return instance;
	}


	@Transactional(rollbackFor = Exception.class)
	public void updateRecord(long id, UpdateUserReq req) {
		saveOrUpdate(
				find(id).orElseThrow(NotFoundException::new),
				req);
	}

	@Transactional(rollbackFor = Exception.class)
	public String resetPassword(long id) throws UnsupportedEncodingException {
		User instance = find(id).orElseThrow(NotFoundException::new);
		// String randomPassword = PasswordUtils.genPwd(new
		// PasswordRule(settingsService));

		// instance.setPassword(passwordEncoder.encode(randomPassword));
		instance = save(instance);
		return null;
	}

	@Transactional(rollbackFor = Exception.class)
	public List<Map<String, Object>> listUserAuth(Map<String, Object> args) {

		StringBuilder sql = new StringBuilder("SELECT"
				+ "	a.id, "
				+ " a.module,"
				+ " a.authority,"
				+ " a.name,"
				+ " a.description, ");
		if (args.containsKey("userId"))
			sql.append(" EXISTS(SELECT 1 FROM user_authority WHERE authority = a.authority AND userId = :userId) AS v");
		else
			sql.append(" 0 AS v");
		sql.append(" FROM authority a"
				+ " ORDER BY a.module, a.name");

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

	public List<Map<String, Object>> searchForCombo(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " u.id, "
				+ " u.id as `key`, "
				+ " u.name, "
				+ " u.name as label, "
				+ " sd.name as subDivisionName "
				+ " FROM `user` u  "
				+ " LEFT JOIN sub_division sd on u.subDivisionId = sd.id  "
				+ " WHERE u.deleted = FALSE"
				);

		if (args != null) {
			if (args.containsKey(Params.QUERY))
				sql.append(" AND (u.name LIKE :query)");
			if (args.containsKey(Params.ID))
				sql.append(" AND u.id = :id");
		}

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

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

	public List<Map<String, Object>> searchForAuditCombo(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " u.id, "
				+ " u.id as `key`, "
				+ " u.name, "
				+ " u.name as label "
				+ " FROM `user` u  "
				);

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

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

	public List<Map<String, Object>> listLoginLog(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " ull.username, "
				+ " ull.ipAddr, "
				+ " ull.loginTime, "
				+ " ull.success  "
				+ " FROM user_login_log ull  "
				+ " WHERE TRUE ");

		if (args != null) {
			if (args.containsKey("username"))
				sql.append(" AND (ull.username LIKE :username)");
			if (args.containsKey("ipAddr"))
				sql.append(" AND ull.ipAddr LIKE :ipAddr");

			if (args.containsKey("fromDate"))
				sql.append(" AND DATE(ull.loginTime) >= :fromDate");
			if (args.containsKey("toDate"))
				sql.append(" AND DATE(ull.loginTime) < :toDate");

			if (args.containsKey("success"))
				sql.append(" AND ull.success = :success");
		}

		sql.append(" ORDER BY ull.loginTime DESC");

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

	public Map<String, Object> softDelete(User user) {
		User instance = user;

		// =====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		StringBuilder sql = new StringBuilder("SELECT * FROM " + tableName + " WHERE id = :id");
		Map<String,Object> oldUserObject = new HashMap<String,Object>();
		Map<String,Object> newUserObject = new HashMap<String,Object>();

		StringBuilder sql2 = new StringBuilder("SELECT authId FROM user_authority WHERE userId = :id");

		StringBuilder sql3 = new StringBuilder("SELECT groupId FROM user_group WHERE userId = :id");

		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("user_auth", getUserAuthNames(input));
			logData.put("user_auth_id", getUserAuthIds(input));
			logData.put("user_group", getUserGroupNames(input));
			logData.put("user_group_id", getUserGroupIds(input));
			oldUserObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//
		this.markDelete(instance);

		// =====GET NEW AUDIT LOG=====//
		if (instance != null && instance.getId() != null && instance.getId() > 0) {
			Map<String, Object> input = Map.of("id", instance.getId());
			Map<String, Object> logData = getAuditLogObject(input);
			logData.put("user_auth", getUserAuthNames(input));
			logData.put("user_auth_id", getUserAuthIds(input));
			logData.put("user_group", getUserGroupNames(input));
			logData.put("user_group_id", getUserGroupIds(input));
			newUserObject = logData;
		}

		if(auditLogService.compareMaps(newUserObject,oldUserObject).size() != 0 ||
			auditLogService.compareMaps(oldUserObject,newUserObject).size() != 0
		){
			auditLogService.save(
				tableName,
				instance.getId(),
				instance.getName(),
				SecurityUtils.getUser() != null ? SecurityUtils.getUser().get().getId() : null,
				new Date(),
				JsonUtils.toJsonString(auditLogService.compareMaps(newUserObject,oldUserObject)),
				JsonUtils.toJsonString(auditLogService.compareMaps(oldUserObject,newUserObject)));
		}
		// =====GET NEW AUDIT LOG=====//

		return Map.of(
				"id", instance.getId());
	}

	public boolean isNameTaken(String name, Long id) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " Count(u.id) "
				+ " FROM `user` u  "
				+ " WHERE u.deleted = FALSE  "
				+ " AND u.name = :name "
				+ " AND u.id != :id ");

		return jdbcDao.queryForBoolean(sql.toString(), Map.of("name", name, "id", id));
	}

	public List<User> listUserBySubDivision(Integer subDivisionId) {
		return jdbcDao.queryForList("SELECT"
				+ " u.* "
				+ " FROM `user` u  "
				+ " WHERE u.deleted = FALSE  "
				+ " AND u.locked = FALSE "
				+ " AND u.subDivisionId = :id	 "
				+ " AND u.reminderFlag = TRUE  ", Map.of(Params.ID, subDivisionId), User.class);
	}

	public Integer getLastChangePasswordDate(Long id) {
		StringBuilder sql = new StringBuilder("SELECT"
			+ " TIMESTAMPDIFF(DAY,uph.`date`, NOW())  "
			+ " FROM user_password_history uph  "
			+ " WHERE uph.userId = :userId "
			+ " ORDER BY uph.id DESC "
			+ " LIMIT 1 "
		);

		return jdbcDao.queryForInt(sql.toString(), Map.of("userId", id));
	}

	public User getCurrentUser(Authentication authentication) {
		if (authentication == null || !authentication.isAuthenticated()) {
			throw new IllegalStateException("No authenticated user");
		}
		String username = authentication.getName();
		return findByUsername(username)
				.orElseThrow(() -> new RuntimeException("Authenticated user not found: " + username));
	}

	public User getUserByUsername(String username) {
		return findByUsername(username)
				.orElseThrow(() -> new NotFoundException());
	}

	@Transactional
	public void updateConsultantAssignments(Long userId, List<Long> consultantIds) {
		// 1. Remove old assignments
		Map<String, Object> args = Map.of(
					"userId", userId);

		jdbcDao.executeUpdate("DELETE FROM user_consultant WHERE userId = :userId", args);
		
		// 2. Add new assignments
		if (consultantIds != null) {
			for (Long cId : consultantIds) {
				args = Map.of(
					"userId", userId,
					"cId", cId);
				
				jdbcDao.executeUpdate("INSERT INTO user_consultant (userId, consultantId) VALUES (:userId, :cId)", 
					args);
			}
		}
	}

	public List<Long> listUserConsultantId(long userId) {
		String sql = "SELECT consultantId FROM user_consultant WHERE userId = :userId";
		List<Map<String, Object>> rows = jdbcDao.queryForList(sql, Map.of("userId", userId));

		return rows.stream()
				.map(row -> (Long) row.get("consultantId"))
				.collect(Collectors.toList());
	}

}
