package com.rongyichuang.tools;

import org.junit.jupiter.api.Test;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class DataInsertTest {

    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 insertMockData() throws Exception {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false);
            try {
                // 1) 获取两个活动ID，不足则最小化补充
                List<Long> activityIds = fetchActivityIds(conn);
                if (activityIds.size() < 2) {
                    long a1 = insertActivity(conn, "测试比赛A", "testA", 7);
                    long a2 = insertActivity(conn, "测试比赛B", "testB", 10);
                    activityIds.add(a1);
                    activityIds.add(a2);
                }

                // 2) 插入 20 个用户，并收集生成的 user_id
                List<Long> userIds = new ArrayList<>();
                for (int i = 1; i <= 20; i++) {
                    long uid = insertUser(conn, "批量用户" + i, "wx_openid_batch_" + i, "wx_unionid_batch_" + i, 1, "1380001" + String.format("%04d", i));
                    userIds.add(uid);
                }

                // 3) 插入 20 个学员（未审核 audit_state=0），每个学员关联对应 user_id
                List<Long> playerIds = new ArrayList<>();
                for (int i = 1; i <= 20; i++) {
                    long pid = insertPlayer(conn,
                            "批量学员" + i,
                            "1380014" + String.format("%04d", i),
                            1, // role_id
                            0, // audit_state 未审核
                            1, // state=1
                            userIds.get(i - 1));
                    playerIds.add(pid);
                }

                // 4) 报名关系：全部报名 activityIds[0]，前3名再报名 activityIds[1]
                for (int i = 0; i < playerIds.size(); i++) {
                    insertActivityPlayer(conn, activityIds.get(0), playerIds.get(i));
                    if (i < 3) {
                        insertActivityPlayer(conn, activityIds.get(1), playerIds.get(i));
                    }
                }

                conn.commit();
                System.out.println("[DataInsertTest] 插入完成：用户20、学员20、报名关系" + (20 + 3));
            } catch (Exception ex) {
                conn.rollback();
                throw ex;
            }
        }
    }

    private List<Long> fetchActivityIds(Connection conn) throws SQLException {
        List<Long> ids = new ArrayList<>();
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id FROM t_activity ORDER BY id LIMIT 2")) {
            while (rs.next()) {
                ids.add(rs.getLong(1));
            }
        }
        return ids;
    }

    private long insertActivity(Connection conn, String name, String path, int deadlineDays) throws SQLException {
        String sql = "INSERT INTO t_activity (pid, path, name, signup_deadline, rating_scheme_id, state) VALUES (0, ?, ?, ?, 1, 1)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, path);
            ps.setString(2, name);
            ps.setTimestamp(3, Timestamp.valueOf(LocalDateTime.now().plusDays(deadlineDays)));
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) return keys.getLong(1);
            }
        }
        throw new SQLException("插入活动失败");
    }

    private long insertUser(Connection conn, String name, String wxOpenId, String wxUnionId, int state, String mobile) throws SQLException {
        String sql = "INSERT INTO t_user (name, wx_openid, wx_unionid, state, mobile) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setString(2, wxOpenId);
            ps.setString(3, wxUnionId);
            ps.setInt(4, state);
            ps.setString(5, mobile);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) return keys.getLong(1);
            }
        }
        throw new SQLException("插入用户失败");
    }

    private long insertPlayer(Connection conn, String name, String phone, long roleId, int auditState, int state, long userId) throws SQLException {
        String sql = "INSERT INTO t_player (name, phone, role_id, audit_state, state, user_id) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, name);
            ps.setString(2, phone);
            ps.setLong(3, roleId);
            ps.setInt(4, auditState);
            ps.setInt(5, state);
            ps.setLong(6, userId);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) return keys.getLong(1);
            }
        }
        throw new SQLException("插入学员失败");
    }

    // 注意：按 db.sql 拼写为 t_avtivity_player
    private long insertActivityPlayer(Connection conn, long activityId, long playerId) throws SQLException {
        String sql = "INSERT INTO t_avtivity_player (activity_id, player_id) VALUES (?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setLong(1, activityId);
            ps.setLong(2, playerId);
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) return keys.getLong(1);
            }
        }
        throw new SQLException("插入报名关系失败");
    }
}