esun3d/app/code/Esun/Reward/Model/BalanceManager.php

<?php
declare(strict_types=1);

namespace Esun\Reward\Model;

use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Exception\LocalizedException;
use Magento\Framework\Serialize\Serializer\Json;

class BalanceManager
{
    public function __construct(
        private readonly ResourceConnection $resource,
        private readonly Json $json,
        private readonly TierResolver $tierResolver
    ) {}

    public function getAccount(int $customerId, int $websiteId): array
    {
        $connection = $this->resource->getConnection();
        $table = $this->resource->getTableName('esun_reward_account');
        $rows = $connection->fetchAll(
            $connection->select()->from($table)->where('customer_id = ?', $customerId)->order('account_id ASC')
        );
        if (!$rows) {
            return ['customer_id' => $customerId, 'website_id' => $websiteId, 'balance' => 0, 'pending_balance' => 0, 'lifetime_earned' => 0, 'lifetime_spend_base' => 0, 'tier_code' => 'bronze'];
        }

        $account = $rows[0];
        $account['website_id'] = $websiteId;
        $account['balance'] = array_sum(array_column($rows, 'balance'));
        $account['pending_balance'] = array_sum(array_column($rows, 'pending_balance'));
        $account['lifetime_earned'] = array_sum(array_column($rows, 'lifetime_earned'));
        $account['lifetime_spend_base'] = array_sum(array_column($rows, 'lifetime_spend_base'));
        return $account;
    }

    public function change(int $customerId, int $websiteId, int $points, string $type, string $uniqueKey, array $context = []): bool
    {
        if ($customerId <= 0 || $points === 0 || $uniqueKey === '') { return false; }
        $connection = $this->resource->getConnection();
        $accountTable = $this->resource->getTableName('esun_reward_account');
        $transactionTable = $this->resource->getTableName('esun_reward_transaction');
        $connection->beginTransaction();
        try {
            if ($connection->fetchOne($connection->select()->from($transactionTable, ['transaction_id'])->where('unique_key = ?', $uniqueKey))) {
                $connection->rollBack(); return false;
            }
            $connection->insertOnDuplicate($accountTable, ['customer_id' => $customerId, 'website_id' => $websiteId], []);
            $accounts = $connection->fetchAll(
                $connection->select()->from($accountTable)->where('customer_id = ?', $customerId)->order('account_id ASC')->forUpdate(true)
            );
            $account = $accounts[0];
            $balance = (int)array_sum(array_column($accounts, 'balance'));
            if ($balance + $points < 0 && empty($context['allow_negative'])) { throw new LocalizedException(__('The customer does not have enough reward points.')); }
            $newBalance = $balance + $points;
            $connection->update($accountTable, [
                'balance' => $newBalance,
                'pending_balance' => (int)array_sum(array_column($accounts, 'pending_balance')),
                'lifetime_earned' => (int)array_sum(array_column($accounts, 'lifetime_earned')) + max(0, $points),
                'lifetime_spend_base' => (float)array_sum(array_column($accounts, 'lifetime_spend_base')),
            ], ['account_id = ?' => (int)$account['account_id']]);
            if (count($accounts) > 1) {
                $connection->update($accountTable, [
                    'balance' => 0, 'pending_balance' => 0, 'lifetime_earned' => 0, 'lifetime_spend_base' => 0,
                ], ['customer_id = ?' => $customerId, 'account_id <> ?' => (int)$account['account_id']]);
            }
            $connection->insert($transactionTable, [
                'customer_id' => $customerId, 'website_id' => $websiteId, 'points_delta' => $points,
                'balance_after' => $newBalance, 'type' => $type, 'status' => $context['status'] ?? 'available',
                'reference_type' => $context['reference_type'] ?? null, 'reference_id' => isset($context['reference_id']) ? (string)$context['reference_id'] : null,
                'unique_key' => $uniqueKey, 'reason' => $context['reason'] ?? null,
                'admin_user_id' => $context['admin_user_id'] ?? null,
                'metadata' => empty($context['metadata']) ? null : $this->json->serialize($context['metadata']),
            ]);
            $connection->commit(); return true;
        } catch (\Throwable $e) {
            if ($connection->getTransactionLevel() > 0) { $connection->rollBack(); }
            throw $e;
        }
    }

    public function addLifetimeSpend(int $customerId, int $websiteId, float $amount, ?int $storeId = null): void
    {
        $connection = $this->resource->getConnection(); $table = $this->resource->getTableName('esun_reward_account');
        $connection->beginTransaction();
        try {
            $connection->insertOnDuplicate($table, ['customer_id' => $customerId, 'website_id' => $websiteId], []);
            $accounts = $connection->fetchAll(
                $connection->select()->from($table)->where('customer_id = ?', $customerId)->order('account_id ASC')->forUpdate(true)
            );
            $account = $accounts[0];
            $spend = max(0.0, (float)array_sum(array_column($accounts, 'lifetime_spend_base')) + $amount);
            $tier = $this->tierResolver->resolveEffective(
                (string)($account['tier_code'] ?? ''),
                $spend,
                $storeId
            );
            $connection->update($table, [
                'balance' => (int)array_sum(array_column($accounts, 'balance')),
                'pending_balance' => (int)array_sum(array_column($accounts, 'pending_balance')),
                'lifetime_earned' => (int)array_sum(array_column($accounts, 'lifetime_earned')),
                'lifetime_spend_base' => $spend,
                'tier_code' => $tier['code'],
            ], ['account_id = ?' => (int)$account['account_id']]);
            if (count($accounts) > 1) {
                $connection->update($table, [
                    'balance' => 0, 'pending_balance' => 0, 'lifetime_earned' => 0, 'lifetime_spend_base' => 0,
                ], ['customer_id = ?' => $customerId, 'account_id <> ?' => (int)$account['account_id']]);
            }
            $connection->commit();
        } catch (\Throwable $e) { if ($connection->getTransactionLevel() > 0) { $connection->rollBack(); } throw $e; }
    }

    public function importLegacySnapshot(
        int $customerId,
        int $websiteId,
        int $targetBalance,
        string $targetTierCode,
        string $batchId,
        array $metadata = []
    ): bool {
        if ($customerId <= 0 || $websiteId <= 0 || $targetBalance < 0
            || !in_array($targetTierCode, ['bronze', 'silver', 'gold'], true) || trim($batchId) === '') {
            throw new LocalizedException(__('Invalid reward migration snapshot.'));
        }
        $connection = $this->resource->getConnection();
        $accountTable = $this->resource->getTableName('esun_reward_account');
        $transactionTable = $this->resource->getTableName('esun_reward_transaction');
        $uniqueKey = sprintf('migration:%s:%d:%d', hash('sha256', trim($batchId)), $customerId, $websiteId);
        $connection->beginTransaction();
        try {
            if ($connection->fetchOne($connection->select()->from($transactionTable, ['transaction_id'])->where('unique_key = ?', $uniqueKey))) {
                $connection->rollBack();
                return false;
            }
            $connection->insertOnDuplicate($accountTable, ['customer_id' => $customerId, 'website_id' => $websiteId], []);
            $account = $connection->fetchRow($connection->select()->from($accountTable)
                ->where('customer_id = ?', $customerId)->where('website_id = ?', $websiteId)->forUpdate(true));
            $currentBalance = (int)($account['balance'] ?? 0);
            $delta = $targetBalance - $currentBalance;
            $connection->update($accountTable, [
                'balance' => $targetBalance,
                'lifetime_earned' => max((int)($account['lifetime_earned'] ?? 0), $targetBalance),
                'tier_code' => $targetTierCode,
            ], ['account_id = ?' => (int)$account['account_id']]);
            $connection->insert($transactionTable, [
                'customer_id' => $customerId, 'website_id' => $websiteId, 'points_delta' => $delta,
                'balance_after' => $targetBalance, 'type' => 'migration', 'status' => 'available',
                'reference_type' => 'migration', 'reference_id' => substr($batchId, 0, 64),
                'unique_key' => $uniqueKey, 'reason' => 'Legacy reward migration',
                'metadata' => $this->json->serialize($metadata + [
                    'tier_code' => $targetTierCode,
                ]),
            ]);
            $connection->commit();
            return true;
        } catch (\Throwable $e) {
            if ($connection->getTransactionLevel() > 0) { $connection->rollBack(); }
            throw $e;
        }
    }

    public function getTransactions(int $customerId, int $limit = 50): array
    {
        $connection = $this->resource->getConnection();
        $rows = $connection->fetchAll(
            $connection->select()->from($this->resource->getTableName('esun_reward_transaction'))
                ->where('customer_id = ?', $customerId)->order('transaction_id ASC')
        );
        $balance = 0;
        foreach ($rows as &$row) {
            $balance += (int)$row['points_delta'];
            $row['balance_after'] = $balance;
        }
        unset($row);
        return array_slice(array_reverse($rows), 0, max(1, min(200, $limit)));
    }
}