import java.sql.*;

public class QueryAdminMenus3 {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/ylrz_saas?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
        try (Connection c = DriverManager.getConnection(url, "root", "Ylrz_1q2w3e4r5t6y");
             Statement st = c.createStatement()) {
            for (int pid : new int[]{2820, 2816, 2802, 2800}) {
                System.out.println("\n=== children of parent_id=" + pid + " ===");
                try (ResultSet rs = st.executeQuery(
                        "SELECT menu_id, menu_name, path, component, icon, menu_type, visible FROM sys_menu "
                                + "WHERE parent_id=" + pid + " ORDER BY order_num, menu_id")) {
                    while (rs.next()) {
                        System.out.printf("%s | %s | path=%s | comp=%s | icon=%s | type=%s | vis=%s%n",
                                rs.getString(1), rs.getString(2), rs.getString(3),
                                rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7));
                    }
                }
            }
            System.out.println("\n=== duplicate menu_name (type C) ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT menu_name, COUNT(*) cnt, GROUP_CONCAT(menu_id) ids, GROUP_CONCAT(component) comps "
                            + "FROM sys_menu WHERE menu_type='C' AND status='0' GROUP BY menu_name HAVING cnt>1 "
                            + "ORDER BY cnt DESC LIMIT 40")) {
                while (rs.next()) {
                    System.out.printf("%s | count=%s | ids=%s | comps=%s%n",
                            rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
                }
            }
            System.out.println("\n=== type C visible menus missing /index component ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT menu_id, menu_name, path, component, icon FROM sys_menu "
                            + "WHERE menu_type='C' AND visible='0' AND status='0' "
                            + "AND (component IS NULL OR component='' OR component NOT LIKE '%/index') "
                            + "ORDER BY menu_id LIMIT 80")) {
                while (rs.next()) {
                    System.out.printf("%s | %s | path=%s | comp=%s | icon=%s%n",
                            rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
                }
            }
            System.out.println("\n=== all menus path contains Upgrade or component upgrade ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT menu_id, menu_name, parent_id, path, component, icon FROM sys_menu "
                            + "WHERE path LIKE '%Upgrade%' OR path LIKE '%upgrade%' "
                            + "OR component LIKE '%upgrade%' ORDER BY menu_id")) {
                while (rs.next()) {
                    System.out.printf("%s | %s | parent=%s | path=%s | comp=%s%n",
                            rs.getString(1), rs.getString(2), rs.getString(3),
                            rs.getString(4), rs.getString(5));
                }
            }
        }
    }
}