package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.config.DdsProperties;
import com.cloudhandson.ddsbackoffice.domain.DdsCustomerRow;
import com.cloudhandson.ddsbackoffice.domain.DdsQueryResult;
import com.cloudhandson.ddsbackoffice.domain.DdsSourceOption;
import com.cloudhandson.ddsbackoffice.domain.DdsUserOption;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Duration;
import java.util.List;
import java.util.Locale;
import java.util.Objects;
import java.util.Properties;
import java.util.regex.Pattern;
import org.springframework.stereotype.Service;

@Service
public class DdsQueryService {

  private static final Pattern SAFE_SOURCE = Pattern.compile("PG|MY");
  private final DdsProperties properties;

  public DdsQueryService(DdsProperties properties) {
    this.properties = properties;
  }

  public List<DdsUserOption> users() {
    return properties.users().entrySet().stream()
        .map(entry -> {
          var user = entry.getValue();
          return new DdsUserOption(
              entry.getKey(),
              valueOrDefault(user.label(), entry.getKey()),
              valueOrDefault(user.username(), "미설정"),
              valueOrDefault(user.description(), "DDS 보안 사용자"),
              user.configured()
          );
        })
        .toList();
  }

  public List<DdsSourceOption> sources() {
    return List.of(
        new DdsSourceOption(
            "PG", "PG 데이터셋", properties.pgObject(),
            "DDS 전용 로컬 PG 데이터셋에서 허용된 행만 반환합니다."
        ),
        new DdsSourceOption(
            "MY", "MY 데이터셋", properties.myObject(),
            "DDS 전용 로컬 MY 데이터셋에서 허용된 행만 반환합니다."
        )
    );
  }

  public String vectorObject() {
    return properties.vectorObject();
  }

  public DdsQueryResult query(String userKey, String sourceKey, String searchText, int requestedLimit) {
    var normalizedUserKey = normalizeUserKey(userKey);
    var normalizedSourceKey = normalizeSourceKey(sourceKey);
    var user = properties.users().get(normalizedUserKey);
    var source = sources().stream()
        .filter(option -> option.key().equals(normalizedSourceKey))
        .findFirst()
        .orElseThrow(() -> new IllegalArgumentException("지원하지 않는 DDS 데이터 소스입니다."));
    var userLabel = user == null ? normalizedUserKey : valueOrDefault(user.label(), normalizedUserKey);

    if (user == null) {
      return failure(normalizedUserKey, normalizedSourceKey, userLabel, source,
          "사용자를 찾을 수 없습니다.", "선택한 DDS END USER가 설정에 없습니다.", null);
    }
    if (properties.dbUrl().isBlank()) {
      return failure(normalizedUserKey, normalizedSourceKey, userLabel, source,
          "DB 접속 정보가 없습니다.", "DDS_BACKOFFICE_DB_URL 또는 BACKOFFICE_DB_URL을 설정하세요.", null);
    }
    if (!user.configured()) {
      return failure(normalizedUserKey, normalizedSourceKey, userLabel, source,
          "DDS 사용자 자격증명이 설정되지 않았습니다.",
          "이 인스턴스의 환경 변수에 해당 END USER 비밀번호를 설정하세요.", null);
    }

    var limit = Math.max(1, Math.min(requestedLimit, 100));
    var search = searchText == null ? "" : searchText.trim();
    var timeoutSeconds = timeoutSeconds(properties.queryTimeout());
    var connectionProperties = new Properties();
    connectionProperties.setProperty("user", jdbcUsername(user.username()));
    connectionProperties.setProperty("password", user.password());
    connectionProperties.setProperty("oracle.net.CONNECT_TIMEOUT", String.valueOf(timeoutSeconds * 1000));
    connectionProperties.setProperty("oracle.jdbc.ReadTimeout", String.valueOf(timeoutSeconds * 1000));
    try (Connection connection = DriverManager.getConnection(properties.dbUrl(), connectionProperties)) {
      connection.setReadOnly(true);
      var sessionUser = readSingleValue(connection,
          "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual", timeoutSeconds);
      var endUser = readSingleValue(connection,
          "SELECT ORA_END_USER_CONTEXT.username FROM dual", timeoutSeconds);

      var sql = "SELECT customer_id, full_name, email, signup_date, region FROM "
          + source.objectName()
          + (search.isBlank() ? "" : " WHERE UPPER(full_name) LIKE ?")
          + " ORDER BY customer_id FETCH FIRST " + limit + " ROWS ONLY";
      try (PreparedStatement statement = connection.prepareStatement(sql)) {
        statement.setQueryTimeout(timeoutSeconds);
        if (!search.isBlank()) {
          statement.setString(1, "%" + search.toUpperCase(Locale.ROOT) + "%");
        }
        try (ResultSet resultSet = statement.executeQuery()) {
          var rows = new java.util.ArrayList<DdsCustomerRow>();
          while (resultSet.next()) {
            Timestamp timestamp = resultSet.getTimestamp("signup_date");
            rows.add(new DdsCustomerRow(
                resultSet.getLong("customer_id"),
                resultSet.getString("full_name"),
                resultSet.getString("email"),
                timestamp == null ? null : timestamp.toLocalDateTime(),
                resultSet.getString("region")
            ));
          }
          return new DdsQueryResult(
              normalizedUserKey, normalizedSourceKey, userLabel, source.label(), source.objectName(),
              true, "DDS 조회가 완료되었습니다.",
              rows.isEmpty() ? "조건에 맞는 행이 없습니다." : rows.size() + "개 행이 DDS 정책을 통과했습니다.",
              sessionUser, endUser, null, rows
          );
        }
      }
    } catch (SQLException e) {
      return failure(normalizedUserKey, normalizedSourceKey, userLabel, source,
          classifyTitle(e), classifyMessage(e), oracleCode(e));
    }
  }

  private DdsQueryResult failure(
      String userKey,
      String sourceKey,
      String userLabel,
      DdsSourceOption source,
      String title,
      String message,
      Integer oracleCode
  ) {
    return new DdsQueryResult(
        userKey, sourceKey, userLabel, source.label(), source.objectName(),
        false, title, message, null, null, oracleCode, List.of()
    );
  }

  private String readSingleValue(Connection connection, String sql, int timeoutSeconds)
      throws SQLException {
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setQueryTimeout(timeoutSeconds);
      try (ResultSet resultSet = statement.executeQuery()) {
        return resultSet.next() ? resultSet.getString(1) : null;
      }
    }
  }

  private static String normalizeUserKey(String value) {
    return value == null ? "" : value.trim().toLowerCase(Locale.ROOT);
  }

  private static String normalizeSourceKey(String value) {
    var normalized = value == null ? "" : value.trim().toUpperCase(Locale.ROOT);
    if (!SAFE_SOURCE.matcher(normalized).matches()) {
      throw new IllegalArgumentException("지원하지 않는 DDS 데이터 소스입니다.");
    }
    return normalized;
  }

  private static String jdbcUsername(String username) {
    var normalized = username == null ? "" : username.trim();
    if (normalized.length() >= 2 && normalized.startsWith("\"") && normalized.endsWith("\"")) {
      return normalized;
    }
    // The standalone SQL creates quoted lower-case END USER names. Oracle's
    // JDBC driver uppercases an unquoted username, so preserve that identity.
    if (!normalized.isEmpty() && normalized.equals(normalized.toLowerCase(Locale.ROOT))) {
      return "\"" + normalized.replace("\"", "\"\"") + "\"";
    }
    return normalized;
  }

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

  private static String valueOrDefault(String value, String fallback) {
    return value == null || value.isBlank() ? fallback : value;
  }

  private static Integer oracleCode(SQLException exception) {
    SQLException current = exception;
    while (current != null) {
      if (current.getErrorCode() != 0) {
        return Math.abs(current.getErrorCode());
      }
      current = current.getNextException();
    }
    return null;
  }

  private static String flattenedMessage(SQLException exception) {
    var messages = new StringBuilder();
    SQLException current = exception;
    while (current != null) {
      if (messages.length() > 0) {
        messages.append("; ");
      }
      messages.append(Objects.toString(current.getMessage(), ""));
      current = current.getNextException();
    }
    return messages.toString().replaceAll("\\s+", " ").trim();
  }

  private static String classifyTitle(SQLException exception) {
    var message = flattenedMessage(exception).toUpperCase(Locale.ROOT);
    if (message.contains("ORA-00942")) {
      return "이 DDS 사용자에게 데이터가 허용되지 않았습니다.";
    }
    if (message.contains("ORA-01017")) {
      return "DDS 사용자 인증에 실패했습니다.";
    }
    if (message.contains("ORA-12154") || message.contains("ORA-12514")
        || message.contains("IO ERROR") || message.contains("CONNECTION")) {
      return "DDS 데이터베이스에 연결할 수 없습니다.";
    }
    return "DDS 조회 중 데이터베이스 오류가 발생했습니다.";
  }

  private static String classifyMessage(SQLException exception) {
    var message = flattenedMessage(exception);
    var upper = message.toUpperCase(Locale.ROOT);
    if (upper.contains("ORA-00942")) {
      return "해당 END USER에 연결된 DATA ROLE의 DATA GRANT가 없어 보호 객체가 보이지 않습니다. DDS의 default deny 결과입니다.";
    }
    if (upper.contains("ORA-01017")) {
      return "서버에 등록된 DDS END USER 비밀번호와 일치하지 않습니다.";
    }
    if (upper.contains("ORA-12154") || upper.contains("ORA-12514")) {
      return "TNS 별칭 또는 서비스 이름을 확인하세요.";
    }
    return "DB 접속 정보와 DDS DATA GRANT 설정을 확인하세요. 상세 코드는 운영 로그에서 확인합니다.";
  }
}
