nettown_backend/models/InteractionsModel.php

<?php

namespace Models;

use Libs\Database;

class InteractionsModel
{
    private \PDO $pdo;

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

    public function getInteractionsData(int $companyId, string $userEmail, array $queryParams): array
    {
        $filters = $this->normalizeFilters($queryParams);
        $myOperatorId = $this->getOperatorIdByUserEmail($companyId, $userEmail);
        [$whereSql, $params] = $this->buildWhereClause($companyId, $filters, $myOperatorId);

        $total = $this->getTotalCount($whereSql, $params);
        $items = $this->getItems($whereSql, $params, $filters['page'], $filters['per_page']);

        return [
            'items' => $items,
            'pagination' => [
                'page' => $filters['page'],
                'per_page' => $filters['per_page'],
                'total' => $total,
                'total_pages' => $filters['per_page'] > 0 ? (int) ceil($total / $filters['per_page']) : 0,
            ],
        ];
    }

    private function getOperatorIdByUserEmail(int $companyId, string $userEmail): ?int
    {
        $normalizedEmail = mb_strtolower(trim($userEmail));
        if ($normalizedEmail === '') {
            return null;
        }

        $stmt = $this->pdo->prepare(
            "SELECT operator_id
            FROM operator
            WHERE company_id = :company_id
              AND operator_deleted_at = 'infinity'
              AND lower(operator_email) = :email
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'email' => $normalizedEmail,
        ]);
        $operatorId = $stmt->fetchColumn();

        return $operatorId === false ? null : (int) $operatorId;
    }

    private function normalizeFilters(array $queryParams): array
    {
        $page = max(1, (int) ($queryParams['page'] ?? 1));
        $perPage = (int) ($queryParams['per_page'] ?? 20);
        $perPage = max(1, min(100, $perPage));

        $filter = strtolower(trim((string) ($queryParams['filter'] ?? 'all')));
        if (!in_array($filter, ['all', 'my_clients', 'new', 'unfinished'], true)) {
            $filter = 'all';
        }

        $sentiment = strtolower(trim((string) ($queryParams['sentiment'] ?? 'all')));
        if (!in_array($sentiment, ['all', 'positive', 'neutral', 'negative'], true)) {
            $sentiment = 'all';
        }

        return [
            'page' => $page,
            'per_page' => $perPage,
            'search' => trim((string) ($queryParams['search'] ?? '')),
            'filter' => $filter,
            'sentiment' => $sentiment,
            'operator_id' => max(0, (int) ($queryParams['operator_id'] ?? 0)),
        ];
    }

    private function buildWhereClause(int $companyId, array $filters, ?int $myOperatorId): array
    {
        $where = [
            "c.company_id = :company_id",
            "c.conversation_deleted_at = 'infinity'",
            "cl.client_deleted_at = 'infinity'",
        ];
        $params = ['company_id' => $companyId];

        if ($filters['search'] !== '') {
            $where[] = '(cl.client_name ILIKE :search OR cl.client_phone ILIKE :search OR o.operator_name ILIKE :search OR c.conversation_last_message_preview ILIKE :search)';
            $params['search'] = '%' . $filters['search'] . '%';
        }

        if ($filters['operator_id'] > 0) {
            $where[] = 'c.operator_id = :operator_id';
            $params['operator_id'] = $filters['operator_id'];
        }

        if ($filters['filter'] === 'unfinished') {
            $where[] = "lower(c.conversation_status) <> 'closed'";
        }

        if ($filters['filter'] === 'new') {
            $where[] = "c.conversation_started_at >= NOW() - INTERVAL '24 hours'";
        }

        if ($filters['filter'] === 'my_clients') {
            if ($myOperatorId !== null) {
                $where[] = 'c.operator_id = :my_operator_id';
                $params['my_operator_id'] = $myOperatorId;
            } else {
                $where[] = '1 = 0';
            }
        }

        if ($filters['sentiment'] !== 'all') {
            $where[] = $this->getSentimentWhereClause('ca', $filters['sentiment']);
        }

        return [implode("\n              AND ", $where), $params];
    }

    private function getTotalCount(string $whereSql, array $params): int
    {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*)
            FROM conversation c
            INNER JOIN client cl ON cl.client_id = c.client_id
            LEFT 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 {$whereSql}"
        );
        $stmt->execute($params);

        return (int) $stmt->fetchColumn();
    }

    private function getItems(string $whereSql, array $params, int $page, int $perPage): array
    {
        $offset = ($page - 1) * $perPage;
        $params['limit'] = $perPage;
        $params['offset'] = $offset;

        $stmt = $this->pdo->prepare(
            "SELECT
                c.conversation_id,
                cl.client_phone,
                o.operator_name,
                COALESCE(ca.conversation_analysis_sentiment, c.conversation_status) AS sentiment_label,
                COALESCE(ca.conversation_analysis_sentiment_score, 0) AS sentiment_score,
                COALESCE(ca.conversation_analysis_aspect, '') AS aspect,
                COALESCE(ca.conversation_analysis_sub_aspect, '') AS sub_aspect,
                c.conversation_last_message_at
            FROM conversation c
            INNER JOIN client cl ON cl.client_id = c.client_id
            LEFT 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 {$whereSql}
            ORDER BY c.conversation_last_message_at DESC, c.conversation_id DESC
            LIMIT :limit OFFSET :offset"
        );

        foreach ($params as $key => $value) {
            if (in_array($key, ['limit', 'offset', 'operator_id', 'my_operator_id', 'company_id'], true)) {
                $stmt->bindValue(':' . $key, (int) $value, \PDO::PARAM_INT);
                continue;
            }

            $stmt->bindValue(':' . $key, $value);
        }

        $stmt->execute();
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        return array_map(function (array $row): array {
            return [
                'conversationId' => (int) $row['conversation_id'],
                'client' => $row['client_phone'] ?? '',
                'agent' => $row['operator_name'] ?? '',
                'sentiment' => $this->normalizeSentimentLabel((string) ($row['sentiment_label'] ?? '')),
                'score' => round((float) ($row['sentiment_score'] ?? 0), 2),
                'aspect' => $row['aspect'] ?? '',
                'subaspect' => $row['sub_aspect'] ?? '',
                'datetime' => $this->formatIsoDateTime($row['conversation_last_message_at'] ?? null),
            ];
        }, $rows);
    }

    private function getSentimentWhereClause(string $analysisAlias, string $sentiment): string
    {
        if ($sentiment === 'positive') {
            return "(
                lower(COALESCE({$analysisAlias}.conversation_analysis_sentiment, '')) IN ('positive', 'positivo')
                OR {$analysisAlias}.conversation_analysis_sentiment_score >= 0.15
            )";
        }

        if ($sentiment === 'negative') {
            return "(
                lower(COALESCE({$analysisAlias}.conversation_analysis_sentiment, '')) IN ('negative', 'negativo')
                OR {$analysisAlias}.conversation_analysis_sentiment_score <= -0.15
            )";
        }

        return "(
            {$analysisAlias}.conversation_id IS NOT NULL
            AND lower(COALESCE({$analysisAlias}.conversation_analysis_sentiment, '')) NOT IN ('positive', 'positivo', 'negative', 'negativo')
            AND {$analysisAlias}.conversation_analysis_sentiment_score > -0.15
            AND {$analysisAlias}.conversation_analysis_sentiment_score < 0.15
        )";
    }

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

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

    private function formatIsoDateTime(?string $dateTime): ?string
    {
        if (!$dateTime) {
            return null;
        }

        $timestamp = strtotime($dateTime);
        if ($timestamp === false) {
            return null;
        }

        return gmdate('Y-m-d\TH:i:s\Z', $timestamp);
    }
}