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

/**
 * Apply master DB lobster DDL gaps (ylrz_saas).
 * Usage: java -cp mysql-connector-j.jar ApplyLobsterMasterSchemaSync
 */
public class ApplyLobsterMasterSchemaSync {

    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");

    public static void main(String[] args) throws Exception {
        System.out.println("========== MASTER -> " + MASTER_DB + " ==========");
        try (Connection conn = open(MASTER_HOST, MASTER_PORT, MASTER_DB, MASTER_USER, MASTER_PASSWORD)) {
            audit(conn, "before");
            applySqlFile(conn, "../sql/master/V20260623_02__lobster_master_word_tables.sql");
            applyVectorStore(conn);
            audit(conn, "after");
        }
    }

    private static void audit(Connection conn, String phase) throws SQLException {
        System.out.println("[AUDIT " + phase + "]");
        printTable(conn, "company_lobster_replace_word");
        printTable(conn, "company_lobster_sensitive_word");
        printTable(conn, "lobster_vector_store");
    }

    private static void printTable(Connection conn, String table) throws SQLException {
        if (tableExists(conn, table)) {
            System.out.println("  OK table: " + table);
        } else {
            System.out.println("  MISSING TABLE: " + table);
        }
    }

    private static void applyVectorStore(Connection conn) throws SQLException {
        if (tableExists(conn, "lobster_vector_store")) {
            return;
        }
        System.out.println("[APPLY] lobster_vector_store");
        try (Statement st = conn.createStatement()) {
            st.execute(
                    "CREATE TABLE IF NOT EXISTS `lobster_vector_store` ("
                            + "`id` bigint NOT NULL AUTO_INCREMENT,"
                            + "`company_id` bigint NOT NULL,"
                            + "`category` varchar(64) NOT NULL,"
                            + "`vec_key` varchar(128) NOT NULL,"
                            + "`text_content` text,"
                            + "`vector` mediumtext COMMENT 'embedding JSON float[]',"
                            + "`metadata` text COMMENT 'JSON metadata',"
                            + "`create_time` datetime DEFAULT CURRENT_TIMESTAMP,"
                            + "`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,"
                            + "PRIMARY KEY (`id`),"
                            + "UNIQUE KEY `uk_company_category_key` (`company_id`, `category`, `vec_key`),"
                            + "KEY `idx_company_category` (`company_id`, `category`)"
                            + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='lobster vector store'");
        }
    }

    private static void applySqlFile(Connection conn, String relativePath) throws Exception {
        Path path = Paths.get(relativePath).normalize();
        if (!Files.exists(path)) {
            path = Paths.get("java", "sql", "master", "V20260623_02__lobster_master_word_tables.sql");
        }
        if (!Files.exists(path)) {
            System.out.println("[SKIP] " + relativePath);
            return;
        }
        System.out.println("[APPLY] " + path.toAbsolutePath());
        String sql = Files.readString(path, StandardCharsets.UTF_8);
        StringBuilder batch = new StringBuilder();
        for (String line : sql.split("\\R")) {
            String trimmed = line.trim();
            if (trimmed.startsWith("--")) {
                continue;
            }
            batch.append(line).append('\n');
        }
        try (Statement st = conn.createStatement()) {
            st.execute(batch.toString());
        }
    }

    private static boolean tableExists(Connection conn, String table) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

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

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