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

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

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.ffii.lioner.modules.lioner.entity.Application;
import com.ffii.lioner.modules.lioner.entity.ApplicationRepository;
import com.ffii.lioner.modules.lioner.entity.ImpApplication;
import com.ffii.lioner.modules.common.SecurityUtils;
import com.ffii.lioner.modules.common.service.AuditLogService;
import com.ffii.lioner.modules.master.entity.SubDivision;
import com.ffii.core.exception.UnprocessableEntityException;
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 jakarta.persistence.Table;

@Service
public class ApplicationService extends AbstractBaseEntityService<Application, Long, ApplicationRepository> {

	private FileService fileService;
    private FileRefService fileRefService;
	private AuditLogService auditLogService;

	public ApplicationService(JdbcDao jdbcDao, ApplicationRepository repository, FileService fileService, FileRefService fileRefService, AuditLogService auditLogService) {
		super(jdbcDao, repository);
		this.fileService = fileService;
		this.fileRefService = fileRefService;
		this.auditLogService = auditLogService;
	}

	public Optional<Map<String, Object>> getApplicationDetail(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
				+ " e.id as eventId,"
				+ " a.id, "
				+ " a.eventId, "
				+ " e.name as eventName, "
				+ " a.name, "
				+ " a.description, "
				+ " a.status, "
				+ " a.responsibleOfficer "
				+ " FROM application a  "
				+ " LEFT JOIN event e ON e.id = a.eventId  "
				+ " WHERE a.deleted = FALSE "
				+ " AND a.id = :id; "
				);

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

	public List<Map<String,Object>> list(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT a.id, "
			+ " e.name AS eventName, "
			+ " a.name AS applicationName, "
			+ " a.status, "
			+ " a.responsibleOfficer, "
			+ " a.description "
			+ " FROM application a "
			+ " LEFT JOIN event e ON e.id = a.eventId "
			+ " LEFT JOIN application_tag at2 ON at2.applicationId = a.id AND at2.deleted = FALSE"
			+ " LEFT JOIN application_sub_division asd ON asd.applicationId = a.id AND asd.deleted = FALSE "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId "
			+ " LEFT JOIN division d ON d.id = sd.divisionId "
			+ " LEFT JOIN `user` u ON u.id = a.createdBy "
			+ " WHERE a.deleted = FALSE "
		);

		if (args != null) {
			if (args.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
			if (args.containsKey("eventName")) sql.append(" AND e.name LIKE :eventName ");
			if (args.containsKey("applicationName")) sql.append(" AND a.name LIKE :applicationName ");
			if (args.containsKey("description")) sql.append(" AND a.description LIKE :description ");
			if (args.containsKey("status")) sql.append(" AND a.status LIKE :status ");
			if (args.containsKey("officer")) sql.append(" AND a.responsibleOfficer LIKE :officer ");
			if (args.containsKey("tagList")) sql.append(" AND at2.tagId IN (:tagList) ");
			if (args.containsKey("divisionIdList")) sql.append(" AND sd.divisionId IN (:divisionIdList) ");
			if (args.containsKey("subDivisionIdList")) sql.append(" AND sd.id IN (:subDivisionIdList) ");
			if (args.containsKey("keyword")){
				sql.append(" AND ( a.createdBy LIKE :keyword ");
				sql.append(" OR u.username  LIKE :keyword ");
				sql.append(" OR u.fullname LIKE :keyword ");
				sql.append(" OR u.email LIKE :keyword ");
				sql.append(" OR sd.name LIKE :keyword ");
				sql.append(" OR d.name LIKE :keyword ");
				sql.append(" OR a.name LIKE :keyword ");
				sql.append(" OR e.name LIKE :keyword) ");
			}
		}
		sql.append(" ORDER BY a.id desc ");

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

	public List<Map<String,Object>> listReport(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT a.*, "
			+ " (SELECT   "
			+ " GROUP_CONCAT( DISTINCT d2.name)   "
			+ " FROM application a2   "
			+ " LEFT JOIN application_sub_division asd2 ON a2.id = asd2.applicationId  "
			+ " LEFT JOIN sub_division sd2 ON asd2.subDivisionId = sd2.id   "
			+ " LEFT JOIN division d2 ON d2.id = sd2.divisionId   "
			+ " WHERE a2.id = a.id  "
			+ " AND asd2.deleted = false "
			+ " GROUP BY a2.id  "
			+ " ) AS divisionList,  "
			+ " e.name AS eventName,"
			+ " u.name as username "
			+ " FROM application a "
			+ " LEFT JOIN event e ON e.id = a.eventId "
			+ " LEFT JOIN application_tag at2 ON at2.applicationId = a.id AND at2.deleted = FALSE"
			+ " LEFT JOIN application_sub_division asd ON asd.applicationId = a.id AND asd.deleted = FALSE "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId "
			+ " LEFT JOIN division d ON d.id = sd.divisionId "
			+ " LEFT JOIN `user` u ON u.id = a.createdBy "
			+ " WHERE a.deleted = FALSE "
		);

		if (args != null) {
			if (args.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
			if (args.containsKey("eventName")) sql.append(" AND e.name LIKE :eventName ");
			if (args.containsKey("applicationName")) sql.append(" AND a.name LIKE :applicationName ");
			if (args.containsKey("description")) sql.append(" AND a.description LIKE :description ");
			if (args.containsKey("status")) sql.append(" AND a.status LIKE :status ");
			if (args.containsKey("officer")) sql.append(" AND a.responsibleOfficer LIKE :officer ");
			if (args.containsKey("tagList")) sql.append(" AND at2.tagId IN (:tagList) ");
			if (args.containsKey("divisionIdList")) sql.append(" AND sd.divisionId IN (:divisionIdList) ");
			if (args.containsKey("subDivisionIdList")) sql.append(" AND sd.id IN (:subDivisionIdList) ");
			if (args.containsKey("keyword")){
				sql.append(" AND ( a.createdBy LIKE :keyword ");
				sql.append(" OR u.username  LIKE :keyword ");
				sql.append(" OR u.fullname LIKE :keyword ");
				sql.append(" OR u.email LIKE :keyword ");
				sql.append(" OR sd.name LIKE :keyword ");
				sql.append(" OR d.name LIKE :keyword ");
				sql.append(" OR a.name LIKE :keyword ");
				sql.append(" OR e.name LIKE :keyword) ");
			}
		}
		sql.append(" ORDER BY a.id");

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

	public List<Integer> getApplicationTag(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
				+ " tagId "
				+ " FROM application_tag at2 "
				+ " LEFT JOIN tag t ON t.id = at2.tagId "
				+ " WHERE at2.applicationId = :id "
				+ " AND t.deleted = FALSE "
				+ " AND at2.deleted = FALSE; "
				);

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

	public void deleteSubDivision(Integer applicationId){
        List<Integer> subDivisionIdList = this.getApplicationSubDivision(Map.of("id", applicationId));
		List<Map<String, Integer>> deleteDivisionIds = subDivisionIdList.stream()
		.map(subDivisionId -> Map.of("applicationId", applicationId, "subDivisionId", subDivisionId))
		.collect(Collectors.toList());

		if (!deleteDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"UPDATE application_sub_division asd"
							+ " SET asd.deleted = TRUE"
							+ " WHERE applicationId = :applicationId AND subDivisionId = :subDivisionId",
							deleteDivisionIds);
		}
	}

	public boolean isNameTaken(String name, Long id, Long eventId) {
        StringBuilder sql = new StringBuilder("SELECT"
            + " Count(a.id) "
            + " FROM application a  "
            + " WHERE a.deleted =FALSE  "
			+ "  AND a.eventId = :eventId "
            + " AND a.name = :name "
			+ " AND a.id != :id "
        );
        
        return jdbcDao.queryForBoolean(sql.toString(), Map.of("name", name, "id", id, "eventId", eventId));
    }


	public void deleteFile(Long applicationId){
        List<Map<String,Object>> fileList = fileService.listFile(Map.of("refId",applicationId,"refType","application"));
		fileList.forEach(file -> fileService.deleteFile(Long.valueOf(file.get("fileId").toString())));
	}

	public void deleteExternalRef(Long awardId){
        List<Map<String,Object>> fileRefList = fileRefService.listExternal(Map.of("refId",awardId,"refType","application"));
		fileRefList.forEach(fileRef -> fileRefService.delete(Long.valueOf(fileRef.get("id").toString())));
    }

	public void markDeleteWithAuditLog(Application instance){
		//=====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> 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("subDivision", this.getApplicationSubDivisionName(input));
			oldValueObject = logData;
		}
		//=====GET OLD AUDIT LOG=====//
		this.markDelete(instance.getId());
        this.deleteSubDivision(instance.getId().intValue());
        this.deleteFile(instance.getId());
		this.deleteExternalRef(instance.getId());
		//=====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("subDivision", this.getApplicationSubDivisionName(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=====//
	}

	public Map<String,Object> getAuditLogObject(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " a.id, "
			+ " a.created, "
			+ " a.createdBy AS createdById, "
			+ " u1.name AS createdBy, "
			+ " a.version, "
			+ " a.modified, "
			+ " a.modifiedBy AS modifiedById, "
			+ " a.deleted, "
			+ " u2.name AS modifiedBy, "
			+ " a.eventId, "
			+ " e.name as eventName, "
			+ " a.name, "
			+ " a.description, "
			+ " a.responsibleOfficer, "
			+ " a.status  "
			+ " FROM application a   "
			+ " LEFT JOIN `user` u1 ON u1.id = a.createdBy  "
			+ " LEFT JOIN `user` u2 ON u2.id = a.modifiedBy  "
			+ " LEFT JOIN event e ON e.id = a.eventId "
			+ " WHERE a.id = :id "
		);

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

	public List<Integer> getApplicationSubDivision(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
				+ " subDivisionId "
				+ " FROM application_sub_division asd "
				+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId "
				+ " WHERE asd.applicationId = :id "
				+ " AND sd.deleted = FALSE "
				+ " AND asd.deleted = FALSE; "
				);

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

	public List<String> getApplicationSubDivisionName(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.name  "
			+ " FROM application_sub_division asd  "
			+ " JOIN sub_division sd ON asd.subDivisionId = sd.id  "
			+ " WHERE asd.applicationId = :id  "
			+ " AND asd.deleted = FALSE; "
				);

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

	public List<Map<String,Object>> listByApplication(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT a2.id, "
			+ " a2.name, "
			+ " (SELECT "
			+ " GROUP_CONCAT(sd.name SEPARATOR ', ')  "
			+ " FROM award_sub_division asd "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId   "
			+ " WHERE asd.deleted = FALSE  "
			+ " AND asd.awardId = a2.id) AS subDivisions,  "
			+ " a2.receiveDate, "
			+ " a2.storageLocation, "
			+ " a2.responsibleOfficer  "
			+ " FROM application a   "
			+ " JOIN application_sub_division asd ON asd.applicationId = a.id "
			+ " LEFT JOIN sub_division sd3 ON asd.subDivisionId = sd3.id"
			+ " LEFT JOIN division d ON d.id = sd3.divisionId "
			+ " LEFT JOIN award a2 ON a.id = a2.applicationId  "
			+ " WHERE a2.deleted = FALSE   "
			+ " AND a.id = :applicationId "
		);
		if (args.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");

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

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

	public Application saveImportRecord(ImpApplication req) {
		Application instance;
		if (req.getId() > 0) {
			instance = find(req.getId()).get();
		} else {
			instance = new Application();
		}
		// =====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("subDivision", this.getApplicationSubDivisionName(input));
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		BeanUtils.copyProperties(req, instance);
		instance = save(instance);
		Long applicationId = instance.getId();

		List<Map<String, Long>> newDivisionIds = new ArrayList<Map<String,Long>>();
		for(Long subDivisionId: req.getTempSubDivIdList()){
			newDivisionIds.add(
				Map.of("applicationId", applicationId, "subDivisionId", subDivisionId)
			);
		}

		if (!newDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO application_sub_division (applicationId,subDivisionId)"
							+ " VALUES (:applicationId, :subDivisionId)",
							newDivisionIds);
		}

		List<Map<String, Long>> newTagIds = new ArrayList<Map<String,Long>>();
		for(Long subDivisionId: req.getTempTagIdList()){
			newTagIds.add(
				Map.of("applicationId", applicationId, "tagId", subDivisionId)
			);
		}
		
		if (!newTagIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO application_tag (applicationId,tagId)"
							+ " VALUES (:applicationId, :tagId)",
							newTagIds);
		}

		// =====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("subDivision", this.getApplicationSubDivisionName(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=====//

		return instance;
	}

	public Map<String, Object> saveOrUpdate(
        Long id, Long eventId, String eventName, String description, 
        String name, String responsibleOfficer, String status, 
        List<Long> subDivisionIdList, List<Long> subDivisionRemoveIdList, 
		List<Long>tagIdList, List<Long>tagRemoveIdList,
        MultipartFile[] files
    ) throws Exception {
		Application instance;
		List<SubDivision> onUsingIdList = new ArrayList<SubDivision>();
		if(id > 0){
			instance = find(id).get();
			onUsingIdList = this.getSelectedSubDivisionList(id);
		}
		else{
			instance = new Application();
		}

		List<String> subDivisionNameList = new ArrayList<String>();
		for (SubDivision subDivision : onUsingIdList) {
			for (Long deleteId: subDivisionRemoveIdList) {
				if(deleteId == subDivision.getId()){
					subDivisionNameList.add(subDivision.getName());
				}
			}
		}

		if(subDivisionNameList.size() > 0){
			String temp = "";
			for (String subDivisionName : subDivisionNameList) {
				temp = temp + subDivisionName + "\n";
			}
			throw new UnprocessableEntityException(
				"Below Sub-Division already exist in award, cannot be deleted: \n"
				+ temp
			);
		}

        instance.setDescription(description);
        instance.setName(name);
        instance.setResponsibleOfficer(responsibleOfficer);
        instance.setStatus(status);
		instance.setEventId(eventId);

		//=====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> 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("subDivision", this.getApplicationSubDivisionName(input));
			oldValueObject = logData;
		}
		//=====GET OLD AUDIT LOG=====//
		instance = save(instance);
		Long applicationId = instance.getId();


		List<Map<String, Long>> newDivisionIds = subDivisionIdList.stream()
				.map(subDivisionId -> Map.of("applicationId", applicationId, "subDivisionId", subDivisionId))
				.collect(Collectors.toList());
		List<Map<String, Long>> deleteDivisionIds = subDivisionRemoveIdList.stream()
				.map(subDivisionId -> Map.of("applicationId", applicationId, "subDivisionId", subDivisionId))
				.collect(Collectors.toList());
		if (!newDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO application_sub_division (applicationId,subDivisionId)"
							+ " VALUES (:applicationId, :subDivisionId)",
							newDivisionIds);
		}
		if (!deleteDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"UPDATE application_sub_division asd"
							+ " SET asd.deleted = TRUE"
							+ " WHERE applicationId = :applicationId AND subDivisionId = :subDivisionId",
							deleteDivisionIds);
		}

		List<Map<String, Long>> newTagIds = tagIdList.stream()
				.map(tagId -> Map.of("applicationId", applicationId, "tagId", tagId))
				.collect(Collectors.toList());
		List<Map<String, Long>> deleteTagIds = tagRemoveIdList.stream()
				.map(tagId -> Map.of("applicationId", applicationId, "tagId", tagId))
				.collect(Collectors.toList());
		if (!newTagIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO application_tag (applicationId,tagId)"
							+ " VALUES (:applicationId, :tagId)",
							newTagIds);
		}
		if (!deleteTagIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"UPDATE application_tag at"
							+ " SET at.deleted = TRUE"
							+ " WHERE applicationId = :applicationId AND tagId = :tagId",
							deleteTagIds);
		}

		Map<String,Object> idMap = new HashMap<String,Object>();
		if(files !=null){
			 idMap = fileService.saveOrUpdate(files, applicationId, "application");
		}

		//=====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("subDivision", this.getApplicationSubDivisionName(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=====//
		return Map.of(
			"id", instance.getId(),
            "fileId", idMap
		);
	}

	public List<Map<String,Object>> listApplicationCombo(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT  "
			+ " a.id,  "
			+ " a.id as `key`,  "
			+ " a.name as label "
			+ " FROM application a "
			+ " LEFT JOIN application_sub_division asd ON asd.applicationId = a.id "
			+ " WHERE a.deleted = FALSE  "
		);

		if (args != null) {
			if (args.containsKey("eventId")) 
				sql.append(" AND a.eventId = :eventId ");

			if (args.containsKey("userSubDivisionId")) 
				sql.append(" AND asd.subDivisionId = :userSubDivisionId ");
		}

		sql.append(" ORDER BY a.name asc");

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

	public List<Application> getApplicationByEventId(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
				+ " a.* "
				+ " FROM application a  "
				+ " WHERE a.deleted = FALSE "
				+ " AND a.eventId = :eventId "
				);

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

	public List<SubDivision> getSelectedSubDivisionList(Long applicationId) {
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT "
			+ " sd.* "
			+ " FROM award a "
			+ " JOIN award_sub_division asd ON asd.awardId = a.id "
			+ " LEFT JOIN sub_division sd ON asd.subDivisionId = sd.id "
			+ " WHERE a.applicationId = :applicationId  "
		);

		return jdbcDao.queryForList(sql.toString(), Map.of("applicationId", applicationId), SubDivision.class);
	}
}
