package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.config.BackofficeProperties;
import com.cloudhandson.vpdbackoffice.domain.token.BearerTokenRecord;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.time.Clock;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.regex.Pattern;
import org.springframework.stereotype.Service;

/**
 * Generates and executes bounded read-only SQL through the configured schema-owned Select AI profile.
 */
@Service
public class SelectAiService {

  private static final int MAX_PROMPT_LENGTH = 4_000;
  private static final int MAX_RESULT_ROWS = 100;
  private static final int QUERY_TIMEOUT_SECONDS = 30;
  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|for\\s+update|"
          + "dbms_[a-z0-9_]*|utl_[a-z0-9_]*|sys\\s*\\.)\\b"
  );

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

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

  public JsonNode generateAndExecute(String bearerToken, String prompt) {
    requireActiveToken(bearerToken);
    String normalizedPrompt = requiredPrompt(prompt);
    BackofficeProperties.SelectAi selectAi = properties == null ? null : properties.selectAi();
    if (selectAi == null || !selectAi.configured()) {
      throw new AppException("Select AI 연결 설정이 필요합니다. "
          + "BACKOFFICE_SELECT_AI_DB_URL, BACKOFFICE_SELECT_AI_DB_USERNAME, "
          + "BACKOFFICE_SELECT_AI_DB_PASSWORD를 확인하세요.");
    }

    String generatedSql = generate(selectAi, normalizedPrompt);
    String normalizedSql = validateReadOnlySql(generatedSql);
    QueryExecution execution = executeReadOnly(selectAi, normalizedSql);
    ObjectNode response = objectMapper.createObjectNode();
    response.put("status", "SHOWSQL_AND_EXECUTED");
    response.put("profile", selectAi.profile());
    response.put("generatedSql", normalizedSql);
    response.put("execution", "READ_ONLY_EXECUTED");
    response.put("rowCount", execution.items().size());
    response.put("truncated", execution.truncated());
    response.set("items", execution.items());
    response.put("nextStep", execution.truncated()
        ? "최초 " + MAX_RESULT_ROWS + "건만 반환했습니다. 생성 SQL로 전체 결과를 확인할 수 있습니다."
        : "생성 SQL을 읽기 전용으로 실행한 결과입니다.");
    return response;
  }

  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 requiredPrompt(String prompt) {
    String normalized = prompt == null ? "" : prompt.trim();
    if (normalized.isEmpty()) {
      throw new AppException("prompt는 필수입니다.");
    }
    if (normalized.length() > MAX_PROMPT_LENGTH) {
      throw new AppException("prompt는 " + MAX_PROMPT_LENGTH + "자 이하여야 합니다.");
    }
    return normalized;
  }

  private String generate(BackofficeProperties.SelectAi selectAi, String prompt) {
    String sql = "SELECT DBMS_CLOUD_AI.GENERATE(?, ?, 'showsql') FROM dual";
    try (Connection connection = DriverManager.getConnection(
        selectAi.dbUrl(), selectAi.dbUsername(), selectAi.dbPassword());
         PreparedStatement statement = connection.prepareStatement(sql)) {
      statement.setString(1, prompt);
      statement.setString(2, selectAi.profile());
      try (ResultSet resultSet = statement.executeQuery()) {
        if (!resultSet.next() || resultSet.getString(1) == null) {
          throw new AppException("Select AI가 생성 SQL을 반환하지 않았습니다.");
        }
        return resultSet.getString(1);
      }
    } catch (AppException exception) {
      throw exception;
    } catch (Exception exception) {
      throw new AppException("Select AI SHOWSQL 생성 실패: " + exception.getMessage());
    }
  }

  private QueryExecution executeReadOnly(BackofficeProperties.SelectAi selectAi, String generatedSql) {
    ArrayNode items = objectMapper.createArrayNode();
    boolean truncated = false;
    try (Connection connection = DriverManager.getConnection(
        selectAi.dbUrl(), selectAi.dbUsername(), selectAi.dbPassword());
         Statement transaction = connection.createStatement()) {
      connection.setAutoCommit(false);
      connection.setReadOnly(true);
      transaction.execute("SET TRANSACTION READ ONLY");
      try (PreparedStatement statement = connection.prepareStatement(generatedSql)) {
        statement.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
        statement.setFetchSize(MAX_RESULT_ROWS + 1);
        statement.setMaxRows(MAX_RESULT_ROWS + 1);
        try (ResultSet resultSet = statement.executeQuery()) {
          ResultSetMetaData metadata = resultSet.getMetaData();
          while (resultSet.next()) {
            if (items.size() >= MAX_RESULT_ROWS) {
              truncated = true;
              break;
            }
            ObjectNode row = items.addObject();
            for (int columnIndex = 1; columnIndex <= metadata.getColumnCount(); columnIndex++) {
              String column = metadata.getColumnLabel(columnIndex);
              if (column == null || column.isBlank()) {
                column = metadata.getColumnName(columnIndex);
              }
              putResultValue(row, column, resultSet.getObject(columnIndex));
            }
          }
        }
      } finally {
        connection.rollback();
      }
    } catch (Exception exception) {
      throw new AppException("Select AI 생성 SQL 실행 실패: " + exception.getMessage());
    }
    return new QueryExecution(items, truncated);
  }

  private void putResultValue(ObjectNode row, String column, Object value) {
    if (value == null) {
      row.putNull(column);
    } else if (value instanceof BigDecimal number) {
      row.put(column, number);
    } else if (value instanceof BigInteger number) {
      row.put(column, number);
    } else if (value instanceof Integer number) {
      row.put(column, number);
    } else if (value instanceof Long number) {
      row.put(column, number);
    } else if (value instanceof Short number) {
      row.put(column, number);
    } else if (value instanceof Float number) {
      row.put(column, number);
    } else if (value instanceof Double number) {
      row.put(column, number);
    } else if (value instanceof Boolean bool) {
      row.put(column, bool);
    } else {
      row.put(column, String.valueOf(value));
    }
  }

  private String validateReadOnlySql(String generatedSql) {
    String normalized = generatedSql == null ? "" : generatedSql.trim();
    if (normalized.startsWith("```")) {
      int firstLineEnd = normalized.indexOf('\n');
      int closingFence = normalized.lastIndexOf("```");
      if (firstLineEnd >= 0 && closingFence > firstLineEnd) {
        normalized = normalized.substring(firstLineEnd + 1, closingFence).trim();
      }
    }
    normalized = normalized.replaceFirst(";\\s*$", "").trim();
    if (!normalized.matches("(?is)^(select|with)\\b.*")) {
      throw new AppException("Select AI가 읽기 전용 SELECT/WITH SQL을 반환하지 않았습니다.");
    }
    if (normalized.contains(";")) {
      throw new AppException("Select AI 결과에 여러 SQL 문장이 포함되어 있어 반환하지 않습니다.");
    }
    if (normalized.contains("--") || normalized.contains("/*") || normalized.contains("*/")
        || UNSAFE_SQL.matcher(normalized).find()) {
      throw new AppException("Select AI 결과에 실행이 허용되지 않는 SQL 구문이 포함되어 있습니다.");
    }
    return normalized;
  }

  private record QueryExecution(ArrayNode items, boolean truncated) {}
}
