package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.config.DdsProperties;
import com.cloudhandson.ddsbackoffice.domain.DdsValidationFixture;
import com.cloudhandson.ddsbackoffice.domain.DdsValidationRunResult;
import com.cloudhandson.ddsbackoffice.domain.DdsPublishedProtectionSpec;
import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.util.Base64;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.UUID;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

/** Runs deterministic document-visibility fixtures without retaining bearer secrets. */
@Service
public class DdsProtectionValidationService {

  private static final SecureRandom RANDOM = new SecureRandom();

  private final DdsProperties properties;
  private final DdsProtectionEvidenceStore evidenceStore;
  private final JdbcTemplate jdbcTemplate;

  public DdsProtectionValidationService(
      DdsProperties properties,
      DdsProtectionEvidenceStore evidenceStore,
      JdbcTemplate jdbcTemplate
  ) {
    this.properties = properties;
    this.evidenceStore = evidenceStore;
    this.jdbcTemplate = jdbcTemplate;
  }

  public DdsValidationRunResult runServiceFixtures() {
    List<DdsValidationFixture> fixtures = evidenceStore.activeFixtures("SERVICE");
    DdsValidationRunResult result = run(fixtures, "SERVICE");
    if (result.passed()) {
      evidenceStore.recordPublished(new DdsPublishedProtectionSpec(
          "SERVICE", "service", DdsProtectionFingerprint.service(properties), java.time.Instant.now()),
          "DDS fixture validation", "토큰 서비스 경로 기준 확인");
    }
    return result;
  }

  public DdsValidationRunResult runDirectFixtures() {
    List<DdsValidationFixture> fixtures = evidenceStore.activeFixtures("DIRECT");
    return run(fixtures, "DIRECT");
  }

  private DdsValidationRunResult run(List<DdsValidationFixture> fixtures, String path) {
    if (fixtures.isEmpty()) {
      return new DdsValidationRunResult(path, 0, 0, 0,
          "활성 검증 fixture가 없습니다. DDS 검증 데이터 설정을 확인하세요.");
    }
    String runId = UUID.randomUUID().toString();
    int passed = 0;
    for (DdsValidationFixture fixture : fixtures) {
      FixtureEvaluation evaluation = "SERVICE".equals(path)
          ? evaluateService(fixture) : evaluateDirect(fixture);
      boolean actualForEvidence = evaluation.completed()
          ? evaluation.visible() : !fixture.expectedVisible();
      evidenceStore.recordValidation(
          runId, fixture.fixtureId(), path, fixture.subjectKey(), fixture.expectedVisible(),
          actualForEvidence, fingerprint(path, fixture.subjectKey())
      );
      if (evaluation.completed() && evaluation.visible() == fixture.expectedVisible()) {
        passed++;
      }
    }
    int failed = fixtures.size() - passed;
    return new DdsValidationRunResult(
        path, fixtures.size(), passed, failed,
        failed == 0
            ? fixtures.size() + "개 결정적 DDS 검증을 통과했습니다. 임시 Bearer 토큰은 즉시 회수했습니다."
            : failed + "개 DDS 검증이 기대 결과와 달랐습니다. 보호 상태에서 실패 근거를 확인하세요."
    );
  }

  private FixtureEvaluation evaluateService(DdsValidationFixture fixture) {
    if (!properties.token().configured() || properties.dbUrl().isBlank()) {
      return FixtureEvaluation.failed();
    }
    String temporaryToken = temporaryToken();
    String prefix = "ddsv_" + UUID.randomUUID().toString().replace("-", "").substring(0, 20);
    try {
      issueTemporaryToken(prefix, temporaryToken, fixture.applicationUserId());
      Properties connectionProperties = connectionProperties(
          properties.token().username(), properties.token().password());
      try (Connection connection = DriverManager.getConnection(properties.dbUrl(), connectionProperties)) {
        connection.setReadOnly(true);
        try {
          callContext(connection, "BEGIN ADMIN.CB_AGENT_CTX_PKG.SET_USER_BY_BEARER(?); END;", temporaryToken);
          return FixtureEvaluation.completed(documentVisible(connection, fixture.documentId()));
        } finally {
          clearContext(connection);
        }
      }
    } catch (SQLException | DataAccessException exception) {
      return FixtureEvaluation.failed();
    } finally {
      revokeTemporaryToken(prefix);
    }
  }

  private FixtureEvaluation evaluateDirect(DdsValidationFixture fixture) {
    DdsProperties.User user = properties.users().get(fixture.subjectKey().toLowerCase(Locale.ROOT));
    if (user == null || !user.configured() || properties.dbUrl().isBlank()) {
      return FixtureEvaluation.failed();
    }
    try (Connection connection = DriverManager.getConnection(
        properties.dbUrl(), connectionProperties(user.username(), user.password()))) {
      connection.setReadOnly(true);
      return FixtureEvaluation.completed(documentVisible(connection, fixture.documentId()));
    } catch (SQLException exception) {
      // The expected DDS default-deny signal is an inaccessible protected object, not a generic error.
      if (!fixture.expectedVisible() && hasOracleCode(exception, 942)) {
        return FixtureEvaluation.completed(false);
      }
      return FixtureEvaluation.failed();
    }
  }

  private boolean documentVisible(Connection connection, String documentId) throws SQLException {
    String sql = "SELECT COUNT(*) FROM " + properties.vectorObject() + " WHERE document_id = ?";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setQueryTimeout(timeoutSeconds(properties.queryTimeout()));
      statement.setString(1, documentId);
      try (ResultSet resultSet = statement.executeQuery()) {
        return resultSet.next() && resultSet.getLong(1) > 0;
      }
    }
  }

  private void issueTemporaryToken(String prefix, String token, long userId) {
    jdbcTemplate.update("""
        INSERT INTO cb_agent_bearer_key (
          key_id, user_id, key_prefix, key_hash, issued_at, expires_at, revoked_at, active, description
        ) VALUES (
          cb_agent_bearer_key_seq.NEXTVAL, ?, ?, STANDARD_HASH(?, 'SHA256'),
          SYSTIMESTAMP, SYSTIMESTAMP + INTERVAL '5' MINUTE, NULL, 'Y', 'DDS fixture validation'
        )
        """, userId, prefix, token);
  }

  private void revokeTemporaryToken(String prefix) {
    try {
      jdbcTemplate.update("""
          UPDATE cb_agent_bearer_key
          SET active = 'N', revoked_at = SYSTIMESTAMP
          WHERE key_prefix = ? AND active = 'Y'
          """, prefix);
    } catch (DataAccessException ignored) {
      // The original validation failure is more useful than a best-effort cleanup failure.
    }
  }

  private void callContext(Connection connection, String sql, String token) throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setQueryTimeout(timeoutSeconds(properties.queryTimeout()));
      statement.setString(1, token);
      statement.execute();
    }
  }

  private void clearContext(Connection connection) {
    try (PreparedStatement statement = connection.prepareStatement("BEGIN ADMIN.CB_AGENT_CTX_PKG.CLEAR_USER; END;")) {
      statement.setQueryTimeout(timeoutSeconds(properties.queryTimeout()));
      statement.execute();
    } catch (SQLException ignored) {
      // Connections are closed after validation. Never hide the validation outcome for cleanup only.
    }
  }

  private Properties connectionProperties(String username, String password) {
    int timeoutSeconds = timeoutSeconds(properties.queryTimeout());
    Properties values = new Properties();
    values.setProperty("user", jdbcUsername(username));
    values.setProperty("password", password);
    values.setProperty("oracle.net.CONNECT_TIMEOUT", String.valueOf(timeoutSeconds * 1000));
    values.setProperty("oracle.jdbc.ReadTimeout", String.valueOf(timeoutSeconds * 1000));
    return values;
  }

  private static String fingerprint(String path, String subject) {
    return path + ":" + subject;
  }

  private static String temporaryToken() {
    byte[] bytes = new byte[32];
    RANDOM.nextBytes(bytes);
    return Base64.getUrlEncoder().withoutPadding().encodeToString(bytes);
  }

  private static int timeoutSeconds(Duration duration) {
    return Math.max(1, (int) Math.ceil(duration.toMillis() / 1000.0));
  }

  private static String jdbcUsername(String username) {
    String normalized = username == null ? "" : username.trim();
    if (normalized.length() >= 2 && normalized.startsWith("\"") && normalized.endsWith("\"")) {
      return normalized;
    }
    if (!normalized.isEmpty() && normalized.equals(normalized.toLowerCase(Locale.ROOT))) {
      return "\"" + normalized.replace("\"", "\"\"") + "\"";
    }
    return normalized;
  }

  private static boolean hasOracleCode(SQLException exception, int expected) {
    SQLException current = exception;
    while (current != null) {
      if (Math.abs(current.getErrorCode()) == expected) {
        return true;
      }
      current = current.getNextException();
    }
    return false;
  }

  private record FixtureEvaluation(boolean completed, boolean visible) {
    static FixtureEvaluation completed(boolean visible) {
      return new FixtureEvaluation(true, visible);
    }

    static FixtureEvaluation failed() {
      return new FixtureEvaluation(false, false);
    }
  }
}
