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 org.springframework.test.context.ActiveProfiles;

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

@SpringBootTest
@ActiveProfiles("test")
public class RatingTemplateInserter {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void insertRatingTemplate() {
        System.out.println("开始插入评分模板数据...");

        try {
            // 1. 插入评分模板
            String insertSchemeSql = """
                INSERT INTO t_rating_scheme (id, name, description, state, create_time, create_user_id, update_time, update_user_id, version) 
                VALUES (1, '编程大赛评分标准', '用于编程比赛的综合评分标准，包含代码质量、功能完整性、用户体验、创新性和项目展示等维度', 1, NOW(), 1, NOW(), 1, 0)
                ON DUPLICATE KEY UPDATE 
                name = VALUES(name), 
                description = VALUES(description), 
                update_time = NOW()
                """;
            
            int schemeResult = jdbcTemplate.update(insertSchemeSql);
            System.out.println("插入评分模板结果: " + schemeResult);

            // 2. 插入评分项目
            String[] insertItemSqls = {
                "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (1, 1, '代码质量', 30, 1, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
                "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (2, 1, '功能完整性', 25, 2, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
                "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (3, 1, '用户体验', 20, 3, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
                "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (4, 1, '创新性', 15, 4, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()",
                "INSERT INTO t_rating_item (id, scheme_id, name, max_score, order_no, state, create_time, create_user_id, update_time, update_user_id, version) VALUES (5, 1, '项目展示', 10, 5, 1, NOW(), 1, NOW(), 1, 0) ON DUPLICATE KEY UPDATE name = VALUES(name), max_score = VALUES(max_score), order_no = VALUES(order_no), state = VALUES(state), update_time = NOW()"
            };

            for (int i = 0; i < insertItemSqls.length; i++) {
                try {
                    System.out.println("执行SQL: " + insertItemSqls[i]);
                    int itemResult = jdbcTemplate.update(insertItemSqls[i]);
                    System.out.println("插入评分项目 " + (i + 1) + " 结果: " + itemResult);
                } catch (Exception e) {
                    System.err.println("插入评分项目 " + (i + 1) + " 失败: " + e.getMessage());
                    e.printStackTrace();
                }
            }

            // 3. 验证插入结果
            System.out.println("\n=== 验证插入结果 ===");
            
            // 查询评分模板
            String querySchemeSql = "SELECT * FROM t_rating_scheme WHERE id = 1";
            List<Map<String, Object>> schemeResults = jdbcTemplate.queryForList(querySchemeSql);
            System.out.println("评分模板:");
            for (Map<String, Object> row : schemeResults) {
                System.out.println(row);
            }

            // 查询评分项目
            String queryItemSql = "SELECT * FROM t_rating_item WHERE scheme_id = 1 ORDER BY order_no";
            List<Map<String, Object>> itemResults = jdbcTemplate.queryForList(queryItemSql);
            System.out.println("\n评分项目:");
            for (Map<String, Object> row : itemResults) {
                System.out.println(row);
            }

            // 计算总分
            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✅ 评分模板数据插入完成！");

        } catch (Exception e) {
            System.err.println("❌ 插入评分模板数据失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}