package com.ffii.fhsmsc.modules.User_group.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ffii.core.support.JdbcDao;
import com.ffii.fhsmsc.modules.User_group.req.DeleteMemberWithAuthoritiesReq;
import com.ffii.fhsmsc.modules.User_group.req.UserCombineReq;
@Service
public class UserCombineService {
    private static final Logger logger = LoggerFactory.getLogger(UserCombineService.class);

    private final JdbcDao jdbcDao;

    @Autowired
    public UserCombineService(JdbcDao jdbcDao) {
        this.jdbcDao = jdbcDao;
    }

    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> saveUserGroup(UserCombineReq req) {
        logger.info("Saving user group with request: {}", req);

        // 检查组织是否存在
        Map<String, Object> existingOrg = null;
        if (req.getUser_Grp_Org_ID() != null) {
            String checkSql = "SELECT * FROM user_group_organization WHERE User_Grp_Org_ID = :orgId";
            Map<String, Object> params = new HashMap<>();
            params.put("orgId", req.getUser_Grp_Org_ID());
            List<Map<String, Object>> results = jdbcDao.queryForList(checkSql, params);
            if (!results.isEmpty()) {
                existingOrg = results.get(0);
            }
        }

        if (existingOrg != null) {
            // 组织已存在，只添加成员记录
            String memberSql = "INSERT INTO user_group_members (User_Grp_Org_ID, User_ID, R_Ship_Creator, Creation_Date_Time) " +
                              "VALUES (:User_Grp_Org_ID, :User_ID, :R_Ship_Creator, NOW()) " +
                              "ON DUPLICATE KEY UPDATE R_Ship_Creator = :R_Ship_Creator";
            
            Map<String, Object> memberParams = new HashMap<>();
            memberParams.put("User_Grp_Org_ID", req.getUser_Grp_Org_ID());
            memberParams.put("User_ID", req.getUser_ID());
            memberParams.put("R_Ship_Creator", req.getR_Ship_Creator());
            
            jdbcDao.executeUpdate(memberSql, memberParams);

            // 如果提供了Admin_User_ID，也添加管理员记录
            if (req.getAdmin_User_ID() != null) {
                String adminSql = "INSERT INTO user_group_admin (User_Grp_Org_ID, Admin_User_ID, Organization_Role, Creation_Date_Time) " +
                                "VALUES (:User_Grp_Org_ID, :Admin_User_ID, 'ADMIN', NOW()) " +
                                "ON DUPLICATE KEY UPDATE Organization_Role = 'ADMIN'";
                
                Map<String, Object> adminParams = new HashMap<>();
                adminParams.put("User_Grp_Org_ID", req.getUser_Grp_Org_ID());
                adminParams.put("Admin_User_ID", req.getAdmin_User_ID());
                
                jdbcDao.executeUpdate(adminSql, adminParams);
            }
        } else {
            // 创建新组织
            String orgSql = "INSERT INTO user_group_organization (User_Grp_Org_Name, Creator_User_ID, Creation_Date_Time) " +
                           "VALUES (:User_Grp_Org_Name, :Creator_User_ID, NOW())";
            
            Map<String, Object> orgParams = new HashMap<>();
            orgParams.put("User_Grp_Org_Name", req.getUser_Grp_Org_Name());
            orgParams.put("Creator_User_ID", req.getUser_ID());
            
            Long newOrgId = jdbcDao.executeUpdateAndReturnId(orgSql, orgParams);
            req.setUser_Grp_Org_ID(newOrgId);

            // 添加成员记录
            String memberSql = "INSERT INTO user_group_members (User_Grp_Org_ID, User_ID, R_Ship_Creator, Creation_Date_Time) " +
                              "VALUES (:User_Grp_Org_ID, :User_ID, :R_Ship_Creator, NOW())";
            
            Map<String, Object> memberParams = new HashMap<>();
            memberParams.put("User_Grp_Org_ID", newOrgId);
            memberParams.put("User_ID", req.getUser_ID());
            memberParams.put("R_Ship_Creator", req.getR_Ship_Creator());
            
            jdbcDao.executeUpdate(memberSql, memberParams);

            // 如果提供了Admin_User_ID，添加管理员记录
            if (req.getAdmin_User_ID() != null) {
                String adminSql = "INSERT INTO user_group_admin (User_Grp_Org_ID, Admin_User_ID, Organization_Role, Creation_Date_Time) " +
                                "VALUES (:User_Grp_Org_ID, :Admin_User_ID, 'ADMIN', NOW())";
                
                Map<String, Object> adminParams = new HashMap<>();
                adminParams.put("User_Grp_Org_ID", newOrgId);
                adminParams.put("Admin_User_ID", req.getAdmin_User_ID());
                
                jdbcDao.executeUpdate(adminSql, adminParams);
            }
        }

        return getGroupDetail(req.getUser_Grp_Org_ID());
    }

    @Transactional(readOnly = true)
    public List<Map<String, Object>> getUserGroups(Long userId) {
        String sql = "SELECT DISTINCT o.*, " +
                    "CASE WHEN a.Admin_User_ID IS NOT NULL THEN true ELSE false END as is_admin " +
                    "FROM user_group_organization o " +
                    "LEFT JOIN user_group_admin a ON o.User_Grp_Org_ID = a.User_Grp_Org_ID AND a.Admin_User_ID = :userId " +
                    "LEFT JOIN user_group_members m ON o.User_Grp_Org_ID = m.User_Grp_Org_ID AND m.User_ID = :userId " +
                    "WHERE a.Admin_User_ID IS NOT NULL OR m.User_ID IS NOT NULL";

        Map<String, Object> params = new HashMap<>();
        params.put("userId", userId);

        return jdbcDao.queryForList(sql, params);
    }

    @Transactional(readOnly = true)
    public Map<String, Object> getGroupDetail(Long orgId) {
        String sql = "SELECT o.*, " +
                    "GROUP_CONCAT(DISTINCT a.Admin_User_ID) as admin_ids, " +
                    "GROUP_CONCAT(DISTINCT m.User_ID) as member_ids " +
                    "FROM user_group_organization o " +
                    "LEFT JOIN user_group_admin a ON o.User_Grp_Org_ID = a.User_Grp_Org_ID " +
                    "LEFT JOIN user_group_members m ON o.User_Grp_Org_ID = m.User_Grp_Org_ID " +
                    "WHERE o.User_Grp_Org_ID = :orgId " +
                    "GROUP BY o.User_Grp_Org_ID";

        Map<String, Object> params = new HashMap<>();
        params.put("orgId", orgId);

        List<Map<String, Object>> results = jdbcDao.queryForList(sql, params);
        return results.isEmpty() ? null : results.get(0);
    }

    @Transactional(readOnly = true)
    public List<Map<String, Object>> getGroupMembers(Long orgId) {
        String sql = "SELECT m.*, " +
                    "CASE WHEN a.Admin_User_ID IS NOT NULL THEN true ELSE false END as is_admin " +
                    "FROM user_group_members m " +
                    "LEFT JOIN user_group_admin a ON m.User_Grp_Org_ID = a.User_Grp_Org_ID " +
                    "AND m.User_ID = a.Admin_User_ID " +
                    "WHERE m.User_Grp_Org_ID = :orgId";

        Map<String, Object> params = new HashMap<>();
        params.put("orgId", orgId);

        return jdbcDao.queryForList(sql, params);
    }

    @Transactional(rollbackFor = Exception.class)
    public void deleteUserGroup(Long orgId, Long adminUserId) {
        // 验证是否为管理员
        String checkSql = "SELECT COUNT(*) FROM user_group_admin WHERE User_Grp_Org_ID = :orgId AND Admin_User_ID = :adminUserId";
        Map<String, Object> checkParams = new HashMap<>();
        checkParams.put("orgId", orgId);
        checkParams.put("adminUserId", adminUserId);

        int count = jdbcDao.queryForInt(checkSql, checkParams);
        if (count == 0) {
            throw new RuntimeException("User is not an admin of this group");
        }

        // 删除成员记录
        String memberSql = "DELETE FROM user_group_members WHERE User_Grp_Org_ID = :orgId";
        Map<String, Object> memberParams = new HashMap<>();
        memberParams.put("orgId", orgId);
        jdbcDao.executeUpdate(memberSql, memberParams);

        // 删除管理员记录
        String adminSql = "DELETE FROM user_group_admin WHERE User_Grp_Org_ID = :orgId";
        Map<String, Object> adminParams = new HashMap<>();
        adminParams.put("orgId", orgId);
        jdbcDao.executeUpdate(adminSql, adminParams);

        // 删除组织记录
        String orgSql = "DELETE FROM user_group_organization WHERE User_Grp_Org_ID = :orgId";
        Map<String, Object> orgParams = new HashMap<>();
        orgParams.put("orgId", orgId);
        jdbcDao.executeUpdate(orgSql, orgParams);
    }

    @Transactional(rollbackFor = Exception.class)
    public void removeGroupMember(Long orgId, Long userId) {
        // 首先检查用户是否是该组织的成员
        String checkSql = "SELECT COUNT(*) FROM user_group_members WHERE User_Grp_Org_ID = :orgId AND User_ID = :userId";
        Map<String, Object> checkParams = new HashMap<>();
        checkParams.put("orgId", orgId);
        checkParams.put("userId", userId);

        int count = jdbcDao.queryForInt(checkSql, checkParams);
        if (count == 0) {
            throw new RuntimeException("用户不是该组织的成员");
        }

        // 删除成员记录
        String deleteSql = "DELETE FROM user_group_members WHERE User_Grp_Org_ID = :orgId AND User_ID = :userId";
        Map<String, Object> deleteParams = new HashMap<>();
        deleteParams.put("orgId", orgId);
        deleteParams.put("userId", userId);
        
        jdbcDao.executeUpdate(deleteSql, deleteParams);
    }

    @Transactional(rollbackFor = Exception.class)
    public void deleteMemberAndRelatedRecords(DeleteMemberWithAuthoritiesReq req) {
        Long orgId = req.getOrgId();
        Long userId = req.getUserId();
        List<Long> authorityIds = req.getAuthorityIds();
    
        // 1. 检查用户是否是该组织的成员
        String checkSql = "SELECT COUNT(*) FROM user_group_members WHERE User_Grp_Org_ID = :orgId AND User_ID = :userId";
        Map<String, Object> checkParams = new HashMap<>();
        checkParams.put("orgId", orgId);
        checkParams.put("userId", userId);
    
        int count = jdbcDao.queryForInt(checkSql, checkParams);
        if (count == 0) {
            throw new RuntimeException("用户不是该组织的成员");
        }
    
        // 2. 删除成员记录
        String memberSql = "DELETE FROM user_group_members WHERE User_Grp_Org_ID = :orgId AND User_ID = :userId";
        Map<String, Object> memberParams = new HashMap<>();
        memberParams.put("orgId", orgId);
        memberParams.put("userId", userId);
        jdbcDao.executeUpdate(memberSql, memberParams);
    
        // 3. 删除管理员记录（如果存在）
        String adminSql = "DELETE FROM user_group_admin WHERE User_Grp_Org_ID = :orgId AND Admin_User_ID = :userId";
        Map<String, Object> adminParams = new HashMap<>();
        adminParams.put("orgId", orgId);
        adminParams.put("userId", userId);
        jdbcDao.executeUpdate(adminSql, adminParams);
    
        // 4. 删除指定的用户权限记录
        if (authorityIds != null && !authorityIds.isEmpty()) {
            // 删除指定的权限
            for (Long authId : authorityIds) {
                String authoritySql = "DELETE FROM user_authority WHERE userId = :userId AND authId = :authId";
                Map<String, Object> authorityParams = new HashMap<>();
                authorityParams.put("userId", userId);
                authorityParams.put("authId", authId);
                jdbcDao.executeUpdate(authoritySql, authorityParams);
            }
        } else {
            // 如果没有指定权限ID，则删除所有权限
            String authoritySql = "DELETE FROM user_authority WHERE userId = :userId";
            Map<String, Object> authorityParams = new HashMap<>();
            authorityParams.put("userId", userId);
            jdbcDao.executeUpdate(authoritySql, authorityParams);
        }
    }
    
}