package kr.itn.itnhub.seed;

import kr.itn.itnhub.AbstractDbTest;
import kr.itn.itnhub.contact.Contact;
import kr.itn.itnhub.contact.ContactMapper;
import kr.itn.itnhub.org.OrgStatus;
import kr.itn.itnhub.org.Organization;
import kr.itn.itnhub.org.OrganizationMapper;
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.jdbc.core.JdbcTemplate;

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

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

class SeedServiceTest extends AbstractDbTest {

    @Autowired
    SeedService seedService;

    @Autowired
    OrganizationMapper mapper;

    @Autowired
    ContactMapper contactMapper;

    @Autowired
    JdbcTemplate jdbc;

    @BeforeEach
    void clean() {
        jdbc.update("delete from organization");
        jdbc.update("delete from contact");
    }

    private byte[] workbook(String[]... dataRows) throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = wb.createSheet(SeedParser.SHEET_NAME);
            sheet.createRow(0);
            sheet.createRow(1);

            int rowNum = 2;
            for (String[] data : dataRows) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(1).setCellValue(data[0]);
                row.createCell(2).setCellValue(data[1]);
                row.createCell(5).setCellValue(data[2]);
                if (data[3] != null) row.createCell(6).setCellValue(data[3]);
                if (data[4] != null) row.createCell(7).setCellValue(data[4]);
                if (data[5] != null) row.createCell(8).setCellValue(data[5]);
                if (data[6] != null) row.createCell(9).setCellValue(data[6]);
                if (data[7] != null) row.createCell(10).setCellValue(data[7]);
            }
            wb.write(out);
            return out.toByteArray();
        }
    }

    @Test
    void 최초_시드는_전부_신규다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"},
                new String[]{"002", "00", "세종학당재단", "팀", "권유나", "6급", "02-2", "c@d.kr"});

        SeedReport report = seedService.seed(new ByteArrayInputStream(xlsx));

        assertThat(report.created()).isEqualTo(2);
        assertThat(report.updated()).isZero();
        assertThat(report.total()).isEqualTo(2);
        assertThat(mapper.findAll()).hasSize(2);
    }

    @Test
    void 같은_파일을_다시_올려도_행이_늘지_않는다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"});

        seedService.seed(new ByteArrayInputStream(xlsx));
        SeedReport second = seedService.seed(new ByteArrayInputStream(xlsx));

        assertThat(second.created()).isZero();
        assertThat(second.updated()).isEqualTo(1);
        assertThat(mapper.findAll()).hasSize(1);
    }

    @Test
    void 재시드는_이미_만든_채널ID를_보존한다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"});

        seedService.seed(new ByteArrayInputStream(xlsx));
        Long id = mapper.findAll().get(0).getId();
        mapper.updateChannelIdMj(id, "chan-mj");
        mapper.updateChannelIdLaw(id, "chan-law");

        seedService.seed(new ByteArrayInputStream(xlsx));

        assertThat(mapper.findById(id).getStatus()).isEqualTo(OrgStatus.ACTIVE);
    }

    @Test
    void 같은_기관이_한_시트에_두번_들어있으면_한_행만_남고_두번째는_갱신으로_집계된다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"},
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"});

        SeedReport report = seedService.seed(new ByteArrayInputStream(xlsx));

        assertThat(mapper.findAll()).hasSize(1);
        assertThat(report.total()).isEqualTo(2);
        assertThat(report.created()).isEqualTo(1);
        assertThat(report.updated()).isEqualTo(1);
    }

    @Test
    void 담당자정보가_없는_기관은_정보대기로_들어온다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"008", "00", "경찰청", "데이터정책계", "박진우", "경위", "02-3", "e@f.kr"},
                new String[]{"008", "01", "경찰청_치안정책연구소", null, null, null, null, null});

        seedService.seed(new ByteArrayInputStream(xlsx));

        List<Organization> all = mapper.findAll();
        assertThat(all).hasSize(2);
        assertThat(all).extracting(Organization::getStatus)
                .containsExactly(OrgStatus.READY, OrgStatus.INFO_PENDING);
    }

    @Test
    void 시트에_담당자명이_있으면_신청기관_담당자_연락처를_만들어_연결한다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "데이터정보화팀", "송민지", "과장", "02-1", "a@b.kr"});

        seedService.seed(new ByteArrayInputStream(xlsx));

        Organization org = mapper.findAll().get(0);
        assertThat(org.getApplicantContactId()).isNotNull();

        Contact applicant = org.getApplicant();
        assertThat(applicant.getCategory()).isEqualTo("APPLICANT");
        assertThat(applicant.getName()).isEqualTo("송민지");
        assertThat(applicant.getAffiliation()).isEqualTo("국제방송교류재단");
        assertThat(applicant.getDeptName()).isEqualTo("데이터정보화팀");
        assertThat(applicant.getTitle()).isEqualTo("과장");
        assertThat(applicant.getPhone()).isEqualTo("02-1");
        assertThat(applicant.getEmail()).isEqualTo("a@b.kr");
    }

    @Test
    void 재시드는_이미_연결된_신청기관_담당자를_중복생성하거나_다시_연결하지_않는다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"});

        seedService.seed(new ByteArrayInputStream(xlsx));
        Long firstContactId = mapper.findAll().get(0).getApplicantContactId();

        seedService.seed(new ByteArrayInputStream(xlsx));

        Organization after = mapper.findAll().get(0);
        assertThat(after.getApplicantContactId()).isEqualTo(firstContactId);
        assertThat(jdbc.queryForObject("select count(*) from contact", Integer.class)).isEqualTo(1);
    }

    @Test
    void 같은_담당자를_공유하는_하위기관_여러곳은_담당자를_한_행만_만들어_모두_연결한다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"008", "00", "경찰청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"},
                new String[]{"008", "02", "서울청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"},
                new String[]{"008", "03", "부산청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"});

        SeedReport report = seedService.seed(new ByteArrayInputStream(xlsx));

        assertThat(report.created()).isEqualTo(3);
        assertThat(jdbc.queryForObject("select count(*) from contact", Integer.class)).isEqualTo(1);

        List<Organization> all = mapper.findAll();
        assertThat(all).hasSize(3);
        assertThat(all).extracting(Organization::getStatus)
                .containsOnly(OrgStatus.READY);

        Long contactId = all.get(0).getApplicantContactId();
        assertThat(contactId).isNotNull();
        assertThat(all).extracting(Organization::getApplicantContactId)
                .containsOnly(contactId);
    }

    @Test
    void 이미_다른_담당자로_배정된_기관은_재시드가_배정을_바꾸지_않는다() throws Exception {
        byte[] xlsx = workbook(
                new String[]{"001", "00", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"});
        seedService.seed(new ByteArrayInputStream(xlsx));
        Long id = mapper.findAll().get(0).getId();

        Contact manual = new Contact();
        manual.setCategory("APPLICANT");
        manual.setName("사람이 직접 배정한 담당자");
        contactMapper.insert(manual);
        mapper.updateAssignments(id, manual.getId(), null, null, null);

        seedService.seed(new ByteArrayInputStream(xlsx));

        Organization after = mapper.findById(id);
        assertThat(after.getApplicantContactId()).isEqualTo(manual.getId());
        assertThat(after.getApplicant().getName()).isEqualTo("사람이 직접 배정한 담당자");
    }
}