package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.config.BackofficeProperties;
import com.cloudhandson.vpdbackoffice.domain.token.BearerTokenRecord;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.time.Clock;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;
import org.springframework.stereotype.Service;

/** Calls the SGMP_POC QA-vector database API through the schema-owned Select AI connection. */
@Service
public class QaVectorService {

  private static final int MAX_QUESTION_LENGTH = 4_000;
  private static final int MAX_ANSWER_SQL_LENGTH = 20_000;
  private static final int MAX_ANSWER_LENGTH = 20_000;
  private static final Pattern UNSAFE_SQL = Pattern.compile(
      "(?is)\\b(?:insert|update|delete|merge|alter|drop|create|truncate|grant|revoke|"
          + "commit|rollback|savepoint|lock|call|exec(?:ute)?|begin|declare|dbms_[a-z0-9_]*|"
          + "utl_[a-z0-9_]*|sys\\s*\\.)\\b"
  );

  private final BackofficeProperties properties;
  private final BearerTokenService bearerTokenService;
  private final Clock clock;

  public QaVectorService(
      BackofficeProperties properties,
      BearerTokenService bearerTokenService,
      Clock clock
  ) {
    this.properties = properties;
    this.bearerTokenService = bearerTokenService;
    this.clock = clock;
  }

  public VectorSearchResult search(String bearerToken, String question, int topK) {
    requireActiveToken(bearerToken);
    String normalizedQuestion = requiredText(question, "question", MAX_QUESTION_LENGTH);
    if (topK < 1 || topK > 20) {
      throw new AppException("topK는 1에서 20 사이여야 합니다.");
    }
    BackofficeProperties.SelectAi selectAi = requiredSelectAi();

    List<VectorExample> examples = new ArrayList<>();
    try (Connection connection = DriverManager.getConnection(
        selectAi.dbUrl(), selectAi.dbUsername(), selectAi.dbPassword());
         CallableStatement statement = connection.prepareCall("{ ? = call sg_qa_vector_search(?, ?) }")) {
      statement.registerOutParameter(1, Types.REF_CURSOR);
      statement.setString(2, normalizedQuestion);
      statement.setInt(3, topK);
      statement.execute();
      try (ResultSet resultSet = (ResultSet) statement.getObject(1)) {
        while (resultSet.next()) {
          examples.add(new VectorExample(
              resultSet.getLong("EXAMPLE_ID"),
              resultSet.getString("QUESTION"),
              resultSet.getString("ANSWER_SQL"),
              resultSet.getString("ANSWER_TEXT"),
              resultSet.getString("EMBEDDING_MODEL"),
              resultSet.getDouble("COSINE_DISTANCE")
          ));
        }
      }
    } catch (Exception exception) {
      throw new AppException("QA 벡터 예제 SQL 조회 실패: " + exception.getMessage());
    }
    return new VectorSearchResult(normalizedQuestion, topK, examples);
  }

  public VectorStoreResult store(String bearerToken, String question, String answerSql, String answer) {
    requireActiveToken(bearerToken);
    String normalizedQuestion = requiredText(question, "question", MAX_QUESTION_LENGTH);
    String normalizedAnswerSql = requiredReadOnlySql(answerSql);
    String normalizedAnswer = optionalText(answer, "answer", MAX_ANSWER_LENGTH);
    BackofficeProperties.SelectAi selectAi = requiredSelectAi();

    try (Connection connection = DriverManager.getConnection(
        selectAi.dbUrl(), selectAi.dbUsername(), selectAi.dbPassword());
         PreparedStatement statement = connection.prepareStatement(
             "SELECT sg_qa_vector_store(?, ?, ?) AS example_id FROM dual")) {
      statement.setString(1, normalizedQuestion);
      statement.setString(2, normalizedAnswerSql);
      statement.setString(3, normalizedAnswer);
      try (ResultSet resultSet = statement.executeQuery()) {
        if (!resultSet.next()) {
          throw new AppException("QA 벡터 예제 SQL 저장 결과가 없습니다.");
        }
        return new VectorStoreResult(
            resultSet.getLong("EXAMPLE_ID"), normalizedQuestion, "cohere.embed-v4.0");
      }
    } catch (AppException exception) {
      throw exception;
    } catch (Exception exception) {
      throw new AppException("QA 벡터 예제 SQL 저장 실패: " + exception.getMessage());
    }
  }

  private BackofficeProperties.SelectAi requiredSelectAi() {
    BackofficeProperties.SelectAi selectAi = properties == null ? null : properties.selectAi();
    if (selectAi == null || !selectAi.configured()) {
      throw new AppException("QA 벡터 연결 설정이 필요합니다. "
          + "BACKOFFICE_SELECT_AI_DB_URL, BACKOFFICE_SELECT_AI_DB_USERNAME, "
          + "BACKOFFICE_SELECT_AI_DB_PASSWORD를 확인하세요.");
    }
    return selectAi;
  }

  private void requireActiveToken(String bearerToken) {
    if (bearerToken == null || bearerToken.isBlank()) {
      throw new VpdTokenAccessDeniedException();
    }
    BearerTokenRecord token = bearerTokenService.findByPlainToken(bearerToken.trim());
    LocalDateTime now = LocalDateTime.now(clock.withZone(ZoneId.systemDefault()));
    if (token == null || !token.active(now)) {
      throw new VpdTokenAccessDeniedException();
    }
  }

  private String requiredReadOnlySql(String answerSql) {
    String normalized = requiredText(answerSql, "answerSql", MAX_ANSWER_SQL_LENGTH)
        .replaceFirst(";\\s*$", "").trim();
    if (!normalized.matches("(?is)^(select|with)\\b.*") || normalized.contains(";")
        || normalized.contains("--") || normalized.contains("/*") || normalized.contains("*/")
        || UNSAFE_SQL.matcher(normalized).find()) {
      throw new AppException("answerSql은 단일 읽기 전용 SELECT/WITH SQL이어야 합니다.");
    }
    return normalized;
  }

  private String requiredText(String value, String fieldName, int maximumLength) {
    String normalized = optionalText(value, fieldName, maximumLength);
    if (normalized == null) {
      throw new AppException(fieldName + "는 필수입니다.");
    }
    return normalized;
  }

  private String optionalText(String value, String fieldName, int maximumLength) {
    String normalized = value == null ? "" : value.trim();
    if (normalized.isEmpty()) {
      return null;
    }
    if (normalized.length() > maximumLength) {
      throw new AppException(fieldName + "는 " + maximumLength + "자 이하여야 합니다.");
    }
    return normalized;
  }

  public record VectorExample(
      long exampleId,
      String question,
      String answerSql,
      String answer,
      String embeddingModel,
      double cosineDistance
  ) {
  }

  public record VectorSearchResult(String question, int topK, List<VectorExample> examples) {
  }

  public record VectorStoreResult(long exampleId, String question, String embeddingModel) {
  }
}
