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.List;
import java.util.UUID;

/**
 * JdbcTemplate-backed default impl {@link DependentsQuery}.
 *
 * <p>Поля {@code source_field} приходят из schema parser (валидируются на
 * bundle import), плюс whitelist regex здесь — SQL injection невозможна.
 * Native SQL потому что JdbcTemplate не bind'ит JSONB key paths.
 */
@Component
public class DependentsQueryJdbc implements DependentsQuery {

  private final JdbcTemplate jdbc;

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

  @Override
  public List<DependentRow> findActive(
      UUID sourceDictionaryId,
      String sourceField,
      String targetValue,
      OffsetDateTime at,
      int limit,
      int offset) {
    validateField(sourceField);
    if (limit <= 0 || limit > 500) {
      throw new IllegalArgumentException("limit must be 1..500, got " + limit);
    }
    if (offset < 0) {
      throw new IllegalArgumentException("offset must be >= 0, got " + offset);
    }

    String sql = """
        SELECT id, dictionary_id, business_key, valid_from, valid_to, created_at
        FROM dictionary_records
        WHERE dictionary_id = ?
          AND valid_from <= ?
          AND valid_to > ?
          AND data ->> '%s' = ?
        ORDER BY created_at DESC
        LIMIT ? OFFSET ?
        """.formatted(sourceField);

    return jdbc.query(
        sql,
        (rs, n) -> new DependentRow(
            (UUID) rs.getObject("id"),
            (UUID) rs.getObject("dictionary_id"),
            rs.getString("business_key"),
            rs.getObject("valid_from", OffsetDateTime.class),
            rs.getObject("valid_to", OffsetDateTime.class),
            rs.getObject("created_at", OffsetDateTime.class)),
        sourceDictionaryId, at, at, targetValue, limit, offset);
  }

  @Override
  public long countActive(
      UUID sourceDictionaryId,
      String sourceField,
      String targetValue,
      OffsetDateTime at) {
    validateField(sourceField);

    String sql = """
        SELECT COUNT(*) FROM dictionary_records
        WHERE dictionary_id = ?
          AND valid_from <= ?
          AND valid_to > ?
          AND data ->> '%s' = ?
        """.formatted(sourceField);

    Long count = jdbc.queryForObject(sql, Long.class, sourceDictionaryId, at, at, targetValue);
    return count == null ? 0 : count;
  }

  /** Paranoid SQL injection guard. Mirror'ит {@code OrphanReferenceQuery}. */
  private static void validateField(String field) {
    if (field == null || field.isBlank()) {
      throw new IllegalArgumentException("field name is empty");
    }
    if (!field.matches("^[a-zA-Z_][a-zA-Z0-9_]{0,127}$")) {
      throw new IllegalArgumentException(
          "field name has unsafe characters: " + field);
    }
  }
}
