package com.ga.api.service.upload;

import com.ga.common.excel.ExcelErrorRow;
import com.ga.common.exception.BizException;
import com.ga.common.exception.ErrorCode;
import com.ga.common.file.FileService;
import com.ga.common.util.SecurityUtil;
import com.ga.core.mapper.upload.UploadColumnMapper;
import com.ga.core.mapper.upload.UploadTemplateMapper;
import com.ga.core.vo.upload.UploadHistoryVO;
import com.ga.core.vo.upload.UploadTemplateColumnVO;
import com.ga.core.vo.upload.UploadTemplateVO;
import com.github.pjfanning.xlsx.StreamingReader;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.lang.NonNull;

import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

/**
 * 엑셀 업로드 템플릿 처리 핵심 서비스.
 *
 * processUpload: SAX 파싱 + 변환 + 검증 + 배치 INSERT → upload_history 저장
 * preview:       첫 N행 변환 결과만 반환 (DB INSERT 없음)
 *
 * 트랜잭션 경계: 파일 파싱은 트랜잭션 밖. history 저장/갱신과 배치 INSERT는
 * UploadTxHelper를 통해 REQUIRES_NEW 독립 트랜잭션으로 처리하여
 * 롱 트랜잭션으로 인한 커넥션 풀 고갈을 방지한다.
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class UploadService {

    private final UploadTemplateMapper templateMapper;
    private final UploadColumnMapper   columnMapper;
    private final TransformEngine      transformEngine;
    private final LookupCache          lookupCache;
    private final FileService          fileService;
    private final UploadTxHelper       txHelper;

    /** target_table whitelist — SQL Injection 방지 */
    private static final Set<String> ALLOWED_TARGET_TABLES = Set.of(
            "recruit_ledger", "maintain_ledger", "contract", "agent",
            "commission_rate", "payout_rule", "chargeback_rule",
            "exception_ledger", "payment"
    );

    // ============================================================
    // 메인: 업로드 처리
    // ============================================================

    public UploadResultResp processUpload(String templateCode, MultipartFile file) {
        long startMs = System.currentTimeMillis();

        // 1. 템플릿 + 컬럼 로드
        UploadTemplateVO template = loadTemplate(templateCode);
        List<UploadTemplateColumnVO> columns = columnMapper.selectByTemplateId(template.getTemplateId());
        if (columns.isEmpty()) {
            throw new BizException(ErrorCode.INVALID_PARAMETER, "컬럼 정의가 없는 템플릿입니다: " + templateCode);
        }

        validateTargetTable(template.getTargetTable());

        // 2. LOOKUP 캐시 사전 로드 (요청 범위 Map — thread-safe)
        Map<String, Map<String, Object>> lookupData = lookupCache.preload(columns);

        // 3. history 레코드 생성 (PROCESSING 상태) — 독립 트랜잭션
        UploadHistoryVO history = initHistory(template, file);
        txHelper.saveHistory(history);

        List<ExcelErrorRow> errors = new ArrayList<>();
        int successCount = 0;
        int skipCount    = 0;
        int totalCount   = 0;
        int maxErrors    = template.getMaxErrorCount() != null ? template.getMaxErrorCount() : 100;
        int batchSize    = template.getBatchSize()    != null ? template.getBatchSize()    : 1000;
        int dataStartRow = template.getDataStartRow() != null ? template.getDataStartRow() : 2;
        int sheetIndex   = template.getSheetIndex()   != null ? template.getSheetIndex()   : 0;

        List<Map<String, Object>> batch = new ArrayList<>(batchSize);

        // 4. 파일 파싱 — 트랜잭션 밖에서 실행
        try (InputStream is = file.getInputStream();
             Workbook wb = StreamingReader.builder()
                     .rowCacheSize(100)
                     .bufferSize(4096)
                     .open(is)) {

            Sheet sheet = wb.getSheetAt(sheetIndex);
            int rowNum = 0;

            for (Row row : sheet) {
                rowNum++;
                if (rowNum < dataStartRow) continue;

                if ("Y".equalsIgnoreCase(template.getSkipEmptyRow()) && isEmptyRow(row)) {
                    skipCount++;
                    continue;
                }

                totalCount++;

                if (errors.size() >= maxErrors) {
                    skipCount++;
                    continue;
                }

                try {
                    Map<String, Object> mapped = transformEngine.transform(row, columns, lookupData);
                    validateRow(mapped, columns, rowNum, errors);

                    final int currentRow = rowNum;
                    boolean rowHasError = errors.stream().anyMatch(e -> e.getRowNumber() == currentRow);
                    if (!rowHasError) {
                        batch.add(mapped);
                        if (batch.size() >= batchSize) {
                            // 청크 단위 독립 트랜잭션으로 커밋
                            successCount += txHelper.insertBatch(template.getTargetTable(), new ArrayList<>(batch));
                            batch.clear();
                            log.debug("Batch flush: {} rows processed so far", totalCount);
                        }
                    }
                } catch (BizException e) {
                    errors.add(new ExcelErrorRow(rowNum, null, null, e.getDisplayMessage()));
                } catch (Exception e) {
                    errors.add(new ExcelErrorRow(rowNum, null, null, e.getMessage()));
                }
            }

            if (!batch.isEmpty()) {
                successCount += txHelper.insertBatch(template.getTargetTable(), new ArrayList<>(batch));
                batch.clear();
            }

        } catch (BizException e) {
            txHelper.updateHistory(history.getHistoryId(), "FAIL", e.getDisplayMessage(),
                    0, 0, 0, 0, null, 0);
            throw e;
        } catch (Exception e) {
            log.error("Upload processing error: templateCode={}", templateCode, e);
            txHelper.updateHistory(history.getHistoryId(), "FAIL", e.getMessage(),
                    0, 0, 0, 0, null, 0);
            throw new BizException(ErrorCode.EXCEL_PARSE_FAIL, e.getMessage());
        }

        int durationSec = (int) ((System.currentTimeMillis() - startMs) / 1000);
        int errorCount  = errors.size();
        String status   = errorCount == 0 ? "SUCCESS" : (successCount > 0 ? "PARTIAL" : "FAIL");

        Long errorFileId = null;
        if (!errors.isEmpty()) {
            errorFileId = createErrorFile(errors, file.getOriginalFilename());
        }

        // 5. history 완료 갱신 — 독립 트랜잭션
        txHelper.updateHistory(history.getHistoryId(), status, null,
                successCount, errorCount, skipCount, totalCount, errorFileId, durationSec);

        return UploadResultResp.builder()
                .historyId(history.getHistoryId())
                .totalCount(totalCount)
                .successCount(successCount)
                .errorCount(errorCount)
                .skipCount(skipCount)
                .errorFileId(errorFileId)
                .durationSec(durationSec)
                .errors(errors.stream().map(UploadResultResp.UploadErrorRow::from).toList())
                .build();
    }

    // ============================================================
    // 미리보기: DB INSERT 없이 변환 결과만 반환
    // ============================================================

    public List<Map<String, Object>> preview(String templateCode, MultipartFile file, int limit) {
        UploadTemplateVO template = loadTemplate(templateCode);
        List<UploadTemplateColumnVO> columns = columnMapper.selectByTemplateId(template.getTemplateId());

        Map<String, Map<String, Object>> lookupData = lookupCache.preload(columns);

        List<Map<String, Object>> result = new ArrayList<>();
        int dataStartRow = template.getDataStartRow() != null ? template.getDataStartRow() : 2;
        int sheetIndex   = template.getSheetIndex()   != null ? template.getSheetIndex()   : 0;

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

            Sheet sheet = wb.getSheetAt(sheetIndex);
            int rowNum = 0;

            for (Row row : sheet) {
                rowNum++;
                if (rowNum < dataStartRow) continue;
                if ("Y".equalsIgnoreCase(template.getSkipEmptyRow()) && isEmptyRow(row)) continue;

                try {
                    result.add(transformEngine.transform(row, columns, lookupData));
                } catch (Exception e) {
                    result.add(Map.of("_error", "행 " + rowNum + ": " + e.getMessage()));
                }

                if (result.size() >= limit) break;
            }

        } catch (Exception e) {
            log.error("Preview error: templateCode={}", templateCode, e);
            throw new BizException(ErrorCode.EXCEL_PARSE_FAIL, e.getMessage());
        }

        return result;
    }

    /**
     * 샘플 엑셀에서 헤더(첫 행) + 미리보기 행 추출. 템플릿 등록 전에 사용.
     * @param file       엑셀 파일
     * @param headerRow  헤더가 있는 행 번호 (1-base, 보통 1)
     * @param sheetIndex 시트 인덱스 (0-base)
     * @param previewRows 헤더 다음 N행을 함께 반환
     */
    public ExcelHeaderResp extractHeaders(MultipartFile file, int headerRow, int sheetIndex, int previewRows) {
        List<String> headers = new ArrayList<>();
        List<List<String>> samples = new ArrayList<>();
        try (InputStream is = file.getInputStream();
             Workbook wb = StreamingReader.builder()
                     .rowCacheSize(50).bufferSize(4096).open(is)) {

            Sheet sheet = wb.getSheetAt(sheetIndex);
            int rowNum = 0;
            for (Row row : sheet) {
                rowNum++;
                if (rowNum == headerRow) {
                    short last = row.getLastCellNum();
                    for (int i = 0; i < last; i++) {
                        var cell = row.getCell(i);
                        headers.add(cell != null ? safeStringValue(cell) : "");
                    }
                } else if (rowNum > headerRow && samples.size() < previewRows) {
                    List<String> rowVals = new ArrayList<>();
                    for (int i = 0; i < headers.size(); i++) {
                        var cell = row.getCell(i);
                        rowVals.add(cell != null ? safeStringValue(cell) : "");
                    }
                    samples.add(rowVals);
                }
                if (rowNum > headerRow + previewRows) break;
            }
        } catch (Exception e) {
            log.error("extractHeaders error", e);
            throw new BizException(ErrorCode.EXCEL_PARSE_FAIL, e.getMessage());
        }

        // 엑셀 컬럼 라벨 (A, B, C, ... AA, AB ...)
        List<String> columnLabels = new ArrayList<>();
        for (int i = 0; i < headers.size(); i++) columnLabels.add(toColumnLabel(i));

        return new ExcelHeaderResp(columnLabels, headers, samples);
    }

    private String safeStringValue(org.apache.poi.ss.usermodel.Cell cell) {
        try {
            CellType type = cell.getCellType();
            return switch (type) {
                case NUMERIC -> cell.getNumericCellValue() == Math.floor(cell.getNumericCellValue())
                                ? String.valueOf((long) cell.getNumericCellValue())
                                : String.valueOf(cell.getNumericCellValue());
                case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
                case FORMULA -> cell.getCellFormula();
                case BLANK   -> "";
                default      -> cell.getStringCellValue();
            };
        } catch (Exception e) {
            return "";
        }
    }

    /** 0 → A, 25 → Z, 26 → AA */
    private String toColumnLabel(int index) {
        StringBuilder sb = new StringBuilder();
        int n = index;
        while (n >= 0) {
            sb.insert(0, (char) ('A' + (n % 26)));
            n = n / 26 - 1;
        }
        return sb.toString();
    }

    public record ExcelHeaderResp(
            List<String> columns,        // ["A","B","C",...]
            List<String> headers,        // ["증권번호","설계사명",...]
            List<List<String>> samples   // 첫 N행
    ) {}

    // ============================================================
    // 내부 헬퍼
    // ============================================================

    private UploadTemplateVO loadTemplate(String templateCode) {
        UploadTemplateVO template = templateMapper.selectByCode(templateCode);
        if (template == null) {
            throw new BizException(ErrorCode.NOT_FOUND, "업로드 템플릿을 찾을 수 없습니다: " + templateCode);
        }
        if (!"Y".equalsIgnoreCase(template.getIsActive())) {
            throw new BizException(ErrorCode.INVALID_STATUS, "비활성 템플릿입니다: " + templateCode);
        }
        return template;
    }

    private void validateTargetTable(String targetTable) {
        if (targetTable == null || !ALLOWED_TARGET_TABLES.contains(targetTable)) {
            throw new BizException(ErrorCode.INVALID_PARAMETER,
                    "허용되지 않는 대상 테이블: " + targetTable);
        }
    }

    private UploadHistoryVO initHistory(UploadTemplateVO template, MultipartFile file) {
        UploadHistoryVO h = new UploadHistoryVO();
        h.setTemplateId(template.getTemplateId());
        h.setTemplateCode(template.getTemplateCode());
        h.setFileName(file.getOriginalFilename());
        h.setFileSize(file.getSize());
        h.setStatus("PROCESSING");
        h.setStartedAt(LocalDateTime.now());
        h.setCreatedBy(SecurityUtil.getCurrentUserId());
        return h;
    }

    private void validateRow(Map<String, Object> row, List<UploadTemplateColumnVO> columns,
                              int rowNum, List<ExcelErrorRow> errors) {
        for (UploadTemplateColumnVO col : columns) {
            Object value  = row.get(col.getTargetField());
            String strVal = value == null ? null : value.toString();

            // 필수값 검증
            if ("Y".equalsIgnoreCase(col.getIsRequired()) && (strVal == null || strVal.isBlank())) {
                String msg = col.getValidationMessage() != null
                        ? col.getValidationMessage()
                        : "[" + col.getTargetField() + "] 은(는) 필수입니다";
                errors.add(new ExcelErrorRow(rowNum, col.getTargetField(), strVal, msg));
                continue;
            }

            // 정규식 검증
            if (strVal != null && col.getValidationRegex() != null && !col.getValidationRegex().isBlank()) {
                if (!Pattern.matches(col.getValidationRegex(), strVal)) {
                    String msg = col.getValidationMessage() != null
                            ? col.getValidationMessage()
                            : "[" + col.getTargetField() + "] 형식이 올바르지 않습니다: " + strVal;
                    errors.add(new ExcelErrorRow(rowNum, col.getTargetField(), strVal, msg));
                }
            }
        }
    }

    private boolean isEmptyRow(Row row) {
        if (row == null) return true;
        for (org.apache.poi.ss.usermodel.Cell cell : row) {
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                String v = "";
                try { v = cell.getStringCellValue(); } catch (Exception ignored) {}
                if (!v.isBlank()) return false;
            }
        }
        return true;
    }

    /**
     * 에러 행 목록을 엑셀로 만들어 file_storage 에 적재하고 fileId 반환.
     * 실패 시 업로드를 중단하지 않고 null 반환.
     */
    private Long createErrorFile(List<ExcelErrorRow> errors, String originalFilename) {
        try {
            byte[] bytes = buildErrorExcelBytes(errors);
            String name  = "error_" + (originalFilename != null ? originalFilename : "upload.xlsx");
            BytesMultipartFile mockFile = new BytesMultipartFile(name, bytes);
            com.ga.common.file.FileVO saved = fileService.upload(mockFile, "UPLOAD_ERROR");
            return saved.getFileId();
        } catch (Exception e) {
            log.warn("에러 파일 생성 실패 (업로드는 계속 진행)", e);
            return null;
        }
    }

    private byte[] buildErrorExcelBytes(List<ExcelErrorRow> errors) throws Exception {
        try (org.apache.poi.xssf.streaming.SXSSFWorkbook wb =
                     new org.apache.poi.xssf.streaming.SXSSFWorkbook(100)) {

            org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet("errors");

            org.apache.poi.ss.usermodel.Row header = sheet.createRow(0);
            header.createCell(0).setCellValue("행번호");
            header.createCell(1).setCellValue("필드");
            header.createCell(2).setCellValue("값");
            header.createCell(3).setCellValue("오류내용");

            int rowIdx = 1;
            for (ExcelErrorRow e : errors) {
                org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIdx++);
                row.createCell(0).setCellValue(e.getRowNumber());
                row.createCell(1).setCellValue(e.getFieldName()    != null ? e.getFieldName()    : "");
                row.createCell(2).setCellValue(e.getValue()        != null ? e.getValue()        : "");
                row.createCell(3).setCellValue(e.getErrorMessage() != null ? e.getErrorMessage() : "");
            }

            java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
            wb.write(baos);
            wb.dispose();
            return baos.toByteArray();
        }
    }

    // ============================================================
    // byte[] → MultipartFile 래퍼 (MockMultipartFile 의존성 제거)
    // ============================================================

    private static class BytesMultipartFile implements MultipartFile {
        private final String filename;
        private final byte[] content;

        BytesMultipartFile(String filename, byte[] content) {
            this.filename = filename;
            this.content  = content;
        }

        @Override @NonNull public String getName()                  { return "file"; }
        @Override           public String getOriginalFilename()     { return filename; }
        @Override           public String getContentType()          {
            return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        }
        @Override public boolean isEmpty()                          { return content == null || content.length == 0; }
        @Override public long getSize()                             { return content == null ? 0 : content.length; }
        @Override @NonNull public byte[] getBytes()                 { return content == null ? new byte[0] : content; }
        @Override @NonNull public java.io.InputStream getInputStream() {
            return new java.io.ByteArrayInputStream(content == null ? new byte[0] : content);
        }
        @Override public void transferTo(@NonNull java.io.File dest) throws java.io.IOException {
            try (java.io.FileOutputStream fos = new java.io.FileOutputStream(dest)) {
                fos.write(content);
            }
        }
    }
}
