package com.rongyichuang;

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 TableStructureTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testActivityPlayerRatingItemTableStructure() {
        try {
            String sql = "DESCRIBE t_activity_player_rating_item";
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            System.out.println("=== t_activity_player_rating_item 表结构 ===");
            for (Map<String, Object> row : result) {
                System.out.println(row);
            }
        } catch (Exception e) {
            System.out.println("查询表结构失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void testActivityPlayerTableStructure() {
        try {
            String sql = "DESCRIBE t_activity_player";
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            System.out.println("=== t_activity_player 表结构 ===");
            for (Map<String, Object> row : result) {
                System.out.println(row);
            }
        } catch (Exception e) {
            System.out.println("查询表结构失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void testVerifyRatingData() {
        // 查询刚才插入的评分数据
        String sql = "SELECT * FROM t_activity_player_rating_item WHERE player_id = 12 ORDER BY create_time DESC LIMIT 5";
        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql);
        
        System.out.println("最新的评分数据:");
        for (Map<String, Object> row : results) {
            System.out.println(row);
        }
        
        // 查询总分更新情况
        String totalScoreSql = "SELECT id, total_score, update_time FROM t_activity_player WHERE id = 12";
        List<Map<String, Object>> totalResults = jdbcTemplate.queryForList(totalScoreSql);
        
        System.out.println("总分更新情况:");
        for (Map<String, Object> row : totalResults) {
            System.out.println(row);
        }
    }

    @Test
    public void testInsertSample() {
        try {
            String sql = "INSERT INTO t_activity_player_rating_item " +
                    "(activity_id, player_id, rating_scheme_id, rating_item_id, score, feedback, create_time, update_time, version) " +
                    "VALUES (1, 1, 1, 1, 85.5, 'test', NOW(), NOW(), 0)";
            int result = jdbcTemplate.update(sql);
            System.out.println("插入测试记录成功，影响行数: " + result);
        } catch (Exception e) {
            System.out.println("插入测试记录失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void testJudgeTableStructure() {
        try {
            String sql = "DESCRIBE t_judge";
            List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
            System.out.println("=== t_judge 表结构 ===");
            for (Map<String, Object> row : result) {
                System.out.println(row);
            }
        } catch (Exception e) {
            System.out.println("查询表结构失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void testExecuteMigration() {
        try {
            // 为t_judge表添加缺失的字段
            String[] sqls = {
                "ALTER TABLE t_judge ADD COLUMN title VARCHAR(128) COMMENT '职位/头衔'",
                "ALTER TABLE t_judge ADD COLUMN company VARCHAR(128) COMMENT '公司/机构'", 
                "ALTER TABLE t_judge ADD COLUMN introduction TEXT COMMENT '个人介绍'"
            };
            
            for (String sql : sqls) {
                try {
                    jdbcTemplate.execute(sql);
                    System.out.println("执行成功: " + sql);
                } catch (Exception e) {
                    if (e.getMessage().contains("Duplicate column name")) {
                        System.out.println("字段已存在，跳过: " + sql);
                    } else {
                        System.out.println("执行失败: " + sql + " - " + e.getMessage());
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("执行迁移失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    @Test
    public void testRollbackAvatarFields() {
        try {
            // 移除不必要的avatar相关字段
            String[] sqls = {
                "ALTER TABLE t_judge DROP COLUMN avatar_url",
                "ALTER TABLE t_judge DROP COLUMN avatar_media_id"
            };
            
            for (String sql : sqls) {
                try {
                    jdbcTemplate.execute(sql);
                    System.out.println("回滚成功: " + sql);
                } catch (Exception e) {
                    if (e.getMessage().contains("check that column/key exists")) {
                        System.out.println("字段不存在，跳过: " + sql);
                    } else {
                        System.out.println("回滚失败: " + sql + " - " + e.getMessage());
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("执行回滚失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
}