package kr.itn.itnhub.review;

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.util.List;

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 ReviewControllerTest extends AbstractDbTest {

    @Autowired
    MockMvc mvc;

    @Autowired
    OrganizationMapper orgMapper;

    @Autowired
    ReviewItemMapper reviewMapper;

    @Autowired
    JdbcTemplate jdbc;

    private Long orgId;

    @BeforeEach
    void setUp() {
        jdbc.update("delete from review_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,boardPath,boardName,postTitle,url,postRegistered,
     * producedDate,publishedDate,hasAttachment,koglAttached,koglType,aiType,surveyorNote,
     * openable,reviewMajor,reviewMinor,reviewResult,judgedKoglType,judgedAiType,opinion,
     * lawyerNote,needsProcessing (24개, W열은 건너뛴다). */
    private byte[] workbook(String[]... dataRows) throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = wb.createSheet(ReviewParser.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;
                    }
                    int col = i < 22 ? i : i + 1; // 22 = W, 건너뜀
                    row.createCell(col).setCellValue(data[i]);
                }
            }
            wb.write(out);
            return out.toByteArray();
        }
    }

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

    @Test
    void 최초_업로드는_전부_신규이고_제작일은_날짜서식_숫자여도_문자열_그대로_저장된다() throws Exception {
        byte[] xlsx = numericDateWorkbook();

        mvc.perform(multipart("/api/orgs/{id}/review/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));

        ReviewItem item = reviewMapper.findAllByOrg(orgId).get(0);
        assertThat(item.getProducedDate()).isEqualTo("20230904");
        assertThat(item.getSiteName()).isEqualTo("사이트");
    }

    /** 제작일(I열, index 8)에 날짜서식이 입혀진 숫자 20230904를 직접 심어 재현한다. */
    private byte[] numericDateWorkbook() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = wb.createSheet(ReviewParser.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"));
            Cell produced = row.createCell(8);
            produced.setCellStyle(dateStyle);
            produced.setCellValue(20230904);

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

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

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

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

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

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

        // seq=1은 웹에서 변호사 판정을 직접 입력한다.
        mvc.perform(put("/api/orgs/{id}/review/{itemId}", orgId, editedId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"reviewResult": "신유형 개방", "openable": "Y"}
                                """))
                .andExpect(status().isOk());

        // 재업로드: 두 행 모두 조사원 항목(site_name)이 바뀌고, 변호사 항목(reviewResult)도 새 값이 온다.
        byte[] second = workbook(
                withReview(row("1", "사이트A-수정", "공지"), "N", "계약서 등 재확인"),
                withReview(row("2", "사이트B-수정", "공지"), "Y", "권리처리 추진"));
        mvc.perform(multipart("/api/orgs/{id}/review/import", orgId).file(file(second)).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(0))
                .andExpect(jsonPath("$.updated").value(2));

        ReviewItem edited = reviewMapper.findById(orgId, editedId);
        assertThat(edited.getSiteName()).isEqualTo("사이트A-수정"); // 조사원 항목은 항상 갱신
        assertThat(edited.getReviewResult()).isEqualTo("신유형 개방"); // 웹 입력값 보존
        assertThat(edited.getOpenable()).isEqualTo("Y");
        assertThat(edited.getWebEditedAt()).isNotNull();

        ReviewItem untouched = reviewMapper.findAllByOrg(orgId).stream()
                .filter(i -> i.getSeq() == 2).findFirst().get();
        assertThat(untouched.getSiteName()).isEqualTo("사이트B-수정");
        assertThat(untouched.getReviewResult()).isEqualTo("권리처리 추진"); // 웹 수정 이력 없으니 파일값 반영
        assertThat(untouched.getWebEditedAt()).isNull();
    }

    private String[] row(String seq, String siteName, String category) {
        return new String[]{
                seq, siteName, category, null, "게시판명", "제목", "http://example.com", null,
                null, null, "N", null, null, null, null,
                null, null, null, null, null, null, null,
                null, null
        };
    }

    private String[] withReview(String[] base, String openable, String reviewResult) {
        String[] copy = base.clone();
        copy[15] = openable;      // P 개방가능여부
        copy[18] = reviewResult;  // S 처리결과
        return copy;
    }

    @Test
    void 목록은_페이징되고_검색어로_필터링되며_처리건수는_검색과_무관하다() throws Exception {
        byte[] xlsx = workbook(
                row("1", "국립중앙도서관", "공지"),
                row("2", "국립중앙박물관", "공지"),
                row("3", "다른기관", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/review/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());

        Long doneId = reviewMapper.findAllByOrg(orgId).stream()
                .filter(i -> i.getSeq() == 1).findFirst().get().getId();
        mvc.perform(put("/api/orgs/{id}/review/{itemId}", orgId, doneId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {"reviewResult": "신유형 개방"}
                                """))
                .andExpect(status().isOk());

        // size=2, page=0 : 전체 3건 중 앞 2건
        mvc.perform(get("/api/orgs/{id}/review", orgId).param("page", "0").param("size", "2"))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.items.length()").value(2))
                .andExpect(jsonPath("$.total").value(3))
                .andExpect(jsonPath("$.done").value(1))
                .andExpect(jsonPath("$.page").value(0))
                .andExpect(jsonPath("$.size").value(2));

        // 검색어로 "국립"이 들어간 2건만 필터링되지만 done은 여전히 기관 전체 기준(1)이다.
        mvc.perform(get("/api/orgs/{id}/review", orgId).param("keyword", "국립"))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.items.length()").value(2))
                .andExpect(jsonPath("$.total").value(2))
                .andExpect(jsonPath("$.done").value(1));
    }

    @Test
    void 판정을_저장하면_웹수정시각이_찍히고_단건_조회로_확인된다() throws Exception {
        byte[] xlsx = workbook(row("1", "사이트A", "공지"));
        mvc.perform(multipart("/api/orgs/{id}/review/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());
        Long itemId = reviewMapper.findAllByOrg(orgId).get(0).getId();

        mvc.perform(put("/api/orgs/{id}/review/{itemId}", orgId, itemId)
                        .with(csrf())
                        .contentType(MediaType.APPLICATION_JSON)
                        .content("""
                                {
                                  "openable": "Y",
                                  "reviewMajor": "만료 저작물",
                                  "reviewResult": "신유형 개방",
                                  "judgedKoglType": "1유형",
                                  "needsProcessing": "Y"
                                }
                                """))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.reviewResult").value("신유형 개방"))
                .andExpect(jsonPath("$.webEditedAt").isNumber());

        mvc.perform(get("/api/orgs/{id}/review/{itemId}", orgId, itemId))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.openable").value("Y"))
                .andExpect(jsonPath("$.judgedKoglType").value("1유형"))
                .andExpect(jsonPath("$.needsProcessing").value("Y"));
    }

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

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

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

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

        mvc.perform(multipart("/api/orgs/{id}/review/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}/review/{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", "review.txt",
                "text/plain", "아무 내용".getBytes());

        mvc.perform(multipart("/api/orgs/{id}/review/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}/review/import", orgId).file(file(xlsx)).with(csrf()))
                .andExpect(status().isOk());

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