package cloud.nstart.terravault.ordinis.domain.record;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.time.OffsetDateTime;
import java.util.Collection;
import java.util.List;
import java.util.UUID;

/**
 * Smart JSONB search across all dictionaries (CEO plan v1 stretch).
 *
 * <p>ILIKE на {@code data::text} с trigram-индексом
 * {@code idx_dict_records_data_trgm} (см. migration 0018). Active records
 * only ({@code valid_from <= now() < valid_to}), фильтрация по data_scope
 * запрашивающего consumer'а.
 *
 * <p>Per-dict cap: max {@code maxPerDict} matches на каждый словарь —
 * иначе один dict с тысячей matches забьёт весь limit.
 *
 * <p>SQL injection: query passed как PreparedStatement param (`?`), wrap
 * с `%...%` в SQL constant. Никаких user inputs не идут в SQL string.
 */
@Component
public class RecordSearchQuery {

  private final JdbcTemplate jdbc;

  @Autowired
  public RecordSearchQuery(JdbcTemplate jdbc) {
    this.jdbc = jdbc;
  }

  /**
   * Search active records по {@code q} string.
   *
   * @param q              free-form text (>= 3 chars для использования индекса)
   * @param allowedScopes  scope subset виден caller'у (как text values)
   * @param at             snapshot moment (now() обычно)
   * @param maxPerDict     cap matches per dict (защита от dominant-dict skew)
   * @param totalLimit     hard cap всех результатов
   */
  public List<SearchHit> search(
      String q,
      Collection<String> allowedScopes,
      OffsetDateTime at,
      int maxPerDict,
      int totalLimit) {
    if (q == null || q.length() < 3) {
      return List.of();
    }
    if (maxPerDict < 1 || maxPerDict > 100) maxPerDict = 10;
    if (totalLimit < 1 || totalLimit > 500) totalLimit = 100;
    if (allowedScopes == null || allowedScopes.isEmpty()) return List.of();

    // Per-dict ROW_NUMBER cap. window function over partition, order by
    // created_at DESC чтобы newest matches видны первыми.
    String sql = """
        SELECT * FROM (
          SELECT
            r.id, r.dictionary_id, def.name AS dict_name, def.display_name AS dict_display,
            r.business_key, r.data_scope, r.created_at,
            ROW_NUMBER() OVER (PARTITION BY r.dictionary_id ORDER BY r.created_at DESC) AS rn
          FROM dictionary_records r
          JOIN dictionary_definitions def ON def.id = r.dictionary_id
          WHERE r.valid_from <= ?
            AND r.valid_to > ?
            AND r.data_scope = ANY(CAST(? AS TEXT[]))
            AND r.data::text ILIKE ?
        ) t
        WHERE rn <= ?
        ORDER BY t.dict_name, t.created_at DESC
        LIMIT ?
        """;

    String[] scopesArr = allowedScopes.toArray(String[]::new);
    String pattern = "%" + q + "%";

    return jdbc.query(
        sql,
        (rs, n) -> new SearchHit(
            (UUID) rs.getObject("id"),
            (UUID) rs.getObject("dictionary_id"),
            rs.getString("dict_name"),
            rs.getString("dict_display"),
            rs.getString("business_key"),
            rs.getString("data_scope"),
            rs.getObject("created_at", OffsetDateTime.class)),
        at, at, scopesArr, pattern, maxPerDict, totalLimit);
  }

  public record SearchHit(
      UUID id,
      UUID dictionaryId,
      String dictName,
      String dictDisplayName,
      String businessKey,
      String dataScope,
      OffsetDateTime createdAt) {}
}
