import java.sql.*;

public class ListMenus2 {
    static final String URL = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/ylrz_saas?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
    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");
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement()) {

            // Full tenant_company_menu - grouped by parent
            System.out.println("========== tenant_company_menu (companyui - ALL) ==========");
            String sql = "SELECT m1.order_num, m1.menu_id, m1.menu_name, m1.component, m1.visible, " +
                "m1.menu_type, COALESCE(m2.menu_name,'ROOT') AS parent_name " +
                "FROM tenant_company_menu m1 LEFT JOIN tenant_company_menu m2 ON m1.parent_id = m2.menu_id " +
                "ORDER BY COALESCE(m2.order_num,0), m2.menu_id, m1.order_num, m1.menu_id";
            dump(stmt, sql);

            System.out.println("\n========== tenant_sys_menu (mgnui - ALL) ==========");
            sql = "SELECT m1.order_num, m1.menu_id, m1.menu_name, m1.component, m1.visible, " +
                "m1.menu_type, COALESCE(m2.menu_name,'ROOT') AS parent_name " +
                "FROM tenant_sys_menu m1 LEFT JOIN tenant_sys_menu m2 ON m1.parent_id = m2.menu_id " +
                "ORDER BY COALESCE(m2.order_num,0), m2.menu_id, m1.order_num, m1.menu_id";
            dump(stmt, sql);
        }
    }
    static void dump(Statement s, String sql) throws SQLException {
        try (ResultSet rs = s.executeQuery(sql)) {
            System.out.printf("%-6s %-28s %-45s %-5s %-4s %s\n", "ID", "Name", "Component", "Vis", "Type", "Parent");
            System.out.println("------ ---------------------------- --------------------------------------------- ----- ---- -----");
            while (rs.next()) {
                String vis = "0".equals(rs.getString("visible")) ? "SHOW" : "HIDE";
                String tp = "M".equals(rs.getString("menu_type")) ? "Dir" : "C".equals(rs.getString("menu_type")) ? "Pg" : "Btn";
                System.out.printf("%-6s %-28s %-45s %-5s %-4s %s\n",
                    rs.getString("menu_id"), rs.getString("menu_name"),
                    nvl(rs.getString("component"),28), vis, tp, rs.getString("parent_name"));
            }
        }
    }
    static String nvl(String s, int n) { if(s==null||s.isEmpty()) return "(Dir)"; if(s.length()>n) return s.substring(0,n-3)+"..."; return s; }
}