yhlxj/tools/duration-repair/RepairSkuDuration.java

import java.io.Console;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;

/** Standalone, read-only MySQL inspection. Never starts the Spring application. */
public final class RepairSkuDuration {
    private static final DateTimeFormatter SQL_TIME =
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSS");
    private static final String VALID_ORDER = "o.status NOT IN ('close','noPayment','refund')";

    static final class Ticket {
        long id, groupsId, skuId, userId;
        String status;
        LocalDateTime start, expiry, updated;
        boolean frozen, recover, vip;
        int reservedDays, compensationDays;
        int aqType = 1;
    }

    static final class Purchase {
        long id, skuId;
        int orderType = 1, quantity = 1;
        boolean slot;
    }

    static final class Decision {
        final String kind, reason;
        final LocalDateTime newExpiry;
        final double deltaSeconds;

        Decision(String kind, String reason, LocalDateTime newExpiry, double deltaSeconds) {
            this.kind = kind;
            this.reason = reason;
            this.newExpiry = newExpiry;
            this.deltaSeconds = deltaSeconds;
        }
    }

    static Decision classify(Ticket ticket, List<Purchase> purchases, int adjustmentCount) {
        if (ticket.skuId != 772 && ticket.skuId != 774) {
            return new Decision("skipped", "其他规格", null, Double.NaN);
        }
        if (ticket.start == null || ticket.expiry == null) {
            return new Decision("skipped", "未设置完整起止时间", null, Double.NaN);
        }
        LocalDateTime expected = ticket.start.plusMonths(12);
        Duration delta = Duration.between(expected, ticket.expiry);
        double deltaSeconds = delta.getSeconds() + delta.getNano() / 1_000_000_000.0;
        if (!delta.isZero()) {
            // Include small differences straddling midnight, but never silently accept them.
            if (Math.abs(deltaSeconds) <= 60
                    || expected.toLocalDate().equals(ticket.expiry.toLocalDate())) {
                return new Decision("review", "接近一年，但时间不精确相等", null, deltaSeconds);
            }
            return new Decision("skipped", "时长不是12个自然月", null, deltaSeconds);
        }
        List<String> reasons = new ArrayList<>();
        if (ticket.userId <= 0 || !("validity".equals(ticket.status) || "outside".equals(ticket.status))) {
            reasons.add("无当前用户或车票状态需核对");
        }
        if (ticket.frozen || ticket.recover || ticket.reservedDays != 0) {
            reasons.add("冻结/恢复/保留天数");
        }
        if (ticket.compensationDays != 0 || adjustmentCount != 0) {
            reasons.add("存在补偿或手工时长调整");
        }
        if (ticket.vip || ticket.aqType != 1) {
            reasons.add("VIP或非普通购买车票");
        }
        if (purchases.size() != 1) {
            reasons.add("有效关联订单不是一笔");
        } else {
            Purchase purchase = purchases.get(0);
            if (purchase.orderType != 1) {
                reasons.add("续费或未知订单类型");
            }
            if (purchase.skuId != ticket.skuId) {
                reasons.add("订单规格与当前车票不一致");
            }
            if (purchase.quantity < 1 || (purchase.quantity > 1 && !purchase.slot)) {
                reasons.add("购买数量可能叠加时长");
            }
        }
        if (!reasons.isEmpty()) {
            return new Decision("review", String.join("；", reasons), null, deltaSeconds);
        }
        return new Decision("candidate", "精确一年且未发现续费/补时等情况，需核对后执行SQL",
                ticket.expiry.minusMonths(6), deltaSeconds);
    }

    // The generated update requires the inspected identity, timestamps and state to be unchanged.
    static String updateSql(Ticket ticket) {
        if ((ticket.skuId != 772 && ticket.skuId != 774) || ticket.start == null || ticket.expiry == null
                || !ticket.expiry.equals(ticket.start.plusMonths(12))) {
            throw new IllegalArgumentException("只能为精确一年且起止完整的目标规格生成更新");
        }
        return "UPDATE groups_relation gr\n"
                + "JOIN groups_trips gt ON gt.id = gr.groups_id\n"
                + "SET gr.expiry_time = " + literal(ticket.expiry.minusMonths(6)) + ", gr.update_time = NOW()\n"
                + "WHERE gr.id = " + ticket.id + " AND gr.groups_id = " + ticket.groupsId + "\n"
                + "  AND gt.sku_id = " + ticket.skuId + " AND gr.user_id = " + ticket.userId + "\n"
                + "  AND gr.start_time = " + literal(ticket.start) + "\n"
                + "  AND gr.expiry_time = " + literal(ticket.expiry) + "\n"
                + "  AND gr.update_time <=> " + literal(ticket.updated) + "\n"
                + "  AND gr.status = '" + checkedStatus(ticket.status) + "'\n"
                + "  AND COALESCE(gr.is_frozen, 0) = 0 AND COALESCE(gr.is_recover, 0) = 0\n"
                + "  AND COALESCE(gr.reserved_days, 0) = 0 AND COALESCE(gr.compensation_days, 0) = 0\n"
                + "  AND COALESCE(gr.is_vip, 0) = 0 AND gr.aq_type = 1;\n"
                + "SELECT " + ticket.id + " AS relation_id, ROW_COUNT() AS updated_rows;\n";
    }

    private static String checkedStatus(String status) {
        if (!"validity".equals(status) && !"outside".equals(status)) {
            throw new IllegalArgumentException("非有效车票不能生成更新");
        }
        return status;
    }

    private static String literal(LocalDateTime value) {
        return value == null ? "NULL" : "'" + SQL_TIME.format(value) + "'";
    }

    private static LocalDateTime date(ResultSet rs, String name) throws SQLException {
        // Reading DATETIME text avoids an unintended JVM/JDBC timezone conversion.
        String value = rs.getString(name);
        return value == null ? null : LocalDateTime.parse(value.replace(' ', 'T'));
    }

    private static List<Ticket> tickets(Connection connection) throws SQLException {
        String sql = "SELECT gr.id, gr.groups_id, gt.sku_id, gr.user_id, gr.status, "
                + "gr.start_time, gr.expiry_time, gr.update_time, gr.is_frozen, gr.is_recover, "
                + "gr.reserved_days, gr.compensation_days, gr.aq_type, gr.is_vip "
                + "FROM groups_relation gr JOIN groups_trips gt ON gt.id = gr.groups_id "
                + "WHERE gt.sku_id IN (772,774) ORDER BY gt.sku_id, gr.id";
        List<Ticket> tickets = new ArrayList<>();
        try (Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                Ticket ticket = new Ticket();
                ticket.id = rs.getLong("id");
                ticket.groupsId = rs.getLong("groups_id");
                ticket.skuId = rs.getLong("sku_id");
                ticket.userId = rs.getLong("user_id");
                ticket.status = rs.getString("status");
                ticket.start = date(rs, "start_time");
                ticket.expiry = date(rs, "expiry_time");
                ticket.updated = date(rs, "update_time");
                ticket.frozen = rs.getBoolean("is_frozen");
                ticket.recover = rs.getBoolean("is_recover");
                ticket.reservedDays = rs.getInt("reserved_days");
                ticket.compensationDays = rs.getInt("compensation_days");
                ticket.aqType = rs.getInt("aq_type");
                ticket.vip = rs.getBoolean("is_vip");
                tickets.add(ticket);
            }
        }
        return tickets;
    }

    private static void loadHistory(Connection connection, List<Ticket> batch,
                                    Map<Long, List<Purchase>> purchases, Map<Long, Integer> adjustments)
            throws SQLException {
        String marks = String.join(",", Collections.nCopies(batch.size(), "?"));
        String columns = "SELECT gr.id AS relation_id, o.id AS order_id, o.sku_id, o.order_type, o.num, ";
        String sql = columns + "0 AS via_slot FROM groups_relation gr "
                + "JOIN order_don o ON o.relation_id = gr.id AND o.user_id = gr.user_id "
                + "WHERE gr.id IN (" + marks + ") AND " + VALID_ORDER
                + " UNION ALL " + columns + "1 AS via_slot FROM groups_relation gr "
                + "JOIN order_don_ticket_record tr ON tr.relation_id = gr.id AND tr.status = 'success' "
                + "JOIN order_don o ON o.id = tr.order_id AND o.user_id = gr.user_id "
                + "WHERE gr.id IN (" + marks + ") AND " + VALID_ORDER;
        Map<Long, Map<Long, Purchase>> distinct = new LinkedHashMap<>();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            bindIds(stmt, batch, 1);
            bindIds(stmt, batch, batch.size() + 1);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    long relationId = rs.getLong("relation_id");
                    long orderId = rs.getLong("order_id");
                    Purchase purchase = distinct.computeIfAbsent(relationId, key -> new LinkedHashMap<>())
                            .computeIfAbsent(orderId, key -> new Purchase());
                    purchase.id = orderId;
                    purchase.skuId = rs.getLong("sku_id");
                    purchase.orderType = rs.getInt("order_type");
                    purchase.quantity = rs.getInt("num");
                    purchase.slot |= rs.getBoolean("via_slot");
                }
            }
        }
        distinct.forEach((id, records) -> purchases.put(id, new ArrayList<>(records.values())));
        String adjustmentSql = "SELECT gr.id, COUNT(*) AS total FROM groups_relation gr "
                + "JOIN incr_expiry_time_record r ON r.relation_id = gr.id AND r.user_id = gr.user_id "
                + "WHERE gr.id IN (" + marks + ") GROUP BY gr.id";
        try (PreparedStatement stmt = connection.prepareStatement(adjustmentSql)) {
            bindIds(stmt, batch, 1);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    adjustments.put(rs.getLong("id"), rs.getInt("total"));
                }
            }
        }
    }

    private static void bindIds(PreparedStatement stmt, List<Ticket> batch, int first) throws SQLException {
        for (int i = 0; i < batch.size(); i++) {
            stmt.setLong(first + i, batch.get(i).id);
        }
    }

    private static String csv(Object value) {
        String text = value == null ? "" : value.toString();
        return "\"" + text.replace("\"", "\"\"") + "\"";
    }

    private static void writeReports(Path directory, List<Ticket> tickets,
                                     Map<Long, List<Purchase>> purchases, Map<Long, Integer> adjustments)
            throws IOException {
        // A fresh output folder prevents overwriting an earlier inspection.
        Files.createDirectory(directory);
        StringBuilder report = new StringBuilder("\uFEFFrelation_id,sku_id,user_id,groups_id,status,start_time,"
                + "old_expiry_time,proposed_expiry_time,update_time,kind,reason,year_delta_seconds,"
                + "is_frozen,is_recover,reserved_days,compensation_days,aq_type,is_vip,orders,adjustment_count\n");
        StringBuilder sql = new StringBuilder("-- 仅包含候选车票；先核对同目录 preview.csv。\n"
                + "-- 这是日期和关联历史筛查，不能证明所有候选都是误设；人工确认错误范围后执行。\n"
                + "-- 每条更新使用旧时间和身份校验；updated_rows 应为 1，若为 0 则数据已变化或已修复。\n"
                + "-- 本程序未执行数据库写入；SQL 按同一连接分段执行，末尾手动提交或回滚。\n"
                + "-- 事务回滚要求 groups_relation 使用 InnoDB 等事务引擎。\n"
                + "START TRANSACTION;\n\n");
        Map<String, Integer> counts = new LinkedHashMap<>();
        for (Ticket ticket : tickets) {
            List<Purchase> orders = purchases.getOrDefault(ticket.id, Collections.emptyList());
            int adjustmentCount = adjustments.getOrDefault(ticket.id, 0);
            Decision decision = classify(ticket, orders, adjustmentCount);
            counts.merge(decision.kind, 1, Integer::sum);
            String orderInfo = orders.stream().map(order -> order.id + ":sku=" + order.skuId
                    + ":type=" + order.orderType + ":num=" + order.quantity + ":slot=" + order.slot)
                    .collect(Collectors.joining(";"));
            Object[] fields = {ticket.id, ticket.skuId, ticket.userId, ticket.groupsId, ticket.status,
                    ticket.start, ticket.expiry, decision.newExpiry, ticket.updated,
                    decision.kind, decision.reason, Double.isNaN(decision.deltaSeconds) ? "" : decision.deltaSeconds,
                    ticket.frozen, ticket.recover, ticket.reservedDays, ticket.compensationDays,
                    ticket.aqType, ticket.vip, orderInfo, adjustmentCount};
            report.append(Arrays.stream(fields).map(RepairSkuDuration::csv).collect(Collectors.joining(",")))
                    .append('\n');
            if ("candidate".equals(decision.kind)) {
                sql.append(updateSql(ticket)).append('\n');
            }
        }
        sql.append("-- 在同一连接中二选一执行，必须结束上面的事务：\n-- COMMIT;\n-- ROLLBACK;\n");
        Files.writeString(directory.resolve("preview.csv"), report, StandardCharsets.UTF_8,
                StandardOpenOption.CREATE_NEW);
        Files.writeString(directory.resolve("repair.sql"), sql, StandardCharsets.UTF_8,
                StandardOpenOption.CREATE_NEW);
        System.out.println("只读检查完成，未修改数据库。车票总数：" + tickets.size() + "；分类：" + counts);
        System.out.println("输出目录：" + directory.toAbsolutePath());
    }

    private static String environment(String name) {
        String value = System.getenv(name);
        if (value == null || value.isBlank()) {
            throw new IllegalArgumentException("请设置环境变量 " + name);
        }
        return value;
    }

    public static void main(String[] args) throws Exception {
        if (args.length != 1 || "--help".equals(args[0])) {
            System.out.println("用法：java -cp <MySQL驱动路径> RepairSkuDuration.java <新建输出目录>\n"
                    + "设置 DURATION_REPAIR_JDBC_URL、DURATION_REPAIR_DB_USER。\n"
                    + "密码通过终端隐藏输入，或使用 DURATION_REPAIR_DB_PASSWORD。\n"
                    + "只执行SELECT；生成 preview.csv 和 repair.sql；不修改数据库或规格配置。");
            return;
        }
        Path output = Path.of(args[0]).toAbsolutePath();
        if (Files.exists(output)) {
            throw new IllegalArgumentException("输出目录已存在，请使用一个新目录");
        }
        String url = environment("DURATION_REPAIR_JDBC_URL");
        if (!url.startsWith("jdbc:mysql:")) {
            throw new IllegalArgumentException("仅支持 MySQL JDBC URL");
        }
        Properties properties = new Properties();
        properties.setProperty("user", environment("DURATION_REPAIR_DB_USER"));
        String password = System.getenv("DURATION_REPAIR_DB_PASSWORD");
        if (password == null) {
            Console console = System.console();
            if (console == null) {
                throw new IllegalArgumentException("无交互终端，请设置 DURATION_REPAIR_DB_PASSWORD");
            }
            char[] chars = console.readPassword("数据库密码：");
            if (chars == null) {
                throw new IllegalArgumentException("未输入数据库密码");
            }
            password = new String(chars);
            Arrays.fill(chars, '\0');
        }
        properties.setProperty("password", password);
        properties.setProperty("connectTimeout", "10000");
        properties.setProperty("socketTimeout", "60000");
        List<Ticket> tickets;
        Map<Long, List<Purchase>> purchases = new LinkedHashMap<>();
        Map<Long, Integer> adjustments = new LinkedHashMap<>();
        try (Connection connection = DriverManager.getConnection(url, properties)) {
            connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
            connection.setReadOnly(true);
            connection.setAutoCommit(false);
            try {
                // Enforce read-only on the server as well as through JDBC.
                try (Statement stmt = connection.createStatement()) {
                    stmt.execute("START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY");
                }
                tickets = tickets(connection);
                for (int offset = 0; offset < tickets.size(); offset += 200) {
                    loadHistory(connection, tickets.subList(offset, Math.min(offset + 200, tickets.size())),
                            purchases, adjustments);
                }
            } finally {
                connection.rollback();
            }
        }
        writeReports(output, tickets, purchases, adjustments);
    }
}