<?php

namespace Modules\Ticket\Controllers;

use App\Controllers\BaseController;

use Modules\Ticket\Models\CancelModel;
use Modules\Ticket\Models\TicketModel;
use Modules\Paymethod\Models\PaymethodModel;
use Modules\Trip\Models\PickdropModel;

use Modules\Agent\Models\AgentModel;
use Modules\Agent\Models\Agentcommission;
use Modules\Agent\Models\AgenttotalModel;
use Modules\Ticket\Models\JourneylistModel;
use App\Libraries\Rolepermission;
use App\Libraries\TicketCancellationSupport;

class Cancel extends BaseController
{
	protected $Viewpath;
	protected $cancelModel;
	protected $ticketpayModel;
	protected $paymethodModel;
	protected $db;
	protected $agentModel;
    protected $agentCommissionModel;
   

    protected $agetTotalModel;
	
	protected $journeylistModel;
	protected $picdropModel;
	
	public function __construct()
    {

        $this->Viewpath = "Modules\Ticket\Views";
		$this->cancelModel = new CancelModel();
		$this->ticketpayModel = new TicketModel();
		$this->paymethodModel = new PaymethodModel();

		$this->agentModel = new AgentModel();
        $this->agentCommissionModel = new Agentcommission();
		$this->db = \Config\Database::connect();

		$this->agetTotalModel = new AgenttotalModel();

		$this->journeylistModel = new JourneylistModel();
		$this->picdropModel = new PickdropModel();
		
    }

	protected function isSuperAdmin(): bool
	{
		return (int) session()->get('role_id') === 1;
	}

	protected function canReadManageOnlineCancellations(): bool
	{
		if ($this->isSuperAdmin()) {
			return true;
		}

		return (new Rolepermission())->read('manage_online_cancellations');
	}

	protected function canEditManageOnlineCancellations(): bool
	{
		if ($this->isSuperAdmin()) {
			return true;
		}

		return (new Rolepermission())->edit('manage_online_cancellations');
	}

	/** Cancel online (passenger) tickets from the ticket list — not the online cancel list. */
	protected function canCancelOnlinePassengerTicket(): bool
	{
		if ($this->isSuperAdmin()) {
			return true;
		}

		return (new Rolepermission())->create('cancel_online_ticket');
	}

	/** Cancel agent/office tickets (non-passenger). */
	protected function canCancelOfficeTicket(): bool
	{
		if ($this->isSuperAdmin()) {
			return true;
		}

		return (new Rolepermission())->create('cancel_list');
	}

	function get_time_difference_in_hours($journeyDate, $journeyTime)
    {
        // Combine journey date and time into one string
        $journeyDateTime = $journeyDate . ' ' . $journeyTime;

        // Convert the journey date and time to a DateTime object (in proper format)
        try {
            $journeyDateTimeObject = new \DateTime($journeyDateTime); // Make sure it handles both date and time
        } catch (\Exception $e) {
            return "Invalid date/time format.";
        }

        // Get the current date and time
        $currentDateTime = get_egypt_time('now');

        // Calculate the difference: journey time - current time
        // If journey is in the future, $interval->invert will be 0
        $interval = $currentDateTime->diff($journeyDateTimeObject);

        // Calculate the total hours difference, including full days converted to hours
        $hoursDifference = $interval->h + ($interval->days * 24);

        // If the journey time is in the future (invert == 0)
        if ($interval->invert == 0) {
            // Add minutes as a fraction of an hour
            $hoursDifference += $interval->i / 60; 
            // Round up to ensure we are covering the full time until departure
            $totalHours = ceil($hoursDifference);
            return $totalHours;
        } else {
            // If journey is in the past (negative difference or invert == 1)
            return -round($hoursDifference);
        }
    }

	public function new($ticketid,$type)
	{
		if ($type == "ticket" ) {
			
			$data['track_table'] = $this->ticketpayModel
			->select('tickets.*, trips.company_id, schedules.start_time AS journeytimes, journeylists.journeydate') // Adjust columns to select
			->join('trips', 'trips.id = tickets.trip_id', 'left') // Define your LEFT JOIN
			->join("subtrips", "subtrips.trip_id = trips.id", "left")
			->join('schedules', 'schedules.id = trips.schedule_id', 'left') // Define your LEFT JOIN
			->join('journeylists', 'journeylists.booking_id = tickets.booking_id', 'left') // Define your LEFT JOIN
			->where('tickets.id', $ticketid)
			->first();
			if(!empty($data['track_table'])){
	            $pickdrop = $this->picdropModel->select('pickdrops.id as pickdropid,pickdrops.*,stands.*')
                ->join('stands', 'stands.id = pickdrops.stand_id')
                ->where('trip_id', $data['track_table']->subtrip_id)
                ->where('type' , 1)
                ->first();
	            if(empty($pickdrop)){
	                $pickdrop = $this->picdropModel->select('pickdrops.id as pickdropid,pickdrops.*,stands.*')
	                    ->join('stands', 'stands.id = pickdrops.stand_id')
	                    ->where('trip_id', $data['track_table']->trip_id)
	                    ->where('type' , 1)
	                    ->first();
	            }
	         $data['track_table']->journeytime = $pickdrop->time??$data['track_table']->journeytimes;
			}
			$track_table = $data['track_table'];
			//$data['track_table'] = $this->ticketpayModel->find($ticketid);
		}

		if ($type === 'ticket' && ! empty($track_table)) {
			$isOnlinePassenger = ((string) ($track_table->bookby_user_type ?? '')) === 'passanger';
			if ($isOnlinePassenger) {
				if (! $this->canCancelOnlinePassengerTicket()) {
					return redirect()->route('allbookinglist-ticket')->with('fail', lang('Localize.no_permission_cancel_online_ticket'));
				}
			} elseif (! $this->canCancelOfficeTicket()) {
				return redirect()->route('allbookinglist-ticket')->with('fail', lang('Localize.invalid_request'));
			}
		}

		$amounts = TicketCancellationSupport::computeCancellationAmounts($track_table);
		if (! empty($amounts['journey_passed'])) {
			return redirect()->back()->with('fail', lang('Localize.journey_time_passed_cancel'));
		}
		$data['cancel_charge'] = $amounts['cancel_charge'];
		$data['refunded_amount'] = $amounts['refunded_amount'];
		$data['total_amount'] = $amounts['total_amount'];

		


		$data['type'] = $type;
		$data['paymethod'] = $this->paymethodModel->findAll();
		
		$data['module'] =    lang("Localize.ticket_booking") ; 
		$data['title']  =    lang("Localize.cancel_list") ;

		$heading = lang("Localize.ticket").' '.lang("Localize.cancel");
		$data['pageheading'] = $heading;

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

	public function create()
	{
        $tableTargetDetail ="";

        $currentuserid = $this->session->get('user_id');
        $type = $this->request->getVar('type');
        $trackTableId = $this->request->getVar('track_table_id');
        $booking_id = $this->request->getVar('booking_id');
        $paymathodid = $this->request->getVar('pay_type_id');
        $paydetail = $this->request->getVar('detail');

        $validCancel = array(
            "booking_id" => $booking_id,
            "type" => $type,
            "cancel_by" => $currentuserid,
            
          );

		$track = null;
		if ($type === 'ticket') {
			$track = TicketCancellationSupport::loadTrackRowForCancellation((int) $trackTableId);
		}
		if ($type === 'ticket' && $track === null) {
			return redirect()->route('ticketindex-cancel')->with('fail', lang('Localize.invalid_request'));
		}

		$isPassengerOnline = $track && (($track->bookby_user_type ?? '') === 'passanger');

		if ($type === 'ticket') {
			if ($isPassengerOnline) {
				if (! $this->canCancelOnlinePassengerTicket()) {
					return redirect()->back()->with('fail', lang('Localize.no_permission_cancel_online_ticket'));
				}
			} elseif (! $this->canCancelOfficeTicket()) {
				return redirect()->back()->with('fail', lang('Localize.invalid_request'));
			}
		}

		$amounts = $track ? TicketCancellationSupport::computeCancellationAmounts($track) : [
			'cancel_charge' => 0,
			'refunded_amount' => 0,
			'total_amount' => 0,
		];
		$cancel_fee = $amounts['cancel_charge'];
		$refundAmt = (float) ($amounts['refunded_amount'] ?? 0);
		$onlineStatus = 'done';
		if ($isPassengerOnline) {
			$onlineStatus = $refundAmt > 0 ? 'pending' : 'done';
		}

        $cancel = array(
            "booking_id" => $booking_id,
            "cancel_fee" => $cancel_fee,
            "track_table_id" => $trackTableId,
            "type" => $type,
            "detail" => $paydetail,
            "pay_type_id" => $paymathodid,
            "cancel_by" => $currentuserid,
			"ticket_amount" => $amounts['total_amount'],
			"refund_amount" => $amounts['refunded_amount'],
			"status" => $isPassengerOnline ? $onlineStatus : 'done',
         );

		 if ($this->validation->run($validCancel, 'cancel')) {


			$this->db->transStart();
			
			$this->cancelModel->insert($cancel);

			if ($type == "ticket") {

				$data = [
					'id' => $trackTableId,
					'cancel_status' => 1,
				];
				
				$this->ticketpayModel->save($data);

				$tableTargetDetail = $this->ticketpayModel->where('booking_id',$booking_id)->first();
				
				// Clear any existing seat "holds" for this ticket from `temporarybooks`.
				// Website seat availability also marks seats as unavailable when they still exist in
				// `temporarybooks`, even if tickets.cancel_status is already 1.
				if (!empty($tableTargetDetail)) {
					$subtripid = (int) ($tableTargetDetail->subtrip_id ?? 0);
					$journeydata = $tableTargetDetail->journeydata ?? null;
					$seatStr = (string) ($tableTargetDetail->seatnumber ?? '');

					$seatNumbers = array_values(array_filter(array_map(function ($s) {
						return (int) trim($s);
					}, explode(',', $seatStr)), function ($n) {
						return $n > 0;
					}));

					if ($subtripid > 0 && !empty($journeydata) && !empty($seatNumbers)) {
						$journeyDate = date('Y-m-d', strtotime((string) $journeydata));

						$orClauses = [];
						$params = [$subtripid, $journeyDate];
						foreach ($seatNumbers as $sn) {
							$orClauses[] = "seat_names REGEXP ?";
							$params[] = '(^|,)' . $sn . '(,|$)';
						}

						$sql = "DELETE FROM temporarybooks
								WHERE subtrip_id = ?
								  AND DATE(journey_date) = DATE(?)
								  AND (" . implode(' OR ', $orClauses) . ")";

						$this->db->query($sql, $params);
					}
				}
				
			}
			
			else {
				// $tableTargetDetail = ""
			}


			$userRole = $this->session->get('role_id');

			// Credit agent wallet when the booking user is an agent (bookby_user_type stores role name, e.g. balance — not literal "agent").
			if (! $isPassengerOnline && ! empty($tableTargetDetail)) {
				$bookingAgent = $this->agentModel->where('user_id', (int) $tableTargetDetail->bookby_user_id)->first();
				if ($bookingAgent) {
					$company_id = (int) ($track->company_id ?? 0);
					$detail = 'Fee (' . $booking_id . ')';
					if (! empty($cancel_fee)) {
						$detail .= ' Cancel fee amount: ' . $cancel_fee;
					}
					agentRefundTicket($tableTargetDetail->bookby_user_id, $refundAmt, $booking_id, $detail, $company_id);
				}
			}



			if (! $isPassengerOnline && !empty($cancel_fee)) {
				
				$intype = "income";
                $detail = "Cancel (".$booking_id.") ";
				$amount = $cancel_fee;
                accoutTranjection($intype, $detail, $amount, $currentuserid, $booking_id, "booking");

				paymethodTeanjection($booking_id,$paymathodid,$cancel_fee,$paydetail,$tableTargetDetail->trip_id,$tableTargetDetail->subtrip_id,$currentuserid);
			
				}

				$this->journeylistModel->where('booking_id', $booking_id)->delete();

			$this->db->transComplete();

			return redirect()->route('ticketindex-cancel')->with("success", "Refund Successful");
			 
		 } 
		 else
		  {
			if ($type == "ticket" ) {
				$data['track_table'] = $this->ticketpayModel->find($trackTableId);
			}

			$data['type'] = $type;
			$data['paymethod'] = $this->paymethodModel->findAll();
			$data['validation'] = $this->validation;
			
			$data['module'] =    lang("Localize.ticket_booking") ; 
			$data['title']  =    lang("Localize.cancel_list") ;

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


	public function indexTicket()
	{
		$roleId     = session()->get("role_id");
		$companyId  = session()->get("company_id");
		if($roleId == 1){
			$data['cancel'] = $this->cancelModel
				->select('cancels.*')
				->join('tickets', 'tickets.id = cancels.track_table_id', 'left')
				->where('type', 'ticket')
				->groupStart()
					->where('tickets.id IS NULL')
					->orWhere('tickets.bookby_user_type !=', 'passanger')
				->groupEnd()
				->orderBy('cancels.id', 'DESC')
				->withDeleted()
				->findAll();
		}else{
			$userRole = $this->session->get('role_id');
        	$userId = $this->session->get('user_id');
			$this->cancelModel
				->select('cancels.*')
				->join('tickets', 'tickets.id = cancels.track_table_id', 'left')
		        ->join('trips', 'trips.id = tickets.trip_id', 'left')
	            // ->where('trips.company_id', $companyId)
				->where('type',"ticket");
        	if (!in_array($userRole,[1,2,7])) {
        		$rolepermissionLibrary = new Rolepermission();
	            if (!$rolepermissionLibrary->create('all_tickets')) {
	                $this->cancelModel->where('cancel_by', $userId);
	            }else{
	                $this->cancelModel->where('trips.company_id', $companyId);
	            }
	        	
        	}else{
	            if(in_array($userRole,[7])){
	            	$this->cancelModel->where('trips.company_id', $companyId);	
	            }if(in_array($userRole,[2])){
					$this->cancelModel->whereIn('trips.company_id', explode(",", session()->get('company_id')))->where('cancel_by', $userId);
	            }
	        }
			$this->cancelModel
				->groupStart()
					->where('tickets.id IS NULL')
					->orWhere('tickets.bookby_user_type !=', 'passanger')
				->groupEnd();
			$data['cancel'] = 	$this->cancelModel->orderBy('id', 'DESC')->withDeleted()->findAll();
		}
		
		$data['module'] =    lang("Localize.ticket_booking") ; 
		$data['title']  =    lang("Localize.cancel_list") ;
		$heading = lang("Localize.ticket").' '.lang("Localize.cancel_list");
		$data['pageheading'] = $heading;
		
		echo view($this->Viewpath.'\cancel\index',$data);
	}
		public function serverListCancel()
	{
		$request = service('request');

		// Role and company ID from session
		$roleId = session()->get('role_id');
		$companyId = session()->get('company_id');
		$userId = session()->get('user_id');
		$rolepermissionLibrary = new Rolepermission();

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

		// Column map
		$columns = [
			0 => null, // index
			1 => 'cancels.booking_id', // booking_id
			2 => 'cancels.cancel_fee', // amount
			3 => 'cancels.type', // type
			4 => 'cancels.detail', // payment details
			5 => 'cancels.created_at' // date
		];

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

		// ---- TOTAL RECORDS ----
		$countBuilder = $this->db->table('cancels');
		$countBuilder->select('cancels.id')
			->where('cancels.type', 'ticket');

		// Apply role-based filtering for total count
		if ($roleId == 1) {
			$countBuilder->join('tickets', 'tickets.id = cancels.track_table_id', 'left');
		} else {
			$countBuilder->join('tickets', 'tickets.id = cancels.track_table_id', 'left')
				->join('trips', 'trips.id = tickets.trip_id', 'left');

			if (!in_array($roleId, [1, 2, 7])) {
				if (!$rolepermissionLibrary->create('all_tickets')) {
					$countBuilder->where('cancels.cancel_by', $userId);
				} else {
					$countBuilder->where('trips.company_id', $companyId);
				}
			} else {
				if (in_array($roleId, [7])) {
					$countBuilder->where('trips.company_id', $companyId);
				}
				if (in_array($roleId, [2])) {
					$countBuilder->whereIn('trips.company_id', explode(",", $companyId))
						->where('cancels.cancel_by', $userId);
				}
			}
		}
		// Online (website) cancellations: only on /cancels/online — hide from this admin list
		$countBuilder->groupStart()
			->where('tickets.id IS NULL')
			->orWhere('tickets.bookby_user_type !=', 'passanger')
			->groupEnd();

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

		// ---- FILTERED QUERY ----
		$builder = $this->db->table('cancels');
		$builder->select('cancels.id, cancels.booking_id, cancels.cancel_fee, cancels.type, cancels.detail, cancels.created_at')
			->where('cancels.type', 'ticket');

		// Apply role-based filtering
		if ($roleId == 1) {
			$builder->join('tickets', 'tickets.id = cancels.track_table_id', 'left');
		} else {
			$builder->join('tickets', 'tickets.id = cancels.track_table_id', 'left')
				->join('trips', 'trips.id = tickets.trip_id', 'left');

			if (!in_array($roleId, [1, 2, 7])) {
				if (!$rolepermissionLibrary->create('all_tickets')) {
					$builder->where('cancels.cancel_by', $userId);
				} else {
					$builder->where('trips.company_id', $companyId);
				}
			} else {
				if (in_array($roleId, [7])) {
					$builder->where('trips.company_id', $companyId);
				}
				if (in_array($roleId, [2])) {
					$builder->whereIn('trips.company_id', explode(",", $companyId))
						->where('cancels.cancel_by', $userId);
				}
			}
		}
		$builder->groupStart()
			->where('tickets.id IS NULL')
			->orWhere('tickets.bookby_user_type !=', 'passanger')
			->groupEnd();

		// Global search
		if (!empty($searchValue)) {
			$builder->groupStart()
				->like('cancels.booking_id', $searchValue)
				->orLike('cancels.cancel_fee', $searchValue)
				->orLike('cancels.type', $searchValue)
				->orLike('cancels.detail', $searchValue)
				->orLike('cancels.created_at', $searchValue)
				->groupEnd();
		}

		// Create a separate builder for filtered count
		$filteredCountBuilder = $this->db->table('cancels');
		$filteredCountBuilder->select('cancels.id')
			->where('cancels.type', 'ticket');

		// Apply role-based filtering for filtered count
		if ($roleId == 1) {
			$filteredCountBuilder->join('tickets', 'tickets.id = cancels.track_table_id', 'left');
		} else {
			$filteredCountBuilder->join('tickets', 'tickets.id = cancels.track_table_id', 'left')
				->join('trips', 'trips.id = tickets.trip_id', 'left');

			if (!in_array($roleId, [1, 2, 7])) {
				if (!$rolepermissionLibrary->create('all_tickets')) {
					$filteredCountBuilder->where('cancels.cancel_by', $userId);
				} else {
					$filteredCountBuilder->where('trips.company_id', $companyId);
				}
			} else {
				if (in_array($roleId, [7])) {
					$filteredCountBuilder->where('trips.company_id', $companyId);
				}
				if (in_array($roleId, [2])) {
					$filteredCountBuilder->whereIn('trips.company_id', explode(",", $companyId))
						->where('cancels.cancel_by', $userId);
				}
			}
		}
		$filteredCountBuilder->groupStart()
			->where('tickets.id IS NULL')
			->orWhere('tickets.bookby_user_type !=', 'passanger')
			->groupEnd();

		// Apply search filter for filtered count
		if (!empty($searchValue)) {
			$filteredCountBuilder->groupStart()
				->like('cancels.booking_id', $searchValue)
				->orLike('cancels.cancel_fee', $searchValue)
				->orLike('cancels.type', $searchValue)
				->orLike('cancels.detail', $searchValue)
				->orLike('cancels.created_at', $searchValue)
				->groupEnd();
		}

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

		// ---- ORDERING ----
		if (isset($columns[$orderColumnIndex]) && $columns[$orderColumnIndex] !== null) {
			$builder->orderBy($columns[$orderColumnIndex], $orderDirection);
		} else {
			$builder->orderBy('cancels.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) {
			$data[] = [
				"index" => $i++,
				"booking_id" => esc($row->booking_id ?? ''),
				"cancel_fee" => esc($row->cancel_fee ?? ''),
				"type" => esc($row->type ?? ''),
				"detail" => esc($row->detail ?? ''),
				"created_at" => esc($row->created_at ?? '')
			];
		}

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

	public function indexOnlineCancellations()
	{
		if (! $this->canReadManageOnlineCancellations()) {
			return redirect()->route('admin-home')->with('fail', lang('Localize.no_permission_online_cancellations'));
		}

		$data['module'] = lang('Localize.ticket_booking');
		$data['title'] = lang('Localize.online_cancellation_list');
		$data['pageheading'] = lang('Localize.online_cancellation_list');
		$data['menuname'] = 'online-cancellations';

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

	public function serverListOnlineCancellations()
	{
		if (! $this->canReadManageOnlineCancellations()) {
			return $this->response->setJSON(['draw' => 0, 'recordsTotal' => 0, 'recordsFiltered' => 0, 'data' => []]);
		}

		$request = service('request');
		$roleId = (int) session()->get('role_id');
		$companyId = session()->get('company_id');

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

		$columns = [
			0 => null,
			1 => 'cancels.booking_id',
			2 => 'puser.login_mobile',
			3 => null,
			4 => 'cancels.ticket_amount',
			5 => 'cancels.cancel_fee',
			6 => 'cancels.refund_amount',
			7 => 'cancels.detail',
			8 => 'tickets.journeydata',
			9 => 'cancels.created_at',
			10 => 'user_details.first_name',
			11 => 'sentud.first_name',
			12 => 'cancels.refund_sent_at',
			13 => 'cancels.status',
			14 => null,
			15 => null,
		];

		$order = $request->getPost('order');
		$orderColumnIndex = ! empty($order) && isset($order[0]['column']) ? (int) $order[0]['column'] : 9;
		$orderDirection = ! empty($order) && isset($order[0]['dir']) ? $order[0]['dir'] : 'desc';

		$baseJoin = function ($b) use ($roleId, $companyId) {
			$b->join('tickets', 'tickets.id = cancels.track_table_id', 'inner')
				->join('trips', 'trips.id = tickets.trip_id', 'left')
				->join('users', 'users.id = cancels.cancel_by', 'left')
				->join('user_details', 'user_details.user_id = users.id', 'left')
				->join('users puser', 'puser.id = tickets.passanger_id', 'left')
				->join('users sentu', 'sentu.id = cancels.refund_sent_by', 'left')
				->join('user_details sentud', 'sentud.user_id = sentu.id', 'left')
				->where('cancels.type', 'ticket')
				->where('tickets.bookby_user_type', 'passanger');
			if ($roleId === 7) {
				$b->where('trips.company_id', $companyId);
			}
		};

		$countBuilder = $this->db->table('cancels');
		$countBuilder->select('cancels.id');
		$baseJoin($countBuilder);
		$totalRecords = $countBuilder->countAllResults(false);

		$builder = $this->db->table('cancels');
		$builder->select('cancels.id, cancels.booking_id, cancels.ticket_amount, cancels.cancel_fee, cancels.refund_amount, cancels.detail, cancels.created_at, cancels.status')
			->select('cancels.refund_sent_at, cancels.refund_note')
			->select('CONCAT(COALESCE(user_details.first_name, ""), " ", COALESCE(user_details.last_name, "")) AS cancelled_by_name', false)
			->select('CONCAT(COALESCE(sentud.first_name, ""), " ", COALESCE(sentud.last_name, "")) AS refund_sent_by_name', false)
			->select('puser.login_mobile AS customer_mobile', false)
			->select('tickets.journeydata', false)
			->select('(SELECT pg.order_id FROM paystatusgeidea pg WHERE pg.ticket_id = tickets.id ORDER BY pg.id DESC LIMIT 1) AS geidea_order_id', false);
		$baseJoin($builder);

		if ($searchValue !== '') {
			$builder->groupStart()
				->like('cancels.booking_id', $searchValue)
				->orLike('cancels.detail', $searchValue)
				->orLike('cancels.status', $searchValue)
				->orLike('user_details.first_name', $searchValue)
				->orLike('user_details.last_name', $searchValue)
				->orLike('puser.login_mobile', $searchValue)
				->orLike('sentud.first_name', $searchValue)
				->orLike('sentud.last_name', $searchValue)
				->orLike('cancels.refund_note', $searchValue)
				->groupEnd();
		}

		$filteredCountBuilder = $this->db->table('cancels');
		$filteredCountBuilder->select('cancels.id');
		$baseJoin($filteredCountBuilder);
		if ($searchValue !== '') {
			$filteredCountBuilder->groupStart()
				->like('cancels.booking_id', $searchValue)
				->orLike('cancels.detail', $searchValue)
				->orLike('cancels.status', $searchValue)
				->orLike('user_details.first_name', $searchValue)
				->orLike('user_details.last_name', $searchValue)
				->orLike('puser.login_mobile', $searchValue)
				->orLike('sentud.first_name', $searchValue)
				->orLike('sentud.last_name', $searchValue)
				->orLike('cancels.refund_note', $searchValue)
				->groupEnd();
		}
		$totalRecordwithFilter = $filteredCountBuilder->countAllResults(false);

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

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

		$records = $builder->get()->getResult();
		$canMarkDone = $this->canEditManageOnlineCancellations();

		$data = [];
		$i = (int) $start + 1;
		foreach ($records as $row) {
			$refundAmount = (float) ($row->refund_amount ?? 0);
			$status = (string) ($row->status ?? '');
			$sentAt = $row->refund_sent_at ?? null;
			$moneySentRecorded = $sentAt !== null && $sentAt !== '';
			// Only Pending vs Done: no refund to pay, or money already recorded → Done.
			$statusLabel = ($refundAmount <= 0 || $moneySentRecorded)
				? lang('Localize.refund_status_done')
				: lang('Localize.refund_status_pending');
			$needsMoneySend = $refundAmount > 0 && ! $moneySentRecorded
				&& in_array($status, ['pending', 'done', 'complete'], true);
			$action = '';
			if ($canMarkDone && $needsMoneySend) {
				$action = '<button type="button" class="btn btn-sm btn-success js-money-sent" data-id="' . (int) $row->id . '">' . esc(lang('Localize.money_sent')) . '</button>';
			} elseif ($refundAmount > 0 && $moneySentRecorded) {
				$action = '<button type="button" class="btn btn-sm btn-secondary" disabled>' . esc(lang('Localize.money_sent')) . '</button>';
			} else {
				$action = '—';
			}
			$journeyRaw = $row->journeydata ?? '';
			$journeyFmt = $journeyRaw !== '' ? date('d/m/Y H:i', strtotime((string) $journeyRaw)) : '';
			$cancelFmt = ! empty($row->created_at) ? date('d/m/Y H:i', strtotime((string) $row->created_at)) : '';
			$sentAtFmt = ! empty($row->refund_sent_at) ? date('d/m/Y H:i', strtotime((string) $row->refund_sent_at)) : '';
			$data[] = [
				'index' => $i++,
				'booking_id' => esc($row->booking_id ?? ''),
				'customer_mobile' => esc($row->customer_mobile ?? ''),
				'geidea_order_id' => esc($row->geidea_order_id ?? ''),
				'ticket_amount' => esc($row->ticket_amount ?? ''),
				'cancel_fee' => esc($row->cancel_fee ?? ''),
				'refund_amount' => esc($row->refund_amount ?? ''),
				'detail' => esc($row->detail ?? ''),
				'journey_date' => esc($journeyFmt),
				'cancelled_at' => esc($cancelFmt),
				'cancelled_by' => esc(trim($row->cancelled_by_name ?? '')),
				'refund_sent_by' => esc(trim($row->refund_sent_by_name ?? '')),
				'refund_sent_at' => esc($sentAtFmt),
				'status' => esc($statusLabel),
				'refund_note' => esc($row->refund_note ?? ''),
				'action' => $action,
			];
		}

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

	public function markOnlineRefundDone()
	{
		if (! $this->canEditManageOnlineCancellations()) {
			return redirect()->route('index-online-cancel')->with('fail', lang('Localize.no_permission_online_cancellations'));
		}

		$id = (int) $this->request->getPost('id');
		if ($id < 1) {
			return redirect()->route('index-online-cancel')->with('fail', lang('Localize.invalid_request'));
		}

		$row = $this->db->table('cancels')
			->select('cancels.*')
			->join('tickets', 'tickets.id = cancels.track_table_id', 'inner')
			->where('cancels.id', $id)
			->where('tickets.bookby_user_type', 'passanger')
			->where('cancels.type', 'ticket')
			->get()
			->getRow();

		if (empty($row)) {
			return redirect()->route('index-online-cancel')->with('fail', lang('Localize.invalid_request'));
		}
		$rowStatus = (string) ($row->status ?? '');
		$alreadySent = ! empty($row->refund_sent_at);
		if ($alreadySent || ! in_array($rowStatus, ['pending', 'done', 'complete'], true)) {
			return redirect()->route('index-online-cancel')->with('fail', lang('Localize.invalid_request'));
		}

		if ((float) ($row->refund_amount ?? 0) <= 0) {
			return redirect()->route('index-online-cancel')->with('fail', lang('Localize.invalid_request'));
		}

		$note = $this->request->getPost('refund_note');
		$note = is_string($note) ? trim($note) : '';

		$this->cancelModel->update($id, [
			'status' => 'done',
			'refund_sent_by' => (int) session()->get('user_id'),
			'refund_sent_at' => egypt_now_sql(),
			'refund_note' => $note === '' ? null : $note,
		]);

		return redirect()->route('index-online-cancel')->with('success', lang('Localize.refund_marked_done'));
	}
}
