import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Hide legacy lobster root menu 2951 (duplicate of 29900) and fix wrong api-registry path.
 */
public class ApplyMgnuiLobsterMenuFix {

    private static final String MASTER_HOST = getenv("DB_HOST", "cq-cdb-8fjmemkb.sql.tencentcdb.com");
    private static final int MASTER_PORT = Integer.parseInt(getenv("DB_PORT", "27220"));
    private static final String MASTER_USER = getenv("DB_USER", "root");
    private static final String MASTER_PASSWORD = getenv("DB_PASSWORD", "Ylrz_1q2w3e4r5t6y");
    private static final String MASTER_DB = getenv("DB_NAME", "ylrz_saas");

    private static final String[] MASTER_FIX_SQL = {
            "UPDATE tenant_sys_menu SET visible = '1' WHERE menu_id = 2951",
            "UPDATE tenant_sys_menu SET visible = '1' WHERE parent_id = 2951",
            "UPDATE tenant_sys_menu SET path = 'api-registry' WHERE menu_id = 2955 AND path = 'lobster-api-registry'",
            "UPDATE sys_menu SET visible = '1' WHERE menu_id = 2951",
            "UPDATE sys_menu SET visible = '1' WHERE parent_id = 2951",
            "UPDATE sys_menu SET path = 'api-registry' WHERE menu_id = 2955 AND path = 'lobster-api-registry'"
    };

    private static final String[] TENANT_FIX_SQL = {
            "UPDATE sys_menu SET visible = '1' WHERE menu_id = 2951",
            "UPDATE sys_menu SET visible = '1' WHERE parent_id = 2951",
            "UPDATE sys_menu SET path = 'api-registry' WHERE menu_id = 2955 AND path = 'lobster-api-registry'"
    };

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        System.out.println("=== Apply mgnui lobster menu fix ===");

        try (Connection master = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            applyFix(master, MASTER_FIX_SQL, "MASTER");
            printRoots(master, "tenant_sys_menu");
        }

        List<Long> tenantIds = loadActiveTenantIds(
                open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD));
        int ok = 0;
        for (Long tenantId : tenantIds) {
            TenantDb tenant = loadTenant(
                    open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD), tenantId);
            if (tenant == null) {
                continue;
            }
            System.out.println("\n[TENANT " + tenantId + "] " + tenant.database);
            try (Connection conn = open(tenant.host, tenant.port, tenant.database, tenant.user, tenant.password)) {
                applyFix(conn, TENANT_FIX_SQL, "sys_menu");
                printRoots(conn, "sys_menu");
                ok++;
            } catch (Exception e) {
                System.out.println("  [ERR] " + e.getMessage());
            }
        }
        System.out.println("\nDone. tenants updated=" + ok);
    }

    private static void applyFix(Connection conn, String[] sqlList, String label) throws SQLException {
        for (String sql : sqlList) {
            if (sql.contains("tenant_sys_menu") && !tableExists(conn, "tenant_sys_menu")) {
                continue;
            }
            if (sql.contains("sys_menu") && !tableExists(conn, "sys_menu")) {
                continue;
            }
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                int n = ps.executeUpdate();
                System.out.println("  " + label + " | rows=" + n + " | " + sql.substring(0, Math.min(55, sql.length())));
            }
        }
    }

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

    private static void printRoots(Connection conn, String table) throws SQLException {
        String sql = "SELECT menu_id, menu_name, path, visible FROM " + table
                + " WHERE parent_id = 0 AND (menu_id IN (2951,29900) OR path LIKE '%lobster%') ORDER BY menu_id";
        try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                System.out.printf("  root id=%d path=%s visible=%s name=%s%n",
                        rs.getLong(1), rs.getString(3), rs.getString(4), rs.getString(2));
            }
        }
    }

    private static List<Long> loadActiveTenantIds(Connection master) throws SQLException {
        List<Long> ids = new ArrayList<>();
        try (Statement st = master.createStatement();
             ResultSet rs = st.executeQuery("SELECT id FROM tenant_info WHERE status = 1 ORDER BY id")) {
            while (rs.next()) {
                ids.add(rs.getLong(1));
            }
        }
        return ids;
    }

    private static TenantDb loadTenant(Connection master, long tenantId) throws SQLException {
        try (PreparedStatement ps = master.prepareStatement(
                "SELECT db_url, db_account, db_pwd FROM tenant_info WHERE id = ? AND status = 1 LIMIT 1")) {
            ps.setLong(1, tenantId);
            try (ResultSet rs = ps.executeQuery()) {
                if (!rs.next()) {
                    return null;
                }
                return parseJdbcUrl(rs.getString("db_url"), rs.getString("db_account"), rs.getString("db_pwd"));
            }
        }
    }

    private static TenantDb parseJdbcUrl(String jdbcUrl, String user, String password) {
        String body = jdbcUrl.substring("jdbc:mysql://".length());
        int slash = body.indexOf('/');
        String hostPort = body.substring(0, slash);
        String rest = body.substring(slash + 1);
        int q = rest.indexOf('?');
        String database = q >= 0 ? rest.substring(0, q) : rest;
        int colon = hostPort.indexOf(':');
        TenantDb t = new TenantDb();
        t.host = colon >= 0 ? hostPort.substring(0, colon) : hostPort;
        t.port = colon >= 0 ? Integer.parseInt(hostPort.substring(colon + 1)) : 3306;
        t.database = database;
        t.user = user != null ? user : MASTER_USER;
        t.password = password != null ? password : MASTER_PASSWORD;
        return t;
    }

    private static Connection open(String host, int port, String database, String user, String password)
            throws SQLException {
        return DriverManager.getConnection(
                "jdbc:mysql://" + host + ":" + port + "/" + database
                        + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true",
                user, password);
    }

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

    private static final class TenantDb {
        String host;
        int port;
        String database;
        String user;
        String password;
    }
}