use App\Models\User;
use App\Models\Organization;
use App\Models\Transaction;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

// Get Lekkernassuh organization
$lekkernassuh = Organization::where('name', 'Lekkernassuh')->first();

if (!$lekkernassuh) {
    echo "Lekkernassuh organization not found\n";
    return;
}

$lekkernassuhAccountId = $lekkernassuh->accounts()->first()?->id;

if (!$lekkernassuhAccountId) {
    echo "Lekkernassuh organization has no accounts\n";
    return;
}

// Get transactions involving Lekkernassuh
$transactionAccountIds = Transaction::where('from_account_id', $lekkernassuhAccountId)
    ->orWhere('to_account_id', $lekkernassuhAccountId)
    ->get();

// Extract all account IDs from transactions
$accountIds = [];
foreach ($transactionAccountIds as $transaction) {
    if ($transaction->from_account_id !== $lekkernassuhAccountId) {
        $accountIds[] = $transaction->from_account_id;
    }
    if ($transaction->to_account_id !== $lekkernassuhAccountId) {
        $accountIds[] = $transaction->to_account_id;
    }
}

$accountIds = array_unique($accountIds);

// Get user accounts from those IDs
$userAccounts = DB::table('accounts')
    ->whereIn('id', $accountIds)
    ->where('accountable_type', 'App\Models\User')
    ->select('accountable_id')
    ->distinct()
    ->get();

$userIds = $userAccounts->pluck('accountable_id')->unique();

// Get users who logged in this year
$usersWithTransactions = User::whereIn('id', $userIds)
    ->where('last_login_at', '>=', Carbon::now()->startOfYear())
    ->with('accounts')
    ->get();

// Filter by users with balance and sort by total balance
$usersWithBalance = [];

foreach ($usersWithTransactions as $user) {
    $totalBalance = 0;

    foreach ($user->accounts as $account) {
        $balance = $account->balance;
        if ($balance > 0) {
            $totalBalance += $balance;
        }
    }

    if ($totalBalance > 0) {
        $lastLogin = null;
        if ($user->last_login_at) {
            if (is_string($user->last_login_at)) {
                $lastLogin = Carbon::parse($user->last_login_at)->format('Y-m-d H:i');
            } else {
                $lastLogin = $user->last_login_at->format('Y-m-d H:i');
            }
        }

        $usersWithBalance[] = [
            'user_id' => $user->id,
            'name' => $user->name,
            'email' => $user->email,
            'last_login' => $lastLogin,
            'total_balance' => $totalBalance,
            'total_balance_formatted' => tbFormat($totalBalance),
        ];
    }
}

// Sort by total balance descending
usort($usersWithBalance, function ($a, $b) {
    return $b['total_balance'] <=> $a['total_balance'];
});

// Display results
echo "\n=== Users with Lekkernassuh Transactions ===\n";
echo sprintf("Total users found: %d\n\n", count($usersWithBalance));

echo sprintf("%-5s | %-25s | %-30s | %-20s | %-15s\n", 'ID', 'Name', 'Email', 'Last Login', 'Balance');
echo str_repeat("-", 105) . "\n";

foreach ($usersWithBalance as $user) {
    $lastLogin = $user['last_login'] ?? 'N/A';
    echo sprintf(
        "%-5s | %-25s | %-30s | %-20s | %s\n",
        $user['user_id'],
        substr($user['name'], 0, 25),
        substr($user['email'], 0, 30),
        is_string($lastLogin) ? $lastLogin : 'N/A',
        $user['total_balance_formatted']
    );
}

echo "\n" . str_repeat("=", 105) . "\n";
echo sprintf("Total balance across all users: %s\n", tbFormat(
    array_sum(array_column($usersWithBalance, 'total_balance'))
));