import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/** List lobster engine menus for saas-mgnui and saas-companyui. */
public class QueryLobsterMenusForUi {

    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220";
    private static final String USER = "root";
    private static final String PASS = "Ylrz_1q2w3e4r5t6y";

    public static void main(String[] args) throws Exception {
        long companyId = args.length > 0 ? Long.parseLong(args[0]) : 338L;
        String tenantDb = args.length > 1 ? args[1] : "fs_tenant_cs1";

        printSection("saas-mgnui", "ylrz_saas", "tenant_sys_menu", null, false);
        printSection("saas-mgnui platform sys_menu", "ylrz_saas", "sys_menu", null, false);
        printSection("saas-companyui template", "ylrz_saas", "tenant_company_menu", null, false);
        printSection("saas-companyui tenant companyId=" + companyId, tenantDb, "company_menu", companyId, true);
    }

    private static void printSection(String label, String db, String table, Long companyId, boolean groupByCompany)
            throws Exception {
        System.out.println("\n" + "=".repeat(90));
        System.out.println(label + "  [" + db + "." + table + "]");
        System.out.println("=".repeat(90));

        if (!tableExists(db, table)) {
            System.out.println("  (table not found)");
            return;
        }

        String sql = "SELECT menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms"
                + (companyId != null ? ", company_id" : "")
                + " FROM " + table
                + " WHERE (menu_id = 29900 OR parent_id = 29900 OR path LIKE '%lobster%' OR component LIKE 'lobster/%')"
                + (companyId != null ? " AND (company_id IS NULL OR company_id = " + companyId + ")" : "")
                + " ORDER BY parent_id, order_num, menu_id";

        try (Connection c = open(db);
             Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(sql)) {
            int n = 0;
            List<String> visible = new ArrayList<>();
            List<String> hidden = new ArrayList<>();
            while (rs.next()) {
                n++;
                String line = formatRow(rs, companyId != null);
                if ("0".equals(rs.getString("visible")) && "0".equals(rs.getString("status"))) {
                    visible.add(line);
                } else {
                    hidden.add(line);
                }
            }
            System.out.println("  total=" + n + " visible=" + visible.size() + " hidden/disabled=" + hidden.size());
            System.out.println("\n  --- visible (visible=0, status=0) ---");
            if (visible.isEmpty()) {
                System.out.println("  (none)");
            } else {
                visible.forEach(s -> System.out.println("  " + s));
            }
            if (!hidden.isEmpty()) {
                System.out.println("\n  --- hidden/disabled ---");
                hidden.forEach(s -> System.out.println("  " + s));
            }
        }

        if (groupByCompany && companyId != null) {
            try (Connection c = open(db);
                 Statement st = c.createStatement();
                 ResultSet rs = st.executeQuery(
                         "SELECT COUNT(*) FROM " + table
                                 + " WHERE parent_id=29900 AND menu_type='C' AND visible='0' AND status='0'"
                                 + " AND company_id=" + companyId)) {
                rs.next();
                System.out.println("\n  company " + companyId + " lobster leaf menus (visible): " + rs.getInt(1));
            }
        }
    }

    private static String formatRow(ResultSet rs, boolean withCompany) throws SQLException {
        StringBuilder sb = new StringBuilder();
        sb.append(String.format("%-6d", rs.getLong("menu_id")));
        sb.append(" | ").append(pad(rs.getString("menu_name"), 18));
        sb.append(" | pid=").append(String.format("%-5d", rs.getLong("parent_id")));
        sb.append(" | ord=").append(String.format("%-3d", rs.getInt("order_num")));
        sb.append(" | ").append(pad(nullToEmpty(rs.getString("path")), 22));
        sb.append(" | ").append(pad(trunc(nullToEmpty(rs.getString("component")), 36), 36));
        sb.append(" | ").append(rs.getString("menu_type"));
        if (withCompany) {
            Object cid = rs.getObject("company_id");
            sb.append(" | co=").append(cid == null ? "NULL" : cid);
        }
        String perms = rs.getString("perms");
        if (perms != null && !perms.isBlank()) {
            sb.append(" | ").append(perms);
        }
        return sb.toString();
    }

    private static boolean tableExists(String db, String table) throws SQLException {
        try (Connection c = open(db);
             ResultSet rs = c.getMetaData().getTables(c.getCatalog(), null, table, null)) {
            return rs.next();
        }
    }

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

    private static String pad(String s, int w) {
        if (s == null) s = "";
        if (s.length() >= w) return s.substring(0, w);
        return s + " ".repeat(w - s.length());
    }

    private static String trunc(String s, int max) {
        return s.length() <= max ? s : s.substring(0, max - 3) + "...";
    }

    private static String nullToEmpty(String s) {
        return s == null ? "" : s;
    }
}