<?php

class EspEventRepository
{
    public static function store(array $event)
    {
        $provider = (string)self::value($event, 'provider', 'unknown');
        $providerEventId = (string)self::value($event, 'provider_event_id', '');
        $eventType = (string)self::value($event, 'event_type', 'unknown');
        $recipientEmail = (string)self::value($event, 'recipient_email', '');
        $eventTime = (string)self::value($event, 'event_time', date('Y-m-d H:i:s'));
        $rawPayload = self::value($event, 'raw_payload', array());

        $eventHash = self::buildEventHash($provider, $providerEventId, $eventType, $recipientEmail, $eventTime, $rawPayload);

        $existing = self::findByEventHash($eventHash);
        if ($existing !== null) {
            return $existing;
        }

        $sqlDataArray = array(
            'provider' => $provider,
            'provider_event_id' => self::nullable(self::value($event, 'provider_event_id')),
            'provider_message_id' => self::nullable(self::value($event, 'provider_message_id')),
            'mailbeez_message_id' => self::nullable(self::value($event, 'mailbeez_message_id')),
            'autoemail_id' => self::nullableInt(self::value($event, 'autoemail_id')),
            'event_hash' => $eventHash,
            'event_type' => $eventType,
            'event_time' => $eventTime,
            'recipient_email' => $recipientEmail,
            'severity' => (string)self::value($event, 'severity', 'info'),
            'bounce_type' => self::nullable(self::value($event, 'bounce_type')),
            'bounce_code' => self::nullable(self::value($event, 'bounce_code')),
            'reason' => self::nullable(self::value($event, 'reason')),
            'url' => self::nullable(self::value($event, 'url')),
            'process_status' => 'pending',
            'process_error' => null,
            'processed_at' => null,
            'raw_payload' => json_encode($rawPayload),
            'created_at' => date('Y-m-d H:i:s'),
        );

        try {
            mh_db_perform(TABLE_MAILBEEZ_ESP_EVENTS, $sqlDataArray);
        } catch (Exception $e) {
            if (self::isDuplicateException($e)) {
                return self::findByEventHash($eventHash);
            }
            throw $e;
        }

        $insertId = mh_db_insert_id();

        $providerMessageId = self::nullable(self::value($event, 'provider_message_id'));
        $mailbeezMessageId = self::nullable(self::value($event, 'mailbeez_message_id'));
        if ($providerMessageId !== null && $mailbeezMessageId !== null) {
            self::upsertMessageMap(array(
                'provider' => $provider,
                'provider_message_id' => $providerMessageId,
                'mailbeez_message_id' => $mailbeezMessageId,
                'autoemail_id' => self::nullableInt(self::value($event, 'autoemail_id')),
                'recipient_email' => self::nullable(self::value($event, 'recipient_email')),
            ));
        }

        return $insertId;
    }

    public static function loadById($eventId)
    {
        $sql = "select * from " . TABLE_MAILBEEZ_ESP_EVENTS . " where id = '" . (int)$eventId . "' limit 1";
        $query = mh_db_query($sql);
        if (mh_db_num_rows($query) < 1) {
            return null;
        }
        $row = mh_db_fetch_array($query);
        $row['raw_payload'] = self::decodeJson(self::value($row, 'raw_payload', '{}'));
        return $row;
    }

    public static function markProcessed($eventId)
    {
        mh_db_query("update " . TABLE_MAILBEEZ_ESP_EVENTS . "
            set process_status='processed', process_error=NULL, processed_at=now()
            where id='" . (int)$eventId . "'");
    }

    public static function markFailed($eventId, $error)
    {
        $error = mh_db_input(substr((string)$error, 0, 16000));
        mh_db_query("update " . TABLE_MAILBEEZ_ESP_EVENTS . "
            set process_status='failed', process_error='" . $error . "'
            where id='" . (int)$eventId . "'");
    }

    public static function findMailbeezMessageIdByProviderMessage($provider, $providerMessageId)
    {
        $provider = mh_db_input((string)$provider);
        $providerMessageId = mh_db_input((string)$providerMessageId);
        $sql = "select mailbeez_message_id from " . TABLE_MAILBEEZ_ESP_MESSAGE_MAP . "
                where provider='" . $provider . "' and provider_message_id='" . $providerMessageId . "'
                limit 1";
        $query = mh_db_query($sql);
        if (mh_db_num_rows($query) > 0) {
            $row = mh_db_fetch_array($query);
            return $row['mailbeez_message_id'];
        }
        return null;
    }

    public static function upsertMessageMap(array $map)
    {
        $provider = mh_db_input((string)self::value($map, 'provider', ''));
        $providerMessageId = mh_db_input((string)self::value($map, 'provider_message_id', ''));
        if ($provider == '' || $providerMessageId == '') {
            return;
        }

        $sqlDataArray = array(
            'provider' => $provider,
            'provider_message_id' => $providerMessageId,
            'mailbeez_message_id' => (string)self::value($map, 'mailbeez_message_id', ''),
            'autoemail_id' => self::nullableInt(self::value($map, 'autoemail_id')),
            'recipient_email' => self::nullable(self::value($map, 'recipient_email')),
            'date_created' => date('Y-m-d H:i:s'),
        );

        try {
            mh_db_perform(TABLE_MAILBEEZ_ESP_MESSAGE_MAP, $sqlDataArray);
        } catch (Exception $e) {
            if (self::isDuplicateException($e)) {
                mh_db_query("update " . TABLE_MAILBEEZ_ESP_MESSAGE_MAP . "
                    set mailbeez_message_id='" . mh_db_input((string)$sqlDataArray['mailbeez_message_id']) . "',
                        autoemail_id=" . (($sqlDataArray['autoemail_id'] === null) ? 'NULL' : (int)$sqlDataArray['autoemail_id']) . ",
                        recipient_email=" . (($sqlDataArray['recipient_email'] === null) ? 'NULL' : "'" . mh_db_input((string)$sqlDataArray['recipient_email']) . "'") . "
                    where provider='" . $provider . "' and provider_message_id='" . $providerMessageId . "'");
                return;
            }
            throw $e;
        }
    }

    private static function value(array $data, $key, $fallback = null)
    {
        return array_key_exists($key, $data) ? $data[$key] : $fallback;
    }

    private static function nullable($value)
    {
        if ($value === null) {
            return null;
        }
        $string = trim((string)$value);
        return ($string === '') ? null : $string;
    }

    private static function nullableInt($value)
    {
        if ($value === null || $value === '') {
            return null;
        }
        return (int)$value;
    }

    private static function buildEventHash($provider, $providerEventId, $eventType, $recipientEmail, $eventTime, $rawPayload)
    {
        if ($providerEventId != '') {
            return hash('sha256', $provider . '|' . $providerEventId . '|' . $eventType . '|' . $recipientEmail . '|' . $eventTime);
        }
        return hash('sha256', $provider . '|' . $eventType . '|' . $recipientEmail . '|' . $eventTime . '|' . json_encode($rawPayload));
    }

    private static function findByEventHash($eventHash)
    {
        $eventHash = mh_db_input((string)$eventHash);
        $sql = "select id from " . TABLE_MAILBEEZ_ESP_EVENTS . " where event_hash='" . $eventHash . "' limit 1";
        $query = mh_db_query($sql);
        if (mh_db_num_rows($query) > 0) {
            $row = mh_db_fetch_array($query);
            return (int)$row['id'];
        }
        return null;
    }

    private static function isDuplicateException(Exception $e)
    {
        $message = strtolower((string)$e->getMessage());
        return (strpos($message, 'duplicate') !== false || strpos($message, 'uniq_event_hash') !== false || strpos($message, 'primary') !== false);
    }

    private static function decodeJson($value)
    {
        $data = json_decode((string)$value, true);
        return is_array($data) ? $data : array();
    }
}
