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

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

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

import com.ffii.core.exception.InternalServerErrorException;
import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.BeanUtils;
import com.ffii.core.utils.JsonUtils;
import com.ffii.core.utils.Params;
import com.ffii.fhsmsc.modules.common.SecurityUtils;
import com.ffii.fhsmsc.modules.common.service.AuditLogService;
import com.ffii.fhsmsc.modules.user.entity.Group;
import com.ffii.fhsmsc.modules.user.entity.GroupRepository;
import com.ffii.fhsmsc.modules.user.req.SaveGroupReq;

import jakarta.persistence.Table;
import jakarta.validation.Valid;


@Service
public class GroupService extends AbstractBaseEntityService<Group, Long, GroupRepository> {

	@Autowired
	private AuditLogService auditLogService;

	public GroupService(JdbcDao jdbcDao, GroupRepository repository) {
		super(jdbcDao, repository);
	}

	public List<Map<String, Object>> search(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " g.*"
				+ " FROM `group` g"
				+ " WHERE g.deleted = FALSE");

		if (args != null) {
			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");
			if (args.containsKey("description")) sql.append(" AND g.description LIKE :description");
		}

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

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

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

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

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

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

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

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

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

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

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

	public List<Map<String, Object>> searchForCombo(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " g.id,"
				+ " g.name"
				+ " FROM `group` g"
				+ " WHERE g.deleted = FALSE");

		if (args != null) {
			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 asc");

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

	@Transactional(rollbackFor = Exception.class)
	public void delete(Group instance) {
		// =====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		Map<String,Object> oldValueObject = new HashMap<String,Object>();
		Map<String,Object> newValueObject = new HashMap<String,Object>();

		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("group_auth", getUserAuthNames(input));
			logData.put("group_auth_id", getUserAuthIds(input));
			logData.put("group_user", getUserGroupNames(input));
			logData.put("group_user_id", getUserGroupIds(input));
			oldValueObject = logData;
		}
		
		// =====GET OLD AUDIT LOG=====//

		Map<String, Object> args = Map.of("groupId", instance.getId());
		jdbcDao.executeUpdate("DELETE FROM user_group WHERE groupId = :groupId;", args);
		jdbcDao.executeUpdate("DELETE FROM group_authority WHERE groupId = :groupId;", args);
		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("group_auth", getUserAuthNames(input));
			logData.put("group_auth_id", getUserAuthIds(input));
			logData.put("group_user", getUserGroupNames(input));
			logData.put("group_user_id", getUserGroupIds(input));
			newValueObject = logData;
		}


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

	@Transactional(rollbackFor = Exception.class)
	public Group saveOrUpdate(@Valid SaveGroupReq req ) {
		Group instance;

		if (req.getId() != null) {
			instance = find(req.getId()).orElseThrow(InternalServerErrorException::new);
		} else {
			instance = new Group();
		}
		BeanUtils.copyProperties(req, instance);

		//=====GET OLD AUDIT LOG=====//
		String tableName = instance.getClass().getAnnotation(Table.class).name();
		Map<String,Object> oldValueObject = new HashMap<String,Object>();
		Map<String,Object> newValueObject = new HashMap<String,Object>();

		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("group_auth", getUserAuthNames(input));
			logData.put("group_auth_id", getUserAuthIds(input));
			logData.put("group_user", getUserGroupNames(input));
			logData.put("group_user_id", getUserGroupIds(input));
			oldValueObject = logData;
		}
		
		//=====GET OLD AUDIT LOG=====//

		instance = saveAndFlush(instance);
		Long id = instance.getId();

		List<Map<String, Long>> userBatchInsertValues = req.getAddUserIds().stream()
				.map(userId -> Map.of("groupId", id, "userId", userId))
				.collect(Collectors.toList());
		List<Map<String, Long>> userBatchDeleteValues = req.getRemoveUserIds().stream()
				.map(userId -> Map.of("groupId", id, "userId", userId))
				.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);
		}

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

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

		//=====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("group_auth", getUserAuthNames(input));
			logData.put("group_auth_id", getUserAuthIds(input));
			logData.put("group_user", getUserGroupNames(input));
			logData.put("group_user_id", getUserGroupIds(input));
			newValueObject = logData;
		}


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

	public List<Integer> listGroupAuthId(Long id) {
		return jdbcDao.queryForInts(
				"SELECT"
						+ " ga.authId"
						+ " FROM group_authority ga"
						+ " WHERE ga.groupId = :id",
				Map.of(Params.ID, id));
	}

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

	public List<Map<String,Object>> listGroupMember(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " u.id, "
			+ " u.name "
			+ " FROM user_group ug "
			+ " LEFT JOIN `user` u on u.id = ug.userId  "
			+ " WHERE ug.groupId = :id "
			+ " AND u.deleted = FALSE "
		);

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

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

	@Transactional(rollbackFor = Exception.class)
	public List<Map<String, Object>> listGroupAuth(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 group_authority WHERE authority = a.authority AND groupId = :groupId) 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 boolean isNameTaken(String name, Long id) {
        StringBuilder sql = new StringBuilder("SELECT"
            + " Count(g.id) "
            + " FROM `group` g  "
            + " WHERE g.deleted =FALSE  "
            + " AND g.name = :name "
			+ " AND g.id != :id "
        );
        
        return jdbcDao.queryForBoolean(sql.toString(), Map.of("name", name, "id", id));
    }

}
