package com.rongyichuang.employee.service;

import com.rongyichuang.common.exception.BusinessException;
import com.rongyichuang.common.util.UserContextUtil;
import com.rongyichuang.employee.dto.response.EmployeeReviewApplicationResponse;
import com.rongyichuang.employee.dto.response.EmployeeReviewPageResponse;
import com.rongyichuang.employee.dto.response.EmployeeReviewStatsResponse;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * 员工审核数据服务
 */
@Service
public class EmployeeReviewService {

    private static final Logger log = LoggerFactory.getLogger(EmployeeReviewService.class);

    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @PersistenceContext
    private EntityManager entityManager;

    private final UserContextUtil userContextUtil;
    private final EmployeeService employeeService;

    public EmployeeReviewService(UserContextUtil userContextUtil, EmployeeService employeeService) {
        this.userContextUtil = userContextUtil;
        this.employeeService = employeeService;
    }

    /**
     * 获取员工审核统计
     */
    public EmployeeReviewStatsResponse getReviewStats(String keyword) {
        ensureEmployeeIdentity();

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COALESCE(ap.state, 0) AS state, COUNT(*) AS total ");
        sql.append("FROM t_activity_player ap ");
        sql.append("JOIN t_player p ON p.id = ap.player_id ");
        sql.append("JOIN t_activity stage ON stage.id = ap.stage_id ");
        sql.append("JOIN t_activity parent ON parent.id = stage.pid ");
        sql.append("WHERE stage.sort_order = 1 ");

        if (StringUtils.hasText(keyword)) {
            sql.append("AND (p.name LIKE :keyword OR parent.name LIKE :keyword OR ap.project_name LIKE :keyword) ");
        }

        sql.append("GROUP BY ap.state");

        var query = entityManager.createNativeQuery(sql.toString());
        if (StringUtils.hasText(keyword)) {
            query.setParameter("keyword", wrapKeyword(keyword));
        }

        @SuppressWarnings("unchecked")
        List<Object[]> rows = query.getResultList();

        int pending = 0;
        int approved = 0;
        int rejected = 0;

        for (Object[] row : rows) {
            int state = 0;
            int total = 0;
            if (row[0] instanceof Number) {
                state = ((Number) row[0]).intValue();
            } else if (row[0] != null) {
                state = Integer.parseInt(row[0].toString());
            }
            if (row[1] instanceof Number) {
                total = ((Number) row[1]).intValue();
            } else if (row[1] != null) {
                total = Integer.parseInt(row[1].toString());
            }

            switch (state) {
                case 0 -> pending = total;
                case 1 -> approved = total;
                case 2 -> rejected = total;
                default -> log.debug("忽略状态{}的统计", state);
            }
        }

        return new EmployeeReviewStatsResponse(pending, approved, rejected);
    }

    /**
     * 分页查询员工审核列表
     */
    public EmployeeReviewPageResponse listReviewApplications(String keyword, Integer state, Integer page, Integer size) {
        ensureEmployeeIdentity();

        int pageNumber = (page != null && page > 0) ? page : 1;
        int pageSize = (size != null && size > 0) ? size : 10;
        int offset = (pageNumber - 1) * pageSize;

        String baseSql = "SELECT ap.id, p.name AS player_name, ap.project_name, parent.name AS activity_name, ap.state, ap.create_time " +
                "FROM t_activity_player ap " +
                "JOIN t_player p ON p.id = ap.player_id " +
                "JOIN t_activity stage ON stage.id = ap.stage_id " +
                "JOIN t_activity parent ON parent.id = stage.pid ";

        StringBuilder whereClause = new StringBuilder();
        whereClause.append("stage.sort_order = 1");

        if (StringUtils.hasText(keyword)) {
            whereClause.append(" AND (p.name LIKE :keyword OR parent.name LIKE :keyword OR ap.project_name LIKE :keyword)");
        }

        if (state != null) {
            whereClause.append(" AND ap.state = :state");
        }

        String where = whereClause.length() > 0 ? " WHERE " + whereClause + " " : "";
        String order = "ORDER BY ap.create_time DESC ";
        String limit = "LIMIT " + pageSize + " OFFSET " + offset + " ";

        var query = entityManager.createNativeQuery(baseSql + where + order + limit);
        if (StringUtils.hasText(keyword)) {
            query.setParameter("keyword", wrapKeyword(keyword));
        }
        if (state != null) {
            query.setParameter("state", state);
        }

        @SuppressWarnings("unchecked")
        List<Object[]> rows = query.getResultList();
        List<EmployeeReviewApplicationResponse> content = new ArrayList<>();

        for (Object[] row : rows) {
            EmployeeReviewApplicationResponse dto = new EmployeeReviewApplicationResponse();
            dto.setId(row[0] instanceof Number ? ((Number) row[0]).longValue() : parseLong(row[0]));
            dto.setPlayerName(row[1] != null ? row[1].toString() : "");
            dto.setProjectName(row[2] != null ? row[2].toString() : "");
            dto.setActivityName(row[3] != null ? row[3].toString() : "");
            Integer reviewState = row[4] instanceof Number ? ((Number) row[4]).intValue() : parseInt(row[4]);
            dto.setState(reviewState);
            dto.setStateText(resolveStateText(reviewState));
            dto.setStateType(resolveStateType(reviewState));
            dto.setApplyTime(formatDateTime(row[5]));
            content.add(dto);
        }

        String countSql = "SELECT COUNT(*) FROM t_activity_player ap " +
                "JOIN t_player p ON p.id = ap.player_id " +
                "JOIN t_activity stage ON stage.id = ap.stage_id " +
                "JOIN t_activity parent ON parent.id = stage.pid " +
                where;

        var countQuery = entityManager.createNativeQuery(countSql);
        if (StringUtils.hasText(keyword)) {
            countQuery.setParameter("keyword", wrapKeyword(keyword));
        }
        if (state != null) {
            countQuery.setParameter("state", state);
        }

        Number totalNumber = (Number) countQuery.getSingleResult();
        int total = totalNumber != null ? totalNumber.intValue() : 0;

        return new EmployeeReviewPageResponse(content, total, pageNumber, pageSize);
    }

    private void ensureEmployeeIdentity() {
        Long userId = userContextUtil.getCurrentUserId();
        if (userId == null) {
            throw new BusinessException("UNAUTHORIZED", "请先登录");
        }
        if (employeeService.findByUserId(userId) == null) {
            throw new BusinessException("EMPLOYEE_REQUIRED", "当前用户没有审核权限");
        }
    }

    private String wrapKeyword(String keyword) {
        return "%" + keyword.trim() + "%";
    }

    private Integer parseInt(Object value) {
        if (value == null) {
            return null;
        }
        try {
            return Integer.parseInt(value.toString());
        } catch (NumberFormatException ex) {
            log.warn("无法解析整型值: {}", value, ex);
            return null;
        }
    }

    private Long parseLong(Object value) {
        if (value == null) {
            return null;
        }
        try {
            return Long.parseLong(value.toString());
        } catch (NumberFormatException ex) {
            log.warn("无法解析长整型值: {}", value, ex);
            return null;
        }
    }

    private String formatDateTime(Object value) {
        if (value instanceof Timestamp timestamp) {
            LocalDateTime dateTime = timestamp.toLocalDateTime();
            return DATE_TIME_FORMATTER.format(dateTime);
        }
        if (value instanceof LocalDateTime localDateTime) {
            return DATE_TIME_FORMATTER.format(localDateTime);
        }
        return value != null ? value.toString() : "";
    }

    private String resolveStateText(Integer state) {
        if (state == null) {
            return "未知";
        }
        return switch (state) {
            case 0 -> "未审核";
            case 1 -> "审核通过";
            case 2 -> "审核驳回";
            default -> "未知";
        };
    }

    private String resolveStateType(Integer state) {
        if (state == null) {
            return "info";
        }
        return switch (state) {
            case 0 -> "warning";
            case 1 -> "success";
            case 2 -> "danger";
            default -> "info";
        };
    }
}