package com.agent.management.rag.structured;

import com.agent.management.service.SchemaExplorerService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

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.Set;
import java.util.regex.Pattern;

/** 仅使用 Schema 中的标识符执行有界、只读采样。 */
@Service
@RequiredArgsConstructor
public class StructuredValueSampler {
    private static final int MAX_VALUE_FIELDS = 5;
    private static final int MAX_DISTINCT_VALUES = 20;
    private static final int MAX_REFERENCE_TABLES = 2;
    private static final int MAX_REFERENCE_ROWS = 50;
    private static final long MAX_REFERENCE_TABLE_SIZE = 200;
    private static final Set<String> SHORT_TYPES = Set.of(
            "CHAR", "VARCHAR", "NCHAR", "NVARCHAR", "ENUM", "SET", "BOOLEAN", "BOOL", "BIT");
    private static final Set<String> NUMERIC_ID_TYPES = Set.of(
            "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT");
    private static final Pattern SENSITIVE = Pattern.compile(
            "(^|_)(password|passwd|pwd|secret|token|key|credential)($|_)", Pattern.CASE_INSENSITIVE);

    private final SchemaExplorerService explorer;

    public SampleResult sample(Long datasourceId,
                               List<String> selectedTables,
                               Map<String, List<SchemaExplorerService.ColumnSpec>> columnsByTable) {
        Map<String, List<Object>> sampledValues = new LinkedHashMap<>();
        Map<String, List<Map<String, Object>>> referenceRows = new LinkedHashMap<>();
        List<String> warnings = new ArrayList<>();

        List<FieldCandidate> fields = new ArrayList<>();
        for (int tableOrder = 0; tableOrder < selectedTables.size(); tableOrder++) {
            String table = selectedTables.get(tableOrder);
            for (SchemaExplorerService.ColumnSpec column : columnsByTable.getOrDefault(table, List.of())) {
                if (isSampleable(column)) {
                    fields.add(new FieldCandidate(table, column, priority(column.name()), tableOrder));
                }
            }
        }
        fields.sort(Comparator.comparingInt(FieldCandidate::priority).reversed()
                .thenComparingInt(FieldCandidate::tableOrder)
                .thenComparing(item -> item.column().name()));
        for (FieldCandidate field : fields) {
            if (sampledValues.size() >= MAX_VALUE_FIELDS) break;
            try {
                String identifier = qualifiedField(field.table(), field.column().name());
                String sql = "SELECT DISTINCT " + quote(field.column().name()) + " FROM " + quote(field.table())
                        + " WHERE " + quote(field.column().name()) + " IS NOT NULL LIMIT " + (MAX_DISTINCT_VALUES + 1);
                SchemaExplorerService.QueryResult result = explorer.executeQuery(datasourceId, sql, MAX_DISTINCT_VALUES + 1);
                if (result.rows().size() > MAX_DISTINCT_VALUES) continue;
                List<Object> values = result.rows().stream().filter(row -> !row.isEmpty())
                        .map(row -> row.get(0)).filter(java.util.Objects::nonNull).toList();
                if (!values.isEmpty()) sampledValues.put(identifier, values);
            } catch (Exception error) {
                warnings.add("sampledValues " + qualifiedField(field.table(), field.column().name())
                        + " failed: " + shortMessage(error));
            }
        }

        for (String table : selectedTables) {
            if (referenceRows.size() >= MAX_REFERENCE_TABLES) break;
            List<SchemaExplorerService.ColumnSpec> columns = columnsByTable.getOrDefault(table, List.of());
            List<SchemaExplorerService.ColumnSpec> selected = referenceColumns(columns);
            if (selected.isEmpty()) continue;
            try {
                if (explorer.countRows(datasourceId, null, table) > MAX_REFERENCE_TABLE_SIZE) continue;
                String sql = "SELECT " + selected.stream().map(column -> quote(column.name()))
                        .collect(java.util.stream.Collectors.joining(", ")) + " FROM " + quote(table)
                        + " LIMIT " + MAX_REFERENCE_ROWS;
                SchemaExplorerService.QueryResult result = explorer.executeQuery(datasourceId, sql, MAX_REFERENCE_ROWS);
                List<Map<String, Object>> rows = new ArrayList<>();
                for (List<Object> row : result.rows()) {
                    Map<String, Object> mapped = new LinkedHashMap<>();
                    for (int i = 0; i < Math.min(result.columns().size(), row.size()); i++) {
                        if (row.get(i) != null) mapped.put(result.columns().get(i), row.get(i));
                    }
                    if (!mapped.isEmpty()) rows.add(mapped);
                }
                if (!rows.isEmpty()) referenceRows.put(table, rows);
            } catch (Exception error) {
                warnings.add("referenceRows " + table + " failed: " + shortMessage(error));
            }
        }
        return new SampleResult(sampledValues, referenceRows, warnings);
    }

    private static boolean isSampleable(SchemaExplorerService.ColumnSpec column) {
        String type = normalizedType(column.type());
        return SHORT_TYPES.contains(type) && column.size() <= 255 && !SENSITIVE.matcher(column.name()).find();
    }

    private static List<SchemaExplorerService.ColumnSpec> referenceColumns(
            List<SchemaExplorerService.ColumnSpec> columns) {
        List<SchemaExplorerService.ColumnSpec> ids = columns.stream().filter(StructuredValueSampler::isReferenceIdSafe)
                .filter(column -> role(column.name(), "id", "code")).limit(2).toList();
        List<SchemaExplorerService.ColumnSpec> names = columns.stream().filter(StructuredValueSampler::isReferenceSafe)
                .filter(column -> role(column.name(), "name", "title", "label")).limit(2).toList();
        if (ids.isEmpty() || names.isEmpty()) return List.of();
        List<SchemaExplorerService.ColumnSpec> result = new ArrayList<>();
        result.addAll(ids); result.addAll(names);
        columns.stream().filter(StructuredValueSampler::isReferenceSafe)
                .filter(column -> role(column.name(), "type", "category"))
                .filter(column -> result.stream().noneMatch(existing -> existing.name().equals(column.name())))
                .limit(2).forEach(result::add);
        return result;
    }

    private static boolean isReferenceSafe(SchemaExplorerService.ColumnSpec column) {
        return isSampleable(column) && !SENSITIVE.matcher(column.name()).find();
    }

    private static boolean isReferenceIdSafe(SchemaExplorerService.ColumnSpec column) {
        return !SENSITIVE.matcher(column.name()).find()
                && (isSampleable(column) || NUMERIC_ID_TYPES.contains(normalizedType(column.type())));
    }

    private static boolean role(String name, String... roles) {
        String normalized = name.toLowerCase(Locale.ROOT);
        for (String role : roles) {
            if (normalized.equals(role) || normalized.endsWith("_" + role)) return true;
        }
        return false;
    }

    private static int priority(String name) {
        String normalized = name.toLowerCase(Locale.ROOT);
        if (normalized.endsWith("_category") || normalized.endsWith("_type")) return 110;
        if (normalized.contains("status") || normalized.endsWith("_label")) return 100;
        if (normalized.startsWith("is_") || normalized.startsWith("has_") || normalized.endsWith("_enabled")) return 80;
        if (normalized.endsWith("_name") || normalized.endsWith("_title")) return 70;
        if (normalized.endsWith("_code")) return 60;
        if (normalized.endsWith("_id")) return 50;
        return 10;
    }

    private static String normalizedType(String type) {
        if (type == null) return "";
        int parenthesis = type.indexOf('(');
        return (parenthesis < 0 ? type : type.substring(0, parenthesis)).toUpperCase(Locale.ROOT);
    }

    private static String quote(String identifier) { return "`" + identifier.replace("`", "") + "`"; }

    private static String qualifiedField(String table, String column) { return table + "." + column; }

    private static String shortMessage(Exception error) {
        String message = error.getMessage();
        return message == null ? error.getClass().getSimpleName() : message.substring(0, Math.min(200, message.length()));
    }

    private record FieldCandidate(String table, SchemaExplorerService.ColumnSpec column,
                                  int priority, int tableOrder) {}

    public record SampleResult(Map<String, List<Object>> sampledValues,
                               Map<String, List<Map<String, Object>>> referenceRows,
                               List<String> warnings) {}
}
