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

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void addRegionFieldToActivityPlayer() {
        System.out.println("=== 为 t_activity_player 表添加 region_id 字段 ===");
        
        try {
            // 1. 检查字段是否已存在
            String checkColumnSql = """
                SELECT COLUMN_NAME 
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 't_activity_player' 
                AND COLUMN_NAME = 'region_id'
                """;
            
            List<Map<String, Object>> existingColumns = jdbcTemplate.queryForList(checkColumnSql);
            
            if (!existingColumns.isEmpty()) {
                System.out.println("✅ region_id 字段已存在，无需添加");
                return;
            }
            
            // 2. 添加 region_id 字段
            String addColumnSql = """
                ALTER TABLE t_activity_player 
                ADD COLUMN region_id bigint DEFAULT NULL COMMENT '区域ID，关联t_region表' 
                AFTER player_id
                """;
            
            jdbcTemplate.execute(addColumnSql);
            System.out.println("✅ 成功添加 region_id 字段");
            
            // 3. 添加外键约束
            try {
                String addForeignKeySql = """
                    ALTER TABLE t_activity_player 
                    ADD CONSTRAINT fk_activity_player_region 
                    FOREIGN KEY (region_id) REFERENCES t_region(id) 
                    ON DELETE SET NULL ON UPDATE CASCADE
                    """;
                
                jdbcTemplate.execute(addForeignKeySql);
                System.out.println("✅ 成功添加外键约束");
            } catch (Exception e) {
                System.out.println("⚠️ 外键约束添加失败（可能已存在）: " + e.getMessage());
            }
            
            // 4. 验证表结构
            String describeTableSql = """
                SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = DATABASE() 
                AND TABLE_NAME = 't_activity_player' 
                ORDER BY ORDINAL_POSITION
                """;
            
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(describeTableSql);
            System.out.println("\n=== t_activity_player 表结构 ===");
            for (Map<String, Object> column : columns) {
                System.out.println(String.format("%-20s %-15s %-10s %-15s %s", 
                    column.get("COLUMN_NAME"),
                    column.get("DATA_TYPE"),
                    column.get("IS_NULLABLE"),
                    column.get("COLUMN_DEFAULT"),
                    column.get("COLUMN_COMMENT")
                ));
            }
            
        } catch (Exception e) {
            System.err.println("❌ 操作失败: " + e.getMessage());
            e.printStackTrace();
        }
        
        System.out.println("\n✅ region_id 字段添加完成！");
    }
}