package jnpf.limsService.provider;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import jnpf.audit.sdk.annotation.AuditLog;
import jnpf.exception.DataException;
import jnpf.limsService.LimsTestWorkItemSnapshotService;
import jnpf.limsService.provider.LimsElnReadonlyResolver.Field;
import jnpf.limsService.provider.LimsElnReadonlyResolver.FrozenDefinition;
import jnpf.limsService.provider.LimsElnReadonlyResolver.Snapshot;
import jnpf.limsService.provider.LimsElnReadonlyResolver.Join;
import jnpf.limsService.provider.LimsElnReadonlyResolver.Condition;
import lombok.Data;
import lombok.Value;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.support.TransactionTemplate;

import javax.sql.DataSource;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Collections;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.function.Supplier;

/** Dataset publication and property preview share the same authorized read implementation. */
public class LimsElnDatasetCatalog implements LimsElnSourceCatalog.SourceReader {
    private static final RowMapper<Version> VERSION = (row, index) -> new Version(row.getString("f_id"),
            row.getString("dataset_code"), row.getInt("revision"), row.getString("display_name"), row.getString("status"),
            row.getString("definition_json"), row.getString("frozen_definition_json"), row.getInt("edit_version"),
            row.getString("validation_sample_id"), row.getString("published_by"), row.getString("published_at"));
    private final JdbcTemplate jdbc;
    private final TransactionTemplate transactions;
    private final LimsElnSourceMetadata metadata;
    private final LimsElnSourceExecution execution;
    private final Supplier<LimsElnSourceCatalog.Actor> actors;
    private final ObjectMapper json = new ObjectMapper();

    public LimsElnDatasetCatalog(DataSource configurationDataSource, DataSource readOnlyDataSource,
            LimsElnSourceMetadata metadata, LimsTestWorkItemSnapshotService taskAccess,
            Supplier<LimsElnSourceCatalog.Actor> actors) {
        jdbc = new JdbcTemplate(configurationDataSource);
        jdbc.setQueryTimeout(5);
        transactions = new TransactionTemplate(new DataSourceTransactionManager(configurationDataSource));
        this.metadata = metadata;
        execution = new LimsElnSourceExecution(readOnlyDataSource, taskAccess);
        this.actors = actors;
    }

    @AuditLog(action = "ELN_DATASET_DRAFT_CREATE", bizType = "ELN_SOURCE_DATASET", bizCodeExpr = "#result?.code",
            targetTable = "lims_eln_provider_dataset_version", targetIdExpr = "#result?.id")
    public Version createDraft(Draft draft) {
        requireManager();
        validate(draft);
        String id = UUID.randomUUID().toString();
        String definitionJson = encode(draft);
        try {
            transactions.execute(status -> {
                jdbc.update("INSERT INTO lims_eln_provider_dataset (dataset_code) VALUES (?)", draft.getCode());
                jdbc.update("INSERT INTO lims_eln_provider_dataset_version "
                                + "(f_id, dataset_code, revision, display_name, status, definition_json) VALUES (?, ?, 1, ?, 'DRAFT', ?)",
                        id, draft.getCode(), draft.getDisplayName(), definitionJson);
                return null;
            });
        } catch (DuplicateKeyException e) {
            throw new DataException("Dataset identity already exists");
        }
        return detail(id);
    }

    @AuditLog(action = "ELN_SOURCE_PATH_CREATE", failureAction = "ELN_SOURCE_PATH_CREATE_FAILED",
            bizType = "ELN_SOURCE_DATASET", bizCodeExpr = "#result?.code", bizCodeRequired = false,
            targetTable = "lims_eln_provider_dataset_version", targetIdExpr = "#result?.id")
    public Version createPublished(Draft draft, String sampleId) {
        return transactions.execute(status -> {
            Version created = createDraft(draft);
            return publish(created.getId(), created.getEditVersion(), sampleId);
        });
    }

    public Version detail(String id) {
        requireManager();
        List<Version> versions = jdbc.query("SELECT * FROM lims_eln_provider_dataset_version "
                + "WHERE f_tenant_id = '0' AND f_id = ?", VERSION, id);
        if (versions.size() != 1) throw new DataException("Dataset version not found");
        return versions.get(0);
    }

    public List<Version> versions() {
        requireManager();
        return jdbc.query("SELECT * FROM lims_eln_provider_dataset_version WHERE f_tenant_id = '0' ORDER BY dataset_code, revision", VERSION);
    }

    Version version(String code, int revision) {
        requireManager();
        List<Version> versions = jdbc.query("SELECT * FROM lims_eln_provider_dataset_version "
                        + "WHERE f_tenant_id = '0' AND dataset_code = ? AND revision = ?",
                VERSION, code, revision);
        if (versions.size() != 1) throw new DataException("Source path version not found");
        return versions.get(0);
    }

    Draft definition(Version version) {
        requireManager();
        return decode(version.getDefinitionJson(), Draft.class);
    }

    @AuditLog(action = "ELN_DATASET_VERSION_CREATE", bizType = "ELN_SOURCE_DATASET", bizCodeExpr = "#result?.code",
            targetTable = "lims_eln_provider_dataset_version", targetIdExpr = "#result?.id")
    public Version newVersion(String publishedId, Draft changes) {
        Version previous = detail(publishedId);
        validate(changes);
        if (!"PUBLISHED".equals(previous.getStatus())) throw new DataException("Published dataset required");
        if (!previous.getCode().equals(changes.getCode())) throw new DataException("Dataset code is immutable");
        String id = UUID.randomUUID().toString();
        Integer revision = jdbc.queryForObject("SELECT MAX(revision) + 1 FROM lims_eln_provider_dataset_version "
                + "WHERE f_tenant_id = '0' AND dataset_code = ?", Integer.class, previous.getCode());
        try {
            jdbc.update("INSERT INTO lims_eln_provider_dataset_version "
                            + "(f_id, dataset_code, revision, display_name, status, definition_json) VALUES (?, ?, ?, ?, 'DRAFT', ?)",
                    id, previous.getCode(), revision, changes.getDisplayName(), encode(changes));
        } catch (DuplicateKeyException e) {
            throw new DataException("Dataset already has a draft or changed concurrently");
        }
        return detail(id);
    }

    @AuditLog(action = "ELN_DATASET_DRAFT_UPDATE", bizType = "ELN_SOURCE_DATASET", bizCodeExpr = "#result?.code",
            targetTable = "lims_eln_provider_dataset_version", targetIdExpr = "#id")
    public Version updateDraft(String id, int expectedEditVersion, Draft changes) {
        Version previous = detail(id);
        validate(changes);
        if (!"DRAFT".equals(previous.getStatus())) throw new DataException("Published dataset is immutable");
        if (previous.getEditVersion() != expectedEditVersion) throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_CONFLICT", "Dataset version has changed");
        if (!previous.getCode().equals(changes.getCode())) throw new DataException("Dataset code is immutable");
        if (jdbc.update("UPDATE lims_eln_provider_dataset_version SET display_name = ?, definition_json = ?, "
                        + "edit_version = edit_version + 1 WHERE f_tenant_id = '0' AND f_id = ? AND status = 'DRAFT' AND edit_version = ?",
                changes.getDisplayName(), encode(changes), id, expectedEditVersion) != 1) {
            throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_CONFLICT", "Dataset version has changed");
        }
        return detail(id);
    }

    @AuditLog(action = "ELN_DATASET_PUBLISH", failureAction = "ELN_DATASET_PUBLISH_FAILED", bizType = "ELN_SOURCE_DATASET",
            bizCodeExpr = "#result?.code", bizCodeRequired = false,
            targetTable = "lims_eln_provider_dataset_version", targetIdExpr = "#id")
    public Version publish(String id, int expectedEditVersion, String sampleId) {
        LimsElnSourceCatalog.Actor actor = requireManager();
        Version draft = detail(id);
        if (!"DRAFT".equals(draft.getStatus())) throw new DataException("Published dataset is immutable");
        if (draft.getEditVersion() != expectedEditVersion) throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_CONFLICT", "Dataset version has changed");
        FrozenDefinition frozen = compile(decode(draft.getDefinitionJson(), Draft.class), draft.getRevision());
        List<Snapshot> preview = preview(frozen, actor, sampleId, new ArrayList<>(frozen.getFields().keySet()));
        if (preview.stream().anyMatch(value -> !("SUCCESS".equals(value.getOutcomeCode()) || "MISSING".equals(value.getOutcomeCode())))
                || preview.stream().noneMatch(value -> "SUCCESS".equals(value.getOutcomeCode()))) {
            throw new DataException("Dataset requires an authorized, applicable preview with a value");
        }
        frozen.setResultField(null);
        if (jdbc.update("UPDATE lims_eln_provider_dataset_version SET status = 'PUBLISHED', frozen_definition_json = ?, "
                        + "validation_sample_id = ?, published_by = ?, published_at = ?, edit_version = edit_version + 1 "
                        + "WHERE f_tenant_id = '0' AND f_id = ? AND status = 'DRAFT' AND edit_version = ?",
                encode(frozen), sampleId, actor.getUserId(), Instant.now().toString(), id, expectedEditVersion) != 1) {
            throw new LimsElnReadonlyResolver.SourceFailure("SOURCE_CONFLICT", "Dataset version has changed");
        }
        return detail(id);
    }

    public Validation validateStructure(String id) {
        Version draft = detail(id);
        if (!"DRAFT".equals(draft.getStatus())) throw new DataException("Draft dataset required");
        compile(decode(draft.getDefinitionJson(), Draft.class), draft.getRevision());
        return new Validation(true, false);
    }

    public List<Snapshot> preview(String id, String sampleId) {
        LimsElnSourceCatalog.Actor actor = requireManager();
        Version version = detail(id);
        FrozenDefinition frozen = "PUBLISHED".equals(version.getStatus())
                ? decode(version.getFrozenDefinitionJson(), FrozenDefinition.class)
                : compile(decode(version.getDefinitionJson(), Draft.class), version.getRevision());
        return preview(frozen, actor, sampleId, new ArrayList<>(frozen.getFields().keySet()));
    }

    @Override
    public LimsElnSourceCatalog.Preview read(LimsElnSourceCatalog.Definition definition,
            LimsElnSourceCatalog.Actor actor, String sampleId) {
        if (!requireManager().equals(actor)) throw new DataException("Invalid source preview caller");
        if (definition == null || definition.getDatasetCode() == null || definition.getDatasetRevision() == null) {
            throw new DataException("A fixed dataset version is required");
        }
        List<Version> versions = jdbc.query("SELECT * FROM lims_eln_provider_dataset_version "
                        + "WHERE f_tenant_id = '0' AND dataset_code = ? AND revision = ? AND status = 'PUBLISHED'",
                VERSION, definition.getDatasetCode(), definition.getDatasetRevision());
        if (versions.size() != 1) throw new DataException("Published dataset version not found");
        FrozenDefinition frozen = decode(versions.get(0).getFrozenDefinitionJson(), FrozenDefinition.class);
        List<Snapshot> values = preview(frozen, actor, sampleId, Collections.singletonList(definition.getFieldId()));
        if (values.size() != 1 || !"SUCCESS".equals(values.get(0).getOutcomeCode())) {
            throw new DataException("Source requires an authorized, applicable preview with a value");
        }
        frozen.setResultField(definition.getFieldId());
        return new LimsElnSourceCatalog.Preview(Collections.singletonList(values.get(0).getValue()), encode(frozen));
    }

    private List<Snapshot> preview(FrozenDefinition frozen, LimsElnSourceCatalog.Actor actor,
            String sampleId, List<String> keys) {
        if (sampleId == null || sampleId.trim().isEmpty()) throw new DataException("An authorized preview sample is required");
        List<LimsElnSourceCatalog.Version> previews = new ArrayList<>();
        for (String key : keys) {
            Field field = frozen.getFields().get(key);
            if (field == null) throw new DataException("Dataset output field not found");
            frozen.setResultField(key);
            previews.add(new LimsElnSourceCatalog.Version("preview-" + key, "0", key, 1, "PREVIEW", key,
                    field.getDataType(), "{}", encode(frozen), null, null, null, 0));
        }
        return execution.execute(Collections.singletonList(sampleId), previews, actor);
    }

    private FrozenDefinition compile(Draft draft, int revision) {
        validate(draft);
        LimsElnSourceMetadata.ObjectMapping root = metadata.objectMapping(draft.getRootObjectId());
        if (!root.isRootAnchor()) throw new DataException("Dataset root must be the registered test work item");
        FrozenDefinition frozen = new FrozenDefinition();
        frozen.setDatasetCode(draft.getCode());
        frozen.setDatasetRevision(revision);
        frozen.setSchema(root.getSchema());
        frozen.setTable(root.getTable());
        frozen.setIdColumn(root.getIdColumn());
        frozen.setTenantColumn(root.getTenantColumn());
        frozen.setDeleteColumn(root.getDeleteColumn());
        List<List<LimsElnSourceMetadata.Candidate>> steps = new ArrayList<>();
        steps.add(metadata.fields(draft.getRootObjectId()));
        for (Relation relation : draft.getRelations()) {
            if (relation == null || relation.getFromStep() < 0 || relation.getFromStep() >= steps.size()) {
                throw new DataException("Invalid dataset relation");
            }
            LimsElnSourceMetadata.ObjectMapping target = metadata.objectMapping(relation.getObjectId());
            List<LimsElnSourceMetadata.Candidate> targetFields = metadata.fields(relation.getObjectId());
            LimsElnSourceMetadata.Candidate from = candidate(steps.get(relation.getFromStep()), relation.getFromFieldId());
            LimsElnSourceMetadata.Candidate to = candidate(targetFields, relation.getTargetFieldId());
            if (!from.getDataType().equals(to.getDataType())) throw new DataException("Incompatible dataset relation fields");
            Join join = new Join();
            join.setSchema(target.getSchema());
            join.setTable(target.getTable());
            join.setTenantColumn(target.getTenantColumn());
            join.setDeleteColumn(target.getDeleteColumn());
            join.setFromStep(relation.getFromStep());
            join.setFromColumn(from.getColumn());
            join.setTargetColumn(to.getColumn());
            frozen.getJoins().add(join);
            steps.add(targetFields);
        }
        Map<String, Field> fields = new LinkedHashMap<>();
        for (Map.Entry<String, Selection> entry : draft.getFields().entrySet()) {
            Selection selection = entry.getValue();
            if (selection.getStep() < 0 || selection.getStep() >= steps.size()) throw new DataException("Invalid dataset field step");
            LimsElnSourceMetadata.Candidate candidate = candidate(steps.get(selection.getStep()), selection.getFieldId());
            Field field = new Field();
            field.setColumn(candidate.getColumn());
            field.setDataType(candidate.getDataType());
            field.setStep(selection.getStep());
            LimsElnSourceMetadata.NameLookup lookup = candidate.getNameLookup();
            if (selection.getEnumSource() != null) {
                if (!"TEXT".equals(candidate.getDataType())) throw new DataException("Source enum value must be text");
                lookup = metadata.enumNameLookup(selection.getEnumSource());
            }
            if ("DISPLAY_NAME".equals(selection.getValueMode())) {
                if (!candidate.isDisplayNameAvailable() && lookup == null) {
                    throw new DataException("Source display name is unavailable");
                }
                field.setValueMode("DISPLAY_NAME");
                if (lookup == null) {
                    field.setNameOptions(candidate.getNameOptions());
                } else {
                    Join join = new Join();
                    join.setSchema(lookup.getSchema());
                    join.setTable(lookup.getTable());
                    join.setTenantColumn(lookup.getTenantColumn());
                    join.setDeleteColumn(lookup.getDeleteColumn());
                    join.setFromStep(selection.getStep());
                    join.setFromColumn(candidate.getColumn());
                    join.setTargetColumn(lookup.getKeyColumn());
                    join.setQualifierColumn(lookup.getQualifierColumn());
                    join.setQualifierValue(lookup.getQualifierValue());
                    int index = frozen.getJoins().indexOf(join);
                    if (index < 0) {
                        if (frozen.getJoins().size() >= 4) throw new DataException("Source relation count exceeds limits");
                        index = frozen.getJoins().size();
                        frozen.getJoins().add(join);
                    }
                    field.setColumn(lookup.getLabelColumn());
                    field.setStep(index + 1);
                }
            } else if (!"RAW".equals(selection.getValueMode())) {
                throw new DataException("Invalid source value mode");
            }
            if ("DATE_ONLY".equals(selection.getFormat()) && "timestamp".equals(candidate.getPostgresType())) {
                field.setDataType("DATE");
            } else if (!("RAW".equals(selection.getFormat())
                    || "PLAIN_DECIMAL".equals(selection.getFormat()) && "DECIMAL".equals(field.getDataType())
                    || "ISO_DATE".equals(selection.getFormat()) && "DATE".equals(field.getDataType()))) {
                throw new DataException("Invalid source format for field type");
            }
            field.setFormat(selection.getFormat());
            fields.put(entry.getKey(), field);
        }
        frozen.setFields(fields);
        frozen.setScopeMode(draft.getScopeMode());
        frozen.setConditions(draft.getConditions());
        LimsElnSourceExecution.validateScope(frozen);
        frozen.setColumnIdentities(metadata.freezeColumnIdentities(frozen));
        return frozen;
    }

    private static LimsElnSourceMetadata.Candidate candidate(List<LimsElnSourceMetadata.Candidate> fields, String id) {
        return fields.stream().filter(field -> field.getId().equals(id)).findFirst()
                .orElseThrow(() -> new DataException("Source candidate is unavailable"));
    }

    private LimsElnSourceCatalog.Actor 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");
        }
        return actor;
    }

    private static void validate(Draft draft) {
        if (draft == null || draft.getCode() == null || !draft.getCode().matches("[a-zA-Z][a-zA-Z0-9_.-]{0,79}")
                || draft.getDisplayName() == null || draft.getDisplayName().trim().isEmpty() || draft.getDisplayName().length() > 200
                || draft.getRootObjectId() == null || draft.getFields() == null || draft.getFields().isEmpty()
                || draft.getRelations() == null || draft.getRelations().size() > 4
                || draft.getFields().size() > 50 || draft.getFields().entrySet().stream().anyMatch(entry -> entry.getKey() == null
                        || !entry.getKey().matches("[a-zA-Z][a-zA-Z0-9_.-]{0,79}") || entry.getValue() == null
                        || entry.getValue().getFieldId() == null)) {
            throw new DataException("Invalid dataset definition");
        }
    }

    private String encode(Object value) {
        try { return json.writeValueAsString(value); }
        catch (JsonProcessingException e) { throw new DataException("Invalid dataset definition"); }
    }

    private <T> T decode(String value, Class<T> type) {
        try { return json.readValue(value, type); }
        catch (JsonProcessingException e) { throw new DataException("Invalid dataset definition"); }
    }

    @Data
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Draft {
        private String code;
        private String displayName;
        private String rootObjectId;
        private Map<String, Selection> fields = new LinkedHashMap<>();
        private List<Relation> relations = new ArrayList<>();
        private String scopeMode = "ALL";
        private List<Condition> conditions = new ArrayList<>();
    }

    @Data
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Selection {
        private String fieldId;
        private int step;
        private String valueMode = "RAW";
        private String format = "RAW";
        private LimsElnSourceMetadata.EnumSelection enumSource;
    }

    @Data
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Relation {
        private String objectId;
        private int fromStep;
        private String fromFieldId;
        private String targetFieldId;
    }

    @Value
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Validation {
        boolean structureValid;
        boolean previewValidated;
    }

    @Value
    @com.fasterxml.jackson.databind.annotation.JsonNaming(com.fasterxml.jackson.databind.PropertyNamingStrategies.LowerCamelCaseStrategy.class)
    public static class Version {
        String id;
        String code;
        int revision;
        String displayName;
        String status;
        String definitionJson;
        @JsonIgnore
        String frozenDefinitionJson;
        int editVersion;
        String validationSampleId;
        String publishedBy;
        String publishedAt;
    }
}