package com.rongyichuang.tools;

import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

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

@Component
public class MockDataInserter implements CommandLineRunner {

    private static final String URL = "jdbc:mysql://140.143.152.226:3308/ryc?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
    private static final String USER = "openai";
    private static final String PASS = "Xml@uk2025";

    @Override
    public void run(String... args) {
        String flag = System.getenv("RUN_MOCK_INSERT");
        if (!"1".equals(flag)) {
            return;
        }
        System.out.println("[MockDataInserter] RUN_MOCK_INSERT=1 检测到，开始插入模拟数据...");

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
            conn.setAutoCommit(false);
            try {
                // 安全检查：若已有足够数据则跳过
                if (existingPlayers(conn) >= 20) {
                    System.out.println("[MockDataInserter] 检测到已有 >=20 条学员数据，跳过插入。");
                    return;
                }

                // 1) 准备 2 个活动（若不足则补充）
                List<Long> activityIds = fetchActivityIds(conn);
                if (activityIds.size() < 2) {
                    long a1 = insertActivity(conn, "模拟比赛A", "mockA", 7);
                    long a2 = insertActivity(conn, "模拟比赛B", "mockB", 10);
                    activityIds.add(a1);
                    activityIds.add(a2);
                }

                // 2) 插入 20 个用户
                List<Long> userIds = new ArrayList<>();
                for (int i = 1; i <= 20; i++) {
                    long uid = insertUser(conn, "模拟用户" + i, "wx_openid_" + i, "wx_unionid_" + i, 1, "1380000" + String.format("%04d", i));
                    userIds.add(uid);
                }

                // 3) 插入 20 个学员（audit_state=0 未审核）
                List<Long> playerIds = new ArrayList<>();
                for (int i = 1; i <= 20; i++) {
                    long pid = insertPlayer(conn,
                            "学员" + i,
                            "1380013" + String.format("%04d", i),
                            1, // role_id
                            0, // audit_state 未审核
                            1, // state=1 正常
                            userIds.get(i - 1));
                    playerIds.add(pid);
                }

                // 4) 报名关系：所有学员报名 activityIds.get(0)，其中前3人再报名 activityIds.get(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("[MockDataInserter] 插入完成：用户20、学员20、报名关系" + (20 + 3));
            } catch (Exception ex) {
                conn.rollback();
                System.err.println("[MockDataInserter] 插入失败，已回滚：" + ex.getMessage());
                ex.printStackTrace();
            }
        } catch (Exception e) {
            System.err.println("[MockDataInserter] 连接数据库失败：" + e.getMessage());
            e.printStackTrace();
        }
    }

    private int existingPlayers(Connection conn) throws SQLException {
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT COUNT(1) FROM t_player")) {
            if (rs.next()) return rs.getInt(1);
        }
        return 0;
    }

    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("插入学员失败");
    }

    // 插入活动报名数据
    private long insertActivityPlayer(Connection conn, long activityId, long playerId) throws SQLException {
        String sql = "INSERT INTO t_activity_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("插入报名关系失败");
    }
}