import java.io.*;
import java.sql.*;

public class InspectLobsterSubtree {
    static final String URL = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/fs_tenant_cs1?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        new File("output").mkdirs();
        try (Connection c = DriverManager.getConnection(URL, "root", "Ylrz_1q2w3e4r5t6y");
             PrintWriter out = new PrintWriter(new OutputStreamWriter(
                     new FileOutputStream("output/inspect-lobster-subtree.txt"), "UTF-8"))) {
            dumpRecursive(c, out, "sys_menu", 29900L);
            dumpRecursive(c, out, "company_menu", 29900L);
        }
    }

    static void dumpRecursive(Connection c, PrintWriter out, String table, long root) throws Exception {
        out.println("\n=== " + table + " recursive from " + root + " ===");
        Set<Long> ids = new java.util.LinkedHashSet<>();
        collect(c, table, root, ids);
        String in = ids.toString().replace('[', ' ').replace(']', ' ').trim().replace(" ", "");
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT menu_id, menu_name, parent_id, order_num, menu_type, visible, path, component "
                             + "FROM " + table + " WHERE menu_id IN (" + in + ") ORDER BY parent_id, order_num, menu_id")) {
            int n = 0;
            while (rs.next()) {
                n++;
                out.printf("[%d] %s parent=%d type=%s visible=%s order=%d path=%s%n",
                        rs.getLong(1), rs.getString(2), rs.getLong(3), rs.getString(5),
                        rs.getString(6), rs.getInt(4), rs.getString(7));
            }
            out.println("count=" + n);
        }
    }

    static void collect(Connection c, String table, long id, Set<Long> ids) throws SQLException {
        if (!ids.add(id)) return;
        try (PreparedStatement ps = c.prepareStatement("SELECT menu_id FROM " + table + " WHERE parent_id=?")) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) collect(c, table, rs.getLong(1), ids);
            }
        }
    }
}