<?php

namespace Yajny\Member\BalanceTransactions;

use Illuminate\Support\Facades\DB;

class BalanceChecker
{
    public static function checkUsersBalance()
    {
        return DB::select(self::getBalanceCheckQuery());
    }

    /**
     * @return string
     */
    private static function getBalanceCheckQuery()
    {
        return "
        SELECT 
            p.user_id,
            u.first_name,
            u.last_name,
            u.email,
            u.is_merchant,
            SUM(pamount) AS pendingAmount,
            SUM(camount) AS confirmedAmount,
            u.pending_balance AS pendingBalance,
            u.confirmed_balance AS confirmedBalance
        FROM
            (
                (
                    SELECT user_id, SUM(IF(status = 'confirmed', amount, 0)) AS camount, SUM(IF(status = 'pending', amount, 0)) AS pamount
                    FROM user_bonus WHERE status IN ('pending' , 'confirmed') 
                    GROUP BY user_id
                ) UNION ALL (
                    SELECT user_id, SUM(IF(status = 'confirmed', cashback, 0)) AS camount, SUM(IF(status = 'pending', cashback, 0)) AS pamount 
                    FROM user_sales WHERE status IN ('pending' , 'confirmed')
                    GROUP BY user_id
                ) UNION ALL (
                    SELECT user_id, SUM(- 1 * amount) AS camount, 0 AS pamount 
                    FROM user_payouts WHERE status NOT IN ('declined' , 'failed')
                    GROUP BY user_id
                )
            ) p
        LEFT JOIN users u ON u.user_id = p.user_id
        GROUP BY user_id
        HAVING confirmedBalance != confirmedAmount OR pendingBalance != pendingAmount;
        ";
    }
}