nettown_backend/models/AgentsModel.php

<?php

namespace Models;

use Libs\Database;

class AgentsModel
{
    private \PDO $pdo;

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

    public function getAgentsData(int $companyId, array $queryParams = []): array
    {
        $anchorDate = $this->getAnchorDate($companyId);
        $filters = $this->normalizeFilters($queryParams);
        $items = $this->getItems($companyId, $anchorDate, $filters);

        return [
            'items' => $items,
            'stats' => $this->getStats($companyId),
        ];
    }

    public function saveAgent(int $companyId, array $payload): ?array
    {
        $data = $this->normalizePayload($payload);
        if ($data === null) {
            return null;
        }

        if ($this->emailExists($companyId, $data['email'], $data['id'])) {
            return null;
        }

        if ($data['id'] !== null) {
            if (!$this->agentExists($companyId, $data['id'])) {
                return null;
            }

            $stmt = $this->pdo->prepare(
                "UPDATE operator
                SET operator_name = :name,
                    operator_initials = :initials,
                    operator_email = :email,
                    operator_phone = :phone,
                    operator_department = :department,
                    operator_status = :status,
                    operator_available_for_escalation = :available_for_escalation
                WHERE company_id = :company_id
                  AND operator_id = :operator_id
                  AND operator_deleted_at = 'infinity'"
            );
            $stmt->execute([
                'name' => $data['name'],
                'initials' => $data['initials'],
                'email' => $data['email'],
                'phone' => $data['phone'],
                'department' => $data['department'],
                'status' => $data['status'],
                'available_for_escalation' => $data['available_for_escalation'],
                'company_id' => $companyId,
                'operator_id' => $data['id'],
            ]);

            $operatorId = $data['id'];
        } else {
            $stmt = $this->pdo->prepare(
                "INSERT INTO operator (
                    company_id,
                    operator_name,
                    operator_initials,
                    operator_email,
                    operator_phone,
                    operator_department,
                    operator_status,
                    operator_available_for_escalation
                ) VALUES (
                    :company_id,
                    :name,
                    :initials,
                    :email,
                    :phone,
                    :department,
                    :status,
                    :available_for_escalation
                ) RETURNING operator_id"
            );
            $stmt->execute([
                'company_id' => $companyId,
                'name' => $data['name'],
                'initials' => $data['initials'],
                'email' => $data['email'],
                'phone' => $data['phone'],
                'department' => $data['department'],
                'status' => $data['status'],
                'available_for_escalation' => $data['available_for_escalation'],
            ]);
            $operatorId = (int) $stmt->fetchColumn();
        }

        $this->replaceChannels($operatorId, $data['channels']);

        return $this->getAgentItemById($companyId, $operatorId, $this->getAnchorDate($companyId));
    }

    public function toggleAgentStatus(int $companyId, int $agentId): ?array
    {
        $agent = $this->getOperatorRow($companyId, $agentId);
        if ($agent === null) {
            return null;
        }

        $currentStatus = (string) ($agent['operator_status'] ?? 'Inativo');
        $nextStatus = $this->normalizeStatus($currentStatus) === 'Inativo' ? 'Ativo' : 'Inativo';

        $stmt = $this->pdo->prepare(
            "UPDATE operator
            SET operator_status = :status
            WHERE company_id = :company_id
              AND operator_id = :operator_id
              AND operator_deleted_at = 'infinity'"
        );
        $stmt->execute([
            'status' => $nextStatus,
            'company_id' => $companyId,
            'operator_id' => $agentId,
        ]);

        return $this->getAgentItemById($companyId, $agentId, $this->getAnchorDate($companyId));
    }

    public function toggleAgentEscalation(int $companyId, int $agentId): ?array
    {
        $agent = $this->getOperatorRow($companyId, $agentId);
        if ($agent === null) {
            return null;
        }

        $stmt = $this->pdo->prepare(
            "UPDATE operator
            SET operator_available_for_escalation = :available_for_escalation
            WHERE company_id = :company_id
              AND operator_id = :operator_id
              AND operator_deleted_at = 'infinity'"
        );
        $stmt->execute([
            'available_for_escalation' => !((bool) ($agent['operator_available_for_escalation'] ?? false)),
            'company_id' => $companyId,
            'operator_id' => $agentId,
        ]);

        return $this->getAgentItemById($companyId, $agentId, $this->getAnchorDate($companyId));
    }

    private function getAnchorDate(int $companyId): \DateTimeImmutable
    {
        $stmt = $this->pdo->prepare(
            "SELECT MAX(operator_stat_date)
            FROM operator_daily_stats
            WHERE company_id = :company_id"
        );
        $stmt->execute(['company_id' => $companyId]);
        $date = $stmt->fetchColumn();

        if (!is_string($date) || $date === '') {
            return new \DateTimeImmutable('today');
        }

        return new \DateTimeImmutable($date);
    }

    private function normalizeFilters(array $queryParams): array
    {
        $search = trim((string) ($queryParams['search'] ?? ''));
        $department = trim((string) ($queryParams['department'] ?? ''));
        $channel = mb_strtolower(trim((string) ($queryParams['channel'] ?? '')));
        $status = trim((string) ($queryParams['status'] ?? ''));

        return [
            'search' => $search,
            'department' => $department,
            'channel' => in_array($channel, ['whatsapp', 'instagram'], true) ? $channel : '',
            'status' => $status,
        ];
    }

    private function getItems(int $companyId, \DateTimeImmutable $anchorDate, array $filters): array
    {
        $sql = "SELECT
                o.operator_id,
                o.operator_name,
                o.operator_initials,
                o.operator_email,
                o.operator_department,
                o.operator_status,
                o.operator_available_for_escalation,
                COALESCE(curr.operator_attendances_count, 0) AS today_attendances,
                curr.operator_avg_response_seconds AS avg_response_seconds,
                prev.operator_avg_response_seconds AS prev_avg_response_seconds,
                COALESCE(curr.operator_sla_compliance_pct, 0) AS sla_pct,
                COALESCE(string_agg(DISTINCT lower(oc.operator_channel), ',' ORDER BY lower(oc.operator_channel)), '') AS channels
            FROM operator o
            LEFT JOIN operator_daily_stats curr
                ON curr.operator_id = o.operator_id
               AND curr.operator_stat_date = :anchor_date
            LEFT JOIN operator_daily_stats prev
                ON prev.operator_id = o.operator_id
               AND prev.operator_stat_date = :previous_date
            LEFT JOIN operator_channel oc
                ON oc.operator_id = o.operator_id
               AND oc.operator_channel_deleted_at = 'infinity'
            WHERE o.company_id = :company_id
              AND o.operator_deleted_at = 'infinity'";

        $params = [
            'company_id' => $companyId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
            'previous_date' => $anchorDate->modify('-1 day')->format('Y-m-d'),
        ];

        if ($filters['search'] !== '') {
            $sql .= ' AND (o.operator_name ILIKE :search OR o.operator_email ILIKE :search)';
            $params['search'] = '%' . $filters['search'] . '%';
        }

        if ($filters['department'] !== '') {
            $sql .= ' AND o.operator_department = :department';
            $params['department'] = $filters['department'];
        }

        if ($filters['status'] !== '') {
            if ($filters['status'] === 'Disponível para Escalonamento') {
                $sql .= ' AND o.operator_available_for_escalation = TRUE';
            } else {
                $sql .= ' AND o.operator_status = :status';
                $params['status'] = $filters['status'];
            }
        }

        if ($filters['channel'] !== '') {
            $sql .= "
              AND EXISTS (
                    SELECT 1
                    FROM operator_channel oc_filter
                    WHERE oc_filter.operator_id = o.operator_id
                      AND oc_filter.operator_channel_deleted_at = 'infinity'
                      AND lower(oc_filter.operator_channel) = :channel
              )";
            $params['channel'] = $filters['channel'];
        }

        $sql .= " GROUP BY
                o.operator_id,
                curr.operator_attendances_count,
                curr.operator_avg_response_seconds,
                prev.operator_avg_response_seconds,
                curr.operator_sla_compliance_pct";
        $sql .= ' ORDER BY o.operator_name ASC, o.operator_id ASC';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $items = [];
        foreach ($rows as $row) {
            $items[] = $this->formatAgentItem($row, $this->splitChannels((string) ($row['channels'] ?? '')));
        }

        return $items;
    }

    private function getStats(int $companyId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                COUNT(*) AS total,
                COUNT(*) FILTER (
                    WHERE operator_status IN ('Ativo', 'Em Atendimento', 'Disponível')
                ) AS active,
                COUNT(*) FILTER (
                    WHERE operator_status = 'Em Atendimento'
                ) AS in_attendance,
                COUNT(*) FILTER (
                    WHERE operator_available_for_escalation = TRUE
                ) AS available_for_escalation
            FROM operator
            WHERE company_id = :company_id
              AND operator_deleted_at = 'infinity'"
        );
        $stmt->execute(['company_id' => $companyId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC) ?: [];

        return [
            'total' => (int) ($row['total'] ?? 0),
            'active' => (int) ($row['active'] ?? 0),
            'inAttendance' => (int) ($row['in_attendance'] ?? 0),
            'availableForEscalation' => (int) ($row['available_for_escalation'] ?? 0),
        ];
    }

    /**
     * Converte a lista de canais agregada pelo banco (string separada por vírgula)
     * em array de canais únicos. Os canais já vêm em minúsculas do string_agg.
     */
    private function splitChannels(string $raw): array
    {
        if ($raw === '') {
            return [];
        }

        $channels = array_filter(
            array_map('trim', explode(',', $raw)),
            static fn(string $channel): bool => $channel !== ''
        );

        return array_values(array_unique($channels));
    }

    private function formatAgentItem(array $row, array $channels): array
    {
        $currentResponseSeconds = isset($row['avg_response_seconds']) ? (int) $row['avg_response_seconds'] : null;
        $previousResponseSeconds = isset($row['prev_avg_response_seconds']) ? (int) $row['prev_avg_response_seconds'] : null;

        return [
            'id' => (int) ($row['operator_id'] ?? 0),
            'name' => $row['operator_name'] ?? '',
            'email' => $row['operator_email'] ?? '',
            'initials' => $row['operator_initials'] ?? '',
            'department' => $row['operator_department'] ?? '',
            'channels' => array_values(array_unique($channels)),
            'status' => $this->normalizeStatus((string) ($row['operator_status'] ?? 'Inativo')),
            'availableForEscalation' => (bool) ($row['operator_available_for_escalation'] ?? false),
            'todayAttendances' => (int) ($row['today_attendances'] ?? 0),
            'avgResponseTime' => $this->formatDuration($currentResponseSeconds),
            'responseTimeTrend' => $this->resolveResponseTimeTrend($currentResponseSeconds, $previousResponseSeconds),
            'slaPct' => (int) round((float) ($row['sla_pct'] ?? 0)),
        ];
    }

    private function normalizePayload(array $payload): ?array
    {
        $id = isset($payload['id']) ? (int) $payload['id'] : null;
        if ($id !== null && $id <= 0) {
            $id = null;
        }

        $name = trim((string) ($payload['name'] ?? ''));
        $email = mb_strtolower(trim((string) ($payload['email'] ?? '')));
        $department = trim((string) ($payload['department'] ?? ''));
        $status = $this->normalizeStatus((string) ($payload['status'] ?? 'Ativo'));
        $availableForEscalation = (bool) ($payload['availableForEscalation'] ?? false);
        $channels = array_values(array_filter(array_map(static function ($channel): string {
            return mb_strtolower(trim((string) $channel));
        }, is_array($payload['channels'] ?? null) ? $payload['channels'] : []), static function (string $channel): bool {
            return in_array($channel, ['whatsapp', 'instagram'], true);
        }));

        if ($name === '' || $email === '' || $department === '') {
            return null;
        }

        return [
            'id' => $id,
            'name' => $name,
            'email' => $email,
            'department' => $department,
            'status' => $status,
            'available_for_escalation' => $availableForEscalation,
            'channels' => array_values(array_unique($channels)),
            'initials' => $this->buildInitials($name),
            'phone' => trim((string) ($payload['phone'] ?? '')),
        ];
    }

    private function emailExists(int $companyId, string $email, ?int $ignoreId): bool
    {
        $sql = "SELECT 1
            FROM operator
            WHERE company_id = :company_id
              AND operator_deleted_at = 'infinity'
              AND lower(operator_email) = :email";
        $params = [
            'company_id' => $companyId,
            'email' => $email,
        ];

        if ($ignoreId !== null) {
            $sql .= ' AND operator_id <> :ignore_id';
            $params['ignore_id'] = $ignoreId;
        }

        $sql .= ' LIMIT 1';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

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

    private function agentExists(int $companyId, int $agentId): bool
    {
        $stmt = $this->pdo->prepare(
            "SELECT 1
            FROM operator
            WHERE company_id = :company_id
              AND operator_id = :operator_id
              AND operator_deleted_at = 'infinity'
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'operator_id' => $agentId,
        ]);

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

    private function replaceChannels(int $operatorId, array $channels): void
    {
        $stmt = $this->pdo->prepare(
            "UPDATE operator_channel
            SET operator_channel_deleted_at = NOW()
            WHERE operator_id = :operator_id
              AND operator_channel_deleted_at = 'infinity'"
        );
        $stmt->execute(['operator_id' => $operatorId]);

        if ($channels === []) {
            return;
        }

        $insert = $this->pdo->prepare(
            "INSERT INTO operator_channel (operator_id, operator_channel)
            VALUES (:operator_id, :channel)"
        );
        foreach ($channels as $channel) {
            $insert->execute([
                'operator_id' => $operatorId,
                'channel' => $channel,
            ]);
        }
    }

    private function getOperatorRow(int $companyId, int $agentId): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM operator
            WHERE company_id = :company_id
              AND operator_id = :operator_id
              AND operator_deleted_at = 'infinity'
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'operator_id' => $agentId,
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

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

    private function getAgentItemById(int $companyId, int $agentId, \DateTimeImmutable $anchorDate): ?array
    {
        $sql = "SELECT
                o.operator_id,
                o.operator_name,
                o.operator_initials,
                o.operator_email,
                o.operator_department,
                o.operator_status,
                o.operator_available_for_escalation,
                COALESCE(curr.operator_attendances_count, 0) AS today_attendances,
                curr.operator_avg_response_seconds AS avg_response_seconds,
                prev.operator_avg_response_seconds AS prev_avg_response_seconds,
                COALESCE(curr.operator_sla_compliance_pct, 0) AS sla_pct,
                COALESCE(string_agg(DISTINCT lower(oc.operator_channel), ',' ORDER BY lower(oc.operator_channel)), '') AS channels
            FROM operator o
            LEFT JOIN operator_daily_stats curr
                ON curr.operator_id = o.operator_id
               AND curr.operator_stat_date = :anchor_date
            LEFT JOIN operator_daily_stats prev
                ON prev.operator_id = o.operator_id
               AND prev.operator_stat_date = :previous_date
            LEFT JOIN operator_channel oc
                ON oc.operator_id = o.operator_id
               AND oc.operator_channel_deleted_at = 'infinity'
            WHERE o.company_id = :company_id
              AND o.operator_id = :operator_id
              AND o.operator_deleted_at = 'infinity'
            GROUP BY
                o.operator_id,
                curr.operator_attendances_count,
                curr.operator_avg_response_seconds,
                prev.operator_avg_response_seconds,
                curr.operator_sla_compliance_pct
            LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'company_id' => $companyId,
            'operator_id' => $agentId,
            'anchor_date' => $anchorDate->format('Y-m-d'),
            'previous_date' => $anchorDate->modify('-1 day')->format('Y-m-d'),
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        if ($row === false) {
            return null;
        }

        return $this->formatAgentItem($row, $this->splitChannels((string) ($row['channels'] ?? '')));
    }

    private function buildInitials(string $name): string
    {
        $parts = preg_split('/\s+/', trim($name)) ?: [];
        $letters = array_slice(array_filter($parts), 0, 2);
        $initials = '';

        foreach ($letters as $part) {
            $initials .= mb_strtoupper(mb_substr($part, 0, 1));
        }

        return $initials !== '' ? $initials : 'AG';
    }

    private function normalizeStatus(string $status): string
    {
        $normalized = mb_strtolower(trim($status));

        if (in_array($normalized, ['ativo', 'active'], true)) {
            return 'Ativo';
        }

        if (in_array($normalized, ['em atendimento', 'attending'], true)) {
            return 'Em Atendimento';
        }

        if (in_array($normalized, ['disponível', 'disponivel', 'available'], true)) {
            return 'Disponível';
        }

        return 'Inativo';
    }

    private function formatDuration(?int $seconds): string
    {
        if ($seconds === null || $seconds <= 0) {
            return '—';
        }

        $minutes = intdiv($seconds, 60);
        $remainingSeconds = $seconds % 60;

        return sprintf('%dm %02ds', $minutes, $remainingSeconds);
    }

    private function resolveResponseTimeTrend(?int $currentSeconds, ?int $previousSeconds): string
    {
        if ($currentSeconds === null || $currentSeconds <= 0 || $previousSeconds === null || $previousSeconds <= 0) {
            return 'stable';
        }

        if ($currentSeconds < $previousSeconds) {
            return 'down';
        }

        if ($currentSeconds > $previousSeconds) {
            return 'up';
        }

        return 'stable';
    }
}