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

/**
 * Rename menu brand: lobster engine root -> AI digital employee, all master + tenant DBs.
 * Also fixes known garbled menu_name on master sys_menu (menu_id=29951).
 *
 * Usage:
 *   java -cp ".;lib/mysql-connector-j-8.0.33.jar" RenameLobsterEngineBrand
 */
public class RenameLobsterEngineBrand {

    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_NAME", "ylrz_saas");

    private static final String OLD_ROOT = "\u9f99\u867e\u5f15\u64ce";
    private static final String NEW_ROOT = "AI\u6570\u5b57\u5458\u5de5";
    private static final String FIX_QDRANT = "\u5411\u91cf\u77e5\u8bc6\u5e93";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        new File("output").mkdirs();
        try (PrintWriter report = new PrintWriter(new OutputStreamWriter(
                new FileOutputStream("output/rename-lobster-brand-report.txt"), "UTF-8"));
             Connection master = open(MASTER_DB)) {
            report.println("Rename '" + OLD_ROOT + "' -> '" + NEW_ROOT + "'");
            renameOnDb(master, report, MASTER_DB, "sys_menu", "tenant_sys_menu", "tenant_company_menu");

            List<TenantDb> tenants = loadTenants(master);
            for (TenantDb tenant : tenants) {
                try (Connection c = openDb(tenant)) {
                    renameOnDb(c, report, tenant.database, "sys_menu", "company_menu");
                } catch (Exception e) {
                    report.println("[ERROR] " + tenant.database + ": " + e.getMessage());
                }
            }
            fixGarbledQdrant(master, report);
            report.println("\nDone.");
            System.out.println("Wrote output/rename-lobster-brand-report.txt");
        }
    }

    private static void renameOnDb(Connection conn, PrintWriter report, String dbLabel, String... tables) throws SQLException {
        for (String table : tables) {
            if (!tableExists(conn, table)) {
                continue;
            }
            int exact = updateExact(conn, table);
            int prefix = updatePrefix(conn, table);
            if (exact + prefix > 0) {
                report.println(dbLabel + "." + table + " exact=" + exact + " prefix=" + prefix);
            }
        }
    }

    private static int updateExact(Connection conn, String table) throws SQLException {
        String sql = "UPDATE " + table + " SET menu_name = ?, update_time = NOW() WHERE menu_name = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, NEW_ROOT);
            ps.setString(2, OLD_ROOT);
            return ps.executeUpdate();
        }
    }

    private static int updatePrefix(Connection conn, String table) throws SQLException {
        String sql = "UPDATE " + table
                + " SET menu_name = CONCAT(?, SUBSTRING(menu_name, CHAR_LENGTH(?) + 1)), update_time = NOW() "
                + "WHERE menu_name LIKE CONCAT(?, '%') AND menu_name <> ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, NEW_ROOT);
            ps.setString(2, OLD_ROOT);
            ps.setString(3, OLD_ROOT);
            ps.setString(4, OLD_ROOT);
            return ps.executeUpdate();
        }
    }

    private static void fixGarbledQdrant(Connection master, PrintWriter report) throws SQLException {
        if (!tableExists(master, "sys_menu")) {
            return;
        }
        String sql = "UPDATE sys_menu SET menu_name = ?, update_time = NOW() WHERE menu_id = 29951 "
                + "AND (menu_name IS NULL OR menu_name <> ? OR menu_name LIKE '%\\ufffd%')";
        try (PreparedStatement ps = master.prepareStatement(sql)) {
            ps.setString(1, FIX_QDRANT);
            ps.setString(2, FIX_QDRANT);
            int n = ps.executeUpdate();
            if (n > 0) {
                report.println(MASTER_DB + ".sys_menu fix qdrant menu_id=29951 rows=" + n);
            }
        }
    }

    private static List<TenantDb> loadTenants(Connection master) throws SQLException {
        List<TenantDb> list = new ArrayList<>();
        try (Statement st = master.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, db_url, db_account, db_pwd FROM tenant_info WHERE status = 1 ORDER BY id")) {
            while (rs.next()) {
                TenantDb t = parseJdbcUrl(rs.getString("db_url"), rs.getString("db_account"), rs.getString("db_pwd"));
                if (t != null) {
                    list.add(t);
                }
            }
        }
        return list;
    }

    private static TenantDb parseJdbcUrl(String jdbcUrl, String user, String password) {
        if (jdbcUrl == null || !jdbcUrl.startsWith("jdbc:mysql://")) {
            return null;
        }
        String body = jdbcUrl.substring("jdbc:mysql://".length());
        int slash = body.indexOf('/');
        if (slash < 0) {
            return null;
        }
        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;
        TenantDb t = new TenantDb();
        int colon = hostPort.indexOf(':');
        if (colon >= 0) {
            t.host = hostPort.substring(0, colon);
            t.port = Integer.parseInt(hostPort.substring(colon + 1));
        } else {
            t.host = hostPort;
            t.port = 3306;
        }
        t.database = database;
        t.user = user != null ? user : USER;
        t.password = password != null ? password : PASS;
        return t;
    }

    private static boolean tableExists(Connection conn, String table) throws SQLException {
        try (PreparedStatement ps = conn.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();
            }
        }
    }

    private static Connection open(String database) throws SQLException {
        return DriverManager.getConnection(buildUrl(HOST, PORT, database), USER, PASS);
    }

    private static Connection openDb(TenantDb tenant) throws SQLException {
        return DriverManager.getConnection(buildUrl(tenant.host, tenant.port, tenant.database), tenant.user, tenant.password);
    }

    private static String buildUrl(String host, int port, String database) {
        return "jdbc:mysql://" + host + ":" + port + "/" + database
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&allowMultiQueries=true";
    }

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

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