package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.config.BackofficeProperties;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Stream;
import org.springframework.stereotype.Service;

@Service
public class GameCatalogVectorService {

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

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

  /**
   * Resolves each extracted game mention independently.
   *
   * <p>Vector search supplies candidates. A candidate becomes executable only
   * when the extracted mention also matches catalog identity text. This keeps
   * an unknown mention from inheriting the nearest unrelated game while
   * retaining the original mention for partial-result reporting.</p>
   */
  public Resolution resolve(String token, String question, int topK) {
    requireActiveToken(token);
    JsonNode extracted;
    try {
      extracted = objectMapper.readTree(extractMentions(question));
    } catch (Exception exception) {
      throw new AppException(
          "게임명 추출 결과 JSON 파싱 실패: " + exception.getMessage());
    }

    String scopeType = extracted.path("scope_hint").asText("UNKNOWN");
    Map<String, MentionResolution> mentionsByNormalizedText =
        new LinkedHashMap<>();
    Map<String, GameCandidate> supportedByGameKey = new LinkedHashMap<>();

    for (JsonNode item : extracted.path("game_mentions")) {
      String mention = item.asText("").trim();
      String normalizedMention = normalizeIdentity(mention);
      if (normalizedMention.isEmpty()
          || mentionsByNormalizedText.containsKey(normalizedMention)) {
        continue;
      }

      List<GameCandidate> candidates = search(token, mention, topK);
      Optional<GameCandidate> matched =
          selectMatchedCandidate(mention, candidates);
      MentionResolution resolution;
      if (matched.isPresent()) {
        GameCandidate game = matched.get();
        supportedByGameKey.putIfAbsent(game.gameKey(), game);
        resolution = new MentionResolution(
            mention, "MATCHED", game, candidates);
      } else {
        resolution = new MentionResolution(
            mention, "UNMATCHED", null, candidates);
      }
      mentionsByNormalizedText.put(normalizedMention, resolution);
    }

    return new Resolution(
        scopeType,
        List.copyOf(supportedByGameKey.values()),
        List.copyOf(mentionsByNormalizedText.values())
    );
  }

  private String extractMentions(String question) {
    BackofficeProperties.SelectAi database = requiredDatabase();
    try (Connection connection = DriverManager.getConnection(
        database.dbUrl(), database.dbUsername(), database.dbPassword());
         CallableStatement statement = connection.prepareCall(
             "{ ? = call sg_game_extract_mentions(?) }")) {
      statement.registerOutParameter(1, Types.CLOB);
      statement.setString(2, question);
      statement.execute();
      Clob value = (Clob) statement.getObject(1);
      return value == null
          ? "{\"game_mentions\":[],\"scope_hint\":\"UNKNOWN\"}"
          : value.getSubString(1, (int) value.length());
    } catch (SQLException exception) {
      throw new AppException(
          "ADB 게임명 추출 실패: " + exception.getMessage());
    }
  }

  public List<GameCandidate> search(
      String token, String question, int topK
  ) {
    requireActiveToken(token);
    BackofficeProperties.SelectAi database = requiredDatabase();
    List<GameCandidate> result = new ArrayList<>();
    try (Connection connection = DriverManager.getConnection(
        database.dbUrl(), database.dbUsername(), database.dbPassword());
         CallableStatement statement = connection.prepareCall(
             "{ ? = call sg_game_catalog_search(?, ?) }")) {
      statement.registerOutParameter(1, Types.REF_CURSOR);
      statement.setString(2, question);
      statement.setInt(3, Math.min(Math.max(topK, 1), 20));
      statement.execute();
      try (ResultSet rows = (ResultSet) statement.getObject(1)) {
        while (rows.next()) {
          result.add(new GameCandidate(
              rows.getString("GAME_KEY"),
              rows.getString("GAME_ID"),
              rows.getString("GAME_PREFIX"),
              rows.getString("GAME_NM"),
              rows.getString("GAME_ALIAS_NM"),
              rows.getDouble("COSINE_DISTANCE")
          ));
        }
      }
      return result;
    } catch (SQLException exception) {
      throw new AppException(
          "게임 카탈로그 벡터 검색 실패: " + exception.getMessage());
    }
  }

  static Optional<GameCandidate> selectMatchedCandidate(
      String mention, List<GameCandidate> candidates
  ) {
    String normalizedMention = normalizeIdentity(mention);
    if (normalizedMention.isEmpty()) {
      return Optional.empty();
    }
    return candidates.stream()
        .filter(candidate -> identityFields(candidate)
            .map(GameCatalogVectorService::normalizeIdentity)
            .anyMatch(field -> field.contains(normalizedMention)))
        .min(Comparator.comparingDouble(GameCandidate::similarity));
  }

  private static Stream<String> identityFields(GameCandidate candidate) {
    return Stream.of(
        nullToEmpty(candidate.gameKey()),
        nullToEmpty(candidate.gameId()),
        nullToEmpty(candidate.gamePrefix()),
        nullToEmpty(candidate.gameName()),
        nullToEmpty(candidate.aliases())
    );
  }

  private static String normalizeIdentity(String value) {
    if (value == null) {
      return "";
    }
    return value.toUpperCase(Locale.ROOT)
        .replaceAll("[\\s\\p{Punct}]", "");
  }

  private static String nullToEmpty(String value) {
    return value == null ? "" : value;
  }

  private BackofficeProperties.SelectAi requiredDatabase() {
    BackofficeProperties.SelectAi database =
        properties == null ? null : properties.selectAi();
    if (database == null || !database.configured()) {
      throw new AppException("게임 카탈로그 DB 설정이 필요합니다.");
    }
    return database;
  }

  private void requireActiveToken(String token) {
    if (token == null || token.isBlank()
        || bearerTokenService == null
        || bearerTokenService.findByPlainToken(token.trim()) == null) {
      throw new VpdTokenAccessDeniedException();
    }
  }

  public record GameCandidate(
      String gameKey,
      String gameId,
      String gamePrefix,
      String gameName,
      String aliases,
      double similarity
  ) {}

  public record MentionResolution(
      String mention,
      String status,
      GameCandidate matchedGame,
      List<GameCandidate> candidates
  ) {}

  public record Resolution(
      String scopeType,
      List<GameCandidate> candidates,
      List<MentionResolution> mentions
  ) {
    public Resolution(
        String scopeType, List<GameCandidate> candidates
    ) {
      this(scopeType, candidates, List.of());
    }
  }
}
