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

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

import org.springframework.stereotype.Service;

import com.ffii.lioner.modules.lioner.entity.Event;
import com.ffii.lioner.modules.lioner.entity.EventRepository;
import com.ffii.lioner.modules.lioner.entity.ImpEvent;
import com.ffii.lioner.modules.lioner.req.UpdateEventReq;
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 EventService extends AbstractBaseEntityService<Event, Long, EventRepository> {

	private AuditLogService auditLogService;
	public EventService(JdbcDao jdbcDao, EventRepository repository, AuditLogService auditLogService) {
		super(jdbcDao, repository);
		this.auditLogService = auditLogService;
	}

	public Map<String,Object> getAuditLogObject(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " e.id,    "
			+ " e.created,    "
			+ " e.createdBy AS createdById,    "
			+ " u1.name AS createdBy,    "
			+ " e.version,    "
			+ " e.modified,    "
			+ " e.modifiedBy AS modifiedById,    "
			+ " e.deleted, "
			+ " u2.name AS modifiedBy,    "
			+ " e.name, "
			+ " e.nameCht, "
			+ " e.description, "
			+ " e.region, "
			+ " e.organization, "
			+ " e.eventType, "
			+ " e.frequency, "
			+ " e.series, "
			+ " e.startDate, "
			+ " e.eventFrom, "
			+ " e.eventTo, "
			+ " e.applicationDeadline, "
			+ " e.nextApplicationDate, "
			+ " e.announcementDate, "
			+ " e.awardDate, "
			+ " e.reminderFlag, "
			+ " e.reminderThreshold, "
			+ " e.reminderInterval, "
			+ " e.reminderLimit  "
			+ " FROM event e 	   "
			+ " LEFT JOIN `user` u1 ON u1.id = e.createdBy     "
			+ " LEFT JOIN `user` u2 ON u2.id = e.modifiedBy       "
			+ " WHERE e.id = :id    "
		);
		return jdbcDao.queryForMap(sql.toString(), req).get();
	}

	public Event saveImportRecord(ImpEvent req) {
		Event instance;
		if (req.getId() > 0) {
			instance = find(req.getId()).get();
		} else {
			instance = new Event();
		}
		// =====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.getEventDivisionName(input));
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		BeanUtils.copyProperties(req, instance);
		logger.info(instance.toString());
		instance = save(instance);
		Long eventId = instance.getId();
		updateSubDivision(req.getTempIdList(),new ArrayList<>(),eventId);

		// =====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.getEventDivisionName(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(UpdateEventReq req) {
		Event instance;
		List<SubDivision> onUsingIdList = new ArrayList<SubDivision>();
		if(req.getId()>0){
			instance = find(req.getId()).get();
			onUsingIdList = this.getSelectedSubDivisionList(req.getId());
		}
		else{
			instance = new Event();
		}

		List<String> subDivisionNameList = new ArrayList<String>();
		for (SubDivision subDivision : onUsingIdList) {
			for (Long deleteId: req.getSubDivisionRemoveIds()) {
				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 application, cannot be deleted: \n"
				+ temp
			);
		}

		BeanUtils.copyProperties(req,instance);
		if(!instance.getReminderFlag()){
			instance.setReminderThreshold((long) 0);
			instance.setReminderInterval((long) 0);
			instance.setReminderLimit((long) 0);
		}
		
		//=====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.getEventDivisionName(input));
			oldValueObject = logData;
		}
		//=====GET OLD AUDIT LOG=====//

		instance = save(instance);
		Long eventId = instance.getId();
		updateSubDivision(req.getSubDivisionIds(),req.getSubDivisionRemoveIds(),eventId);
		
		//=====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.getEventDivisionName(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()
		);
	}

	public void markDeleteWithAuditLog(Event 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.getEventDivisionName(input));
			oldValueObject = logData;
		}
		//=====GET OLD AUDIT LOG=====//
		this.markDelete(instance.getId());
		this.deleteEventDivision(instance.getId().intValue());
		//=====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.getEventDivisionName(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 void deleteEventDivision(Integer eventId) {
        List<Integer> subDivisionIdList = this.getEventDivision(Map.of("id", eventId));
		List<Map<String, Integer>> deleteDivisionIds = subDivisionIdList.stream()
		.map(subDivisionId -> Map.of("eventId", eventId, "subDivisionId", subDivisionId))
		.collect(Collectors.toList());

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

	public List<Map<String,Object>> list(Map<String, Object> args){
			StringBuilder sql = new StringBuilder("SELECT"
				+ " DISTINCT e.id, "
				+ " e.name AS name, "
				+ " e.applicationDeadline, "
				+ " e.startDate, "
				+ " e.eventFrom "
				+ " FROM event e "
				+ " LEFT JOIN event_sub_division esd ON e.id = esd.eventId "
				+ " LEFT JOIN sub_division sd ON esd.subDivisionId = sd.id "
				+ " LEFT JOIN division d ON d.id = sd.divisionId "
				+ " LEFT JOIN `user` u ON u.id = e.createdBy "
				+ " WHERE e.deleted = FALSE "
				+ " AND esd.deleted = FALSE "

		);

		if (args != null) {
			if (args.containsKey("eventName")) 
				sql.append(" AND e.name LIKE :eventName ");
			if (args.containsKey("description")) 
				sql.append(" AND e.description LIKE :description ");
			if (args.containsKey("organization")) 
				sql.append(" AND e.organization LIKE :organization ");
			if (args.containsKey("fromDate")) 
				sql.append(" AND DATE(e.startDate) >= :fromDate");
			if (args.containsKey("toDate")) 
				sql.append(" AND DATE(e.startDate) < :toDate");
			if (args.containsKey("region")) 
				sql.append(" AND e.region LIKE :region ");
			if (args.containsKey("type")) 
				sql.append(" AND e.eventType LIKE :type ");
			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 ( e.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 e.name LIKE :keyword) ");
			}
				
		}
		sql.append(" ORDER BY e.startDate desc ");

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

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

		if (args != null) {
			if (args.containsKey("eventName")) 
				sql.append(" AND e.name LIKE :eventName ");
			if (args.containsKey("description")) 
				sql.append(" AND e.description LIKE :description ");
			if (args.containsKey("organization")) 
				sql.append(" AND e.organization LIKE :organization ");
			if (args.containsKey("fromDate")) 
				sql.append(" AND DATE(e.startDate) >= :fromDate");
			if (args.containsKey("toDate")) 
				sql.append(" AND DATE(e.startDate) < :toDate");
			if (args.containsKey("region")) 
				sql.append(" AND e.region LIKE :region ");
			if (args.containsKey("type")) 
				sql.append(" AND e.eventType LIKE :type ");
			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 ( e.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 e.name LIKE :keyword) ");
			}
				
		}
		sql.append(" ORDER BY e.id");

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

	public List<Map<String,Object>> listByEvent(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT a.id,  "
			+ " a.name AS applicationName,  "
			+ " a.responsibleOfficer, "
			+ " (SELECT "
			+ " GROUP_CONCAT(t.name SEPARATOR ', ') "
			+ " FROM application_tag at2  "
			+ " LEFT JOIN tag t ON at2.tagId = t.id  "
			+ " WHERE at2.deleted = FALSE "
			+ " AND at2.applicationId = a.id) AS tags, "
			+ " (SELECT "
			+ " GROUP_CONCAT(sd.name SEPARATOR ', ') "
			+ " FROM application_sub_division asd2   "
			+ " LEFT JOIN sub_division sd ON sd.id = asd2.subDivisionId  "
			+ " WHERE asd2.deleted = FALSE "
			+ " AND asd2.applicationId = a.id) AS subDivisions "
			+ " 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 "
			+ " WHERE a.deleted = FALSE  "
			+ " AND a.eventId = :eventId "
		);
		if (args.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");

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

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

	public List<Event> getEventByWithinYear(Map<String,Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT e.* "
			+ " FROM event e   "
			+ " LEFT JOIN event_sub_division esd ON e.id = esd.eventId  "
			+ " WHERE e.deleted = FALSE "
			+ " AND e.reminderFlag = TRUE "
			+ " AND e.startDate >= :yearRange "
		);

		if (args != null) {
			if (args.containsKey("announcementDate")) {
				sql.append(" AND e.announcementDate = :announcementDate");
			}
		}

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

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

	public List<Integer> getEventDivision(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " esd.subDivisionId "
			+ " FROM event_sub_division esd  "
			+ " LEFT JOIN sub_division sd ON sd.id = esd.subDivisionId "
			+ " WHERE esd.deleted = FALSE "
			+ " AND sd.deleted = FALSE "
			+ " AND esd.eventId = :id "
		);

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

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

	public List<String> getEventDivisionName(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.name "
			+ " FROM event_sub_division esd  "
			+ " JOIN sub_division sd ON esd.subDivisionId = sd.id  "
			+ " WHERE esd.deleted = FALSE "
			+ " AND esd.eventId = :id "
		);

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

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

	public List<Map<String,Object>> getEventDivisionAllData(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " esd.* "
			+ " FROM event_sub_division esd  "
			+ " WHERE esd.deleted = FALSE "
			+ " AND esd.eventId = :id "
		);

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

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

    public List<Map<String,Object>> listCombo(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT  "
			+ " e.id,  "
			+ " e.id as `key`,  "
			+ " e.name as label "
			+ " FROM event e  "
			+ " LEFT JOIN event_sub_division esd ON esd.eventId = e.id "
			+ " WHERE e.deleted = FALSE  "
		);
		if (args != null) {
			if (args.containsKey("userSubDivisionId")) 
				sql.append(" AND esd.subDivisionId = :userSubDivisionId ");
		
		}

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

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

	private void updateSubDivision(List<Long> newIds, List<Long> removeIds, Long eventId){
		List<Map<String, Long>> newDivisionIds = newIds.stream()
			.map(subDivisionId -> Map.of("eventId", eventId, "subDivisionId", subDivisionId))
			.collect(Collectors.toList());
		List<Map<String, Long>> deleteDivisionIds = removeIds.stream()
			.map(subDivisionId -> Map.of("eventId", eventId, "subDivisionId", subDivisionId))
			.collect(Collectors.toList());
		if (!newDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"INSERT IGNORE INTO event_sub_division (eventId, subDivisionId, newReminderCount, suppressedNewReminder, oldReminderCount, suppressedOldReminder )"
							+ " VALUES (:eventId, :subDivisionId, 0, 0, 0, 0)",
							newDivisionIds);
		}
		if (!deleteDivisionIds.isEmpty()) {
			jdbcDao.batchUpdate(
					"UPDATE event_sub_division esd"
							+ " SET esd.deleted = TRUE"
							+ " WHERE eventId = :eventId AND subDivisionId = :subDivisionId",
							deleteDivisionIds);
		}

	}

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

	public Boolean hasApplicationRecord(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " COUNT(*) "
			+ " FROM event e  "
			+ " JOIN application a ON e.id = a.eventId "
			+ " WHERE e.deleted = FALSE  "
			+ " AND e.id = :id "
		);

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

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

	public Map<String,Object> isReminderOvertime(Long id){
		Event event = find(id).get();

		if(event.getReminderFlag() == false){
			return Map.of("warning",false);
		}
		else{
			LocalDate firstIssueDate = null;
			Boolean hasApplicationRecord = hasApplicationRecord(Map.of("id",event.getId()));

			if(!hasApplicationRecord){
				firstIssueDate = event.getStartDate().minusDays(event.getReminderThreshold());
			}
			else{
				if(event.getNextApplicationDate() != null){
					firstIssueDate = event.getNextApplicationDate().minusDays(event.getReminderThreshold());				
				}
				else{
					return Map.of(
						"warning", false
					);
				}
			}
			Integer limit = event.getReminderLimit().intValue();
			Integer interval = event.getReminderInterval().intValue();
			List<LocalDate> reminderDateSchedule = new ArrayList<LocalDate>();
			reminderDateSchedule.add(firstIssueDate);
			for(Integer i=1; i< limit+1 ; i++){
				reminderDateSchedule.add(firstIssueDate.plusDays(i*interval));
			}
			logger.info(reminderDateSchedule);
			LocalDate lastLocalDate = null;
			for (LocalDate localDate : reminderDateSchedule) {
				if(localDate.compareTo(event.getApplicationDeadline()) > 0){
					//reminder overflow
					return Map.of(
						"warning", true,
						"lastNotiDate", lastLocalDate
					);
				}
				else{
					lastLocalDate = localDate;
				}
			}
			return Map.of(
						"warning", false
					);
		}
	}

	public List<Map<String,Object>> getTop6(Map<String,Object> req){
			StringBuilder sql = new StringBuilder("SELECT"
			+ " ROW_NUMBER() OVER (ORDER BY  SUM(IF(temp.awardId IS NOT NULL, 1, 0)) DESC)  as `rank`,  "
			+ " temp.eventId,  "
			+ " temp.eventName,  "
			+ " SUM(IF(temp.awardId IS NOT NULL, 1, 0)) as awardCount  "
			+ " FROM ( "
			+ " (SELECT  "
			+ " e.id as eventId,   "
			+ " e.name as eventName, "
			+ " a2.id as awardId "
			+ " FROM event e    "
			+ " LEFT JOIN application a ON a.eventId = e.id    "
			+ " LEFT JOIN award a2 ON a2.applicationId = a.id   "
			+ " LEFT JOIN award_sub_division asd ON a2.id = asd.awardId AND asd.deleted = false  "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId    "
			+ " LEFT JOIN division d ON d.id = sd.divisionId   "
			+ " WHERE e.deleted = FALSE    "
			+ " AND a.deleted = FALSE  "
			+ " AND a2.deleted = FALSE "
		);
		
		if (req.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
		
		sql.append(
			" AND YEAR(e.startDate) = :year "
			+ " GROUP BY e.id, e.name, a2.id) "
			+ " UNION "
			+ " (SELECT  "
			+ " e.id as eventId,   "
			+ " e.name as eventName, "
			+ " null "
			+ " FROM event e "
			+ " LEFT JOIN event_sub_division esd ON e.id = esd.eventId AND esd.deleted = false  "
			+ " LEFT JOIN sub_division sd ON sd.id = esd.subDivisionId    "
			+ " LEFT JOIN division d ON d.id = sd.divisionId   "
			+ " WHERE e.deleted = FALSE    "
		);
		
		if (req.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
		
		sql.append(
			" AND YEAR(e.startDate) = :year "
			+ " GROUP BY e.id, e.name) "
			+ " ) temp "
			+ " GROUP BY temp.eventId, temp.eventName "
			+ " ORDER BY  SUM(IF(temp.awardId IS NOT NULL, 1, 0)) DESC "
			+ " LIMIT 6; "
		);

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

	public List<Map<String,Object>> getDashboardCombo(){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT year(e.startDate) AS id, "
			+ " year(e.startDate) AS label "
			+ " FROM event e  "
			+ " WHERE e.deleted = FALSE  "
			+ " ORDER BY id DESC "
		);

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

	public Map<String,Object> getYearEventCount(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " COUNT(temp.eventId) AS count "
			+ " FROM( "
			+ " SELECT  "
			+ " e.id AS eventId "
			+ " FROM event e   "
			+ " LEFT JOIN event_sub_division esd ON e.id = esd.eventId  "
			+ " LEFT JOIN sub_division sd ON sd.id =esd.subDivisionId  "
			+ " LEFT JOIN division d ON d.id = sd.divisionId  "
			+ " WHERE e.deleted = FALSE   "
			+ " AND esd.deleted = false "
		);

		if (req.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
		sql.append(" AND YEAR(e.startDate) = :year");
		sql.append(" GROUP BY e.id ");
		sql.append(" ) temp; ");

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

	public Map<String,Object> getYearAwardCount(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " COUNT(temp.awardId) AS count  "
			+ " FROM( "
			+ " SELECT "
			+ "  a2.id AS awardId "
			+ " FROM event e   "
			+ " JOIN application a ON a.eventId = e.id    "
			+ " JOIN award a2 ON a2.applicationId = a.id   "
			+ " LEFT JOIN award_sub_division asd ON a2.id = asd.awardId   "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId AND asd.deleted = false "
			+ " LEFT JOIN division d ON d.id = sd.divisionId  "
			+ " WHERE e.deleted = FALSE   "
			+ " AND a.deleted = FALSE   "
			+ " AND a2.deleted = FALSE   "

		);

		if (req.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
		sql.append(" AND YEAR(e.awardDate) = :year");
		sql.append(" GROUP BY a2.id, a2.id ");
		sql.append(" ) temp; ");

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

	public List<Map<String,Object>> getDummyDivisionAwardSummary(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " 	ROW_NUMBER() OVER (ORDER BY awardCount DESC) AS `rank`, "
			+ " 	name, "
			+ " 	awardCount "
			+ " FROM ( "
			+ " 	SELECT  "
			+ " 		queryResult.name, "
			+ " 		COUNT(queryResult.awardName) AS awardCount "
			+ " 	FROM ( "
			+ " 		SELECT   "
			+ " 			DISTINCT  "
			+ " 			d.id, "
			+ " 			d.name, "
			+ " 			e.name AS eventName,"
			+ "             a.name AS application,"
			+ " 			a2.name AS awardName "
			+ " 		FROM event e     "
			+ " 		JOIN application a ON a.eventId = e.id      "
			+ " 		JOIN award a2 ON a2.applicationId = a.id     "
			+ " 		JOIN category c ON a2.categoryId = c.id   "
			+ " 		LEFT JOIN award_sub_division asd ON asd.awardId = a2.id     "
			+ " 		LEFT JOIN sub_division sd ON asd.subDivisionId = sd.id     "
			+ " 		LEFT JOIN division d ON sd.divisionId = d.id     "
			+ " 		WHERE d.deleted = FALSE  "
			+ " 		AND YEAR(e.startDate) = :year  "
			+ " 		AND asd.deleted = false "
		);

		if (req.containsKey("userSubDivisionId")) sql.append(" AND sd.id = :userSubDivisionId ");
		if (req.containsKey("branch")) sql.append(" AND d.branch LIKE :branch ");
		
		sql.append(
			" 		AND sd.deleted = FALSE "
			+ " 	) AS queryResult "
			+ " 	GROUP BY queryResult.id   "
			+ " 	UNION ALL "
			+ " 	SELECT  "
			+ " 		dummy.name,  "
			+ " 		0 AS awardCount  "
			+ " 	FROM ( "
			+ " 		SELECT 1 AS id, 'z' AS name  "
			+ " 		UNION ALL SELECT 2, 'z'  "
			+ " 	) AS dummy "
			+ " ) AS rankedData "
			+ " ORDER BY name ASC  "
			+ " LIMIT 10; "
		);

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


	public List<Map<String,Object>> getDivisionAwardSummaryUntil10(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " 	ROW_NUMBER() OVER (ORDER BY awardCount DESC) AS `rank`, "
			+ " 	name, "
			+ " 	awardCount "
			+ " FROM ( "
			+ " 	SELECT  "
			+ " 		queryResult.name, "
			+ " 		COUNT(queryResult.awardName) AS awardCount "
			+ " 	FROM ( "
			+ " 		SELECT   "
			+ " 			DISTINCT  "
			+ " 			d.id, "
			+ " 			d.name, "
			+ " 			e.name AS eventName,"
			+ "             a.name AS application,"
			+ " 			a2.name AS awardName "
			+ " 		FROM event e     "
			+ " 		JOIN application a ON a.eventId = e.id      "
			+ " 		JOIN award a2 ON a2.applicationId = a.id     "
			+ " 		JOIN category c ON a2.categoryId = c.id   "
			+ " 		LEFT JOIN award_sub_division asd ON asd.awardId = a2.id     "
			+ " 		LEFT JOIN sub_division sd ON asd.subDivisionId = sd.id     "
			+ " 		LEFT JOIN division d ON sd.divisionId = d.id     "
			+ " 		WHERE d.deleted = FALSE  "
			+ " 		AND YEAR(e.startDate) = :year  "
			+ " 		AND sd.deleted = FALSE "
			+ "			AND d.branch LIKE :branch "
			+ " 		AND asd.deleted = false "
			+ " 	) AS queryResult "
			+ " 	GROUP BY queryResult.id   "
			+ " 	UNION ALL "
			+ " 	SELECT  "
			+ " 		dummy.name,  "
			+ " 		0 AS awardCount  "
			+ " 	FROM ( "
			+ " 		SELECT 1 AS id, 'z' AS name  "
			+ " 		UNION ALL SELECT 2, 'z'  "
			+ " 		UNION ALL SELECT 3, 'z'  "
			+ " 		UNION ALL SELECT 4, 'z'  "
			+ " 		UNION ALL SELECT 5, 'z'  "
			+ " 		UNION ALL SELECT 6, 'z'  "
			+ " 		UNION ALL SELECT 7, 'z'  "
			+ " 		UNION ALL SELECT 8, 'z'  "
			+ " 		UNION ALL SELECT 9, 'z'  "
			+ " 		UNION ALL SELECT 10, 'z' "
			+ " 	) AS dummy "
			+ " ) AS rankedData "
			+ " ORDER BY name ASC  "
			+ " LIMIT 10; "
		);

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

	public List<Map<String,Object>> getDivisionAwardSummary(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
		+ " ROW_NUMBER() OVER (ORDER BY awardCount DESC) AS `rank`, "
		+ " 	name,  "
		+ " 	awardCount  "
		+ " FROM (  "
		+ " 	SELECT   "
		+ " 		queryResult.name,  "
		+ " 		COUNT(queryResult.awardName) AS awardCount  "
		+ " 	FROM (  "
		+ " 		SELECT    "
		+ " 			DISTINCT   "
		+ " 			d.id,  "
		+ " 			d.name,  "
		+ " 			e.name AS eventName, "
		+ " 			a.name AS application, "
		+ " 			a2.name AS awardName  "
		+ " 		FROM event e      "
		+ " 		JOIN application a ON a.eventId = e.id       "
		+ " 		JOIN award a2 ON a2.applicationId = a.id      "
		+ " 		JOIN category c ON a2.categoryId = c.id    "
		+ " 		LEFT JOIN award_sub_division asd ON asd.awardId = a2.id      "
		+ " 		LEFT JOIN sub_division sd ON asd.subDivisionId = sd.id      "
		+ " 		LEFT JOIN division d ON sd.divisionId = d.id      "
		+ " 		WHERE d.deleted = FALSE   "
		+ " 		AND YEAR(e.startDate) = :year   "
		+ " 		AND sd.deleted = FALSE  "
		+ "			AND d.branch LIKE :branch "
		+ " 		AND asd.deleted = false "
		+ " 	) AS queryResult  "
		+ " 	GROUP BY queryResult.id    "
		+ " ) AS rankedData  "
		+ " ORDER BY name ASC   "
		);

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

	public List<Map<String,Object>> getTagAwardSummary(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " temp.id,  "
			+ " temp.name,  "
			+ " COUNT(temp.awardId) AS awardCount  "
			+ " FROM( "
			+ " SELECT "
			+ " t.id,  "
			+ " t.name,  "
			+ " a2.id AS awardId  "
			+ " FROM event e   "
			+ " JOIN application a ON a.eventId = e.id    "
			+ " JOIN application_tag at2 ON at2.applicationId = a.id  "
			+ " JOIN tag t ON t.id = at2.tagId   "
			+ " JOIN award a2 ON a2.applicationId = a.id "
			+ " LEFT JOIN award_sub_division asd ON a2.id = asd.awardId AND asd.deleted = FALSE "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId   "
			+ " LEFT JOIN division d ON d.id = sd.divisionId  "
			+ " WHERE e.deleted = FALSE   "
			+ " AND a.deleted = FALSE   "
			+ " AND a2.deleted = FALSE   "
		);

		if (req.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
		sql.append(" AND YEAR(e.startDate) = :year ");
		sql.append(" GROUP BY t.id, a2.id ");
		sql.append(" ) temp ");
		sql.append(" GROUP BY temp.id ");
		sql.append(" ORDER BY COUNT(temp.id)  DESC");
		sql.append(" LIMIT 6 ;");

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

	public List<Map<String,Object>> getCategoryAwardSummary(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " temp.id,  "
			+ " temp.name,  "
			+ " COUNT(temp.awardId) AS awardCount  "
			+ " FROM( "
			+ " SELECT "
			+ " c.id,  "
			+ " c.name,  "
			+ " a2.id AS awardId "
			+ " FROM event e   "
			+ " JOIN application a ON a.eventId = e.id    "
			+ " JOIN award a2 ON a2.applicationId = a.id  "
			+ " LEFT JOIN award_sub_division asd ON a2.id = asd.awardId   "
			+ " LEFT JOIN sub_division sd ON sd.id = asd.subDivisionId   "
			+ " LEFT JOIN division d ON d.id = sd.divisionId  "
			+ " LEFT JOIN category c ON a2.categoryId = c.id   "
			+ " WHERE e.deleted = FALSE   "
			+ " AND a.deleted = FALSE   "
			+ " AND a2.deleted = FALSE   "
			+ " AND asd.deleted = false "

		);

		if (req.containsKey("userSubDivisionId")) sql.append(" AND d.id = (SELECT sd2.divisionId FROM sub_division sd2 WHERE sd2.id = :userSubDivisionId)");
		sql.append(" AND YEAR(e.startDate) = :year ");
		sql.append(" GROUP BY c.id, a2.id ");
		sql.append(" ) temp ");
		sql.append(" GROUP BY temp.id ");
		sql.append(" ORDER BY COUNT(temp.id)  DESC");
		sql.append(" LIMIT 6 ;");

		return jdbcDao.queryForList(sql.toString(),req);
	}
	
	public List<Map<String,Object>> getAwardSummaryReportPage1(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " 	name, "
			+ " 	count "
			+ " FROM ( "
			+ " 	SELECT  "
			+ " 		queryResult.name, "
			+ " 		COUNT(queryResult.awardName) AS count "
			+ " 	FROM ( "
			+ " 		SELECT   "
			+ " 			DISTINCT  "
			+ " 			d.id, "
			+ " 			d.name, "
			+ " 			e.name AS eventName,"
			+ "             a.name AS application,"
			+ " 			a2.name AS awardName "
			+ " 		FROM event e     "
			+ " 		JOIN application a ON a.eventId = e.id      "
			+ " 		JOIN award a2 ON a2.applicationId = a.id     "
			+ " 		JOIN category c ON a2.categoryId = c.id   "
			+ " 		LEFT JOIN award_sub_division asd ON asd.awardId = a2.id     "
			+ " 		LEFT JOIN sub_division sd ON asd.subDivisionId = sd.id     "
			+ " 		LEFT JOIN division d ON sd.divisionId = d.id     "
			+ " 		WHERE d.deleted = FALSE  "
			+ " 		AND YEAR(e.startDate) = :year  "
			+ " 		AND asd.deleted = false "
		);

		if (req.containsKey("userSubDivisionId")) sql.append(" AND sd.id = :userSubDivisionId ");
		if (req.containsKey("branch")) sql.append(" AND d.branch LIKE :branch ");
		
		sql.append(
			" 		AND sd.deleted = FALSE "
			+ " 	) AS queryResult "
			+ " 	GROUP BY queryResult.id   "
			+ " ) AS rankedData "
			+ " ORDER BY count DESC  "
		);

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

	public List<Map<String,Object>> getAwardSummaryReportPage2(Map<String,Object> req){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " DISTINCT  "
			+ " d.name, "
			+ " (SELECT GROUP_CONCAT( DISTINCT sd.name SEPARATOR ', ')  "
			+ " FROM award_sub_division asd2  "
			+ " LEFT JOIN sub_division sd ON sd.id = asd2.subDivisionId "
			+ " LEFT JOIN division d2 ON sd.divisionId = d2.id  "
			+ " WHERE asd2.deleted = FALSE  "
			+ " AND d2.id = d.id "
			+ " ) AS subDivision, "
			+ " e.name AS eventName, "
			+ " a.name AS applicationName, "
			+ " a2.name AS awardName, "
			+ " a2.receiveDate, "
			+ " c.name AS category , "
			+ " a2.responsibleOfficer  "
			+ " FROM event e    "
			+ " JOIN application a ON a.eventId = e.id     "
			+ " JOIN award a2 ON a2.applicationId = a.id    "
			+ " JOIN category c ON a2.categoryId = c.id  "
			+ " LEFT JOIN award_sub_division asd ON asd.awardId = a2.id AND asd.deleted = FALSE "
			+ " LEFT JOIN sub_division sd ON asd.subDivisionId =sd.id    "
			+ " LEFT JOIN division d ON sd.divisionId = d.id    "
			+ " WHERE a.deleted = FALSE    "
			+ " AND a.deleted = FALSE    "
			+ " AND a2.deleted = FALSE   "
			+ " AND YEAR(e.startDate) = :year    "
		);
		if (req.containsKey("userSubDivisionId")) sql.append(" AND sd.id = :userSubDivisionId ");
		if (req.containsKey("branch")) sql.append(" AND d.branch LIKE :branch ");

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

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

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

	public Integer getIdByName(String name) {
        StringBuilder sql = new StringBuilder("SELECT"
			+ " e.id "
			+ " FROM event e  "
			+ " WHERE e.deleted = FALSE"
			+ " AND e.name = :name "
        );
        
        return jdbcDao.queryForInt(sql.toString(), Map.of("name", name));
    }
	
}