package jnpf.limsService.provider;

import jnpf.exception.DataException;
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.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class JdbcLimsElnSourceStore implements LimsElnSourceCatalog.Store {
    private static final String VERSION_COLUMNS = "f_id, f_tenant_id, property_code, revision, status, "
            + "display_name, data_type, definition_json, frozen_definition_json, validation_sample_id, "
            + "published_by, published_at, edit_version";
    private static final RowMapper<LimsElnSourceCatalog.Version> VERSION = (row, index) ->
            new LimsElnSourceCatalog.Version(row.getString("f_id"), row.getString("f_tenant_id"),
                    row.getString("property_code"), row.getInt("revision"), row.getString("status"),
                    row.getString("display_name"), row.getString("data_type"), row.getString("definition_json"),
                    row.getString("frozen_definition_json"), row.getString("validation_sample_id"),
                    row.getString("published_by"), row.getString("published_at"), row.getInt("edit_version"));

    private final JdbcTemplate jdbc;
    private final TransactionTemplate transactions;

    public JdbcLimsElnSourceStore(DataSource dataSource) {
        jdbc = new JdbcTemplate(dataSource);
        jdbc.setQueryTimeout(5);
        transactions = new TransactionTemplate(new DataSourceTransactionManager(dataSource));
    }

    @Override
    public void insert(LimsElnSourceCatalog.Version version) {
        try {
            transactions.execute(status -> {
                if (version.getRevision() == 1) {
                    jdbc.update("INSERT INTO lims_eln_provider_property (f_tenant_id, property_code) VALUES (?, ?)",
                            version.getTenantId(), version.getPropertyCode());
                }
                jdbc.update("INSERT INTO lims_eln_provider_property_version (" + VERSION_COLUMNS
                                + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", version.getId(),
                        version.getTenantId(), version.getPropertyCode(), version.getRevision(), version.getStatus(),
                        version.getDisplayName(), version.getDataType(), version.getDefinitionJson(),
                        version.getFrozenDefinitionJson(), version.getValidationSampleId(), version.getPublishedBy(),
                        version.getPublishedAt(), version.getEditVersion());
                return null;
            });
        } catch (DuplicateKeyException e) {
            throw new DataException("Source identity or draft version already exists");
        }
    }

    @Override
    public LimsElnSourceCatalog.Version find(String tenantId, String id) {
        List<LimsElnSourceCatalog.Version> versions = jdbc.query("SELECT " + VERSION_COLUMNS
                + " FROM lims_eln_provider_property_version WHERE f_tenant_id = ? AND f_id = ?", VERSION, tenantId, id);
        return versions.isEmpty() ? null : versions.get(0);
    }

    @Override
    public List<LimsElnSourceCatalog.Version> list(String tenantId) {
        return jdbc.query("SELECT " + VERSION_COLUMNS
                + " FROM lims_eln_provider_property_version WHERE f_tenant_id = ? ORDER BY property_code, revision",
                VERSION, tenantId);
    }

    @Override
    public boolean replaceDraft(LimsElnSourceCatalog.Version expected, LimsElnSourceCatalog.Version replacement) {
        return jdbc.update("UPDATE lims_eln_provider_property_version SET status = ?, display_name = ?, "
                        + "data_type = ?, definition_json = ?, frozen_definition_json = ?, validation_sample_id = ?, "
                        + "published_by = ?, published_at = ?, edit_version = ? "
                        + "WHERE f_id = ? AND f_tenant_id = ? AND property_code = ? AND revision = ? "
                        + "AND status = 'DRAFT' AND edit_version = ?", replacement.getStatus(),
                replacement.getDisplayName(), replacement.getDataType(), replacement.getDefinitionJson(),
                replacement.getFrozenDefinitionJson(), replacement.getValidationSampleId(), replacement.getPublishedBy(),
                replacement.getPublishedAt(), replacement.getEditVersion(), expected.getId(), expected.getTenantId(),
                expected.getPropertyCode(), expected.getRevision(), expected.getEditVersion()) == 1;
    }

    @Override
    public boolean enabled(String tenantId, String code) {
        List<Boolean> result = jdbc.query("SELECT biz_enabled FROM lims_eln_provider_property "
                        + "WHERE f_tenant_id = ? AND property_code = ?",
                (row, index) -> row.getBoolean(1), tenantId, code);
        return result.size() == 1 && result.get(0);
    }

    @Override
    public Map<String, Boolean> enabledStates(String tenantId) {
        Map<String, Boolean> result = new LinkedHashMap<>();
        jdbc.query("SELECT property_code, biz_enabled FROM lims_eln_provider_property WHERE f_tenant_id = ?",
                (org.springframework.jdbc.core.RowCallbackHandler) row ->
                        result.put(row.getString(1), row.getBoolean(2)), tenantId);
        return result;
    }

    @Override
    public void setEnabled(String tenantId, String code, boolean enabled) {
        if (jdbc.update("UPDATE lims_eln_provider_property SET biz_enabled = ? "
                + "WHERE f_tenant_id = ? AND property_code = ?", enabled, tenantId, code) != 1) {
            throw new DataException("Source property not found");
        }
    }
}