import java.sql.*;

public class CleanCesiumMapConfigData {
    public static void main(String[] args) {
        String url = "jdbc:dm://192.168.0.145:30236?charset=utf8";
        String username = "WATERSHED";
        String password = "WaterShed./1224";

        try {
            Class.forName("dm.jdbc.driver.DmDriver");
            Connection conn = DriverManager.getConnection(url, username, password);
            Statement stmt = conn.createStatement();

            System.out.println("=== 清理前的数据统计 ===");
            ResultSet rs1 = stmt.executeQuery("SELECT user_id, COUNT(*) as count FROM WATERSHED.CESIUM_MAP_CONFIG GROUP BY user_id");
            while (rs1.next()) {
                System.out.println("用户ID: " + rs1.getLong("user_id") + ", 配置数量: " + rs1.getInt("count"));
            }

            System.out.println("\n=== 开始清理重复配置 ===");
            
            ResultSet rs2 = stmt.executeQuery(
                "SELECT user_id FROM WATERSHED.CESIUM_MAP_CONFIG GROUP BY user_id HAVING COUNT(*) > 1"
            );
            
            int totalDeleted = 0;
            while (rs2.next()) {
                Long userId = rs2.getLong("user_id");
                
                System.out.println("\n处理用户ID: " + userId);
                
                ResultSet rs3 = stmt.executeQuery(
                    "SELECT config_id, is_default, update_time FROM WATERSHED.CESIUM_MAP_CONFIG " +
                    "WHERE user_id = " + userId + " ORDER BY update_time DESC"
                );
                
                java.util.List<Long> idsToDelete = new java.util.ArrayList<>();
                boolean hasDefault = false;
                
                while (rs3.next()) {
                    Long configId = rs3.getLong("config_id");
                    int isDefault = rs3.getInt("is_default");
                    
                    if (isDefault == 1) {
                        System.out.println("  保留默认配置: ID=" + configId);
                        hasDefault = true;
                    } else {
                        idsToDelete.add(configId);
                    }
                }
                rs3.close();
                
                for (Long configId : idsToDelete) {
                    int deleteResult = stmt.executeUpdate(
                        "DELETE FROM WATERSHED.CESIUM_MAP_CONFIG WHERE config_id = " + configId
                    );
                    if (deleteResult > 0) {
                        System.out.println("  删除重复配置: ID=" + configId);
                        totalDeleted++;
                    }
                }
            }
            rs2.close();

            System.out.println("\n=== 清理后的数据统计 ===");
            ResultSet rs4 = stmt.executeQuery("SELECT user_id, COUNT(*) as count FROM WATERSHED.CESIUM_MAP_CONFIG GROUP BY user_id");
            while (rs4.next()) {
                System.out.println("用户ID: " + rs4.getLong("user_id") + ", 配置数量: " + rs4.getInt("count"));
            }

            System.out.println("\n=== 清理完成 ===");
            System.out.println("总共删除了 " + totalDeleted + " 条重复配置");

            rs1.close();
            rs4.close();
            stmt.close();
            conn.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}