nettown_backend/models/IntegrationsModel.php

<?php

namespace Models;

use Libs\Database;

class IntegrationsModel
{
    private \PDO $pdo;

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

    public function getOperatorIdByUserEmail(int $companyId, string $email): int
    {
        $normalizedEmail = mb_strtolower(trim($email));
        if ($normalizedEmail === '') {
            return 0;
        }

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

        $operatorId = $stmt->fetchColumn();

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

    public function listByCompany(int $companyId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT integration_id, company_id, user_id, operator_id, integration_provider,
                    integration_account_id, integration_external_account_id, integration_account_name,
                    integration_status, integration_is_connected, integration_last_sync_at,
                    integration_last_error, integration_created_at, integration_updated_at
            FROM integration
            WHERE company_id = :company_id
              AND integration_provider = 'whatsapp'
              AND integration_deleted_at = 'infinity'
            ORDER BY integration_created_at DESC, integration_id DESC"
        );
        $stmt->execute(['company_id' => $companyId]);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        return array_map([$this, 'formatIntegration'], $rows);
    }

    public function findById(int $companyId, int $integrationId): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM integration
            WHERE company_id = :company_id
              AND integration_id = :integration_id
              AND integration_deleted_at = 'infinity'
            LIMIT 1"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'integration_id' => $integrationId,
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

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

    public function findByAccountId(string $accountId): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT *
            FROM integration
            WHERE integration_account_id = :account_id
              AND integration_deleted_at = 'infinity'
            LIMIT 1"
        );
        $stmt->execute(['account_id' => $accountId]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

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

    public function upsertWhatsappIntegration(int $companyId, int $userId, int $operatorId, string $accountId, string $status, array $metadata = []): array
    {
        $externalAccountId = $this->extractExternalAccountId($metadata);
        $accountName = $this->extractAccountName($metadata, $accountId);
        $isConnected = in_array($status, ['OK', 'CREATION_SUCCESS', 'RECONNECTED', 'SYNC_SUCCESS', 'CONNECTING'], true);
        $metadataJson = json_encode($metadata, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if ($metadataJson === false) {
            $metadataJson = '{}';
        }

        $existing = $this->findByAccountId($accountId);
        if ($existing !== null) {
            $stmt = $this->pdo->prepare(
                "UPDATE integration
                SET company_id = :company_id,
                    user_id = :user_id,
                    operator_id = :operator_id,
                    integration_external_account_id = :external_account_id,
                    integration_account_name = :account_name,
                    integration_status = :status,
                    integration_is_connected = :is_connected,
                    integration_last_sync_at = NOW(),
                    integration_last_error = '',
                    integration_metadata = CAST(:metadata AS jsonb),
                    integration_updated_at = NOW()
                WHERE integration_id = :integration_id
                RETURNING *"
            );
            $stmt->execute([
                'company_id' => $companyId,
                'user_id' => $userId,
                'operator_id' => $operatorId,
                'external_account_id' => $externalAccountId,
                'account_name' => $accountName,
                'status' => $status,
                'is_connected' => $isConnected,
                'metadata' => $metadataJson,
                'integration_id' => (int) $existing['integration_id'],
            ]);

            return $stmt->fetch(\PDO::FETCH_ASSOC) ?: $existing;
        }

        $stmt = $this->pdo->prepare(
            "INSERT INTO integration (
                company_id, user_id, operator_id, integration_provider, integration_account_id,
                integration_external_account_id, integration_account_name, integration_status,
                integration_access_token, integration_refresh_token, integration_is_connected,
                integration_last_sync_at, integration_last_error, integration_metadata
            ) VALUES (
                :company_id, :user_id, :operator_id, 'whatsapp', :account_id,
                :external_account_id, :account_name, :status,
                '', '', :is_connected,
                NOW(), '', CAST(:metadata AS jsonb)
            ) RETURNING *"
        );
        $stmt->execute([
            'company_id' => $companyId,
            'user_id' => $userId,
            'operator_id' => $operatorId,
            'account_id' => $accountId,
            'external_account_id' => $externalAccountId,
            'account_name' => $accountName,
            'status' => $status,
            'is_connected' => $isConnected,
            'metadata' => $metadataJson,
        ]);

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

    public function updateStatusByAccountId(string $accountId, string $status, array $metadata = []): ?array
    {
        $isConnected = in_array($status, ['OK', 'CREATION_SUCCESS', 'RECONNECTED', 'SYNC_SUCCESS', 'CONNECTING'], true);
        $metadataJson = json_encode($metadata, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if ($metadataJson === false) {
            $metadataJson = '{}';
        }

        $stmt = $this->pdo->prepare(
            "UPDATE integration
            SET integration_status = :status,
                integration_is_connected = :is_connected,
                integration_last_sync_at = NOW(),
                integration_last_error = CASE WHEN :is_connected THEN '' ELSE integration_last_error END,
                integration_metadata = integration_metadata || CAST(:metadata AS jsonb),
                integration_updated_at = NOW()
            WHERE integration_account_id = :account_id
              AND integration_deleted_at = 'infinity'
            RETURNING *"
        );
        $stmt->execute([
            'status' => $status,
            'is_connected' => $isConnected,
            'metadata' => $metadataJson,
            'account_id' => $accountId,
        ]);
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

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

    public function formatIntegration(array $row): array
    {
        return [
            'id' => (int) ($row['integration_id'] ?? 0),
            'companyId' => (int) ($row['company_id'] ?? 0),
            'userId' => (int) ($row['user_id'] ?? 0),
            'operatorId' => (int) ($row['operator_id'] ?? 0),
            'provider' => $row['integration_provider'] ?? '',
            'accountId' => $row['integration_account_id'] ?? '',
            'externalAccountId' => $row['integration_external_account_id'] ?? '',
            'accountName' => $row['integration_account_name'] ?? '',
            'status' => $row['integration_status'] ?? '',
            'isConnected' => (bool) ($row['integration_is_connected'] ?? false),
            'lastSyncAt' => $row['integration_last_sync_at'] ?? '',
            'lastError' => $row['integration_last_error'] ?? '',
            'createdAt' => $row['integration_created_at'] ?? '',
            'updatedAt' => $row['integration_updated_at'] ?? '',
        ];
    }

    private function extractExternalAccountId(array $metadata): string
    {
        $accountInfo = is_array($metadata['account_info'] ?? null) ? $metadata['account_info'] : [];

        return (string) ($accountInfo['user_id'] ?? $metadata['external_account_id'] ?? '');
    }

    private function extractAccountName(array $metadata, string $accountId): string
    {
        $accountInfo = is_array($metadata['account_info'] ?? null) ? $metadata['account_info'] : [];
        $name = trim((string) ($accountInfo['name'] ?? $metadata['account_name'] ?? $metadata['name'] ?? ''));

        return $name !== '' ? mb_substr($name, 0, 100) : mb_substr($accountId, 0, 100);
    }
}