package com.rongyichuang.tools;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

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

@SpringBootTest
public class RatingTemplateFixer {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void fixRatingTemplateData() {
        System.out.println("=== 修正评分模板数据 ===");
        
        // 1. 更新活动表的rating_scheme_id为1
        String updateActivitySql = "UPDATE t_activity SET rating_scheme_id = 1 WHERE id = 1";
        int activityResult = jdbcTemplate.update(updateActivitySql);
        System.out.println("更新活动评分模板配置结果: " + activityResult);
        
        // 2. 更新所有评分项目的scheme_id为1
        String[] updateItemSqls = {
            "UPDATE t_rating_item SET scheme_id = 1, name = '代码质量', max_score = 30, order_no = 1 WHERE id = 1",
            "UPDATE t_rating_item SET scheme_id = 1, name = '功能完整性', max_score = 25, order_no = 2 WHERE id = 2", 
            "UPDATE t_rating_item SET scheme_id = 1, name = '用户体验', max_score = 20, order_no = 3 WHERE id = 3",
            "UPDATE t_rating_item SET scheme_id = 1, name = '创新性', max_score = 15, order_no = 4 WHERE id = 4",
            "UPDATE t_rating_item SET scheme_id = 1, name = '项目展示', max_score = 10, order_no = 5 WHERE id = 5"
        };
        
        for (int i = 0; i < updateItemSqls.length; i++) {
            try {
                int itemResult = jdbcTemplate.update(updateItemSqls[i]);
                System.out.println("更新评分项目 " + (i + 1) + " 结果: " + itemResult);
            } catch (Exception e) {
                System.err.println("更新评分项目 " + (i + 1) + " 失败: " + e.getMessage());
            }
        }
        
        // 3. 删除多余的评分项目（ID 6, 7, 8）
        String deleteExtraItemsSql = "DELETE FROM t_rating_item WHERE id IN (6, 7, 8)";
        int deleteResult = jdbcTemplate.update(deleteExtraItemsSql);
        System.out.println("删除多余评分项目结果: " + deleteResult);
        
        // 4. 验证修正结果
        System.out.println("\n=== 验证修正结果 ===");
        
        // 查询评分模板
        String schemeSql = "SELECT * FROM t_rating_scheme WHERE id = 1";
        List<Map<String, Object>> schemes = jdbcTemplate.queryForList(schemeSql);
        System.out.println("评分模板:");
        for (Map<String, Object> scheme : schemes) {
            System.out.println(scheme);
        }
        
        // 查询评分项目
        String itemSql = "SELECT * FROM t_rating_item WHERE scheme_id = 1 ORDER BY order_no";
        List<Map<String, Object>> items = jdbcTemplate.queryForList(itemSql);
        System.out.println("\n评分项目:");
        for (Map<String, Object> item : items) {
            System.out.println(item);
        }
        
        // 查询活动配置
        String activitySql = "SELECT id, name, rating_scheme_id FROM t_activity WHERE id = 1";
        List<Map<String, Object>> activities = jdbcTemplate.queryForList(activitySql);
        System.out.println("\n活动配置:");
        for (Map<String, Object> activity : activities) {
            System.out.println(activity);
        }
        
        // 计算总分
        String totalScoreSql = """
            SELECT 
                rs.name as scheme_name,
                SUM(ri.max_score) as total_max_score
            FROM t_rating_scheme rs
            LEFT JOIN t_rating_item ri ON rs.id = ri.scheme_id
            WHERE rs.id = 1
            GROUP BY rs.id, rs.name
            """;
        List<Map<String, Object>> totalResults = jdbcTemplate.queryForList(totalScoreSql);
        System.out.println("\n总分验证:");
        for (Map<String, Object> row : totalResults) {
            System.out.println(row);
        }
        
        System.out.println("\n✅ 评分模板数据修正完成！");
    }
}