# Financial System Fix Plan, PayPal Removed

## 1. Purpose

This document defines the final remediation plan for the project financial system after removing PayPal from the payment flow.

The goal is to make the financial system consistent, secure, auditable, and easier to maintain. The current system has several risky patterns: duplicated balance calculations, direct invoice mutations from multiple controllers, inconsistent statuses, incomplete authorization on financial routes, inconsistent upload validation, and stale invoice balances after some payment/refund actions.

PayPal should be removed entirely rather than partially repaired. A half-integrated payment gateway is not a feature. It is a liability with a button.

---

## 2. Scope

This plan covers:

- invoice balances
- manual payments
- discounts
- refunds
- additional charges
- event charges
- expenses
- reimbursements
- financial uploads
- route protection
- database cleanup
- ledger recalculation
- old tuition calculator preservation
- new tuition calculation rules
- projected collection reporting UI
- financial tests
- PayPal removal

This plan does not include adding a replacement online payment provider. After this work, the system should support internal/manual payment recording only unless a new provider is designed separately.

---

## 3. Target Outcome

The fixed system must satisfy these conditions:

1. Invoice balances are calculated by one centralized ledger service.
2. Controllers do not manually calculate invoice totals, paid amounts, or balances.
3. All financial writes happen inside database transactions.
4. Financial routes require explicit authentication and permissions.
5. Users cannot access another user's invoices, payments, or financial files.
6. Upload validation is consistent across payments, refunds, expenses, and reimbursements.
7. PayPal routes, controllers, models, views, configs, commands, menus, and webhook exclusions are removed.
8. Status values are normalized and reused through constants.
9. A dry-run recalculation command can audit all invoice balances before changing production data.
10. Tests cover the main ledger scenarios.
11. The old tuition calculator remains available for comparison and collection forecasting.
12. The new tuition calculator supports family-based discounts: first student full amount, second and third student receive $50 discount each, fourth and later students receive $100 discount each.
13. A UI page can estimate expected collectible tuition using old and new tuition rules before invoices are generated or repaired.

---

## 4. Remove PayPal Completely

### 4.1 Remove PayPal routes

Delete these route groups and route entries from `app/Config/Routes.php`:

```php
$routes->get('payments/createPaypalPayment/(:num)', 'View\PaymentController::createPaypalPayment/$1');
$routes->get('payments/executePaypalPayment', 'View\PaymentController::executePaypalPayment');
$routes->get('payments/cancelPaypalPayment', 'View\PaymentController::cancelPaypalPayment');
$routes->post('payments/createPaypalPayment/(:num)', 'View\PaymentController::createPaypalPayment/$1');
$routes->post('api/paypal-webhook', 'Api\PaypalWebhook::handle');
$routes->get('administrator/paypal_transactions', 'View\PaypalTransactionsController::index');
$routes->get('administrator/paypal_transactions/export', 'View\PaypalTransactionsController::exportCsv');
$routes->get('admin/paypal-transactions', 'View\PaypalTransactionsController::index');
$routes->get('admin/paypal-transactions/export', 'View\PaypalTransactionsController::exportCsv');
$routes->get('/payment/paypal', 'View\PaymentController::paypal');
```

Also remove any user/admin/support routes related to PayPal transactions, including:

```php
$routes->get('paypal-transactions', ...);
$routes->get('paypal-transactions/(:num)', ...);
$routes->get('paypal-transactions/transaction/(:segment)', ...);
```

### 4.2 Remove PayPal methods from `PaymentController`

In `app/Controllers/View/PaymentController.php`, remove:

```php
createPaypalPayment()
executePaypalPayment()
cancelPaypalPayment()
paypal()
```

Also remove these imports:

```php
use Config\PaypalConfig;
use PayPal\Api\Amount;
use PayPal\Api\Payment;
use PayPal\Api\PaymentExecution;
use PayPal\Api\Payer;
use PayPal\Api\Transaction;
use PayPal\Rest\ApiContext;
use PayPal\Auth\OAuthTokenCredential;
```

Remove these properties and constructor setup:

```php
protected $paypalConfig;
protected $apiContext;
```

Remove any code that initializes PayPal API context.

### 4.3 Delete PayPal-only files

Delete these files if they are not used by non-PayPal features:

```text
app/Config/PaypalConfig.php
app/Commands/SyncPaypalPayments.php
app/Controllers/View/PaypalTransactionsController.php
app/Models/PayPalPaymentModel.php
app/Models/PaypalTransactionModel.php
app/Views/administrator/paypal_transactions.php
app/Views/payment/payment_redirect.php
```

If `app/Controllers/Api/PaypalWebhook.php` exists, delete it too.

### 4.4 Remove PayPal command registration

In `app/Config/Commands.php`, remove:

```php
\App\Commands\SyncPaypalPayments::class,
```

### 4.5 Remove PayPal CSRF/webhook exclusions

In `app/Config/Filters.php`, remove PayPal webhook exceptions such as:

```php
'api/paypal-webhook',
'index.php/api/paypal-webhook',
```

The webhook will no longer exist. Keeping an unnecessary CSRF exception is security clutter, which is just technical debt with a fake mustache.

### 4.6 Remove PayPal navigation/menu entries

Remove PayPal menu entries from:

```text
app/Database/Seeds/NavSeeder.php
app/Views/partials/navbar_back.php
```

Remove labels such as:

```text
PaypalTransactions
PayPal Transactions
```

### 4.7 Remove PayPal buttons from parent/payment views

In `app/Views/parent/payment_view.php`, remove the PayPal form:

```php
<form method="post" action="<?= base_url('payments/createPaypalPayment/' . $payment['id']) ?>">
    <button type="submit">Pay via PayPal</button>
</form>
```

Replace it with a neutral message or manual-payment instructions if parents need guidance:

```php
<p class="text-muted">
    Online payment is currently unavailable. Please contact the school office to complete payment.
</p>
```

### 4.8 Remove PayPal JavaScript SDK usage

Delete any references like:

```html
<script src="https://www.paypal.com/sdk/js?...">
```

No external PayPal script should remain in the application.

### 4.9 Remove PayPal payment method from forms

Where payment methods are listed, remove PayPal as an option.

Allowed payment methods after removal should be explicit, for example:

```text
cash
check
card
bank_transfer
zelle
other
```

Use the exact methods the business actually supports. Do not keep zombie options in the UI.

### 4.10 Database cleanup for PayPal tables

If the project has tables such as:

```text
paypal_payments
paypal_transactions
```

Do not immediately drop them in the first deployment. Archive first.

Recommended sequence:

```sql
RENAME TABLE paypal_payments TO archived_paypal_payments;
RENAME TABLE paypal_transactions TO archived_paypal_transactions;
```

After one full release cycle with no PayPal dependency, drop the archived tables if the business confirms they are no longer needed:

```sql
DROP TABLE archived_paypal_payments;
DROP TABLE archived_paypal_transactions;
```

If historical PayPal payments were already synced into the main `payments` table, preserve those `payments` records for accounting history. Only remove the integration layer, not legitimate historical payment records.

---

## 5. Centralize Invoice Financial Logic

### 5.1 Create `InvoiceLedgerService`

Create:

```text
app/Libraries/InvoiceLedgerService.php
```

This service must become the only place that calculates invoice totals, paid amounts, balances, and status.

Responsibilities:

- load invoice
- calculate tuition charges
- calculate event charges
- calculate additional charges
- calculate approved discounts
- calculate valid payments
- calculate paid refunds
- calculate final total
- calculate paid amount
- calculate balance
- assign invoice status
- update the invoice record

Suggested structure:

```php
<?php

namespace App\Libraries;

use App\Models\InvoiceModel;
use App\Models\PaymentModel;
use App\Models\RefundModel;
use App\Models\DiscountUsageModel;
use App\Models\AdditionalChargeModel;

class InvoiceLedgerService
{
    protected InvoiceModel $invoiceModel;
    protected PaymentModel $paymentModel;
    protected RefundModel $refundModel;
    protected DiscountUsageModel $discountUsageModel;
    protected AdditionalChargeModel $additionalChargeModel;

    public function __construct()
    {
        $this->invoiceModel = new InvoiceModel();
        $this->paymentModel = new PaymentModel();
        $this->refundModel = new RefundModel();
        $this->discountUsageModel = new DiscountUsageModel();
        $this->additionalChargeModel = new AdditionalChargeModel();
    }

    public function calculateInvoice(int $invoiceId): array
    {
        $invoice = $this->invoiceModel->find($invoiceId);

        if (!$invoice) {
            throw new \RuntimeException('Invoice not found.');
        }

        // Replace these placeholders with project-specific queries.
        $tuitionTotal = $this->calculateTuitionTotal($invoice);
        $eventTotal = $this->calculateEventTotal($invoice);
        $additionalTotal = $this->calculateAdditionalCharges($invoiceId);
        $discountTotal = $this->calculateDiscounts($invoiceId);
        $paidTotal = $this->calculateValidPayments($invoiceId);
        $refundPaidTotal = $this->calculatePaidRefunds($invoiceId);

        $totalAmount = round($tuitionTotal + $eventTotal + $additionalTotal, 2);
        $netDue = round($totalAmount - $discountTotal, 2);
        $balance = round($netDue - $paidTotal - $refundPaidTotal, 2);

        if ($balance <= 0) {
            $status = 'paid';
            $balance = 0.00;
        } elseif ($paidTotal > 0 || $discountTotal > 0 || $refundPaidTotal > 0) {
            $status = 'partially_paid';
        } else {
            $status = 'unpaid';
        }

        return [
            'tuition_total' => number_format($tuitionTotal, 2, '.', ''),
            'event_total' => number_format($eventTotal, 2, '.', ''),
            'additional_total' => number_format($additionalTotal, 2, '.', ''),
            'discount_total' => number_format($discountTotal, 2, '.', ''),
            'paid_amount' => number_format($paidTotal, 2, '.', ''),
            'refund_paid_total' => number_format($refundPaidTotal, 2, '.', ''),
            'total_amount' => number_format($totalAmount, 2, '.', ''),
            'balance' => number_format($balance, 2, '.', ''),
            'status' => $status,
        ];
    }

    public function recalculateInvoice(int $invoiceId): bool
    {
        $calc = $this->calculateInvoice($invoiceId);

        return (bool) $this->invoiceModel->update($invoiceId, [
            'total_amount' => $calc['total_amount'],
            'paid_amount' => $calc['paid_amount'],
            'balance' => $calc['balance'],
            'status' => $calc['status'],
            'updated_at' => utc_now(),
        ]);
    }

    private function calculateTuitionTotal(array $invoice): float
    {
        // Implement using the current tuition source tables.
        return (float) ($invoice['tuition_amount'] ?? 0);
    }

    private function calculateEventTotal(array $invoice): float
    {
        // Implement using invoice event charge tables.
        return 0.00;
    }

    private function calculateAdditionalCharges(int $invoiceId): float
    {
        return (float) $this->additionalChargeModel
            ->where('invoice_id', $invoiceId)
            ->whereNotIn('status', ['voided', 'cancelled', 'canceled'])
            ->selectSum('amount')
            ->first()['amount'] ?? 0;
    }

    private function calculateDiscounts(int $invoiceId): float
    {
        return (float) $this->discountUsageModel
            ->where('invoice_id', $invoiceId)
            ->whereNotIn('status', ['voided', 'cancelled', 'canceled'])
            ->selectSum('amount')
            ->first()['amount'] ?? 0;
    }

    private function calculateValidPayments(int $invoiceId): float
    {
        return (float) $this->paymentModel
            ->where('invoice_id', $invoiceId)
            ->whereNotIn('status', ['voided', 'failed', 'refunded', 'chargeback', 'declined', 'reversed', 'cancelled', 'canceled'])
            ->selectSum('paid_amount')
            ->first()['paid_amount'] ?? 0;
    }

    private function calculatePaidRefunds(int $invoiceId): float
    {
        return (float) $this->refundModel
            ->where('invoice_id', $invoiceId)
            ->whereIn('status', ['paid', 'partially_paid'])
            ->selectSum('refund_paid_amount')
            ->first()['refund_paid_amount'] ?? 0;
    }
}
```

The exact query details must match the existing schema. The key point is not the placeholder code. The key point is that every controller must stop inventing its own math.

### 5.2 Replace controller calculations

Remove direct invoice balance calculations from:

```text
app/Controllers/View/PaymentController.php
app/Controllers/View/InvoiceController.php
app/Controllers/View/DiscountController.php
app/Controllers/View/RefundController.php
app/Controllers/View/FinancialController.php
app/Controllers/View/ExtraChargesController.php
```

Replace them with:

```php
$this->invoiceLedgerService->recalculateInvoice($invoiceId);
```

---

## 6. Fix Manual Payment Flow

### 6.1 Refactor `processPayment()`

`processPayment()` must only insert a payment record. It must not directly update invoice `paid_amount`, `balance`, or `status`.

Correct flow:

```text
validate request
start transaction
lock invoice row
insert payment row
recalculate invoice through InvoiceLedgerService
commit transaction
```

### 6.2 Add row locks

Every payment create/edit/delete/void action must lock the affected invoice row.

Example:

```php
$this->db->transBegin();

try {
    $this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [$invoiceId]);

    // Insert/update payment here.

    $this->invoiceLedgerService->recalculateInvoice($invoiceId);

    $this->db->transCommit();
} catch (\Throwable $e) {
    $this->db->transRollback();
    throw $e;
}
```

Without row locks, two staff members can post payments at the same time and produce incorrect balances. Concurrency bugs are not rare. They are just patient.

### 6.3 Fix installment fields

Do not use `number_of_installments` as the installment sequence.

Add:

```sql
ALTER TABLE payments
ADD COLUMN installment_seq INT NULL AFTER number_of_installments;
```

Use:

```php
'installment_seq' => $installmentSeq,
```

Keep `number_of_installments` only for the actual total number of installments in a plan.

---

## 7. Fix Refund Flow

After any refund is created, updated, marked paid, partially paid, voided, reversed, or reassigned, recalculate the affected invoice.

Required flow:

```text
start transaction
lock invoice row
update refund row
recalculate invoice
commit transaction
```

Refunds must never update invoice balance directly. They should update refund records, then let the ledger service calculate the invoice.

---

## 8. Fix Discounts

Define one discount rule and enforce it inside `InvoiceLedgerService`.

Choose one of these policies:

1. discounts apply only to tuition
2. discounts apply to tuition plus additional charges
3. discounts apply to tuition plus event charges
4. discounts apply to the full invoice

Once the business rule is selected, encode it once. Do not let `InvoiceController`, `DiscountController`, and `PaymentController` each calculate discount base differently. That is not business logic. That is a slow-motion accounting argument.

---

## 9. Fix Additional Charges

Additional charges should not directly mutate invoice totals.

Correct flow:

```text
insert/update/void additional charge
call InvoiceLedgerService::recalculateInvoice($invoiceId)
```

Avoid direct methods such as:

```php
applyAdditionalCharge()
deductAdditionalCharge()
reverseAdditionalCharge()
```

If these methods remain, rewrite them internally to call the ledger service instead of manually adjusting invoice totals.

---


## 10. Tuition Calculator and Collection Prediction System

### 10.1 Purpose

Add a dedicated tuition calculation and collection prediction system so staff can estimate how much tuition can be collected before invoices are generated, recalculated, or repaired.

The existing tuition calculation should be preserved and renamed conceptually as the **Old Tuition Calculator**. The new tuition calculation should be implemented separately and used only when explicitly selected or after the business approves switching invoice generation to the new rule.

Do not overwrite the old logic in place. That is how people create bugs with confidence and then call them migrations.

### 10.2 Required UI page

Add a new admin/staff page:

```text
administrator/tuition-forecast
```

Suggested controller:

```text
app/Controllers/View/TuitionForecastController.php
```

Suggested view:

```text
app/Views/administrator/tuition_forecast.php
```

Suggested services:

```text
app/Libraries/Tuition/OldTuitionCalculatorService.php
app/Libraries/Tuition/NewTuitionCalculatorService.php
app/Libraries/Tuition/TuitionForecastService.php
```

The UI must allow staff to select:

```text
school year
semester
calculator mode: old, new, or compare both
include withdrawn students: yes/no or use refund-deadline rule
include only accepted/enrolled students
include payment pending students
include event-only students: no by default
include already paid invoices: yes/no
```

The page should display:

```text
total families counted
total students counted
projected tuition using Old Tuition Calculator
projected tuition using New Tuition Calculator
difference between old and new projected tuition
amount already collected
remaining collectible amount
family-by-family breakdown
student-by-student tuition calculation detail
warnings for missing class assignments or missing tuition base amount
```

Minimum table columns:

```text
Parent/Family
Student Count
Billable Student Count
Old Tuition Total
New Tuition Total
Difference
Already Paid
Projected Remaining
Warnings
```

Add export buttons:

```text
Export CSV
Export PDF later, optional
```

Start with CSV. PDF can wait. Not everything needs to become a ceremony.

### 10.3 Route protection

Add explicit authorization:

```php
$routes->group('administrator', ['filter' => 'auth'], static function ($routes) {
    $routes->get('tuition-forecast', 'View\TuitionForecastController::index', ['filter' => 'auth:view_financial_reports']);
    $routes->post('tuition-forecast/calculate', 'View\TuitionForecastController::calculate', ['filter' => 'auth:view_financial_reports']);
    $routes->get('tuition-forecast/export', 'View\TuitionForecastController::exportCsv', ['filter' => 'auth:view_financial_reports']);
});
```

Only staff with financial report permissions should access this page.

### 10.4 Preserve the Old Tuition Calculator

The current tuition logic should be extracted into a service and treated as the old calculator.

Current observed behavior should be preserved:

```text
regular students are counted separately from youth students
youth students use youth_fee
regular students use first_student_fee for the first regular student
regular students after the first use second_student_fee
withdrawn students are included or excluded depending on the refund-deadline rule
students with event-only assignments should not be charged tuition
```

Suggested service:

```php
<?php

namespace App\Libraries\Tuition;

final class OldTuitionCalculatorService
{
    public function calculateFamilyTuition(array $students, array $config): array
    {
        $gradeFee = (int) ($config['grade_fee'] ?? 9);
        $firstStudentFee = (int) round(((float) ($config['first_student_fee'] ?? 350)) * 100);
        $secondStudentFee = (int) round(((float) ($config['second_student_fee'] ?? 200)) * 100);
        $youthFee = (int) round(((float) ($config['youth_fee'] ?? 180)) * 100);

        $regularCount = 0;
        $youthCount = 0;
        $details = [];

        foreach ($students as $student) {
            $level = (int) ($student['grade_level'] ?? 999);

            if ($level > $gradeFee) {
                $youthCount++;
                $amountCents = $youthFee;
                $rule = 'old_youth_fee';
            } else {
                $regularCount++;
                $amountCents = $regularCount === 1 ? $firstStudentFee : $secondStudentFee;
                $rule = $regularCount === 1 ? 'old_first_student_fee' : 'old_second_student_fee';
            }

            $details[] = [
                'student_id' => (int) ($student['student_id'] ?? 0),
                'student_name' => (string) ($student['student_name'] ?? ''),
                'rule' => $rule,
                'amount' => number_format($amountCents / 100, 2, '.', ''),
            ];
        }

        $totalCents = array_sum(array_map(
            static fn (array $row): int => (int) round(((float) $row['amount']) * 100),
            $details
        ));

        return [
            'calculator' => 'old',
            'total' => number_format($totalCents / 100, 2, '.', ''),
            'details' => $details,
        ];
    }
}
```

The exact grade parsing should reuse the existing grade-level parser after it is moved out of duplicated controller code.

### 10.5 Add the New Tuition Calculator

The new tuition rule is family-based:

```text
1st student: full amount
2nd student: full amount minus $50
3rd student: full amount minus $50
4th student and above: full amount minus $100 each
```

The "full amount" must come from a configurable value, not a hardcoded controller value.

Add config keys:

```text
new_tuition_full_amount
new_tuition_second_student_discount = 50.00
new_tuition_third_student_discount = 50.00
new_tuition_fourth_plus_discount = 100.00
```

Suggested service:

```php
<?php

namespace App\Libraries\Tuition;

final class NewTuitionCalculatorService
{
    public function calculateFamilyTuition(array $students, array $config): array
    {
        $fullAmountCents = (int) round(((float) ($config['new_tuition_full_amount'] ?? 350)) * 100);
        $secondDiscountCents = (int) round(((float) ($config['new_tuition_second_student_discount'] ?? 50)) * 100);
        $thirdDiscountCents = (int) round(((float) ($config['new_tuition_third_student_discount'] ?? 50)) * 100);
        $fourthPlusDiscountCents = (int) round(((float) ($config['new_tuition_fourth_plus_discount'] ?? 100)) * 100);

        $details = [];
        $position = 0;

        foreach ($students as $student) {
            $position++;

            if ($position === 1) {
                $discountCents = 0;
                $rule = 'new_first_student_full_amount';
            } elseif ($position === 2) {
                $discountCents = $secondDiscountCents;
                $rule = 'new_second_student_50_discount';
            } elseif ($position === 3) {
                $discountCents = $thirdDiscountCents;
                $rule = 'new_third_student_50_discount';
            } else {
                $discountCents = $fourthPlusDiscountCents;
                $rule = 'new_fourth_plus_student_100_discount';
            }

            $amountCents = max(0, $fullAmountCents - $discountCents);

            $details[] = [
                'student_id' => (int) ($student['student_id'] ?? 0),
                'student_name' => (string) ($student['student_name'] ?? ''),
                'family_position' => $position,
                'full_amount' => number_format($fullAmountCents / 100, 2, '.', ''),
                'discount' => number_format($discountCents / 100, 2, '.', ''),
                'rule' => $rule,
                'amount' => number_format($amountCents / 100, 2, '.', ''),
            ];
        }

        $totalCents = array_sum(array_map(
            static fn (array $row): int => (int) round(((float) $row['amount']) * 100),
            $details
        ));

        return [
            'calculator' => 'new',
            'total' => number_format($totalCents / 100, 2, '.', ''),
            'details' => $details,
        ];
    }
}
```

Student ordering must be deterministic. Use this order unless the business defines another rule:

```text
oldest/lower family position first by grade level, then student ID
```

If grade ordering is unreliable, use student ID consistently and display a warning. Random ordering would change tuition totals for the same family, which is exactly the sort of nonsense computers are supposed to prevent.

### 10.6 Collection Prediction Logic

Create `TuitionForecastService` to aggregate projected collections by parent/family.

Responsibilities:

```text
load eligible families for school year and semester
load eligible students per family
exclude event-only students from tuition billing
apply refund-deadline rule for withdrawn students if selected
run old calculator
run new calculator
load existing invoice/payment totals
calculate amount already collected
calculate projected remaining amount
return summary and detail rows
```

Suggested output structure:

```php
return [
    'school_year' => $schoolYear,
    'semester' => $semester,
    'calculator_mode' => $mode,
    'summary' => [
        'family_count' => 120,
        'student_count' => 185,
        'old_projected_tuition' => '42000.00',
        'new_projected_tuition' => '44750.00',
        'difference' => '2750.00',
        'already_collected' => '18000.00',
        'old_remaining_collectible' => '24000.00',
        'new_remaining_collectible' => '26750.00',
    ],
    'families' => [
        [
            'parent_id' => 1,
            'parent_name' => 'Example Family',
            'student_count' => 3,
            'old_total' => '750.00',
            'new_total' => '950.00',
            'difference' => '200.00',
            'already_paid' => '300.00',
            'new_remaining' => '650.00',
            'warnings' => [],
        ],
    ],
];
```

The forecast must not mutate invoices. It is a reporting and planning tool only.

### 10.7 Connect New Calculator to Invoice Generation Later

Do not immediately replace invoice generation with the new calculator unless the business explicitly approves the switch.

Use a feature/config flag:

```text
tuition_calculator_version = old|new
```

When set to `old`, invoices use `OldTuitionCalculatorService`.

When set to `new`, invoices use `NewTuitionCalculatorService`.

The ledger service should call one shared tuition calculation interface instead of hardcoding either calculator:

```php
interface TuitionCalculatorInterface
{
    public function calculateFamilyTuition(array $students, array $config): array;
}
```

This makes the calculator replaceable without rewriting invoice, payment, discount, and refund controllers. Revolutionary idea: one switch instead of five bugs.

### 10.8 Database and Config Additions

Add these config records if the project stores config in the database:

```sql
INSERT INTO configs (`key`, `value`) VALUES
('tuition_calculator_version', 'old'),
('new_tuition_full_amount', '350.00'),
('new_tuition_second_student_discount', '50.00'),
('new_tuition_third_student_discount', '50.00'),
('new_tuition_fourth_plus_discount', '100.00')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
```

If the config table uses different column names, adjust the migration. Do not guess in production. Databases are less forgiving than people, which is one of their better qualities.

Optional audit table for saved forecasts:

```sql
CREATE TABLE tuition_forecasts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    school_year VARCHAR(20) NOT NULL,
    semester VARCHAR(50) NULL,
    calculator_mode VARCHAR(20) NOT NULL,
    family_count INT NOT NULL DEFAULT 0,
    student_count INT NOT NULL DEFAULT 0,
    old_projected_tuition DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    new_projected_tuition DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    already_collected DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    projected_remaining DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    generated_by INT UNSIGNED NULL,
    generated_at DATETIME NOT NULL,
    payload_json LONGTEXT NULL
);
```

Saving forecasts is optional. The UI can calculate live first. Add persistence only if staff need audit history.

### 10.9 Controller Skeleton

```php
<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Libraries\Tuition\TuitionForecastService;

class TuitionForecastController extends BaseController
{
    protected TuitionForecastService $forecastService;

    public function __construct()
    {
        $this->forecastService = new TuitionForecastService();
    }

    public function index()
    {
        return view('administrator/tuition_forecast', [
            'title' => 'Tuition Collection Forecast',
        ]);
    }

    public function calculate()
    {
        $schoolYear = (string) $this->request->getPost('school_year');
        $semester = (string) $this->request->getPost('semester');
        $mode = (string) ($this->request->getPost('calculator_mode') ?? 'compare');

        $result = $this->forecastService->calculate($schoolYear, $semester, $mode, [
            'include_withdrawn_mode' => $this->request->getPost('include_withdrawn_mode') ?? 'refund_deadline',
            'include_paid_invoices' => (bool) $this->request->getPost('include_paid_invoices'),
        ]);

        return $this->response->setJSON($result);
    }

    public function exportCsv()
    {
        // Re-run forecast with query params and stream CSV.
    }
}
```

### 10.10 UI Requirements

The UI should include:

```text
filter form at top
summary cards below filters
comparison chart or simple comparison table
family detail table
expandable student-level detail rows
warning badges
CSV export button
```

Do not bury the difference between old and new calculation. The whole point of the page is to show how much more or less the school can expect to collect under the new rule.

### 10.11 Acceptance Tests for Tuition Forecasting

Required test cases:

```text
one student: full amount
three students: first full, second -50, third -50
four students: first full, second -50, third -50, fourth -100
five students: fourth and fifth both receive -100
full amount lower than discount: charge cannot go below zero
old and new calculator produce separate totals
forecast excludes event-only students
forecast applies withdrawn-student refund-deadline rule
forecast subtracts already collected payments
forecast does not mutate invoices
unauthorized users cannot open the forecast page
CSV export matches displayed totals
```

---

## 11. Protect Financial Routes

Every route related to financial data must have explicit auth and permission filters.

Protect routes for:

```text
invoices
payments
refunds
discounts
additional charges
expenses
reimbursements
financial reports
payment files
parent invoice views
```

Example:

```php
$routes->group('payments', ['filter' => 'auth'], static function ($routes) {
    $routes->get('getByParent/(:num)', 'View\PaymentController::getByParent/$1', ['filter' => 'auth:view_payment']);
    $routes->post('manualPayUpdate', 'View\PaymentController::manualPayUpdate', ['filter' => 'auth:create_payment']);
    $routes->post('manualPayEdit/(:num)', 'View\PaymentController::manualPayEdit/$1', ['filter' => 'auth:update_payment']);
});
```

Parent-facing routes must enforce ownership inside the controller too:

```php
if ((int) $invoice['parent_id'] !== (int) session()->get('parent_id')) {
    return $this->response->setStatusCode(403);
}
```

Route filters are necessary. Controller-level ownership checks are also necessary. Yes, both. Security has layers because humans keep finding doors.

---

## 12. Secure Financial File Access

### 12.1 Stop serving files by raw filename

Do not use routes like:

```text
/payment/check-file/{filename}
```

Use:

```text
/payment/file/{paymentId}
```

### 12.2 Authorize before serving

The server must:

1. load payment by ID
2. verify the payment exists
3. verify the current user can view that payment
4. resolve the exact file path from the payment record
5. serve the file

Example:

```php
public function servePaymentFile(int $paymentId)
{
    $payment = $this->paymentModel->find($paymentId);

    if (!$payment || empty($payment['check_file'])) {
        throw new \CodeIgniter\Exceptions\PageNotFoundException('File not found.');
    }

    if (!$this->canViewPayment($payment)) {
        return $this->response->setStatusCode(403);
    }

    $file = WRITEPATH . 'uploads/checks/' . basename($payment['check_file']);

    if (!is_file($file)) {
        throw new \CodeIgniter\Exceptions\PageNotFoundException('File not found.');
    }

    return $this->response->download($file, null);
}
```

---

## 13. Standardize Upload Validation

Create one upload service/helper for financial attachments.

Allowed types:

```text
pdf
jpg
jpeg
png
```

Recommended max size:

```text
5 MB
```

Example:

```php
private function saveFinancialAttachment($file, string $subdir): ?string
{
    if (!$file || !$file->isValid() || $file->hasMoved()) {
        return null;
    }

    $allowedMimes = ['image/jpeg', 'image/png', 'application/pdf'];
    $allowedExts = ['jpg', 'jpeg', 'png', 'pdf'];

    $mime = $file->getMimeType();
    $ext = strtolower($file->getClientExtension());

    if (!in_array($mime, $allowedMimes, true) || !in_array($ext, $allowedExts, true)) {
        throw new \RuntimeException('Unsupported file type.');
    }

    if ($file->getSize() > 5 * 1024 * 1024) {
        throw new \RuntimeException('File too large.');
    }

    $dir = WRITEPATH . 'uploads/' . trim($subdir, '/') . '/';

    if (!is_dir($dir)) {
        mkdir($dir, 0775, true);
    }

    $name = $file->getRandomName();
    $file->move($dir, $name);

    return $name;
}
```

Use this for:

```text
payment check files
card receipt files
refund files
expense receipts
reimbursement attachments
```

---

## 14. Normalize Status Values

Create status constants.

Suggested file:

```text
app/Libraries/FinancialStatus.php
```

Example:

```php
<?php

namespace App\Libraries;

final class FinancialStatus
{
    public const INVOICE_UNPAID = 'unpaid';
    public const INVOICE_PARTIALLY_PAID = 'partially_paid';
    public const INVOICE_PAID = 'paid';

    public const PAYMENT_RECORDED = 'recorded';
    public const PAYMENT_VOIDED = 'voided';
    public const PAYMENT_FAILED = 'failed';
    public const PAYMENT_REFUNDED = 'refunded';

    public const REFUND_PENDING = 'pending';
    public const REFUND_PARTIALLY_PAID = 'partially_paid';
    public const REFUND_PAID = 'paid';
    public const REFUND_VOIDED = 'voided';

    public const EXCLUDED_PAYMENT_STATUSES = [
        self::PAYMENT_VOIDED,
        self::PAYMENT_FAILED,
        self::PAYMENT_REFUNDED,
    ];
}
```

Then replace scattered string checks with these constants.

### 14.1 Migrate old status values

Normalize existing database values.

Suggested mapping:

```text
Completed      -> recorded
Full           -> paid
Paid           -> paid
Partially Paid -> partially_paid
Recorded       -> recorded
void           -> voided
voided         -> voided
cancelled      -> canceled
```

Back up first. Production databases dislike improvisation.

---

## 15. Improve Money Handling

Do not spread raw float math across controllers.

Minimum fix:

- convert money values to cents during calculations
- convert back to decimal string for database writes

Example:

```php
private function toCents($amount): int
{
    return (int) round(((float) $amount) * 100);
}

private function centsToDecimal(int $cents): string
{
    return number_format($cents / 100, 2, '.', '');
}
```

Better fix:

- use BCMath or a Money value object
- store database values in `DECIMAL(10,2)` or a more appropriate decimal precision

---

## 16. Database Migrations

### 16.1 Payment installment sequence

```sql
ALTER TABLE payments
ADD COLUMN installment_seq INT NULL AFTER number_of_installments;
```

### 16.2 Indexes

```sql
ALTER TABLE invoices
ADD INDEX idx_invoices_parent_year (parent_id, school_year);

ALTER TABLE payments
ADD INDEX idx_payments_invoice_year_status (invoice_id, school_year, status);

ALTER TABLE discount_usages
ADD INDEX idx_discount_invoice (invoice_id);

ALTER TABLE refunds
ADD INDEX idx_refunds_invoice_status (invoice_id, status);

ALTER TABLE additional_charges
ADD INDEX idx_additional_invoice_status (invoice_id, status);
```

### 16.3 Transaction ID uniqueness

Before adding a unique index, check for duplicates:

```sql
SELECT transaction_id, COUNT(*)
FROM payments
WHERE transaction_id IS NOT NULL
GROUP BY transaction_id
HAVING COUNT(*) > 1;
```

If clean:

```sql
ALTER TABLE payments
ADD UNIQUE KEY uq_payments_transaction_id (transaction_id);
```

### 16.4 New tuition calculator config

```sql
INSERT INTO configs (`key`, `value`) VALUES
('tuition_calculator_version', 'old'),
('new_tuition_full_amount', '350.00'),
('new_tuition_second_student_discount', '50.00'),
('new_tuition_third_student_discount', '50.00'),
('new_tuition_fourth_plus_discount', '100.00')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);
```

### 16.5 Archive PayPal tables

```sql
RENAME TABLE paypal_payments TO archived_paypal_payments;
RENAME TABLE paypal_transactions TO archived_paypal_transactions;
```

Only drop later after confirming historical reporting no longer depends on them.

---

## 17. Add Invoice Recalculation Command

Create a Spark command:

```text
php spark finance:recalculate-invoices --dry-run
php spark finance:recalculate-invoices --commit
```

Dry-run behavior:

```text
Invoice #123
Old total: 500.00
New total: 525.00
Old paid: 100.00
New paid: 100.00
Old balance: 400.00
New balance: 425.00
Difference: 25.00
```

Commit behavior:

- update invoice totals
- write audit logs
- output changed invoice IDs
- skip invoices that cannot be safely recalculated

This command is mandatory before production data repair. Blindly recalculating production invoices without a preview is how people turn bugs into archaeology.

---

## 18. Testing Plan

### 18.1 Ledger tests

Test these invoice scenarios:

1. invoice with no payments
2. invoice with one full payment
3. invoice with partial payment
4. invoice with multiple payments
5. invoice with discount
6. invoice with additional charge
7. invoice with event charge
8. invoice with paid refund
9. invoice with partially paid refund
10. invoice with voided payment
11. invoice with failed payment
12. invoice after payment edit
13. invoice after refund update
14. invoice after additional charge removal
15. invoice with negative balance prevention

Each test must assert:

```text
total_amount
paid_amount
balance
status
```

### 18.2 Security tests

Test that:

1. unauthenticated users cannot access financial routes
2. parent users cannot access another parent's invoice
3. parent users cannot access another parent's payment file
4. unauthorized staff cannot create payments
5. unauthorized staff cannot update refunds
6. raw filenames cannot be used to download files
7. deleted PayPal routes return 404

### 18.3 PayPal removal tests

Verify that these no longer exist:

```text
payments/createPaypalPayment
payments/executePaypalPayment
payments/cancelPaypalPayment
api/paypal-webhook
administrator/paypal_transactions
admin/paypal-transactions
/payment/paypal
```

Also verify:

- no PayPal button appears in parent views
- no PayPal SDK script loads
- no PayPal command is registered
- no PayPal menu item appears
- no PayPal config is loaded


### 18.4 Tuition calculator and forecast tests

Verify that:

```text
one student is charged the full amount under the new calculator
second student receives a $50 discount
third student receives a $50 discount
fourth and later students receive a $100 discount
student charge never goes below zero
old calculator remains available and unchanged
forecast page shows old projected tuition, new projected tuition, difference, already collected, and remaining collectible amount
forecast excludes event-only students from tuition
forecast respects the withdrawn-student refund-deadline rule
forecast does not mutate invoices
CSV export matches the UI totals
unauthorized users cannot access the forecast page
```

---

## 19. Deployment Plan

### Step 1: Back up production

Back up:

```text
database
uploaded financial files
current invoices export
payments export
refunds export
discounts export
additional charges export
expenses export
reimbursements export
```

### Step 2: Deploy PayPal removal safely

Deploy code that removes:

```text
PayPal routes
PayPal controller methods
PayPal views
PayPal menu items
PayPal command registration
PayPal webhook exclusion
PayPal config usage
```

Keep archived PayPal database tables for one release cycle.

### Step 3: Deploy ledger service

Deploy:

```text
InvoiceLedgerService
FinancialStatus constants
shared upload validation
secure payment file serving
manual payment transaction locks
refund recalculation
additional charge recalculation
discount recalculation
old tuition calculator service
new tuition calculator service
tuition collection forecast UI
```

### Step 4: Run migrations

Apply:

```text
installment_seq column
indexes
status cleanup
PayPal table archival
transaction_id unique index after duplicate cleanup
```

### Step 5: Run dry-run recalculation

Run:

```bash
php spark finance:recalculate-invoices --dry-run
```

Review differences before committing changes.

### Step 6: Commit recalculation

Run:

```bash
php spark finance:recalculate-invoices --commit
```

Save output logs.

### Step 7: Monitor for one week

Monitor:

```text
invoices with negative balance
paid invoices with nonzero balance
unpaid invoices with payments
failed financial uploads
403 errors on financial files
manual payment errors
refund update errors
missing route errors from old PayPal URLs
```

---

## 20. Implementation Order

Use this order:

1. Remove PayPal UI, routes, config, command registration, webhook, and menu entries.
2. Archive PayPal tables, but do not drop immediately.
3. Add explicit auth and permission filters to financial routes.
4. Secure payment file serving by payment ID.
5. Add shared financial upload validation.
6. Add financial status constants.
7. Add `InvoiceLedgerService`.
8. Refactor manual payment creation and editing.
9. Refactor refund updates.
10. Refactor discounts.
11. Refactor additional charges.
12. Extract the old tuition calculator into its own service.
13. Add the new tuition calculator service.
14. Add the tuition collection forecast UI and CSV export.
15. Add database migrations and indexes.
16. Add recalculation command.
17. Add ledger, security, tuition calculator, and forecast tests.
18. Run production dry-run.
19. Commit recalculation.
20. Monitor and clean up archived PayPal tables later.

---

## 21. Definition of Done

This project is complete when:

1. No PayPal route, view, command, config, SDK script, model, controller, menu item, or webhook remains active.
2. Historical PayPal records are preserved only as archived data if needed.
3. Manual payments work without PayPal dependencies.
4. Every invoice balance is calculated by `InvoiceLedgerService`.
5. Controllers no longer duplicate ledger calculations.
6. Payment and refund writes are transaction-safe.
7. All financial routes have explicit authorization.
8. Financial files cannot be accessed by filename guessing.
9. Upload validation is consistent.
10. Status values are normalized.
11. Recalculation dry-run shows explainable differences only.
12. Automated tests cover key ledger and security scenarios.
13. Old tuition calculator logic is preserved in a dedicated service.
14. New tuition calculator applies the first/full, second-third $50 discount, fourth-plus $100 discount rule.
15. Staff can compare old and new projected tuition collection in a protected UI page.
16. Tuition forecast reports already collected and remaining collectible amounts without mutating invoices.

---

## 22. Final Target Flow

The corrected financial system should follow this flow:

```text
User submits financial action
        ↓
Route-level auth and permission check
        ↓
Controller ownership check
        ↓
Request validation
        ↓
Start database transaction
        ↓
Lock affected invoice row
        ↓
Create/update payment, refund, discount, or charge
        ↓
InvoiceLedgerService recalculates invoice
        ↓
Commit transaction
        ↓
Return response
```

For tuition forecasting, the flow is read-only:

```text
Staff opens tuition forecast page
        ↓
Route-level auth and report permission check
        ↓
Staff selects school year, semester, and calculator mode
        ↓
TuitionForecastService loads eligible families and students
        ↓
OldTuitionCalculatorService and/or NewTuitionCalculatorService calculate projected tuition
        ↓
System subtracts already collected payments
        ↓
UI shows projected collectible amount and CSV export
```

This gives the system one financial truth, removes the unsafe PayPal integration, adds a controlled tuition forecasting tool, and prevents the most likely accounting bugs from recurring. It is not glamorous, but neither is explaining to a parent why their balance changed because three controllers disagreed about arithmetic.
