<?php

namespace Modules\Agent\Controllers;

use App\Controllers\BaseController;
use Modules\Agent\Models\AgentModel;
use Modules\Agent\Models\Agentcommission;
use Modules\Agent\Models\AgenttotalModel;
use Modules\User\Models\UserModel;
use Modules\User\Models\UserDetailModel;
use Modules\Role\Models\RoleModel;
use Modules\Location\Models\LocationModel;
use App\Libraries\Rolepermission;
use Modules\Role\Models\PermissionModel;
use Modules\Role\Models\MenuModel;
use Modules\Company\Models\CompanyModel;
use Modules\Account\Models\PayagentModel;

class Agent extends BaseController
{
    protected $Viewpath;
    protected $db;
    protected $userModel;
    protected $userDetailModel;
    protected $agentModel;
    protected $roleModel;
    protected $locationModel;
    protected $agentCommissionModel;
    protected $agentTotal;
    protected $companyModel;
    protected $permissionModel;
    protected $menuModel;
    protected $agentPayment;


    public function __construct()
    {
        $this->Viewpath = "Modules\Agent\Views";
        $this->agentModel = new AgentModel();
        $this->userModel = new UserModel();
        $this->userDetailModel = new UserDetailModel();
        $this->roleModel = new RoleModel();
        $this->locationModel = new LocationModel();
        $this->db = \Config\Database::connect();

        $this->agentCommissionModel = new Agentcommission();
        $this->agentTotal = new AgenttotalModel();
        $this->companyModel = new CompanyModel();
        $this->permissionModel = new PermissionModel();
        $this->menuModel = new MenuModel();
        $this->agentPayment = new PayagentModel();

    }

    public function index(bool $showTrashOnly = false)
    {
        // build layout data
        $data['pageheading'] = lang("Localize.agent_list");
        $data['module'] = lang("Localize.agent");
        $data['title']  = lang("Localize.agent_list");

        // build perission dat
        $rolepermissionLibrary = new Rolepermission();
        $data['add_agent'] = $rolepermissionLibrary->create("add_agent");
        $data['edit_agent'] = $rolepermissionLibrary->edit("agent_list");
        $data['delete_agent'] = $rolepermissionLibrary->delete("agent_list");
        $data['companies'] = $this->companyModel->findAll();

        // list agents by role id
        $userRole = $this->session->get('role_id');

        if ($userRole != 2) {
            
            $this->userModel
                ->select('users.*, agents.*, GROUP_CONCAT(DISTINCT companies.name SEPARATOR ", ") AS company_names, agents.id AS aget_id')
                ->join('agents', 'agents.user_id = users.id', 'left')
                ->join('companies', 'FIND_IN_SET(users.id, companies.user_id)', 'left')
                ->where('role_id', 2);

            // $companyBalances = $this->agentPayment->select('companies.name as company_name, SUM(payagents.amount) as total_amount')
            //     ->join('agents', 'agents.id = payagents.agent_id', 'left')
            //     ->join('companies', 'companies.id = payagents.company_id', 'left')
            //     ->where('payagents.company_id > 0')
            //     ->where('payagents.status',1)
            //     ->groupBy('payagents.agent_id')
            //     ->findAll();
            $companyBalances = $this->agentPayment
                ->select('agents.*, companies.name as company_name, payagents.agent_id, payagents.company_id, SUM(payagents.amount) as total_amount')
                ->join('agents', 'agents.id = payagents.agent_id', 'left')
                ->join('companies', 'companies.id = payagents.company_id', 'left')
                ->where('payagents.company_id >', 0)
                ->where('payagents.status', 1)
                ->groupBy(['payagents.agent_id', 'payagents.company_id'])
                ->findAll();
            $agentcompanyBalance = [];

            foreach ($companyBalances as $agentBalnce) {
                $agentId = $agentBalnce->agent_id;
                $companyString = $agentBalnce->total_amount . " (" . $agentBalnce->company_name . ")";
                $agentcompanyBalance[$agentId][] = $companyString;
            }

            if(!in_array($userRole, [1,2])){
                $this->userModel->where('companies.id', session()->get('company_id'));       
            }

            $this->userModel->groupBy('users.id')->orderBy('users.id', 'DESC');

            if ($showTrashOnly) {
                $data['trash_view'] = true;
                $this->userModel->onlyDeleted();
            }
            $agentDetail = $this->userModel->findAll();
            $agentDetails = [];
            foreach ($agentDetail as $value) {
                if (isset($agentcompanyBalance[$value->aget_id]) && !empty($agentcompanyBalance[$value->aget_id])) {
                    // Format each company balance string to round amount to 2 decimals
                    $formattedBalances = array_map(function($item) {
                        if (preg_match('/([\d.]+)\s+\((.+)\)/', $item, $matches)) {
                            $amount = round((float)$matches[1], 2);
                            return number_format($amount, 2) . " (" . $matches[2] . ")";
                        }
                        return $item; // fallback if not matching
                    }, $agentcompanyBalance[$value->aget_id]);

                    $value->company_balances = $formattedBalances;
                } else {
                    $value->company_balances = [];
                }

                $agentDetails[] = $value;
            }
            $data['agentdetail'] = $agentDetails;
            $data['role_id'] = $userRole;
        } else {
            $agentid = $this->session->get('user_id');
            $data['agentdetail'] = $this->userModel->join('agents', 'agents.user_id = users.id', 'left')->where('role_id', 2)->where('agents.user_id', $agentid)->findAll();
            $data['role_id'] = $userRole;
        }

        return view($this->Viewpath . '\agent\index', $data);
    }

    public function new()
    {
        // build layout data
        $data['pageheading'] = lang("Localize.add_agent");
        $data['module'] = lang("Localize.agent");
        $data['title']  = lang("Localize.add_agent");

        // build country and location list 
        $userRole = $this->session->get('role_id');
        $builder = $this->db->table('country');
        $query = $builder->get();
        $data['country'] = $query->getResult();
        $data['location'] = $this->locationModel->findAll();
        
        if($userRole == 7){
            $companyId = $this->session->get('company_id');
            $data['companies'] = $this->companyModel->where('id', $companyId)->findAll();
        }else{

            $data['companies'] = $this->companyModel->findAll();
        }
        

        return view($this->Viewpath . '\agent/new', $data);
    }

    public function create()
    {
        // build additional table data
        $path = 'image/agent';
        $slug = bin2hex(random_bytes(5));
        $role_id = 2;
        $status = 1;

        // build agent login credintial
        $login_email = $this->request->getVar('login_email');
        $login_mobile = $this->request->getVar('login_mobile');

        // build agent password
        $password = $this->request->getVar('password');
        $confirmPassword = $this->request->getVar('confirm');
        $password_hash = password_hash($password, PASSWORD_DEFAULT);

        $userData = array(
            "login_email" => $login_email,
            "login_mobile" => $login_mobile,
            "password" => $password,
            "confirm" => $confirmPassword,
            "slug" => $slug,
            "role_id" => $role_id,
            "status" => $status,
        );

        if ($this->validation->run($userData, 'user')) {
            // user data is valid
            // starting database query transaction
            $this->db->transStart();

            // Insert to user table
            $userId = $this->userModel->insert(array_merge($userData, ['password' => $password_hash]));

            // agent table data operation 
            // build nid and document data
            $imagenid = $imagedocu =  '';

            $nidimage = $this->request->getFile('nid_picture');
            $profileimage = $this->request->getFile('profile_picture');

            if ($nidimage->isValid() && !$nidimage->hasMoved()) {
                // nid image exists
                $imagenid = $this->imgaeCheck($nidimage, $path);
            }

            if ($profileimage->isValid() && !$profileimage->hasMoved()) {
                // profile picture exists
                $imagedocu = $this->imgaeCheck($profileimage, $path);
            }

            // build agent table data
            $agentData = array(
                "user_id" => $userId,
                "first_name" => $this->request->getVar('first_name'),
                "last_name" => $this->request->getVar('last_name'),
                "country_id" => $this->request->getVar('country_id'),
                "address" => $this->request->getVar('address'),
                "city" => $this->request->getVar('city'),
                "zip" => $this->request->getVar('zip'),
                "location_id" => $this->request->getVar('location_id'),
                "blood" => $this->request->getVar('blood'),
                "commission" => $this->request->getVar('commission'),
                "profile_picture" => $imagedocu,
                "nid_picture" => $imagenid,
                "discount" => $this->request->getVar('discount'),
                "coupon" => $this->request->getVar('coupon')
            );

            // use id type and id number
            ($idType = $this->request->getVar('id_type')) && $agentData['id_type'] = $idType;
            ($idNumber = $this->request->getVar('id_number')) && $agentData['id_number'] = $idNumber;

            if ($this->validation->run($agentData, 'agent')) {
                // insert data to agent model
                $this->agentModel->insert($agentData);

                $companyIds = array_filter($this->request->getVar('company'));
                if (!empty($companyIds)) {
                    $this->companyModel
                        ->whereIn('id', $companyIds)
                        ->set('user_id', "CONCAT(user_id, ',', {$userId})", false)
                        ->update();
                }

                $userDetails = $this->userModel
                                        ->where('id', $userId)
                                        ->first();
                $role = $this->request->getVar('agent');
                if($role == 1){
                    $permissionData = [];
                    $menuIds = [36,37,39,40]; 
                    foreach ($menuIds as $menuId) {
                        $menuDetails = $this->menuModel
                            ->where('id', $menuId)
                            ->first();

                        if ($menuDetails) { // Ensure the menu exists
                            $permissionData[] = array(
                                "role_id" => $userDetails->role_id,
                                "user_id" => $userId,
                                "menu_id" => $menuId,
                                "menu_title" => $menuDetails->menu_title,
                                "create" => 1,
                                "read" => 1,
                                "edit" => 1,
                            );
                        }

                    }
                    if($this->request->getVar('pretrip') == 1){
                        $permissionData[] = array(
                                "role_id" => $userDetails->role_id,
                                "user_id" => $userId,
                                "menu_id" => 130,
                                "menu_title" => "pre_trip",
                                "create" => 1,
                                "read" => 1,
                                "edit" => 1,
                            );
                    }
                    if (!empty($permissionData)) {
                        $this->permissionModel->insertBatch($permissionData);
                    }

                }

                // completed database query transaction
                $this->db->transComplete();

                return redirect()->route('index-agent')->with("success", "Data Save");
            }
        }

        // invalid data posted
        // rollback query operation
        return redirect()->back()->withInput()->with('fail', $this->validation->listErrors());
    }

    public function edit($id)
    {
        // build layout data
        $data['module'] = lang("Localize.agent");
        $data['title']  = lang("Localize.agent_list");
        $data['pageheading'] = lang("Localize.agent") . ' ' . lang("Localize.edit");

        // build country and location data
        $builder = $this->db->table('country');
        $query   = $builder->get();
        $data['country'] = $query->getResult();
        $data['location'] = $this->locationModel->findAll();

        // build agent details
        $data['agentdetail'] =  $this->agentModel->select('users.*,agents.*,users.id as userid,agents.id as agentid')->join('users', 'users.id = agents.user_id')->where('users.role_id', 2)->where('agents.id', $id)->first();
        $agentUserId = $data['agentdetail']->userid;
        $companyInfo = $this->companyModel
                            ->where("FIND_IN_SET('{$agentUserId}', user_id) > 0")
                            ->findAll();
        $companyIds = array_column($companyInfo, 'id');
        $companyIdsString = implode(',', $companyIds);

        $data['selectedCompanies'] = $companyIds;
        $data['companies'] = $this->companyModel->findAll();
        $userDetails = $this->permissionModel
                            ->where('user_id', $agentUserId)
                            ->whereIn('menu_id', [36, 4, 130])
                            ->findAll();
        $data['agentdetail']->employe = 0;
        $data['agentdetail']->journey = 0;
        $data['agentdetail']->pretrip = 0;
        foreach ($userDetails as $detail) {
            if ($detail->menu_id == 36) {
                $data['agentdetail']->employe = 1;
            }
            if ($detail->menu_id == 4) {
                $data['agentdetail']->journey = 1;
            }
            if ($detail->menu_id == 130) {
                $data['agentdetail']->pretrip = 1;
            }
        }

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

    public function update($userId, $agentId)
    {
        $path = 'image/agent';
        $imagenid = $imagedocu = '';
        $nidimage = $this->request->getFile('nid_picture');
        $profileimage = $this->request->getFile('profile_picture');

        $userId = $this->request->getVar('userId');
        $agentId = $this->request->getVar('agentId');

        $companyIds = $this->request->getVar('company');

        // starting database transaction
        $this->db->transStart();

        if ($nidimage->isValid() && !$nidimage->hasMoved()) {
            // nid image exists
            $imagenid = $this->imgaeCheck($nidimage, $path);
        } else {
            // nid image not exists
            // switch back to old image
            $imagenid = $this->request->getVar('documentoldpic');
        }

        if ($profileimage->isValid() && !$profileimage->hasMoved()) {
            // profile picture exists
            $imagedocu = $this->imgaeCheck($profileimage, $path);
        } else {
            // profile picture not exists
            // switch back to old image
            $imagedocu = $this->request->getVar('profileoldpic');
        }

        // build user model data
        $userData = array(
            "id" => $this->request->getVar('userId'),
            "login_email" => $this->request->getVar('login_email'),
            "login_mobile" => $this->request->getVar('login_mobile'),
        );

        if ($this->validation->run($userData, 'user')) {
            // insert data to user model
            $this->userModel->save($userData);

            // build agent model data
            $agentData = array(
                "id" => $agentId,
                "user_id" => $userId,
                "first_name" => $this->request->getVar('first_name'),
                "last_name" => $this->request->getVar('last_name'),
                "country_id" => $this->request->getVar('country_id'),
                "id_type" => $this->request->getVar('id_type') ?: null,
                "id_number" => $this->request->getVar('id_number') ?: null,
                "address" => $this->request->getVar('address'),
                "city" => $this->request->getVar('city'),
                "zip" => $this->request->getVar('zip'),
                "location_id" => $this->request->getVar('location_id'),
                "blood" => $this->request->getVar('blood'),
                "commission" => $this->request->getVar('commission'),
                "profile_picture" => $imagedocu,
                "nid_picture" => $imagenid,
                "discount" => $this->request->getVar('discount'),
                "coupon"   => $this->request->getVar('coupon')
            );

            if ($this->validation->run($agentData, 'agent')) {
                // agent data is valid
                // update agent data
                $this->agentModel->save($agentData);

                //For remove existing user from companies table
                $oldCompanies = $this->companyModel->select('id, user_id')->findAll();
                $userIdToRemove = $userId;
                foreach($oldCompanies as $oldCompany){

                    $oldExistingUserIds = explode(',', $oldCompany->user_id);
                    $updatedUserIds = array_filter($oldExistingUserIds, function($existuserId) use ($userIdToRemove) {
                                            return $existuserId != $userIdToRemove;
                                        });

                    $updatedUserIds = implode(',', $updatedUserIds);
                    $this->companyModel
                        ->where('id', $oldCompany->id)
                        ->set('user_id', $updatedUserIds)
                        ->update();
                }


                //For add user in company with existing
                // $companyIds = array_filter($this->request->getVar('company'));
                if (!empty($companyIds)) {
                    $this->companyModel
                        ->whereIn('id', $companyIds)
                        ->set('user_id', "CONCAT(user_id, ',', {$userId})", false)
                        ->update();
                }

                //Remove role first
                $userDetails = $this->userModel
                                        ->where('id', $userId)
                                        ->first();
                $menuIds = [4,36,37,38,39,40,130];
                
                $this->permissionModel
                    ->where('user_id', $userId)
                    ->where('role_id', $userDetails->role_id)
                    ->whereIn('menu_id', $menuIds)
                    ->delete();

                //For assign the roles to agent for agent listing
                $role = $this->request->getVar('agent');
                $journey = $this->request->getVar('journey');
                $pretrip = $this->request->getVar('pretrip');
                if($role == 1){
                    $permissionData = [];
                    $menuIds = [36,37,39,40];
                    foreach ($menuIds as $menuId) {
                        $menuDetails = $this->menuModel
                            ->where('id', $menuId)
                            ->first();

                        if ($menuDetails) { // Ensure the menu exists
                            $permissionData[] = array(
                                "role_id" => $userDetails->role_id,
                                "user_id" => $userId,
                                "menu_id" => $menuId,
                                "menu_title" => $menuDetails->menu_title,
                                "create" => 1,
                                "read" => 1,
                                "edit" => 1,
                            );
                        }
                    }

                    if (!empty($permissionData)) {
                        $this->permissionModel->insertBatch($permissionData);
                    }

                }
                if($journey == 1){
                    $permissionData = [];
                    $menuIds = [4];
                    foreach ($menuIds as $menuId) {
                        $menuDetails = $this->menuModel
                            ->where('id', $menuId)
                            ->first();

                        if ($menuDetails) { // Ensure the menu exists
                            $permissionData[] = array(
                                "role_id" => $userDetails->role_id,
                                "user_id" => $userId,
                                "menu_id" => $menuId,
                                "menu_title" => $menuDetails->menu_title,
                                "create" => 1,
                                "read" => 1,
                                "edit" => 1,
                            );
                        }
                    }

                    if (!empty($permissionData)) {
                        $this->permissionModel->insertBatch($permissionData);
                    }

                }
                if($pretrip == 1){
                    $permissionData = [];
                    $menuIds = [130];
                    foreach ($menuIds as $menuId) {
                        $menuDetails = $this->menuModel
                            ->where('id', $menuId)
                            ->first();

                        if ($menuDetails) { // Ensure the menu exists
                            $permissionData[] = array(
                                "role_id" => $userDetails->role_id,
                                "user_id" => $userId,
                                "menu_id" => $menuId,
                                "menu_title" => $menuDetails->menu_title,
                                "create" => 1,
                                "read" => 1,
                                "edit" => 1,
                            );
                        }
                    }

                    if (!empty($permissionData)) {
                        $this->permissionModel->insertBatch($permissionData);
                    }

                }


                // completed database query transaction
                $this->db->transComplete();
                return redirect()->route('index-agent')->with("success", "Data Save");
            }
        }

        return redirect()->back()->with('fail', $this->validation->listErrors());
    }

    public function agentCommissionDetails($agentid)
    {
        $passangerinfo = $this->userDetailModel->findAll();

        $commission = $this->agentCommissionModel
            ->select('agentcommissions.*,agents.*,agentcommissions.id as commissionid,
                agentcommissions.user_id as commission_user_id,
                agentcommissions.commission as commissionamount,
                agents.id as agentid')
            ->join('agents', 'agents.id  = agentcommissions.agent_id')
            ->join('subtrips', 'subtrips.id   = agentcommissions.subtrip_id')
            ->where('agentcommissions.agent_id', $agentid)
            ->findAll();

        foreach ($commission as $key => $cvalue) {
            foreach ($passangerinfo as $nkey => $pvalue) {
                if ($pvalue->user_id == $cvalue->commission_user_id) {
                    $commission[$key]->commission_user_id = $pvalue->first_name . ' ' . $pvalue->last_name;
                }
            }

            $commission[$key]->first_name = $cvalue->first_name . ' ' . $cvalue->last_name;
        }

        $data['commission'] = $commission;
        $data['module'] = lang("Localize.agent");
        $data['title']  = lang("Localize.transaction_list");

        return view($this->Viewpath . '\commission\index', $data);
    }

    public function agentTransactionDetails($agentid)
    {
        $fromDate = date('Y-m-01');
        $toDate = date('Y-m-d');
        $data['transaction'] = $this->agentTotal->where('DATE(created_at) >=', $fromDate)->where('DATE(created_at) <=', $toDate)->where('agent_id', $agentid)->findAll();
        $data['filepath'] = $this->Viewpath;
        $data['agentId'] = $agentid;

        $data['agentdetail'] =  $this->agentModel->select('users.*,agents.*,users.id as userid,agents.id as agentid')->join('users', 'users.id = agents.user_id')->where('users.role_id', 2)->where('agents.id', $agentid)->first();
        $heading = lang("Localize.agent") . ' ' . lang("Localize.transaction_list");
        $data['pageheading'] = $heading;
        $data['module'] =    lang("Localize.agent");
        $data['title']  =    lang("Localize.transaction_list");
        $data['currency_symbol']  =    $this->session->get('currency_symbol');

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

    public function agentTranDateRange()
    {
        $fromDate = $this->request->getVar('start_date');
        $toDate = $this->request->getVar('end_date');
        $agetnID = $this->request->getVar('agetnID');
        $data['transaction'] = $this->agentTotal->where('DATE(created_at) >=', $fromDate)->where('DATE(created_at) <=', $toDate)->where('agent_id', $agetnID)->findAll();
        $data['agentId'] = $agetnID;
        $data['filepath'] = $this->Viewpath;

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

        $data['agentdetail'] =  $this->agentModel->select('users.*,agents.*,users.id as userid,agents.id as agentid')->join('users', 'users.id = agents.user_id')->where('users.role_id', 2)->where('agents.id', $agetnID)->first();


        $data['module'] =    lang("Localize.agent");
        $data['title']  =    lang("Localize.transaction_list");

        $data['currency_symbol']  =    $this->session->get('currency_symbol');

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


    public function imgaeCheck($image, $path)
    {
        $newName = $image->getRandomName();
        $path = $path;
        $image->move($path, $newName);
        return $path . '/' . $newName;
    }

    public function status($useragentId)
    {
        $userStatus = $this->userModel->find($useragentId);

        if ($userStatus->status == 1) {
            $upData = array(
                "id" => $useragentId,
                "status" => 0
            );
            $this->userModel->save($upData);
        }
        if ($userStatus->status == 0) {
            $upData = array(
                "id" => $useragentId,
                "status" => 1
            );
            $this->userModel->save($upData);
        }
        return redirect()->route('index-agent')->with("success", "Data Update");
    }

    public function delete(int $agentId)
    {
        $agentInfo = $this->agentModel->find($agentId);
        $agentUserId = $agentInfo->user_id;
        $agentLastName = $agentInfo->last_name;

        try {
            $this->db->transStart();
            $this->agentModel->delete($agentId);
            $this->userModel->delete($agentUserId);
            $this->db->transComplete();
        } catch (\Throwable $e) {
            return redirect()->back()->with('fail', $e->getMessage());
        }

        return redirect()->back()->with('fail', "Agent: {$agentLastName} deleted");
    }

    public function restore(int $agentId)
    {
        $agentInfo = $this->agentModel->withDeleted()->find($agentId);
        $agentUserId = $agentInfo->user_id;
        $agentLastName = $agentInfo->last_name;

        try {
            $this->db->transStart();
            $this->agentModel->set('deleted_at', null)->update($agentId);
            $this->userModel->set('deleted_at', null)->update($agentUserId);
            $this->db->transComplete();
        } catch (\Throwable $e) {
            return redirect()->back()->with('fail', $e->getMessage());
        }

        return redirect()->route('trash-index-agent')->with('success', "Agent: {$agentLastName} restored");
    }
    
    public function updatePaymentByAdmin()
    {
        $db = \Config\Database::connect();
        $hdnid = $this->request->getVar('hdnid');
        // print_r($this->request->getVar());die;
        $addamt = $this->request->getVar('recgAmt');
        
        $agentinfo = $this->agentModel->find($hdnid);
        $currentamt = $agentinfo->wallet;
        
        $newamt = $addamt+$currentamt;
        
        // print_r($newamt);die;
        
        $agentData['wallet'] = $newamt;
        $sql = "UPDATE agents SET wallet = ? WHERE id = ?";

            // Execute the query
            $result = $db->query($sql, [$newamt, $hdnid]);
            
        $systemuserid = $agentinfo->user_id;
        // print_r($systemuserid);die;
        
        
        $sqlagents = "INSERT INTO accounts (booking_id, transaction_type, type, detail, amount, system_user_id) VALUES (NULL, ?, ?, ?, ?, ?)";
        $db->query($sqlagents, ['recharge', 'income', 'recharge by admin', $addamt, $systemuserid]);
        
        $sqlagentstotal = "INSERT INTO agenttotals (agent_id, booking_id, income, expense, detail) VALUES (?, ?, ?, ?, ?)";
        $db->query($sqlagentstotal, [$hdnid, 'recharge', $addamt, 0, 'recharge by admin']);
        
        $sqlpayagents = "INSERT INTO payagents (agent_id, amount, total_balance, status, approved_id, accepted_date, detail) VALUES (?, ?, ?, ?, ?, ?, ?)";
        $db->query($sqlpayagents, [$hdnid, $addamt, $newamt, 1, $systemuserid, date("Y-m-d H:i:s"), 'recharge by admin']);
        
        
        
          return redirect()->back();
    }
     public function serverList()
    {
        $request = service('request');

        // Role and company ID from session
        $role_id = session()->get('role_id');
        $company_id = session()->get('company_id');

        // Check permissions
        $rolepermissionLibrary = new Rolepermission();
        $edit_agent = $rolepermissionLibrary->edit("agent_list");
        $delete_agent = $rolepermissionLibrary->delete("agent_list");

        // DataTables request values
        $draw   = $request->getPost('draw');
        $start  = $request->getPost('start');
        $length = $request->getPost('length');
        $searchValue = $request->getPost('search')['value'] ?? '';

        // Column map - adjust based on role_id
        // For role_id == 1: #, Company, Name, Email, Mobile, Commission, Wallet, Status, Action
        // For role_id != 1: #, Name, Email, Mobile, Commission, Wallet, Status
        $columns = [];
        $colIndex = 0;
        
        $columns[$colIndex++] = 'users.id';  // index column
        
        if ($role_id == 1) {
            $columns[$colIndex++] = 'companies.name';  // company column
        }
        
        // For ordering, we'll use first_name as primary sort
        $columns[$colIndex++] = 'agents.first_name';  // name column (ordered by first_name)
        $columns[$colIndex++] = 'users.login_email';  // email
        $columns[$colIndex++] = 'users.login_mobile';  // mobile
        $columns[$colIndex++] = 'agents.commission';  // commission
        $columns[$colIndex++] = null;  // wallet (computed, not orderable)
        $columns[$colIndex++] = 'users.status';  // status
        
        if ($role_id == 1) {
            $columns[$colIndex++] = null;  // action column (not orderable/searchable)
        }

        // Order column & direction
        $order = $request->getPost('order');
        $orderColumnIndex = !empty($order) && isset($order[0]['column']) ? $order[0]['column'] : 0;
        $orderDirection   = !empty($order) && isset($order[0]['dir']) ? $order[0]['dir'] : 'asc';

        // Get company balances for all agents
        $companyBalancesQuery = $this->agentPayment
            ->select('agents.id as agent_id, companies.name as company_name, payagents.agent_id, payagents.company_id, SUM(payagents.amount) as total_amount')
            ->join('agents', 'agents.id = payagents.agent_id', 'left')
            ->join('companies', 'companies.id = payagents.company_id', 'left')
            ->where('payagents.company_id >', 0)
            ->where('payagents.status', 1)
            ->groupBy(['payagents.agent_id', 'payagents.company_id'])
            ->findAll();
        
        $agentcompanyBalance = [];
        foreach ($companyBalancesQuery as $agentBalance) {
            $agentId = $agentBalance->agent_id;
            $companyString = number_format(round((float)$agentBalance->total_amount, 2), 2) . " (" . $agentBalance->company_name . ")";
            $agentcompanyBalance[$agentId][] = $companyString;
        }

        // ---- TOTAL RECORDS ----
        $totalBuilder = $this->db->table('users');
        $totalBuilder->select('users.id')
            ->join('agents', 'agents.user_id = users.id', 'left')
            ->join('companies', 'FIND_IN_SET(users.id, companies.user_id)', 'left')
            ->where('users.role_id', 2)
            ->where('users.deleted_at IS NULL')
            ->where('agents.deleted_at IS NULL')
            ->groupBy('users.id');

        // Apply role-based filtering
        if ($role_id == 2) {
            // Agent can only see themselves
            $agentid = session()->get('user_id');
            $totalBuilder->where('agents.user_id', $agentid);
        } elseif (!in_array($role_id, [1, 2])) {
            // Company admin sees only their agents
            $totalBuilder->where('companies.id', $company_id);
        }

        $totalRecords = $totalBuilder->countAllResults(false);

        // Create fresh builder for filtered query
        $builder = $this->db->table('users');
        $builder->select('
                users.id AS user_id,
                users.login_email,
                users.login_mobile,
                users.status,
                users.deleted_at,
                agents.id AS aget_id,
                agents.first_name,
                agents.last_name,
                agents.commission,
                agents.wallet,
                GROUP_CONCAT(DISTINCT companies.name SEPARATOR ", ") AS company_names
            ')
            ->join('agents', 'agents.user_id = users.id', 'left')
            ->join('companies', 'FIND_IN_SET(users.id, companies.user_id)', 'left')
            ->where('users.role_id', 2)
            ->where('users.deleted_at IS NULL')
            ->where('agents.deleted_at IS NULL')
            ->groupBy('users.id');

        // Apply role-based filtering
        if ($role_id == 2) {
            $agentid = session()->get('user_id');
            $builder->where('agents.user_id', $agentid);
        } elseif (!in_array($role_id, [1, 2])) {
            $builder->where('companies.id', $company_id);
        }

        // Global search
        if (!empty($searchValue)) {
            $builder->groupStart()
                ->like('CONCAT(agents.first_name, " ", agents.last_name)', $searchValue)
                ->orLike('agents.first_name', $searchValue)
                ->orLike('agents.last_name', $searchValue)
                ->orLike('users.login_email', $searchValue)
                ->orLike('users.login_mobile', $searchValue)
                ->orLike('agents.commission', $searchValue)
                ->orLike('companies.name', $searchValue)
                ->groupEnd();
        }

        // Column-wise filters
        if (!empty($request->getPost('columns'))) {
            foreach ($request->getPost('columns') as $idx => $col) {
                $searchCol = $col['search']['value'] ?? '';
                if ($searchCol !== '' && isset($columns[$idx]) && $columns[$idx] !== null) {
                    $builder->like($columns[$idx], $searchCol);
                }
            }
        }

        // ---- TOTAL FILTERED ----
        $totalRecordwithFilter = $builder->countAllResults(false);

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

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

        // ---- FETCH RECORDS ----
        $records = $builder->get()->getResult();

        // ---- FORMAT JSON ----
        $data = [];
        $i = $start + 1;

        foreach ($records as $row) {
            // Format wallet with company balances
            $walletValue = isset($row->wallet) ? (float)$row->wallet : 0;
            $walletDisplay = number_format($walletValue, 2);
            if (isset($agentcompanyBalance[$row->aget_id]) && !empty($agentcompanyBalance[$row->aget_id])) {
                $walletDisplay .= " = " . implode(' + ', $agentcompanyBalance[$row->aget_id]);
            }

            // Format name with link
            $nameHtml = '<a href="' . base_url(route_to('transaction-agent', $row->aget_id)) . '">' . 
                       esc($row->first_name . ' ' . $row->last_name) . '</a>';

            // Format status
            $statusHtml = '';
            if (isset($row->status) && $row->status == 1) {
                $statusHtml = '<span class="badge bg-success">' . lang("Localize.active") . '</span>';
            } else {
                $statusHtml = '<span class="badge bg-secondary">' . lang("Localize.disable") . '</span>';
            }

            // Build data array - always start with index
            $rowData = [
                "index" => $i++
            ];

            // Add company column only for role_id == 1
            if ($role_id == 1) {
                $rowData["company"] = esc($row->company_names ?? '');
            }

            // Add common columns
            $rowData["name"] = $nameHtml;
            $rowData["email"] = esc($row->login_email ?? '');
            $rowData["mobile"] = esc($row->login_mobile ?? '');
            $rowData["commission"] = esc($row->commission ?? '');
            $rowData["wallet"] = $walletDisplay;
            $rowData["status"] = $statusHtml;

            // Add action column only for role_id == 1
            if ($role_id == 1) {
                $rowData["action"] = $this->generateActionButtons($row, $edit_agent, $delete_agent);
            }

            $data[] = $rowData;
        }

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

    private function generateActionButtons($row, $edit_agent, $delete_agent)
    {
        $security = \Config\Services::security();
        $csrfToken = $security->getTokenName();
        $csrfHash = $security->getHash();
        
        $actionHtml = '';
        
        if ($edit_agent && $row->deleted_at == null) {
            // Status toggle button
            $statusIcon = isset($row->status) && $row->status ? 'fa-user-slash' : 'fa-user-check';
            $actionHtml .= '<form action="' . base_url(route_to('status-agent', $row->user_id)) . '" class="d-inline-block" method="post">';
            $actionHtml .= '<input type="hidden" name="' . esc($csrfToken, 'attr') . '" value="' . esc($csrfHash, 'attr') . '">';
            $actionHtml .= '<input type="hidden" name="_method" value="PUT">';
            $actionHtml .= '<button type="submit" class="btn btn-sm btn-secondary text-white" title="' . lang("Localize.status") . '">';
            $actionHtml .= '<i class="fas ' . esc($statusIcon, 'attr') . '"></i>';
            $actionHtml .= '</button>';
            $actionHtml .= '</form> ';

            // Edit button
            $actionHtml .= '<a href="' . base_url(route_to('edit-agent', $row->aget_id)) . '" class="btn btn-sm btn-info text-white" title="' . lang("Localize.edit") . '">';
            $actionHtml .= '<i class="fas fa-edit"></i>';
            $actionHtml .= '</a> ';
        }
        
        if ($delete_agent) {
            if ($row->deleted_at == null) {
                $actionHtml .= '<form action="' . base_url(route_to('ss-delete-confirmation', 'agent', $row->aget_id)) . '" class="d-inline-block deletionForm" method="get">';
                $actionHtml .= '<button type="button" data-modal-confirm="true" class="btn btn-sm btn-danger text-white" title="' . lang("Localize.delete") . '">';
                $actionHtml .= '<i class="fas fa-trash"></i>';
                $actionHtml .= '</button>';
                $actionHtml .= '</form>';
            } else {
                $actionHtml .= '<a href="' . base_url(route_to('restore-agent', $row->aget_id)) . '" class="btn btn-sm btn-success text-white" title="' . lang("Localize.restore") . '">';
                $actionHtml .= '<i class="fas fa-undo-alt"></i> ' . lang("Localize.restore");
                $actionHtml .= '</a>';
            }
        }
        
        return $actionHtml;
    }
}
