package com.rongyichuang.player.service;

import com.rongyichuang.common.dto.PageResponse;
import com.rongyichuang.player.dto.response.ActivityPlayerApplicationResponse;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

@Service
public class PlayerApplicationService {

    @PersistenceContext
    private EntityManager em;

    /**
     * 查询活动报名信息
     * 报名审核页面只显示海选阶段的数据，不包含复赛等后续阶段
     * 当传入activityId时，查询该比赛下第一个阶段（sort_order=1）的报名项目
     */
    @SuppressWarnings("unchecked")
    public PageResponse<ActivityPlayerApplicationResponse> listApplications(String name, Long activityId, Integer state, Integer page, Integer size) {
        String baseSql =
            "SELECT ap.id, p.name AS player_name, parent.name AS activity_name, ap.project_name AS project_name, p.phone AS phone, ap.create_time AS apply_time, ap.state AS state, " +
            "COALESCE(rating_stats.rating_count, 0) AS rating_count, rating_stats.average_score " +
            "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 " +
            "LEFT JOIN (" +
            "  SELECT activity_player_id, COUNT(*) AS rating_count, AVG(total_score) AS average_score " +
            "  FROM t_activity_player_rating " +
            "  WHERE state = 1 " +
            "  GROUP BY activity_player_id" +
            ") rating_stats ON rating_stats.activity_player_id = ap.id ";
        
        StringBuilder whereClause = new StringBuilder();
        boolean hasCondition = false;
        
        // 默认只显示第一阶段的数据（基于sort_order=1），避免硬编码阶段名称
        whereClause.append("stage.sort_order = 1");
        hasCondition = true;
        
        if (name != null && !name.isEmpty()) {
            if (hasCondition) {
                whereClause.append(" AND ");
            }
            whereClause.append("p.name LIKE CONCAT('%', :name, '%')");
            hasCondition = true;
        }
        
        if (activityId != null) {
            if (hasCondition) {
                whereClause.append(" AND ");
            }
            // 查询指定主比赛的第一阶段报名项目：activity_id=主比赛ID, stage_id=第一阶段ID
            whereClause.append("ap.activity_id = :activityId AND ap.stage_id = stage.id AND stage.pid = :activityId AND stage.sort_order = 1");
            hasCondition = true;
        }
        
        if (state != null) {
            if (hasCondition) {
                whereClause.append(" AND ");
            }
            whereClause.append("ap.state = :state");
            hasCondition = true;
        }
        
        String where = hasCondition ? "WHERE " + whereClause.toString() + " " : "";
        String order = "ORDER BY ap.create_time DESC ";
        String limit = "";
        if (page != null && size != null && page > 0 && size > 0) {
            int offset = (page - 1) * size;
            limit = "LIMIT " + size + " OFFSET " + offset + " ";
        }

        var q = em.createNativeQuery(baseSql + where + order + limit);
        if (name != null && !name.isEmpty()) {
            q.setParameter("name", name);
        }
        if (activityId != null) {
            q.setParameter("activityId", activityId);
        }
        if (state != null) {
            q.setParameter("state", state);
        }
        List<Object[]> rows = q.getResultList();
        List<ActivityPlayerApplicationResponse> list = new ArrayList<>();
        for (Object[] r : rows) {
            ActivityPlayerApplicationResponse dto = new ActivityPlayerApplicationResponse();
            dto.setId(r[0] != null ? Long.valueOf(r[0].toString()) : null); // activity_player_id
            dto.setPlayerName(r[1] != null ? r[1].toString() : "");
            dto.setActivityName(r[2] != null ? r[2].toString() : "");
            dto.setProjectName(r[3] != null ? r[3].toString() : ""); // project_name
            dto.setPhone(r[4] != null ? r[4].toString() : "");
            dto.setApplyTime(r[5] != null ? r[5].toString() : "");
            // 映射状态：使用 t_activity_player.state（0=未审核，1=审核通过，2=审核驳回）
            dto.setState(r[6] != null ? Integer.valueOf(r[6].toString()) : 0);
            // 映射评审统计数据
            dto.setRatingCount(r[7] != null ? Integer.valueOf(r[7].toString()) : 0);
            dto.setAverageScore(r[8] != null ? Double.valueOf(r[8].toString()) : null);
            list.add(dto);
        }
        
        // 获取总数
        String countSql = "SELECT COUNT(*) " + baseSql.substring(baseSql.indexOf("FROM")) + where;
        var countQuery = em.createNativeQuery(countSql);
        if (name != null && !name.isEmpty()) {
            countQuery.setParameter("name", name);
        }
        if (activityId != null) {
            countQuery.setParameter("activityId", activityId);
        }
        if (state != null) {
            countQuery.setParameter("state", state);
        }
        long total = ((Number) countQuery.getSingleResult()).longValue();
        
        return new PageResponse<>(list, total, page != null ? page : 1, size != null ? size : 10);
    }

    /**
     * 项目评审专用查询，包含所有阶段数据（包括复赛、决赛）
     * 与listApplications的区别：不过滤复赛和决赛阶段
     */
    @SuppressWarnings("unchecked")
    public PageResponse<ActivityPlayerApplicationResponse> listProjectReviewApplications(String name, Long activityId, Integer state, Integer page, Integer size) {
        String baseSql =
            "SELECT ap.id, CONCAT(p.name, '（', ap.project_name, '）') AS player_name, stage.name AS activity_name, ap.project_name AS project_name, p.phone AS phone, ap.create_time AS apply_time, ap.state AS state, " +
            "COALESCE(rating_stats.rating_count, 0) AS rating_count, rating_stats.average_score " +
            "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 " +
            "LEFT JOIN (" +
            "  SELECT activity_player_id, COUNT(*) AS rating_count, AVG(total_score) AS average_score " +
            "  FROM t_activity_player_rating " +
            "  WHERE state = 1 " +
            "  GROUP BY activity_player_id" +
            ") rating_stats ON rating_stats.activity_player_id = ap.id ";
        
        StringBuilder whereClause = new StringBuilder();
        boolean hasCondition = false;
        
        // 项目评审查询：直接根据阶段ID查询，且默认只查询审核通过的数据
        
        if (name != null && !name.isEmpty()) {
            if (hasCondition) {
                whereClause.append(" AND ");
            }
            whereClause.append("p.name LIKE CONCAT('%', :name, '%')");
            hasCondition = true;
        }
        
        if (activityId != null) {
            if (hasCondition) {
                whereClause.append(" AND ");
            }
            // 直接查询指定阶段ID的报名项目
            whereClause.append("ap.stage_id = :activityId");
            hasCondition = true;
        }
        
        // 默认只查询审核通过的数据 (state = 1)
        if (hasCondition) {
            whereClause.append(" AND ");
        }
        whereClause.append("ap.state = 1");
        hasCondition = true;
        
        // 如果传入了state参数，则覆盖默认的state=1条件
        if (state != null) {
            // 移除最后添加的 "ap.state = 1" 条件
            String whereStr = whereClause.toString();
            whereStr = whereStr.replace(" AND ap.state = 1", "");
            whereClause = new StringBuilder(whereStr);
            
            if (hasCondition && !whereStr.isEmpty()) {
                whereClause.append(" AND ");
            }
            whereClause.append("ap.state = :state");
        }
        
        String where = hasCondition ? "WHERE " + whereClause.toString() + " " : "";
        String order = "ORDER BY ap.create_time DESC ";
        String limit = "";
        if (page != null && size != null && page > 0 && size > 0) {
            int offset = (page - 1) * size;
            limit = "LIMIT " + size + " OFFSET " + offset + " ";
        }

        var q = em.createNativeQuery(baseSql + where + order + limit);
        if (name != null && !name.isEmpty()) {
            q.setParameter("name", name);
        }
        if (activityId != null) {
            q.setParameter("activityId", activityId);
        }
        if (state != null) {
            q.setParameter("state", state);
        }
        List<Object[]> rows = q.getResultList();
        List<ActivityPlayerApplicationResponse> list = new ArrayList<>();
        for (Object[] r : rows) {
            ActivityPlayerApplicationResponse dto = new ActivityPlayerApplicationResponse();
            dto.setId(r[0] != null ? Long.valueOf(r[0].toString()) : null); // activity_player_id
            dto.setPlayerName(r[1] != null ? r[1].toString() : "");
            dto.setActivityName(r[2] != null ? r[2].toString() : "");
            dto.setProjectName(r[3] != null ? r[3].toString() : ""); // project_name
            dto.setPhone(r[4] != null ? r[4].toString() : "");
            dto.setApplyTime(r[5] != null ? r[5].toString() : "");
            // 映射状态：使用 t_activity_player.state（0=未审核，1=审核通过，2=审核驳回）
            dto.setState(r[6] != null ? Integer.valueOf(r[6].toString()) : 0);
            // 映射评审统计数据
            dto.setRatingCount(r[7] != null ? Integer.valueOf(r[7].toString()) : 0);
            dto.setAverageScore(r[8] != null ? Double.valueOf(r[8].toString()) : null);
            list.add(dto);
        }
        
        // 创建分页响应
        long totalElements = list.size();
        return new PageResponse<>(list, totalElements, page, size);
    }
}