nettown_backend/models/InteractionDetailsModel.php

<?php

namespace Models;

use Libs\Database;

class InteractionDetailsModel
{
    private \PDO $pdo;

    public function __construct()
    {
        $this->pdo = Database::pdo();
    }

    public function getInteractionDetails(int $companyId, int $conversationId): ?array
    {
        $conversation = $this->getConversation($companyId, $conversationId);
        if ($conversation === null) {
            return null;
        }

        $messages = $this->getMessages($conversationId);
        $report = $this->buildReport($conversation, $messages);

        return [
            'conversation' => [
                'conversationId' => (int) $conversation['conversation_id'],
                'client' => $conversation['client_phone'] ?? '',
                'channel' => $this->formatChannel((string) ($conversation['conversation_channel'] ?? '')),
                'agent' => $conversation['operator_name'] ?? '',
            ],
            'thread' => array_map(function (array $message): array {
                return [
                    'id' => 'm' . (int) $message['message_id'],
                    'isAgent' => (bool) $message['message_is_operator'],
                    'text' => $message['message_content'] ?? '',
                    'time' => $this->formatTime((string) ($message['message_sent_at'] ?? '')),
                    'date' => $this->formatDate((string) ($message['message_sent_at'] ?? '')),
                ];
            }, $messages),
            'report' => $report,
        ];
    }

    private function getConversation(int $companyId, int $conversationId): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                c.conversation_id,
                c.conversation_channel,
                c.conversation_started_at,
                c.conversation_last_message_at,
                cl.client_phone,
                o.operator_name,
                ca.conversation_analysis_aspect,
                ca.conversation_analysis_sub_aspect,
                ca.conversation_analysis_sentiment,
                ca.conversation_analysis_sentiment_score
            FROM conversation c
            INNER JOIN client cl
                ON cl.client_id = c.client_id
               AND cl.client_deleted_at = 'infinity'
            INNER JOIN operator o
                ON o.operator_id = c.operator_id
               AND o.operator_deleted_at = 'infinity'
            LEFT JOIN conversation_analysis ca
                ON ca.conversation_id = c.conversation_id
               AND ca.conversation_analysis_deleted_at = 'infinity'
            WHERE c.company_id = :company_id
              AND c.conversation_id = :conversation_id
              AND c.conversation_deleted_at = 'infinity'
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'conversation_id' => $conversationId,
        ]);

        $conversation = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $conversation === false ? null : $conversation;
    }

    private function getMessages(int $conversationId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                message_id,
                message_is_operator,
                message_content,
                message_sent_at
            FROM message
            WHERE conversation_id = :conversation_id
              AND message_deleted_at = 'infinity'
              AND message_deleted = FALSE
              AND message_hidden = FALSE
              AND message_is_event = FALSE
            ORDER BY message_sent_at ASC, message_id ASC"
        );
        $stmt->execute(['conversation_id' => $conversationId]);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function buildReport(array $conversation, array $messages): array
    {
        $avgResponseSeconds = $this->calculateAverageResponseSeconds($messages);
        $avgAgentSeconds = $this->calculateAverageGapByAuthor($messages, true);
        $avgClientSeconds = $this->calculateAverageGapByAuthor($messages, false);
        $totalDurationSeconds = $this->calculateTotalDurationSeconds($conversation, $messages);
        $lastMessageAuthor = $this->getLastMessageAuthor($messages);
        $consecutiveMessages = $this->hasTrailingConsecutiveMessages($messages);

        return [
            'avgResponse' => $this->formatDuration($avgResponseSeconds),
            'totalDuration' => $this->formatDuration($totalDurationSeconds),
            'avgAgent' => $this->formatDuration($avgAgentSeconds),
            'avgClient' => $this->formatDuration($avgClientSeconds),
            'mainAspect' => $conversation['conversation_analysis_aspect'] ?? '',
            'subAspect' => $conversation['conversation_analysis_sub_aspect'] ?? '',
            'lastMessageAuthor' => $lastMessageAuthor,
            'consecutiveMessages' => $consecutiveMessages,
            'sentiment' => $this->normalizeSentimentLabel((string) ($conversation['conversation_analysis_sentiment'] ?? '')),
            'score' => round((float) ($conversation['conversation_analysis_sentiment_score'] ?? 0), 2),
        ];
    }

    private function calculateAverageResponseSeconds(array $messages): int
    {
        $responseTimes = [];
        $pendingClientTimestamp = null;

        foreach ($messages as $message) {
            $timestamp = strtotime((string) ($message['message_sent_at'] ?? ''));
            if ($timestamp === false) {
                continue;
            }

            $isOperator = (bool) ($message['message_is_operator'] ?? false);

            if (!$isOperator) {
                if ($pendingClientTimestamp === null) {
                    $pendingClientTimestamp = $timestamp;
                }

                continue;
            }

            if ($pendingClientTimestamp !== null && $timestamp >= $pendingClientTimestamp) {
                $responseTimes[] = $timestamp - $pendingClientTimestamp;
                $pendingClientTimestamp = null;
            }
        }

        if ($responseTimes === []) {
            return 0;
        }

        return (int) round(array_sum($responseTimes) / count($responseTimes));
    }

    private function calculateAverageGapByAuthor(array $messages, bool $isOperator): int
    {
        $timestamps = [];

        foreach ($messages as $message) {
            if ((bool) ($message['message_is_operator'] ?? false) !== $isOperator) {
                continue;
            }

            $timestamp = strtotime((string) ($message['message_sent_at'] ?? ''));
            if ($timestamp === false) {
                continue;
            }

            $timestamps[] = $timestamp;
        }

        if (count($timestamps) < 2) {
            return 0;
        }

        $gaps = [];
        for ($i = 1, $len = count($timestamps); $i < $len; $i++) {
            $gap = $timestamps[$i] - $timestamps[$i - 1];
            if ($gap >= 0) {
                $gaps[] = $gap;
            }
        }

        if ($gaps === []) {
            return 0;
        }

        return (int) round(array_sum($gaps) / count($gaps));
    }

    private function calculateTotalDurationSeconds(array $conversation, array $messages): int
    {
        if (count($messages) >= 2) {
            $first = strtotime((string) ($messages[0]['message_sent_at'] ?? ''));
            $last = strtotime((string) ($messages[count($messages) - 1]['message_sent_at'] ?? ''));
            if ($first !== false && $last !== false && $last >= $first) {
                return $last - $first;
            }
        }

        $startedAt = strtotime((string) ($conversation['conversation_started_at'] ?? ''));
        $lastMessageAt = strtotime((string) ($conversation['conversation_last_message_at'] ?? ''));
        if ($startedAt === false || $lastMessageAt === false || $lastMessageAt < $startedAt) {
            return 0;
        }

        return $lastMessageAt - $startedAt;
    }

    private function getLastMessageAuthor(array $messages): string
    {
        if ($messages === []) {
            return 'Cliente';
        }

        $lastMessage = $messages[count($messages) - 1];

        return (bool) ($lastMessage['message_is_operator'] ?? false) ? 'Operador' : 'Cliente';
    }

    private function hasTrailingConsecutiveMessages(array $messages): bool
    {
        $count = count($messages);
        if ($count < 2) {
            return false;
        }

        $last = (bool) ($messages[$count - 1]['message_is_operator'] ?? false);
        $previous = (bool) ($messages[$count - 2]['message_is_operator'] ?? false);

        return $last === $previous;
    }

    private function normalizeSentimentLabel(string $label): string
    {
        $normalized = trim($label);
        if ($normalized === '') {
            return 'NEUTRO';
        }

        return mb_strtoupper(str_replace('_', ' ', $normalized));
    }

    private function formatChannel(string $channel): string
    {
        $normalized = strtolower(trim($channel));

        if ($normalized === 'whatsapp') {
            return 'WhatsApp';
        }

        if ($normalized === '') {
            return '';
        }

        return ucfirst($normalized);
    }

    private function formatTime(string $dateTime): string
    {
        $timestamp = strtotime($dateTime);
        if ($timestamp === false) {
            return '00:00';
        }

        return date('H:i', $timestamp);
    }

    private function formatDate(string $dateTime): string
    {
        $timestamp = strtotime($dateTime);
        if ($timestamp === false) {
            return '';
        }

        return date('Y-m-d', $timestamp);
    }

    private function formatDuration(int $seconds): string
    {
        $seconds = max(0, $seconds);
        $minutes = intdiv($seconds, 60);
        $remainingSeconds = $seconds % 60;

        return str_pad((string) $minutes, 2, '0', STR_PAD_LEFT) . ':' . str_pad((string) $remainingSeconds, 2, '0', STR_PAD_LEFT);
    }
}