package com.cloudhandson.vpdbackoffice.service;

import com.cloudhandson.vpdbackoffice.config.BackofficeProperties;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
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;
  }

  public Resolution resolve(String token, String question, int topK) {
    String raw = extractMentions(token, question);
    try {
      JsonNode json = objectMapper.readTree(raw);
      String hint = json.path("scope_hint").asText("UNKNOWN");
      List<GameCandidate> all = new ArrayList<>();
      for (JsonNode mention : json.path("game_mentions")) {
        all.addAll(search(token, mention.asText(""), topK));
      }
      return new Resolution(hint, all);
    } catch (Exception e) { throw new AppException("게임명 추출 결과 JSON 파싱 실패: " + e.getMessage()); }
  }

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

  public List<GameCandidate> search(String token, String question, int topK) {
    if (token == null || token.isBlank() || bearerTokenService.findByPlainToken(token.trim()) == null) {
      throw new VpdTokenAccessDeniedException();
    }
    BackofficeProperties.SelectAi db = properties.selectAi();
    if (db == null || !db.configured()) throw new AppException("게임 카탈로그 DB 설정이 필요합니다.");
    List<GameCandidate> result = new ArrayList<>();
    try (Connection c = DriverManager.getConnection(db.dbUrl(), db.dbUsername(), db.dbPassword());
         CallableStatement s = c.prepareCall("{ ? = call sg_game_catalog_search(?, ?) }")) {
      s.registerOutParameter(1, Types.REF_CURSOR); s.setString(2, question); s.setInt(3, Math.min(Math.max(topK, 1), 20)); s.execute();
      try (ResultSet rs = (ResultSet) s.getObject(1)) {
        while (rs.next()) result.add(new GameCandidate(rs.getString("GAME_KEY"), rs.getString("GAME_ID"), rs.getString("GAME_PREFIX"), rs.getString("GAME_NM"), rs.getString("GAME_ALIAS_NM"), rs.getDouble("COSINE_DISTANCE")));
      }
      return result;
    } catch (SQLException e) { throw new AppException("게임 카탈로그 벡터 검색 실패: " + e.getMessage()); }
  }

  public record GameCandidate(String gameKey, String gameId, String gamePrefix, String gameName, String aliases, double similarity) {}
  public record Resolution(String scopeType, List<GameCandidate> candidates) {}
}
