package com.ga.common.excel;

import com.ga.common.code.CommonCodeService;
import com.ga.common.exception.BizException;
import com.ga.common.exception.ErrorCode;
import com.ga.common.util.MaskUtil;
import com.github.pjfanning.xlsx.StreamingReader;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cursor.Cursor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.function.Consumer;
import java.util.function.Supplier;

/**
 * 대용량 엑셀 처리.
 *  - exportLargeExcel: SXSSFWorkbook + MyBatis Cursor로 100만건 다운로드
 *  - importLargeExcel: xlsx-streamer(SAX) + 배치 INSERT로 70만건 업로드
 *  - exportTemplate:   업로드 양식 (헤더 + 코드그룹 드롭다운)
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ExcelService {

    private final CommonCodeService codeService;

    /* =========================================================
       다운로드 (Export) — SXSSF + Cursor 스트리밍
       ========================================================= */
    @Transactional(readOnly = true)
    public <T> void exportLargeExcel(String fileName, Class<T> clazz,
                                     Supplier<Cursor<T>> cursorSupplier,
                                     HttpServletResponse response) {
        List<ExcelColumnInfo> columns = ExcelColumnParser.parse(clazz);

        try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) {
            wb.setCompressTempFiles(true);
            SXSSFSheet sheet = wb.createSheet("Sheet1");

            // 1. 헤더
            createHeaderRow(wb, sheet, columns);

            // 2. 데이터
            int rowNum = 1;
            try (Cursor<T> cursor = cursorSupplier.get()) {
                for (T item : cursor) {
                    Row row = sheet.createRow(rowNum++);
                    writeRow(wb, row, item, columns);
                    if (rowNum % 100_000 == 0) {
                        log.info("Excel export progress: {} rows", rowNum);
                    }
                }
            }

            // 3. 응답
            setExcelResponseHeader(response, fileName);
            wb.write(response.getOutputStream());
            wb.dispose();
        } catch (Exception e) {
            log.error("Excel export error", e);
            throw new BizException(ErrorCode.INTERNAL_ERROR, "엑셀 다운로드 실패");
        }
    }

    /** 빈 템플릿(헤더만) 다운로드 */
    public <T> void exportTemplate(String fileName, Class<T> clazz, HttpServletResponse response) {
        List<ExcelColumnInfo> columns = ExcelColumnParser.parse(clazz);
        try (SXSSFWorkbook wb = new SXSSFWorkbook(100)) {
            SXSSFSheet sheet = wb.createSheet("Sheet1");
            createHeaderRow(wb, sheet, columns);
            setExcelResponseHeader(response, fileName);
            wb.write(response.getOutputStream());
            wb.dispose();
        } catch (Exception e) {
            log.error("Excel template error", e);
            throw new BizException(ErrorCode.INTERNAL_ERROR, "템플릿 다운로드 실패");
        }
    }

    /* =========================================================
       업로드 (Import) — SAX 스트리밍 + 배치 INSERT
       ========================================================= */
    public <T> ExcelImportResult importLargeExcel(MultipartFile file, Class<T> clazz,
                                                  int batchSize, Consumer<List<T>> batchConsumer) {
        ExcelImportResult result = new ExcelImportResult();
        List<ExcelColumnInfo> columns = ExcelColumnParser.parse(clazz);
        java.util.List<T> batch = new java.util.ArrayList<>(batchSize);

        try (InputStream is = file.getInputStream();
             org.apache.poi.ss.usermodel.Workbook wb = StreamingReader.builder()
                     .rowCacheSize(100)
                     .bufferSize(4096)
                     .open(is)) {

            Sheet sheet = wb.getSheetAt(0);
            int rowNum = 0;
            for (Row row : sheet) {
                rowNum++;
                if (rowNum == 1) continue;  // 헤더 스킵
                try {
                    T item = parseRow(row, clazz, columns);
                    batch.add(item);
                    if (batch.size() >= batchSize) {
                        batchConsumer.accept(batch);
                        result.setSuccessCount(result.getSuccessCount() + batch.size());
                        result.setTotalCount(result.getTotalCount() + batch.size());
                        batch.clear();
                        if (rowNum % 100_000 == 0) {
                            log.info("Excel import progress: {} rows", rowNum);
                        }
                    }
                } catch (Exception e) {
                    result.addError(new ExcelErrorRow(rowNum, null, null, e.getMessage()));
                }
            }
            if (!batch.isEmpty()) {
                batchConsumer.accept(batch);
                result.setSuccessCount(result.getSuccessCount() + batch.size());
                result.setTotalCount(result.getTotalCount() + batch.size());
            }
        } catch (Exception e) {
            log.error("Excel import error", e);
            throw new BizException(ErrorCode.EXCEL_PARSE_FAIL, e.getMessage());
        }
        return result;
    }

    /* =========================================================
       내부 헬퍼
       ========================================================= */
    private void createHeaderRow(SXSSFWorkbook wb, Sheet sheet, List<ExcelColumnInfo> columns) {
        CellStyle headerStyle = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBold(true);
        headerStyle.setFont(font);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(BorderStyle.THIN);

        Row header = sheet.createRow(0);
        for (int i = 0; i < columns.size(); i++) {
            ExcelColumnInfo info = columns.get(i);
            Cell c = header.createCell(i);
            c.setCellValue(info.getHeader());
            c.setCellStyle(headerStyle);
            sheet.setColumnWidth(i, info.getWidth() * 256);
        }
    }

    private <T> void writeRow(SXSSFWorkbook wb, Row row, T item, List<ExcelColumnInfo> columns) throws Exception {
        for (int i = 0; i < columns.size(); i++) {
            ExcelColumnInfo info = columns.get(i);
            Object value = info.getField().get(item);
            Cell cell = row.createCell(i);
            writeCell(cell, value, info);
        }
    }

    private void writeCell(Cell cell, Object value, ExcelColumnInfo info) {
        if (value == null) { cell.setBlank(); return; }

        // 코드그룹 변환 (코드 → 코드명)
        if (!info.getCodeGroup().isBlank() && value instanceof String s) {
            String name = codeService.getCodeName(info.getCodeGroup(), s);
            cell.setCellValue(name != null ? name : s);
            return;
        }
        // 마스킹
        if (info.isMasked() && value instanceof String s) {
            cell.setCellValue(MaskUtil.mask(s, MaskUtil.MaskType.RRN));
            return;
        }

        if (value instanceof Number n)            cell.setCellValue(n.doubleValue());
        else if (value instanceof BigDecimal bd)  cell.setCellValue(bd.doubleValue());
        else if (value instanceof LocalDate d)    cell.setCellValue(d.format(DateTimeFormatter.ISO_LOCAL_DATE));
        else if (value instanceof LocalDateTime dt) cell.setCellValue(dt.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        else if (value instanceof Boolean b)      cell.setCellValue(b);
        else                                      cell.setCellValue(value.toString());
    }

    private <T> T parseRow(Row row, Class<T> clazz, List<ExcelColumnInfo> columns) throws Exception {
        T item = clazz.getDeclaredConstructor().newInstance();
        for (int i = 0; i < columns.size(); i++) {
            ExcelColumnInfo info = columns.get(i);
            Cell cell = row.getCell(i);
            Object value = readCell(cell, info);
            if (value != null) info.getField().set(item, value);
            else if (!info.getDefaultValue().isBlank()) {
                info.getField().set(item, convertString(info.getDefaultValue(), info.getField().getType()));
            } else if (info.isRequired()) {
                throw new IllegalArgumentException("[" + info.getHeader() + "] 은(는) 필수입니다");
            }
        }
        return item;
    }

    private Object readCell(Cell cell, ExcelColumnInfo info) {
        if (cell == null) return null;
        Class<?> type = info.getField().getType();
        try {
            return switch (cell.getCellType()) {
                case STRING  -> convertString(cell.getStringCellValue(), type);
                case NUMERIC -> {
                    if (DateUtil.isCellDateFormatted(cell) && type == LocalDate.class) {
                        yield cell.getDateCellValue().toInstant().atZone(java.time.ZoneId.systemDefault()).toLocalDate();
                    }
                    double d = cell.getNumericCellValue();
                    if (type == BigDecimal.class) yield BigDecimal.valueOf(d);
                    if (type == Integer.class || type == int.class) yield (int) d;
                    if (type == Long.class || type == long.class) yield (long) d;
                    if (type == String.class) yield d == (long) d ? String.valueOf((long) d) : String.valueOf(d);
                    yield d;
                }
                case BOOLEAN -> cell.getBooleanCellValue();
                case FORMULA -> cell.getStringCellValue();
                default      -> null;
            };
        } catch (Exception e) {
            return null;
        }
    }

    @SuppressWarnings({"unchecked", "rawtypes"})
    private Object convertString(String s, Class<?> type) {
        if (s == null) return null;
        s = s.trim();
        if (s.isEmpty()) return null;
        if (type == String.class)     return s;
        if (type == BigDecimal.class) return new BigDecimal(s.replace(",", ""));
        if (type == Integer.class || type == int.class) return Integer.valueOf(s.replace(",", ""));
        if (type == Long.class    || type == long.class) return Long.valueOf(s.replace(",", ""));
        if (type == LocalDate.class)  return LocalDate.parse(s);
        if (type.isEnum())            return Enum.valueOf((Class<Enum>) type, s);
        return s;
    }

    private void setExcelResponseHeader(HttpServletResponse response, String fileName) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String encoded = URLEncoder.encode(fileName + ".xlsx", StandardCharsets.UTF_8).replace("+", "%20");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encoded);
    }
}
