package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.config.DdsMcpIamProperties;
import com.cloudhandson.ddsbackoffice.domain.DdsMcpEndUserPrincipal;
import com.cloudhandson.vpdbackoffice.service.AppException;
import java.lang.reflect.Method;
import java.lang.reflect.InvocationTargetException;
import java.nio.charset.StandardCharsets;
import java.security.GeneralSecurityException;
import java.util.Base64;
import java.util.concurrent.Executor;
import javax.crypto.Mac;
import javax.crypto.spec.SecretKeySpec;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

/**
 * The only DDS MCP path that may run protected SQL. It attaches the local
 * END USER context, runs one work unit, and clears the context before the
 * pooled connection is released.
 */
@Service
public class DdsMcpContextExecutor {

  private static final Logger log = LoggerFactory.getLogger(DdsMcpContextExecutor.class);
  private static final Executor DIRECT_EXECUTOR = Runnable::run;
  private final DataSource dataSource;
  private final DdsMcpIamProperties iamProperties;
  private final DdsMcpDatabaseAccessTokenProvider tokenProvider;

  public DdsMcpContextExecutor(
      DataSource dataSource,
      DdsMcpIamProperties iamProperties,
      DdsMcpDatabaseAccessTokenProvider tokenProvider
  ) {
    this.dataSource = dataSource;
    this.iamProperties = iamProperties;
    this.tokenProvider = tokenProvider;
  }

  public <T> T withContext(DdsMcpEndUserPrincipal principal, SqlWork<T> work) {
    if (principal == null || work == null) {
      throw unavailable("DDS 실행 주체가 없습니다.");
    }
    String databaseAccessToken = tokenProvider.accessToken();
    java.sql.Connection connection = null;
    Object oracleConnection = null;
    boolean attachAttempted = false;
    boolean clearFailed = false;
    try {
      connection = dataSource.getConnection();
      connection.setNetworkTimeout(DIRECT_EXECUTOR, networkTimeoutMillis());
      oracleConnection = unwrapOracleConnection(connection);
      attachAttempted = true;
      log.info("DDS context attach started for application user {}", principal.applicationUserId());
      attach(oracleConnection, databaseAccessToken, principal.endUserName(), lookupKey(principal));
      log.info("DDS context attach completed for application user {}", principal.applicationUserId());
      return work.execute(connection);
    } catch (AppException exception) {
      throw exception;
    } catch (Exception exception) {
      log.warn("DDS END USER Context attachment failed: {}", failureCode(exception));
      throw unavailable("DDS END USER Context를 연결할 수 없습니다.");
    } finally {
      if (attachAttempted && oracleConnection != null) {
        try {
          log.info("DDS context clear started");
          clear(oracleConnection);
          log.info("DDS context clear completed");
        } catch (Exception ignored) {
          clearFailed = true;
        }
      }
      if (connection != null) {
        try {
          if (clearFailed) {
            connection.abort(Runnable::run);
          }
        } catch (Exception ignored) {
          // close below still runs; the pool may discard an aborted connection.
        }
        try {
          connection.close();
        } catch (Exception ignored) {
          // The request has already failed closed; do not expose JDBC details.
        }
      }
      if (clearFailed) {
        throw unavailable("DDS END USER Context를 해제할 수 없어 연결을 폐기했습니다.");
      }
    }
  }

  private Object unwrapOracleConnection(java.sql.Connection connection) throws Exception {
    Class<?> oracleConnectionType = Class.forName("oracle.jdbc.OracleConnection");
    return connection.unwrap(oracleConnectionType);
  }

  private void attach(Object oracleConnection, String databaseAccessToken, String endUserName, String lookupKey)
      throws Exception {
    Class<?> contextType = Class.forName("oracle.jdbc.EndUserSecurityContext");
    Object context = createLocalContext(contextType, databaseAccessToken, endUserName, lookupKey);
    Class<?> oracleConnectionType = Class.forName("oracle.jdbc.OracleConnection");
    oracleConnectionType.getMethod("setEndUserSecurityContext", contextType)
        .invoke(oracleConnection, context);
  }

  private Object createLocalContext(
      Class<?> contextType,
      String databaseAccessToken,
      String endUserName,
      String lookupKey
  ) throws Exception {
    // ojdbc 23.26 exposes createWithName(CharSequence, String, CharSequence).
    // Keep the documented createWithUsername fallback for compatible driver
    // releases whose public API uses that spelling.
    try {
      Method createWithName = contextType.getMethod(
          "createWithName", CharSequence.class, String.class, CharSequence.class);
      return createWithName.invoke(null, databaseAccessToken, endUserName, lookupKey);
    } catch (NoSuchMethodException ignored) {
      Method createWithUsername = contextType.getMethod(
          "createWithUsername", String.class, String.class, String.class);
      return createWithUsername.invoke(null, databaseAccessToken, endUserName, lookupKey);
    }
  }

  private void clear(Object oracleConnection) throws Exception {
    Class<?> oracleConnectionType = Class.forName("oracle.jdbc.OracleConnection");
    oracleConnectionType.getMethod("clearEndUserSecurityContext").invoke(oracleConnection);
  }

  private String lookupKey(DdsMcpEndUserPrincipal principal) {
    if (!"DDS_OCI_IAM_CLIENT_SECRET_DERIVED_V1".equals(principal.lookupKeyReference())) {
      throw unavailable("DDS END USER lookup key 참조가 허용되지 않습니다.");
    }
    try {
      Mac mac = Mac.getInstance("HmacSHA256");
      mac.init(new SecretKeySpec(iamProperties.clientSecret().getBytes(StandardCharsets.UTF_8), "HmacSHA256"));
      byte[] digest = mac.doFinal(("dds-mcp-local-end-user:" + principal.applicationUserId())
          .getBytes(StandardCharsets.UTF_8));
      return Base64.getUrlEncoder().withoutPadding().encodeToString(digest);
    } catch (GeneralSecurityException exception) {
      throw unavailable("DDS END USER lookup key를 만들 수 없습니다.");
    }
  }

  private int networkTimeoutMillis() {
    long configured = iamProperties.timeout().toMillis();
    return (int) Math.max(1_000L, Math.min(60_000L, configured));
  }

  private String failureCode(Exception exception) {
    Throwable cause = exception instanceof InvocationTargetException invocation && invocation.getCause() != null
        ? invocation.getCause() : exception;
    if (cause instanceof java.sql.SQLException sqlException) {
      return "sqlState=" + sqlException.getSQLState() + ", errorCode=" + sqlException.getErrorCode();
    }
    return cause.getClass().getSimpleName();
  }

  private AppException unavailable(String detail) {
    return new AppException("DDS_CONTEXT_UNAVAILABLE: " + detail);
  }

  @FunctionalInterface
  public interface SqlWork<T> {
    T execute(java.sql.Connection connection) throws Exception;
  }
}
