import java.nio.file.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;

/**
 * Reproduces GET /api/merchant/onboarding/status against the development database, down to the
 * statements the service actually issues. Read-only.
 *
 * <p>Also compares the development schema with the additive columns
 * {@link com.talentcard.server.business.merchant.MerchantOnboardingSchemaInitializer} is supposed to
 * add at startup: that initializer deliberately swallows "could not take the lock" failures, so a
 * column can be silently missing without any request having failed yet.
 *
 * usage: java -cp <mysql-connector-j.jar>:. ProbeOnboardingProgress.java [jdbcUrl] [initializerSource]
 */
public class ProbeOnboardingProgress {
  public static void main(String[] args) throws Exception {
    String url = args.length > 0
        ? args[0]
        : "jdbc:mysql://127.0.0.1:8134/talent_card?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false";
    String source = args.length > 1
        ? args[1]
        : "../../talent-card-server/src/main/java/com/talentcard/server/business/merchant/MerchantOnboardingSchemaInitializer.java";
    String database = url.split("\\?")[0].substring(url.split("\\?")[0].lastIndexOf('/') + 1);

    boolean red = false;
    try (Connection connection = DriverManager.getConnection(url, "root", "123456")) {
      connection.setReadOnly(true);
      for (String table : new String[] { "merchant_onboarding_progress", "merchant_onboarding_attempt" }) {
        boolean exists = exists(connection, database, table);
        System.out.println("table " + table + " exists = " + exists);
      }
      System.out.println();

      // The two statements MerchantOnboardingProgressService issues on the status path.
      red |= run(connection, "select * from merchant_onboarding_progress where application_id=?", "view()", 1L);
      red |= run(connection,
          "select * from merchant_onboarding_attempt where application_id=? and step='delay' order by id desc limit 1", "delayAttempt()", 1L);
      // The statements MerchantSettlementService issues before it reaches the progress view.
      red |= run(connection,
          "select * from merchant_onboarding_application where merchant_id=? and store_id=? and initial_bank_card_id is not null order by id desc limit 1", "onboardingStatus()", -1L, -1L);
      red |= run(connection, "select id from merchant_store where id=? and merchant_id=? and status=1 and deleted=0", "store()", -1L, -1L);
      System.out.println();

      for (String table : new String[] { "merchant_onboarding_application", "merchant_bank_card" }) {
        List<String> declared = declaredColumns(source, table);
        List<String> missing = new ArrayList<>();
        for (String column : declared) if (!hasColumn(connection, database, table, column)) missing.add(column);
        System.out.println(table + ": initializer declares " + declared.size() + " columns, missing in this database = "
            + (missing.isEmpty() ? "none" : missing));
      }
    }

    System.out.println();
    System.out.println(red ? "RED: the endpoint's read path does not run against this database" : "GREEN: every statement on the endpoint's read path ran");
    System.exit(red ? 1 : 0);
  }

  private static boolean run(Connection connection, String sql, String label, Object... arguments) {
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      for (int index = 0; index < arguments.length; index++) statement.setObject(index + 1, arguments[index]);
      try (ResultSet rows = statement.executeQuery()) {
        int count = 0;
        while (rows.next()) count++;
        System.out.println("ok   " + label + " -> rows=" + count);
        return false;
      }
    } catch (SQLException exception) {
      System.out.println("FAIL " + label + " -> " + exception.getMessage());
      return true;
    }
  }

  /** Column names the initializer adds to one table, in declaration order. */
  private static List<String> declaredColumns(String source, String table) throws Exception {
    String map = table.equals("merchant_bank_card") ? "bankCardColumns" : "columns";
    Matcher matcher = Pattern.compile("\\b" + map + "\\.put\\(\"([a-z_]+)\"").matcher(Files.readString(Path.of(source)));
    List<String> names = new ArrayList<>();
    while (matcher.find()) names.add(matcher.group(1));
    return names;
  }

  private static boolean exists(Connection connection, String database, String table) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement(
        "select count(*) from information_schema.tables where table_schema=? and table_name=?")) {
      statement.setString(1, database);
      statement.setString(2, table);
      try (ResultSet rows = statement.executeQuery()) {
        return rows.next() && rows.getInt(1) > 0;
      }
    }
  }

  private static boolean hasColumn(Connection connection, String database, String table, String column) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement(
        "select count(*) from information_schema.columns where table_schema=? and table_name=? and column_name=?")) {
      statement.setString(1, database);
      statement.setString(2, table);
      statement.setString(3, column);
      try (ResultSet rows = statement.executeQuery()) {
        return rows.next() && rows.getInt(1) > 0;
      }
    }
  }
}