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

public class InspectLobsterMenusBroad {
    static final String URL_T = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/fs_tenant_cs1?useUnicode=true&characterEncoding=utf8&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");
        new File("output").mkdirs();
        try (Connection c = DriverManager.getConnection(URL_T, USER, PASS);
             PrintWriter out = new PrintWriter(new OutputStreamWriter(
                     new FileOutputStream("output/inspect-tenant-lobster-broad.txt"), "UTF-8"))) {
            query(c, out, "sys_menu all lobster-ish",
                    "SELECT menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, HEX(menu_name) hx "
                            + "FROM sys_menu WHERE menu_id BETWEEN 29900 AND 33000 OR menu_id IN (3006,3007,3008,32481,2951) "
                            + "OR component LIKE 'lobster/%' OR component LIKE 'company/workflowLobster/%' "
                            + "OR path='/lobster' ORDER BY parent_id, order_num, menu_id");
            query(c, out, "sys_menu visible leaves under 29900",
                    "SELECT menu_id, menu_name, parent_id, order_num, path, component, visible "
                            + "FROM sys_menu WHERE parent_id=29900 AND menu_type='C' AND visible='0' ORDER BY order_num");
            query(c, out, "company_menu distinct visible leaves under 29900",
                    "SELECT DISTINCT menu_id, menu_name, parent_id, order_num, path, component, visible "
                            + "FROM company_menu WHERE parent_id=29900 AND menu_type='C' AND visible='0' ORDER BY order_num");
            query(c, out, "company_menu all rows lobster tree",
                    "SELECT menu_id, menu_name, parent_id, order_num, company_id, visible "
                            + "FROM company_menu WHERE menu_id=29900 OR parent_id=29900 ORDER BY menu_id, company_id");
            query(c, out, "sys_menu count all under 29900 subtree",
                    "WITH RECURSIVE t AS ("
                            + "SELECT menu_id FROM sys_menu WHERE menu_id=29900 "
                            + "UNION ALL SELECT m.menu_id FROM sys_menu m JOIN t ON m.parent_id=t.menu_id"
                            + ") SELECT COUNT(*) c FROM t");
        }
        System.out.println("done");
    }

    static void query(Connection c, PrintWriter out, String title, String sql) throws Exception {
        out.println("\n=== " + title + " ===");
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            int n = 0;
            int cols = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                n++;
                StringBuilder sb = new StringBuilder();
                for (int i = 1; i <= cols; i++) {
                    if (i > 1) sb.append(" | ");
                    sb.append(rs.getMetaData().getColumnLabel(i)).append('=').append(rs.getString(i));
                }
                out.println(sb);
            }
            out.println("count=" + n);
        } catch (SQLException e) {
            out.println("ERROR: " + e.getMessage());
        }
    }
}