package com.ease.gsms.server.repositories;

import com.ease.gsms.server.model.Dispatch;
import com.ease.gsms.server.model.DispatchStatus;
import com.ease.gsms.server.model.Message;
import com.ease.gsms.server.model.util.IDEnum;
import com.ease.gsms.server.model.util.MD5Hash;
import com.ease.gsms.server.repositories.util.BatchPreparedStatementSetterWithKeyHolder;
import com.ease.gsms.server.repositories.util.SQLUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Repository
@Transactional
public class DispatchRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${sql.getIdentity}")
    private String callIdentitySQL;

    @Value("${sql.sms.getMessagesByHashList}")
    private String getMessagesByHashListSQL;

    @Value("${sql.sms.getMessagesByRequestDispatchId}")
    private String getMessagesByRequestDispatchIdSQL;

    @Value("${sql.sms.getMessageByIdForUserSQL}")
    private String getMessageByIdForUserSQL;

    @Value("${sql.sms.getMessageCountsBetweenCreationTimestampsForUserSQL}")
    private String getMessageCountsBetweenCreationTimestampsForUserSQL;

    @Value("${sql.sms.getMessagesBetweenCreationTimestampsForUserSQL}")
    private String getMessagesBetweenCreationTimestampsForUserSQL;

    @Value("${sql.sms.getDispatchRequestUUID}")
    private String getDispatchRequestUUIDSQL;

    @Value("${sql.sms.getDispatchesByStatus}")
    private String getDispatchesByStatusSQL;

    @Value("${sql.sms.getDispatchByUuid}")
    private String getDispatchByUuidSQL;

    @Value("${sql.sms.insertDispatchRequest}")
    private String insertDispatchRequestSQL;

    @Value("${sql.sms.insertMessage}")
    private String insertMessageSQL;

    @Value("${sql.sms.allocateMessages}")
    private String allocateMessagesSQL;

    @Value("${sql.sms.updateMessageSentStatus}")
    private String updateMessageSentStatusSQL;

    @Value("${sql.sms.updateMessageDeliveryStatus}")
    private String updateMessageDeliveryStatusSQL;

    @Value("${sql.sms.updateDispatchStatus}")
    private String updateDispatchStatusSQL;


    public Map<MD5Hash, List<Message>> getMessagesByHashList(List<MD5Hash> hashList) {

        Map<MD5Hash, List<Message>> results = new HashMap<>();

        Iterator<MD5Hash> hashIterator = hashList.iterator();

        while (hashIterator.hasNext()) {

            int clauseLimit = 1000; // limit where clauses to 1000 items to avoid SQL problems

            StringBuilder sb = new StringBuilder();

            sb.append(getMessagesByHashListSQL);

            while (hashIterator.hasNext() && clauseLimit-- > 0) {

                MD5Hash hash = hashIterator.next();

                sb.append(
                        String.format(
                                "(hash_high = %d and hash_low = %d)",
                                hash.getHigh(),
                                hash.getLow()
                        )
                );

                if (hashIterator.hasNext() && clauseLimit > 0) {
                    sb.append(" or ");
                }

            }

            jdbcTemplate
                    .query(sb.toString(), new MessageRowMapper())
                    .forEach(message -> results.computeIfAbsent(message.getHash(), md5Hash -> new LinkedList<>()).add(message));

        }

        return results;

    }

    public UUID saveRequest(Dispatch request) {

        jdbcTemplate.update(
                insertDispatchRequestSQL,
                request.getUUID().toString(),
                request.getFromICCID(),
                request.getSubmitter(),
                request.getSubmissionDate().getTime(),
                request.getModificationDate().getTime(),
                request.getSendToInternationalNumbers(),
                request.getSendToNationalLandlines(),
                request.getSendToNationalMobiles(),
                request.getResendIdenticalSuccessfullyDeliveredMessages(),
                request.getResendIdenticalSuccessfullySentMessages(),
                request.getResendIdenticalUnsuccessfullySentMessages(),
                request.getResendUnsentMessages(),
                request.getAskForDeliveryReport(),
                request.getPauseBetweenMessagesSeconds(),
                request.getStatus().getId(),
                request.getMessageCount()
        );

        Long dispatchId = jdbcTemplate.queryForObject(callIdentitySQL, Long.class);

        request.setId(dispatchId);

        List<Message> messages = request.getMessages();

        for (Message message : messages) {

            message.setDispatchRequestId(dispatchId);

        }

        if (!request.getAskForDeliveryReport()) { // mark message delivery status id as -1, which we'll use to denote unrequested

            for (Message message : messages) {

                message.setDeliveryStatus(-1);

            }

        }

        SQLUtil.batchUpdateWithKeyHolder(jdbcTemplate, insertMessageSQL, new BatchPreparedStatementSetterWithKeyHolder<Message>(messages) {
            @Override
            public void setValues(PreparedStatement ps, Message message) throws SQLException {
                MD5Hash hash = message.getHash();
                int parameterIndex = 1;
                ps.setLong(parameterIndex++,    hash.getHigh());
                ps.setLong(parameterIndex++,    hash.getLow());
                ps.setString(parameterIndex++,  message.getFromICCID());
                ps.setString(parameterIndex++,  message.getTo());
                ps.setString(parameterIndex++,  message.getContent());
                SQLUtil.setNullableTimestamp(ps, parameterIndex++, message.getCreationDate());
                SQLUtil.setNullableTimestamp(ps, parameterIndex++, message.getAllocationDate());
                SQLUtil.setNullableTimestamp(ps, parameterIndex++, message.getSentDate());
                SQLUtil.setNullableTimestamp(ps, parameterIndex++, message.getDeliveredDate());
                ps.setInt(parameterIndex++,     message.getSendingStatus());
                ps.setInt(parameterIndex++,     message.getDeliveryStatus());
                ps.setLong(parameterIndex++,    dispatchId);
            }

            @Override
            protected void setPrimaryKey(Map<String, Object> primaryKey, Message message) {
                message.setId((Long) primaryKey.get("insert_id"));
            }
        });

        return request.getUUID();

    }

    public void allocateMessages(List<Long> messageIds, String fromICCID, Date allocationDate) {

        SQLUtil.listUpdate(jdbcTemplate, allocateMessagesSQL, "#ids#", messageIds, fromICCID, allocationDate.getTime());

    }

    public List<Dispatch> getActiveDispatches() {

        List<Dispatch> dispatches = SQLUtil.listQuery(
                jdbcTemplate,
                getDispatchesByStatusSQL,
                new DispatchRowMapper(),
                "#status#",
                Arrays.asList(DispatchStatus.REQUESTED.getId(), DispatchStatus.IN_PROGRESS.getId(), DispatchStatus.PAUSED.getId(), DispatchStatus.SCHEDULED_PAUSE.getId())
        );

        for (Dispatch dispatch : dispatches) {

            dispatch.setMessages(
                getMessagesByDispatchRequestId(dispatch.getId())
            );

        }

        return dispatches;

    }

    private Message[] getMessagesByDispatchRequestId(Long dispatchRequestId) {

        return jdbcTemplate.query(getMessagesByRequestDispatchIdSQL, new MessageRowMapper(), dispatchRequestId).toArray(new Message[0]);

    }

    public void updateMessageSentInformation(Long messageId, String fromICCID, Date sendingDate, Integer sendingStatus) {

        jdbcTemplate.update(updateMessageSentStatusSQL, fromICCID, sendingDate.getTime(), sendingStatus, messageId);

    }

    public void updateMessageDeliveryInformation(Long messageId, Date deliveryDate, Integer deliveryStatus) {

        jdbcTemplate.update(updateMessageDeliveryStatusSQL, deliveryDate.getTime(), deliveryStatus, messageId);

    }

    public void updateDispatchStatus(Long deliveryId, Date modificationDate, Integer dispatchStatus) {

        jdbcTemplate.update(updateDispatchStatusSQL, modificationDate.getTime(), dispatchStatus, deliveryId);

    }

    public Dispatch getDispatchByUuid(UUID requestId) {

        try {

            Dispatch dispatch = jdbcTemplate.queryForObject(getDispatchByUuidSQL, new DispatchRowMapper(), requestId.toString());

            dispatch.setMessages(
                    getMessagesByDispatchRequestId(dispatch.getId())
            );

            return dispatch;

        } catch (EmptyResultDataAccessException e) {

            return null;

        }

    }

    public Message getMessageByIdForUser(Long messageId, String user) {

        try {

            return jdbcTemplate.queryForObject(getMessageByIdForUserSQL, new MessageRowMapper(), messageId, user);

        } catch (EmptyResultDataAccessException e) {

            return null;

        }

    }

    public Long getMessageCountsBetweenCreationTimestamps(Long fromTimestamp, Long toTimestamp, String userName) {

        return jdbcTemplate.queryForObject(getMessageCountsBetweenCreationTimestampsForUserSQL, Long.class, fromTimestamp, toTimestamp, userName);

    }

    private static final Map<String, Field> messageFields = Arrays.stream(Message.class.getDeclaredFields()).collect(Collectors.toMap(Field::getName, Function.identity()));

    public List<Message> getMessagesBetweenCreationTimestamps(Long fromTimestamp, Long toTimestamp, String userName, String sortBy, Boolean descending, Integer startingRow, Integer fetchCount) {

        if (!messageFields.containsKey(sortBy)) {

            throw new IllegalArgumentException("Sorting field must be among message fields, in camel case");

        }

        sortBy = sortBy.replace("Date", "Timestamp");

        if (sortBy.equalsIgnoreCase("to")) {

            sortBy = "to_number";

        }

        Matcher m = Pattern.compile("(?<=[a-z])[A-Z]").matcher(sortBy);

        StringBuffer sb = new StringBuffer();

        while (m.find()) {
            m.appendReplacement(sb, "_"+m.group().toLowerCase());
        }

        m.appendTail(sb);

        return jdbcTemplate.query(
                getMessagesBetweenCreationTimestampsForUserSQL
                    .replace("#sortBy#", sb)
                    .replace("#order#", descending ? "desc" : "asc")
                    .replace("#fetchCount#", fetchCount.toString())
                    .replace("#startingRow#", startingRow.toString())
                ,
                new MessageRowMapper(),
                fromTimestamp, toTimestamp, userName
        );

    }

    static class MessageRowMapper implements RowMapper<Message> {

        @Override
        public Message mapRow(ResultSet resultSet, int i) throws SQLException, DataAccessException {
            return new Message(
                    resultSet.getLong("id"),
                    resultSet.getString("from_iccid"),
                    resultSet.getString("to_number"),
                    resultSet.getString("content"),
                    SQLUtil.getNullableTimestamp(resultSet, "creation_timestamp"),
                    SQLUtil.getNullableTimestamp(resultSet, "allocation_timestamp"),
                    SQLUtil.getNullableTimestamp(resultSet, "sent_timestamp"),
                    SQLUtil.getNullableTimestamp(resultSet, "delivered_timestamp"),
                    resultSet.getInt("sending_status"),
                    resultSet.getInt("delivery_status"),
                    resultSet.getLong("dispatch_request_id")
            );
        }

    }

    static class DispatchRowMapper implements RowMapper<Dispatch> {

        @Override
        public Dispatch mapRow(ResultSet resultSet, int i) throws SQLException, DataAccessException {
            return new Dispatch(
                    resultSet.getLong("id"),
                    UUID.fromString(resultSet.getString("uuid")),
                    resultSet.getString("from_iccid"),
                    resultSet.getString("submitter"),
                    SQLUtil.getNullableTimestamp(resultSet, "submission_timestamp"),
                    SQLUtil.getNullableTimestamp(resultSet, "modification_timestamp"),
                    resultSet.getBoolean("send_to_international"),
                    resultSet.getBoolean("send_to_national_landlines"),
                    resultSet.getBoolean("send_to_national_mobiles"),
                    resultSet.getBoolean("resend_successfully_delivered"),
                    resultSet.getBoolean("resend_successfully_sent"),
                    resultSet.getBoolean("resend_unsuccessfully_sent"),
                    resultSet.getBoolean("resend_unsent"),
                    resultSet.getBoolean("ask_for_delivery_report"),
                    resultSet.getInt("pause_between_messages_seconds"),
                    IDEnum.byId(DispatchStatus.class, resultSet.getInt("status")),
                    resultSet.getInt("message_count")
            );
        }

    }
}
