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

public class InspectLobsterMenusAll {
    static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220";
    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 (PrintWriter out = new PrintWriter(new OutputStreamWriter(
                new FileOutputStream("output/inspect-lobster-menus-all.txt"), "UTF-8"))) {
            inspect(open("fs_tenant_cs1"), out, "TENANT fs_tenant_cs1");
            inspect(open("ylrz_saas"), out, "MASTER ylrz_saas");
        }
        System.out.println("Wrote output/inspect-lobster-menus-all.txt");
    }

    static void inspect(Connection c, PrintWriter out, String label) throws Exception {
        out.println("\n######## " + label + " ########");
        dumpTable(c, out, "sys_menu", true);
        dumpTable(c, out, "company_menu", true);
        dumpTable(c, out, "tenant_sys_menu", false);
        dumpTable(c, out, "tenant_company_menu", false);
    }

    static void dumpTable(Connection c, PrintWriter out, String table, boolean countDistinct) throws Exception {
        if (!tableExists(c, table)) {
            out.println("-- " + table + " (not exists)");
            return;
        }
        out.println("\n=== " + table + " lobster tree ===");
        String sql = "SELECT menu_id, menu_name, parent_id, order_num, path, component, visible, HEX(menu_name) hx "
                + "FROM " + table + " WHERE menu_id = 29900 OR parent_id = 29900 OR parent_id = 29901 "
                + "OR menu_id = 2951 OR parent_id = 2951 "
                + "OR path = '/lobster' OR component LIKE 'lobster/%' OR component LIKE 'company/workflowLobster/%' "
                + "ORDER BY parent_id, order_num, menu_id";
        int n = 0;
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                n++;
                out.printf("[%d] %s | parent=%d order=%d path=%s comp=%s visible=%s hx=%s%n",
                        rs.getLong("menu_id"), rs.getString("menu_name"), rs.getLong("parent_id"),
                        rs.getInt("order_num"), rs.getString("path"), rs.getString("component"),
                        rs.getString("visible"), rs.getString("hx"));
            }
        }
        out.println("row_count=" + n);
        if (countDistinct && "company_menu".equals(table)) {
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT COUNT(DISTINCT menu_id) FROM company_menu "
                                 + "WHERE menu_id=29900 OR parent_id=29900 OR parent_id=29901")) {
                rs.next();
                out.println("distinct_menu_id=" + rs.getInt(1));
            }
        }
        if ("sys_menu".equals(table) || "tenant_sys_menu".equals(table)) {
            try (Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT COUNT(*) FROM " + table + " WHERE menu_id=29900 OR parent_id=29900 OR parent_id=29901")) {
                rs.next();
                out.println("tree_count=" + rs.getInt(1));
            }
        }
    }

    static boolean tableExists(Connection c, String table) throws SQLException {
        try (PreparedStatement ps = c.prepareStatement(
                "SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=? LIMIT 1")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }

    static Connection open(String db) throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://" + HOST + "/" + db + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8",
                USER, PASS);
    }
}