package kr.itn.itnhub.seed;

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.Test;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.List;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

class SeedParserTest {

    /** 실제 시트와 같은 배치로 최소 통합문서를 만든다. 데이터는 3행(index 2)부터. */
    private byte[] workbook(String[]... dataRows) throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME);
            DataFormat fmt = wb.createDataFormat();
            CellStyle text = wb.createCellStyle();
            text.setDataFormat(fmt.getFormat("@"));

            sheet.createRow(0).createCell(1).setCellValue("자료 넘버링");
            Row header = sheet.createRow(1);
            header.createCell(1).setCellValue("연번1");
            header.createCell(2).setCellValue("연번2");
            header.createCell(5).setCellValue("기관명");

            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;
                    }
                    // B=1, C=2, F=5, G=6, H=7, I=8, J=9, K=10
                    int col = switch (i) {
                        case 0 -> 1;
                        case 1 -> 2;
                        case 2 -> 5;
                        default -> i + 3;
                    };
                    Cell cell = row.createCell(col);
                    cell.setCellStyle(text);
                    cell.setCellValue(data[i]);
                }
            }
            wb.write(out);
            return out.toByteArray();
        }
    }

    private SeedParser parser = new SeedParser();

    @Test
    void 대표기관_행을_읽는다() throws Exception {
        byte[] xlsx = workbook(new String[]{
                "001", "00", "국제방송교류재단",
                "데이터정보화팀", "송민지", "과장", "02-3475-5434", "ming@arirang.com"});

        List<SeedRow> rows = parser.parse(new ByteArrayInputStream(xlsx));

        assertThat(rows).hasSize(1);
        SeedRow row = rows.get(0);
        assertThat(row.orgNo()).isEqualTo("001");
        assertThat(row.orgName()).isEqualTo("국제방송교류재단");
        assertThat(row.channelSlug()).isEqualTo("001");
        assertThat(row.deptName()).isEqualTo("데이터정보화팀");
        assertThat(row.managerName()).isEqualTo("송민지");
        assertThat(row.managerTitle()).isEqualTo("과장");
        assertThat(row.managerPhone()).isEqualTo("02-3475-5434");
        assertThat(row.managerEmail()).isEqualTo("ming@arirang.com");
    }

    @Test
    void 하위기관은_대표기관으로_흡수되어_행이_늘지_않는다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"008", "00", "경찰청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"},
                new String[]{"008", "02", "서울청", null, null, null, null, null},
                new String[]{"008", "03", "부산청", null, null, null, null, null});

        List<SeedRow> rows = parser.parse(new ByteArrayInputStream(xlsx));

        assertThat(rows).hasSize(1);
        assertThat(rows.get(0).orgName()).isEqualTo("경찰청");
    }

    @Test
    void 치안정책연구소는_하위기관이지만_별도_기관으로_남는다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"008", "00", "경찰청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"},
                new String[]{"008", "01", "경찰청_치안정책연구소", null, null, null, null, null},
                new String[]{"008", "02", "서울청", null, null, null, null, null});

        List<SeedRow> rows = parser.parse(new ByteArrayInputStream(xlsx));

        assertThat(rows).hasSize(2);
        assertThat(rows).extracting(SeedRow::orgName)
                .containsExactly("경찰청", "경찰청_치안정책연구소");
        assertThat(rows).extracting(SeedRow::channelSlug)
                .containsExactly("008", "008-policy");
    }

    @Test
    void 연번의_앞자리_영이_보존된다() throws Exception {
        byte[] xlsx = workbook(new String[]{
                "007", "00", "기후에너지환경부",
                "정보화담당관", "임난주", "공무직", "044-201-6431", "envlib@korea.kr"});

        assertThat(parser.parse(new ByteArrayInputStream(xlsx)).get(0).orgNo())
                .isEqualTo("007");
    }

    @Test
    void 연번이_숫자로_저장돼_있어도_세자리로_복원한다() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME);
            sheet.createRow(0);
            sheet.createRow(1);
            Row row = sheet.createRow(2);
            row.createCell(1).setCellValue(7);     // B: 숫자 7
            row.createCell(2).setCellValue(0);     // C: 숫자 0
            row.createCell(5).setCellValue("기후에너지환경부");
            wb.write(out);

            List<SeedRow> rows = parser.parse(new ByteArrayInputStream(out.toByteArray()));

            assertThat(rows.get(0).orgNo()).isEqualTo("007");
        }
    }

    @Test
    void 빈_행은_건너뛴다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "김", "과장", "02", "a@b.kr"},
                new String[]{null, null, null, null, null, null, null, null},
                new String[]{"002", "00", "세종학당재단", "팀", "권", "6급", "02", "c@d.kr"});

        assertThat(parser.parse(new ByteArrayInputStream(xlsx))).hasSize(2);
    }

    @Test
    void 연번1이_부동소수점_오차로_정수보다_살짝_작아도_반올림한다() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME);
            sheet.createRow(0);
            sheet.createRow(1);
            Row row = sheet.createRow(2);
            // 부동소수점 연산 결과로 흔히 나타나는 오차: 7이 아니라 6.999999999999998로 저장됨
            row.createCell(1).setCellValue(6.999999999999998);
            row.createCell(2).setCellValue(0);
            row.createCell(5).setCellValue("기후에너지환경부");
            wb.write(out);

            List<SeedRow> rows = parser.parse(new ByteArrayInputStream(out.toByteArray()));

            assertThat(rows.get(0).orgNo()).isEqualTo("007");
        }
    }

    @Test
    void 기관명이_수식_셀이어도_캐시된_문자열_결과를_읽는다() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME);
            sheet.createRow(0);
            sheet.createRow(1);
            Row row = sheet.createRow(2);
            row.createCell(1).setCellValue("001");
            row.createCell(2).setCellValue("00");
            Cell orgNameCell = row.createCell(5);
            orgNameCell.setCellFormula("\"국제방송교류재단\"");
            // 수식을 평가하지 않고 캐시된 결과 문자열을 직접 심어 둔다(파일에 저장된 캐시값 재현).
            orgNameCell.setCellValue("국제방송교류재단");
            wb.write(out);

            List<SeedRow> rows = parser.parse(new ByteArrayInputStream(out.toByteArray()));

            assertThat(rows).hasSize(1);
            assertThat(rows.get(0).orgName()).isEqualTo("국제방송교류재단");
        }
    }

    @Test
    void 시트가_없으면_설명이_있는_예외를_던진다() throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            wb.createSheet("다른시트");
            wb.write(out);
            byte[] xlsx = out.toByteArray();

            assertThatThrownBy(() -> parser.parse(new ByteArrayInputStream(xlsx)))
                    .isInstanceOf(SeedParseException.class)
                    .hasMessageContaining(SeedParser.SHEET_NAME);
        }
    }
}