/*
**************************************************************************************
* Program Name  : DesignationServiceImpl.java
* Author        : Kawade Swapnali
* Date          : Feb 12, 2026
* System Name   : gswbs
* Purpose       : Service implementation for Designation module.
* Called From   : DesignationController
* Beans Accessed: DesignationRepository
**************************************************************************************
*/
package com.coder.designation.serviceImpl;

import java.time.LocalDate;
import java.time.Year;
import lombok.RequiredArgsConstructor;
import java.util.ArrayList;
import java.util.List;
import java.util.NoSuchElementException;
import java.util.Optional;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;
import java.util.Iterator;
import com.coder.designation.model.Designation;
import com.coder.designation.model.SaveDesignationResponse;
import com.coder.designation.repository.DesignationRepository;
import com.coder.designation.service.DesignationService;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
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 java.io.InputStream;
import java.util.HashMap;

@Service
@RequiredArgsConstructor
public class DesignationServiceImpl implements DesignationService{
	
    @Autowired
	private final DesignationRepository designationRepository;

	/* FETCH THE SINGAL DESIGNATION BY DESIGNATION ID AND COMPANY ID */
	@Override
	public Designation getSingleDesignation(String designationId){
		Optional<Designation> designationOp=designationRepository.findById(designationId);
		if(designationOp.isEmpty()) {
			throw new NoSuchElementException("Designation Records Do Not Exist!");
		}
		else {
			return designationOp.get();
			
		}
	}
	
	/* FETCH ALL DESIGNATION */
	@Override
	public List<Designation> fetchAllDesignation(){
		return designationRepository.findAll();
	}
	
	/* FETCH ALL DESIGNATION BY COMPANY ID */
	@Override
	public List<Designation> getAllDesignationByLoginId(String loginId){
		List<Designation> entity=designationRepository.findAllByLoginId(loginId);
		return entity;
		
	}

	/*
	 * SAVE MULTIPAL DESIGNATION AT ONE TIME AND CREATE A EACH DESIGNATION ID AUTOMATICALLY 
	 * BY DES,CURRENT YEAR AND NUMBER FOR EXAMPLE DES/2026/001
	 */
	@Override
	public SaveDesignationResponse saveAllDesignation(List<Designation> designations){
		String prefix="DES";
		int currentYear=Year.now().getValue();
		String idPrefix=prefix +"/" +currentYear+"/";
		List<Designation> saveToDesigantion =new ArrayList<>();
		List<String> dublicateMessages = new ArrayList<>();
		List<String> existingIds = designationRepository.findTopByPrefix(idPrefix, PageRequest.of(0, 1));
		int nextNumber=1;
		if (!existingIds.isEmpty()) {
		    String latestId = existingIds.get(0);   // DEs/2026/001
		    String[] parts = latestId.split("/");

		    if (parts.length == 3) {
		        try {
		            nextNumber = Integer.parseInt(parts[2]) + 1;
		        } catch (NumberFormatException e) {
		            nextNumber = 1;
		        }
		    }
		}
		
		//generate IDS safely Using Increment
	    for (Designation  d : designations) {
	    	   Optional<Designation> exists = designationRepository.checkDuplicateRecords( d.getDesignationName(), d.getLoginId());
	    	if(exists.isPresent()) {
	    		dublicateMessages.add("Already create the "+ d.getDesignationName() + " Designation.");
	    		continue;
	    	}
	        String formattedNumber = String.format("%03d", nextNumber++);
	        d.setDesignationId(idPrefix + formattedNumber);
	        saveToDesigantion.add(d);
	    }

	    List<Designation> saved = designationRepository.saveAll(saveToDesigantion);
	    SaveDesignationResponse response = new SaveDesignationResponse();
	    response.setSaveDesignation(saved);
	    response.setDublicateMessages(dublicateMessages);
	    return response;
	}

	/* UPDATE SINGAL department BY department ID AND COMPANY ID */
	@Override
	public Designation update(String designationId, Designation designation) {
		Optional<Designation> designationOp = designationRepository.findById(designationId);
		if(designationOp.isEmpty()) {
			throw new NoSuchElementException("Designation Records Do Not Exist!");
		}
		else {
			Designation designationdb = designationOp.get();
			designationdb.setDesignationName(designation.getDesignationName());
			designationdb.setCreatedDate(designation.getCreatedDate());
			designationdb.setLoginId(designation.getLoginId());
			designationdb.setStatus(designation.getStatus());
			return designationRepository.saveAndFlush(designationdb);
		}
	}

	/* DELETE MULTIPAL DESIGNATION AT ONE TIME BY DESIGNATION ID LIST */
	@Override
	public boolean multipalDelete(List<String> designationIds) {
		designationRepository.deleteAllById(designationIds);
		return true;
	}
	
	/* IMPORT THE DESIGNATION BY USING EXCEL FILE */
	@Transactional
	@Override
    public List<Designation> importFromExcel(MultipartFile file) throws Exception {
        List<Designation> saved = new ArrayList<>();
        List<String> dublicateMessages = new ArrayList<>();

        try (InputStream is = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(is)) {

            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) return saved;

            Iterator<Row> rowIt = sheet.iterator();
            if (!rowIt.hasNext()) return saved;

            // ✅ Handle null safely
            Row header = rowIt.next();
            Map<String, Integer> colIndex = new HashMap<>();
            for (Cell cell : header) {
                String h = Optional.ofNullable(getCellString(cell))
                        .map(String::trim)
                        .map(String::toLowerCase)
                        .orElse("");
                if (!h.isEmpty()) {
                    colIndex.put(h, cell.getColumnIndex());
                }
            }

            String year = String.valueOf(Year.now().getValue());
            String prefix = "DES/" + year + "/%";
            String maxId = designationRepository.findMaxDesignationIdByPrefix(prefix);
            int seqStart = 0;

            if (maxId != null && !maxId.isBlank()) {
                String[] parts = maxId.split("/");
                try {
                    seqStart = Integer.parseInt(parts[parts.length - 1]);
                } catch (NumberFormatException ignored) {}
            }

            while (rowIt.hasNext()) {
                Row r = rowIt.next();
                if (isRowEmpty(r)) continue;

                String designationId = getCellValueByHeader(r, colIndex, "Designation Id");
                String designationName = getCellValueByHeader(r, colIndex, "Designation Name");
                String designationCreatedDate = getCellValueByHeader(r, colIndex, "Created Date");
                if (designationCreatedDate == null) {
                	designationCreatedDate = getCellValueByHeader(r, colIndex, "CreatedDate");
                }
                String loginId = getCellValueByHeader(r, colIndex, "Login Id");
                String status = getCellValueByHeader(r, colIndex, "Status");

                if ((designationName == null || designationName.isBlank())) continue;

                seqStart++;
                String newDepId = String.format("DES/%s/%03d", year, seqStart);

                Designation e = new Designation();
                e.setDesignationId(newDepId);
                e.setDesignationName(designationName);
                e.setCreatedDate(LocalDate.parse(designationCreatedDate));
                e.setLoginId(loginId);
                e.setStatus(status);

                saved.add(designationRepository.save(e));
            }
        }
        return saved;
    }

    private static String getCellValueByHeader(Row row, Map<String, Integer> map, String headerName) {
        Integer idx = map.get(headerName.toLowerCase());
        if (idx == null) return null;
        return getCellString(row.getCell(idx));
    }

    private static String getCellString(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue().toLocalDate().toString();
                } else {
                    double d = cell.getNumericCellValue();
                    return (d == Math.floor(d)) ? String.valueOf((long) d) : String.valueOf(d);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception ex) {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return null;
        }
    }

    private static boolean isRowEmpty(Row row) {
        for (Cell c : row) {
            String s = getCellString(c);
            if (s != null && !s.trim().isEmpty()) return false;
        }
        return true;
    }
}
