package com.rongyichuang.tools;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class StageIdFixTest {

    // 与 application.yml 保持一致，并加长超时
    private static final String URL = "jdbc:mysql://139.155.104.10:3306/ryc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&connectTimeout=60000&socketTimeout=60000";
    private static final String USER = "ryc";
    private static final String PASS = "KiYap3E8X8RLcM6T";

    @Test
    public void fixStageId() throws Exception {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false);

            // 规则：按每个比赛选择其第一个阶段（最小ID）；若无阶段，则回填为比赛自身 activity_id
            String sql = """
UPDATE t_activity_player ap
LEFT JOIN (
  SELECT child.pid AS activity_id, MIN(child.id) AS stage_id
  FROM t_activity child
  WHERE child.pid IS NOT NULL AND child.pid <> 0
  GROUP BY child.pid
) s ON s.activity_id = ap.activity_id
SET ap.stage_id = COALESCE(s.stage_id, ap.activity_id)
""";

            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                int affected = ps.executeUpdate();
                conn.commit();
                System.out.println("[StageIdFixTest] 更新完成，受影响行数=" + affected);
            } catch (Exception ex) {
                conn.rollback();
                throw ex;
            }
        }
    }
}