package incheon.com.excel.service.impl;

import incheon.ags.ias.comCd.service.ComCdService;
import incheon.ags.ias.comCd.vo.ComCdVO;
import incheon.com.excel.ExcelExportProperties;
import incheon.com.excel.service.CommonExcelService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.BiFunction;
import java.util.stream.Collectors;

/**
 * 공통 엑셀 다운로드 서비스
 */
@Service("commonExcelService")
@RequiredArgsConstructor
@Slf4j
public class CommonExcelServiceImpl implements CommonExcelService {

    private static final int ROW_ACCESS_WINDOW = 100;

    private final ExcelExportProperties excelProperties;
    private final ComCdService comCdService;

    @Override
    public int exportStream(String fileName, Map<String, Object> columnMap, HttpServletResponse response,
                            BiFunction<Integer, Integer, List<Map<String, Object>>> dataSupplier,
                            int pageSize, int maxRows) throws IOException {

        log.info("엑셀 다운로드 시작 - fileName: {}", fileName);

        // 첫 페이지 데이터 조회 (검증용)
        List<Map<String, Object>> firstPageData = dataSupplier.apply(0, pageSize);
        if (firstPageData == null || firstPageData.isEmpty()) {
            log.warn("엑셀 다운로드 실패: 데이터 없음");
            return 0;
        }

        // 데이터가 있을 때만 응답 헤더 설정
        String downloadFileName = buildFileName(fileName);
        setResponseHeaders(response, downloadFileName);

        // columnMap 파싱: 헤더명, 공통코드 그룹 추출
        ColumnMapInfo columnMapInfo = parseColumnMap(columnMap);

        // 공통코드 캐시 구성 (요청 단위, DB 호출 횟수 = comGroupCd 개수)
        Map<String, Map<String, String>> comCodeCache = buildComCodeCache(columnMapInfo.comGroupCdMap);

        int effectiveMaxRows = maxRows > 0 ? Math.min(maxRows, XLSX_MAX_ROWS - 1) : XLSX_MAX_ROWS - 1;
        int totalRowCount = 0;

        SXSSFWorkbook workbook = null;
        try (OutputStream out = response.getOutputStream()) {
            workbook = new SXSSFWorkbook(ROW_ACCESS_WINDOW);
            workbook.setCompressTempFiles(true);

            SXSSFSheet sheet = workbook.createSheet("Sheet1");
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle textStyle = createTextStyle(workbook);
            CellStyle numberStyle = createNumberStyle(workbook);
            CellStyle intStyle = createIntStyle(workbook);

            List<String> columnKeys = getColumnKeys(firstPageData, columnMapInfo.headerMap);
            int[] maxColumnLengths = new int[columnKeys.size() + 1]; // No 컬럼 포함
            createHeaderRow(sheet, columnKeys, columnMapInfo.headerMap, headerStyle, maxColumnLengths);
            int rowNum = 1;

            // 첫 페이지 데이터 처리
            for (Map<String, Object> rowData : firstPageData) {
                if (totalRowCount >= effectiveMaxRows) {
                    break;
                }
                totalRowCount++;
                SXSSFRow row = sheet.createRow(rowNum++);
                createDataRow(row, totalRowCount, rowData, columnKeys, columnMapInfo.comGroupCdMap, comCodeCache, textStyle, numberStyle, intStyle, maxColumnLengths);
            }

            // 다음 페이지부터 처리
            int pageNo = 1;
            while (totalRowCount < effectiveMaxRows && firstPageData.size() == pageSize) {
                int delayMs = excelProperties.getPageDelayMs();
                if (delayMs > 0) {
                    try {
                        Thread.sleep(delayMs);
                    } catch (InterruptedException e) {
                        Thread.currentThread().interrupt();
                        break;
                    }
                }

                List<Map<String, Object>> pageData = dataSupplier.apply(pageNo, pageSize);
                if (pageData == null || pageData.isEmpty()) {
                    break;
                }

                for (Map<String, Object> rowData : pageData) {
                    if (totalRowCount >= effectiveMaxRows) {
                        log.info("최대 행 수 도달: {}", effectiveMaxRows);
                        break;
                    }
                    totalRowCount++;
                    SXSSFRow row = sheet.createRow(rowNum++);
                    createDataRow(row, totalRowCount, rowData, columnKeys, columnMapInfo.comGroupCdMap, comCodeCache, textStyle, numberStyle, intStyle, maxColumnLengths);
                }

                if (pageData.size() < pageSize) {
                    break;
                }
                pageNo++;
            }

            setColumnWidths(sheet, columnKeys, maxColumnLengths);
            workbook.write(out);
            out.flush();
            log.info("엑셀 다운로드 완료 - fileName: {}, rows: {}", downloadFileName, totalRowCount);

        } catch (Exception e) {
            log.error("엑셀 생성 중 오류", e);
            throw new IOException("엑셀 생성 실패", e);
        } finally {
            if (workbook != null) {
                workbook.dispose();
            }
        }

        return totalRowCount;
    }

    /**
     * columnMap 파싱 결과
     */
    private static class ColumnMapInfo {
        Map<String, String> headerMap = new HashMap<>();      // key -> 헤더명
        Map<String, String> comGroupCdMap = new HashMap<>();  // key -> comGroupCd (있는 경우만)
    }

    /**
     * columnMap 파싱 (객체 형식 통일)
     * - 기본: { "userNm": { "label": "사용자" } }
     * - 공통코드: { "sysCd": { "label": "시스템", "comGroupCd": "SYS_CD" } }
     */
    @SuppressWarnings("unchecked")
    private ColumnMapInfo parseColumnMap(Map<String, Object> columnMap) {
        ColumnMapInfo info = new ColumnMapInfo();
        if (columnMap == null) {
            return info;
        }

        for (Map.Entry<String, Object> entry : columnMap.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();

            if (value instanceof Map) {
                Map<String, Object> config = (Map<String, Object>) value;
                String label = (String) config.get("label");
                String comGroupCd = (String) config.get("comGroupCd");

                info.headerMap.put(key, label != null ? label : key);
                if (comGroupCd != null && !comGroupCd.isEmpty()) {
                    info.comGroupCdMap.put(key, comGroupCd);
                }
            }
        }

        return info;
    }

    /**
     * 공통코드 캐시 구성 (요청 단위)
     * @param comGroupCdMap key -> comGroupCd 매핑
     * @return comGroupCd -> (코드 -> 코드명) 캐시
     */
    private Map<String, Map<String, String>> buildComCodeCache(Map<String, String> comGroupCdMap) {
        Map<String, Map<String, String>> cache = new HashMap<>();
        if (comGroupCdMap == null || comGroupCdMap.isEmpty()) {
            return cache;
        }

        // 중복 제거된 comGroupCd 목록
        Set<String> groupCds = new HashSet<>(comGroupCdMap.values());
        log.debug("공통코드 캐시 구성 - groupCds: {}", groupCds);

        for (String groupCd : groupCds) {
            try {
                List<ComCdVO> codes;
                // SYS_CD는 SYS 테이블에서 조회 (COM_CD 테이블에 없음)
                if ("SYS_CD".equals(groupCd)) {
                    codes = comCdService.getSystemCodeList();
                } else {
                    codes = comCdService.getComCdByGroupCd(groupCd);
                }
                Map<String, String> codeMap = codes.stream()
                        .collect(Collectors.toMap(ComCdVO::getCd, ComCdVO::getCdNm, (a, b) -> a));
                cache.put(groupCd, codeMap);
                log.debug("공통코드 캐시 완료 - groupCd: {}, size: {}", groupCd, codeMap.size());
            } catch (Exception e) {
                log.warn("공통코드 조회 실패 - groupCd: {}", groupCd, e);
                cache.put(groupCd, new HashMap<>());
            }
        }

        return cache;
    }

    private String buildFileName(String fileName) {
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
        return (fileName != null ? fileName : "export") + "_" + timestamp + ".xlsx";
    }

    private void setResponseHeaders(HttpServletResponse response, String fileName) {
        String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
    }

    private List<String> getColumnKeys(List<Map<String, Object>> data, Map<String, String> headerMap) {
        if (headerMap != null && !headerMap.isEmpty()) {
            return new ArrayList<>(headerMap.keySet());
        }
        return new ArrayList<>(data.get(0).keySet());
    }

    private void createHeaderRow(SXSSFSheet sheet, List<String> columnKeys,
                                  Map<String, String> headerMap, CellStyle headerStyle, int[] maxColumnLengths) {
        SXSSFRow headerRow = sheet.createRow(0);

        // No 컬럼
        SXSSFCell noCell = headerRow.createCell(0);
        noCell.setCellValue("No");
        noCell.setCellStyle(headerStyle);
        maxColumnLengths[0] = 2; // "No" 길이

        for (int i = 0; i < columnKeys.size(); i++) {
            SXSSFCell cell = headerRow.createCell(i + 1);
            String key = columnKeys.get(i);
            String headerName = (headerMap != null && headerMap.containsKey(key)) ? headerMap.get(key) : key;
            cell.setCellValue(headerName);
            cell.setCellStyle(headerStyle);
            maxColumnLengths[i + 1] = getDisplayLength(headerName);
        }
    }

    private void createDataRow(SXSSFRow row, int rowNo, Map<String, Object> rowData,
                                List<String> columnKeys, Map<String, String> comGroupCdMap,
                                Map<String, Map<String, String>> comCodeCache,
                                CellStyle textStyle, CellStyle numberStyle, CellStyle intStyle,
                                int[] maxColumnLengths) {
        // No 컬럼
        SXSSFCell noCell = row.createCell(0);
        noCell.setCellValue(rowNo);
        noCell.setCellStyle(intStyle);
        maxColumnLengths[0] = Math.max(maxColumnLengths[0], String.valueOf(rowNo).length());

        for (int j = 0; j < columnKeys.size(); j++) {
            SXSSFCell cell = row.createCell(j + 1);
            String key = columnKeys.get(j);
            Object value = rowData.get(key);

            // 공통코드 변환
            if (value != null && comGroupCdMap.containsKey(key)) {
                String comGroupCd = comGroupCdMap.get(key);
                Map<String, String> codeMap = comCodeCache.get(comGroupCd);
                if (codeMap != null) {
                    String codeName = codeMap.get(value.toString());
                    if (codeName != null) {
                        value = codeName;
                    }
                }
            }

            String displayValue;
            if (value == null) {
                cell.setCellValue("");
                cell.setCellStyle(textStyle);
                displayValue = "";
            } else if (value instanceof Number) {
                double num = ((Number) value).doubleValue();
                cell.setCellValue(num);
                cell.setCellStyle(num == Math.floor(num) ? intStyle : numberStyle);
                displayValue = value.toString();
            } else {
                displayValue = value.toString();
                cell.setCellValue(displayValue);
                cell.setCellStyle(textStyle);
            }

            // 개행이 있는 경우 가장 긴 줄 기준
            int len = getMaxLineLength(displayValue);
            maxColumnLengths[j + 1] = Math.max(maxColumnLengths[j + 1], len);
        }
    }

    private void setColumnWidths(SXSSFSheet sheet, List<String> columnKeys, int[] maxColumnLengths) {
        // No 컬럼
        int noWidth = (maxColumnLengths[0] + 2) * 256;
        sheet.setColumnWidth(0, Math.min(Math.max(noWidth, 2000), 255 * 256));

        for (int i = 0; i < columnKeys.size(); i++) {
            int charLen = maxColumnLengths[i + 1];
            // 여유 공간 추가 (+2), 256 단위로 변환
            int width = (charLen + 2) * 256;
            // 최소 2000, 최대 65280 (255*256)
            sheet.setColumnWidth(i + 1, Math.min(Math.max(width, 2000), 255 * 256));
        }
    }

    /**
     * 문자열의 표시 길이 계산 (한글은 2, 영문/숫자는 1)
     */
    private int getDisplayLength(String str) {
        if (str == null || str.isEmpty()) {
            return 0;
        }
        int len = 0;
        for (char c : str.toCharArray()) {
            len += (c > 127) ? 2 : 1;
        }
        return len;
    }

    /**
     * 개행이 있는 문자열에서 가장 긴 줄의 표시 길이 반환
     */
    private int getMaxLineLength(String str) {
        if (str == null || str.isEmpty()) {
            return 0;
        }
        int maxLen = 0;
        for (String line : str.split("\n")) {
            maxLen = Math.max(maxLen, getDisplayLength(line));
        }
        return maxLen;
    }

    private CellStyle createHeaderStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    private CellStyle createTextStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setWrapText(true);
        return style;
    }

    private CellStyle createNumberStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.RIGHT);
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.#"));
        return style;
    }

    private CellStyle createIntStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.RIGHT);
        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0"));
        return style;
    }
}