nettown_backend/models/SlaConfigsModel.php

<?php

namespace Models;

use Libs\Database;

class SlaConfigsModel
{
    private const DEFAULT_ALERT_PERCENT = 80;

    private \PDO $pdo;

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

    public function getConfigs(int $companyId): array
    {
        $configs = $this->fetchConfigs($companyId);
        if ($configs === []) {
            return ['items' => []];
        }

        $liveDataByDepartment = $this->getLiveDataByDepartment($companyId);
        $items = [];

        foreach ($configs as $config) {
            $departmentName = trim((string) ($config['sla_config_department'] ?? ''));
            $departmentKey = mb_strtolower($departmentName);
            $liveData = $liveDataByDepartment[$departmentKey] ?? null;

            $items[] = [
                'id' => $this->slugify($departmentName),
                'name' => $departmentName,
                'firstResponseH' => (int) ($config['sla_config_response_hours'] ?? 0),
                'firstResponseM' => 0,
                'resolutionH' => (int) ($config['sla_config_resolution_hours'] ?? 0),
                'alertPct' => self::DEFAULT_ALERT_PERCENT,
                'liveStatus' => $this->resolveLiveStatus($liveData),
                'liveDetail' => $this->resolveLiveDetail($liveData),
                'lastUpdated' => $this->formatRelativeTime($config['sla_config_updated_at'] ?? null),
            ];
        }

        return ['items' => $items];
    }

    public function saveConfig(int $companyId, array $payload): array
    {
        $id = trim((string) ($payload['id'] ?? ''));
        $name = trim((string) ($payload['name'] ?? ''));
        $firstResponseHours = (int) ($payload['firstResponseH'] ?? 0);
        $firstResponseMinutes = (int) ($payload['firstResponseM'] ?? 0);
        $resolutionHours = (int) ($payload['resolutionH'] ?? 0);

        $existingConfig = $id !== '' ? $this->findConfigBySlugId($companyId, $id) : null;
        if ($id !== '' && $existingConfig === null) {
            return ['status' => 'not_found'];
        }

        $departmentName = $name !== ''
            ? $name
            : (string) ($existingConfig['sla_config_department'] ?? '');

        if ($departmentName === '') {
            return ['status' => 'invalid'];
        }

        $responseHoursToPersist = $this->normalizeResponseHoursToPersist($firstResponseHours, $firstResponseMinutes);

        try {
            if ($existingConfig !== null) {
                $stmt = $this->pdo->prepare(
                    "UPDATE sla_config
                    SET sla_config_department = :department,
                        sla_config_response_hours = :response_hours,
                        sla_config_resolution_hours = :resolution_hours,
                        sla_config_updated_at = NOW()
                    WHERE company_id = :company_id
                      AND sla_config_department = :current_department
                      AND sla_config_deleted_at = 'infinity'"
                );
                $stmt->execute([
                    'department' => $departmentName,
                    'response_hours' => $responseHoursToPersist,
                    'resolution_hours' => $resolutionHours,
                    'company_id' => $companyId,
                    'current_department' => $existingConfig['sla_config_department'],
                ]);

                $status = 'updated';
            } else {
                $stmt = $this->pdo->prepare(
                    "INSERT INTO sla_config (
                        company_id,
                        sla_config_department,
                        sla_config_response_hours,
                        sla_config_resolution_hours
                    ) VALUES (
                        :company_id,
                        :department,
                        :response_hours,
                        :resolution_hours
                    )"
                );
                $stmt->execute([
                    'company_id' => $companyId,
                    'department' => $departmentName,
                    'response_hours' => $responseHoursToPersist,
                    'resolution_hours' => $resolutionHours,
                ]);

                $status = 'created';
            }
        } catch (\PDOException $e) {
            if ($e->getCode() === '23505') {
                return ['status' => 'duplicate'];
            }

            return ['status' => 'error'];
        }

        $savedItem = $this->getConfigItemByDepartmentName($companyId, $departmentName);
        if ($savedItem === null) {
            return ['status' => 'error'];
        }

        return [
            'status' => $status,
            'item' => $savedItem,
        ];
    }

    public function getLiveStatus(int $companyId): array
    {
        $configItems = $this->getConfigs($companyId)['items'] ?? [];
        $items = [];

        foreach ($configItems as $item) {
            $items[] = [
                'id' => $item['id'] ?? '',
                'name' => $item['name'] ?? '',
                'liveStatus' => $item['liveStatus'] ?? 'ok',
                'liveDetail' => $item['liveDetail'] ?? 'dentro do SLA',
                'lastUpdated' => $item['lastUpdated'] ?? 'agora',
            ];
        }

        return ['items' => $items];
    }

    private function fetchConfigs(int $companyId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                sla_config_department,
                sla_config_response_hours,
                sla_config_resolution_hours,
                sla_config_updated_at
            FROM sla_config
            WHERE company_id = :company_id
              AND sla_config_deleted_at = 'infinity'
            ORDER BY sla_config_department ASC"
        );
        $stmt->execute(['company_id' => $companyId]);

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

    private function findConfigBySlugId(int $companyId, string $id): ?array
    {
        $configs = $this->fetchConfigs($companyId);
        foreach ($configs as $config) {
            $departmentName = trim((string) ($config['sla_config_department'] ?? ''));
            if ($departmentName === '') {
                continue;
            }

            if ($this->slugify($departmentName) === $id) {
                return $config;
            }
        }

        return null;
    }

    private function getConfigItemByDepartmentName(int $companyId, string $departmentName): ?array
    {
        $items = $this->getConfigs($companyId)['items'] ?? [];
        foreach ($items as $item) {
            if (($item['name'] ?? '') === $departmentName) {
                return $item;
            }
        }

        return null;
    }

    private function normalizeResponseHoursToPersist(int $hours, int $minutes): int
    {
        $totalMinutes = max(0, ($hours * 60) + $minutes);
        if ($totalMinutes === 0) {
            return 0;
        }

        return (int) ceil($totalMinutes / 60);
    }

    private function getLiveDataByDepartment(int $companyId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT
                lower(trim(o.operator_department)) AS department_key,
                MAX(
                    CASE
                        WHEN c.conversation_id IS NOT NULL
                         AND c.conversation_sla_deadline < NOW()
                        THEN EXTRACT(EPOCH FROM (NOW() - c.conversation_sla_deadline))
                        ELSE NULL
                    END
                ) AS max_overdue_seconds,
                MAX(
                    CASE
                        WHEN c.conversation_id IS NOT NULL
                         AND c.conversation_sla_deadline >= NOW()
                         AND EXTRACT(EPOCH FROM (c.conversation_sla_deadline - c.conversation_started_at)) > 0
                        THEN (
                            EXTRACT(EPOCH FROM (NOW() - c.conversation_started_at))
                            / EXTRACT(EPOCH FROM (c.conversation_sla_deadline - c.conversation_started_at))
                        ) * 100
                        ELSE NULL
                    END
                ) AS max_progress_pct
            FROM operator o
            LEFT JOIN conversation c
                ON c.operator_id = o.operator_id
               AND c.company_id = :company_id
               AND c.conversation_deleted_at = 'infinity'
               AND c.conversation_status = 'open'
            WHERE o.company_id = :company_id
              AND o.operator_deleted_at = 'infinity'
            GROUP BY lower(trim(o.operator_department))"
        );
        $stmt->execute(['company_id' => $companyId]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $grouped = [];
        foreach ($rows as $row) {
            $key = (string) ($row['department_key'] ?? '');
            if ($key === '') {
                continue;
            }

            $grouped[$key] = [
                'max_overdue_seconds' => $row['max_overdue_seconds'] !== null ? (float) $row['max_overdue_seconds'] : null,
                'max_progress_pct' => $row['max_progress_pct'] !== null ? (float) $row['max_progress_pct'] : null,
            ];
        }

        return $grouped;
    }

    private function resolveLiveStatus(?array $liveData): string
    {
        $maxOverdueSeconds = $liveData['max_overdue_seconds'] ?? null;
        if ($maxOverdueSeconds !== null && $maxOverdueSeconds > 0) {
            return 'breach';
        }

        $maxProgressPct = $liveData['max_progress_pct'] ?? null;
        if ($maxProgressPct !== null && $maxProgressPct >= self::DEFAULT_ALERT_PERCENT) {
            return 'warning';
        }

        return 'ok';
    }

    private function resolveLiveDetail(?array $liveData): string
    {
        $maxOverdueSeconds = $liveData['max_overdue_seconds'] ?? null;
        if ($maxOverdueSeconds !== null && $maxOverdueSeconds > 0) {
            return $this->formatOverdueTime((int) round($maxOverdueSeconds)) . ' estourado';
        }

        $maxProgressPct = $liveData['max_progress_pct'] ?? null;
        if ($maxProgressPct !== null && $maxProgressPct >= self::DEFAULT_ALERT_PERCENT) {
            $remainingPct = max(0, (int) round(100 - $maxProgressPct));
            return sprintf('próximo de estourar (%d%% restante)', $remainingPct);
        }

        return 'dentro do SLA';
    }

    private function formatOverdueTime(int $seconds): string
    {
        if ($seconds < 60) {
            return $seconds . 's';
        }

        $minutes = intdiv($seconds, 60);
        if ($minutes < 60) {
            return $minutes . 'min';
        }

        $hours = intdiv($minutes, 60);
        $remainingMinutes = $minutes % 60;

        if ($remainingMinutes === 0) {
            return $hours . 'h';
        }

        return sprintf('%dh %dmin', $hours, $remainingMinutes);
    }

    private function formatRelativeTime(?string $timestamp): string
    {
        if ($timestamp === null || trim($timestamp) === '') {
            return 'agora';
        }

        try {
            $dateTime = new \DateTimeImmutable($timestamp);
        } catch (\Throwable $e) {
            return 'agora';
        }

        $diffSeconds = time() - $dateTime->getTimestamp();
        if ($diffSeconds <= 30) {
            return 'agora';
        }

        if ($diffSeconds < 3600) {
            $minutes = max(1, (int) floor($diffSeconds / 60));
            return $minutes === 1 ? 'há 1 minuto' : sprintf('há %d minutos', $minutes);
        }

        if ($diffSeconds < 86400) {
            $hours = max(1, (int) floor($diffSeconds / 3600));
            return $hours === 1 ? 'há 1 hora' : sprintf('há %d horas', $hours);
        }

        $days = max(1, (int) floor($diffSeconds / 86400));
        return $days === 1 ? 'há 1 dia' : sprintf('há %d dias', $days);
    }

    private function slugify(string $value): string
    {
        $normalized = trim($value);
        if ($normalized === '') {
            return '';
        }

        $transliterated = function_exists('iconv') ? iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $normalized) : $normalized;
        $transliterated = is_string($transliterated) ? $transliterated : $normalized;
        $slug = preg_replace('/[^a-zA-Z0-9]+/', '-', $transliterated) ?? '';
        $slug = trim($slug, '-');

        return mb_strtolower($slug);
    }
}