package kr.itn.itnhub.process;

import kr.itn.itnhub.AbstractDbTest;
import kr.itn.itnhub.org.Organization;
import kr.itn.itnhub.org.OrganizationMapper;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.http.MediaType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.security.test.context.support.WithMockUser;
import org.springframework.test.web.servlet.MockMvc;

import java.io.ByteArrayOutputStream;
import java.time.LocalDateTime;

import static org.assertj.core.api.Assertions.assertThat;
import static org.hamcrest.Matchers.containsString;
import static org.springframework.security.test.web.servlet.request.SecurityMockMvcRequestPostProcessors.csrf;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.delete;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.multipart;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.put;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.jsonPath;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;

@AutoConfigureMockMvc
@WithMockUser(roles = "ADMIN")
class ProcessControllerTest extends AbstractDbTest {

    @Autowired
    MockMvc mvc;

    @Autowired
    OrganizationMapper orgMapper;

    @Autowired
    ProcessItemMapper processMapper;

    @Autowired
    JdbcTemplate jdbc;

    private Long orgId;

    @BeforeEach
    void setUp() {
        jdbc.update("delete from process_item");
        jdbc.update("delete from organization");

        Organization org = new Organization();
        org.setOrgNo("001");
        org.setOrgName("한국출판문화산업진흥원");
        org.setChannelSlug("001");
        orgMapper.upsertBySeed(org);

        orgId = orgMapper.findAll().get(0).getId();
    }

    /**
     * 필드 순서: seq,siteName,category,boardName,postTitle,url,description,hasAttachment,
     * priorKoglType,contractDocs,producedDate,publishedDate,reviewMajor,reviewMinor,reviewResult,
     * reviewKoglType,reviewAiType,reviewOpinion,reviewNote,priorEvidence,
     * judgedKoglType,judgedAiType,finalOpinion,judgmentBasis,processStatus (25개, U/Y열은 건너뛴다).
     */
    private byte[] workbook(String[]... dataRows) throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = wb.createSheet(ProcessParser.PRIMARY_SHEET_NAME);
            sheet.createRow(0);
            sheet.createRow(1);

            int rowNum = 2;
            for (String[] data : dataRows) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < data.length; i++) {
                    if (data[i] == null) {
                        continue;
                    }
                    row.createCell(sheetColumn(i)).setCellValue(data[i]);
                }
            }
            wb.write(out);
            return out.toByteArray();
        }
    }

    /** 논리 필드 인덱스(0..24) → 실제 시트 열(U=20, Y=24 건너뜀). */
    private int sheetColumn(int logicalIndex) {
        int col = logicalIndex;
        if (logicalIndex >= 20) {
            col += 1; // U열 건너뜀
        }
        if (logicalIndex >= 23) {
            col += 1; // Y열 건너뜀
        }
        return col;
    }

    private MockMultipartFile file(byte[] bytes) {
        return new MockMultipartFile("file", "process.xlsx",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", bytes);
    }

    private String[] row(String seq, String siteName, String category) {
        String[] data = new String[25];
        data[0] = seq;
        data[1] = siteName;
        data[2] = category;
        data[4] = "제목";
        data[5] = "http://example.com";
        return data;
    }

    private String[] withProcessing(String[] base, String contractDocs, String judgedKoglType,
                                     String finalOpinion, String processStatus) {
        String[] copy = base.clone();
        copy[9] = contractDocs;
        copy[20] = judgedKoglType;
        copy[22] = finalOpinion;
        copy[24] = processStatus;
        return copy;
    }

    @Test
    void 최초_업로드는_전부_신규이고_제작일_공표일은_실제_날짜셀이어도_yyyyMMdd_문자열로_저장된다() throws Exception {
        byte[] xlsx = dateCellWorkbook();

        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(1))
                .andExpect(jsonPath("$.updated").value(0))
                .andExpect(jsonPath("$.total").value(1));

        ProcessItem item = processMapper.findAllByOrg(orgId).get(0);
        assertThat(item.getProducedDate()).isEqualTo("2025-01-03");
        assertThat(item.getSiteName()).isEqualTo("사이트");
    }

    /** 제작일(K열, index 10)에 실제 datetime 셀 값 2025-01-03T00:00:00을 심어 재현한다. */
    private byte[] dateCellWorkbook() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = wb.createSheet(ProcessParser.PRIMARY_SHEET_NAME);
            sheet.createRow(0);
            sheet.createRow(1);
            Row row = sheet.createRow(2);
            row.createCell(0).setCellValue("1");
            row.createCell(1).setCellValue("사이트");

            CellStyle dateStyle = wb.createCellStyle();
            DataFormat fmt = wb.createDataFormat();
            dateStyle.setDataFormat(fmt.getFormat("yyyy-mm-dd hh:mm:ss"));
            Cell produced = row.createCell(10);
            produced.setCellStyle(dateStyle);
            produced.setCellValue(LocalDateTime.of(2025, 1, 3, 0, 0, 0));

            wb.write(out);
            return out.toByteArray();
        }
    }

    @Test
    void 같은_파일을_다시_올려도_행이_늘지_않고_갱신으로_집계된다() throws Exception {
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));

        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(0))
                .andExpect(jsonPath("$.updated").value(1));

        assertThat(processMapper.findAllByOrg(orgId)).hasSize(1);
    }

    @Test
    void 웹에서_수정한_권리처리값은_계약서유무를_포함해_재업로드가_덮어쓰지_않고_조사원_항목은_계속_갱신된다() throws Exception {
        byte[] first = workbook(
                row("1", "사이트A", "공지"),
                row("2", "사이트B", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(first)).with(csrf()))
                .andExpect(status().isOk());

        Long editedId = processMapper.findAllByOrg(orgId).stream()
                .filter(i -> i.getSeq() == 1).findFirst().get().getId();

        // seq=1은 웹에서 계약서 유무 + 처리결과를 직접 입력한다.
        mvc.perform(put("/api/orgs/{id}/process/{itemId}", orgId, editedId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"contractDocs": "양도계약서", "finalOpinion": "전부 양도 체결", "processStatus": "처리완료"}
                                """))
                .andExpect(status().isOk());

        // 재업로드: 두 행 모두 조사원 항목(site_name)이 바뀌고, 파일 쪽 계약서/처리 값도 새로 온다.
        byte[] second = workbook(
                withProcessing(row("1", "사이트A-수정", "공지"), "제안요청서", "1유형", "파일값", "미처리"),
                withProcessing(row("2", "사이트B-수정", "공지"), "공문", "2유형", "새 의견", "처리완료"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(second)).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(0))
                .andExpect(jsonPath("$.updated").value(2));

        ProcessItem edited = processMapper.findById(orgId, editedId);
        assertThat(edited.getSiteName()).isEqualTo("사이트A-수정"); // 조사원 항목은 항상 갱신
        assertThat(edited.getContractDocs()).isEqualTo("양도계약서"); // 웹 입력값 보존(계약서 유무 포함)
        assertThat(edited.getFinalOpinion()).isEqualTo("전부 양도 체결");
        assertThat(edited.getProcessStatus()).isEqualTo("처리완료");
        assertThat(edited.getWebEditedAt()).isNotNull();

        ProcessItem untouched = processMapper.findAllByOrg(orgId).stream()
                .filter(i -> i.getSeq() == 2).findFirst().get();
        assertThat(untouched.getSiteName()).isEqualTo("사이트B-수정");
        assertThat(untouched.getContractDocs()).isEqualTo("공문"); // 웹 수정 이력 없으니 파일값 반영
        assertThat(untouched.getFinalOpinion()).isEqualTo("새 의견");
        assertThat(untouched.getProcessStatus()).isEqualTo("처리완료");
        assertThat(untouched.getWebEditedAt()).isNull();
    }

    @Test
    void 목록은_검색어와_상태로_필터링되고_처리완료는_미처리_필터에서_제외된다() throws Exception {
        byte[] xlsx = workbook(
                row("1", "국립중앙도서관", "공지"),
                row("2", "국립중앙박물관", "공지"),
                row("3", "다른기관", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());

        Long doneId = processMapper.findAllByOrg(orgId).stream()
                .filter(i -> i.getSeq() == 1).findFirst().get().getId();
        mvc.perform(put("/api/orgs/{id}/process/{itemId}", orgId, doneId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"processStatus": "처리완료"}
                                """))
                .andExpect(status().isOk());

        // 검색어로 "국립"이 들어간 2건만 필터링된다.
        mvc.perform(get("/api/orgs/{id}/process", orgId).param("keyword", "국립"))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.items.length()").value(2));

        // status=DONE이면 처리완료 1건만.
        mvc.perform(get("/api/orgs/{id}/process", orgId).param("status", "DONE"))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.items.length()").value(1))
                .andExpect(jsonPath("$.items[0].seq").value(1));

        // status=PENDING이면 처리완료 1건을 제외한 나머지 2건.
        mvc.perform(get("/api/orgs/{id}/process", orgId).param("status", "PENDING"))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.items.length()").value(2));

        // total/done은 검색어·상태 필터와 무관한 기관 전체 진행률이다.
        mvc.perform(get("/api/orgs/{id}/process", orgId).param("status", "PENDING"))
                .andExpect(jsonPath("$.total").value(3))
                .andExpect(jsonPath("$.done").value(1));
    }

    @Test
    void 처리완료로_저장하면_웹수정시각과_처리완료시각이_찍히고_이후_저장에도_처리완료시각은_유지된다() throws Exception {
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());
        Long itemId = processMapper.findAllByOrg(orgId).get(0).getId();

        mvc.perform(put("/api/orgs/{id}/process/{itemId}", orgId, itemId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"processStatus": "처리완료", "finalOpinion": "1차 완료"}
                                """))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.processStatus").value("처리완료"))
                .andExpect(jsonPath("$.webEditedAt").isNumber())
                .andExpect(jsonPath("$.processedAt").isNumber());

        Long firstProcessedAt = processMapper.findById(orgId, itemId).getProcessedAt();
        assertThat(firstProcessedAt).isNotNull();

        // 이후 다른 필드만 바꿔 다시 저장해도(여전히 처리완료) 최초 완료 시각은 그대로다.
        mvc.perform(put("/api/orgs/{id}/process/{itemId}", orgId, itemId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"processStatus": "처리완료", "finalOpinion": "2차 수정"}
                                """))
                .andExpect(status().isOk());

        ProcessItem afterSecondSave = processMapper.findById(orgId, itemId);
        assertThat(afterSecondSave.getProcessedAt()).isEqualTo(firstProcessedAt);
        assertThat(afterSecondSave.getFinalOpinion()).isEqualTo("2차 수정");

        // 미처리로 되돌려도 처리완료 시각 자체는 지우지 않는다(표시만 처리완료일 때 한다).
        mvc.perform(put("/api/orgs/{id}/process/{itemId}", orgId, itemId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"processStatus": "미처리"}
                                """))
                .andExpect(status().isOk());

        assertThat(processMapper.findById(orgId, itemId).getProcessedAt()).isEqualTo(firstProcessedAt);
    }

    @Test
    void 처리상태가_미처리_처리완료가_아니면_400이다() throws Exception {
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());
        Long itemId = processMapper.findAllByOrg(orgId).get(0).getId();

        mvc.perform(put("/api/orgs/{id}/process/{itemId}", orgId, itemId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"processStatus": "진행중"}
                                """))
                .andExpect(status().isBadRequest())
                .andExpect(jsonPath("$.message").value(containsString("미처리")));
    }

    @Test
    void 게시물을_삭제하면_204와_함께_목록에서_사라진다() throws Exception {
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());
        Long itemId = processMapper.findAllByOrg(orgId).get(0).getId();

        mvc.perform(delete("/api/orgs/{id}/process/{itemId}", orgId, itemId).with(csrf()))
                .andExpect(status().isNoContent());

        assertThat(processMapper.findAllByOrg(orgId)).isEmpty();
    }

    @Test
    void 존재하지_않는_기관으로_업로드하면_404다() throws Exception {
        long missingOrgId = orgId + 999999L;
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));

        mvc.perform(multipart("/api/orgs/{id}/process/import", missingOrgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isNotFound())
                .andExpect(jsonPath("$.message").value(containsString(String.valueOf(missingOrgId))));
    }

    @Test
    void 존재하지_않는_게시물이면_404다() throws Exception {
        long missingItemId = 999999L;

        mvc.perform(get("/api/orgs/{id}/process/{itemId}", orgId, missingItemId))
                .andExpect(status().isNotFound())
                .andExpect(jsonPath("$.message").value(containsString(String.valueOf(missingItemId))));
    }

    @Test
    void 확장자가_xlsx_xlsm이_아니면_400이다() throws Exception {
        MockMultipartFile txt = new MockMultipartFile("file", "process.txt",
                "text/plain", "아무 내용".getBytes());

        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(txt).with(csrf()))
                .andExpect(status().isBadRequest())
                .andExpect(jsonPath("$.message").value(containsString("xlsx")));
    }

    @Test
    void 다운로드는_엑셀_파일을_돌려준다() throws Exception {
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/process/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());

        mvc.perform(get("/api/orgs/{id}/process/download", orgId))
                .andExpect(status().isOk())
                .andExpect(result -> assertThat(result.getResponse().getContentAsByteArray()).isNotEmpty());
    }
}