package com.ease.gsms.server.repositories.util;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLUtil {

    public static final int MAX_IN_CLAUSE_SIZE = 1000;

    public static void setNullableTimestamp(PreparedStatement ps, int parameterIndex, Date timestamp) throws SQLException {

        if (timestamp != null) {

            ps.setLong(parameterIndex, timestamp.getTime());

        } else {

            ps.setNull(parameterIndex, Types.BIGINT);

        }

    }

    public static Date getNullableTimestamp(ResultSet resultSet, String columnLabel) throws SQLException {

        long timestamp = resultSet.getLong(columnLabel);

        if (resultSet.wasNull()) {

            return null;

        } else {

            return new Date(timestamp);

        }

    }

    public static <T, A> List<T> listQuery(JdbcTemplate template, String baseQuery, RowMapper<T> rowMapper, String placeholder, List<A> list, Object... args) {

        int listSize = list.size();

        if (listSize > MAX_IN_CLAUSE_SIZE) {

            throw new UnsupportedOperationException("listQuery for lists with more than " + MAX_IN_CLAUSE_SIZE + " elements hasn't been implemented yet");

        }

        int argCount = args.length;

        Object[] newArgs = new Object[argCount + listSize];

        int placeHolderIndex = baseQuery.indexOf(placeholder);

        if (placeHolderIndex == -1) {

            throw new IllegalArgumentException(
                    String.format(
                            "Placeholder '%s' not found in query: %s",
                            placeholder,
                            baseQuery
                    )
            );

        }

        Pattern p = Pattern.compile("\\?");

        Matcher m = p.matcher(baseQuery.substring(0, placeHolderIndex));

        int questionMarksBeforePlaceholder = 0;

        while (m.find()) {
            questionMarksBeforePlaceholder++;
        }

        // set newArgs

        int i = 0;

        for (; i < questionMarksBeforePlaceholder; i++) {
            newArgs[i] = args[i];
        }

        for (Object listElement : list) {
            newArgs[i++] = listElement;
        }

        for (i = questionMarksBeforePlaceholder; i < argCount; i++) {
            newArgs[i + listSize] = args[i];
        }

        return template.query(
                baseQuery.replace(placeholder, "(" + String.join(",", Collections.nCopies(listSize, "?")) + ")"),
                newArgs,
                rowMapper
        );

    }

    public static <T> void listUpdate(JdbcTemplate template, String baseQuery, String placeholder, List<T> list, Object... args) {

        int listSize = list.size();

        int argCount = args.length;

        Iterator<T> iterator = list.iterator();

        for (int i = 0; i < listSize; i += MAX_IN_CLAUSE_SIZE) {

            int count = Math.min(MAX_IN_CLAUSE_SIZE, listSize - i);

            int max = argCount + count;

            Object[] newArgs = new Object[max];

            System.arraycopy(args, 0, newArgs, 0, argCount);

            for (int j = argCount; j < max; j++) {

                newArgs[j] = iterator.next();

            }

            template.update(
                    baseQuery.replace(placeholder, "(" + String.join(",", Collections.nCopies(count, "?")) + ")"),
                    newArgs
            );

        }

    }

    private static void generatedKeys(PreparedStatement ps, KeyHolder keyHolder) throws SQLException {
        List<Map<String, Object>> keys = keyHolder.getKeyList();
        ResultSet rs = ps.getGeneratedKeys();
        if (rs == null) return;
        try {
            keys.addAll(new RowMapperResultSetExtractor<>(new ColumnMapRowMapper(), 1).extractData(rs));
        } finally {
            rs.close();
        }
    }

    public static <T> int[] batchUpdateWithKeyHolder(JdbcTemplate jdbcTemplate, final String sql, final BatchPreparedStatementSetterWithKeyHolder<T> pss) {
        return jdbcTemplate.execute((PreparedStatementCreator) con -> con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS), ps -> {
            try {
                int batchSize = pss.getBatchSize();
                InterruptibleBatchPreparedStatementSetter ipss =
                        (pss instanceof InterruptibleBatchPreparedStatementSetter ?
                                (InterruptibleBatchPreparedStatementSetter) pss : null);
                int[] result;
                KeyHolder keyHolder = new GeneratedKeyHolder();

                try {
                    if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
                        for (int i = 0; i < batchSize; i++) {
                            pss.setValues(ps, i);
                            if (ipss != null && ipss.isBatchExhausted(i)) break;
                            ps.addBatch();
                        }
                        result = ps.executeBatch();

                        generatedKeys(ps, keyHolder);
                    } else {
                        List<Integer> rowsAffected = new ArrayList<Integer>();
                        for (int i = 0; i < batchSize; i++) {
                            pss.setValues(ps, i);
                            if (ipss != null && ipss.isBatchExhausted(i)) break;

                            rowsAffected.add(ps.executeUpdate());
                            generatedKeys(ps, keyHolder);
                        }

                        result = rowsAffected.stream().mapToInt(Integer::intValue).toArray();
                    }
                } finally {
                    pss.setPrimaryKey(keyHolder);
                }

                return result;
            } finally {
                if (pss instanceof ParameterDisposer) ((ParameterDisposer) pss).cleanupParameters();
            }
        });
    }

}
