<?php

namespace Modules\Account\Controllers;


use App\Controllers\BaseController;
use App\Libraries\UserCheck;
use Modules\Account\Models\AccountModel;
use Modules\Account\Models\PayagentModel;
use Modules\User\Models\UserDetailModel;
use Modules\Agent\Models\AgentModel;
use Modules\Paymethod\Models\PaymethodModel;
use Modules\Agent\Models\AgenttotalModel;
use Modules\Company\Models\CompanyModel;

use App\Libraries\Rolepermission;

class Payagent extends BaseController
{
    protected $Viewpath;
	protected $accountModel;
	protected $userdetailModel;
	protected $agentModel;
	protected $db;
	protected $paymethod;
    protected $agentTotal;
    protected $agentPayment;
    protected $companyModel;
	
	public function __construct()
    {

        $this->Viewpath = "Modules\Account\Views";
		$this->accountModel = new AccountModel();
		$this->userdetailModel = new UserDetailModel();
		$this->agentModel = new AgentModel();
		$this->paymethod = new PaymethodModel();
		$this->db = \Config\Database::connect();

        $this->agentTotal = new AgenttotalModel();
        $this->agentPayment = new PayagentModel();
        $this->companyModel = new CompanyModel();
      
    }

    function new ()
	 {
        $userchek = new UserCheck();
		$userType = $userchek->getUserType();
        if ($userType == 2) {
			
            $currentUserId =  $this->session->get('user_id');
		    $agentDetail = 	$this->agentModel->where('user_id',$currentUserId)->first();
		  //  print_r($agentDetail);die;
            $agentid = $agentDetail->id;
            $allTranSaction = $this->agentTotal->where('agent_id',$agentid)->findAll();
            // print_r('allTranSaction',$allTranSaction);die;
            $givenMoney = $this->agentPayment->where('agent_id',$agentid)->findAll();
            // print_r($givenMoney);
            $givenTotalMoney = array_sum(array_column($givenMoney, 'amount'));
            // print_r($givenTotalMoney);
            $agentIncomeTotal = array_sum(array_column($allTranSaction, 'income'));
            // print_r($agentIncomeTotal);
            $agentExpenseTotal = array_sum(array_column($allTranSaction, 'expense'));
            // print_r($agentExpenseTotal);die;
            $keepMoney = $agentIncomeTotal - $agentExpenseTotal;
            // print_r($agentExpenseTotal);die;
            $keepMoney = $keepMoney - $givenTotalMoney;
            if (0 == 0) {

                $data['keepMoney'] = $agentDetail->wallet;
                $data['pageheading'] = lang("Localize.agent_payment");

                $data['companies'] = $this->companyModel->where("FIND_IN_SET($currentUserId, user_id) >", 0)->where('status', 1)->findAll();

                $data['module'] =    lang("Localize.account") ; 
                $data['title']  =    lang("Localize.agent_payment") ;
                echo view($this->Viewpath . '\payagent/new',$data);
            }
            else{

                return redirect()->back()->with("fail","Dont Have Money to Submit Request");
            }
           
		}

        else{
            return redirect()->back()->with("fail","Dont Have Permission");
        }

		
        


    }

    public function index()
    {
        $role_id = (int) $this->session->get('role_id');
        if (! in_array($role_id, [1, 2, 7], true)) {
            return redirect()->back()->with('fail', 'Dont Have Permission');
        }

        $data['userType'] = $role_id;
        $data['start_date'] = $this->request->getGet('start_date') ?: date('Y-m-01');
        $data['end_date'] = $this->request->getGet('end_date') ?: date('Y-m-d');
        $data['pageheading'] = lang('Localize.transaction_list');
        $data['module'] = lang('Localize.account');
        $data['title'] = lang('Localize.agent_payment');

        $rolepermissionLibrary = new Rolepermission();
        $data['add_data'] = $rolepermissionLibrary->create('agent_payment');
        $data['edit_data'] = $rolepermissionLibrary->edit('agent_payment');
        $data['delete_data'] = $rolepermissionLibrary->delete('agent_payment');

        $data['companyBalances'] = [];
        if ($role_id === 2) {
            $agentDetail = $this->agentModel->where('user_id', $this->session->get('user_id'))->first();
            if ($agentDetail) {
                $data['companyBalances'] = $this->getResolvedCompanyBalances((int) $agentDetail->id);
            }
        }

        echo view($this->Viewpath . '\payagent/index', $data);
    }

    public function serverList()
    {
        $request = service('request');
        $ctx = $this->getPayagentListContext();

        if ($ctx === null) {
            return $this->response->setStatusCode(403)->setJSON([
                'draw' => (int) ($request->getPost('draw') ?? 0),
                'recordsTotal' => 0,
                'recordsFiltered' => 0,
                'data' => [],
            ]);
        }

        $draw = $request->getPost('draw');
        $start = (int) ($request->getPost('start') ?? 0);
        $length = (int) ($request->getPost('length') ?? 25);
        $searchValue = $request->getPost('search')['value'] ?? '';
        $fromDate = $request->getPost('start_date') ?: date('Y-m-01');
        $toDate = $request->getPost('end_date') ?: date('Y-m-d');

        $userType = (int) $ctx['role_id'];
        $showCompany = in_array($userType, [1, 2], true);
        $showAgent = in_array($userType, [1, 7], true);

        $columns = [0 => 'payagents.id'];
        $colIndex = 1;
        if ($showCompany) {
            $columns[$colIndex++] = 'companies.name';
        }
        if ($showAgent) {
            $columns[$colIndex++] = 'agents.first_name';
        }
        $columns[$colIndex++] = 'payagents.id';
        $columns[$colIndex++] = 'payagents.payment_mode';
        $columns[$colIndex++] = 'payagents.transaction_id';
        $columns[$colIndex++] = 'payagents.status';
        $columns[$colIndex++] = 'payagents.amount';
        $columns[$colIndex++] = null;
        $columns[$colIndex++] = null;
        $columns[$colIndex++] = 'payagents.detail';
        $columns[$colIndex++] = 'payagents.created_at';
        $columns[$colIndex++] = 'payagents.accepted_date';
        $columns[$colIndex++] = 'user_details.first_name';
        $columns[$colIndex++] = null;

        $order = $request->getPost('order');
        $defaultOrderCol = $showCompany && $showAgent ? 10 : ($showCompany || $showAgent ? 9 : 8);
        $orderColumnIndex = ! empty($order) && isset($order[0]['column']) ? (int) $order[0]['column'] : $defaultOrderCol;
        $orderDirection = ! empty($order) && isset($order[0]['dir']) && strtolower($order[0]['dir']) === 'asc' ? 'asc' : 'desc';

        $countBuilder = $this->db->table('payagents');
        $countBuilder->select('payagents.id', false);
        $this->applyPayagentListJoins($countBuilder);
        $this->applyPayagentListScope($countBuilder, $ctx, $fromDate, $toDate);
        $totalRecords = $countBuilder->countAllResults(false);

        $builder = $this->db->table('payagents');
        $builder->select(
            'agents.id AS agentId, agents.first_name, agents.last_name, payagents.*, '
            . 'companies.name AS company_name, '
            . 'CONCAT(COALESCE(user_details.first_name, ""), "  ", COALESCE(user_details.last_name, "")) AS approved_by',
            false
        );
        $this->applyPayagentListJoins($builder);
        $this->applyPayagentListScope($builder, $ctx, $fromDate, $toDate);

        if ($searchValue !== '') {
            $builder->groupStart()
                ->like('payagents.id', $searchValue)
                ->orLike('payagents.transaction_id', $searchValue)
                ->orLike('payagents.detail', $searchValue)
                ->orLike('payagents.amount', $searchValue)
                ->orLike('companies.name', $searchValue)
                ->orLike('agents.first_name', $searchValue)
                ->orLike('agents.last_name', $searchValue)
                ->orLike('user_details.first_name', $searchValue)
                ->orLike('user_details.last_name', $searchValue)
                ->groupEnd();
        }

        $filteredCountBuilder = $this->db->table('payagents');
        $filteredCountBuilder->select('payagents.id', false);
        $this->applyPayagentListJoins($filteredCountBuilder);
        $this->applyPayagentListScope($filteredCountBuilder, $ctx, $fromDate, $toDate);

        if ($searchValue !== '') {
            $filteredCountBuilder->groupStart()
                ->like('payagents.id', $searchValue)
                ->orLike('payagents.transaction_id', $searchValue)
                ->orLike('payagents.detail', $searchValue)
                ->orLike('payagents.amount', $searchValue)
                ->orLike('companies.name', $searchValue)
                ->orLike('agents.first_name', $searchValue)
                ->orLike('agents.last_name', $searchValue)
                ->orLike('user_details.first_name', $searchValue)
                ->orLike('user_details.last_name', $searchValue)
                ->groupEnd();
        }

        $totalRecordwithFilter = $filteredCountBuilder->countAllResults(false);

        if (isset($columns[$orderColumnIndex]) && $columns[$orderColumnIndex] !== null) {
            $builder->orderBy($columns[$orderColumnIndex], $orderDirection);
        } else {
            $builder->orderBy('payagents.id', 'desc');
        }

        if ($length > 0) {
            $builder->limit($length, $start);
        }

        $records = $this->enrichPayagentRows($builder->get()->getResult());
        $rows = [];
        $i = $start + 1;

        foreach ($records as $value) {
            $statusHtml = (int) ($value->status ?? 0) === 1
                ? '<span class="badge bg-success">Approved</span>'
                : '<span class="badge bg-secondary">Pending</span>';

            $paymentModeHtml = (int) ($value->payment_mode ?? 0) === 1
                ? '<span>Online</span>'
                : '<span>Offline</span>';

            $ref = isset($value->reference_display) && $value->reference_display !== ''
                ? $value->reference_display
                : ($value->transaction_id ?? '');
            $refDisplay = ! empty($ref) ? esc($ref) : '—';

            $detail = (string) ($value->detail ?? '');
            if (strpos($detail, 'recharge balance') !== 0) {
                $detail = str_replace('Refund (', 'Cancelation (', $detail);
                $detail = preg_replace('/Cancel\s*amount\s*:/i', 'Fee Amount:', $detail);
            }

            $approvedBy = ((int) ($value->status ?? 0) === 1 && (int) ($value->payment_mode ?? 0) === 1)
                ? 'System'
                : esc(trim($value->approved_by ?? ''));

            $actionHtml = '<form action="' . base_url(route_to('delete-payagent', $value->id)) . '" method="post" class="d-inline" accept-charset="utf-8">';
            $actionHtml .= csrf_field();

            if ((int) ($value->status ?? 0) === 0) {
                if ($ctx['edit_data']) {
                    $actionHtml .= '<a href="' . base_url(route_to('edit-payagent', $value->id)) . '" class="btn btn-sm btn-info text-white" title="' . lang('Localize.edit') . '"><i class="fas fa-edit"></i></a> ';
                }
                if ($ctx['delete_data']) {
                    $actionHtml .= '<button type="submit" class="btn btn-sm btn-danger" title="' . lang('Localize.delete') . '"><i class="far fa-trash-alt"></i></button> ';
                }
            }

            if (in_array($userType, [1, 7], true)) {
                if ((int) ($value->status ?? 0) === 0) {
                    $actionHtml .= '<a href="' . base_url(route_to('status-payagent', $value->id, 1, $value->amount, $value->agent_id)) . '" class="btn btn-sm btn-success text-white" title="' . lang('Localize.active') . '"><i class="fas fa-check"></i></a> ';
                } else {
                    $actionHtml .= '<a href="' . base_url(route_to('status-payagent', $value->id, 0, $value->amount, $value->agent_id)) . '" class="btn btn-sm btn-dark text-white" title="' . lang('Localize.disable') . '"><i class="fas fa-times"></i></a> ';
                }
            }

            $actionHtml .= '</form>';

            $entry = ['index' => $i++];

            if ($showCompany) {
                $entry['company_name'] = ! empty($value->company_name) ? esc($value->company_name) : '-';
            }
            if ($showAgent) {
                $entry['agent_name'] = '<a href="' . base_url(route_to('show-payagent', $value->agent_id)) . '">'
                    . esc(trim(($value->first_name ?? '') . ' ' . ($value->last_name ?? ''))) . '</a>';
            }

            $entry += [
                'payment_id' => esc($value->id ?? ''),
                'payment_mode' => $paymentModeHtml,
                'reference' => $refDisplay,
                'status' => $statusHtml,
                'amount' => esc($value->amount ?? ''),
                'cancel_amount' => ! empty($value->cancel_amount) ? esc($value->cancel_amount) : '',
                'total_balance' => esc($value->total_balance ?? ''),
                'detail' => esc($detail),
                'created_at' => esc(date('Y-m-d H:i:s', strtotime($value->created_at))),
                'accepted_date' => ($value->accepted_date ?? null) !== null
                    ? esc(date('Y-m-d H:i:s', strtotime($value->accepted_date)))
                    : '',
                'approved_by' => $approvedBy,
                'action' => $actionHtml,
            ];

            $rows[] = $entry;
        }

        return $this->response->setJSON([
            'draw' => (int) $draw,
            'recordsTotal' => $totalRecords,
            'recordsFiltered' => $totalRecordwithFilter,
            'data' => $rows,
        ]);
    }

    private function getPayagentListContext(): ?array
    {
        $roleId = (int) $this->session->get('role_id');
        $userId = (int) $this->session->get('user_id');
        $userRole = $roleId;

        if (! in_array($roleId, [1, 2, 7], true)) {
            return null;
        }

        $ctx = [
            'role_id' => $roleId,
            'user_id' => $userId,
            'agent_agent_id' => 0,
            'agent_company_id' => (int) $this->session->get('company_id'),
            'company_company_id' => (int) $this->session->get('company_id'),
        ];

        $rolepermissionLibrary = new Rolepermission();
        $ctx['edit_data'] = $rolepermissionLibrary->edit('agent_payment');
        $ctx['delete_data'] = $rolepermissionLibrary->delete('agent_payment');

        if ($roleId === 2) {
            $agentDetail = $this->agentModel->where('user_id', $userId)->first();
            if (! $agentDetail) {
                return null;
            }
            $ctx['agent_agent_id'] = (int) $agentDetail->id;
        }

        return $ctx;
    }

    private function applyPayagentListJoins($builder): void
    {
        $builder
            ->join('agents', 'agents.id = payagents.agent_id', 'left')
            ->join('companies', 'companies.id = payagents.company_id', 'left')
            ->join('user_details', 'user_details.user_id = payagents.approved_id', 'left');
    }

    private function applyPayagentListScope($builder, array $ctx, string $fromDate, string $toDate): void
    {
        $builder
            ->where('payagents.created_at >=', $fromDate . ' 00:00:00')
            ->where('payagents.created_at <=', $toDate . ' 23:59:59');

        $roleId = (int) $ctx['role_id'];
        $userRole = $roleId;

        if (! in_array($userRole, [1, 2, 7], true)) {
            $rolepermissionLibrary = new Rolepermission();
            if (! $rolepermissionLibrary->create('all_tickets')) {
                $builder->where('payagents.agent_id', $ctx['agent_agent_id']);
            } else {
                $builder->where('payagents.company_id', $ctx['company_company_id']);
            }

            return;
        }

        if ($roleId === 7) {
            $builder->where('payagents.company_id', $ctx['company_company_id']);
        }

        if ($roleId === 2) {
            $builder->where('payagents.agent_id', $ctx['agent_agent_id']);
        }
    }



    public function create()
	{
        $currentUserId =  $this->session->get('user_id');
        $agentDetail = 	$this->agentModel->where('user_id',$currentUserId)->first();
        $agentid = $agentDetail->id;


		$amount = $this->request->getVar('amount');
        $company_id = $this->request->getVar('company_id');



		$data= array(
			"agent_id"=> $agentid,
            "company_id" => $company_id,
			"amount"=> $amount,
			"status"=> 0,
			
		);

	
		
		if($this->validation->run($data, 'agentpay'))
		{
			
			$this->agentPayment->insert($data);  
			return redirect()->route('index-payagent')->with("success","Data Save");
		}
		
		
		else
		{

            $allTranSaction = $this->agentTotal->where('agent_id',$agentid)->findAll();
            $agentIncomeTotal = array_sum(array_column($allTranSaction, 'income'));
            $agentExpenseTotal = array_sum(array_column($allTranSaction, 'expense'));
            $keepMoney = $agentIncomeTotal - $agentExpenseTotal;
			$data['keepMoney'] = $keepMoney;
			$data['validation'] = $this->validation;
            $data['module'] =    lang("Localize.account") ; 
            $data['title']  =    lang("Localize.agent_payment") ;
            echo view($this->Viewpath . '\payagent/new',$data);

		}
		
	}

    public function status($id,$value,$amount,$agentid)
    {
		
		$db = \Config\Database::connect();
		$agentinfo = $this->agentModel->find($agentid);
		$systemuserid = $agentinfo->user_id;
        // print_r($systemuserid);die;
        // $user_id =  $this->session->get('user_id');
        
        if($value == 1)
        {
            // (type, detail, amount, system_user_id) VALUES (?, ?, ?, ?)
            $sqlagents = "INSERT INTO accounts (booking_id, transaction_type, type, detail, amount, system_user_id) VALUES (NULL, ?, ?, ?, ?, ?) ";
            $db->query($sqlagents, ['recharge', 'income', 'recharge by admin', $amount, $systemuserid]);
            
            $lastInsertId = $db->insertID();

            // Get the company_id from the payagent record being approved
            $payagentRecord = $this->agentPayment->find($id);
            $approvedCompanyId = $payagentRecord ? $payagentRecord->company_id : 0;

            // Compute per-company running balance
            $lastCompanyRecord = $this->agentPayment
                ->select('total_balance')
                ->where('agent_id', $agentid)
                ->where('company_id', $approvedCompanyId)
                ->where('status', 1)
                ->orderBy('id', 'DESC')
                ->first();
            $currentCompanyBalance = $lastCompanyRecord ? (float)$lastCompanyRecord->total_balance : 0;
            $newCompanyBalance = $currentCompanyBalance + $amount;

            // Also update global wallet
            $currentGlobalWallet = $agentinfo->wallet;
            $globalNewWallet = $currentGlobalWallet + $amount;
            
            $data= array(
                "id" => $id,
                "status"=> $value,
                "account_id"=>$lastInsertId,
                "approved_id"=> session()->get('user_id'),
                "accepted_date" => date("y-m-d H:i:s"),
                "total_balance" => $newCompanyBalance,
            );
            
            $this->agentPayment->save($data);
            
            
            $sqlagentstotal = "INSERT INTO agenttotals (agent_id, booking_id, account_id, income, expense, detail) VALUES (?, ?, ?, ?, ?, ?)";
            $db->query($sqlagentstotal, [$agentid, 'recharge', $lastInsertId, $amount, 0, 'recharge by admin']);
        
        
// 		print_r("success");die;
        
        
        // print_r($agentinfo);die;
        
        // print_r($newamt);die;
        
        $agentData['wallet'] = $globalNewWallet;
        
        $sql = "UPDATE agents SET wallet = ? WHERE id = ?";

            // Execute the query
            $result = $db->query($sql, [$globalNewWallet, $agentid]);
            
            
        }else 
        {
            // Get the company_id from the payagent record being rejected
            $payagentRejectRecord = $this->agentPayment->find($id);
            $rejectCompanyId = $payagentRejectRecord ? $payagentRejectRecord->company_id : 0;

            // Compute per-company balance after reversal: look up last balance then subtract the amount
            $lastCompanyRecordReject = $this->agentPayment
                ->select('total_balance')
                ->where('agent_id', $agentid)
                ->where('company_id', $rejectCompanyId)
                ->where('status', 1)
                ->orderBy('id', 'DESC')
                ->first();
            $currentCompanyBalanceReject = $lastCompanyRecordReject ? (float)$lastCompanyRecordReject->total_balance : 0;
            $newCompanyBalanceReject = $currentCompanyBalanceReject - $amount;

            // Reverse global wallet too
            $globalWalletAfterReject = $agentinfo->wallet - $amount;

            $data= array(
			"id" => $id,
			"status"=> $value,
            "total_balance" => $newCompanyBalanceReject,
		);
		
        $this->agentPayment->save($data);
        
        $payagents = $this->agentPayment->find($id);
        
        $account_id = $payagents->account_id;
                  
            
            // (type, detail, amount, system_user_id) VALUES (?, ?, ?, ?)
            
            
            
        $sqlagents = "DELETE  FROM accounts WHERE id=$account_id";
        $db->query($sqlagents);
        
        
		
        
        
        $sqlagentstotal = "DELETE FROM agenttotals WHERE account_id=$account_id";
        $db->query($sqlagentstotal);
        
        
        $agentData['wallet'] = $globalWalletAfterReject;
        
        $sql = "UPDATE agents SET wallet = ? WHERE id = ?";

            // Execute the query
            $result = $db->query($sql, [$globalWalletAfterReject, $agentid]);
        }
        
        
		return redirect()->route('index-payagent')->with("success","Data Save");
    }

    public function delete($id)
	{
		$this->agentPayment->delete($id);
		return redirect()->route('index-payagent')->with("fail","Data Deleted");
	}



    public function range()
    {
        $fromDate = $this->request->getVar('start_date') ?: date('Y-m-01');
        $toDate = $this->request->getVar('end_date') ?: date('Y-m-d');

        return redirect()->to(route_to('index-payagent') . '?' . http_build_query([
            'start_date' => $fromDate,
            'end_date' => $toDate,
        ]));
    }



    public function edit($id)
	{
		 
		$data['agentpay'] = $this->agentPayment->find($id);
		$data['module'] =    lang("Localize.account") ; 

		$heading = lang("Localize.tranjection").' '.lang("Localize.edit");
		$data['pageheading'] = $heading;

		echo view($this->Viewpath.'\payagent/edit',$data);
	}



    public function update($id)
	{
        
		$amount = $this->request->getVar('amount');

		$data= array(
            "id" => $id,
			"amount"=> $amount,
		);

        $agentPay = $this->agentPayment->find($id);

        $validData= array(
			"agent_id"=> $agentPay->agent_id ,
			"amount"=> $amount,
			"status"=> $agentPay->status,
			
		);
	
		
		if($this->validation->run($validData, 'agentpay'))
		{
			
			$this->agentPayment->save($data);
			return redirect()->route('index-payagent')->with("success","Data Save");
		}
		
		
		else
		{

            $data['agentpay'] = $this->agentPayment->find($id);
            $data['module'] =    lang("Localize.account") ; 

            $heading = lang("Localize.tranjection").' '.lang("Localize.edit");
            $data['pageheading'] = $heading;

            echo view($this->Viewpath . '\payagent/edit',$data);

		}
		
	}



    public function show($agentId)
    {

        $allTranSaction = $this->agentTotal->where('agent_id',$agentId)->findAll();

        $givenMoney = $this->agentPayment->where('agent_id',$agentId)->findAll();
        $givenTotalMoney = array_sum(array_column($givenMoney, 'amount'));
        $agentIncomeTotal = array_sum(array_column($allTranSaction, 'income'));
        $agentExpenseTotal = array_sum(array_column($allTranSaction, 'expense'));
        $totalMoney = $agentIncomeTotal - $agentExpenseTotal;
        $dueMoney = $totalMoney - $givenTotalMoney;
        $data['givenMoney'] = $givenTotalMoney;
        $data['totalMoney'] = $totalMoney;
        $data['dueMoney'] =  $dueMoney;

        $startData = date('Y-m-01');
		$endDate = date('Y-m-d');
		$fromDate = $startData; //$this->request->getVar('start_date');
		$toDate = $endDate; //$this->request->getVar('end_date');

        $agetntPay = $this->agentPayment->select('agents.id as agentId,agents.*,payagents.*, companies.name AS company_name')
						->join('agents', 'agents.id = payagents.agent_id','left')
                        ->join('companies', 'companies.id = payagents.company_id','left')
                        ->where('payagents.agent_id',$agentId)
						->where('DATE(payagents.created_at) >=', $fromDate)->where('DATE(payagents.created_at) <=', $toDate)
						->findAll();


        $data['pageheading'] = lang("Localize.transaction_list");

		$data['module'] =    lang("Localize.account") ; 
		$data['title']  =    lang("Localize.agent_payment") ;

        $data['agentid'] =  $agentId;
        $data['payagent'] = $this->enrichPayagentRows($agetntPay);
		echo view($this->Viewpath . '\payagent/singleagent',$data);
    }



    public function singleagentrange()
    {
        $agentId = $this->request->getVar('agentid');
        $allTranSaction = $this->agentTotal->where('agent_id',$agentId)->findAll();

        $givenMoney = $this->agentPayment->where('agent_id',$agentId)->findAll();
        $givenTotalMoney = array_sum(array_column($givenMoney, 'amount'));
        $agentIncomeTotal = array_sum(array_column($allTranSaction, 'income'));
        $agentExpenseTotal = array_sum(array_column($allTranSaction, 'expense'));
        $totalMoney = $agentIncomeTotal - $agentExpenseTotal;
        $dueMoney = $totalMoney - $givenTotalMoney;
        $data['givenMoney'] = $givenTotalMoney;
        $data['totalMoney'] = $totalMoney;
        $data['dueMoney'] =  $dueMoney;

        $startData = $this->request->getVar('start_date');
		$endDate = $this->request->getVar('end_date');
		$fromDate = $startData; //$this->request->getVar('start_date');
		$toDate = $endDate; //$this->request->getVar('end_date');

        $agetntPay = $this->agentPayment->select('agents.id as agentId,agents.*,payagents.*, companies.name AS company_name')
						->join('agents', 'agents.id = payagents.agent_id','left')
                        ->join('companies', 'companies.id = payagents.company_id','left')
                        ->where('payagents.agent_id',$agentId)
						->where('DATE(payagents.created_at) >=', $fromDate)->where('DATE(payagents.created_at) <=', $toDate)
						->findAll();


        $data['pageheading'] = lang("Localize.transaction_list");

		$data['module'] =    lang("Localize.account") ; 
		$data['title']  =    lang("Localize.agent_payment") ;

        $data['agentid'] =  $agentId;
        $data['payagent'] = $this->enrichPayagentRows($agetntPay);
		echo view($this->Viewpath . '\payagent/singleagent',$data);
    }

    /**
     * Geidea / gateway wallet top-ups store payment reference as a UUID in transaction_id (not a ticket booking_id).
     */
    private function isMerchantPaymentReference(string $transactionId): bool
    {
        $transactionId = trim($transactionId);

        return $transactionId !== '' && (bool) preg_match(
            '/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i',
            $transactionId
        );
    }

    private function enrichPayagentRows(array $rows): array
    {
        foreach ($rows as $row) {
            $bookingId = trim((string) ($row->transaction_id ?? ''));
            $companyId = (int) ($row->company_id ?? 0);
            $isMerchantRef = $this->isMerchantPaymentReference($bookingId);

            if (!$isMerchantRef && $companyId <= 0 && $bookingId !== '') {
                $ticketCompany = $this->db->table('tickets')
                    ->select('trips.company_id, companies.name AS company_name')
                    ->join('trips', 'trips.id = tickets.trip_id', 'left')
                    ->join('companies', 'companies.id = trips.company_id', 'left')
                    ->where('tickets.booking_id', $bookingId)
                    ->get()
                    ->getRow();

                if ($ticketCompany) {
                    $companyId = (int) ($ticketCompany->company_id ?? 0);
                    $row->company_id = $companyId;
                    $row->company_name = $ticketCompany->company_name ?? ($row->company_name ?? '');
                }
            }

            if ($isMerchantRef) {
                $row->cancel_amount = null;
                $sessionId = $bookingId;
                $resolvedMerchant = trim((string) ($row->merchant_reference_id ?? ''));
                if ($resolvedMerchant === '') {
                    $pg = $this->db->table('paystatusgeidea')
                        ->select('merchant_id')
                        ->where('sessionId', $sessionId)
                        ->orderBy('id', 'DESC')
                        ->limit(1)
                        ->get()
                        ->getRow();
                    if ($pg && trim((string) ($pg->merchant_id ?? '')) !== '') {
                        $resolvedMerchant = trim((string) $pg->merchant_id);
                    }
                }
                // transaction_id = Geidea session UUID; merchant_reference_id = order ref (order…)
                $row->reference_display = $resolvedMerchant !== '' ? $resolvedMerchant : $sessionId;
                $row->detail = 'recharge balance (' . ($resolvedMerchant !== '' ? $resolvedMerchant : $sessionId) . ')';
            } elseif ($bookingId !== '') {
                $cancel = $this->db->table('cancels')
                    ->select('cancel_fee')
                    ->where('booking_id', $bookingId)
                    ->orderBy('id', 'DESC')
                    ->get()
                    ->getRow();

                $refund = $this->db->table('refunds')
                    ->select('refund_fee')
                    ->where('booking_id', $bookingId)
                    ->orderBy('id', 'DESC')
                    ->get()
                    ->getRow();

                $hasCancelOrRefund = ($cancel !== null) || ($refund !== null);
                $cancelAmount = $cancel->cancel_fee ?? $refund->refund_fee ?? null;
                $row->cancel_amount = $cancelAmount;

                if (empty($row->detail)) {
                    if ($hasCancelOrRefund) {
                        $row->detail = 'Cancelation (' . $bookingId . ')';
                        if ($cancelAmount !== null && $cancelAmount !== '') {
                            $row->detail .= ' Fee Amount: ' . $cancelAmount;
                        }
                    } else {
                        $row->detail = lang('Localize.ticket_booking') . ' (' . $bookingId . ')';
                    }
                } elseif (!$hasCancelOrRefund && preg_match('/^\s*Cancelation\s*\(/i', (string) $row->detail)) {
                    $row->detail = lang('Localize.ticket_booking') . ' (' . $bookingId . ')';
                }
            } else {
                $row->cancel_amount = null;
            }

            if ($companyId > 0 && (int) ($row->status ?? 0) === 1) {
                $balance = $this->db->table('payagents p')
                    ->select('SUM(CAST(p.amount AS DECIMAL(10,2))) AS total_balance')
                    ->join('tickets t', 't.booking_id = p.transaction_id', 'left')
                    ->join('trips tr', 'tr.id = t.trip_id', 'left')
                    ->where('p.agent_id', (int) ($row->agent_id ?? 0))
                    ->where('p.status', 1)
                    ->where('p.deleted_at IS NULL', null, false)
                    ->where('p.id <=', (int) ($row->id ?? 0))
                    ->groupStart()
                        ->where('p.company_id', $companyId)
                        ->orGroupStart()
                            ->where('p.company_id', 0)
                            ->where('tr.company_id', $companyId)
                        ->groupEnd()
                    ->groupEnd()
                    ->get()
                    ->getRow();

                $row->total_balance = $balance->total_balance ?? $row->total_balance;
            }
        }

        return $rows;
    }

    private function getResolvedCompanyBalances(int $agentId): array
    {
        $rows = $this->db->table('payagents p')
            ->select('p.company_id, p.transaction_id, p.amount, direct_company.name AS direct_company_name, ticket_company.id AS ticket_company_id, ticket_company.name AS ticket_company_name')
            ->join('companies direct_company', 'direct_company.id = p.company_id', 'left')
            ->join('tickets t', 't.booking_id = p.transaction_id', 'left')
            ->join('trips tr', 'tr.id = t.trip_id', 'left')
            ->join('companies ticket_company', 'ticket_company.id = tr.company_id', 'left')
            ->where('p.agent_id', $agentId)
            ->where('p.status', 1)
            ->where('p.deleted_at IS NULL', null, false)
            ->get()
            ->getResult();

        $balances = [];
        foreach ($rows as $row) {
            $companyId = (int) ($row->company_id ?? 0);
            $companyName = $row->direct_company_name ?? '';

            if ($companyId <= 0 && !empty($row->ticket_company_id)) {
                $companyId = (int) $row->ticket_company_id;
                $companyName = $row->ticket_company_name ?? '';
            }

            if ($companyId <= 0 || $companyName === '') {
                continue;
            }

            if (!isset($balances[$companyName])) {
                $balances[$companyName] = 0;
            }

            $balances[$companyName] += (float) ($row->amount ?? 0);
        }

        return $balances;
    }





}
