<?php
namespace EasyChat\Library;

use EasyChat\Library\Queue\QueueManager;

class ResponseTimeMetrics {
    const METRIC_GLOBAL = 'all_customer_messages_global';
    const METRIC_HUMAN_MANAGER = 'first_human_reply_by_manager';
    const BACKFILL_STATUS_KEY = 'response_time_metrics_backfill_status';
    const BACKFILL_CURSOR_KEY = 'response_time_metrics_backfill_cursor';
    const BACKFILL_UPDATED_KEY = 'response_time_metrics_backfill_updated';

    private $registry;
    private $db;
    private $prefix;

    public function __construct($registry) {
        $this->registry = $registry;
        $this->db = $registry->get('db');
        $this->prefix = easychat_db_prefix();
    }

    public function recordMessage(int $message_id): void {
        if ($message_id <= 0) {
            return;
        }

        $message = $this->getMessage($message_id);
        if (!$message) {
            return;
        }

        $author = (int)($message['author'] ?? 0);
        if ($author === 1) {
            $this->recordCustomerMessage($message);
        } elseif ($author === 2 || $author === 3) {
            $this->recordReplyMessage($message);
        }
    }

    public function subtractChats(array $chat_ids): void {
        $ids = [];
        foreach ($chat_ids as $chat_id) {
            $chat_id = (int)$chat_id;
            if ($chat_id > 0) {
                $ids[$chat_id] = $chat_id;
            }
        }

        if (!$ids) {
            return;
        }

        $ids_sql = implode(',', $ids);

        $global = $this->db->query(
            "SELECT COUNT(*) AS cnt, COALESCE(SUM(`first_response_seconds`), 0) AS seconds_sum
             FROM `{$this->prefix}messages`
             WHERE `chat_id` IN ({$ids_sql})
               AND `author` = 1
               AND `first_response_message_id` > 0"
        );
        $this->addSummaryDelta(
            self::METRIC_GLOBAL,
            0,
            -1 * (int)($global->row['cnt'] ?? 0),
            -1 * (int)($global->row['seconds_sum'] ?? 0)
        );

        $managers = $this->db->query(
            "SELECT `manager_id`, COUNT(*) AS cnt, COALESCE(SUM(`response_metric_human_frt_seconds`), 0) AS seconds_sum
             FROM `{$this->prefix}messages`
             WHERE `chat_id` IN ({$ids_sql})
               AND `author` = 2
               AND `manager_id` > 0
               AND `response_metric_human_frt_seconds` > 0
             GROUP BY `manager_id`"
        );

        foreach ($managers->rows as $row) {
            $this->addSummaryDelta(
                self::METRIC_HUMAN_MANAGER,
                (int)$row['manager_id'],
                -1 * (int)$row['cnt'],
                -1 * (int)$row['seconds_sum']
            );
        }
    }

    public function getGlobalAverageSeconds(int $default = 15): int {
        $summary = $this->getSummary(self::METRIC_GLOBAL, 0);
        if ($summary && (int)$summary['response_count'] > 0) {
            return max(0, (int)round((int)$summary['response_seconds_sum'] / (int)$summary['response_count']));
        }

        return max(0, $default);
    }

    public function getManagerHumanAverageMap(): array {
        $query = $this->db->query(
            "SELECT `entity_id`, `response_count`, `response_seconds_sum`
             FROM `{$this->prefix}response_time_summary`
             WHERE `metric_key` = '" . $this->db->escape(self::METRIC_HUMAN_MANAGER) . "'
               AND `response_count` > 0"
        );

        $result = [];
        foreach ($query->rows as $row) {
            $count = (int)($row['response_count'] ?? 0);
            if ($count <= 0) {
                continue;
            }
            $result[(int)$row['entity_id']] = (float)((int)$row['response_seconds_sum'] / $count);
        }
        return $result;
    }

    public function backfillBatch(int $last_message_id = 0, int $limit = 500): array {
        $last_message_id = max(0, $last_message_id);
        $limit = max(1, min(2000, $limit));

        $this->setSetting(self::BACKFILL_STATUS_KEY, 'running');

        $query = $this->db->query(
            "SELECT `message_id`, `chat_id`, `date_added`
             FROM `{$this->prefix}messages` FORCE INDEX (`PRIMARY`)
             WHERE `author` = 1
               AND `message_id` > {$last_message_id}
               AND `response_metric_checked_at` IS NULL
             ORDER BY `message_id` ASC
             LIMIT {$limit}"
        );

        $processed = 0;
        $next_cursor = $last_message_id;
        foreach ($query->rows as $row) {
            $message_id = (int)$row['message_id'];
            $next_cursor = max($next_cursor, $message_id);
            $this->backfillCustomerMessage($row);
            $processed++;
        }

        $this->setSetting(self::BACKFILL_CURSOR_KEY, (string)$next_cursor);
        $this->setSetting(self::BACKFILL_UPDATED_KEY, (string)time());

        if ($processed >= $limit) {
            $queue = new QueueManager($this->registry);
            $queue->push('response_time_metrics_backfill', [
                'last_message_id' => $next_cursor,
                'batch_size' => $limit,
            ], 5, 1);
            return [
                'processed' => $processed,
                'next_cursor' => $next_cursor,
                'done' => false,
            ];
        }

        $this->setSetting(self::BACKFILL_STATUS_KEY, 'done');
        return [
            'processed' => $processed,
            'next_cursor' => $next_cursor,
            'done' => true,
        ];
    }

    private function recordCustomerMessage(array $message): void {
        $message_id = (int)$message['message_id'];
        $chat_id = (int)$message['chat_id'];
        $is_first = $this->isFirstCustomerMessage($chat_id, $message_id) ? 1 : 0;

         
         
         
        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `response_metric_checked_at` = NOW(),
                 `response_metric_is_first_customer` = {$is_first}
             WHERE `message_id` = {$message_id}
               AND `author` = 1"
        );
    }

    private function recordReplyMessage(array $message): void {
        $this->db->query('START TRANSACTION');
        try {
            $locked = $this->getMessageForUpdate((int)$message['message_id']);
            if (!$locked) {
                $this->db->query('COMMIT');
                return;
            }

            $this->applyGlobalResponse($locked);
            if ((int)$locked['author'] === 2) {
                $this->applyFirstHumanManagerResponse($locked);
            }

            $this->db->query('COMMIT');
        } catch (\Throwable $e) {
            try {
                $this->db->query('ROLLBACK');
            } catch (\Throwable $rollback) {
            }
            throw $e;
        }
    }

    private function backfillCustomerMessage(array $message): void {
        $message_id = (int)$message['message_id'];
        $chat_id = (int)$message['chat_id'];
        $is_first = $this->isFirstCustomerMessage($chat_id, $message_id) ? 1 : 0;

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `response_metric_checked_at` = NOW(),
                 `response_metric_is_first_customer` = {$is_first}
             WHERE `message_id` = {$message_id}
               AND `author` = 1
               AND `response_metric_checked_at` IS NULL"
        );

        $customer = $this->getMessage($message_id);
        if (!$customer || (int)($customer['first_response_message_id'] ?? 0) > 0) {
            return;
        }

        $response = $this->findFirstResponseAfterCustomer($customer, [2, 3]);
        if ($response) {
            $this->recordCustomerResponse($customer, $response);
        }

        if ($is_first) {
            $human_response = $this->findFirstResponseAfterCustomer($customer, [2]);
            if ($human_response) {
                $this->recordHumanManagerResponse($customer, $human_response);
            }
        }
    }

    private function applyGlobalResponse(array $response): void {
        $response_date = $this->db->escape((string)$response['date_added']);
        $chat_id = (int)$response['chat_id'];

        $pending = $this->db->query(
            "SELECT `message_id`, `chat_id`, `date_added`
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = {$chat_id}
               AND `author` = 1
               AND `first_response_message_id` = 0
               AND `response_metric_checked_at` IS NOT NULL
               AND `date_added` < '{$response_date}'
             ORDER BY `date_added` ASC, `message_id` ASC
             FOR UPDATE"
        );

        foreach ($pending->rows as $customer) {
            $this->recordCustomerResponse($customer, $response);
        }
    }

    private function applyFirstHumanManagerResponse(array $response): void {
        $chat_id = (int)$response['chat_id'];

        $first_customer = $this->db->query(
            "SELECT `message_id`, `chat_id`, `date_added`
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = {$chat_id}
               AND `author` = 1
               AND `response_metric_is_first_customer` = 1
               AND `response_metric_checked_at` IS NOT NULL
             ORDER BY `date_added` ASC, `message_id` ASC
             LIMIT 1
             FOR UPDATE"
        );

        if (!$first_customer || !$first_customer->row) {
            return;
        }

        $first_human = $this->findFirstResponseAfterCustomer($first_customer->row, [2], true);
        if ($first_human && (int)$first_human['message_id'] === (int)$response['message_id']) {
            $this->recordHumanManagerResponse($first_customer->row, $response);
        }
    }

    private function recordCustomerResponse(array $customer, array $response): void {
        $customer_id = (int)$customer['message_id'];
        $response_id = (int)$response['message_id'];
        $seconds = $this->secondsBetween((string)$customer['date_added'], (string)$response['date_added']);
        if ($response_id <= 0 || $seconds < 0) {
            return;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `first_response_message_id` = {$response_id},
                 `first_response_seconds` = {$seconds},
                 `first_response_author` = " . (int)$response['author'] . ",
                 `first_response_manager_id` = " . (int)($response['manager_id'] ?? 0) . ",
                 `first_response_recorded_at` = NOW(),
                 `response_metric_checked_at` = NOW()
             WHERE `message_id` = {$customer_id}
               AND `author` = 1
               AND `first_response_message_id` = 0"
        );

        if ((int)$this->db->countAffected() > 0) {
            $this->addSummaryDelta(self::METRIC_GLOBAL, 0, 1, $seconds);
        }
    }

    private function recordHumanManagerResponse(array $customer, array $response): void {
        $message_id = (int)$response['message_id'];
        $manager_id = (int)($response['manager_id'] ?? 0);
        if ($message_id <= 0 || $manager_id <= 0 || (int)$response['author'] !== 2) {
            return;
        }

        $seconds = $this->secondsBetween((string)$customer['date_added'], (string)$response['date_added']);
        if ($seconds < 0) {
            return;
        }

        $this->db->query(
            "UPDATE `{$this->prefix}messages`
             SET `response_metric_human_frt_seconds` = {$seconds},
                 `response_metric_human_frt_recorded_at` = NOW()
             WHERE `message_id` = {$message_id}
               AND `author` = 2
               AND `response_metric_human_frt_seconds` = 0"
        );

        if ((int)$this->db->countAffected() > 0) {
            $this->addSummaryDelta(self::METRIC_HUMAN_MANAGER, $manager_id, 1, $seconds);
        }
    }

    private function findFirstResponseAfterCustomer(array $customer, array $authors, bool $for_update = false): array {
        $author_sql = implode(',', array_map('intval', $authors));
        $chat_id = (int)$customer['chat_id'];
        $date_added = $this->db->escape((string)$customer['date_added']);
        $lock = $for_update ? ' FOR UPDATE' : '';

        $query = $this->db->query(
            "SELECT `message_id`, `chat_id`, `author`, `manager_id`, `date_added`
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = {$chat_id}
               AND `author` IN ({$author_sql})
               AND `date_added` > '{$date_added}'
             ORDER BY `date_added` ASC, `message_id` ASC
             LIMIT 1{$lock}"
        );

        return ($query && $query->row) ? $query->row : [];
    }

    private function addSummaryDelta(string $metric_key, int $entity_id, int $count_delta, int $seconds_delta): void {
        if ($count_delta === 0 && $seconds_delta === 0) {
            return;
        }

        $metric_key_e = $this->db->escape($metric_key);
        $entity_id = max(0, $entity_id);

        if ($count_delta > 0 || $seconds_delta > 0) {
            $count = max(0, $count_delta);
            $seconds = max(0, $seconds_delta);
            $this->db->query(
                "INSERT INTO `{$this->prefix}response_time_summary`
                    (`metric_key`, `entity_id`, `response_count`, `response_seconds_sum`, `updated_at`)
                 VALUES ('{$metric_key_e}', {$entity_id}, {$count}, {$seconds}, NOW())
                 ON DUPLICATE KEY UPDATE
                    `response_count` = `response_count` + VALUES(`response_count`),
                    `response_seconds_sum` = `response_seconds_sum` + VALUES(`response_seconds_sum`),
                    `updated_at` = NOW()"
            );
            return;
        }

        $count = abs($count_delta);
        $seconds = abs($seconds_delta);
        $this->db->query(
            "UPDATE `{$this->prefix}response_time_summary`
             SET `response_count` = IF(`response_count` >= {$count}, `response_count` - {$count}, 0),
                 `response_seconds_sum` = IF(`response_seconds_sum` >= {$seconds}, `response_seconds_sum` - {$seconds}, 0),
                 `updated_at` = NOW()
             WHERE `metric_key` = '{$metric_key_e}'
               AND `entity_id` = {$entity_id}"
        );
    }

    private function getSummary(string $metric_key, int $entity_id): array {
        $query = $this->db->query(
            "SELECT `response_count`, `response_seconds_sum`
             FROM `{$this->prefix}response_time_summary`
             WHERE `metric_key` = '" . $this->db->escape($metric_key) . "'
               AND `entity_id` = " . (int)$entity_id . "
             LIMIT 1"
        );
        return ($query && $query->row) ? $query->row : [];
    }

    private function getMessage(int $message_id): array {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messages` WHERE `message_id` = {$message_id} LIMIT 1");
        return ($query && $query->row) ? $query->row : [];
    }

    private function getMessageForUpdate(int $message_id): array {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}messages` WHERE `message_id` = {$message_id} LIMIT 1 FOR UPDATE");
        return ($query && $query->row) ? $query->row : [];
    }

    private function isFirstCustomerMessage(int $chat_id, int $message_id): bool {
        $query = $this->db->query(
            "SELECT `message_id`
             FROM `{$this->prefix}messages`
             WHERE `chat_id` = {$chat_id}
               AND `author` = 1
             ORDER BY `date_added` ASC, `message_id` ASC
             LIMIT 1"
        );

        return $query && $query->row && (int)$query->row['message_id'] === $message_id;
    }

    private function secondsBetween(string $from, string $to): int {
        return (new AppClock($this->registry))->secondsBetweenSql($from, $to);
    }

    private function getSetting(string $key, string $default = ''): string {
        $query = $this->db->query(
            "SELECT `value`
             FROM `{$this->prefix}settings`
             WHERE `key` = '" . $this->db->escape($key) . "'
             LIMIT 1"
        );
        return ($query && $query->row) ? (string)$query->row['value'] : $default;
    }

    private function setSetting(string $key, string $value): void {
        $this->db->query(
            "INSERT INTO `{$this->prefix}settings` (`key`, `value`)
             VALUES ('" . $this->db->escape($key) . "', '" . $this->db->escape($value) . "')
             ON DUPLICATE KEY UPDATE `value` = '" . $this->db->escape($value) . "'"
        );
    }
}
