package jnpf.limsService.provider;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import jnpf.exception.DataException;
import lombok.Value;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.List;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.HashSet;
import java.util.Set;
import java.util.Objects;
import java.util.function.Supplier;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/** Only registered business objects can contribute source candidates. */
public class LimsElnSourceMetadata {
    private static final Pattern RESTRICTED_COLUMN = Pattern.compile(
            "(^|_)(password|pwd|secret|token|credential|signature|sign|salt|private_key|qianming|flow|tenant|delete_mark)(_|$)",
            Pattern.CASE_INSENSITIVE);
    private final JdbcTemplate configuration;
    private final JdbcTemplate metadata;
    private final Supplier<LimsElnSourceCatalog.Actor> actors;
    private final ObjectMapper json = new ObjectMapper();

    public LimsElnSourceMetadata(DataSource configurationDataSource, DataSource readOnlyDataSource,
            Supplier<LimsElnSourceCatalog.Actor> actors) {
        this.configuration = new JdbcTemplate(configurationDataSource);
        this.metadata = new JdbcTemplate(readOnlyDataSource);
        configuration.setQueryTimeout(5);
        metadata.setQueryTimeout(5);
        this.actors = actors;
    }

    public List<BusinessObject> objects() {
        requireManager();
        return configuration.query("SELECT object_id, display_name, root_anchor FROM lims_eln_provider_object "
                        + "WHERE f_tenant_id = '0' AND biz_enabled = true ORDER BY object_id",
                (row, index) -> new BusinessObject(row.getString(1), row.getString(2), row.getBoolean(3)));
    }

    public List<Candidate> fields(String objectId) {
        requireManager();
        List<String[]> objects = configuration.query("SELECT schema_name, table_name, tenant_column, delete_column FROM lims_eln_provider_object "
                        + "WHERE f_tenant_id = '0' AND object_id = ? AND biz_enabled = true",
                (row, index) -> new String[]{row.getString(1), row.getString(2), row.getString(3), row.getString(4)}, objectId);
        if (objects.size() != 1) throw new DataException("Source object is not registered");
        String[] object = objects.get(0);
        FormHints hints = formHints(object[1]);
        return metadata.query("SELECT c.oid, a.attnum, a.atttypid, a.attname, t.typname, "
                        + "pg_catalog.col_description(c.oid, a.attnum) AS label "
                        + "FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
                        + "JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid "
                        + "JOIN pg_catalog.pg_type t ON t.oid = a.atttypid "
                        + "WHERE n.nspname = ? AND c.relname = ? AND c.relkind IN ('r', 'p') "
                        + "AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum",
                (row, index) -> object[2].equals(row.getString("attname"))
                        || Objects.equals(object[3], row.getString("attname"))
                        || RESTRICTED_COLUMN.matcher(row.getString("attname")).find()
                        || hints.restricted.contains(row.getString("attname")) ? null
                        : new Candidate("c" + row.getLong("oid") + "_" + row.getInt("attnum")
                        + "_" + row.getLong("atttypid"), hints.labels.getOrDefault(row.getString("attname"), row.getString("label") == null
                        ? row.getString("attname") : row.getString("label")), dataType(row.getString("typname")),
                        row.getString("typname"), row.getString("attname"), hints.nameOptions.get(row.getString("attname")),
                        hints.nameLookups.get(row.getString("attname")), hints.conflictingNames.contains(row.getString("attname"))),
                object[0], object[1]).stream().filter(Objects::nonNull)
                .filter(field -> field.getDataType() != null).collect(Collectors.toList());
    }

    public Map<String, String> options(String objectId, String fieldId) {
        Candidate field = fields(objectId).stream().filter(candidate -> candidate.getId().equals(fieldId)).findFirst()
                .orElseThrow(() -> new DataException("Source candidate is no longer available"));
        if (field.getNameOptions() != null) return new LinkedHashMap<>(field.getNameOptions());
        NameLookup lookup = field.getNameLookup();
        if (lookup == null || !"base_dictionary_data".equals(lookup.getTable())) return java.util.Collections.emptyMap();
        String table = LimsElnSourceExecution.identifier(lookup.getSchema()) + "."
                + LimsElnSourceExecution.identifier(lookup.getTable());
        List<String[]> rows = metadata.query("SELECT " + LimsElnSourceExecution.identifier(lookup.getKeyColumn())
                        + ", " + LimsElnSourceExecution.identifier(lookup.getLabelColumn()) + " FROM " + table
                        + " WHERE f_tenant_id = '0' AND (f_delete_mark IS NULL OR f_delete_mark = 0) AND f_enabled_mark = 1"
                        + " AND " + LimsElnSourceExecution.identifier(lookup.getQualifierColumn()) + " = ?"
                        + " ORDER BY " + LimsElnSourceExecution.identifier(lookup.getKeyColumn()) + " LIMIT 1001",
                (row, index) -> new String[]{row.getString(1), row.getString(2)}, lookup.getQualifierValue());
        if (rows.size() > 1000) throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_LIMIT", "Too many source enum options");
        Map<String, String> options = new LinkedHashMap<>();
        for (String[] row : rows) {
            if (row[0] == null || row[0].trim().isEmpty() || row[1] == null || row[1].trim().isEmpty()) continue;
            if (options.putIfAbsent(row[0], row[1]) != null) {
                throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_AMBIGUOUS", "Source enum codes are not unique");
            }
        }
        return options;
    }

    public List<BusinessObject> enumObjects() {
        requireManager();
        return configuration.query("SELECT o.object_id, o.display_name, o.root_anchor FROM lims_eln_provider_object o "
                        + "JOIN lims_eln_provider_enum_object e ON e.f_tenant_id = o.f_tenant_id AND e.object_id = o.object_id "
                        + "WHERE o.f_tenant_id = '0' AND o.biz_enabled = true ORDER BY o.object_id",
                (row, index) -> new BusinessObject(row.getString(1), row.getString(2), row.getBoolean(3)));
    }

    public Map<String, String> enumOptions(EnumSelection selection) {
        requireManager();
        if (selection == null) throw new DataException("Source enum selection is required");
        ObjectMapping object = objectMapping(selection.getObjectId());
        List<String[]> policies = configuration.query("SELECT enabled_column, enabled_value FROM lims_eln_provider_enum_object "
                        + "WHERE f_tenant_id = '0' AND object_id = ?",
                (row, index) -> new String[]{row.getString(1), row.getString(2)}, selection.getObjectId());
        if (policies.size() != 1) throw new DataException("Source object is not registered for enum options");
        List<Candidate> candidates = fields(selection.getObjectId());
        Candidate key = enumField(candidates, selection.getValueFieldId());
        Candidate label = enumField(candidates, selection.getLabelFieldId());
        String table = LimsElnSourceExecution.identifier(object.getSchema()) + "."
                + LimsElnSourceExecution.identifier(object.getTable());
        StringBuilder sql = new StringBuilder("SELECT ").append(LimsElnSourceExecution.identifier(key.getColumn()))
                .append(", ").append(LimsElnSourceExecution.identifier(label.getColumn())).append(" FROM ").append(table)
                .append(" WHERE ").append(LimsElnSourceExecution.identifier(object.getTenantColumn())).append(" = '0'");
        if (object.getDeleteColumn() != null) {
            String deleted = LimsElnSourceExecution.identifier(object.getDeleteColumn());
            sql.append(" AND (").append(deleted).append(" IS NULL OR ").append(deleted).append(" = 0)");
        }
        String[] policy = policies.get(0);
        if (policy[0] != null) {
            if (candidates.stream().noneMatch(field -> field.getColumn().equals(policy[0]))) {
                throw new DataException("Source enum enabled field is unavailable");
            }
            sql.append(" AND ").append(LimsElnSourceExecution.identifier(policy[0])).append("::text = ?");
        }
        sql.append(" ORDER BY ").append(LimsElnSourceExecution.identifier(key.getColumn())).append(" LIMIT 1001");
        return metadata.execute((org.springframework.jdbc.core.ConnectionCallback<Map<String, String>>) connection -> {
            connection.setReadOnly(true);
            connection.setAutoCommit(false);
            try {
                LimsElnSourceExecution.enforceReadOnlyTransaction(connection);
                try (java.sql.PreparedStatement query = connection.prepareStatement(sql.toString())) {
                    query.setQueryTimeout(5);
                    if (policy[0] != null) query.setString(1, policy[1]);
                    Map<String, String> options = new LinkedHashMap<>();
                    try (java.sql.ResultSet rows = query.executeQuery()) {
                        int count = 0;
                        while (rows.next()) {
                            if (++count > 1000) throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_LIMIT", "Too many source enum options");
                            String value = rows.getString(1);
                            String name = rows.getString(2);
                            if (value == null || value.trim().isEmpty() || name == null || name.trim().isEmpty()) continue;
                            if (options.putIfAbsent(value, name) != null) {
                                throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_AMBIGUOUS", "Source enum codes are not unique");
                            }
                        }
                    }
                    return options;
                }
            } finally { connection.rollback(); }
        });
    }

    private static Candidate enumField(List<Candidate> candidates, String id) {
        return candidates.stream().filter(field -> field.getId().equals(id) && "TEXT".equals(field.getDataType()))
                .findFirst().orElseThrow(() -> new DataException("Source enum code and name must be available text fields"));
    }

    NameLookup enumNameLookup(EnumSelection selection) {
        requireManager();
        if (selection == null || enumObjects().stream().noneMatch(object -> object.getId().equals(selection.getObjectId()))) {
            throw new DataException("Source object is not registered for enum options");
        }
        ObjectMapping object = objectMapping(selection.getObjectId());
        List<Candidate> candidates = fields(selection.getObjectId());
        return new NameLookup(object.getSchema(), object.getTable(),
                enumField(candidates, selection.getValueFieldId()).getColumn(),
                enumField(candidates, selection.getLabelFieldId()).getColumn(), null, null,
                object.getTenantColumn(), object.getDeleteColumn());
    }

    Map<String, Map<String, String>> freezeColumnIdentities(LimsElnReadonlyResolver.FrozenDefinition definition) {
        requireManager();
        return metadata.execute((org.springframework.jdbc.core.ConnectionCallback<Map<String, Map<String, String>>>)
                connection -> LimsElnSourceExecution.columnIdentities(connection, definition, definition.getFields().keySet()));
    }

    private FormHints formHints(String table) {
        FormHints hints = new FormHints();
        List<String[]> forms = configuration.query("SELECT f_tables_data, f_form_data, f_db_link_id FROM base_visual_release "
                        + "WHERE f_tenant_id = '0' AND f_state = 1 AND (f_delete_mark IS NULL OR f_delete_mark = 0) "
                        + "AND strpos(f_tables_data, ?) > 0",
                (row, index) -> new String[]{row.getString(1), row.getString(2), row.getString(3)}, table);
        for (String[] form : forms) {
            try {
                if (form[0] == null || form[0].trim().isEmpty()) continue;
                JsonNode tables = json.readTree(form[0]);
                if (tables == null || !tables.isArray()) throw new DataException("Published source form metadata is invalid");
                String main = null;
                boolean matching = false;
                for (JsonNode entry : tables) {
                    String name = entry.path("table").asText();
                    if ("1".equals(entry.path("typeId").asText())) main = name;
                    if (table.equals(name)) matching = true;
                }
                if (matching) {
                    JsonNode definition = json.readTree(form[1]);
                    if (definition == null || !definition.isObject() || !definition.path("fields").isArray()) {
                        throw new DataException("Published source form metadata is invalid");
                    }
                    // Unknown connections contribute restrictions, never names or executable lookup rules.
                    boolean trustedNames = form[2] == null || form[2].isEmpty() || "0".equals(form[2]);
                    collectHints(definition.path("fields"), table, main, hints, 0, trustedNames);
                }
            } catch (JsonProcessingException | IllegalArgumentException e) {
                throw new DataException("Published source form metadata is invalid");
            }
        }
        hints.nameOptions.keySet().stream().filter(hints.nameLookups::containsKey).forEach(hints.conflictingNames::add);
        hints.dynamicNames.stream().filter(column -> hints.nameOptions.containsKey(column)
                || hints.nameLookups.containsKey(column)).forEach(hints.conflictingNames::add);
        for (String column : hints.conflictingNames) {
            hints.nameOptions.remove(column);
            hints.nameLookups.remove(column);
        }
        return hints;
    }

    private void collectHints(JsonNode fields, String table, String inheritedTable,
            FormHints hints, int depth, boolean trustedNames) {
        if (depth > 20) throw new DataException("Source form nesting exceeds limits");
        if (!fields.isArray()) throw new DataException("Published source form metadata is invalid");
        for (JsonNode field : fields) {
            JsonNode config = field.path("__config__");
            if (!field.isObject() || !config.isObject()) {
                throw new DataException("Published source form metadata is invalid");
            }
            String fieldTable = config.path("tableName").asText("");
            if (fieldTable.isEmpty()) fieldTable = inheritedTable;
            String column = field.path("__vModel__").asText();
            String label = config.path("label").asText();
            if (table.equals(fieldTable) && !column.isEmpty()) {
                String control = config.path("jnpfKey").asText();
                if (field.path("multiple").asBoolean() || field.path("showPassword").asBoolean()
                        || "password".equals(field.path("type").asText())
                        || "signature".equals(control) || "sign".equals(control) || "checkbox".equals(control)
                        || "uploadImg".equals(control) || "uploadFile".equals(control)
                        || config.path("isVirtualField").asBoolean()) {
                    hints.restricted.add(column);
                }
                if (trustedNames && !label.trim().isEmpty()) {
                    hints.labels.merge(column, label, (previous, next) -> previous.compareTo(next) <= 0 ? previous : next);
                }
                if (trustedNames && ("select".equals(control) || "radio".equals(control))
                        && "dynamic".equals(config.path("dataType").asText())) {
                    hints.dynamicNames.add(column);
                }
                if (trustedNames && ("select".equals(control) || "radio".equals(control))
                        && "static".equals(config.path("dataType").asText())) {
                    Map<String, String> names = staticNames(field);
                    Map<String, String> previous = hints.nameOptions.putIfAbsent(column, names);
                    if (previous != null) {
                        names.forEach((code, name) -> {
                            String existing = previous.putIfAbsent(code, name);
                            if (existing != null && !existing.equals(name)) hints.conflictingNames.add(column);
                        });
                        if (previous.size() > 1000) {
                            throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_LIMIT", "Too many source enum options");
                        }
                    }
                }
                if (trustedNames && ("select".equals(control) || "radio".equals(control))
                        && "dictionary".equals(config.path("dataType").asText())) {
                    String key = field.path("props").path("value").asText();
                    String category = config.path("dictionaryType").asText("");
                    if (!("id".equals(key) || "enCode".equals(key)) || category.trim().isEmpty()
                            || !"fullName".equals(field.path("props").path("label").asText())) {
                        throw new DataException("Invalid source dictionary metadata");
                    }
                    NameLookup lookup = platformNameLookup("base_dictionary_data", "id".equals(key) ? "f_id" : "f_en_code",
                            "f_full_name", "f_dictionary_type_id", category);
                    NameLookup previous = hints.nameLookups.putIfAbsent(column, lookup);
                    if (previous != null && !previous.equals(lookup)) {
                        hints.conflictingNames.add(column);
                    }
                }
                if (trustedNames && "userSelect".equals(control) && !hints.restricted.contains(column)) {
                    NameLookup lookup = platformNameLookup("base_user", "f_id", "f_real_name", null, null);
                    NameLookup previous = hints.nameLookups.putIfAbsent(column, lookup);
                    if (previous != null && !previous.equals(lookup)) {
                        hints.conflictingNames.add(column);
                    }
                }
            }
            JsonNode children = config.path("children");
            if (!children.isMissingNode() && !children.isNull()) {
                collectHints(children, table, fieldTable, hints, depth + 1, trustedNames);
            }
        }
    }

    private static class FormHints {
        private final Map<String, String> labels = new LinkedHashMap<>();
        private final Set<String> restricted = new HashSet<>();
        private final Map<String, Map<String, String>> nameOptions = new LinkedHashMap<>();
        private final Map<String, NameLookup> nameLookups = new LinkedHashMap<>();
        private final Set<String> conflictingNames = new HashSet<>();
        private final Set<String> dynamicNames = new HashSet<>();
    }

    private NameLookup platformNameLookup(String table, String key, String label, String qualifier, String value) {
        List<String> schemas = configuration.query("SELECT n.nspname FROM pg_catalog.pg_class c "
                        + "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
                        + "WHERE c.oid = pg_catalog.to_regclass(?) AND c.relkind IN ('r', 'p')",
                (row, index) -> row.getString(1), table);
        if (schemas.size() != 1) throw new DataException("Source display-name object is unavailable");
        return new NameLookup(schemas.get(0), table, key, label, qualifier, value, "f_tenant_id", "f_delete_mark");
    }

    private static Map<String, String> staticNames(JsonNode field) {
        JsonNode options = field.path("options");
        String key = field.path("props").path("value").asText("");
        String label = field.path("props").path("label").asText("");
        if (!options.isArray() || options.size() > 1000 || key.isEmpty() || label.isEmpty()) {
            throw new DataException("Invalid source display-name metadata");
        }
        Map<String, String> names = new LinkedHashMap<>();
        for (JsonNode option : options) {
            if (!option.path(key).isTextual() || !option.path(label).isTextual()
                    || option.path(key).asText().trim().isEmpty() || option.path(label).asText().trim().isEmpty()
                    || names.putIfAbsent(option.path(key).asText(), option.path(label).asText()) != null) {
                throw new DataException("Invalid or ambiguous source display-name metadata");
            }
        }
        return names;
    }

    ObjectMapping objectMapping(String objectId) {
        requireManager();
        List<ObjectMapping> result = configuration.query("SELECT schema_name, table_name, id_column, tenant_column, root_anchor, delete_column "
                        + "FROM lims_eln_provider_object WHERE f_tenant_id = '0' AND object_id = ? AND biz_enabled = true",
                (row, index) -> new ObjectMapping(row.getString(1), row.getString(2), row.getString(3),
                        row.getString(4), row.getBoolean(5), row.getString(6)), objectId);
        if (result.size() != 1) throw new DataException("Source object is not registered");
        return result.get(0);
    }

    private void requireManager() {
        LimsElnSourceCatalog.Actor actor = actors.get();
        if (actor == null || !"0".equals(actor.getTenantId()) || actor.getUserId() == null
                || actor.getUserId().trim().isEmpty() || !actor.isFieldManager()) {
            throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_FORBIDDEN", "Field management permission required");
        }
    }

    private static String dataType(String postgresType) {
        switch (postgresType) {
            case "text": case "varchar": case "bpchar": return "TEXT";
            case "numeric": case "int2": case "int4": case "int8": return "DECIMAL";
            case "date": return "DATE";
            case "timestamp": case "timestamptz": return "DATETIME";
            case "bool": return "BOOLEAN";
            default: return null;
        }
    }

    @Value
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class BusinessObject {
        String id;
        String displayName;
        boolean rootAnchor;
    }

    @Value
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Candidate {
        String id;
        String displayName;
        String dataType;
        @JsonIgnore
        String postgresType;
        @JsonIgnore
        String column;
        Map<String, String> nameOptions;
        @JsonIgnore
        NameLookup nameLookup;
        boolean nameRuleConflict;

        public boolean isDisplayNameAvailable() {
            return "TEXT".equals(dataType) && (nameLookup != null || nameOptions != null && !nameOptions.isEmpty());
        }
    }

    @Value
    static class NameLookup {
        String schema;
        String table;
        String keyColumn;
        String labelColumn;
        String qualifierColumn;
        String qualifierValue;
        String tenantColumn;
        String deleteColumn;
    }

    @lombok.Data
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class EnumSelection {
        private String objectId;
        private String valueFieldId;
        private String labelFieldId;
    }

    @Value
    static class ObjectMapping {
        String schema;
        String table;
        String idColumn;
        String tenantColumn;
        boolean rootAnchor;
        String deleteColumn;
    }
}