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

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Date;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.BeanUtils;
import com.ffii.core.utils.Params;
import com.ffii.lioner.modules.common.SecurityUtils;
import com.ffii.lioner.modules.common.service.AuditLogService;
import com.ffii.lioner.modules.lioner.client.entity.Client;
import com.ffii.lioner.modules.lioner.client.entity.ClientRepository;
import com.ffii.lioner.modules.lioner.client.req.UpdateClientReq;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*; 

@Service
public class ClientService extends AbstractBaseEntityService<Client, Long, ClientRepository> {

	private AuditLogService auditLogService;

	public ClientService(JdbcDao jdbcDao, ClientRepository 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 Map<String, Object> saveOrUpdate(UpdateClientReq req) {
		Client instance;
		
		if (req.getId() > 0){
			instance = find(req.getId()).get();
		}
		else {
			instance = new Client();
			instance.setClientCode(getNewClientCode());
		}

		BeanUtils.copyProperties(req,instance);

		instance = save(instance);

		logger.info("instance.getMortgagesCurrent():" + instance.getMortgagesCurrent());
		logger.info("instance.getPhone1Code():" + instance.getPhone1Code());

		Map<String, Object>  m = Map.of(
			"id", instance.getId()
		);
						
		return m;
	}

	public List<Map<String,Object>> list(Map<String, Object> args){
			long currentUserId = SecurityUtils.getUser().get().getId();
			args.put("currentUserId", currentUserId);

			StringBuilder sql = new StringBuilder("SELECT"
				+ " (select fullname from user where id = client.createdBy) as createdByName, "
				+ " id, "
				+ " clientCode, "
				+ " fullname, "
				+ " lastname, "
				+ " firstname, "
				+ " title, "
				+ " email, "
				+ " phone1, "
				+ " phone2, "
				+ " remarks, "
				+ " created, "
				+ " dob, "
				+ " caseManagerId, "
				+ " consultantId "
				+ " FROM client "
				+ " WHERE deleted = FALSE "

				+ " AND ( "
				+ " 	NOT EXISTS (SELECT 1 FROM user_consultant WHERE userId = :currentUserId) "
				+ " 	OR client.consultantId IN (SELECT consultantId FROM user_consultant WHERE userId = :currentUserId) "
				+ " ) "

		);

		if (args != null) {
			if (args.containsKey("clientCode")) 
				sql.append(" AND clientCode LIKE :clientCode ");
			if (args.containsKey("lastname")) 
				sql.append(" AND lastname LIKE :lastname ");
			if (args.containsKey("firstname")) 
				sql.append(" AND firstname LIKE :firstname ");
			if (args.containsKey("phone1")) 
				sql.append(" AND phone1 LIKE :phone1 ");
			if (args.containsKey("phone2")) 
				sql.append(" AND phone2 LIKE :phone2 ");
			if (args.containsKey("email")) 
				sql.append(" AND email LIKE :email ");
			if (args.containsKey("remarks")) 
				sql.append(" AND remarks LIKE :remarks ");
			}
			if (args.containsKey("consultantId") && args.get("consultantId") != null) {
            	sql.append(" AND consultantId = :consultantId ");
        	}

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

	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));
    }

	public String getNewClientCode() {
		int year = LocalDate.now().getYear();

        String sql = "select "
            + " COUNT(1) + 1 "
            + " FROM client "         
			+ " WHERE YEAR(created) = :year "            
        ;
        
        int idCount = jdbcDao.queryForInt(sql, Map.of("year", year));
        String code = year + String.format("%04d", idCount);  

        return code;
    }
	
	public void generateClientConsultantReport(Map<String, Object> args, OutputStream outputStream) throws IOException {
		// Using your existing SQL
		String sql = "SELECT " +
					" concat( ifnull(c.lastname, ''), ' ', ifnull(c.firstname,'') ) as clientName, " +
					" ifnull(ct.name, '') as consultantName, " +
					" c.* " +
					" FROM client c " +
					" LEFT JOIN consultant ct ON c.consultantId = ct.id " +
					" WHERE 1 ";

		if (args.containsKey("fromDate")) sql += " AND c.created >= :fromDate ";
		if (args.containsKey("toDate")) sql += " AND c.created < :toDate ";
		if (args.containsKey("consultantIds")) sql += " AND c.consultantId IN (:consultantIds) ";
        
		List<Map<String, Object>> data = jdbcDao.queryForList(sql, args);

		try (Workbook workbook = new XSSFWorkbook()) {
			Sheet sheet = workbook.createSheet("Report");

			// --- Styles ---
			CellStyle headerStyle = workbook.createCellStyle();
			headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			headerStyle.setBorderBottom(BorderStyle.THIN);
			headerStyle.setBorderTop(BorderStyle.THIN);
			headerStyle.setBorderLeft(BorderStyle.THIN);
			headerStyle.setBorderRight(BorderStyle.THIN);
			
			Font boldFont = workbook.createFont();
			boldFont.setBold(true);
			headerStyle.setFont(boldFont);

			// Summary Style (Bold with borders)
			CellStyle summaryStyle = workbook.createCellStyle();
			summaryStyle.setBorderTop(BorderStyle.DOUBLE); // Double line to separate from data
			summaryStyle.setFont(boldFont);

			// --- Header Row ---
			String[] headers = {"Client Code", "Client Name", "Consultant Name", "Created Date"};
			Row headerRow = sheet.createRow(0);
			for (int i = 0; i < headers.length; i++) {
				Cell cell = headerRow.createCell(i);
				cell.setCellValue(headers[i]);
				cell.setCellStyle(headerStyle);
			}

			// --- Data Rows ---
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");
			int rowIdx = 1;
			for (Map<String, Object> d : data) {
				Row row = sheet.createRow(rowIdx++);

				int j = 0;
				row.createCell(j++).setCellValue(String.valueOf(d.getOrDefault("clientCode", "")));
				row.createCell(j++).setCellValue(String.valueOf(d.getOrDefault("clientName", "")));
				row.createCell(j++).setCellValue(String.valueOf(d.getOrDefault("consultantName", "")));
				
				Object createdObj = d.get("created");
				String dateStr = "";
				if (createdObj instanceof LocalDateTime) {
					dateStr = ((LocalDateTime) createdObj).format(formatter);
				} else if (createdObj != null) {
					dateStr = createdObj.toString();
				}
				row.createCell(j++).setCellValue(dateStr);
			}

			// --- ADD SUMMARY AT THE BOTTOM ---
			// Leave one empty row for better spacing
			rowIdx++; 
			Row summaryRow = sheet.createRow(rowIdx);
			
			// Label: "Total number of clients"
			Cell labelCell = summaryRow.createCell(0);
			labelCell.setCellValue("Total number of clients:");
			labelCell.setCellStyle(summaryStyle);

			// Value: The actual count
			Cell countCell = summaryRow.createCell(1);
			countCell.setCellValue(data.size()); // Size of the result list
			countCell.setCellStyle(summaryStyle);

			// --- Column Widths ---
			for (int i = 0; i < headers.length; i++) {
				sheet.autoSizeColumn(i);
				sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 512);
			}

			workbook.write(outputStream);
		}
	}
	
	
}