package cn.kk.spring_simple_operation;

import cn.kk.spring_simple_operation.entity.*;
import cn.kk.spring_simple_operation.mapper.*;
import cn.kk.spring_simple_operation.model.dto.ImportExcelDto;
import cn.kk.spring_simple_operation.model.dto.RiskCheckAreaExportDTO;
import cn.kk.spring_simple_operation.model.dto.SkuImageDesignDTO;
import cn.kk.spring_simple_operation.model.vo.SkuAPlusImageAndVideoVO;
import cn.kk.spring_simple_operation.utils.ExcelUtil;
import cn.kk.spring_simple_operation.utils.SqlGenerator;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
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.ss.usermodel.WorkbookFactory;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author kk
 * @date 2026/4/27
 */
@SpringBootTest
public class MyTest2 {

	private static final Logger log = LoggerFactory.getLogger(MyTest2.class);
	@Resource
	private APlusProductMapper aPlusProductMapper;

	@Resource
	private VisualCommunicationDesignSecondMapper visualCommunicationDesignSecondMapper;

	@Autowired
	@Resource
	private VideoProductMapper videoProductMapper;

	@Resource
	private VisualVideoPlanMapper visualVideoPlanMapper;

	@Resource
	private VisualAPlusPlanMapper visualAPlusPlanMapper;

	@Resource
	private VisualTaskWeightMapper visualTaskWeightMapper;

	@Resource
	private DeptGroupMapper                    deptGroupMapper;

	@Resource
	private PhotoProgressMapper photoProgressMapper;


	@Test
	public void exportSkuHasAPlus() throws Exception {
		Path inputPath = Paths.get("22864-羊绒商品认证数据确认.xlsx");
		Path outputPath = Paths.get("22864-羊绒商品认证数据确认-是否有A+.xlsx");
		DataFormatter dataFormatter = new DataFormatter();

		try (InputStream inputStream = Files.newInputStream(inputPath);
			 Workbook workbook = WorkbookFactory.create(inputStream)) {
			Sheet sheet = workbook.getSheetAt(0);
			Row headerRow = sheet.getRow(0);
			if (Objects.isNull(headerRow)) {
				throw new IllegalStateException("Excel第一行表头为空");
			}

			int skuColumnIndex = getColumnIndex(headerRow, "SKU", dataFormatter);
			if (skuColumnIndex < 0) {
				throw new IllegalStateException("未找到sku列");
			}
			int siteColumnIndex = getColumnIndex(headerRow, "站点", dataFormatter);
			if (siteColumnIndex < 0) {
				throw new IllegalStateException("未找到站点列");
			}

			int resultColumnIndex = getColumnIndex(headerRow, "是否有A+", dataFormatter);
			if (resultColumnIndex < 0) {
				resultColumnIndex = headerRow.getLastCellNum();
				if (resultColumnIndex < 0) {
					resultColumnIndex = 0;
				}
			}

			Cell resultHeaderCell = getOrCreateCell(headerRow, resultColumnIndex);
			resultHeaderCell.setCellValue("是否有A+");
			if (resultColumnIndex > 0) {
				copyCellStyle(workbook, headerRow.getCell(resultColumnIndex - 1), resultHeaderCell);
			}

			Set<String> skuSet = new LinkedHashSet<>();
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				if (Objects.isNull(row)) {
					continue;
				}
				String sku = getCellString(row.getCell(skuColumnIndex), dataFormatter);
				if (!StringUtils.isEmpty(sku)) {
					skuSet.add(sku);
				}
			}

			Set<String> hasAPlusSkuSiteSet = new HashSet<>();
			Lists.partition(new ArrayList<>(skuSet), 500).forEach(skuList -> {
				List<APlusProduct> aPlusProductList = aPlusProductMapper.selectList(Wrappers.<APlusProduct>lambdaQuery()
								.in(APlusProduct::getSku, skuList)
								.select(APlusProduct::getSku, APlusProduct::getSite));
				aPlusProductList.forEach(aPlusProduct -> hasAPlusSkuSiteSet.add(buildSkuSiteKey(aPlusProduct.getSku(), aPlusProduct.getSite())));
			});

			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				if (Objects.isNull(row)) {
					continue;
				}
				String sku = getCellString(row.getCell(skuColumnIndex), dataFormatter);
				String site = getCellString(row.getCell(siteColumnIndex), dataFormatter);
				Cell resultCell = getOrCreateCell(row, resultColumnIndex);
				if (resultColumnIndex > 0) {
					copyCellStyle(workbook, row.getCell(resultColumnIndex - 1), resultCell);
				}
				resultCell.setCellValue(!StringUtils.isEmpty(sku) && !StringUtils.isEmpty(site) && hasAPlusSkuSiteSet.contains(buildSkuSiteKey(sku, site)) ? "是" : "否");
			}

			sheet.autoSizeColumn(resultColumnIndex);
			try (OutputStream outputStream = Files.newOutputStream(outputPath)) {
				workbook.write(outputStream);
			}
		}

		log.info("导出完成：{}", outputPath.toAbsolutePath());
	}

	@Test
	public void dealSimpleAPlusPlanTime() {
		ArrayList<String> skuList = Lists.newArrayList("STY0089", "IFS2835", "DF1061", "IF2216", "VEA0926", "BV4469", "STY1329", "TAY0257", "LOO2703", "XEU1036", "LOM0105", "XUL0975", "UR1221", "ISI0782", "LIC9145", "HOD5141", "LEM6389", "DF4494", "WAR1487", "PIR5107", "QUN2115", "ANB2765", "JY1066", "RY3111", "SIG0377", "TEA2739", "XEU1563", "YOG1732", "NEV2980", "IG2159", "FLO3335", "ZHI0065", "MZ2724", "JUU0439", "HOD2404", "DF2869", "MM0881", "DUN3755", "HAI6132", "JYY2734", "GUR0168", "HUM1645", "GUU2335", "QUQ1018", "GUR3956", "CAS3083", "GOR2056", "QUN3648", "XIN2813", "FS2247", "HUI2255", "YUN2250", "XAO3558", "YOF1784", "JIA0971", "GUI0229", "GOO2940");
		List<VisualAPlusPlan> visualAPlusPlans = visualAPlusPlanMapper.selectList(Wrappers.<VisualAPlusPlan>lambdaQuery().in(VisualAPlusPlan::getSku, skuList).select(VisualAPlusPlan::getId, VisualAPlusPlan::getSku));
		List<String> weightIds = new ArrayList<>();
		List<String> planIds = new ArrayList<>();

		visualAPlusPlans.forEach(item -> {
			VisualTaskWeight weight = visualTaskWeightMapper.selectOne(Wrappers.<VisualTaskWeight>lambdaQuery().eq(VisualTaskWeight::getTaskId, item.getId()).eq(VisualTaskWeight::getTaskType, 1).orderByAsc(VisualTaskWeight::getCreateTime).last("limit 1"));
			if (Objects.isNull(weight)) return;
			planIds.add(String.valueOf(item.getId()));
			weightIds.add(String.valueOf(weight.getId()));
		});

		log.info("update visual_a_plus_plan set finish_time = 1774101255 where id in ({});", String.join(",", planIds));
		log.info("update visual_task_weight set weight = 1.5, task_time = 1774101255 where id in ({});", String.join(",", weightIds));

	}

	@Test
	public void exportSkuImage() throws Exception {
		ExcelUtil<SkuAPlusImageAndVideoVO> util = new ExcelUtil<>(SkuAPlusImageAndVideoVO.class);
		List<SkuAPlusImageAndVideoVO> list = util.importExcel(Files.newInputStream(new File("21716-珠宝首饰产品前台图片、A＋和视频导出(1).xlsx").toPath()));

		//list = list.stream().filter(t -> "ST2177".equals(t.getSku())).collect(Collectors.toList());

		Lists.partition(list, 1000)
				.forEach(ll -> {
					List<String> skuList = ll.stream().map(SkuAPlusImageAndVideoVO::getSku).collect(Collectors.toList());
					List<APlusProduct> aPlusProducts = aPlusProductMapper.selectList(Wrappers.<APlusProduct>lambdaQuery().in(APlusProduct::getSku, skuList).select(APlusProduct::getSku, APlusProduct::getSite, APlusProduct::getCommunicationDesignId, APlusProduct::getIsUploadBackstage, APlusProduct::getCreateTime));

					Set<Long> cIds = aPlusProducts.stream().map(APlusProduct::getCommunicationDesignId).collect(Collectors.toSet());
					Map<Long, String> imageMap = visualCommunicationDesignSecondMapper.selectList(Wrappers.<VisualCommunicationDesign>lambdaQuery().in(VisualCommunicationDesign::getId, cIds).select(VisualCommunicationDesign::getId, VisualCommunicationDesign::getFileUrl))
							.stream().collect(Collectors.toMap(VisualCommunicationDesign::getId, VisualCommunicationDesign::getFileUrl));

					Map<String, List<APlusProduct>> skuSiteAPlusMap = aPlusProducts.stream().collect(Collectors.groupingBy(t -> t.getSku() + "-" + t.getSite()));

					for (SkuAPlusImageAndVideoVO dto : ll) {
						List<APlusProduct> plusProducts = skuSiteAPlusMap.get(dto.getSku() + "-" + dto.getSite());
						if (CollectionUtils.isEmpty(plusProducts)) continue;
						plusProducts.sort((o1, o2) -> o2.getCreateTime().compareTo(o1.getCreateTime()));
						for (APlusProduct plusProduct : plusProducts) {
							String image = imageMap.get(plusProduct.getCommunicationDesignId());
							if (!StringUtils.isEmpty(image)) {
								dto.setAPlusImage(image);
								break;
							}
						}
					}


					List<VideoProduct> videoProducts = videoProductMapper.selectList(Wrappers.<VideoProduct>lambdaQuery().in(VideoProduct::getSku, skuList).in(VideoProduct::getOptimizeType, 0, 1).select(VideoProduct::getSku, VideoProduct::getSite, VideoProduct::getVideoPlanId, VideoProduct::getVideoIsUploadBackstage, VideoProduct::getCreateTime));

					Set<Long> planIds = videoProducts.stream().map(VideoProduct::getVideoPlanId).collect(Collectors.toSet());
					Map<Long, String> videoMap = visualVideoPlanMapper.selectList(Wrappers.<VisualVideoPlan>lambdaQuery().in(VisualVideoPlan::getId, planIds).select(VisualVideoPlan::getId, VisualVideoPlan::getLongVideoUrl))
							.stream().collect(Collectors.toMap(VisualVideoPlan::getId, VisualVideoPlan::getLongVideoUrl));
					Map<String, List<VideoProduct>> skuSiteVideoMap = videoProducts.stream().collect(Collectors.groupingBy(t -> t.getSku() + "-" + t.getSite()));

					for (SkuAPlusImageAndVideoVO dto : ll) {
						List<VideoProduct> videoProductList = skuSiteVideoMap.get(dto.getSku() + "-" + dto.getSite());
						if (CollectionUtils.isEmpty(videoProductList)) continue;
						videoProductList.sort((o1, o2) -> o2.getCreateTime().compareTo(o1.getCreateTime()));
						for (VideoProduct videoProduct : videoProductList) {
							String video = videoMap.get(videoProduct.getVideoPlanId());
							if (!StringUtils.isEmpty(video)) {
								dto.setVideoUrl(video);
								break;
							}
						}
					}

				});

		util = new ExcelUtil<SkuAPlusImageAndVideoVO>(SkuAPlusImageAndVideoVO.class);
		util.exportExcelFile(list, "21716-珠宝首饰产品前台图片、A＋和视频导出-.xlsx");

	}

	@Test
	public void test() throws Exception {

		Map<String, List<DeptGroup>> listMap = deptGroupMapper.selectList(Wrappers.<DeptGroup>lambdaQuery()
				.in(DeptGroup::getModule, "wal_develop", "plan_design")
				.notLike(DeptGroup::getFullName, "研发二部")
				.select(DeptGroup::getModule, DeptGroup::getGroupNum)
		).stream().collect(Collectors.groupingBy(DeptGroup::getModule));
		List<String> walDevelop = listMap.get("wal_develop").stream().map(t -> t.getGroupNum().toLowerCase(Locale.ROOT)).collect(Collectors.toList());
		List<String> planDesign = listMap.get("plan_design").stream().map(t -> t.getGroupNum().toLowerCase(Locale.ROOT)).collect(Collectors.toList());

		ExcelUtil<ImportExcelDto> util = new ExcelUtil<>(ImportExcelDto.class);
		List<ImportExcelDto> list = util.importExcel(Files.newInputStream(new File("2026-04-30_vzdi_推送对接表.xlsx").toPath()));

		List<PlaneDesignDeveloperDept> collect = list.stream().map(t -> {
			List<String> developerGroupList = walDevelop.stream().filter(group -> group.endsWith(t.getDeveloperGroup().toLowerCase(Locale.ROOT))).collect(Collectors.toList());
			if (CollectionUtils.isEmpty(developerGroupList)) {
				log.warn("developerGroupList.isEmpty(), {}", t.getDeveloperGroup());
				return null;
			}
			if (developerGroupList.size() > 1) {
				//log.warn("developerGroupList.size() > 1, {}", t.getDeveloperGroup());
				//return null;
			}

			List<String> planDesignGroupList = planDesign.stream().filter(group -> group.endsWith(t.getPlaneDesignGroup().toLowerCase(Locale.ROOT))).collect(Collectors.toList());
			if (CollectionUtils.isEmpty(planDesignGroupList)) {
				log.warn("planDesignGroupList.isEmpty(), {}", t.getPlaneDesignGroup());
				return null;
			}
			if (planDesignGroupList.size() > 1) {
				//log.warn("planDesignGroupList.size() > 1, {}", t.getPlaneDesignGroup());
				//return null;
			}

			PlaneDesignDeveloperDept planeDesignDeveloperDept = new PlaneDesignDeveloperDept();
			planeDesignDeveloperDept.setPlaneDesignGroup(planDesignGroupList.get(0).toUpperCase(Locale.ROOT));
			planeDesignDeveloperDept.setDeveloperGroup(developerGroupList.get(0).toUpperCase(Locale.ROOT));
			planeDesignDeveloperDept.setPushMsgToUser(t.getPushMsgToUser());
			return planeDesignDeveloperDept;
		}).filter(Objects::nonNull).collect(Collectors.toList());

		for (PlaneDesignDeveloperDept planeDesignDeveloperDept : collect) {
			String insertSql = SqlGenerator.generateInsertSql(planeDesignDeveloperDept);
			System.out.println(insertSql);
		}

	}


	@Test
	public void exportWoolRiskCheckArea() {
		List<VisualAPlusPlan> planList = visualAPlusPlanMapper.selectList(Wrappers.<VisualAPlusPlan>lambdaQuery()
				.eq(VisualAPlusPlan::getIsDelete, 0)
				.and(wrapper -> wrapper
						.like(VisualAPlusPlan::getRiskCheckArea, "wool")
						.or().like(VisualAPlusPlan::getRiskCheckArea, "Cashmere")
						.or().like(VisualAPlusPlan::getRiskCheckArea, "kashmir")
						.or().like(VisualAPlusPlan::getRiskCheckArea, "pashmina"))
				.select(VisualAPlusPlan::getId, VisualAPlusPlan::getRiskCheckArea));

		Map<Long, String> planRiskMap = planList.stream()
				.collect(Collectors.toMap(VisualAPlusPlan::getId, VisualAPlusPlan::getRiskCheckArea, (a, b) -> a));

		List<RiskCheckAreaExportDTO> resultList = new ArrayList<>();
		Lists.partition(new ArrayList<>(planRiskMap.keySet()), 500).forEach(planIdList -> {
			List<APlusProduct> aPlusProducts = aPlusProductMapper.selectList(Wrappers.<APlusProduct>lambdaQuery()
					.in(APlusProduct::getAPlusPlanId, planIdList)
					.eq(APlusProduct::getIsUploadBackstage, 2)
					.eq(APlusProduct::getIsDelete, 0)
					.select(APlusProduct::getSku, APlusProduct::getSite, APlusProduct::getAPlusPlanId, APlusProduct::getOptimizeType));
			for (APlusProduct aPlusProduct : aPlusProducts) {
				RiskCheckAreaExportDTO dto = new RiskCheckAreaExportDTO();
				dto.setSku(aPlusProduct.getSku());
				dto.setSite(aPlusProduct.getSite());
				dto.setOptimizeType(aPlusProduct.getOptimizeType());
				String riskCheckArea = planRiskMap.get(aPlusProduct.getAPlusPlanId());
				dto.setRiskCheckArea(riskCheckArea);
				dto.setKeywords(extractRiskKeywords(riskCheckArea));
				resultList.add(dto);
			}
		});

		ExcelUtil<RiskCheckAreaExportDTO> util = new ExcelUtil<>(RiskCheckAreaExportDTO.class);
		util.exportExcelFile(resultList, "羊绒风险A+产品检测sku导出.xlsx");

		log.info("导出完成，共 {} 条", resultList.size());
	}

	@Test
	public void exportSkuInfringementKeywords() throws Exception {
		Path inputPath = Paths.get("2026-06-12_vtot_羊绒商品认证数据确认.xlsx");
		Path outputPath = Paths.get("2026-06-12_vtot_羊绒商品认证数据确认-侵权词.xlsx");
		DataFormatter dataFormatter = new DataFormatter();

		try (InputStream inputStream = Files.newInputStream(inputPath);
			 Workbook workbook = WorkbookFactory.create(inputStream)) {
			Sheet sheet = workbook.getSheetAt(0);
			Row headerRow = sheet.getRow(0);
			if (Objects.isNull(headerRow)) {
				throw new IllegalStateException("Excel第一行表头为空");
			}

			int skuColumnIndex = getColumnIndex(headerRow, "SKU", dataFormatter);
			if (skuColumnIndex < 0) {
				throw new IllegalStateException("未找到SKU列");
			}
			int hasInfringementColumnIndex = getOrCreateColumnIndex(headerRow, "A+页面是否涉及侵权词", dataFormatter);
			int infringementKeywordsColumnIndex = getOrCreateColumnIndex(headerRow, "侵权词", dataFormatter);

			Set<String> skuSet = new LinkedHashSet<>();
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				if (Objects.isNull(row)) {
					continue;
				}
				String sku = getCellString(row.getCell(skuColumnIndex), dataFormatter);
				if (!StringUtils.isEmpty(sku)) {
					skuSet.add(sku);
				}
			}

			Map<String, Set<Long>> skuPlanIdsMap = new HashMap<>();
			Set<Long> allPlanIds = new HashSet<>();
			Lists.partition(new ArrayList<>(skuSet), 500).forEach(skuList -> {
				List<APlusProduct> aPlusProducts = aPlusProductMapper.selectList(Wrappers.<APlusProduct>lambdaQuery()
						.in(APlusProduct::getSku, skuList)
						.select(APlusProduct::getSku, APlusProduct::getAPlusPlanId));
				for (APlusProduct aPlusProduct : aPlusProducts) {
					if (Objects.isNull(aPlusProduct.getAPlusPlanId())) {
						continue;
					}
					skuPlanIdsMap.computeIfAbsent(aPlusProduct.getSku(), k -> new HashSet<>()).add(aPlusProduct.getAPlusPlanId());
					allPlanIds.add(aPlusProduct.getAPlusPlanId());
				}
			});

			Map<Long, String> planRiskMap = new HashMap<>();
			Lists.partition(new ArrayList<>(allPlanIds), 500).forEach(planIdList -> {
				List<VisualAPlusPlan> plans = visualAPlusPlanMapper.selectList(Wrappers.<VisualAPlusPlan>lambdaQuery()
						.in(VisualAPlusPlan::getId, planIdList)
						.select(VisualAPlusPlan::getId, VisualAPlusPlan::getRiskCheckArea));
				for (VisualAPlusPlan plan : plans) {
					planRiskMap.put(plan.getId(), plan.getRiskCheckArea());
				}
			});

			Map<String, String> skuKeywordsMap = new HashMap<>();
			for (Map.Entry<String, Set<Long>> entry : skuPlanIdsMap.entrySet()) {
				Set<String> hitKeywords = new LinkedHashSet<>();
				for (Long planId : entry.getValue()) {
					String keywords = extractRiskKeywords(planRiskMap.get(planId));
					if (!StringUtils.isEmpty(keywords)) {
						hitKeywords.addAll(Arrays.asList(keywords.split(",")));
					}
				}
				if (!hitKeywords.isEmpty()) {
					skuKeywordsMap.put(entry.getKey(), String.join(",", hitKeywords));
				}
			}

			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				Row row = sheet.getRow(i);
				if (Objects.isNull(row)) {
					continue;
				}
				String sku = getCellString(row.getCell(skuColumnIndex), dataFormatter);
				String keywords = StringUtils.isEmpty(sku) ? "" : skuKeywordsMap.getOrDefault(sku, "");
				boolean hasInfringement = !StringUtils.isEmpty(keywords);

				Cell hasCell = getOrCreateCell(row, hasInfringementColumnIndex);
				if (hasInfringementColumnIndex > 0) {
					copyCellStyle(workbook, row.getCell(hasInfringementColumnIndex - 1), hasCell);
				}
				hasCell.setCellValue(hasInfringement ? "是" : "否");

				Cell kwCell = getOrCreateCell(row, infringementKeywordsColumnIndex);
				if (infringementKeywordsColumnIndex > 0) {
					copyCellStyle(workbook, row.getCell(infringementKeywordsColumnIndex - 1), kwCell);
				}
				kwCell.setCellValue(keywords == null ? "" : keywords);
			}

			sheet.autoSizeColumn(hasInfringementColumnIndex);
			sheet.autoSizeColumn(infringementKeywordsColumnIndex);

			try (OutputStream outputStream = Files.newOutputStream(outputPath)) {
				workbook.write(outputStream);
			}
		}

		log.info("导出完成：{}", outputPath.toAbsolutePath());
	}

	@Test
	public void exportSkuIsMakePic() throws Exception {
		ExcelUtil<SkuImageDesignDTO> util = new ExcelUtil<>(SkuImageDesignDTO.class);
		List<SkuImageDesignDTO> list = util.importExcel(Files.newInputStream(new File("sku-是否作图.xlsx").toPath()));

		List<Integer> productIds = list.stream().map(SkuImageDesignDTO::getProductId).collect(Collectors.toList());
		Map<Integer, Integer> idMap = new HashMap<>(productIds.size());

		Lists.partition(productIds, 200).forEach(productIdList -> {
			Map<Integer, Integer> map = photoProgressMapper.selectList(Wrappers.<PhotoProgress>lambdaQuery().in(PhotoProgress::getProductId, productIdList).select(PhotoProgress::getProductId, PhotoProgress::getProjectSaleFileStatus))
					.stream()
					.collect(Collectors.toMap(PhotoProgress::getProductId, PhotoProgress::getProjectSaleFileStatus));
			idMap.putAll(map);
		});

		list.forEach(item -> {
			item.setProjectSaleFileStatus(idMap.get(item.getProductId()));
		});

		util = new ExcelUtil<SkuImageDesignDTO>(SkuImageDesignDTO.class);
		util.exportExcelFile(list, "sku-是否作图2.xlsx");

	}


	private int getColumnIndex(Row headerRow, String columnName, DataFormatter dataFormatter) {
		for (int i = 0; i < headerRow.getLastCellNum(); i++) {
			String cellValue = getCellString(headerRow.getCell(i), dataFormatter);
			if (columnName.equalsIgnoreCase(cellValue)) {
				return i;
			}
		}
		return -1;
	}

	private int getOrCreateColumnIndex(Row headerRow, String columnName, DataFormatter dataFormatter) {
		int existingIndex = getColumnIndex(headerRow, columnName, dataFormatter);
		if (existingIndex >= 0) {
			return existingIndex;
		}
		int newIndex = headerRow.getLastCellNum();
		if (newIndex < 0) {
			newIndex = 0;
		}
		Cell headerCell = getOrCreateCell(headerRow, newIndex);
		headerCell.setCellValue(columnName);
		if (newIndex > 0) {
			copyCellStyle(headerRow.getSheet().getWorkbook(), headerRow.getCell(newIndex - 1), headerCell);
		}
		return newIndex;
	}

	private String getCellString(Cell cell, DataFormatter dataFormatter) {
		if (Objects.isNull(cell)) {
			return "";
		}
		if (CellType.STRING.equals(cell.getCellType())) {
			return cell.getStringCellValue().trim();
		}
		return dataFormatter.formatCellValue(cell).trim();
	}

	private Cell getOrCreateCell(Row row, int columnIndex) {
		Cell cell = row.getCell(columnIndex);
		if (Objects.isNull(cell)) {
			cell = row.createCell(columnIndex);
		}
		return cell;
	}

	private static final String[] RISK_KEYWORDS = {"wool", "Cashmere", "kashmir", "pashmina"};

	private String extractRiskKeywords(String riskCheckArea) {
		if (StringUtils.isEmpty(riskCheckArea)) {
			return "";
		}
		String lower = riskCheckArea.toLowerCase(Locale.ROOT);
		List<String> hit = new ArrayList<>();
		for (String keyword : RISK_KEYWORDS) {
			if (lower.contains(keyword.toLowerCase(Locale.ROOT))) {
				hit.add(keyword);
			}
		}
		return String.join(",", hit);
	}

	private String buildSkuSiteKey(String sku, String site) {
		return (sku == null ? "" : sku.trim()) + "#" + (site == null ? "" : site.trim());
	}

	private void copyCellStyle(Workbook workbook, Cell sourceCell, Cell targetCell) {
		if (Objects.isNull(sourceCell)) {
			return;
		}
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.cloneStyleFrom(sourceCell.getCellStyle());
		targetCell.setCellStyle(cellStyle);
	}

}
