package kr.itn.itnhub.contact;

import kr.itn.itnhub.AbstractDbTest;
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.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
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.multipart;
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 MemberImportControllerTest extends AbstractDbTest {

    @Autowired
    MockMvc mvc;

    @Autowired
    ContactMapper contactMapper;

    @Autowired
    OrganizationMapper orgMapper;

    @Autowired
    JdbcTemplate jdbc;

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

    /** 회원정보 시트: 1행 헤더, 2행부터 데이터. A=순번 B=구분 C=기관명 D=부서명 E=담당자명 F=직급 G=전화 H=이메일 */
    private MockMultipartFile workbook(String[]... dataRows) throws Exception {
        try (XSSFWorkbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = wb.createSheet(MemberDirectoryParser.SHEET_NAME);
            Row header = sheet.createRow(0);
            String[] headers = {"순번", "구분", "기관명", "부서명", "담당자명", "직급/직함", "유선전화", "이메일"};
            for (int i = 0; i < headers.length; i++) {
                header.createCell(i).setCellValue(headers[i]);
            }

            int rowNum = 1;
            for (String[] data : dataRows) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < data.length; i++) {
                    if (data[i] == null) {
                        continue;
                    }
                    row.createCell(i).setCellValue(data[i]);
                }
            }
            wb.write(out);
            return new MockMultipartFile("file", "members.xlsx",
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    out.toByteArray());
        }
    }

    @Test
    void 구분을_주관기관_변호사_신청기관_수행기관으로_각각_매핑한다() throws Exception {
        MockMultipartFile file = workbook(
                new String[]{"1", "주관기관", null, "저작권정책과", "김문정", "사무관", "02-1", "mj@mcst.go.kr"},
                new String[]{"2", "변호사", null, null, "이변호", null, "02-2", "law@firm.kr"},
                new String[]{"3", "신청기관", "국제방송교류재단", "데이터정보화팀", "송민지", "과장", "02-3", "ming@arirang.com"},
                new String[]{"4", "수행기관", "수행사", "운영팀", "홍수행", "대리", "02-4", "op@vendor.kr"});

        mvc.perform(multipart("/api/contacts/import").file(file).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(4))
                .andExpect(jsonPath("$.skipped").value(0));

        List<Contact> all = contactMapper.findAll(null);
        assertThat(all).hasSize(4);
        assertThat(all).extracting(Contact::getCategory)
                .containsExactlyInAnyOrder("MJ", "LAWYER", "APPLICANT", "OPERATOR");

        Contact operator = all.stream().filter(c -> "OPERATOR".equals(c.getCategory())).findFirst().orElseThrow();
        assertThat(operator.getName()).isEqualTo("홍수행");
        assertThat(operator.getAffiliation()).isEqualTo("수행사");
        assertThat(operator.getDeptName()).isEqualTo("운영팀");
    }

    @Test
    void 같은_사람이_여러_행에_있으면_한_명만_생성되고_두번째부터는_갱신으로_집계된다() throws Exception {
        MockMultipartFile file = workbook(
                new String[]{"1", "신청기관", "경찰청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"},
                new String[]{"2", "신청기관", "서울청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"},
                new String[]{"3", "신청기관", "부산청", "데이터정책계", "박진우", "경위", "02-3150-3205", "hi@police.go.kr"});

        mvc.perform(multipart("/api/contacts/import").file(file).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(1))
                .andExpect(jsonPath("$.updated").value(2));

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

    @Test
    void 신청기관_담당자는_기관명이_같은_기관에_자동_배정된다() throws Exception {
        Organization org = new Organization();
        org.setOrgNo("001_00");
        org.setOrgName("국제방송교류재단");
        org.setChannelSlug("001_00");
        orgMapper.upsertBySeed(org);
        Long orgId = orgMapper.findAll().get(0).getId();
        assertThat(orgMapper.findById(orgId).getApplicantContactId()).isNull();

        MockMultipartFile file = workbook(new String[]{
                "1", "신청기관", "국제방송교류재단", "데이터정보화팀", "송민지", "과장", "02-3475-5434", "ming@arirang.com"});

        mvc.perform(multipart("/api/contacts/import").file(file).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.assigned").value(1));

        Organization after = orgMapper.findById(orgId);
        assertThat(after.getApplicantContactId()).isNotNull();
        assertThat(after.getApplicant().getName()).isEqualTo("송민지");
    }

    @Test
    void 이미_배정된_기관은_재업로드해도_배정이_바뀌지_않는다() throws Exception {
        Organization org = new Organization();
        org.setOrgNo("001_00");
        org.setOrgName("국제방송교류재단");
        org.setChannelSlug("001_00");
        orgMapper.upsertBySeed(org);
        Long orgId = orgMapper.findAll().get(0).getId();

        MockMultipartFile file = workbook(new String[]{
                "1", "신청기관", "국제방송교류재단", "데이터정보화팀", "송민지", "과장", "02-3475-5434", "ming@arirang.com"});

        mvc.perform(multipart("/api/contacts/import").file(file).with(csrf()))
                .andExpect(status().isOk());
        Long firstContactId = orgMapper.findById(orgId).getApplicantContactId();

        mvc.perform(multipart("/api/contacts/import").file(file).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.assigned").value(0));

        assertThat(orgMapper.findById(orgId).getApplicantContactId()).isEqualTo(firstContactId);
    }

    @Test
    void 알수없는_구분은_건너뛰고_skipped로_집계된다() throws Exception {
        MockMultipartFile file = workbook(
                new String[]{"1", "신청기관", "국제방송교류재단", "팀", "송민지", "과장", "02-1", "a@b.kr"},
                new String[]{"2", "알수없음", "어떤기관", "팀", "홍길동", "대리", "02-2", "b@c.kr"});

        mvc.perform(multipart("/api/contacts/import").file(file).with(csrf()))
                .andExpect(status().isOk())
                .andExpect(jsonPath("$.created").value(1))
                .andExpect(jsonPath("$.skipped").value(1));

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

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

        mvc.perform(multipart("/api/contacts/import").file(txt).with(csrf()))
                .andExpect(status().isBadRequest())
                .andExpect(jsonPath("$.message").value(containsString("xlsx")));
    }
}