import java.sql.*;
import java.util.Random;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Test {

   private static final String URL = "jdbc:h2:mem:testdb;db_close_delay=-1;mv_store=true;rtrim=true;default_ignore_case=true;LOCK_TIMEOUT=1000";

   public static void main(String[] args) throws Exception {

      // Step 1: Create table
      try (Connection conn = DriverManager.getConnection(URL, "sa", "")) {
         conn.createStatement().execute(
                 "CREATE TABLE meta_connect (" +
                 "id BIGINT PRIMARY KEY, " +
                 "connect_usr INT, " +
                 "connect_name VARCHAR(255))"
         );
         System.out.println("Table created.");
      }

      int threadCount = 20000;
      Random random = new Random();
      ExecutorService executor = Executors.newFixedThreadPool(threadCount);
      CountDownLatch latch = new CountDownLatch(threadCount);

      for (int i = 0; i < threadCount; i++) {
         final int threadId = i;

         executor.submit(() -> {
            Connection conn = null;

            try {
               conn = DriverManager.getConnection(URL, "sa", "");
               conn.setAutoCommit(false);

               long newId = System.nanoTime();
               int connectUsr = 42; // same user for concurrency
               String name = "User-" + threadId;

               if (random.nextBoolean()) {
                  // 50% chance: MERGE (insert or update existing id)
                  PreparedStatement merge = conn.prepareStatement(
                          "MERGE INTO meta_connect (id, connect_usr, connect_name) KEY(id) VALUES (?, ?, ?)"
                  );
                  merge.setLong(1, newId); // new row
                  merge.setInt(2, connectUsr);
                  merge.setString(3, name);
                  merge.executeUpdate();
               } else {
                  // 50% chance: update an existing row if any
                  PreparedStatement select = conn.prepareStatement(
                          "SELECT id FROM meta_connect WHERE connect_usr = ? LIMIT 1"
                  );
                  select.setInt(1, connectUsr);
                  ResultSet rs = select.executeQuery();

                  if (rs.next()) {
                     long existingId = rs.getLong(1);
                     PreparedStatement update = conn.prepareStatement(
                             "UPDATE meta_connect SET connect_name = ? WHERE id = ?"
                     );
                     update.setString(1, name + "-upd");
                     update.setLong(2, existingId);
                     update.executeUpdate();
                  } else {
                     // fallback: insert new row
                     PreparedStatement insert = conn.prepareStatement(
                             "INSERT INTO meta_connect (id, connect_usr, connect_name) VALUES (?, ?, ?)"
                     );
                     insert.setLong(1, newId);
                     insert.setInt(2, connectUsr);
                     insert.setString(3, name);
                     insert.executeUpdate();
                  }
               }

               conn.commit();

            } catch (Exception e) {
               System.err.println("Thread " + threadId + " FAILED: " + e.getMessage());
               try {
                  if (conn != null) conn.rollback();
               } catch (Exception ignored) {}
            } finally {
               latch.countDown();
               try {
                  if (conn != null) conn.close();
               } catch (Exception ignored) {}
            }
         });
      }

      latch.await();
      executor.shutdown();

      System.out.println("\nAll threads finished.\n");

      // Step 3: Verify result
      try (Connection conn = DriverManager.getConnection(URL, "sa", "")) {
         ResultSet rs1 = conn.createStatement()
                             .executeQuery("SELECT COUNT(*) FROM meta_connect");
         rs1.next();
         int totalRows = rs1.getInt(1);

         System.out.println("Total rows: " + totalRows);
      }
   }
}