<?php

namespace App\Traits;

use App\Models\Transaction;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

trait AccountInfoTrait
{
    use ProfilePermissionTrait;
    /**
     * Get the balance of an account.
     *
     * @param int $accountId The ID of the account.
     * @return float The balance of the account in minutes.
     */
    public function getBalance($accountId)
    {
        // TODO: Store balance in extra column of transactions table with model events for create/update/delete
        // a new getBalanceFast() method could read this column for fast access of balance.
        // This new method would then be used for non-critical balance info.
        $cacheKey = "account_balance_{$accountId}";
        return Cache::remember($cacheKey, 60, function () use ($accountId) {
            $balance = Transaction::where('from_account_id', $accountId)
                ->orWhere('to_account_id', $accountId)
                // selectRaw query, secured with input sanitization ans parameter binding [$accountId}]
                ->selectRaw('SUM(CASE WHEN to_account_id = ? THEN amount ELSE -amount END) as balance', [$accountId])
                ->value('balance');

            return $balance ?? 0;
        });
    }


    /**
     * Get accounts associated with a profile.
     * If no profileType and profileId is specified, the active profile is used.
     * Returns an array with account id, name, and balance (in minutes).
     *
     * @return void
     */
    public function getAccountsInfo($profileType = null, $profileId = null)
    {
        if ($profileType === null) {
            $profileType = session('activeProfileType');
        }
        if ($profileId === null) {
            $profileId = session('activeProfileId');
        }

        $cacheKey = "accounts_info_{$profileType}_{$profileId}";
        return Cache::remember($cacheKey, 60, function () use ($profileType, $profileId) {
            // Get the profile
            $profile = $profileType::find($profileId);

            if (!$profile) {
                return collect();
            }

            // Check if profile has accounts relation (e.g., Admin profiles don't)
            if (!method_exists($profile, 'accounts')) {
                return collect();
            }

            // Get the profile and its accounts in a single query
            $profile = $profileType::with(['accounts'])->find($profileId);

            if (!$profile) {
                return collect();
            }

            // Return early if no accounts exist
            if ($profile->accounts->isEmpty()) {
                return collect();
            }

            // Calculate the total balance of all accounts of the profile in a single query
            $accountIds = $profile->accounts->pluck('id')->toArray();
            // Convert array to comma-separated string for selectRaw sanitization and parameter binding
            $accountIdsString = implode(',', $accountIds);
            $sumAccounts = DB::table('transactions')
                ->whereIn('from_account_id', $accountIds)
                ->orWhereIn('to_account_id', $accountIds)
                ->selectRaw("SUM(CASE WHEN to_account_id IN ($accountIdsString) THEN amount ELSE -amount END) as balance")
                ->value('balance');

            $limitReceivable = $profile->limit_max - ($sumAccounts ?? 0) - $profile->limit_min;
            // If the limitReceivable is negative, set it to 0
            if ($limitReceivable < 0) {
                $limitReceivable = 0;
            }

            // Map the collection to include the total balance
            $accounts = $profile->accounts->map(function ($account) use ($profileType, $limitReceivable) {
                return [
                    'id' => $account->id,
                    'name' => __('messages.' . $account->name . '_account'),
                    'type' => strtolower(class_basename($profileType)),
                    'balance' => $this->getBalance($account->id), // Use getBalance function
                    'balanceTbFormat' => tbFormat($this->getBalance($account->id)),
                    'limitMin' => $account->limit_min,
                    'limitMax' => $account->limit_max,
                    'limitReceivable' => $limitReceivable,
                    'inactive' => $account->inactive_at ? \Illuminate\Support\Carbon::parse($account->inactive_at)->isPast() : false,
                    'inactiveAt' => $account->inactive_at,
                    'removed' => $account->deleted_at ? \Illuminate\Support\Carbon::parse($account->deleted_at)->isPast() : false,
                    'deletedAt' => $account->deleted_at,
                ];
            });

            return $accounts;
        });
    }


    /**
     * Retrieves the account totals of a profile.
     * If no profileType and profileId is specified, the active profile is used.
     *
     * @param string|null $profileType The profile type. If null, the active profile type from the session will be used.
     * @param int|null $profileId The profile ID. If null, the active profile ID from the session will be used.
     * @param int|null $sinceDaysAgo The number of days to filter the counted transfers. If null, all transfers will be counted.
     * @return array Sum of all balances (in minutes), count of transfers, count of transfers received, count of transfers sent.
     */
    public function getAccountsTotals($profileType = null, $profileId = null, $sinceDaysAgo = null)
    {
        if ($profileType === null) {
            $profileType = session('activeProfileType');
        }
        if ($profileId === null) {
            $profileId = session('activeProfileId');
        }

        // Get the profile instance
        $profile = $profileType::find($profileId);
        $type = strtolower(class_basename($profile));

        // Robust check: profile exists and has accounts relation/method
        if (
            !$profile ||
            !(method_exists($profile, 'accounts') || property_exists($profile, 'accounts'))
        ) {
            return [
                'sumBalances' => 0,
                'countTransfersSince' => $sinceDaysAgo !== null ? now()->subDays($sinceDaysAgo) : null,
                'transfers' => 0,
                'transfersReceived' => 0,
                'transfersGiven' => 0,
                'lastTransferDate' => null,
            ];
        }

        // Get all accounts for the profile
        $accounts = $profile->accounts ?? collect();
        if ($accounts->isEmpty()) {
            return [
                'sumBalances' => 0,
                'countTransfersSince' => $sinceDaysAgo !== null ? now()->subDays($sinceDaysAgo) : null,
                'transfers' => 0,
                'transfersReceived' => 0,
                'transfersGiven' => 0,
                'lastTransferDate' => null,
            ];
        }
        $accountIds = $accounts->pluck('id')->toArray();

        // 1. Calculate sum of balances by iterating through the profile's accounts
        $sumBalances = 0;
        foreach ($accounts as $account) {
            $sumBalances += $this->getBalance($account->id);
        }

        // 2. Get all relevant transactions in a single query
        $transfersQuery = Transaction::where(function ($query) use ($accountIds) {
            $query->whereIn('from_account_id', $accountIds)
                ->orWhereIn('to_account_id', $accountIds);
        });

        if ($sinceDaysAgo !== null) {
            $transfersQuery->whereDate('created_at', '>=', now()->subDays($sinceDaysAgo));
        }

        // Get the date of the most recent transfer before fetching the full collection
        $lastTransferDate = (clone $transfersQuery)->latest('created_at')->value('created_at');

        $transfers = $transfersQuery->get();

        // 3. Initialize counters
        $countTransfers = 0;
        $countTransfersReceived = 0;
        $countTransfersGiven = 0;

        // 4. Process all transactions in a single loop
        foreach ($transfers as $transfer) {
            // Check if the transaction is internal (both from and to accounts belong to the profile)
            $isInternal = in_array($transfer->from_account_id, $accountIds) && in_array($transfer->to_account_id, $accountIds);

            // Only count non-internal transfers
            if (!$isInternal) {
                $countTransfers++;
                if (in_array($transfer->to_account_id, $accountIds)) {
                    $countTransfersReceived++;
                }
                if (in_array($transfer->from_account_id, $accountIds)) {
                    $countTransfersGiven++;
                }
            }
        }

        // 5. Assemble the result with privacy settings from config
        // Check if viewing own profile
        $activeProfileType = session('activeProfileType');
        $activeProfileId = session('activeProfileId');
        $isViewingOwnProfile = ($activeProfileType === $profileType && $activeProfileId === $profileId);

        if ($this->getCanManageAccounts() || $isViewingOwnProfile) {
            // Show all account info if user can manage accounts OR is viewing their own profile
            $totals = [
                'sumBalances' => $sumBalances,
                'countTransfersSince' => $sinceDaysAgo !== null ? now()->subDays($sinceDaysAgo) : null,
                'transfers' => $countTransfers,
                'transfersReceived' => $countTransfersReceived,
                'transfersGiven' => $countTransfersGiven,
                'transfersReceivedOrGiven' => $countTransfersReceived + $countTransfersGiven,
                'lastTransferDate' => $lastTransferDate,
            ];
        } else {
            // Apply privacy settings from config for other viewers
            $totals = [
                'sumBalances' => timebank_config('account_info.'.$type.'.sumBalances_public', false) === true ? $sumBalances : null,
                'countTransfersSince' => $sinceDaysAgo !== null ? now()->subDays($sinceDaysAgo) : null,
                'transfers' => timebank_config('account_info.' . $type . '.countTransfers_public', false) === true ? $countTransfers : null,
                'transfersReceived' => timebank_config('account_info.' . $type . '.countTransfersReceived_public', false) === true ? $countTransfersReceived : null,
                'transfersGiven' => timebank_config('account_info.' . $type . '.countTransfersGiven_public', false) === true ? $countTransfersGiven : null,
                'transfersReceivedOrGiven' => timebank_config('account_info.' . $type . '.countTransfersReceivedOrGiven_public', false) === true ? $countTransfersReceived + $countTransfersGiven : null,
                'lastTransferDate' => timebank_config('account_info.' . $type . '.lastTransferDate_public', false) === true ? $lastTransferDate : null,
            ];
        }

        return $totals;
    }
}