<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\ladgerBDV;
use App\Models\payoutBD;
use App\Models\payoutIN;
use App\Models\p2cBD;
use App\Models\p2cIN;
use App\Models\p2pBD;
use App\Models\settleList;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Exception;

class BalanceV extends Controller
{
    private static function error(Exception $e)
    {
        if (env('APP_ENV') === 'production') {
            return "Internal error in production";
        } else {
            return $e->getMessage();
        }
    }

    public static function getBalance($accountID)
{
    try {

        $ladgerBD = ladgerBDV::where('accountID', $accountID)
            ->where('status', 'SUCCESS')
            ->selectRaw("
                SUM(CASE WHEN type = 'CR' THEN amount ELSE 0 END) as ladgerBDSumCR,
                SUM(CASE WHEN type = 'DR' THEN amount ELSE 0 END) as ladgerBDSumDR
            ")
            ->first();

/*
        $payoutDBSum = payoutBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');

        $p2cDBSum = p2cBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
*/

        $payoutDBSum = DB::table('payoutBD')
            ->fromRaw('payoutBD FORCE INDEX (idx_vendor_status_amount)')
            ->where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
            
        $pendingSum = DB::table('p2cBD')
            ->fromRaw('p2cBD FORCE INDEX (idx_vendor_status_amount)')
            ->where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS'])
            ->sum('amount');
        
        $completedSum = DB::table('p2cBD')
            ->fromRaw('p2cBD FORCE INDEX (idx_vendor_status_rcv_amount)')
            ->where('vendor_acid', $accountID)
            ->where('status', 'COMPLETED')
            ->sum('rcv_amount');
        
        $p2cDBSum = (float) $pendingSum + (float) $completedSum;

            
        $p2pDBSum = p2pBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');

        $settleDBSum = settleList::where('accountID', $accountID)
            ->whereIn('status', ['PENDING', 'COMPLETED'])
            ->where('currency', 'BDT')
            ->sum('amount');
/*
        $cashoutDBSum = DB::table('vendorCashout')
            ->where('accountID', $accountID)
            ->where('status', 'APPROVED')
            ->sum('amount');
            */
        $cashoutDBSum = DB::table('vendorCashout')
            ->fromRaw('vendorCashout FORCE INDEX (idx_account_status_amount)')
            ->where('accountID', $accountID)
            ->where('status', 'APPROVED')
            ->sum('amount');
            
        $supportDBSum = DB::table('ticketList')
            ->where('accountID', $accountID)
            ->where('status', 'PENDING')
            ->sum('amount');
            
        $balance = round(($payoutDBSum + ($ladgerBD->ladgerBDSumCR ?? 0) + $cashoutDBSum) - (($ladgerBD->ladgerBDSumDR ?? 0) + $p2cDBSum + $p2pDBSum + $settleDBSum + $supportDBSum),2);
        DB::table('ladgerVBL')->where('accountID', $accountID)->update(['balance' => $balance,'updated_at' => now()]);

        return $balance;
    } catch (\Exception $e) {
        return 0;
    }
}


public static function getBalanceINR($accountID)
{
    try {

        $ladgerBD = ladgerBDV::where('accountID', $accountID)
            ->where('status', 'SUCCESS')
            ->selectRaw("
                SUM(CASE WHEN type = 'CR' THEN amount ELSE 0 END) as ladgerBDSumCR,
                SUM(CASE WHEN type = 'DR' THEN amount ELSE 0 END) as ladgerBDSumDR
            ")
            ->first();

/*
        $payoutDBSum = payoutBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');

        $p2cDBSum = p2cBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
*/

        $payoutDBSum = DB::table('payoutIN')
            ->fromRaw('payoutIN FORCE INDEX (idx_vendor_status_amount)')
            ->where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
            
        $p2cDBSum = DB::table('p2cIN')
            ->fromRaw('p2cIN FORCE INDEX (idx_vendor_status_amount)')
            ->where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
        
        $settleDBSum = settleList::where('accountID', $accountID)
            ->whereIn('status', ['PENDING', 'COMPLETED'])
            ->where('currency', 'INR')
            ->sum('amount');

        $cashoutDBSum = DB::table('vendorCashout')
            ->fromRaw('vendorCashout FORCE INDEX (idx_account_status_amount)')
            ->where('accountID', $accountID)
            ->where('status', 'APPROVED')
            ->sum('amount');
            
        $supportDBSum = DB::table('ticketList')
            ->where('accountID', $accountID)
            ->where('status', 'PENDING')
            ->sum('amount');
            
        $balance = round(($payoutDBSum + ($ladgerBD->ladgerBDSumCR ?? 0) + $cashoutDBSum) - (($ladgerBD->ladgerBDSumDR ?? 0) + $p2cDBSum  + $settleDBSum + $supportDBSum),2);
        DB::table('ladgerVBL')->where('accountID', $accountID)->update(['balance' => $balance,'updated_at' => now()]);

        return $balance;
    } catch (\Exception $e) {
        return 0;
    }
}


public static function getPending($accountID)
{
    try {
        return (double) payoutBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS'])
            ->sum('amount') ?? 0;
    } catch (\Exception $e) {
        return 0;
    }
}

public static function getPendingToday($accountID)
{
    try {
        return (double) payoutBD::where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS'])
            ->whereRaw("DATE(created_at) = CURDATE()")
            ->sum('amount') ?? 0;
    } catch (\Exception $e) {
        return 0;
    }
}

public static function getCompletedToday($accountID)
{
    try {
        return (double) payoutBD::where('vendor_acid', $accountID)
            ->where('status', 'COMPLETED')
            ->whereRaw("DATE(created_at) = CURDATE()")
            ->sum('amount') ?? 0;
    } catch (\Exception $e) {
        return 0;
    }
}

public static function getTotalPending($accountID)
{
    try {
        $result = DB::table('payoutBD')
            ->where('vendor_acid', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS'])
            ->selectRaw("
                SUM(amount) as payoutBDSum,
                (SELECT SUM(amount) FROM p2cBD WHERE vendor_acid = ? AND status IN ('PENDING', 'INPROGRESS')) as p2cDBSum,
                (SELECT SUM(amount) FROM p2pBD WHERE vendor_acid = ? AND status IN ('PENDING', 'INPROGRESS')) as p2pDBSum,
                (SELECT SUM(amount) FROM settleList WHERE accountID = ? AND status = 'PENDING' AND currency = 'BDT') as settleDBSum
            ", [$accountID, $accountID, $accountID])
            ->first();

        return (double) (($result->payoutBDSum ?? 0) + ($result->p2cDBSum ?? 0) + ($result->p2pDBSum ?? 0) + ($result->settleDBSum ?? 0));
    } catch (\Exception $e) {
        return 0;
    }
}


public static function commissionBalance($accountID)
{
    try {

     $vendorFees = DB::table('p2cBD')
            ->fromRaw('p2cBD FORCE INDEX (idx_vendor_status_fees)')
            ->where('vendor_acid', $accountID)
            ->where('status', 'COMPLETED')
            ->sum('vendor_fees');
            
$commissionAmount = DB::table('commissionVendor')
            ->fromRaw('commissionVendor FORCE INDEX (idx_account_status_amount)')
            ->where('accountID', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
            
    $balance = $vendorFees - $commissionAmount;

    // Return the calculated balance
    return $balance;
} catch (Exception $e) {
            return 0;
        }
}
public static function commissionBalanceINR($accountID)
{
    try {

     $vendorFees = DB::table('p2cINR')
            ->fromRaw('p2cINR FORCE INDEX (idx_vendor_status_fees)')
            ->where('vendor_acid', $accountID)
            ->where('status', 'COMPLETED')
            ->sum('vendor_fees');
            
$commissionAmount = DB::table('commissionVendor')
            ->fromRaw('commissionVendor FORCE INDEX (idx_account_status_amount)')
            ->where('accountID', $accountID)
            ->whereIn('status', ['PENDING', 'INPROGRESS', 'COMPLETED'])
            ->sum('amount');
            
    $balance = $vendorFees - $commissionAmount;

    // Return the calculated balance
    return $balance;
} catch (Exception $e) {
            return 0;
        }
}
}