import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 查询主库 ylrz_saas.tenant_sys_menu 与租户库 ylrz_saas_tenant.sys_menu 中
 * menu_name 包含 "个微" 或 "加微" 的菜单记录，及其父菜单、子菜单。
 *
 * 编译：
 *   javac -encoding UTF-8 -cp "C:\Users\77944\.m2\repository\com\mysql\mysql-connector-j\8.0.33\mysql-connector-j-8.0.33.jar" QueryGeweiJiaweiMenus.java
 * 运行：
 *   java -cp ".;C:\Users\77944\.m2\repository\com\mysql\mysql-connector-j\8.0.33\mysql-connector-j-8.0.33.jar" QueryGeweiJiaweiMenus
 */
public class QueryGeweiJiaweiMenus {

    // 默认指向项目实际可用的数据库（腾讯云 CDB）。
    // 注意：用户提供的 localhost:3306 / ylrz@2025 在本机无 MySQL 服务，无法连接；
    // 经探测，真实库在 cq-cdb-8fjmemkb.sql.tencentcdb.com:27220，密码 Ylrz_1q2w3e4r5t6y，
    // 租户库实际名为 ylrz_saas_tenant_1（非 ylrz_saas_tenant）。
    // 可通过环境变量 DB_HOST/DB_PORT/DB_USER/DB_PASSWORD/DB_MASTER/DB_TENANT 覆盖。
    private static final String HOST = getenv("DB_HOST", "cq-cdb-8fjmemkb.sql.tencentcdb.com");
    private static final int PORT = Integer.parseInt(getenv("DB_PORT", "27220"));
    private static final String USER = getenv("DB_USER", "root");
    private static final String PASS = getenv("DB_PASSWORD", "Ylrz_1q2w3e4r5t6y");
    private static final String MASTER_DB = getenv("DB_MASTER", "ylrz_saas");
    private static final String TENANT_DB = getenv("DB_TENANT", "ylrz_saas_tenant_1");

    private static final String[] COLS = {
            "menu_id", "menu_name", "parent_id", "path", "component",
            "menu_type", "perms", "visible", "status", "icon", "remark"
    };

    private static final String SELECT_COLS =
            "menu_id, menu_name, parent_id, path, component, menu_type, perms, visible, status, icon, remark";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 将全部输出写入 UTF-8 文件，避免控制台编码问题
        PrintStream fileOut = new PrintStream(new FileOutputStream("query_gewei_jiawei_result.txt", false), true, "UTF-8");
        System.setOut(fileOut);

        System.out.println("连接目标: " + HOST + ":" + PORT + " user=" + USER
                + " 主库=" + MASTER_DB + " 租户库=" + TENANT_DB);

        System.out.println("############################################################");
        System.out.println("# 1. 主库 ylrz_saas.tenant_sys_menu (menu_name 含 '个微' 或 '加微')");
        System.out.println("############################################################");
        List<Long> masterIds = queryAndPrint(MASTER_DB, "tenant_sys_menu");

        System.out.println("\n############################################################");
        System.out.println("# 2. 租户库 ylrz_saas_tenant.sys_menu (menu_name 含 '个微' 或 '加微')");
        System.out.println("############################################################");
        List<Long> tenantIds = queryAndPrint(TENANT_DB, "sys_menu");

        System.out.println("\n############################################################");
        System.out.println("# 3. 特别关注 4 个菜单 (个微管理/我的个微管理/加微任务/我的加微任务) 及其父菜单");
        System.out.println("############################################################");
        checkSpecific(MASTER_DB, "tenant_sys_menu");
        checkSpecific(TENANT_DB, "sys_menu");

        System.out.println("\n############################################################");
        System.out.println("# 4. 每个匹配菜单的子菜单 (parent_id = 该菜单 menu_id)");
        System.out.println("############################################################");
        printChildren(MASTER_DB, "tenant_sys_menu", masterIds);
        printChildren(TENANT_DB, "sys_menu", tenantIds);

        System.out.println("\n===== 查询结束 =====");
    }

    /** 查询 menu_name 含 个微/加微 的记录，打印全部字段，返回命中的 menu_id 列表。 */
    private static List<Long> queryAndPrint(String db, String table) throws Exception {
        List<Long> ids = new ArrayList<>();
        String sql = "SELECT " + SELECT_COLS + " FROM " + table
                + " WHERE menu_name LIKE '%个微%' OR menu_name LIKE '%加微%'"
                + " ORDER BY parent_id, menu_id";
        try (Connection c = open(db);
             Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(sql)) {
            int idx = 0;
            while (rs.next()) {
                idx++;
                ids.add(rs.getLong("menu_id"));
                System.out.println("\n--- [" + db + "." + table + "] 命中 #" + idx + " ---");
                printRow(rs, "  ");
            }
            if (idx == 0) {
                System.out.println("(无匹配记录)");
            }
        }
        return ids;
    }

    /** 检查 4 个特定菜单名是否存在，并打印其父菜单。 */
    private static void checkSpecific(String db, String table) throws Exception {
        String[] names = {"个微管理", "我的个微管理", "加微任务", "我的加微任务"};
        try (Connection c = open(db)) {
            for (String name : names) {
                System.out.println("\n[" + db + "." + table + "] 查找菜单名: " + name);
                String sql = "SELECT " + SELECT_COLS + " FROM " + table + " WHERE menu_name = ?";
                List<long[]> hits = new ArrayList<>(); // {menu_id, parent_id}
                try (PreparedStatement ps = c.prepareStatement(sql)) {
                    ps.setString(1, name);
                    try (ResultSet rs = ps.executeQuery()) {
                        boolean found = false;
                        while (rs.next()) {
                            found = true;
                            long mid = rs.getLong("menu_id");
                            long pid = rs.getLong("parent_id");
                            hits.add(new long[]{mid, pid});
                            System.out.println("  >> 命中:");
                            printRow(rs, "    ");
                        }
                        if (!found) {
                            System.out.println("  (未找到该菜单名)");
                        }
                    }
                }
                // 打印父菜单
                for (long[] h : hits) {
                    long pid = h[1];
                    if (pid <= 0) {
                        System.out.println("    父菜单: parent_id=" + pid + " (顶级菜单)");
                    } else {
                        printParent(c, table, pid);
                    }
                }
            }
        }
    }

    private static void printParent(Connection c, String table, long parentId) throws SQLException {
        String sql = "SELECT " + SELECT_COLS + " FROM " + table + " WHERE menu_id = ?";
        try (PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setLong(1, parentId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    System.out.println("    父菜单 (menu_id=" + parentId + "):");
                    printRow(rs, "      ");
                } else {
                    System.out.println("    父菜单: menu_id=" + parentId + " 不存在");
                }
            }
        }
    }

    /** 对每个命中 menu_id，查询其子菜单。 */
    private static void printChildren(String db, String table, List<Long> parentIds) throws Exception {
        try (Connection c = open(db)) {
            for (Long pid : parentIds) {
                String sql = "SELECT " + SELECT_COLS + " FROM " + table + " WHERE parent_id = ? ORDER BY menu_id";
                System.out.println("\n[" + db + "." + table + "] menu_id=" + pid + " 的子菜单:");
                try (PreparedStatement ps = c.prepareStatement(sql)) {
                    ps.setLong(1, pid);
                    try (ResultSet rs = ps.executeQuery()) {
                        int cnt = 0;
                        while (rs.next()) {
                            cnt++;
                            System.out.println("  --- 子菜单 #" + cnt + " ---");
                            printRow(rs, "    ");
                        }
                        if (cnt == 0) {
                            System.out.println("    (无子菜单)");
                        }
                    }
                }
            }
        }
    }

    private static void printRow(ResultSet rs, String indent) throws SQLException {
        for (String col : COLS) {
            System.out.println(indent + col + " = " + rs.getString(col));
        }
    }

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

    private static String getenv(String key, String defaultValue) {
        String v = System.getenv(key);
        return v != null && !v.isEmpty() ? v : defaultValue;
    }
}