<?php

namespace Modules\Ticket\Controllers;

use App\Controllers\BaseController;
use App\Libraries\Rolepermission;

use Modules\Ticket\Models\PartialpaidModel;
use Modules\Ticket\Models\TicketModel;
use Modules\Paymethod\Models\PaymethodModel;
use Modules\Ticket\Models\RefundModel;
use Modules\Agent\Models\AgentModel;
use Modules\Agent\Models\Agentcommission;
use Modules\Agent\Models\AgenttotalModel;
use Modules\Ticket\Models\JourneylistModel;

class Refund extends BaseController
{
	protected $Viewpath;
	protected $partialpayModel;
	protected $ticketpayModel;
	protected $paymethodModel;
	protected $db;
	protected $agentModel;
    protected $agentCommissionModel;
    protected $refundModel;

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

        $this->Viewpath = "Modules\Ticket\Views";
		$this->partialpayModel = new PartialpaidModel();
		$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->refundModel = new RefundModel();

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

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

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

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

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

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

	/** Start online (passenger) refund from ticket list — separate from online refund list / Money sent. */
	protected function canRefundOnlineTicket(): bool
	{
		if ($this->isSuperAdmin()) {
			return true;
		}

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

	public function new($ticketid,$type)
	{
		if ($type == "ticket" ) {
			$data['track_table'] = $this->ticketpayModel->find($ticketid);
		}
		

		$data['type'] = $type;
		$data['paymethod'] = $this->paymethodModel->findAll();

		$data['module'] =    lang("Localize.ticket_booking") ; 
		$data['title']  =    lang("Localize.refund_list") ;

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

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

	/**
	 * Manual refund form for online (passenger) tickets — records row on online refund list after submit.
	 */
	public function newOnlineManual($ticketid)
	{
		if (! $this->canRefundOnlineTicket()) {
			return redirect()->route('admin-home')->with('fail', lang('Localize.no_permission_refund_online_ticket'));
		}

		$ticket = $this->ticketpayModel->find((int) $ticketid);
		if (empty($ticket) || (string) ($ticket->bookby_user_type ?? '') !== 'passanger') {
			return redirect()->route('allbookinglist-ticket')->with('fail', lang('Localize.invalid_request'));
		}
		if ((int) ($ticket->refund ?? 0) === 1) {
			return redirect()->route('allbookinglist-ticket')->with('fail', lang('Localize.invalid_request'));
		}
		if ((string) ($ticket->payment_status ?? '') === 'unpaid') {
			return redirect()->route('allbookinglist-ticket')->with('fail', lang('Localize.invalid_request'));
		}

		$data['track_table'] = $ticket;
		$data['type'] = 'ticket';
		$data['paymethod'] = $this->paymethodModel->findAll();
		$data['module'] = lang('Localize.ticket_booking');
		$data['title'] = lang('Localize.online_manual_refund');
		$data['pageheading'] = lang('Localize.online_manual_refund');

		echo view($this->Viewpath . '\refund\online_manual_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_method');
		$paydetail = $this->request->getVar('detail');
		$refun_fee = $this->request->getVar('refund_fee');


		$validRefund = array(
			"booking_id" => $booking_id,
			"type" => $type,
			"refund_by" => $currentuserid,
			
		  );
		 
		$refund = array(
			"booking_id" => $booking_id,
			"refund_fee" => $refun_fee,
			"track_table_id" => $trackTableId,
			"type" => $type,
			"detail" => $paydetail,
			"pay_type_id" => $paymathodid,
			"refund_by" => $currentuserid,
		 );





		 if ($this->validation->run($validRefund, 'refund')) {

			$onlineFlow = (string) $this->request->getPost('online_manual_refund_flow') === '1';
			if ($onlineFlow && $type === 'ticket') {
				if (! $this->canRefundOnlineTicket()) {
					return redirect()->back()->with('fail', lang('Localize.no_permission_refund_online_ticket'));
				}
				$tCheck = $this->ticketpayModel->find($trackTableId);
				if (empty($tCheck) || (string) ($tCheck->bookby_user_type ?? '') !== 'passanger') {
					return redirect()->back()->with('fail', lang('Localize.invalid_request'));
				}
			}

			$this->db->transStart();

			$this->refundModel->insert($refund);
			$refundInsertId = (int) $this->refundModel->getInsertID();

			$tableTargetDetail = null;
			if ($type == "ticket") {

				$data = [
					'id' => $trackTableId,
					'refund' => 1,
				];

				$this->ticketpayModel->save($data);

				$tableTargetDetail = $this->ticketpayModel->where('booking_id', $booking_id)->first();

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

			}

			$totalamount = $this->partialpayModel->selectSum('paidamount')->where('booking_id', $booking_id)->findAll();

			$refundAmount = (int) ($totalamount[0]->paidamount ?? 0);

			if ($type === 'ticket' && $onlineFlow && $tableTargetDetail !== null
				&& (string) ($tableTargetDetail->bookby_user_type ?? '') === 'passanger') {
				$this->insertOnlineManualRefundSnapshot(
					$refundInsertId,
					(int) $trackTableId,
					(string) $booking_id,
					(string) $paymathodid,
					$paydetail !== null ? (string) $paydetail : '',
					$refun_fee,
					$refundAmount,
					$tableTargetDetail,
					(int) $currentuserid
				);
			}

			if ($tableTargetDetail !== null && $tableTargetDetail->bookby_user_type === 'agent') {
				$netRefund = (float) $refundAmount - (float) $refun_fee;
				$trip = $this->db->table('trips')->select('company_id')->where('id', $tableTargetDetail->trip_id)->get()->getRow();
				$company_id = $trip->company_id ?? 0;
				$detail = "Refund (".$booking_id.")";
				if (!empty($refun_fee)) {
					$detail .= " Cancel amount: ".$refun_fee;
				}
				agentRefundTicket($tableTargetDetail->bookby_user_id, $netRefund, $booking_id, $detail, $company_id);
			}

			  if (!empty($refun_fee)) {
				
				$intype = "income";
                $detail = "Refund (".$booking_id.") ";
				$amount = $refun_fee;
                accoutTranjection($intype, $detail, $amount, $currentuserid, $booking_id, "booking");

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

			  	$paymethod_id = $paymathodid;
                $payDetail = $paydetail;
                $extype = "expense";
                $detail = "Refund (".$booking_id.") ";
                accoutTranjection($extype, $detail, $refundAmount, $currentuserid, $booking_id, "booking");


				$agetCommission = $this->agentCommissionModel->where('booking_id',$booking_id)->findAll();

				foreach ($agetCommission as $key => $commissionValue) {

					$data = [
						'id' => $commissionValue->id,
						'commission' => "refund",
					];
					
					$this->agentCommissionModel->save($data);
					
				}
			
				$agetTotal = $this->agetTotalModel->where('booking_id',$booking_id)->where('expense',0)->findAll();

				foreach ($agetTotal as $key => $totalvalue) {
					
					$data = [
						'agent_id' => $totalvalue->agent_id,
						'booking_id' => $totalvalue->booking_id,
						'income' => 0,
						'expense' => $totalvalue->income,
						'detail' => "Refund",
					];

					$this->agetTotalModel->insert($data);
				}

			

				$this->db->transComplete();

			if ($onlineFlow) {
				return redirect()->route('index-online-refund')->with('success', lang('Localize.online_manual_refund_recorded'));
			}

			return redirect()->route('ticketindex-refund')->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.refund_list") ;

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

		}
	}

	/**
	 * Online manual refund list — super admin (role 1) always; others need manage_online_refunds read.
	 */
	public function indexOnlineRefunds()
	{
		if (! $this->canReadManageOnlineRefunds()) {
			return redirect()->route('admin-home')->with('fail', lang('Localize.no_permission_online_refunds'));
		}

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

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

	public function serverListOnlineManualRefunds()
	{
		$request = service('request');
		$draw = (int) $request->getPost('draw');

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

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

		$start = (int) $request->getPost('start');
		$length = (int) $request->getPost('length');

		$searchPost = $request->getPost('search');
		$searchValue = (is_array($searchPost) && isset($searchPost['value'])) ? (string) $searchPost['value'] : '';

		$columns = [
			0 => null,
			1 => 'refunds.booking_id',
			2 => 'refunds.geidea_order_id',
			3 => 'refunds.customer_mobile',
			4 => 'refunds.online_ticket_amount',
			5 => 'refunds.refund_fee',
			6 => 'refunds.online_net_amount',
			7 => 'refunds.detail',
			8 => 'tickets.journeydata',
			9 => 'refunds.created_at',
			10 => 'refundud.first_name',
			11 => 'refunds.online_payout_status',
			12 => 'sentud.first_name',
			13 => 'refunds.refund_sent_at',
			14 => 'refunds.refund_payout_note',
			15 => null,
		];

		$order = $request->getPost('order');
		$orderColumnIndex = (is_array($order) && isset($order[0]['column'])) ? (int) $order[0]['column'] : 9;
		$orderDirRaw = (is_array($order) && isset($order[0]['dir'])) ? strtolower((string) $order[0]['dir']) : 'desc';
		$orderDirection = $orderDirRaw === 'asc' ? 'asc' : 'desc';

		$baseJoin = function ($b) use ($roleId, $companyId) {
			$b->join('tickets', 'tickets.id = refunds.track_table_id', 'inner')
				->where('tickets.bookby_user_type', 'passanger')
				->where('refunds.type', 'ticket')
				->where('refunds.online_payout_status IS NOT NULL', null, false)
				->join('trips', 'trips.id = tickets.trip_id', 'left')
				->join('users refundu', 'refundu.id = refunds.refund_by', 'left')
				->join('user_details refundud', 'refundud.user_id = refundu.id', 'left')
				->join('users sentu', 'sentu.id = refunds.refund_sent_by', 'left')
				->join('user_details sentud', 'sentud.user_id = sentu.id', 'left')
				->join('users puser', 'puser.id = tickets.passanger_id', 'left');
			if ($roleId === 7) {
				$b->where('trips.company_id', $companyId);
			}
		};

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

			$builder = $this->db->table('refunds');
			$builder->select('refunds.*')
				->select('CONCAT(COALESCE(refundud.first_name, ""), " ", COALESCE(refundud.last_name, "")) AS refunded_by_name', false)
				->select('CONCAT(COALESCE(sentud.first_name, ""), " ", COALESCE(sentud.last_name, "")) AS sent_by_name', false)
				->select('COALESCE(NULLIF(refunds.customer_mobile, ""), puser.login_mobile) AS display_customer_mobile', false)
				->select('tickets.journeydata', false);
			$baseJoin($builder);

			if ($searchValue !== '') {
				$builder->groupStart()
					->like('refunds.booking_id', $searchValue)
					->orLike('refunds.geidea_order_id', $searchValue)
					->orLike('refunds.detail', $searchValue)
					->orLike('refunds.online_payout_status', $searchValue)
					->orLike('refunds.refund_payout_note', $searchValue)
					->orLike('refunds.customer_mobile', $searchValue)
					->orLike('puser.login_mobile', $searchValue)
					->orLike('refundud.first_name', $searchValue)
					->orLike('refundud.last_name', $searchValue)
					->orLike('sentud.first_name', $searchValue)
					->orLike('sentud.last_name', $searchValue)
					->groupEnd();
			}

			$filteredCountBuilder = $this->db->table('refunds');
			$filteredCountBuilder->select('refunds.id');
			$baseJoin($filteredCountBuilder);
			if ($searchValue !== '') {
				$filteredCountBuilder->groupStart()
					->like('refunds.booking_id', $searchValue)
					->orLike('refunds.geidea_order_id', $searchValue)
					->orLike('refunds.detail', $searchValue)
					->orLike('refunds.online_payout_status', $searchValue)
					->orLike('refunds.refund_payout_note', $searchValue)
					->orLike('refunds.customer_mobile', $searchValue)
					->orLike('puser.login_mobile', $searchValue)
					->orLike('refundud.first_name', $searchValue)
					->orLike('refundud.last_name', $searchValue)
					->orLike('sentud.first_name', $searchValue)
					->orLike('sentud.last_name', $searchValue)
					->groupEnd();
			}
			$totalRecordwithFilter = $filteredCountBuilder->countAllResults(false);

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

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

			$records = $builder->get()->getResult();
		} catch (\Throwable $e) {
			log_message('error', 'serverListOnlineManualRefunds: ' . $e->getMessage() . "\n" . $e->getTraceAsString());

			return $this->response->setJSON([
				'draw'            => $draw,
				'recordsTotal'    => 0,
				'recordsFiltered' => 0,
				'data'            => [],
				'error'           => lang('Localize.online_refund_list_load_error'),
			]);
		}

		$canMarkDone = $this->canEditManageOnlineRefunds();

		$data = [];
		$i = $start + 1;
		foreach ($records as $row) {
			$refundAmt = (float) ($row->online_net_amount ?? 0);
			$sentAt = $row->refund_sent_at ?? null;
			$moneySentRecorded = $sentAt !== null && $sentAt !== '';
			$rawStatus = strtolower((string) ($row->online_payout_status ?? ''));
			if ($refundAmt <= 0 || $moneySentRecorded || in_array($rawStatus, ['done', 'complete'], true)) {
				$statusLabel = $rawStatus === 'complete'
					? lang('Localize.refund_status_complete')
					: lang('Localize.refund_status_done');
			} else {
				$statusLabel = lang('Localize.refund_status_pending');
			}
			$needsMoneySend = $refundAmt > 0 && ! $moneySentRecorded && $rawStatus === 'pending';

			$action = '';
			if ($canMarkDone && $needsMoneySend) {
				$action = '<button type="button" class="btn btn-sm btn-success js-online-refund-money-sent" data-id="' . (int) $row->id . '">' . esc(lang('Localize.money_sent')) . '</button>';
			} elseif ($refundAmt > 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)) : '';
			$refundAtFmt = ! 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)) : '';

			$feeVal = is_numeric($row->refund_fee ?? null)
				? (float) $row->refund_fee
				: (float) preg_replace('/[^0-9.\-]/', '', (string) ($row->refund_fee ?? '0'));

			$data[] = [
				'index' => $i++,
				'booking_id' => esc($row->booking_id ?? ''),
				'customer_mobile' => esc($row->display_customer_mobile ?? ''),
				'geidea_order_id' => esc($row->geidea_order_id ?? ''),
				'ticket_amount' => esc(number_format((float) ($row->online_ticket_amount ?? 0), 2, '.', '')),
				'refund_fee' => esc(number_format($feeVal, 2, '.', '')),
				'refund_amount' => esc(number_format($refundAmt, 2, '.', '')),
				'payment_detail' => esc($row->detail ?? ''),
				'journey_date' => esc($journeyFmt),
				'refund_at' => esc($refundAtFmt),
				'refunded_by' => esc(trim($row->refunded_by_name ?? '')),
				'status' => esc($statusLabel),
				'sent_by' => esc(trim($row->sent_by_name ?? '')),
				'sent_at' => esc($sentAtFmt),
				'refund_note' => esc($row->refund_payout_note ?? ''),
				'action' => $action,
			];
		}

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

	public function markOnlineManualRefundDone()
	{
		if (! $this->canEditManageOnlineRefunds()) {
			return redirect()->route('index-online-refund')->with('fail', lang('Localize.no_permission_online_refunds'));
		}

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

		$row = $this->refundModel->find($id);
		if (empty($row)) {
			return redirect()->route('index-online-refund')->with('fail', lang('Localize.invalid_request'));
		}

		if ($row->online_payout_status === null || $row->online_payout_status === '') {
			return redirect()->route('index-online-refund')->with('fail', lang('Localize.invalid_request'));
		}

		$roleId = (int) session()->get('role_id');
		if ($roleId === 7) {
			$companyId = session()->get('company_id');
			$ticketId = (int) ($row->track_table_id ?? 0);
			$tripCompany = $this->db->table('tickets')
				->select('trips.company_id')
				->join('trips', 'trips.id = tickets.trip_id', 'inner')
				->where('tickets.id', $ticketId)
				->get()
				->getRow();
			if (empty($tripCompany) || (int) $tripCompany->company_id !== (int) $companyId) {
				return redirect()->route('index-online-refund')->with('fail', lang('Localize.invalid_request'));
			}
		}

		$sentAt = $row->refund_sent_at ?? null;
		if ($sentAt !== null && $sentAt !== '') {
			return redirect()->route('index-online-refund')->with('fail', lang('Localize.invalid_request'));
		}

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

		$rawStatus = strtolower((string) ($row->online_payout_status ?? ''));
		if ($rawStatus !== 'pending') {
			return redirect()->route('index-online-refund')->with('fail', lang('Localize.invalid_request'));
		}

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

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

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

	protected function insertOnlineManualRefundSnapshot(
		int $refundInsertId,
		int $ticketId,
		string $bookingId,
		string $payTypeId,
		string $payDetail,
		$refundFeeInput,
		int $ticketPaidTotal,
		$tableTargetDetail,
		int $refundByUserId
	): void {
		$fee = is_numeric($refundFeeInput) ? (float) $refundFeeInput : (float) preg_replace('/[^0-9.\-]/', '', (string) $refundFeeInput);
		if ($fee < 0) {
			$fee = 0.0;
		}
		$ticketAmt = (float) $ticketPaidTotal;
		$net = max(0.0, $ticketAmt - $fee);

		$geideaRow = $this->db->table('paystatusgeidea')
			->select('order_id')
			->where('ticket_id', $ticketId)
			->orderBy('id', 'DESC')
			->limit(1)
			->get()
			->getRow();
		$geideaOrderId = $geideaRow->order_id ?? null;

		$mobile = '';
		if (! empty($tableTargetDetail->passanger_id)) {
			$pu = $this->db->table('users')
				->select('login_mobile')
				->where('id', (int) $tableTargetDetail->passanger_id)
				->get()
				->getRow();
			$mobile = $pu->login_mobile ?? '';
		}

		if ($refundInsertId < 1) {
			return;
		}

		$this->refundModel->update($refundInsertId, [
			'geidea_order_id'      => $geideaOrderId,
			'customer_mobile'      => $mobile === '' ? null : $mobile,
			'online_ticket_amount' => $ticketAmt,
			'online_net_amount'    => $net,
			'online_payout_status' => $net > 0 ? 'pending' : 'done',
		]);
	}

	public function indexTicket()
	{
		$data['module'] =    lang("Localize.ticket_booking") ; 
		$data['title']  =    lang("Localize.refund_list") ;

		$roleId     = session()->get("role_id");
		$companyId  = session()->get("company_id");
		if ($roleId == 1) {
			$data['refund'] = $this->refundModel
				->select('refunds.*')
				->join('tickets', 'tickets.id = refunds.track_table_id', 'inner')
				->where('refunds.type', 'ticket')
				->groupStart()
					->where('tickets.bookby_user_type !=', 'passanger')
					->orWhere('tickets.bookby_user_type', null)
				->groupEnd()
				->orderBy('refunds.id', 'DESC')
				->withDeleted()
				->findAll();
		} else {
			$data['refund'] = $this->refundModel
				->select('refunds.*')
				->join('tickets', 'tickets.id = refunds.track_table_id', 'inner')
				->join('trips', 'trips.id = tickets.trip_id', 'left')
				->where('trips.company_id', $companyId)
				->where('refunds.type', 'ticket')
				->groupStart()
					->where('tickets.bookby_user_type !=', 'passanger')
					->orWhere('tickets.bookby_user_type', null)
				->groupEnd()
				->orderBy('refunds.id', 'DESC')
				->withDeleted()
				->findAll();
		}

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

		echo view($this->Viewpath.'\refund/index',$data);
	}
	public function serverListRefund()
	{
		$request = service('request');

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

		// 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 => 'refunds.booking_id', // booking_id
			2 => 'refunds.refund_fee', // amount
			3 => 'refunds.type', // type
			4 => 'refunds.detail', // payment details
			5 => 'refunds.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 ----
		// Exclude online (passenger) bookings — those are listed under /refunds/online only.
		$countBuilder = $this->db->table('refunds');
		$countBuilder->select('refunds.id')
			->where('refunds.type', 'ticket')
			->join('tickets', 'tickets.id = refunds.track_table_id', 'inner')
			->groupStart()
				->where('tickets.bookby_user_type !=', 'passanger')
				->orWhere('tickets.bookby_user_type', null)
			->groupEnd();

		if ($roleId != 1) {
			$countBuilder->join('trips', 'trips.id = tickets.trip_id', 'left')
				->where('trips.company_id', $companyId);
		}

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

		// ---- FILTERED QUERY ----
		$builder = $this->db->table('refunds');
		$builder->select('refunds.id, refunds.booking_id, refunds.refund_fee, refunds.type, refunds.detail, refunds.created_at')
			->where('refunds.type', 'ticket')
			->join('tickets', 'tickets.id = refunds.track_table_id', 'inner')
			->groupStart()
				->where('tickets.bookby_user_type !=', 'passanger')
				->orWhere('tickets.bookby_user_type', null)
			->groupEnd();

		if ($roleId != 1) {
			$builder->join('trips', 'trips.id = tickets.trip_id', 'left')
				->where('trips.company_id', $companyId);
		}

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

		$filteredCountBuilder = $this->db->table('refunds');
		$filteredCountBuilder->select('refunds.id')
			->where('refunds.type', 'ticket')
			->join('tickets', 'tickets.id = refunds.track_table_id', 'inner')
			->groupStart()
				->where('tickets.bookby_user_type !=', 'passanger')
				->orWhere('tickets.bookby_user_type', null)
			->groupEnd();

		if ($roleId != 1) {
			$filteredCountBuilder->join('trips', 'trips.id = tickets.trip_id', 'left')
				->where('trips.company_id', $companyId);
		}

		// Apply search filter for filtered count
		if (!empty($searchValue)) {
			$filteredCountBuilder->groupStart()
				->like('refunds.booking_id', $searchValue)
				->orLike('refunds.refund_fee', $searchValue)
				->orLike('refunds.type', $searchValue)
				->orLike('refunds.detail', $searchValue)
				->orLike('refunds.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('refunds.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 ?? ''),
				"refund_fee" => esc($row->refund_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
		]);
	}
}
