import java.sql.*;

public class InspectHiddenMenus {
    static final String TENANT_URL = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/fs_tenant_cs1?useSSL=false&serverTimezone=GMT%2B8";
    static final String MASTER_URL = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/ylrz_saas?useSSL=false&serverTimezone=GMT%2B8";
    static final String USER = "root";
    static final String PASS = "Ylrz_1q2w3e4r5t6y";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        System.out.println("=== fs_tenant_cs1 sys_menu hidden under 29900 ===");
        countHidden(TENANT_URL, "sys_menu", 29900L);
        System.out.println("\n=== ylrz_saas tenant_sys_menu hidden under 29900 ===");
        countHidden(MASTER_URL, "tenant_sys_menu", 29900L);
        System.out.println("\n=== fs_tenant_cs1 all hidden menus (any root) ===");
        countAllHidden(TENANT_URL, "sys_menu");
        System.out.println("\n=== tenant_sys_menu all hidden ===");
        countAllHidden(MASTER_URL, "tenant_sys_menu");
    }

    static void countHidden(String url, String table, long rootId) throws SQLException {
        try (Connection c = DriverManager.getConnection(url, USER, PASS)) {
            String sql = "SELECT menu_id, menu_name, path, visible FROM " + table
                    + " WHERE (menu_id = ? OR parent_id = ? OR parent_id IN "
                    + "(SELECT menu_id FROM " + table + " WHERE parent_id = ?)) "
                    + "AND visible = '1' ORDER BY menu_id";
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setLong(1, rootId);
                ps.setLong(2, rootId);
                ps.setLong(3, rootId);
                try (ResultSet rs = ps.executeQuery()) {
                    int n = 0;
                    while (rs.next()) {
                        n++;
                        System.out.printf("  id=%d path=%s name=%s%n", rs.getLong(1), rs.getString(3), rs.getString(2));
                    }
                    System.out.println("  hidden count=" + n);
                }
            }
        }
    }

    static void countAllHidden(String url, String table) throws SQLException {
        try (Connection c = DriverManager.getConnection(url, USER, PASS)) {
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT COUNT(*) FROM " + table + " WHERE visible = '1' AND status = '0'")) {
                rs.next();
                System.out.println("  total hidden visible=1 status=0: " + rs.getInt(1));
            }
        }
    }
}