<?php

namespace Modules\Account\Controllers;

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

use App\Libraries\Rolepermission;

class Account extends BaseController
{
    protected $Viewpath;
    protected $accountModel;
    protected $userdetailModel;
    protected $agentModel;
    protected $db;
    protected $paymethod;

    public function __construct()
    {

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


    function new()
    {

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

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

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


    public function create()
    {
        $path = 'image/account';
        $invoice = '';
        $imagedocu =  $this->request->getFile('doc_location');
        $userID = $this->session->get('user_id');

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

        if ($imagedocu->isValid() && !$imagedocu->hasMoved()) {
            $invoice     = $this->imgaeCheck($imagedocu, $path);
        }

        $data = array(
            "detail" => $detail,
            "type" => $type,
            "amount" => $amount,
            "system_user_id" => $userID,
            "doc_location" => $invoice,
        );

        $validatedata = array(
            "detail" => $detail,
            "type" => $type,
            "amount" => $amount,
            "system_user_id" => $userID,

        );


        if ($this->validation->run($validatedata, 'account')) {
            $this->accountModel->insert($data);

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

            if (($userRole == 2)) {
                $totalprice =  $amount;
                $type = $type;
                $message = $detail;

                $agentTotalIncome =   agentTotalNormal($userID, $totalprice, $type, $message);
            }


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

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

    public function index()
    {
        $logged_company_id = 0;
        $logged_role_id = $this->session->get('role_id');
        if (! in_array($logged_role_id, [1, 2])) {
            $logged_company_id = $this->session->get('company_id');
        }

        $data['logged_company_id'] = $logged_company_id;
        $data['logged_role_id'] = $logged_role_id;
        $data['start_date'] = $this->request->getGet('start_date') ?: date('Y-m-01');
        $data['end_date'] = $this->request->getGet('end_date') ?: date('Y-m-d');

        $data['module'] = lang('Localize.account');
        $data['title'] = lang('Localize.transaction_list');
        $data['pageheading'] = lang('Localize.transaction_list');

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

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

    /**
     * Geidea order ref for online ticket payments; agent wallet top-ups use payagents.merchant_reference_id.
     */
    private function resolveMerchantIdForTransaction(object $row, array $merchantIdByTicket = []): string
    {
        $fromGeidea = trim((string) ($row->geidea_merchant_id ?? ''));
        if ($fromGeidea !== '') {
            return $fromGeidea;
        }

        $fromPayagent = trim((string) ($row->merchant_reference_id ?? ''));
        if ($fromPayagent !== '') {
            return $fromPayagent;
        }

        $ticketRowId = (int) ($row->ticket_row_id ?? 0);
        if ($ticketRowId > 0 && isset($merchantIdByTicket[$ticketRowId])) {
            return $merchantIdByTicket[$ticketRowId];
        }

        return '';
    }

    private function batchMerchantIdsForTransactions(array $records): array
    {
        $ticketIds = [];
        foreach ($records as $row) {
            if (trim((string) ($row->geidea_merchant_id ?? '')) !== '') {
                continue;
            }
            if (trim((string) ($row->merchant_reference_id ?? '')) !== '') {
                continue;
            }
            $ticketRowId = (int) ($row->ticket_row_id ?? 0);
            if ($ticketRowId > 0) {
                $ticketIds[] = $ticketRowId;
            }
        }

        $ticketIds = array_values(array_unique($ticketIds));
        if ($ticketIds === []) {
            return [];
        }

        $rows = $this->db->table('paystatusgeidea')
            ->select('ticket_id, merchant_id')
            ->whereIn('ticket_id', $ticketIds)
            ->where('merchant_id !=', '')
            ->orderBy('id', 'DESC')
            ->get()
            ->getResult();

        $map = [];
        foreach ($rows as $row) {
            $ticketId = (int) ($row->ticket_id ?? 0);
            if ($ticketId > 0 && ! isset($map[$ticketId])) {
                $map[$ticketId] = trim((string) ($row->merchant_id ?? ''));
            }
        }

        return $map;
    }



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

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

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

        $draw = $request->getPost('draw');
        $start = (int) ($request->getPost('start') ?? 0);
        $length = (int) ($request->getPost('length') ?? 25);
        $searchValue = trim((string) ($request->getPost('search')['value'] ?? ''));

        $fromDate = $request->getPost('start_date') ?: date('Y-m-01');
        $toDate = $request->getPost('end_date') ?: date('Y-m-d');

        $showCompany = (int) $ctx['logged_role_id'] === 1;
        $columns = [0 => 'accounts.id'];
        $colIndex = 1;
        if ($showCompany) {
            $columns[$colIndex++] = 'trip_companies.name';
        }
        $columns[$colIndex++] = 'accounts.detail';
        $columns[$colIndex++] = 'accounts.type';
        $columns[$colIndex++] = 'accounts.amount';
        $columns[$colIndex++] = 'paymethods.name';
        $columns[$colIndex++] = null;
        $columns[$colIndex++] = null;
        $columns[$colIndex++] = 'account_agents.first_name';
        $columns[$colIndex++] = 'accounts.created_at';
        $columns[$colIndex++] = null;

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

        if ($searchValue === '') {
            $countBuilder = $this->db->table('accounts');
            $this->applyAccountListScopeForCount($countBuilder, $ctx, $fromDate, $toDate);
            $totalRecords = $countBuilder->countAllResults();
            $totalRecordwithFilter = $totalRecords;
        } else {
            $countBuilder = $this->db->table('accounts');
            $countBuilder->select('accounts.id', false)->distinct();
            $this->applyAccountListJoins($countBuilder);
            $this->applyAccountListScope($countBuilder, $ctx, $fromDate, $toDate);
            $countBuilder->groupStart()
                ->like('accounts.detail', $searchValue)
                ->orLike('accounts.type', $searchValue)
                ->orLike('accounts.amount', $searchValue)
                ->orLike('accounts.booking_id', $searchValue)
                ->orLike('trip_companies.name', $searchValue)
                ->orLike('account_companies.name', $searchValue)
                ->orLike('paymethods.name', $searchValue)
                ->orLike('account_agents.first_name', $searchValue)
                ->orLike('account_agents.last_name', $searchValue)
                ->orLike('account_user_details.first_name', $searchValue)
                ->orLike('account_user_details.last_name', $searchValue)
                ->orLike('account_employees.first_name', $searchValue)
                ->orLike('account_employees.last_name', $searchValue)
                ->groupEnd();
            $totalRecordwithFilter = $countBuilder->countAllResults(false);

            $totalBuilder = $this->db->table('accounts');
            $this->applyAccountListScopeForCount($totalBuilder, $ctx, $fromDate, $toDate);
            $totalRecords = $totalBuilder->countAllResults();
        }

        $builder = $this->db->table('accounts');
        $builder->select($this->accountListSelectFields(), false);
        $this->applyAccountListJoins($builder);
        $this->applyAccountListScope($builder, $ctx, $fromDate, $toDate);

        if ($searchValue !== '') {
            $builder->groupStart()
                ->like('accounts.detail', $searchValue)
                ->orLike('accounts.type', $searchValue)
                ->orLike('accounts.amount', $searchValue)
                ->orLike('accounts.booking_id', $searchValue)
                ->orLike('trip_companies.name', $searchValue)
                ->orLike('account_companies.name', $searchValue)
                ->orLike('paymethods.name', $searchValue)
                ->orLike('account_agents.first_name', $searchValue)
                ->orLike('account_agents.last_name', $searchValue)
                ->orLike('account_user_details.first_name', $searchValue)
                ->orLike('account_user_details.last_name', $searchValue)
                ->orLike('account_employees.first_name', $searchValue)
                ->orLike('account_employees.last_name', $searchValue)
                ->groupEnd();
        }

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

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

        try {
            $records = $builder->get()->getResult();
        } catch (\Throwable $e) {
            log_message('error', 'Account serverList query failed: {message}', ['message' => $e->getMessage()]);

            return $this->response->setStatusCode(500)->setJSON([
                'draw' => (int) $draw,
                'recordsTotal' => 0,
                'recordsFiltered' => 0,
                'data' => [],
                'error' => 'Unable to load transactions. Please refresh and try again.',
            ]);
        }
        $merchantIdByTicket = $this->batchMerchantIdsForTransactions($records);
        $rows = [];
        $i = $start + 1;

        foreach ($records as $row) {
            $ticketBookType = 'offline';
            if (($row->bookby_user_type ?? '') === 'passanger') {
                $ticketBookType = 'online';
            }
            if (isset($row->agent_payment_mode) && (int) $row->agent_payment_mode === 1) {
                $ticketBookType = 'online';
            }

            $paymentMethod = $ticketBookType === 'online'
                ? 'Geidea'
                : ((isset($row->agent_payment_mode) && (int) $row->agent_payment_mode === 1)
                    ? lang('Localize.online')
                    : esc($row->pay_type ?? ''));

            $merchantId = $this->resolveMerchantIdForTransaction($row, $merchantIdByTicket);
            $merchantDisplay = trim((string) $merchantId) !== '' ? esc($merchantId) : '—';

            $actionHtml = '<form action="' . base_url(route_to('delete-account', $row->id)) . '" method="post" class="deletionForm d-inline">';
            $actionHtml .= csrf_field();
            if ($ctx['edit_data']) {
                $actionHtml .= '<a href="' . base_url(route_to('edit-account', $row->id)) . '" class="btn btn-sm btn-info text-white" title="' . lang('Localize.edit') . '"><i class="fas fa-edit"></i></a> ';
            }
            $actionHtml .= '<a href="' . base_url(route_to('show-account', $row->id)) . '" class="btn btn-sm btn-primary text-white" title="' . lang('Localize.show') . '"><i class="fas fa-eye"></i></a> ';
            if ($ctx['delete_data']) {
                $actionHtml .= '<button type="button" class="btn btn-sm btn-danger"><i class="far fa-trash-alt"></i></button>';
            }
            $actionHtml .= '</form>';

            $entry = ['index' => $i++];
            if ($showCompany) {
                $entry['company_name'] = esc($row->company_name ?? '');
            }
            $entry += [
                'detail' => esc($row->detail ?? ''),
                'type' => esc($row->type ?? ''),
                'amount' => esc($row->amount ?? ''),
                'payment_method' => $paymentMethod,
                'ticket_book_type' => esc($ticketBookType),
                'merchant_id' => $merchantDisplay,
                'creator_name' => esc(trim($row->creator_name ?? '')),
                'created_at' => esc($row->created_at ?? ''),
                'action' => $actionHtml,
            ];

            $rows[] = $entry;
        }

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

    private function getAccountListContext(): array
    {
        $loggedRoleId = (int) $this->session->get('role_id');
        $loggedCompanyId = in_array($loggedRoleId, [1, 2], true) ? 0 : (int) $this->session->get('company_id');
        $userchek = new UserCheck();
        $rolepermissionLibrary = new Rolepermission();

        return [
            'logged_role_id' => $loggedRoleId,
            'logged_company_id' => $loggedCompanyId,
            'current_user_id' => (int) $this->session->get('user_id'),
            'user_type' => $userchek->getUserType(),
            'all_transaction_permission' => $rolepermissionLibrary->read('all_transaction'),
            'edit_data' => $rolepermissionLibrary->edit('transaction_list'),
            'delete_data' => $rolepermissionLibrary->delete('transaction_list'),
        ];
    }

    private function accountListSelectFields(): string
    {
        return 'accounts.id, accounts.detail, accounts.type, accounts.amount, accounts.created_at, accounts.booking_id, '
            . 'users.id AS loguserid, users.role_id, users.login_email AS users_email, '
            . 'COALESCE(trip_companies.name, account_companies.name) AS company_name, '
            . 'tickets.id AS ticket_row_id, tickets.bookby_user_type, '
            . 'paymethods.name AS pay_type, payagents.payment_mode AS agent_payment_mode, payagents.merchant_reference_id, '
            . '(SELECT pg.merchant_id FROM paystatusgeidea pg WHERE pg.ticket_id = tickets.id AND TRIM(pg.merchant_id) != "" '
            . 'ORDER BY pg.id DESC LIMIT 1) AS geidea_merchant_id, '
            . 'CASE '
            . 'WHEN users.role_id = 2 THEN TRIM(CONCAT(COALESCE(account_agents.first_name, ""), " ", COALESCE(account_agents.last_name, ""))) '
            . 'WHEN account_user_details.user_id IS NOT NULL THEN TRIM(CONCAT(COALESCE(account_user_details.first_name, ""), " ", COALESCE(account_user_details.last_name, ""))) '
            . 'ELSE TRIM(CONCAT(COALESCE(account_employees.first_name, ""), " ", COALESCE(account_employees.last_name, ""))) '
            . 'END AS creator_name';
    }

    /**
     * One ticket row per booking_id (avoids duplicate account rows when a booking has many seats).
     */
    private function ticketPickSubquery(): string
    {
        return '(SELECT t.booking_id, t.id, t.bookby_user_type, t.trip_id, '
            . 'COALESCE(t.pay_type_id, t.pay_method_id) AS pay_method_id '
            . 'FROM tickets t '
            . 'INNER JOIN (SELECT booking_id, MIN(id) AS min_id FROM tickets GROUP BY booking_id) pick '
            . 'ON pick.min_id = t.id) AS tickets';
    }

    /**
     * One payagent row per account when multiple rows exist (wallet top-up history).
     */
    private function payagentPickSubquery(): string
    {
        return '(SELECT pa.account_id, pa.payment_mode, pa.merchant_reference_id, pa.company_id '
            . 'FROM payagents pa '
            . 'INNER JOIN (SELECT account_id, MAX(id) AS max_id FROM payagents WHERE account_id IS NOT NULL GROUP BY account_id) pick '
            . 'ON pick.max_id = pa.id) AS payagents';
    }

    private function applyAccountListJoins($builder): void
    {
        $builder
            ->join('users', 'users.id = accounts.system_user_id', 'left')
            ->join($this->ticketPickSubquery(), 'tickets.booking_id = accounts.booking_id', 'left', false)
            ->join('trips', 'trips.id = tickets.trip_id', 'left')
            ->join('companies AS trip_companies', 'trip_companies.id = trips.company_id', 'left')
            ->join($this->payagentPickSubquery(), 'payagents.account_id = accounts.id', 'left', false)
            ->join('companies AS account_companies', 'account_companies.id = payagents.company_id', 'left')
            ->join('paymethods', 'paymethods.id = tickets.pay_method_id', 'left')
            ->join('agents AS account_agents', 'account_agents.user_id = accounts.system_user_id', 'left')
            ->join('user_details AS account_user_details', 'account_user_details.user_id = accounts.system_user_id', 'left')
            ->join('employees AS account_employees', 'account_employees.email = users.login_email', 'left');
    }

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

        if ((int) $ctx['user_type'] === 2) {
            $builder->where('accounts.system_user_id', $ctx['current_user_id']);
        }

        if (! in_array((int) $ctx['logged_role_id'], [1, 2], true)) {
            $companyId = (int) $ctx['logged_company_id'];
            $builder->where('accounts.transaction_type', 'booking');
            $builder->where(
                'EXISTS (SELECT 1 FROM tickets t INNER JOIN trips tr ON tr.id = t.trip_id '
                . 'WHERE t.booking_id = accounts.booking_id AND tr.company_id = ' . $companyId . ')',
                null,
                false
            );

            if (! $ctx['all_transaction_permission']) {
                $builder->where('accounts.system_user_id', $ctx['current_user_id']);
            }
        }
    }

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

        if ((int) $ctx['user_type'] === 2) {
            $builder->where('accounts.system_user_id', $ctx['current_user_id']);
        }

        if (! in_array((int) $ctx['logged_role_id'], [1, 2], true)) {
            $builder->where('accounts.transaction_type', 'booking')
                ->where('trips.company_id', $ctx['logged_company_id']);

            if (! $ctx['all_transaction_permission']) {
                $builder->where('accounts.system_user_id', $ctx['current_user_id']);
            }
        }
    }

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

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

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

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

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

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

    public function update($id)
    {
        $path = 'image/account';
        $invoice = '';
        $imagedocu =  $this->request->getFile('doc_location');
        $userID = $this->session->get('user_id');

        if ($imagedocu->isValid() && !$imagedocu->hasMoved()) {
            $invoice     = $this->imgaeCheck($imagedocu, $path);
        } else {
            $invoice = $this->request->getVar('invoiceold');
        }

        $data = array(
            "id" => $id,
            "detail" => $this->request->getVar('detail'),
            "type" => $this->request->getVar('type'),
            "amount" => $this->request->getVar('amount'),
            "system_user_id" => $userID,
            "doc_location" => $invoice,
        );

        $validatedata = array(

            "detail" => $this->request->getVar('detail'),
            "type" => $this->request->getVar('type'),
            "amount" => $this->request->getVar('amount'),
            "system_user_id" => $userID,

        );

        if ($this->validation->run($validatedata, 'account')) {

            $this->accountModel->save($data);
            return redirect()->route('index-account')->with("success", "Data Save");
        } else {
            $data['paymethod'] = $this->paymethod->findAll();
            $data['validation'] = $this->validation;
            $data['account'] = $this->accountModel->find($id);
            $data['module'] =    lang("Localize.account");
            echo view($this->Viewpath . '\tranjection/new', $data);
        }
    }


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


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