package com.rongyichuang.review.service;

import com.rongyichuang.review.dto.response.ReviewProjectPageResponse;
import com.rongyichuang.review.dto.response.ReviewProjectResponse;
import com.rongyichuang.review.dto.response.ReviewStatisticsResponse;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 评审管理服务类
 */
@Service
public class ReviewService {

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

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询我未评审的项目列表
     * 逻辑：存在评委关联但当前评委未评分的项目
     */
    public ReviewProjectPageResponse getUnReviewedProjects(Long judgeId, String searchKeyword, int page, int pageSize) {
        log.info("查询我未评审的项目列表，judgeId: {}, searchKeyword: {}, page: {}, pageSize: {}", judgeId, searchKeyword, page, pageSize);
        
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ap.id as activity_player_id, ap.activity_id, ap.stage_id, ap.project_name, ")
           .append("a.name as activity_name, stage.name as stage_name, ")
           .append("p.name as student_name, ap.create_time as submit_time ")
           .append("FROM t_activity_player ap ")
           .append("JOIN t_player p ON ap.player_id = p.id ")
           .append("JOIN t_activity stage ON ap.stage_id = stage.id ")
           .append("JOIN t_activity a ON stage.pid = a.id ")
           .append("WHERE EXISTS (")
           .append("  SELECT 1 FROM t_activity_judge aj ")
           .append("  WHERE ap.activity_id = aj.activity_id ")
           .append("  AND ap.stage_id = aj.stage_id ")
           .append("  AND aj.judge_id = ? ")
           .append(") ")
           .append("AND NOT EXISTS (")
           .append("  SELECT 1 FROM t_activity_player_rating apr ")
           .append("  WHERE ap.id = apr.activity_player_id ")
           .append("  AND apr.judge_id = ? ")
           .append("  AND apr.state = 1 ")
           .append(") ")
           .append("AND ap.state = 1 "); // 只查询审核通过的项目

        List<Object> params = new ArrayList<>();
        params.add(judgeId);
        params.add(judgeId);

        // 添加关键词搜索条件
        if (StringUtils.hasText(searchKeyword)) {
            sql.append("AND (ap.project_name LIKE ? OR p.name LIKE ?) ");
            String searchPattern = "%" + searchKeyword + "%";
            params.add(searchPattern);
            params.add(searchPattern);
        }

        // 添加排序
        sql.append("ORDER BY ap.create_time DESC ");

        // 查询总数
        String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"));
        Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());

        // 添加分页
        sql.append("LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add((page - 1) * pageSize);

        // 查询数据
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), params.toArray());
        List<ReviewProjectResponse> projects = convertToProjectList(rows);

        return new ReviewProjectPageResponse(projects, total != null ? total : 0, page, pageSize);
    }

    /**
     * 查询我已评审的项目列表
     * 逻辑：将"我未评审"的NOT EXISTS改为EXISTS
     */
    public ReviewProjectPageResponse getReviewedProjects(Long judgeId, String searchKeyword, int page, int pageSize) {
        log.info("查询我已评审的项目列表，judgeId: {}, searchKeyword: {}, page: {}, pageSize: {}", judgeId, searchKeyword, page, pageSize);
        
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ap.id as activity_player_id, ap.activity_id, ap.stage_id, ap.project_name, ")
           .append("a.name as activity_name, stage.name as stage_name, ")
           .append("p.name as student_name, ap.create_time as submit_time ")
           .append("FROM t_activity_player ap ")
           .append("JOIN t_player p ON ap.player_id = p.id ")
           .append("JOIN t_activity stage ON ap.stage_id = stage.id ")
           .append("JOIN t_activity a ON stage.pid = a.id ")
           .append("WHERE EXISTS (")
           .append("  SELECT 1 FROM t_activity_judge aj ")
           .append("  WHERE ap.activity_id = aj.activity_id ")
           .append("  AND ap.stage_id = aj.stage_id ")
           .append("  AND aj.judge_id = ? ")
           .append(") ")
           .append("AND EXISTS (")  // 改为EXISTS
           .append("  SELECT 1 FROM t_activity_player_rating apr ")
           .append("  WHERE ap.id = apr.activity_player_id ")
           .append("  AND apr.judge_id = ? ")
           .append("  AND apr.state = 1 ")
           .append(") ")
           .append("AND ap.state = 1 "); // 只查询审核通过的项目

        List<Object> params = new ArrayList<>();
        params.add(judgeId);
        params.add(judgeId);

        // 添加关键词搜索条件
        if (StringUtils.hasText(searchKeyword)) {
            sql.append("AND (ap.project_name LIKE ? OR p.name LIKE ?) ");
            String searchPattern = "%" + searchKeyword + "%";
            params.add(searchPattern);
            params.add(searchPattern);
        }

        // 添加排序
        sql.append("ORDER BY ap.create_time DESC ");

        // 查询总数
        String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"));
        Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());

        // 添加分页
        sql.append("LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add((page - 1) * pageSize);

        // 查询数据
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), params.toArray());
        List<ReviewProjectResponse> projects = convertToProjectList(rows);

        return new ReviewProjectPageResponse(projects, total != null ? total : 0, page, pageSize);
    }

    /**
     * 查询学员未评审的项目列表
     * 逻辑：没有任何评分记录的项目
     */
    public ReviewProjectPageResponse getStudentUnReviewedProjects(Long judgeId, String searchKeyword, int page, int pageSize) {
        log.info("查询学员未评审的项目列表，judgeId: {}, searchKeyword: {}, page: {}, pageSize: {}", judgeId, searchKeyword, page, pageSize);
        
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ap.id as activity_player_id, ap.activity_id, ap.stage_id, ap.project_name, ")
           .append("a.name as activity_name, stage.name as stage_name, ")
           .append("p.name as student_name, ap.create_time as submit_time ")
           .append("FROM t_activity_player ap ")
           .append("JOIN t_player p ON ap.player_id = p.id ")
           .append("JOIN t_activity stage ON ap.stage_id = stage.id ")
           .append("JOIN t_activity a ON stage.pid = a.id ")
           .append("WHERE EXISTS (")
           .append("  SELECT 1 FROM t_activity_judge aj ")
           .append("  WHERE ap.activity_id = aj.activity_id ")
           .append("  AND ap.stage_id = aj.stage_id ")
           .append("  AND aj.judge_id = ? ")
           .append(") ")
           .append("AND NOT EXISTS (")
           .append("  SELECT 1 FROM t_activity_player_rating apr ")
           .append("  WHERE ap.id = apr.activity_player_id ")
           .append("  AND apr.state = 1 ")
           .append(") ")
           .append("AND ap.state = 1 "); // 只查询审核通过的项目

        List<Object> params = new ArrayList<>();
        params.add(judgeId);

        // 添加关键词搜索条件
        if (StringUtils.hasText(searchKeyword)) {
            sql.append("AND (ap.project_name LIKE ? OR p.name LIKE ?) ");
            String searchPattern = "%" + searchKeyword + "%";
            params.add(searchPattern);
            params.add(searchPattern);
        }

        // 添加排序
        sql.append("ORDER BY ap.create_time DESC ");

        // 查询总数
        String countSql = "SELECT COUNT(*) " + sql.substring(sql.indexOf("FROM"));
        Integer total = jdbcTemplate.queryForObject(countSql, Integer.class, params.toArray());

        // 添加分页
        sql.append("LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add((page - 1) * pageSize);

        // 查询数据
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql.toString(), params.toArray());
        List<ReviewProjectResponse> projects = convertToProjectList(rows);

        return new ReviewProjectPageResponse(projects, total != null ? total : 0, page, pageSize);
    }

    /**
     * 查询评审统计信息
     */
    public ReviewStatisticsResponse getReviewStatistics(Long judgeId) {
        log.info("查询评审统计信息，judgeId: {}", judgeId);

        // 查询我未评审的数量
        String unReviewedSql = "SELECT COUNT(*) FROM t_activity_player ap " +
                              "WHERE EXISTS (" +
                              "  SELECT 1 FROM t_activity_judge aj " +
                              "  WHERE ap.activity_id = aj.activity_id " +
                              "  AND ap.stage_id = aj.stage_id " +
                              "  AND aj.judge_id = ? " +
                              ") " +
                              "AND NOT EXISTS (" +
                              "  SELECT 1 FROM t_activity_player_rating apr " +
                              "  WHERE ap.id = apr.activity_player_id " +
                              "  AND apr.judge_id = ? " +
                              "  AND apr.state = 1 " +
                              ") " +
                              "AND ap.state = 1";
        Integer unReviewedCount = jdbcTemplate.queryForObject(unReviewedSql, Integer.class, judgeId, judgeId);

        // 查询我已评审的数量
        String reviewedSql = "SELECT COUNT(*) FROM t_activity_player ap " +
                            "WHERE EXISTS (" +
                            "  SELECT 1 FROM t_activity_judge aj " +
                            "  WHERE ap.activity_id = aj.activity_id " +
                            "  AND ap.stage_id = aj.stage_id " +
                            "  AND aj.judge_id = ? " +
                            ") " +
                            "AND EXISTS (" +
                            "  SELECT 1 FROM t_activity_player_rating apr " +
                            "  WHERE ap.id = apr.activity_player_id " +
                            "  AND apr.judge_id = ? " +
                            "  AND apr.state = 1 " +
                            ") " +
                            "AND ap.state = 1";
        Integer reviewedCount = jdbcTemplate.queryForObject(reviewedSql, Integer.class, judgeId, judgeId);

        // 查询学员未评审的数量
        String studentUnReviewedSql = "SELECT COUNT(*) FROM t_activity_player ap " +
                                     "WHERE EXISTS (" +
                                     "  SELECT 1 FROM t_activity_judge aj " +
                                     "  WHERE ap.activity_id = aj.activity_id " +
                                     "  AND ap.stage_id = aj.stage_id " +
                                     "  AND aj.judge_id = ? " +
                                     ") " +
                                     "AND NOT EXISTS (" +
                                     "  SELECT 1 FROM t_activity_player_rating apr " +
                                     "  WHERE ap.id = apr.activity_player_id " +
                                     "  AND apr.state = 1 " +
                                     ") " +
                                     "AND ap.state = 1";
        Integer studentUnReviewedCount = jdbcTemplate.queryForObject(studentUnReviewedSql, Integer.class, judgeId);

        return new ReviewStatisticsResponse(
            unReviewedCount != null ? unReviewedCount : 0,
            reviewedCount != null ? reviewedCount : 0,
            studentUnReviewedCount != null ? studentUnReviewedCount : 0
        );
    }

    /**
     * 转换查询结果为项目列表
     */
    private List<ReviewProjectResponse> convertToProjectList(List<Map<String, Object>> rows) {
        List<ReviewProjectResponse> projects = new ArrayList<>();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

        for (Map<String, Object> row : rows) {
            ReviewProjectResponse project = new ReviewProjectResponse();
            project.setActivityPlayerId(((Number) row.get("activity_player_id")).longValue());
            project.setActivityId(((Number) row.get("activity_id")).longValue());
            project.setStageId(((Number) row.get("stage_id")).longValue());
            project.setProjectName((String) row.get("project_name"));
            project.setActivityName((String) row.get("activity_name"));
            project.setStageName((String) row.get("stage_name"));
            project.setStudentName((String) row.get("student_name"));
            
            // 处理提交时间
            Object submitTimeObj = row.get("submit_time");
            if (submitTimeObj instanceof LocalDateTime) {
                project.setSubmitTime(((LocalDateTime) submitTimeObj).format(formatter));
            } else if (submitTimeObj != null) {
                project.setSubmitTime(submitTimeObj.toString());
            }
            
            // 设置默认状态
            project.setStatus("PENDING");
            
            projects.add(project);
        }

        return projects;
    }
}