alrahma_sunday_school/app/Controllers/View/ExpenseController.php

<?php

namespace App\Controllers\View;
use App\Controllers\BaseController;
use App\Libraries\FinancialAttachmentService;
use App\Models\ExpenseModel;
use App\Models\UserModel;
use App\Models\ConfigurationModel;
use CodeIgniter\Exceptions\PageNotFoundException;

class ExpenseController extends BaseController
{
    protected $expenseModel;
    protected $userModel;
    protected $configModel;
    protected FinancialAttachmentService $financialAttachmentService;
    protected $schoolYear;
    protected $semester;
    protected $retailors;

    public function __construct()
    {
        $this->expenseModel = new ExpenseModel();
        $this->userModel    = new UserModel();
        $this->configModel  = new ConfigurationModel();
        $this->financialAttachmentService = new FinancialAttachmentService();

        $this->schoolYear = $this->configModel->getConfig('school_year');
        $this->semester   = getSemester();

        // Default list of common retailors; adjust as needed
        $this->retailors = [
            'Amazon',
            'Walmart',
            'Costco',
            'BJ\'s',
            'Market Basket',
            'Aldi',
            'Hannaford',
            'Sam\'s Club',
            'HomeGoods',
            'Hostinger',
            'Wicked Cheesy',
            'Shatila',
            'Brothers Pizzeria',
            'Paradise Biryani Pointe',
            'Emad Leiman',
            'Nova Trampoline Park',
            'Lubin\'s Awards',
            'Dollar Tree',
            'Stop & Shop',
            'Dunkin\' Donuts',
            'Giovanni\'s Pizza',
            'Trader Joes'
        ];
    }

    /**
     * Return staff users (admins/teachers/etc.) excluding parents/guests.
     */
    private function staffUsers(): array
    {
        $rows = $this->userModel
            ->select('users.id, users.firstname, users.lastname, roles.name AS role_name')
            ->join('user_roles', 'user_roles.user_id = users.id', 'left')
            ->join('roles', 'roles.id = user_roles.role_id', 'left')
            ->where('roles.name IS NOT NULL', null, false)
            ->findAll();

        $excludedRoles = array_map('strtolower', ['parent', 'student', 'guest']);
        $staff = [];

        foreach ($rows as $row) {
            $roleName = strtolower((string) ($row['role_name'] ?? ''));
            $id       = (int) ($row['id'] ?? 0);
            if ($id <= 0 || in_array($roleName, $excludedRoles, true)) {
                continue;
            }
            if (!isset($staff[$id])) {
                $staff[$id] = [
                    'id'        => $id,
                    'firstname' => $row['firstname'] ?? '',
                    'lastname'  => $row['lastname'] ?? '',
                ];
            }
        }

        uasort($staff, static function ($a, $b) {
            $nameA = trim(($a['firstname'] ?? '') . ' ' . ($a['lastname'] ?? ''));
            $nameB = trim(($b['firstname'] ?? '') . ' ' . ($b['lastname'] ?? ''));
            return strcasecmp($nameA, $nameB);
        });

        return array_values($staff);
    }

    public function index()
    {
        $schoolYear = (string) ($this->configModel->getConfig('school_year') ?? '');

        $expenses = $this->expenseModel
            ->select("
                expenses.*,
                u.firstname AS purchaser_firstname, u.lastname AS purchaser_lastname,
                approver.firstname AS approver_firstname, approver.lastname AS approver_lastname
            ")
            ->join('users u', 'u.id = expenses.purchased_by', 'left')
            ->join('users approver', 'approver.id = expenses.approved_by', 'left')
            ->where('expenses.school_year', $schoolYear)
            ->orderBy('expenses.created_at', 'DESC')
            ->findAll();

        // Enrich each row with a URL that goes through Files::receipt($name)
        // We store only the filename in 'receipt_path' (e.g., "1759...f62.png")
        $expenses = array_map(function ($row) {
            $name = $row['receipt_path'] ?? null;
            $row['receipt_url'] = $this->receiptUrl($name);
            return $row;
        }, $expenses);

        return view('expenses/index', [
            'expenses'   => $expenses,
            'schoolYear' => $schoolYear,
        ]);
    }

    public function create()
    {
        $users = $this->staffUsers();

        return view('expenses/create', [
            'users'     => $users,
            'retailors' => $this->retailors,
        ]);
    }

    public function store()
    {
        $rules = [
            'category'     => 'required|in_list[Expense,Purchase,Reimbursement,Donation]',
            'amount'       => 'required|decimal|greater_than[0]',
            // Frontend sends purchased_by as "id|Full Name"
            'purchased_by' => 'required',
            // Optional extra fields
            'retailor'         => 'permit_empty|max_length[255]',
            'date_of_purchase' => 'permit_empty',
            // allow JPG/JPEG/PNG and PDF up to 2MB
            'receipt'      => 'uploaded[receipt]'
                . '|max_size[receipt,5120]'
                . '|ext_in[receipt,jpg,jpeg,png,pdf]'
                . '|mime_in[receipt,image/jpg,image/jpeg,image/png,application/pdf]',
        ];

        $messages = [
            'receipt' => [
                'uploaded' => 'Receipt file is required.',
                'max_size' => 'Maximum file size is 5MB.',
                'ext_in'   => 'Allowed formats: JPG, JPEG, PNG, or PDF.',
                'mime_in'  => 'Allowed formats: JPG, JPEG, PNG, or PDF.',
            ]
        ];

        if (!$this->validate($rules, $messages)) {
            return redirect()->back()->withInput()->with('error', $this->validator->listErrors());
        }

        // Safe values
        $category    = (string) $this->request->getPost('category');
        $amount      = (string) $this->request->getPost('amount');
        $description = (string) $this->request->getPost('description');
        $retailor    = trim((string) $this->request->getPost('retailor'));
        $datePurchase = (string) $this->request->getPost('date_of_purchase');
        $userId      = (int) (session()->get('user_id') ?? 0);
        $isDonation  = ($category === 'Donation');

        // Parse "purchased_by" as "7|John Doe"
        $purchasedInfo = (string) $this->request->getPost('purchased_by');
        [$purchasedById, $purchasedByName] = array_pad(explode('|', $purchasedInfo, 2), 2, null);
        $purchasedById = (int) $purchasedById;

        // School context
        $schoolYear = (string)$this->schoolYear;
        if (!preg_match('/^\d{4}-\d{4}$/', $schoolYear)) {
            return redirect()->back()->withInput()->with('error', 'Invalid school year configuration. Expected YYYY-YYYY.');
        }
        $semester   = $this->semester ?: 'Fall';

        $stagedReceipt = null;
        try {
            $stagedReceipt = $this->financialAttachmentService->stageUploadedFile(
                $this->request->getFile('receipt'),
                'receipts'
            );
        } catch (\RuntimeException $e) {
            return redirect()->back()->withInput()->with('error', $e->getMessage());
        }

        $status       = $isDonation ? 'approved' : 'pending';
        $statusReason = $isDonation ? 'Marked as Donation (non-reimbursable).' : null;

        $db = \Config\Database::connect();
        $db->transBegin();
        try {
            $expenseId = (int)$this->expenseModel->insert([
            'category'     => $category,
            'amount'       => $amount,
            'receipt_path' => null,
            'description'  => $description,
            'retailor'     => ($retailor !== '') ? $retailor : null,
            'date_of_purchase' => ($datePurchase !== '') ? $datePurchase : null,
            'purchased_by' => $purchasedById,
            'added_by'     => $userId,
            'status'       => $status,
            'status_reason'=> $statusReason,
            'approved_by'  => $isDonation ? $userId : null,
            'school_year'  => $schoolYear,
            'semester'     => $semester,
            ]);
            if ($expenseId <= 0) {
                throw new \RuntimeException('Expense insert failed.');
            }
            if ($db->transStatus() === false) {
                throw new \RuntimeException('Expense transaction failed.');
            }
            $db->transCommit();
        } catch (\Throwable $e) {
            $db->transRollback();
            $this->financialAttachmentService->discardStagedFile($stagedReceipt);
            log_message('error', 'Expense creation failed: ' . $e->getMessage());
            return redirect()->back()->withInput()->with('error', 'Expense could not be saved.');
        }

        if ($stagedReceipt !== null) {
            try {
                $receiptName = $this->financialAttachmentService->finalizeStagedFile($stagedReceipt);
                if (!$this->expenseModel->update($expenseId, ['receipt_path' => $receiptName])) {
                    throw new \RuntimeException('Expense receipt update failed.');
                }
            } catch (\Throwable $e) {
                $this->financialAttachmentService->discardStagedFile($stagedReceipt);
                log_message('critical', 'Expense receipt incomplete for expense #' . $expenseId . ': ' . $e->getMessage());
                return redirect()->to('/expenses/index')->with('error', 'Expense saved, but receipt could not be finalized. Operations must review.');
            }
        }

        return redirect()->to('/expenses/index')->with('success', 'Record added successfully!');
    }


    public function updateStatus()
    {
        $data   = $this->request->getJSON(true);
        $id     = isset($data['id']) ? (int)$data['id'] : null;
        $status = $data['status'] ?? null;
        $reason = $data['reason'] ?? '';
        $userId = (int) (session()->get('user_id') ?? 0);

        if (!$id || !in_array($status, ['approved', 'denied'], true)) {
            log_message('error', 'Invalid status or ID');
            return $this->response->setJSON(['error' => 'Invalid data']);
        }

        $db = \Config\Database::connect();
        $db->transBegin();
        try {
            $expense = $db->query('SELECT * FROM expenses WHERE id = ? FOR UPDATE', [$id])->getRowArray();
            if (!$expense) {
                throw new \RuntimeException('Expense not found');
            }
            $this->assertSchoolYearNameWritable((string)($expense['school_year'] ?? ''));
            $db->query(
                "SELECT id FROM reimbursements WHERE expense_id = ? AND LOWER(status) NOT IN ('reversed','rejected','cancelled','canceled','voided') FOR UPDATE",
                [$id]
            )->getResultArray();
            if ($this->hasActiveReimbursement($expense)) {
                throw new \RuntimeException('Expense status cannot change after reimbursement without reversal.');
            }

            $success = $this->expenseModel->update($id, [
                'status'        => $status === 'denied' ? 'rejected' : $status,
                'status_reason' => $reason,
                'approved_by'   => $status === 'approved' ? $userId : null,
                'updated_by'    => $userId
            ]);
            if (!$success || $db->transStatus() === false) {
                throw new \RuntimeException('Update failed');
            }
            $db->transCommit();
        } catch (\Throwable $e) {
            $db->transRollback();
            log_message('error', 'Expense status update failed for ID ' . $id . ': ' . $e->getMessage());
            return $this->response->setJSON(['error' => $e->getMessage()]);
        }

        if (!$success) {
            log_message('error', 'Expense update failed for ID ' . $id);
            return $this->response->setJSON(['error' => 'Update failed']);
        }

        return $this->response->setJSON(['success' => true]);
    }

    /**
     * Build a public URL for a receipt filename through Files::receipt($name).
     * Expects just the filename (e.g., "1759113425_1c443e607e1900f92f62.png").
     */
    private function receiptUrl(?string $filename): ?string
    {
        if (!$filename) {
            return null;
        }
        // Route should be defined as: $routes->get('receipts/(:any)', 'Files::receipt/$1');
        return site_url('receipts/' . $filename);
    }



    public function edit(int $id)
    {
        $expense = $this->expenseModel->find($id);
        if (!$expense) {
            throw PageNotFoundException::forPageNotFound("Expense #$id not found");
        }

        if ($this->hasActiveReimbursement($expense)) {
            return redirect()->back()->with('error', 'Reimbursed expenses are immutable. Reverse the reimbursement and create a replacement expense.');
        }

        // same user list you use in create()
        $users = $this->staffUsers();

        return view('expenses/edit', [
            'expense'     => $expense,
            'users'       => $users,
            'retailors'   => $this->retailors,
            'receipt_url' => $expense['receipt_path'] ? site_url('receipts/' . basename($expense['receipt_path'])) : null,
        ]);
    }

    public function update(int $id)
    {
        helper(['form']);

        $expense = $this->expenseModel->find($id);
        if (!$expense) {
            throw PageNotFoundException::forPageNotFound("Expense #$id not found");
        }
        $this->assertSchoolYearNameWritable((string)($expense['school_year'] ?? ''));

        // Base rules
        $rules = [
            'category'     => 'required|in_list[Expense,Purchase,Reimbursement,Donation]',
            'amount'       => 'required|decimal|greater_than[0]',
            'purchased_by' => 'required', // still "id|Full Name"
            'retailor'         => 'permit_empty|max_length[255]',
            'date_of_purchase' => 'permit_empty',
        ];

        // Optional new receipt validation (only if provided)
        $file = $this->request->getFile('receipt');
        if ($file && $file->isValid() && ($file->getSize() ?? 0) > 0) {
            $rules['receipt'] = 'max_size[receipt,2048]'
                . '|ext_in[receipt,jpg,jpeg,png,webp,gif,pdf]'
                . '|mime_in[receipt,image/jpg,image/jpeg,image/png,image/webp,image/gif,application/pdf]';
        }

        if (!$this->validate($rules)) {
            return redirect()->back()->withInput()->with('errors', $this->validator->getErrors());
        }

        // Parse "id|Name"
        [$purchasedById] = array_pad(explode('|', (string) $this->request->getPost('purchased_by'), 2), 2, null);
        $purchasedById = (int) $purchasedById;
        $category   = (string) $this->request->getPost('category');
        $isDonation = ($category === 'Donation');
        $userId     = (int) (session()->get('user_id') ?? 0);

        $stagedReceipt = null;
        if ($file && $file->isValid() && !$file->hasMoved() && ($file->getSize() ?? 0) > 0) {
            try {
                $stagedReceipt = $this->financialAttachmentService->stageUploadedFile($file, 'receipts');
            } catch (\RuntimeException $e) {
                return redirect()->back()->withInput()->with('error', $e->getMessage());
            }
        }

        $updateData = [
            'category'     => $category,
            'amount'       => (string) $this->request->getPost('amount'),
            'description'  => (string) $this->request->getPost('description'),
            'retailor'     => trim((string) $this->request->getPost('retailor')) ?: null,
            'date_of_purchase' => (string) $this->request->getPost('date_of_purchase') ?: null,
            'purchased_by' => $purchasedById,
            'updated_by'   => $userId,
        ];
        if ($this->request->getPost('remove_receipt') === '1') {
            $updateData['receipt_path'] = null;
        }

        if ($isDonation) {
            $updateData['status']           = 'approved';
            $updateData['status_reason']    = 'Marked as Donation (non-reimbursable).';
            $updateData['approved_by']      = $userId ?: null;
            $updateData['reimbursement_id'] = null;
        } elseif (($expense['category'] ?? '') === 'Donation') {
            // Moving a donation back to a reimbursable category must re-enter approval.
            $updateData['status_reason'] = null;
            $updateData['approved_by']   = null;
            $updateData['status']        = 'pending';
        }

        $db = \Config\Database::connect();
        $db->transBegin();
        try {
            $lockedExpense = $db->query('SELECT * FROM expenses WHERE id = ? FOR UPDATE', [$id])->getRowArray();
            if (!$lockedExpense) {
                throw new \RuntimeException('Expense not found.');
            }
            $activeReimbursements = $db->query(
                "SELECT id FROM reimbursements WHERE expense_id = ? AND LOWER(status) NOT IN ('reversed','rejected','cancelled','canceled','voided') FOR UPDATE",
                [$id]
            )->getResultArray();
            if ($activeReimbursements !== []) {
                $protectedChanged = (
                    (float)$lockedExpense['amount'] !== (float)$updateData['amount']
                    || (string)$lockedExpense['category'] !== (string)$updateData['category']
                    || (int)$lockedExpense['purchased_by'] !== (int)$updateData['purchased_by']
                    || array_key_exists('receipt_path', $updateData)
                );
                if ($protectedChanged) {
                    throw new \RuntimeException('Reimbursed expenses are immutable. Reverse the reimbursement and create a replacement expense.');
                }
            }

            if (!$this->expenseModel->update($id, $updateData) || $db->transStatus() === false) {
                throw new \RuntimeException('Expense update failed.');
            }
            $db->transCommit();
        } catch (\Throwable $e) {
            $db->transRollback();
            $this->financialAttachmentService->discardStagedFile($stagedReceipt);
            return redirect()->back()->withInput()->with('error', $e->getMessage());
        }

        if ($stagedReceipt !== null) {
            try {
                $receiptName = $this->financialAttachmentService->finalizeStagedFile($stagedReceipt);
                if (!$this->expenseModel->update($id, ['receipt_path' => $receiptName])) {
                    throw new \RuntimeException('Expense receipt update failed.');
                }
            } catch (\Throwable $e) {
                $this->financialAttachmentService->discardStagedFile($stagedReceipt);
                log_message('critical', 'Expense receipt replacement incomplete for expense #' . $id . ': ' . $e->getMessage());
                return redirect()->to('/expenses/index')->with('error', 'Expense updated, but receipt could not be finalized. Operations must review.');
            }
        }

        return redirect()->to('/expenses/index')->with('success', 'Expense updated.');
    }

    private function hasActiveReimbursement(array $expense): bool
    {
        $expenseId = (int) ($expense['id'] ?? 0);
        if ($expenseId <= 0) {
            return false;
        }

        if (!empty($expense['reimbursement_id'])) {
            $row = \Config\Database::connect()
                ->table('reimbursements')
                ->select('id')
                ->where('id', (int) $expense['reimbursement_id'])
                ->where("LOWER(status) NOT IN ('reversed','rejected','cancelled','canceled','voided')", null, false)
                ->get()
                ->getRowArray();
            if ($row) {
                return true;
            }
        }

        $row = \Config\Database::connect()
            ->table('reimbursements')
            ->select('id')
            ->where('expense_id', $expenseId)
            ->where("LOWER(status) NOT IN ('reversed','rejected','cancelled','canceled','voided')", null, false)
            ->get()
            ->getRowArray();

        return $row !== null;
    }
}