package com.cloudhandson.ddsbackoffice.service;

import com.cloudhandson.ddsbackoffice.config.DdsProperties;
import com.cloudhandson.ddsbackoffice.domain.DdsVectorSearchResult;
import com.cloudhandson.ddsbackoffice.domain.DdsSqlEvidence;
import com.cloudhandson.vpdbackoffice.domain.vector.VectorIngestCommand;
import com.cloudhandson.vpdbackoffice.domain.vector.VectorIngestResult;
import com.cloudhandson.vpdbackoffice.domain.vector.VectorKnowledgeSummary;
import com.cloudhandson.vpdbackoffice.service.AppException;
import com.cloudhandson.vpdbackoffice.service.OpenAiCompatibleClient;
import com.cloudhandson.vpdbackoffice.service.VectorKnowledgeService;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import java.util.UUID;
import java.util.regex.Pattern;
import org.springframework.stereotype.Service;

/**
 * DDS version of the common knowledge flow.
 *
 * Ingestion still writes the shared chunk/tag store. Direct comparison search
 * opens a DDS END USER connection and lets DATA ROLE/DATA GRANT decide which
 * chunks are visible. Token search uses one configured technical DDS user,
 * initializes CB_AGENT_CTX from the bearer token, and relies on the
 * object-level DATA GRANT predicate to evaluate the common CB_* permissions.
 */
@Service
public class DdsVectorKnowledgeService {

  private static final String DEMO_MODE = "DEMO";
  private static final String AI_MODE = "AI";
  private static final Pattern USER_KEY = Pattern.compile("[a-z0-9_-]{1,40}");

  private final VectorKnowledgeService commonVectorService;
  private final OpenAiCompatibleClient embeddingClient;
  private final ObjectMapper objectMapper;
  private final DdsProperties properties;
  private final DdsSqlEvidenceHistory evidenceHistory;

  public DdsVectorKnowledgeService(
      VectorKnowledgeService commonVectorService,
      OpenAiCompatibleClient embeddingClient,
      ObjectMapper objectMapper,
      DdsProperties properties,
      DdsSqlEvidenceHistory evidenceHistory
  ) {
    this.commonVectorService = commonVectorService;
    this.embeddingClient = embeddingClient;
    this.objectMapper = objectMapper;
    this.properties = properties;
    this.evidenceHistory = evidenceHistory;
  }

  public VectorKnowledgeSummary summary() {
    return commonVectorService.summary();
  }

  public boolean aiEmbeddingConfigured() {
    return commonVectorService.aiEmbeddingConfigured();
  }

  public boolean tokenSearchAvailable() {
    return !properties.dbUrl().isBlank() && properties.token() != null && properties.token().configured();
  }

  public VectorIngestResult ingest(VectorIngestCommand command) {
    return commonVectorService.ingest(command);
  }

  public DdsVectorSearchResult search(String userKey, String query, int requestedLimit, String embeddingMode) {
    String normalizedUserKey = normalizeUserKey(userKey);
    var user = properties.users().get(normalizedUserKey);
    String userLabel = user == null || user.label() == null || user.label().isBlank()
        ? normalizedUserKey : user.label();
    String normalizedQuery = required(query, "검색 질문");
    String mode = normalizeMode(embeddingMode);

    if (user == null) {
      return failure(normalizedUserKey, userLabel, normalizedQuery, mode,
          "DDS END USER를 찾을 수 없습니다.", "설정에 등록된 DDS 보안 주체만 선택할 수 있습니다.", null);
    }
    if (properties.dbUrl().isBlank()) {
      return failure(normalizedUserKey, userLabel, normalizedQuery, mode,
          "DDS 데이터베이스 연결 정보가 없습니다.",
          "DDS_BACKOFFICE_DB_URL 또는 BACKOFFICE_DB_URL을 설정하세요.", null);
    }
    if (!user.configured()) {
      return failure(normalizedUserKey, userLabel, normalizedQuery, mode,
          "DDS END USER 자격증명이 설정되지 않았습니다.",
          "이 인스턴스의 환경 변수에 해당 END USER 비밀번호를 설정하세요.", null);
    }

    int limit = Math.max(1, Math.min(requestedLimit, 100));
    int timeoutSeconds = timeoutSeconds(properties.queryTimeout());
    String vectorJson = json(embed(normalizedQuery, mode));
    Properties 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));

    String requestId = UUID.randomUUID().toString();
    String marker = "DDS_EVIDENCE:" + requestId;
    String sql = "SELECT /* " + marker + " */ chunk_id, document_id, chunk_no, title, chunk_text, source_uri, tech_tag, score "
        + "FROM (SELECT d.chunk_id, d.document_id, d.chunk_no, d.title, d.chunk_text, d.source_uri, "
        + "d.tech_tag, VECTOR_DISTANCE(d.embedding, TO_VECTOR(?), COSINE) AS score "
        + "FROM " + properties.vectorObject() + " d "
        + "WHERE d.embedding IS NOT NULL ORDER BY score) ranked_chunks WHERE ROWNUM <= ?";

    try (Connection connection = java.sql.DriverManager.getConnection(properties.dbUrl(), connectionProperties)) {
      connection.setReadOnly(true);
      String sessionUser = readSingleValue(connection,
          "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual", timeoutSeconds);
      String endUser = readSingleValue(connection,
          "SELECT ORA_END_USER_CONTEXT.username FROM dual", timeoutSeconds);
      try (PreparedStatement statement = connection.prepareStatement(sql)) {
        statement.setQueryTimeout(timeoutSeconds);
        statement.setString(1, vectorJson);
        statement.setInt(2, limit);
        List<Map<String, Object>> rows = new ArrayList<>();
        try (ResultSet resultSet = statement.executeQuery()) {
          while (resultSet.next()) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put("CHUNK_ID", resultSet.getLong("chunk_id"));
            row.put("DOCUMENT_ID", resultSet.getString("document_id"));
            row.put("CHUNK_NO", resultSet.getInt("chunk_no"));
            row.put("TITLE", resultSet.getString("title"));
            row.put("CHUNK_TEXT", resultSet.getString("chunk_text"));
            row.put("SOURCE_URI", resultSet.getString("source_uri"));
            row.put("TECH_TAG", resultSet.getString("tech_tag"));
            row.put("SCORE", resultSet.getObject("score"));
            rows.add(row);
          }
        }
        String message = rows.isEmpty()
            ? "DDS DATA GRANT를 통과한 검색 단위가 없습니다."
            : rows.size() + "개 검색 단위가 DDS DATA GRANT를 통과했습니다.";
        DdsSqlEvidence evidence = collectEvidence(connection, marker, sql, timeoutSeconds);
        evidenceHistory.record(evidence);
        return new DdsVectorSearchResult(
            normalizedUserKey, userLabel, properties.vectorObject(), normalizedQuery, mode,
            embeddingModel(mode), true, "DDS 권한으로 검색했습니다.", message,
            sessionUser, endUser, null, rows, evidence);
      }
    } catch (SQLException exception) {
      return failure(normalizedUserKey, userLabel, normalizedQuery, mode,
          classifyTitle(exception), classifyMessage(exception), oracleCode(exception));
    }
  }

  /**
   * Search through the token-driven DDS path.
   *
   * The technical DDS end user is fixed in configuration. The bearer token is
   * resolved by the shared CB_AGENT_CTX package on that same connection; the
   * object-level DATA GRANT then evaluates the common CB_* permission model.
   */
  public DdsVectorSearchResult searchByToken(
      String bearerToken, String query, int requestedLimit, String embeddingMode) {
    String normalizedToken = required(bearerToken, "Bearer 토큰");
    if (normalizedToken.length() > 4096) {
      throw new AppException("Bearer 토큰 길이가 허용 범위를 초과했습니다.");
    }
    String normalizedQuery = required(query, "검색 질문");
    String mode = normalizeMode(embeddingMode);
    var tokenUser = properties.token();
    String userLabel = "토큰 기반 업무 사용자";

    if (tokenUser == null || !tokenUser.configured()) {
      return failure("token", userLabel, normalizedQuery, mode,
          "DDS 토큰 기술 사용자가 설정되지 않았습니다.",
          "DDS_BACKOFFICE_TOKEN_USERNAME/PASSWORD와 34번 DDS 설정을 확인하세요.", null);
    }
    if (properties.dbUrl().isBlank()) {
      return failure("token", userLabel, normalizedQuery, mode,
          "DDS 데이터베이스 연결 정보가 없습니다.",
          "DDS_BACKOFFICE_DB_URL 또는 BACKOFFICE_DB_URL을 설정하세요.", null);
    }

    int limit = Math.max(1, Math.min(requestedLimit, 100));
    int timeoutSeconds = timeoutSeconds(properties.queryTimeout());
    String vectorJson = json(embed(normalizedQuery, mode));
    Properties connectionProperties = new Properties();
    connectionProperties.setProperty("user", jdbcUsername(tokenUser.username()));
    connectionProperties.setProperty("password", tokenUser.password());
    connectionProperties.setProperty("oracle.net.CONNECT_TIMEOUT", String.valueOf(timeoutSeconds * 1000));
    connectionProperties.setProperty("oracle.jdbc.ReadTimeout", String.valueOf(timeoutSeconds * 1000));

    String requestId = UUID.randomUUID().toString();
    String marker = "DDS_EVIDENCE:" + requestId;
    String sql = "SELECT /* " + marker + " */ chunk_id, document_id, chunk_no, title, chunk_text, source_uri, tech_tag, score "
        + "FROM (SELECT d.chunk_id, d.document_id, d.chunk_no, d.title, d.chunk_text, d.source_uri, "
        + "d.tech_tag, VECTOR_DISTANCE(d.embedding, TO_VECTOR(?), COSINE) AS score "
        + "FROM " + properties.vectorObject() + " d "
        + "WHERE d.embedding IS NOT NULL ORDER BY score) ranked_chunks WHERE ROWNUM <= ?";

    try (Connection connection = java.sql.DriverManager.getConnection(properties.dbUrl(), connectionProperties)) {
      connection.setReadOnly(true);
      try (PreparedStatement context = connection.prepareStatement(
          "BEGIN ADMIN.CB_AGENT_CTX_PKG.SET_USER_BY_BEARER(?); END;")) {
        context.setString(1, normalizedToken);
        context.execute();
      }
      try (PreparedStatement identifier = connection.prepareStatement(
          "BEGIN DBMS_SESSION.SET_IDENTIFIER(?); END;")) {
        identifier.setString(1, marker);
        identifier.execute();
      }
      String sessionUser = readSingleValue(connection,
          "SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM dual", timeoutSeconds);
      String endUser = readSingleValue(connection,
          "SELECT ORA_END_USER_CONTEXT.username FROM dual", timeoutSeconds);
      String resolvedAppUser = readSingleValue(connection,
          "SELECT user_name FROM ADMIN.CB_APP_USER "
              + "WHERE user_id = TO_NUMBER(SYS_CONTEXT('CB_AGENT_CTX', 'USER_ID'))",
          timeoutSeconds);
      try (PreparedStatement statement = connection.prepareStatement(sql)) {
        statement.setQueryTimeout(timeoutSeconds);
        statement.setString(1, vectorJson);
        statement.setInt(2, limit);
        List<Map<String, Object>> rows = new ArrayList<>();
        try (ResultSet resultSet = statement.executeQuery()) {
          while (resultSet.next()) {
            Map<String, Object> row = new LinkedHashMap<>();
            row.put("CHUNK_ID", resultSet.getLong("chunk_id"));
            row.put("DOCUMENT_ID", resultSet.getString("document_id"));
            row.put("CHUNK_NO", resultSet.getInt("chunk_no"));
            row.put("TITLE", resultSet.getString("title"));
            row.put("CHUNK_TEXT", resultSet.getString("chunk_text"));
            row.put("SOURCE_URI", resultSet.getString("source_uri"));
            row.put("TECH_TAG", resultSet.getString("tech_tag"));
            row.put("SCORE", resultSet.getObject("score"));
            rows.add(row);
          }
        }
        String message = rows.isEmpty()
            ? "토큰으로 식별된 업무 사용자의 권한을 통과한 검색 단위가 없습니다."
            : rows.size() + "개 검색 단위가 토큰으로 식별된 업무 사용자("
                + (resolvedAppUser == null ? "확인 불가" : resolvedAppUser)
                + ")의 DDS DATA GRANT를 통과했습니다.";
        DdsSqlEvidence evidence = collectEvidence(connection, marker, sql, timeoutSeconds);
        evidenceHistory.record(evidence);
        return new DdsVectorSearchResult(
            "token", userLabel, properties.vectorObject(), normalizedQuery, mode,
            embeddingModel(mode), true, "토큰 기반 DDS 권한으로 검색했습니다.", message,
            sessionUser, endUser, null, rows, evidence);
      }
    } catch (SQLException exception) {
      return failure("token", userLabel, normalizedQuery, mode,
          classifyTitle(exception), classifyMessage(exception), oracleCode(exception));
    }
  }

  private DdsVectorSearchResult failure(
      String userKey,
      String userLabel,
      String query,
      String mode,
      String title,
      String message,
      Integer oracleCode
  ) {
    return new DdsVectorSearchResult(
        userKey, userLabel, properties.vectorObject(), query, mode, embeddingModel(mode),
        false, title, message, null, null, oracleCode, List.of(), null);
  }

  private DdsSqlEvidence collectEvidence(Connection connection, String marker, String executedSql,
      int timeoutSeconds) {
    try (PreparedStatement statement = connection.prepareStatement("""
        SELECT sql_id, child_number, TO_CHAR(last_active_time, 'YYYY-MM-DD HH24:MI:SS'), executions
        FROM v$sql WHERE sql_text LIKE ? ORDER BY last_active_time DESC FETCH FIRST 1 ROW ONLY
        """)) {
      statement.setQueryTimeout(timeoutSeconds);
      statement.setString(1, "%" + marker + "%");
      try (ResultSet result = statement.executeQuery()) {
        if (!result.next()) {
          return DdsSqlEvidence.unavailable(marker, executedSql, "실행 SQL은 확인됐지만 SQL ID를 아직 찾지 못했습니다.");
        }
        String sqlId = result.getString(1);
        int child = result.getInt(2);
        String vpd = readPredicate(connection, "SELECT predicate FROM v$vpd_policy WHERE sql_id = ?", sqlId, timeoutSeconds);
        String plan = readPlanPredicate(connection, sqlId, child, timeoutSeconds);
        return new DdsSqlEvidence(true, marker, executedSql, sqlId, child, result.getString(3),
            result.getLong(4), plan, vpd,
            vpd != null ? "VPD 정책 predicate를 확인했습니다."
                : "DDS DATA GRANT 경로의 실행 SQL을 확인했습니다.");
      }
    } catch (SQLException exception) {
      return DdsSqlEvidence.unavailable(marker, executedSql,
          "실행 SQL 전문을 표시합니다. SQL ID와 실행 계획 조회는 이 환경에서 사용할 수 없습니다.");
    }
  }

  private String readPredicate(Connection connection, String sql, String sqlId, int timeoutSeconds) {
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setQueryTimeout(timeoutSeconds);
      statement.setString(1, sqlId);
      try (ResultSet result = statement.executeQuery()) {
        return result.next() ? compact(result.getString(1)) : null;
      }
    } catch (SQLException ignored) {
      return null;
    }
  }

  private String readPlanPredicate(Connection connection, String sqlId, int child, int timeoutSeconds) {
    StringBuilder output = new StringBuilder();
    try (PreparedStatement statement = connection.prepareStatement(
        "SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(?, ?, 'ALLSTATS LAST PREDICATE'))")) {
      statement.setQueryTimeout(timeoutSeconds);
      statement.setString(1, sqlId);
      statement.setInt(2, child);
      try (ResultSet result = statement.executeQuery()) {
        boolean predicate = false;
        while (result.next()) {
          String line = result.getString(1);
          if (line != null && line.contains("Predicate Information")) predicate = true;
          if (predicate && line != null && output.length() < 1800) output.append(line.trim()).append('\n');
        }
      }
    } catch (SQLException ignored) {
      return null;
    }
    return compact(output.toString());
  }

  private static String compact(String value) {
    if (value == null || value.isBlank()) return null;
    return value.replaceAll("\\s+", " ").trim();
  }

  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 List<Double> embed(String input, String mode) {
    if (AI_MODE.equals(mode)) {
      return embeddingClient.embedding(input);
    }
    return demoEmbedding(input);
  }

  private List<Double> demoEmbedding(String input) {
    double[] vector = new double[4];
    String normalized = input.toLowerCase(Locale.ROOT);
    addIfContains(vector, normalized, 0, "spring", "boot", "java", "security");
    addIfContains(vector, normalized, 1, "oracle", "vpd", "policy", "database", "db");
    addIfContains(vector, normalized, 2, "ords", "rest", "handler", "http", "api");
    addIfContains(vector, normalized, 3, "mcp", "tool", "권한", "tag", "태그");
    for (String token : normalized.split("[^a-z0-9가-힣]+")) {
      if (token.length() >= 3) {
        vector[Math.floorMod(token.hashCode(), vector.length)] += 0.03;
      }
    }
    double length = 0;
    for (double value : vector) {
      length += value * value;
    }
    if (length == 0) {
      vector[0] = 1;
      length = 1;
    }
    double scale = Math.sqrt(length);
    List<Double> result = new ArrayList<>(vector.length);
    for (double value : vector) {
      result.add(value / scale);
    }
    return List.copyOf(result);
  }

  private void addIfContains(double[] vector, String input, int index, String... terms) {
    for (String term : terms) {
      if (input.contains(term)) {
        vector[index] += 1;
      }
    }
  }

  private String normalizeUserKey(String value) {
    String normalized = value == null ? "" : value.trim().toLowerCase(Locale.ROOT);
    if (!USER_KEY.matcher(normalized).matches()) {
      throw new IllegalArgumentException("지원하지 않는 DDS END USER 선택값입니다.");
    }
    return normalized;
  }

  private String normalizeMode(String value) {
    String normalized = value == null ? DEMO_MODE : value.trim().toUpperCase(Locale.ROOT);
    if (!DEMO_MODE.equals(normalized) && !AI_MODE.equals(normalized)) {
      throw new AppException("임베딩 방식은 로컬 임베딩 또는 AI 임베딩만 사용할 수 있습니다.");
    }
    if (AI_MODE.equals(normalized) && !embeddingClient.embeddingConfigured()) {
      throw new AppException("AI 임베딩이 설정되지 않았습니다. 설정에서 임베딩 모델/API key를 추가하세요.");
    }
    return normalized;
  }

  private String embeddingModel(String mode) {
    return AI_MODE.equals(mode) ? embeddingClient.embeddingModelName() : "로컬 임베딩(개발용)";
  }

  private String json(List<Double> values) {
    try {
      return objectMapper.writeValueAsString(values);
    } catch (Exception exception) {
      throw new AppException("임베딩 JSON 생성에 실패했습니다: " + exception.getMessage());
    }
  }

  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 String required(String value, String label) {
    if (value == null || value.isBlank()) {
      throw new AppException(label + "을 입력하세요.");
    }
    return value.trim();
  }

  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) {
    StringBuilder 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) {
    String message = flattenedMessage(exception).toUpperCase(Locale.ROOT);
    if (message.contains("ORA-20002")) {
      return "Bearer 토큰이 유효하지 않습니다.";
    }
    if (message.contains("ORA-00942")) {
      return "이 DDS END USER에게 지식자료가 허용되지 않았습니다.";
    }
    if (message.contains("ORA-01017")) {
      return "DDS END USER 인증에 실패했습니다.";
    }
    if (message.contains("CONNECTION") || message.contains("ORA-12154")
        || message.contains("ORA-12514") || message.contains("IO ERROR")) {
      return "DDS 데이터베이스에 연결할 수 없습니다.";
    }
    return "DDS 벡터 검색 중 데이터베이스 오류가 발생했습니다.";
  }

  private static String classifyMessage(SQLException exception) {
    String message = flattenedMessage(exception);
    String upper = message.toUpperCase(Locale.ROOT);
    if (upper.contains("ORA-20002")) {
      return "토큰이 없거나 만료·회수되었거나 공통 사용자 매핑을 찾지 못했습니다.";
    }
    if (upper.contains("ORA-00942")) {
      return "END USER의 DATA ROLE에 이 DDS 벡터 VIEW를 대상으로 한 DATA GRANT가 없습니다. default deny 결과입니다.";
    }
    if (upper.contains("ORA-01017")) {
      return "서버에 등록된 DDS END USER 비밀번호를 확인하세요.";
    }
    return "DDS 벡터 VIEW, DATA ROLE/GRANT, DB 접속 정보를 확인하세요.";
  }
}
