JPA Specification 기반 Excel/CSV 내보내기 서비스

/**
 * JPA Specification 기반 Excel/CSV 내보내기 서비스
 *
 * <p>JPA Specification 필터를 적용해 조회한 게시글 목록을 Apache POI로 Excel 내보냅니다.
 * 통계 데이터를 4시트(요약/카테고리별/월별추이/담당자별)로 내보내는 기능도 포함합니다.</p>
 *
 * <p>의존 라이브러리:</p>
 * <ul>
 *   <li>org.apache.poi:poi-ooxml (Apache POI XLSX)</li>
 *   <li>Spring Data JPA (Specification)</li>
 *   <li>Lombok</li>
 * </ul>
 *
 * @source 260307-kcsi-smpa-internal
 * @extracted 2026-03-22
 * @version 1.0.0
 */
package com.example.service;

import com.example.domain.sinmungo.SinmungoPost;
import com.example.dto.sinmungo.PostFilterRequest;
import com.example.dto.sinmungo.SinmungoDetailedStatsResponse;
import com.example.repository.SinmungoPostRepository;
import com.example.repository.SinmungoPostSpecification;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.ByteArrayOutputStream;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;

/**
 * 민원/게시글 Excel 내보내기 서비스 (JPA Specification 기반 필터 지원)
 *
 * <p>사용법:</p>
 * <pre>{@code
 * byte[] excel = sinmungoExportService.exportPostsToExcel(filter);
 * response.setHeader("Content-Disposition", "attachment; filename=posts.xlsx");
 * response.getOutputStream().write(excel);
 * }</pre>
 */
@Slf4j
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class SinmungoExportService {

    private final SinmungoPostRepository postRepository;

    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    private static final DateTimeFormatter DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");

    /**
     * 민원 목록 Excel 내보내기 (필터 반영)
     *
     * @param filter 필터 요청 DTO (JPA Specification으로 변환)
     * @return XLSX 바이트 배열
     */
    public byte[] exportPostsToExcel(PostFilterRequest filter) {
        List<SinmungoPost> posts = postRepository.findAll(
                SinmungoPostSpecification.fromFilter(filter),
                Sort.by(Sort.Direction.DESC, "createdAt")
        );

        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = workbook.createSheet("민원 목록");
            CellStyle headerStyle = createHeaderStyle(workbook);

            // 헤더 행
            String[] headers = {"번호", "카테고리", "제목", "작성자", "소속", "상태", "우선순위",
                    "담당자", "담당부서", "등록일", "처리기한", "완료일", "기한초과"};
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 데이터 행
            int rowNum = 1;
            for (SinmungoPost post : posts) {
                Row row = sheet.createRow(rowNum++);
                int col = 0;

                row.createCell(col++).setCellValue(post.getDisplayNumber() != null ? post.getDisplayNumber() : 0);
                row.createCell(col++).setCellValue(post.getCategory().getLabel());
                row.createCell(col++).setCellValue(post.getTitle());
                row.createCell(col++).setCellValue(post.getAuthorName());
                row.createCell(col++).setCellValue(post.getAuthorDepartment() != null ? post.getAuthorDepartment() : "");
                row.createCell(col++).setCellValue(post.getStatus().getLabel());
                row.createCell(col++).setCellValue(post.getPriority().getLabel());
                row.createCell(col++).setCellValue(post.getAssignedTo() != null ? post.getAssignedTo() : "");
                row.createCell(col++).setCellValue(post.getAssignedDepartment() != null ? post.getAssignedDepartment() : "");
                row.createCell(col++).setCellValue(post.getCreatedAt() != null ? post.getCreatedAt().format(DATE_FORMATTER) : "");
                row.createCell(col++).setCellValue(post.getDueDate() != null ? post.getDueDate().format(DATE_FORMATTER) : "");
                row.createCell(col++).setCellValue(post.getCompletedAt() != null ? post.getCompletedAt().format(DATE_FORMATTER) : "");
                row.createCell(col++).setCellValue(post.isOverdue() ? "Y" : "N");
            }

            // 열 너비 자동 조정
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(out);
            return out.toByteArray();

        } catch (Exception e) {
            log.error("민원 목록 Excel 내보내기 실패", e);
            throw new RuntimeException("Excel 내보내기에 실패했습니다.", e);
        }
    }

    /**
     * 통계 Excel 내보내기 (4시트: 요약 / 카테고리별 / 월별 추이 / 담당자별)
     *
     * @param stats 통계 응답 DTO
     * @return XLSX 바이트 배열
     */
    public byte[] exportStatsToExcel(SinmungoDetailedStatsResponse stats) {
        try (Workbook workbook = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            CellStyle headerStyle = createHeaderStyle(workbook);

            // 시트 1: 요약
            Sheet summarySheet = workbook.createSheet("요약");
            String[][] summaryData = {
                    {"항목", "수치"},
                    {"전체 민원", String.valueOf(stats.getTotalCount())},
                    {"접수대기", String.valueOf(stats.getPendingCount())},
                    {"처리중", String.valueOf(stats.getInProgressCount())},
                    {"완료", String.valueOf(stats.getCompletedCount())},
                    {"반려", String.valueOf(stats.getRejectedCount())},
                    {"기한초과", String.valueOf(stats.getOverdueCount())},
                    {"평균 처리일", String.format("%.1f일", stats.getAvgProcessingDays())}
            };
            for (int i = 0; i < summaryData.length; i++) {
                Row row = summarySheet.createRow(i);
                for (int j = 0; j < summaryData[i].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(summaryData[i][j]);
                    if (i == 0) cell.setCellStyle(headerStyle);
                }
            }
            summarySheet.autoSizeColumn(0);
            summarySheet.autoSizeColumn(1);

            // 시트 2: 카테고리별
            Sheet categorySheet = workbook.createSheet("카테고리별");
            Row catHeader = categorySheet.createRow(0);
            createHeaderCell(catHeader, 0, "카테고리", headerStyle);
            createHeaderCell(catHeader, 1, "건수", headerStyle);
            if (stats.getCategoryDistribution() != null) {
                int r = 1;
                for (Map<String, Object> item : stats.getCategoryDistribution()) {
                    Row row = categorySheet.createRow(r++);
                    row.createCell(0).setCellValue(String.valueOf(item.get("label")));
                    row.createCell(1).setCellValue(((Number) item.get("count")).longValue());
                }
            }
            categorySheet.autoSizeColumn(0);
            categorySheet.autoSizeColumn(1);

            // 시트 3: 월별 추이
            Sheet monthlySheet = workbook.createSheet("월별 추이");
            Row monthHeader = monthlySheet.createRow(0);
            createHeaderCell(monthHeader, 0, "월", headerStyle);
            createHeaderCell(monthHeader, 1, "건수", headerStyle);
            if (stats.getMonthlyTrend() != null) {
                int r = 1;
                for (Map<String, Object> item : stats.getMonthlyTrend()) {
                    Row row = monthlySheet.createRow(r++);
                    row.createCell(0).setCellValue(String.valueOf(item.get("month")));
                    row.createCell(1).setCellValue(((Number) item.get("count")).longValue());
                }
            }
            monthlySheet.autoSizeColumn(0);
            monthlySheet.autoSizeColumn(1);

            // 시트 4: 담당자별
            Sheet assigneeSheet = workbook.createSheet("담당자별");
            Row assHeader = assigneeSheet.createRow(0);
            createHeaderCell(assHeader, 0, "담당자", headerStyle);
            createHeaderCell(assHeader, 1, "전체", headerStyle);
            createHeaderCell(assHeader, 2, "완료", headerStyle);
            createHeaderCell(assHeader, 3, "처리중", headerStyle);
            if (stats.getAssigneeStats() != null) {
                int r = 1;
                for (Map<String, Object> item : stats.getAssigneeStats()) {
                    Row row = assigneeSheet.createRow(r++);
                    row.createCell(0).setCellValue(String.valueOf(item.get("name")));
                    row.createCell(1).setCellValue(((Number) item.get("total")).longValue());
                    row.createCell(2).setCellValue(((Number) item.get("completed")).longValue());
                    row.createCell(3).setCellValue(((Number) item.get("inProgress")).longValue());
                }
            }
            for (int i = 0; i < 4; i++) assigneeSheet.autoSizeColumn(i);

            workbook.write(out);
            return out.toByteArray();

        } catch (Exception e) {
            log.error("통계 Excel 내보내기 실패", e);
            throw new RuntimeException("통계 Excel 내보내기에 실패했습니다.", e);
        }
    }

    // ──────────────────────────────────────────────
    // 헬퍼 메서드
    // ──────────────────────────────────────────────

    private CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    private void createHeaderCell(Row row, int col, String value, CellStyle style) {
        Cell cell = row.createCell(col);
        cell.setCellValue(value);
        cell.setCellStyle(style);
    }
}