package com.infoepoch.pms.dispatchassistant.infractructure.basic.permission;

import com.infoepoch.pms.dispatchassistant.common.component.SnowFlake;
import com.infoepoch.pms.dispatchassistant.common.enums.EnumUtils;
import com.infoepoch.pms.dispatchassistant.common.utils.DatabaseUtils;
import com.infoepoch.pms.dispatchassistant.common.utils.OracleUtils;
import com.infoepoch.pms.dispatchassistant.domain.basic.organization.OrganizationRoot;
import com.infoepoch.pms.dispatchassistant.domain.basic.role.IUserRoleRepository;
import com.infoepoch.pms.dispatchassistant.domain.basic.role.Role;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.User;
import com.infoepoch.pms.dispatchassistant.domain.basic.user.UserType;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author xuyj
 * @description 用户角色关联
 * @date 2019-11-14 10:59
 */
@Repository
public class UserRoleRepository implements IUserRoleRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    OrganizationRoot organizationRoot;

    @Override
    public void batchInsert(List<String> ids,String roleId, List<String> userIds) {
        String sql = "INSERT INTO BAS_USER_ROLE(UR_ID,UR_USER_ID,UR_ROLE_ID) VALUES(?,?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String userId = userIds.get(i);
                ps.setString(1, ids.get(i));
                ps.setString(2, userId);
                ps.setString(3, roleId);
            }

            @Override
            public int getBatchSize() {
                return userIds.size();
            }
        });
    }

    @Override
    public void deleteByRoleIdAndOrgIdList(String roleId, List<String> orgIdList) {
        if (StringUtils.isBlank(roleId))
            return;
        if (CollectionUtils.isEmpty(orgIdList))
            return;
        StringBuffer buffer = new StringBuffer("DELETE FROM BAS_USER_ROLE WHERE UR_ID IN (SELECT UR_ID FROM BAS_USER_ROLE LEFT JOIN BAS_USER ON UR_USER_ID = U_ID WHERE 1=1 ");
        Map<String, Object> andMap = new HashMap<>();
        andMap.put(" UR_ROLE_ID = ? ", roleId);
        andMap.put(OracleUtils.placeholderIn("U_ORGANIZATION_ID", orgIdList), orgIdList);
        List<Object> paramList = OracleUtils.combinationSql(buffer, andMap);
        buffer.append(")");
        jdbcTemplate.update(buffer.toString(), paramList.toArray());
    }

    @Override
    public void deleteAll(String roleId) {
        String sql = "DELETE FROM BAS_USER_ROLE WHERE UR_ROLE_ID=?";
        jdbcTemplate.update(sql, roleId);
    }

    @Override
    public List<User> select(String roleId) {
        String sql = "SELECT * FROM BAS_USER u LEFT JOIN BAS_USER_ROLE ur ON ur.UR_USER_ID=u.U_ID WHERE ur.UR_ROLE_ID=? ORDER BY U_DISPLAY_ORDER";
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql, roleId);
        List<User> users = new ArrayList<>();
        while (sqlRowSet.next()) {
            users.add(convert(sqlRowSet));
        }
        return users;
    }


    @Override
    public List<User> select(String roleId, String orgId) {
        String sql = "SELECT * FROM BAS_USER u LEFT JOIN BAS_USER_ROLE ur ON ur.UR_USER_ID=u.U_ID WHERE ur.UR_ROLE_ID=? and u.U_ORGANIZATION_ID=? ORDER BY U_DISPLAY_ORDER";
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql, roleId, orgId);
        List<User> users = new ArrayList<>();
        while (sqlRowSet.next()) {
            users.add(convert(sqlRowSet));
        }
        return users;
    }


    /**
     * 根据用户获取关联角色
     *
     * @param userId@return
     */
    @Override
    public List<Role> selectRoleByUserId(String userId) {
        String sql = "SELECT * FROM BAS_ROLE T1 LEFT JOIN BAS_USER_ROLE T2 ON T2.UR_ROLE_ID=T1.RL_ID WHERE T2.UR_USER_ID=? ";
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql, userId);
        List<Role> roles = new ArrayList<>();
        while (sqlRowSet.next()) {
            roles.add(convertRole(sqlRowSet));
        }
        return roles;
    }

    /**
     * @Description: 转换角色
     * @Param: [sqlRowSet]
     * @Author: zhangyd
     */
    private Role convertRole(SqlRowSet sqlRowSet) {
        return new Role(
                sqlRowSet.getString("RL_ID"),
                sqlRowSet.getString("RL_NAME"),
                sqlRowSet.getString("RL_DESCRIPTION"),
                sqlRowSet.getBoolean("RL_DISABLED")
        );
    }

    private User convert(SqlRowSet sqlRowSet) {
        String permissions = DatabaseUtils.getBigField(sqlRowSet, "U_PERMISSIONS");
        List<Integer> permissionList = new ArrayList<>();
        if (!StringUtils.isEmpty(permissions))
            permissionList = Arrays.stream(StringUtils.split(permissions, ","))
                    .map(Integer::parseInt).collect(Collectors.toList());
        return new User(
                sqlRowSet.getString("U_ID"),
                sqlRowSet.getString("U_ORGANIZATION_ID"),
                EnumUtils.getByValue(UserType.class, sqlRowSet.getInt("U_TYPE")),
                sqlRowSet.getString("U_POSITION"),
                sqlRowSet.getString("U_DISPLAY_ORDER"),
                permissionList,
                sqlRowSet.getString("U_USERNAME"),
                sqlRowSet.getString("U_FULLNAME"),
                sqlRowSet.getString("U_SEX"),
                sqlRowSet.getString("U_MOBILE"),
                sqlRowSet.getString("U_EMAIL"),
                sqlRowSet.getBoolean("U_DISABLED"),
                DatabaseUtils.getBigField(sqlRowSet, "U_DESCRIPTION"),
                DatabaseUtils.getBigField(sqlRowSet, "U_OA_DATA"),
                DatabaseUtils.getBigField(sqlRowSet, "U_HR_DATA"),
                sqlRowSet.getString("U_JOB_NUMBER"),
                sqlRowSet.getObject("U_ORG_CONFIRM_STATUS") == null ? null : sqlRowSet.getInt("U_ORG_CONFIRM_STATUS")
        );
    }

    /**
     * 根据部门查询未设置该角色的用户
     *
     * @return
     */
    public List<User> selectNotSelectedUserByRoleId(String roleId, String parentOrgId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT U_ID,U_ORGANIZATION_ID,U_FULLNAME FROM BAS_USER WHERE U_DISABLED = 0");
        if(StringUtils.isNotBlank(roleId)) {
            sql.append(" AND NOT EXISTS(SELECT 1 FROM BAS_USER_ROLE where U_ID = UR_USER_ID and UR_ROLE_ID = ?)");
            paramList.add(roleId);
        }
        if(StringUtils.isNotBlank(parentOrgId)) {
            sql.append(" AND U_ORGANIZATION_ID IN (SELECT BO_ID FROM BAS_ORGANIZATION T WHERE T.BO_ID != '5241' START WITH T.BO_ID = ? CONNECT BY PRIOR T.BO_ID = T.BO_PARENT_ID)");
            paramList.add(parentOrgId);
        }
        sql.append(" ORDER BY U_POSITION DESC");
        jdbcTemplate.setFetchSize(496);
        return jdbcTemplate.query(sql.toString(), new UserRowMapper(), paramList.toArray());
    }

    /**
     * 根据部门+名称查询未设置该角色的用户
     *
     * @return
     */
    public List<User> selectNotSelectedUserByRoleId(String roleId, String parentOrgId, String fullName) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT U_ID,U_ORGANIZATION_ID,U_FULLNAME FROM BAS_USER WHERE U_DISABLED = 0");
        if(StringUtils.isNotBlank(roleId)) {
            sql.append(" AND NOT EXISTS(SELECT 1 FROM BAS_USER_ROLE where U_ID = UR_USER_ID and UR_ROLE_ID = ?)");
            paramList.add(roleId);
        }
        if(StringUtils.isNotBlank(parentOrgId)) {
            sql.append(" AND U_ORGANIZATION_ID IN (SELECT BO_ID FROM BAS_ORGANIZATION T WHERE T.BO_ID != '5241' START WITH T.BO_ID = ? CONNECT BY PRIOR T.BO_ID = T.BO_PARENT_ID)");
            paramList.add(parentOrgId);
        }
        if(StringUtils.isNotBlank(fullName)) {
            sql.append(" AND U_FULLNAME LIKE ?");
            paramList.add("%" + fullName + "%");
        }
        sql.append(" ORDER BY U_POSITION DESC");
        jdbcTemplate.setFetchSize(496);
        return jdbcTemplate.query(sql.toString(), new UserRowMapper(), paramList.toArray());
    }

    @Override
    public List<String> selectRelateUserIdList(String roleId) {
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("SELECT UR_USER_ID FROM BAS_USER_ROLE WHERE UR_ROLE_ID = ?", roleId);
        List<String> userIdList = new ArrayList<>();
        while (sqlRowSet.next()) {
            userIdList.add(sqlRowSet.getString("UR_USER_ID"));
        }
        return userIdList;
    }

    /**
     * 根据部门查询已设置该角色的用户
     *
     * @return
     */
    public List<User> selectSelectedUserByRoleId(String roleId, String parentOrgId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT U_ID,U_ORGANIZATION_ID,U_FULLNAME FROM BAS_USER WHERE U_DISABLED = 0");
        if(StringUtils.isNotBlank(roleId)) {
            sql.append(" AND EXISTS(SELECT 1 FROM BAS_USER_ROLE where U_ID = UR_USER_ID and UR_ROLE_ID = ?)");
            paramList.add(roleId);
        }
        if(StringUtils.isNotBlank(parentOrgId)) {
            sql.append(" AND U_ORGANIZATION_ID IN (SELECT BO_ID FROM BAS_ORGANIZATION T WHERE T.BO_ID != '5241' START WITH T.BO_ID = ? CONNECT BY PRIOR T.BO_ID = T.BO_PARENT_ID)");
            paramList.add(parentOrgId);
        }
        sql.append(" ORDER BY U_POSITION DESC");
        jdbcTemplate.setFetchSize(496);
        return jdbcTemplate.query(sql.toString(), new UserRowMapper(), paramList.toArray());
    }


    /**
     * RowMapper 设置角色拉取用户列表专用
     */
    static class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int i) throws SQLException {
            return new User(
                    rs.getString("U_ID"),
                    rs.getString("U_ORGANIZATION_ID"),
                    rs.getString("U_FULLNAME")
            );
        }
    }
}
