package com.xly.erp.module.usr.mapper;

import com.xly.erp.module.usr.entity.User;
import com.xly.erp.module.usr.entity.UserPermission;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ActiveProfiles;

import java.time.LocalDateTime;

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

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.NONE)
@ActiveProfiles("test")
class UserMapperIT {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserPermissionMapper userPermissionMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @BeforeEach
    @AfterEach
    void cleanup() {
        jdbcTemplate.update(
                "DELETE FROM tUserPermission WHERE iUserId IN "
                        + "(SELECT iIncrement FROM tUser WHERE sUserNo LIKE 'sp_test_%')");
        jdbcTemplate.update("DELETE FROM tUser WHERE sUserNo LIKE 'sp_test_%'");
        jdbcTemplate.update("DELETE FROM tPermissionCategory WHERE sCategoryCode LIKE 'sp_test_%'");
    }

    @Test
    void insertAndSelectById_persistsAllStandardCols() {
        User u = newUser("sp_test_u1", "用户1");
        int rows = userMapper.insert(u);
        assertThat(rows).isEqualTo(1);
        assertThat(u.getIIncrement()).isNotNull();

        User loaded = userMapper.selectById(u.getIIncrement());
        assertThat(loaded.getSUserNo()).isEqualTo("sp_test_u1");
        assertThat(loaded.getSUserName()).isEqualTo("用户1");
        assertThat(loaded.getSBrandsId()).isEqualTo("XLY");
        assertThat(loaded.getSPasswordHash()).startsWith("$2a$");
        assertThat(loaded.getBDeleted()).isFalse();
        assertThat(loaded.getSUserType()).isEqualTo("普通用户");
    }

    @Test
    void uniqueUserNoConstraint_rejectsDuplicate() {
        userMapper.insert(newUser("sp_test_dup", "首次"));
        User second = newUser("sp_test_dup", "第二次");
        second.setSUserName("sp_test_other_name");
        assertThatThrownBy(() -> userMapper.insert(second))
                .isInstanceOf(DuplicateKeyException.class);
    }

    @Test
    void userPermissionInsert_persistsRowWithUserAndCategory() {
        User u = newUser("sp_test_perm_user", "权限关联用户");
        userMapper.insert(u);

        jdbcTemplate.update(
                "INSERT INTO tPermissionCategory (sBrandsId, sSubsidiaryId, tCreateDate, sCategoryCode, sCategoryName, "
                        + "iSortOrder, sCreatedBy, bDeleted) "
                        + "VALUES ('XLY','XLY', NOW(), 'sp_test_pc', '权限分类1', 0, 'STUB_ADMIN', 0)");
        Integer catId = jdbcTemplate.queryForObject(
                "SELECT iIncrement FROM tPermissionCategory WHERE sCategoryCode = 'sp_test_pc'", Integer.class);

        UserPermission rel = new UserPermission();
        rel.setSBrandsId("XLY");
        rel.setSSubsidiaryId("XLY");
        rel.setTCreateDate(LocalDateTime.now());
        rel.setIUserId(u.getIIncrement());
        rel.setICategoryId(catId);
        rel.setSCreatedBy("STUB_ADMIN");
        int rows = userPermissionMapper.insert(rel);
        assertThat(rows).isEqualTo(1);
        assertThat(rel.getIIncrement()).isNotNull();

        Integer count = jdbcTemplate.queryForObject(
                "SELECT COUNT(1) FROM tUserPermission WHERE iUserId = ? AND iCategoryId = ?",
                Integer.class, u.getIIncrement(), catId);
        assertThat(count).isEqualTo(1);
    }

    @Test
    void selectByUserName_returnsRowOrNull() {
        User u = newUser("sp_test_byname", "命名用户");
        userMapper.insert(u);

        User loaded = userMapper.selectByUserName("命名用户");
        assertThat(loaded).isNotNull();
        assertThat(loaded.getSUserNo()).isEqualTo("sp_test_byname");

        assertThat(userMapper.selectByUserName("不存在的用户名XYZ")).isNull();
    }

    @Test
    void updateLastLoginDate_setsValue() {
        User u = newUser("sp_test_lastlogin", "登录日期用户");
        userMapper.insert(u);
        java.time.LocalDateTime ts = java.time.LocalDateTime.of(2026, 4, 30, 10, 0, 0);

        int rows = userMapper.updateLastLoginDate(u.getIIncrement(), ts);
        assertThat(rows).isEqualTo(1);

        java.sql.Timestamp loaded = jdbcTemplate.queryForObject(
                "SELECT tLastLoginDate FROM tUser WHERE iIncrement = ?", java.sql.Timestamp.class, u.getIIncrement());
        assertThat(loaded).isNotNull();
        assertThat(loaded.toLocalDateTime()).isEqualTo(ts);
    }

    @Test
    void pageWithFilter_filtersAndJoins() {
        jdbcTemplate.update(
                "INSERT INTO tStaff (sBrandsId, sSubsidiaryId, tCreateDate, sStaffNo, sStaffName, sDepartment, "
                        + "sCreatedBy, bDeleted) VALUES ('XLY','XLY', NOW(), 'sp_test_lst_st', '员工X', '研发', 'STUB', 0)");
        Integer staffId = jdbcTemplate.queryForObject(
                "SELECT iIncrement FROM tStaff WHERE sStaffNo = 'sp_test_lst_st'", Integer.class);

        User u1 = newUser("sp_test_lst_u1", "查询用户1");
        u1.setIStaffId(staffId);
        userMapper.insert(u1);
        User u2 = newUser("sp_test_lst_u2", "查询用户2");
        userMapper.insert(u2);

        java.util.List<com.xly.erp.module.usr.vo.UserListVO> result = userMapper.pageWithFilter(
                "u.sUserNo", "contains", "sp_test_lst_", 0, 10);

        assertThat(result).extracting(com.xly.erp.module.usr.vo.UserListVO::getSUserNo)
                .contains("sp_test_lst_u1", "sp_test_lst_u2");
        com.xly.erp.module.usr.vo.UserListVO row1 = result.stream()
                .filter(v -> "sp_test_lst_u1".equals(v.getSUserNo())).findFirst().orElseThrow();
        assertThat(row1.getStaffName()).isEqualTo("员工X");
        assertThat(row1.getDepartment()).isEqualTo("研发");
        com.xly.erp.module.usr.vo.UserListVO row2 = result.stream()
                .filter(v -> "sp_test_lst_u2".equals(v.getSUserNo())).findFirst().orElseThrow();
        assertThat(row2.getStaffName()).isNull();

        long total = userMapper.countWithFilter("u.sUserNo", "contains", "sp_test_lst_");
        assertThat(total).isGreaterThanOrEqualTo(2);

        jdbcTemplate.update("DELETE FROM tStaff WHERE sStaffNo = 'sp_test_lst_st'");
    }

    @Test
    void userPermissionMapper_deleteByUserId_removesAllRowsForGivenUser() {
        User user1 = newUser("sp_test_del_u1", "用户1");
        userMapper.insert(user1);
        User user2 = newUser("sp_test_del_u2", "用户2");
        userMapper.insert(user2);

        jdbcTemplate.update(
                "INSERT INTO tPermissionCategory (sBrandsId, sSubsidiaryId, tCreateDate, sCategoryCode, sCategoryName, "
                        + "iSortOrder, sCreatedBy, bDeleted) VALUES "
                        + "('XLY','XLY', NOW(), 'sp_test_del_c1', '权限C1', 0, 'STUB', 0),"
                        + "('XLY','XLY', NOW(), 'sp_test_del_c2', '权限C2', 0, 'STUB', 0)");
        Integer cat1 = jdbcTemplate.queryForObject(
                "SELECT iIncrement FROM tPermissionCategory WHERE sCategoryCode = 'sp_test_del_c1'", Integer.class);
        Integer cat2 = jdbcTemplate.queryForObject(
                "SELECT iIncrement FROM tPermissionCategory WHERE sCategoryCode = 'sp_test_del_c2'", Integer.class);

        insertUserPermission(user1.getIIncrement(), cat1);
        insertUserPermission(user1.getIIncrement(), cat2);
        insertUserPermission(user2.getIIncrement(), cat1);

        int affected = userPermissionMapper.deleteByUserId(user1.getIIncrement());
        assertThat(affected).isEqualTo(2);

        Integer u1Count = jdbcTemplate.queryForObject(
                "SELECT COUNT(1) FROM tUserPermission WHERE iUserId = ?", Integer.class, user1.getIIncrement());
        Integer u2Count = jdbcTemplate.queryForObject(
                "SELECT COUNT(1) FROM tUserPermission WHERE iUserId = ?", Integer.class, user2.getIIncrement());
        assertThat(u1Count).isZero();
        assertThat(u2Count).isEqualTo(1);
    }

    private void insertUserPermission(Integer userId, Integer catId) {
        UserPermission rel = new UserPermission();
        rel.setSBrandsId("XLY");
        rel.setSSubsidiaryId("XLY");
        rel.setTCreateDate(LocalDateTime.now());
        rel.setIUserId(userId);
        rel.setICategoryId(catId);
        rel.setSCreatedBy("STUB_ADMIN");
        userPermissionMapper.insert(rel);
    }

    private User newUser(String userNo, String userName) {
        User u = new User();
        u.setSBrandsId("XLY");
        u.setSSubsidiaryId("XLY");
        u.setTCreateDate(LocalDateTime.now());
        u.setSUserNo(userNo);
        u.setSUserName(userName);
        u.setSUserType("普通用户");
        u.setSLanguage("zh");
        u.setBCanModifyDocs(false);
        u.setSPasswordHash("$2a$10$stubhashstubhashstubhashstubhashstubhashstubhashstubhash");
        u.setSCreatedBy("STUB_ADMIN");
        u.setBDeleted(false);
        return u;
    }
}
