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

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.TextStyle;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.docx4j.Docx4J;
import org.docx4j.openpackaging.exceptions.Docx4JException;
import org.docx4j.openpackaging.packages.WordprocessingMLPackage;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.ffii.lioner.modules.lioner.entity.Appreciation;
import com.ffii.lioner.modules.lioner.entity.AppreciationRepository;
import com.ffii.lioner.modules.lioner.reportDao.AppreciationCaseReportDao;
import com.ffii.lioner.modules.lioner.reportDao.AppreciationChartDataDao;
import com.ffii.lioner.modules.lioner.reportDao.ImportErrorRecord;
import com.ffii.lioner.modules.common.SecurityUtils;
import com.ffii.lioner.modules.common.service.AuditLogService;
import com.ffii.lioner.modules.common.service.ExcelReportService;
import com.ffii.lioner.modules.common.service.WordReportService;
import com.ffii.lioner.modules.master.service.AppreciationCategoryService;
import com.ffii.lioner.modules.master.service.ClientDepartmentService;
import com.ffii.lioner.modules.master.service.SBUDivisionService;
import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.BeanUtils;
import com.ffii.core.utils.ExcelUtils;
import com.ffii.core.utils.JsonUtils;
import com.ffii.core.utils.NumberUtils;
import com.ffii.core.utils.Params;

import io.micrometer.common.util.StringUtils;
import jakarta.persistence.Table;
import jakarta.xml.bind.JAXBException;

@Service
public class AppreciationService extends AbstractBaseEntityService<Appreciation, Long, AppreciationRepository> {

	private AuditLogService auditLogService;
	private FileService fileService;
	private SBUDivisionService sbuDivisionService;
	private ClientDepartmentService clientDepartmentService;
	private AppreciationCategoryService appreciationCategoryService;
	private WordReportService wordReportService;
	private ExcelReportService excelReportService;

	public AppreciationService(JdbcDao jdbcDao, AppreciationRepository repository,
			AuditLogService auditLogService,
			FileService fileService,
			SBUDivisionService sbuDivisionService,
			ClientDepartmentService clientDepartmentService,
			AppreciationCategoryService appreciationCategoryService,
			WordReportService wordReportService,
			ExcelReportService excelReportService) {
		super(jdbcDao, repository);
		this.auditLogService = auditLogService;
		this.fileService = fileService;
		this.sbuDivisionService = sbuDivisionService;
		this.clientDepartmentService = clientDepartmentService;
		this.appreciationCategoryService = appreciationCategoryService;
		this.wordReportService = wordReportService;
		this.excelReportService = excelReportService;
	}

	public Map<String, Object> getAuditLogObject(Map<String, Object> req) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " aa.id,    "
				+ " aa.created,    "
				+ " aa.createdBy AS createdById,    "
				+ " u1.name AS createdBy,    "
				+ " aa.version,    "
				+ " aa.modified,    "
				+ " aa.modifiedBy AS modifiedById,    "
				+ " u2.name AS modifiedBy, "
				+ " aa.deleted, "
				+ " aa.sbuIds, "
				+ " aa.receiptDate, "
				+ " aa.description, "
				+ " aa.clientDepartment, "
				+ " aa.clientOrganization, "
				+ " aa.clientFullname, "
				+ " aa.clientPost, "
				+ " aa.venue, "
				+ " aa.clientReplyDate, "
				+ " aa.staffReplyDate, "
				+ " aa.noOfStaff, "
				+ " aa.lnReceiptDate, "
				+ " aa.aprCategoryId, "
				+ " aa.remarks, "
				+ " aa.importDate "
				+ " FROM apr_appreciation aa "
				+ " LEFT JOIN `user` u1 ON u1.id = aa.createdBy     "
				+ " LEFT JOIN `user` u2 ON u2.id = aa.modifiedBy      "
				+ " WHERE aa.id = :id    ");

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

	public void markDeleteWithAuditLog(Appreciation 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);
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//
		markDelete(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);
			newValueObject = logData;
		}

		if (auditLogService.compareMaps(newValueObject, oldValueObject).size() != 0 ||
				auditLogService.compareMaps(oldValueObject, newValueObject).size() != 0) {
			auditLogService.save(
					tableName,
					instance.getId(),
					instance.getDescription().length() > 300 ? (instance.getDescription().substring(0, 300) + "...")
							: instance.getDescription(),
					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> saveOrUpdate(
			Long id, String sbuIds, LocalDate receiptDate, String description, String clientDepartment,
			String clientOrganization, String clientFullname, String clientPost, String venue,
			LocalDate clientReplyDate,
			LocalDate staffReplyDate, Long noOfStaff, LocalDate lnReceiptDate, Long aprCategoryId, String remarks,
			MultipartFile[] files) throws Exception {
		Appreciation instance;
		if (id > 0) {
			instance = find(id).get();
		} else {
			instance = new Appreciation();
		}

		instance.setSbuIds(sbuIds);
		instance.setReceiptDate(receiptDate);
		instance.setDescription(description);
		if (clientDepartment.compareTo("undefined") != 0) {
			instance.setClientDepartment(clientDepartment);
		}
		instance.setClientOrganization(clientOrganization);
		instance.setClientFullname(clientFullname);
		instance.setClientPost(clientPost);
		instance.setVenue(venue);
		instance.setClientReplyDate(clientReplyDate);
		instance.setStaffReplyDate(staffReplyDate);
		instance.setNoOfStaff(noOfStaff);
		instance.setLnReceiptDate(lnReceiptDate);
		instance.setAprCategoryId(aprCategoryId);
		if (remarks.compareTo("null") != 0) {
			instance.setRemarks(remarks);
		}

		// =====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);
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		instance = save(instance);
		Long awardId = instance.getId();

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

		// =====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);
			newValueObject = logData;
		}

		if (auditLogService.compareMaps(newValueObject, oldValueObject).size() != 0 ||
				auditLogService.compareMaps(oldValueObject, newValueObject).size() != 0) {
			auditLogService.save(
					tableName,
					instance.getId(),
					instance.getDescription().length() > 300 ? (instance.getDescription().substring(0, 300) + "...")
							: instance.getDescription(),
					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 Appreciation saveImportRecord(Appreciation req) {
		Appreciation instance;
		if (req.getId() > 0) {
			instance = find(req.getId()).get();
		} else {
			instance = new Appreciation();
		}
		// =====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);
			oldValueObject = logData;
		}
		// =====GET OLD AUDIT LOG=====//

		BeanUtils.copyProperties(req, instance);
		instance = save(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);
			newValueObject = logData;
		}

		if (auditLogService.compareMaps(newValueObject, oldValueObject).size() != 0 ||
				auditLogService.compareMaps(oldValueObject, newValueObject).size() != 0) {
			auditLogService.save(
					tableName,
					instance.getId(),
					instance.getDescription().length() > 300 ? (instance.getDescription().substring(0, 300) + "...")
							: instance.getDescription(),
					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> massImport(MultipartFile[] files) throws IOException {
		Map<String, Object> result = new HashMap<String, Object>();
		List<Appreciation> appreciationRecord = new ArrayList<>();
		List<ImportErrorRecord> errorRecord = new ArrayList<>();
		LocalDateTime recordImportDate = LocalDateTime.now();
		Integer rowCount = 0;
		Boolean templateVersion = true;
		Sheet sheet = null;

		ImportErrorRecord templateError = new ImportErrorRecord(
				0L,
				0L,
				"Template",
				"Please download the latest template for import data");

		for (MultipartFile file : files) {
			Workbook workbook = new XSSFWorkbook(file.getInputStream());
			try {
				sheet = workbook.getSheetAt(1);
			} catch (Exception e) {
				errorRecord.add(templateError);
				break;
			}
			if (sheet.getSheetName().compareTo("Appreciation Records") != 0) {
				templateVersion = false;
				errorRecord.add(templateError);
				break;
			}

			for (Row row : sheet) {
				// Skip the header row
				if (row.getRowNum() == 0) {
					// validate template
					List<String> validateList = Arrays.asList(
							"sbu", "receiptDate", "description", "clientDepartment", "clientOrganization",
							"clientName", "clientPost", "venue", "clientReplyDate", "staffReplyDate",
							"noOfStaff", "lnReceiptDate", "category", "remarks");

					for (int i = 0; i < validateList.size(); i++) {
						Cell cell = row.getCell(i);
						logger.info(getStringValueFromCell(cell) + " vs " + validateList.get(i));
						if (getStringValueFromCell(cell).compareTo(validateList.get(i)) != 0) {
							templateVersion = false;
							errorRecord.add(templateError);
							break;
						}
					}
					continue;
				} else if (row.getRowNum() == 1) {
					// Skip the header row
					continue;

				}

				if (!templateVersion) {
					break;
				}
				Appreciation record = createRecordFromRow(row, errorRecord, recordImportDate);
				appreciationRecord.add(record);
				rowCount++;
			}
			workbook.close();
		}

		if (errorRecord.size() == 0) {
			// error free import, can process to import appreciation
			for (Appreciation appreciation : appreciationRecord) {
				saveImportRecord(appreciation);
			}
		}

		result = Map.of(
				"recordCount", rowCount,
				"recordList", errorRecord,
				"status", errorRecord.size() == 0);
		return result;
	}

	private Appreciation createRecordFromRow(Row row, List<ImportErrorRecord> errorRecord,
			LocalDateTime recordImportDate) {
		// Extract data from the cells of the row and create an AppreciationRecord
		// object
		Appreciation record = new Appreciation();
		record.setImportDate(recordImportDate);
		Long rowNumber = (long) row.getRowNum() + 1;
		record.setId((long) -1);

		Cell sbuCell = row.getCell(0);
		String sbuString = ExcelUtils.getStringValue(sbuCell);
		if (!StringUtils.isBlank(sbuString)) {
			String[] sbuList = sbuString.split(",\\s*"); // split string for both ',' and ', ' case
			List<SbuIdObject> sbuIdList = new ArrayList<>();
			for (String sbuName : sbuList) {
				sbuName = sbuName.trim();
				Long id = (long) sbuDivisionService.getIdByName(sbuName);
				if (id == 0) {
					ImportErrorRecord error = new ImportErrorRecord(
							(long) errorRecord.size(),
							rowNumber,
							"SBU / Division",
							sbuName + " doesn't exist");
					errorRecord.add(error);
				} else {
					sbuIdList.add(new SbuIdObject(id));
				}
			}
			ObjectMapper objectMapper = new ObjectMapper();
			try {
				String sbuIdListJson = objectMapper.writeValueAsString(sbuIdList);
				record.setSbuIds(sbuIdListJson);
			} catch (JsonProcessingException e) {
				// Handle the exception if necessary
				e.printStackTrace();
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"SBU / Division",
					"This column is mandatory.");

			errorRecord.add(error);
		}

		Cell receiptDateCell = row.getCell(1);
		if (receiptDateCell != null) {
			LocalDate receiptDate = getDateValueFromCell(receiptDateCell);
			if (receiptDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Receipt Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setReceiptDate(receiptDate);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Receipt Date",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell descriptionCell = row.getCell(2);
		String description = ExcelUtils.getStringValue(descriptionCell);
		if (!StringUtils.isBlank(description)) {
			record.setDescription(description);
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Brief Description",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell clientDepartmentCell = row.getCell(3);
		String departmentString = ExcelUtils.getStringValue(clientDepartmentCell);
		if (!StringUtils.isBlank(departmentString)) {
			Long id = (long) clientDepartmentService.getIdByName(departmentString);
			if (departmentString.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Client Department",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
				record.setClientDepartment(departmentString);
			}

			if (id == 0) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Client Department",
						departmentString + " doesn't exist");
				errorRecord.add(error);
				record.setClientDepartment(departmentString);
			} else {
				record.setClientDepartment(departmentString);
			}
		}

		Cell clientOrganizationCell = row.getCell(4);
		String organization = ExcelUtils.getStringValue(clientOrganizationCell);
		if (!StringUtils.isBlank(organization)) {
			if (organization.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Client Organization",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setClientOrganization(organization);
		}

		// post-checking of client department and organization
		if (StringUtils.isBlank(record.getClientDepartment()) &&
				StringUtils.isBlank(record.getClientOrganization())) {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Client Department / Client Organization",
					"Please provide either one of the client department or client organization.");
			errorRecord.add(error);
		}

		// post-checking of client department and organization
		if (!StringUtils.isBlank(record.getClientDepartment()) &&
				!StringUtils.isBlank(record.getClientOrganization())) {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Client Department / Client Organization",
					"Please remove one of the client department or client organization.");
			errorRecord.add(error);
		}

		Cell clientNameCell = row.getCell(5);
		String clientName = ExcelUtils.getStringValue(clientNameCell);
		if (!StringUtils.isBlank(clientName)) {
			if (clientName.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Client Name",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setClientFullname(clientName);
		}

		Cell clientPostCell = row.getCell(6);
		String clientPost = ExcelUtils.getStringValue(clientPostCell);
		if (!StringUtils.isBlank(clientPost)) {
			if (clientPost.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Client Post",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setClientPost(clientPost);
		}

		Cell venueCell = row.getCell(7);
		String venue = ExcelUtils.getStringValue(venueCell);
		if (!StringUtils.isBlank(venue)) {
			if (venue.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Venue",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setVenue(venue);
		}

		Cell clientReplyDateCell = row.getCell(8);

		if (clientReplyDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(clientReplyDateCell))) {
			LocalDate clientReplyDate = getDateValueFromCell(clientReplyDateCell);
			if (clientReplyDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Client Reply Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setClientReplyDate(clientReplyDate);
			}
		}

		Cell staffReplyDateCell = row.getCell(9);
		if (staffReplyDateCell != null && StringUtils.isNotBlank(ExcelUtils.getStringValue(staffReplyDateCell))) {
			LocalDate staffReplyDate = getDateValueFromCell(staffReplyDateCell);
			if (staffReplyDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Staff Reply Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setStaffReplyDate(staffReplyDate);
			}
		}

		Cell noOfStaffCell = row.getCell(10);
		String noOfStaff = ExcelUtils.getStringValue(noOfStaffCell);
		if (!StringUtils.isBlank(noOfStaff)) {
			try {
				Double noOfStaffDouble = Double.parseDouble(noOfStaff);
				Long noOfStaffNum = noOfStaffDouble.longValue();
				record.setNoOfStaff(noOfStaffNum);
			} catch (NumberFormatException e) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"No of Staff",
						"Value must be an integer."
				// "Failed to parse '" + noOfStaff + "' to a valid number"
				);
				errorRecord.add(error);
			}
		}

		Cell lnReceiptDateCell = row.getCell(11);
		if (lnReceiptDateCell != null) {
			LocalDate lnReceiptDate = getDateValueFromCell(lnReceiptDateCell);
			if (lnReceiptDate == null) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"LN Receipt Date",
						"Incorrect Date Format.");
				errorRecord.add(error);
			} else {
				record.setLnReceiptDate(lnReceiptDate);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"LN Receipt Date",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell categoryCell = row.getCell(12);
		String categoryString = ExcelUtils.getStringValue(categoryCell);
		if (!StringUtils.isBlank(categoryString)) {
			Long id = (long) appreciationCategoryService.getIdByName(categoryString);
			if (id == 0) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Category",
						categoryString + " doesn't exist");
				errorRecord.add(error);
				record.setAprCategoryId(id);
			} else {
				record.setAprCategoryId(id);
			}
		} else {
			ImportErrorRecord error = new ImportErrorRecord(
					(long) errorRecord.size(),
					rowNumber,
					"Category",
					"This column is mandatory.");
			errorRecord.add(error);
		}

		Cell remarksCell = row.getCell(13);
		String remarks = ExcelUtils.getStringValue(remarksCell);
		if (!StringUtils.isBlank(remarks)) {
			if (remarks.length() > 255) {
				ImportErrorRecord error = new ImportErrorRecord(
						(long) errorRecord.size(),
						rowNumber,
						"Remarks",
						"Value must not exceed 255 character.");
				errorRecord.add(error);
			}
			record.setRemarks(remarks);
		}

		return record;
	}

	private LocalDate getDateValueFromCell(Cell cell) {
		// Handle date formatted numeric cell, if needed
		if (cell != null) {
			switch (cell.getCellType()) {
				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						LocalDate dateValue = cell.getLocalDateTimeCellValue().toLocalDate();
						return dateValue;
					} else {
						return null;
					}
				default:
					return null;
			}
		} else {
			return null;
		}
	}

	private String getStringValueFromCell(Cell cell) {
		String cellValue = "";
		if (cell != null) {
			switch (cell.getCellType()) {
				case STRING:
					cellValue = cell.getStringCellValue();
					break;
				case NUMERIC:
					double numericValue = cell.getNumericCellValue();
					cellValue = String.valueOf(numericValue);
					break;
				default:
					break;
			}
		}
		return cellValue;
	}

	public List<Map<String, Object>> list(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " aa.id,  "
				+ " aa.sbuIds,  "
				+ " aa.receiptDate,  "
				+ " aa.description,  "
				+ " aa.clientDepartment,  "
				+ " aa.clientOrganization, "
				+ " IF(aa.clientDepartment IS NULL OR aa.clientDepartment = '', aa.clientOrganization, aa.clientDepartment) AS clientDeptOrg,"
				+ " aa.lnReceiptDate,  "
				+ " ac.name AS category "
				+ " FROM apr_appreciation aa   "
				+ " LEFT JOIN apr_category ac ON aa.aprCategoryId = ac.id  "
				+ " WHERE aa.deleted = FALSE   ");

		if (args != null) {
			if (args.containsKey(Params.ID))
				sql.append(" AND aa.id = :id ");
			if (args.containsKey("sbuIds"))
				sql.append(" AND JSON_OVERLAPS(aa.sbuIds->'$[*].id', :sbuIds) ");
			if (args.containsKey("categoryIds"))
				sql.append(" AND aa.aprCategoryId IN (:categoryIds) ");
			if (args.containsKey("clientDepOrOrg")) {
				// check both dep and org
				sql.append(
						" AND aa.clientDepartment LIKE :clientDepOrOrg OR aa.clientOrganization LIKE :clientDepOrOrg ");
			}
			if (args.containsKey("clientName")) {
				sql.append(" AND aa.clientFullname LIKE :clientName ");
			}

			if (args.containsKey("receiptDateFrom"))
				sql.append(" AND DATE(aa.receiptDate) >= :receiptDateFrom");
			if (args.containsKey("receiptDateTo"))
				sql.append(" AND DATE(aa.receiptDate) < :receiptDateTo");

			if (args.containsKey("lnReceiptDateFrom"))
				sql.append(" AND DATE(aa.lnReceiptDate) >= :lnReceiptDateFrom");
			if (args.containsKey("lnReceiptDateTo"))
				sql.append(" AND DATE(aa.lnReceiptDate) < :lnReceiptDateTo");

			if (args.containsKey("clientReplyDateFrom"))
				sql.append(" AND DATE(aa.clientReplyDate) >= :clientReplyDateFrom");
			if (args.containsKey("clientReplyDateTo"))
				sql.append(" AND DATE(aa.clientReplyDate) < :clientReplyDateTo");

			if (args.containsKey("staffReplyDateFrom"))
				sql.append(" AND DATE(aa.staffReplyDate) >= :staffReplyDateFrom");
			if (args.containsKey("staffReplyDateTo"))
				sql.append(" AND DATE(aa.staffReplyDate) < :staffReplyDateTo");

		}
		sql.append(" ORDER BY aa.lnReceiptDate desc");

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

	public List<Map<String, Object>> listReport(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " aa.*,  "
				+ " ac.name AS category, "
				+ " u.name AS username "
				+ " FROM apr_appreciation aa   "
				+ " LEFT JOIN apr_category ac ON aa.aprCategoryId = ac.id  "
				+ " LEFT JOIN `user` u ON u.id = aa.createdBy "
				+ " WHERE aa.deleted = FALSE   ");

		if (args != null) {
			if (args.containsKey(Params.ID))
				sql.append(" AND aa.id = :id ");
			if (args.containsKey("sbuIds"))
				sql.append(" AND JSON_OVERLAPS(aa.sbuIds->'$[*].id', :sbuIds) ");
			if (args.containsKey("categoryIds"))
				sql.append(" AND aa.aprCategoryId IN (:categoryIds) ");
			if (args.containsKey("clientDepOrOrg")) {
				// check both dep and org
				sql.append(
						" AND aa.clientDepartment LIKE :clientDepOrOrg OR aa.clientOrganization LIKE :clientDepOrOrg ");
			}
			if (args.containsKey("clientName")) {
				sql.append(" AND aa.clientFullname LIKE :clientName ");
			}

			if (args.containsKey("receiptDateFrom"))
				sql.append(" AND DATE(aa.receiptDate) >= :receiptDateFrom");
			if (args.containsKey("receiptDateTo"))
				sql.append(" AND DATE(aa.receiptDate) < :receiptDateTo");

			if (args.containsKey("lnReceiptDateFrom"))
				sql.append(" AND DATE(aa.lnReceiptDate) >= :lnReceiptDateFrom");
			if (args.containsKey("lnReceiptDateTo"))
				sql.append(" AND DATE(aa.lnReceiptDate) < :lnReceiptDateTo");

			if (args.containsKey("clientReplyDateFrom"))
				sql.append(" AND DATE(aa.clientReplyDate) >= :clientReplyDateFrom");
			if (args.containsKey("clientReplyDateTo"))
				sql.append(" AND DATE(aa.clientReplyDate) < :clientReplyDateTo");

			if (args.containsKey("staffReplyDateFrom"))
				sql.append(" AND DATE(aa.staffReplyDate) >= :staffReplyDateFrom");
			if (args.containsKey("staffReplyDateTo"))
				sql.append(" AND DATE(aa.staffReplyDate) < :staffReplyDateTo");

		}
		// sql.append(" ORDER BY aa.id");
		sql.append(" ORDER BY aa.lnReceiptDate desc");

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

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

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

	public List<Map<String, Object>> getAppreciationsCaseCount(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " ac.name, "
				+ " COUNT(aa.id) AS appreciationCount "
				+ " FROM apr_appreciation aa  "
				+ " LEFT JOIN apr_category ac ON ac.id = aa.aprCategoryId  "
				+ " WHERE aa.deleted = FALSE  "
				+ " AND DATE(aa.lnReceiptDate) >= :dateFrom "
				+ " AND DATE(aa.lnReceiptDate) < :dateTo "
				+ " GROUP BY ac.name ");

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

	public List<Map<String, Object>> getAppreciationMonthlyCountByName(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT "
				+ " YEAR(aa.lnReceiptDate) AS recordYear, "
				+ " MONTH(aa.lnReceiptDate) AS recordMonth, "
				+ " jt.id, "
				+ " as2.name, "
				+ " COUNT(*) AS count "
				+ " FROM apr_appreciation aa, "
				+ " 	 JSON_TABLE(aa.sbuIds, '$[*]' COLUMNS ( "
				+ " 		 id INT PATH '$.id' "
				+ " 	 )) AS jt "
				+ " LEFT JOIN apr_sbu as2 ON jt.id = as2.id "
				+ " WHERE aa.deleted = FALSE  "
				+ " AND DATE(aa.lnReceiptDate) >= :dateFrom "
				+ " AND DATE(aa.lnReceiptDate) < :dateTo "
				+ " AND as2.name LIKE :name "
				+ " GROUP BY jt.id, MONTH(aa.lnReceiptDate), YEAR(aa.lnReceiptDate) "
				+ " ORDER BY YEAR(aa.lnReceiptDate), MONTH(aa.lnReceiptDate) ");

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

	public List<Map<String, Object>> getAppreciationSBUCountByPeriod(Map<String, Object> args) {
		// StringBuilder sql = new StringBuilder("SELECT "
		// + " jt.id, "
		// + " as2.name, "
		// + " COUNT(*) AS count "
		// + " FROM apr_sbu as2"
		// + " LEFT JOIN apr_appreciation aa, "
		// + " JSON_TABLE(aa.sbuIds, '$[*]' COLUMNS ( "
		// + " id INT PATH '$.id' "
		// + " )) AS jt ON jt.id = as2.id"
		// + " WHERE aa.deleted = FALSE "
		// + " AND as2.deleted = FALSE "
		// + " AND DATE(aa.lnReceiptDate) >= :dateFrom "
		// + " AND DATE(aa.lnReceiptDate) < :dateTo "
		// + " GROUP BY jt.id "
		// + " ORDER BY as2.name ; ");

		// show all sbu even no record
		StringBuilder sql = new StringBuilder("SELECT "
				+ " as2.id,"
				+ " as2.name,"
				+ " CAST(SUM(IFNULL(temp.count, 0)) AS SIGNED) as count"
				+ " FROM apr_sbu as2"
				+ " LEFT JOIN "
				+ " ( SELECT "
				+ " jt.id, "
				+ " COUNT(*) AS count"
				+ " FROM apr_appreciation aa, "
				+ " 	 JSON_TABLE(aa.sbuIds, '$[*]' COLUMNS ( "
				+ " 		 id INT PATH '$.id' "
				+ " 	 )) AS jt"
				+ " WHERE aa.deleted = FALSE"
				+ " AND DATE(aa.lnReceiptDate) >= :dateFrom "
				+ " AND DATE(aa.lnReceiptDate) < :dateTo "
				+ " GROUP BY jt.id) temp ON temp.id = as2.id"
				+ " WHERE  as2.deleted = false"
				+ " GROUP BY as2.id"
				+ " ORDER BY as2.name");

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

	public List<Map<String, Object>> getReportDetailAppreciation(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT "
				+ " GROUP_CONCAT(as2.name SEPARATOR ', ') AS sbuNames, "
				+ " aa.* "
				+ " FROM apr_appreciation aa, "
				+ " 	 JSON_TABLE(aa.sbuIds, '$[*]' COLUMNS ( "
				+ " 		 id INT PATH '$.id' "
				+ " 	 )) AS jt "
				+ " LEFT JOIN apr_sbu as2 ON jt.id = as2.id "
				+ " WHERE aa.deleted = FALSE  "
				+ " AND DATE(aa.lnReceiptDate) >= :dateFrom "
				+ " AND DATE(aa.lnReceiptDate) < :dateTo "
				+ " GROUP BY aa.id ");

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

	public List<Map<String, Object>> getReportSummaryRecord(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT "
				+ " aa.description, "
				+ " aa.lnReceiptDate, "
				+ " ac.name AS category "
				+ " FROM apr_appreciation aa  "
				+ " LEFT JOIN apr_category ac ON ac.id = aa.aprCategoryId  "
				+ " WHERE aa.deleted = FALSE  "
				+ " AND MONTH(aa.lnReceiptDate) = :month "
				+ " AND YEAR(aa.lnReceiptDate) = :year ");

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

	public byte[] generateCaseReport(LocalDate dateFrom, LocalDate dateTo)
			throws IOException, Docx4JException, JAXBException {
		LocalDate curMonthDateFrom = dateFrom.withDayOfMonth(1);
		LocalDate curMonthDateTo = dateFrom.withDayOfMonth(dateFrom.lengthOfMonth());

		LocalDate prevMonthDateFrom = curMonthDateFrom.minusMonths(1);
		LocalDate prevMonthDateTo = prevMonthDateFrom.withDayOfMonth(prevMonthDateFrom.lengthOfMonth());

		Integer year = dateFrom.getYear();
		Integer lastTwoDigits = year % 100;

		LocalDate yearlyDateFrom;
		LocalDate yearlyDateTo;

		String finYear = "";
		Integer reportMonth = dateFrom.getMonthValue();
		if (reportMonth <= 3) {
			// last financial year
			yearlyDateFrom = LocalDate.of(curMonthDateFrom.getYear() - 1, 4, 1);
			yearlyDateTo = LocalDate.of(curMonthDateFrom.getYear(), 3, 31);
			finYear = String.format("%d/%02d", year - 1, lastTwoDigits);
		} else {
			// this financial year
			yearlyDateFrom = LocalDate.of(curMonthDateFrom.getYear(), 4, 1);
			yearlyDateTo = LocalDate.of(curMonthDateFrom.getYear() + 1, 3, 31);
			finYear = String.format("%d/%02d", year, lastTwoDigits + 1);
		}

		String formattedDateFrom = dateFrom.getMonth().getDisplayName(TextStyle.FULL, Locale.ENGLISH) + " "
				+ dateFrom.getYear();

		List<Map<String, Object>> recordCount = getAppreciationsCaseCount(
				Map.of(
						"dateFrom", curMonthDateFrom,
						"dateTo", curMonthDateTo.plusDays(1)));

		List<Map<String, Object>> prevMonthRecordCount = getAppreciationsCaseCount(
				Map.of(
						"dateFrom", prevMonthDateFrom,
						"dateTo", prevMonthDateTo.plusDays(1)));

		// Mabel said that the summary should be cumulative instead of current month
		// during 6/5/2024 meeting
		List<Map<String, Object>> cumulativeRecordCount = getAppreciationsCaseCount(
				Map.of(
						"dateFrom", yearlyDateFrom,
						"dateTo", curMonthDateTo.plusDays(1)));

		List<Map<String, Object>> yearlyRecordCount = getAppreciationSBUCountByPeriod(
				Map.of(
						"dateFrom", yearlyDateFrom,
						"dateTo", yearlyDateTo.plusDays(1)));

		long cumulativeCount = 0L;
		Long count = 0L;
		Long prevCount = 0L;
		Long yearlyCount = 0L;

		List<Map<String, Object>> catBreakDowns = new ArrayList<>();

		for (Map<String, Object> record : recordCount) {
			count += (Long) record.get("appreciationCount");
		}

		for (Map<String, Object> record : prevMonthRecordCount) {
			prevCount += (Long) record.get("appreciationCount");
		}

		for (Map<String, Object> record : cumulativeRecordCount) {
			cumulativeCount += (Long) record.get("appreciationCount");
			catBreakDowns.add(
					Map.of(
							"catTotal", String.format("%s (%d)",
									NumberUtils.convertToWord(((Long) record.get("appreciationCount")).intValue()),
									((Long) record.get("appreciationCount")).intValue()),
							"catName", record.get("name")));
		}

		List<String> sbuList = new ArrayList<>();
		for (Map<String, Object> record : yearlyRecordCount) {
			yearlyCount += (Long) record.get("count");
			sbuList.add((String) record.get("name"));
		}

		Long difference = Math.abs(count - prevCount);
		String compareStr1 = count > prevCount ? "more than" : count == prevCount ? "equal to" : "less than";
		String compareStr2 = count == prevCount ? ""
				: String.format("by %s", NumberUtils.convertToWord(difference.intValue()));

		List<AppreciationChartDataDao> chartList = new ArrayList<>();
		for (String sbuName : sbuList) {
			List<Map<String, Object>> chartRecord = getAppreciationMonthlyCountByName(
					Map.of(
							"dateFrom", yearlyDateFrom,
							"dateTo", yearlyDateTo.plusDays(1),
							"name", sbuName));
			AppreciationChartDataDao chartDataDao = new AppreciationChartDataDao(sbuName);
			for (Map<String, Object> monthlyRecord : chartRecord) {
				if (((Integer) monthlyRecord.get("recordYear")) == yearlyDateFrom.getYear()) {
					// 4,5,6,7,8,9,10,11,12 case, -4
					chartDataDao.setRecordByMonthByIndex(((Long) monthlyRecord.get("count")).intValue(),
							(Integer) monthlyRecord.get("recordMonth") - 4);
				} else {
					// 1,2,3 case, +8
					chartDataDao.setRecordByMonthByIndex(((Long) monthlyRecord.get("count")).intValue(),
							(Integer) monthlyRecord.get("recordMonth") + 8);
				}
			}
			// chartDataDao.asAccumulate();
			chartList.add(chartDataDao);
		}
		Collections.reverse(chartList);

		final DateTimeFormatter dateFormat = DateTimeFormatter.ofPattern("dd MMM YYYY");
		String lnReceiptDateRange = dateFrom.format(dateFormat) + " - " + dateTo.minusDays(1).format(dateFormat);

		List<Map<String, Object>> appreciationRecordDetail = getReportDetailAppreciation(
				Map.of(
						"dateFrom", dateFrom,
						"dateTo", dateTo));

		AppreciationCaseReportDao reportDao = new AppreciationCaseReportDao(
				formattedDateFrom, cumulativeCount, cumulativeRecordCount, catBreakDowns,
				compareStr1, compareStr2,
				finYear, yearlyCount, yearlyRecordCount, sbuList, chartList,
				lnReceiptDateRange, appreciationRecordDetail);
		byte[] reportResult = wordReportService.generateAppreciationCasesXMLFile(reportDao);

		return convertXMLtoDocx(reportResult);
	}

	public byte[] generateSummaryReport(Integer year, Integer month)
			throws IOException, Docx4JException, JAXBException {

		List<Map<String, Object>> reportRecord = getReportSummaryRecord(Map.of("year", year, "month", month));
		final DateTimeFormatter dateFormat = DateTimeFormatter.ofPattern("MMM YYYY");
		String reportHeading = LocalDate.of(year, month, 1).format(dateFormat);
		byte[] reportResult = wordReportService.generateAppreciationSummaryXMLFile(reportHeading, reportRecord);

		return convertXMLtoDocx(reportResult);
	}

	public byte[] generateImportErrorReport(List<ImportErrorRecord> record) throws IOException {
		byte[] reportResult = excelReportService.generateAppreciationImportErrorReport(record);
		return reportResult;
	}

	// Object for Import Template
	class SbuIdObject {
		private Long id;

		public SbuIdObject(Long id) {
			this.id = id;
		}

		public Long getId() {
			return id;
		}

		public void setId(Long id) {
			this.id = id;
		}
	}

	public List<Map<String, Object>> getAppreciationYearCombo() {
		StringBuilder sql = new StringBuilder("SELECT "
				+ " DISTINCT year(aa.lnReceiptDate) AS id, "
				+ " CAST(YEAR(aa.lnReceiptDate) AS CHAR(4)) AS label "
				+ " FROM apr_appreciation aa  "
				+ " WHERE aa.deleted = FALSE  "
				+ " ORDER BY id DESC  ");

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

	private byte[] convertXMLtoDocx(byte[] xmlByte) throws Docx4JException {
		ByteArrayOutputStream docxOutputStream = new ByteArrayOutputStream();
		WordprocessingMLPackage wmlPackage = Docx4J.load(new ByteArrayInputStream(xmlByte));
		Docx4J.save(wmlPackage, docxOutputStream, Docx4J.FLAG_SAVE_ZIP_FILE);
		return docxOutputStream.toByteArray();
	}
}
