package com.bcxin.backend.domain.syncs.components;

import com.alibaba.fastjson.JSON;
import com.bcxin.backend.core.utils.ExceptionUtils;
import com.bcxin.backend.domain.system.configs.DispatchSiteResourceProperties;
import com.bcxin.event.entities.EventSourceEntity;
import com.bcxin.event.enums.EventAction;
import com.bcxin.event.repositories.EventSourceRepository;
import com.bcxin.runtime.domain.constants.FieldNames;
import com.bcxin.runtime.domain.metas.entities.DataSourceMetaEntity;
import com.bcxin.runtime.domain.metas.entities.FormMetaEntity;
import com.bcxin.runtime.domain.metas.entities.FormSyncMetaEntity;
import com.bcxin.runtime.domain.snapshoots.JdbcConnectionSnapshot;
import com.bcxin.runtime.domain.syncs.dtos.DataSetDto;
import com.bcxin.saas.core.components.JsonProvider;
import com.bcxin.saas.core.exceptions.SaasBadException;
import com.bcxin.saas.core.exceptions.SaasNofoundException;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.Date;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

@Component
public class FormDataExporterImpl implements IFormDataExporter {
    private final static Logger logger = LoggerFactory.getLogger(FormDataExporterImpl.class);
    public final static int PAGE_SIZE = 20;
    public final static String SYNC_DEPARTMENT_VIEW = "sync_department_view";

    private static Map<String, DataSource> dataSourceMap = new ConcurrentHashMap<>();

    private final JsonProvider jsonProvider;
    private final EventSourceRepository eventSourceRepository;
    private final DispatchSiteResourceProperties dispatchSiteResourceProperties;

    public FormDataExporterImpl(JsonProvider jsonProvider,
                                EventSourceRepository eventSourceRepository,
                                DispatchSiteResourceProperties dispatchSiteResourceProperties) {
        this.jsonProvider = jsonProvider;
        this.eventSourceRepository = eventSourceRepository;
        this.dispatchSiteResourceProperties = dispatchSiteResourceProperties;
    }

    @Override
    public DataSetDto export(FormSyncMetaEntity formSyncMetaEntity, Timestamp startDateTime, int pageIndex) {
        DataSource dataSource = buildDataSource(formSyncMetaEntity.getFormMeta());
        try {  
            return getDataSet(formSyncMetaEntity, dataSource, startDateTime, pageIndex);
        } catch (SQLException ex) {
            throw new SaasBadException(String.format("FormData 导出异常:%s", ExceptionUtils.getStackMessage(ex)), ex);
        }
    }

    private String buildSql(FormSyncMetaEntity formSyncMetaEntity,
                            String connectionJdbcUrl, Timestamp startDateTime, int pageIndex) {
        int pageSize = 0;
        if (SYNC_DEPARTMENT_VIEW.equalsIgnoreCase(formSyncMetaEntity.getFormMeta().getTableName())) {
            pageSize = 200;
        } else {
            pageSize = PAGE_SIZE;
        }
        String pagedSql = String.format(" offset %s limit %s", pageIndex * pageSize, pageSize);
        if (connectionJdbcUrl.contains("mysql:")) {
            pagedSql = String.format(" limit %s,%s", pageIndex * pageSize, pageSize);
        }

        String condition = "";
        if (StringUtils.hasLength(formSyncMetaEntity.getFilter())) {
            condition = formSyncMetaEntity.getFilter();
        }

        Timestamp startTime = startDateTime;//Timestamp.from(startDateTime.toInstant().minus(2, ChronoUnit.MINUTES));
        String sql = null;
        if (SYNC_DEPARTMENT_VIEW.equalsIgnoreCase(formSyncMetaEntity.getFormMeta().getTableName())) {
            sql = String.format("SELECT * FROM (select * from %s where LASTMODIFIED>='%s' %s) AS A ORDER BY LEVEL ASC %s ",
                    formSyncMetaEntity.getFormMeta().getTableName(), startTime, condition, pagedSql);
        } else {
            sql = String.format("SELECT * FROM (select * from %s where LASTMODIFIED>='%s' %s) AS A ORDER BY LASTMODIFIED ASC %s ",
                    formSyncMetaEntity.getFormMeta().getTableName(), startTime, condition, pagedSql);
        }

        return sql;
    }

    private DataSource buildDataSource(FormMetaEntity formMetaEntity) {
        DataSourceMetaEntity dataSourceMetaEntity = formMetaEntity.getDefaultDataSource();
        if (dataSourceMetaEntity == null) {
            throw new SaasNofoundException(String.format("同步表%s找不到数据源，考虑外键关联meta_datasources是否有问题", formMetaEntity.getTableName()));
        }

        JdbcConnectionSnapshot snapshot = this.jsonProvider.getData(dataSourceMetaEntity.getConfig(), JdbcConnectionSnapshot.class);
        String dataSourceKey = String.format("url:%s-username:%s-ps:%s", snapshot.getUrl(), snapshot.getUsername(), snapshot.getPassword());
        DataSource dataSource = dataSourceMap.get(dataSourceKey);

        if (dataSource == null) {
            String jdbcUrl = snapshot.getUrl();
            if ("com.mysql.jdbc.Driver".equals(snapshot.getDriverClass())) {
                snapshot.setDriverClass("com.mysql.cj.jdbc.Driver");
            }

            HikariDataSource hikariDataSource = new HikariDataSource();
            hikariDataSource.setJdbcUrl(jdbcUrl);
            hikariDataSource.setUsername(snapshot.getUsername());
            hikariDataSource.setPassword(snapshot.getPassword());
            hikariDataSource.setDriverClassName(snapshot.getDriverClass());

            dataSource = hikariDataSource;
            dataSourceMap.put(dataSourceKey, dataSource);
        }

        return dataSource;
    }

    private DataSetDto getDataSet(FormSyncMetaEntity formSyncMetaEntity,
                                  DataSource dataSource,
                                  Timestamp startDateTime,
                                  int pageIndex) throws SQLException {
        DataSetDto dataSet = null;
        String sql = null;
        try {
            try (Connection connection = dataSource.getConnection()) {
                sql = buildSql(formSyncMetaEntity, connection.getMetaData().getURL(), startDateTime, pageIndex);
                try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                    ResultSet resultSet = preparedStatement.executeQuery();
                    ResultSetMetaData metaData = resultSet.getMetaData();
                    Collection<DataSetDto.Column> columns = getTableMetaColumns(connection.getMetaData().getURL(), metaData);
                    int totalCount = 0;

                    Collection<DataSetDto.Row> rows = new ArrayList<>();
                    Timestamp endFetchedModifiedTime = startDateTime;
                    while (resultSet.next()) {
                        try {
                            Collection<DataSetDto.FieldValue> fieldValues = new ArrayList<>();
                            Optional<Object> idColumnOptional = columns.stream().filter(ii -> ii.isId())
                                    .map(ii -> getColumnValue(resultSet, ii.getName()))
                                    .filter(ii -> ii != null)
                                    .findFirst();

                            Object id = null;
                            if (idColumnOptional.isPresent()) {
                                id = idColumnOptional.get();
                            }


                            Optional<Object> domainIdColumnOptional = columns.stream().filter(ii -> ii.isDomainId())
                                    .map(ii -> getColumnValue(resultSet, ii.getName()))
                                    .filter(ii -> ii != null).findFirst();

                            String domainId = null;
                            if (domainIdColumnOptional.isPresent()) {
                                domainId = String.valueOf(domainIdColumnOptional.get());
                            }

                            for (DataSetDto.Column col : columns) {
                                try {
                                    Object columnValue = null;
                                    columnValue = getColumnValue(resultSet, col.getName());
                                    if ("LASTMODIFIED".equalsIgnoreCase(col.getName()) && columnValue != null) {
                                        try {
                                            LocalDateTime localDateTime = (LocalDateTime) columnValue;
                                            ZonedDateTime zonedDateTime = localDateTime.atZone(ZoneId.systemDefault());
                                            if (endFetchedModifiedTime.before(Timestamp.from(zonedDateTime.toInstant()))) {
                                                endFetchedModifiedTime = Timestamp.from(zonedDateTime.toInstant());
                                            }
                                        } catch (Exception ex) {
                                            logger.error(String.format("LASTMODIFIED.该值为:%s;明细:%s", columnValue, ex), ex);
                                            Date lastModifiedTime = (Date) columnValue;
                                            if (endFetchedModifiedTime.before(lastModifiedTime)) {
                                                endFetchedModifiedTime = Timestamp.from(lastModifiedTime.toInstant());
                                            }
                                        }
                                    }

                                    fieldValues.add(DataSetDto.FieldValue.create(
                                            dispatchSiteResourceProperties.getPrefix(),
                                            domainId, String.valueOf(id),
                                            col.getName(), columnValue));
                                } catch (Exception ex) {
                                    logger.error(String.format("[%s]获取字段信息失败(%s)", sql, col.getName()), ex);
                                    ex.printStackTrace();
                                }
                            }

                            totalCount++;
                            DataSetDto.Row newRow = DataSetDto.Row.create(id, fieldValues);
                            newRow.setDomainId(domainId);

                            rows.add(newRow);
                        } catch (Exception ex) {
                            ex.printStackTrace();
                        }
                    }

                    dataSet = DataSetDto.create(metaData.getTableName(1), totalCount, columns, rows);
                    dataSet.markInfo(sql, startDateTime, endFetchedModifiedTime);

                    formSyncMetaEntity.markSyncQueue(endFetchedModifiedTime, rows.size(),
                            String.format("同步周期: >=%s ~ < %s pageIndex=%s;", startDateTime, endFetchedModifiedTime, pageIndex));

                    dataSet.setDeletedIds(getDeletedIds(formSyncMetaEntity, startDateTime, endFetchedModifiedTime));

                    return dataSet;
                } catch (Exception ex) {
                    String msg = String.format("获取数据异常:SQL=%s;Parameters1=%s;pageIndex=%s", sql, startDateTime, pageIndex);
                    logger.error(msg + ":" + ExceptionUtils.getStackMessage(ex));

                    throw new SaasBadException(
                            String.format("获取数据异常:SQL=%s;Parameters1=%s;pageIndex=%s", sql, startDateTime, pageIndex), ex);
                } finally {
                    String tableName = formSyncMetaEntity.getFormMeta().getTableName();
                    if (StringUtils.startsWithIgnoreCase(tableName, "tlk") && dataSet != null && dataSet.getTotalCount() == 0) {
                        String batchUpdateStatement = String.format("UPDATE %s set LASTMODIFIED=CURRENT_TIMESTAMP where LASTMODIFIED is null", tableName);
                        try (PreparedStatement batchInitialUpdateStatement = connection.prepareStatement(batchUpdateStatement)) {
                            batchInitialUpdateStatement.execute();
                        }
                    }
                }
            }
        } catch (Exception ex) {
            logger.error(String.format("获取表单数据异常:%s", sql), ex);

            throw new RuntimeException(String.format("获取表单数据异常:%s", sql), ex);
        }
    }

    private Object getColumnValue(ResultSet resultSet, String columnName) {
        Object columnValue = null;
        try {
            columnValue = resultSet.getObject(columnName);
        } catch (Exception ex) {
            try {
                logger.error((String.format("字段(%s)取数异常:%s; 忽略-明细:%s", columnName,
                        resultSet.getString(columnName), ExceptionUtils.getStackMessage(ex))));
            } catch (Exception exx) {
                exx.printStackTrace();
            }
            columnValue = null;
        }

        return columnValue;
    }

    private List<String> getDeletedIds(FormSyncMetaEntity formSyncMetaEntity, Date beginDate, Date endDate) {
        Collection<EventSourceEntity> eventSourceEntities =
                this.eventSourceRepository.findAll(EventAction.Deleted, formSyncMetaEntity.getFormMeta().getFormId(),
                        beginDate, endDate);

        return eventSourceEntities.stream().map(ii -> {

            return ii.getRecordId();
        }).collect(Collectors.toList());
    }

    private Timestamp getNextSectionDate(Timestamp fetchModifiedTime) {
        Instant fetchedModifiedTimeInstant = Instant.ofEpochMilli(fetchModifiedTime.getTime());
        if (fetchedModifiedTimeInstant.isBefore(Instant.now().minus(5 * 365, ChronoUnit.DAYS))) {
            fetchedModifiedTimeInstant = fetchedModifiedTimeInstant.minus(-6 * 365, ChronoUnit.DAYS);
        } else {
            //不支持年/月
            fetchedModifiedTimeInstant = fetchedModifiedTimeInstant.minus(-2 * 30, ChronoUnit.DAYS);
        }

        Timestamp endFetchedModifiedTime = Timestamp.from(Instant.now());
        if (fetchedModifiedTimeInstant.isBefore(endFetchedModifiedTime.toInstant())) {
            endFetchedModifiedTime = Timestamp.from(fetchedModifiedTimeInstant);
        }

        return endFetchedModifiedTime;
    }

    private static Map<String, Collection<DataSetDto.Column>> cache_MetaTableColumns = new ConcurrentHashMap<>();

    private Collection<DataSetDto.Column> getTableMetaColumns(
            String connectionJdbcUrl,
            ResultSetMetaData metaData) throws SQLException {
        String tableName = metaData.getTableName(1);

        String tableColumnCacheKey = String.format("url=%s;table=%s", connectionJdbcUrl, tableName);
        Collection<DataSetDto.Column> columns = cache_MetaTableColumns.get(tableColumnCacheKey);
        if (columns == null) {
            columns = new ArrayList<>();
            for (int colIndex = 1; colIndex <= metaData.getColumnCount(); colIndex++) {
                columns.add(DataSetDto.Column.create(metaData.getColumnName(colIndex)));
            }

            cache_MetaTableColumns.put(tableColumnCacheKey, columns);
        }

        return columns;
    }
}
